java - 使用 JDBC 时出现 SQL 错误

Question

我正在尝试获取一个表单来将信息写入 MySQL 数据库。 servlet 正在连接到确认 jsp，但数据库没有记录信息。我收到以下错误消息:

SEVERE: java.sql.SQLException: No value specified for parameter 11
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
at com.mysql.jdbc.PreparedStatement.checkAllParametersSet(PreparedStatement.java:2560)
at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2536)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2383)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2327)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2312)
at com.sun.gjc.spi.base.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:125)
at eBooks.data.UserDB.update(UserDB.java:100)
at eBooks.controller.RegisterUserServlet.doPost(RegisterUserServlet.java:65)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:754)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:847)
at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1539)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:281)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:655)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:595)
at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:98)
at com.sun.enterprise.web.PESessionLockingStandardPipeline.invoke(PESessionLockingStandardPipeline.java:91)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:162)
at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:330)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:231)
at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:174)
at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:828)
at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:725)
at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:1019)
at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:225)
at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:137)
at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:104)
at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:90)
at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:79)
at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:54)
at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:59)
at com.sun.grizzly.ContextTask.run(ContextTask.java:71)
at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:532)
at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:513)
    at java.lang.Thread.run(Thread.java:722)

SEVERE:     at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
SEVERE:     at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
SEVERE:     at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
SEVERE:     at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
SEVERE:     at com.mysql.jdbc.PreparedStatement.checkAllParametersSet(PreparedStatement.java:2560)
SEVERE:     at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2536)
SEVERE:     at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2383)
SEVERE:     at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2327)
SEVERE:     at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2312)
SEVERE:     at com.sun.gjc.spi.base.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:125)
SEVERE:     at eBooks.data.UserDB.update(UserDB.java:100)
SEVERE:     at eBooks.controller.RegisterUserServlet.doPost(RegisterUserServlet.java:65)
SEVERE:     at javax.servlet.http.HttpServlet.service(HttpServlet.java:754)
SEVERE:     at javax.servlet.http.HttpServlet.service(HttpServlet.java:847)

这是我的 UserDB 类的代码:

package eBooks.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


import eBooks.business.User;
import eBooks.util.DBUtil;

/**
 * @author L10
 */

public class UserDB {

    public static int insert(User user)
    {

        ConnectionPool pool = ConnectionPool.getInstance();
        Connection connection = pool.getConnection();
        PreparedStatement ps = null;

         String query = 
                "INSERT INTO User (fName, lName, email_address, password, dataOfBirth, phone,"
                + " address, city,  state,  country,  zipcode, accountType)"
                + "VALUES (?,?,?,?,?,?,?,?,?,?,?)";

         try
         {
             ps = connection.prepareStatement(query);
             ps.setString(1, user.getfName());
             ps.setString(2, user.getlName());
             ps.setString(3, user.getEmailAddress());
             ps.setString(4, user.getPassword());
             ps.setString(5, user.getDateOfBirth());
             ps.setString(5, user.getPhone());
             ps.setString(6, user.getAddress());
             ps.setString(7, user.getCity());
             ps.setString(8, user.getCountry());
             ps.setString(9, user.getState());
             ps.setString(10, user.getZipcode());
             //ps.setString(11, user.getAccountType()); 

             return ps.executeUpdate();

         }
         catch(SQLException e)
         {
             e.printStackTrace();
             return 0;
         }
         finally
         {
             DBUtil.closePreparedStatement(ps);
             pool.freeConnection(connection);
         }

    }


    public static int update(User user)
    {
        ConnectionPool pool = ConnectionPool.getInstance();
        Connection connection = pool.getConnection();
        PreparedStatement ps = null;

        String query = "UPDATE User SET" 
                        + "fName = ?"
                        + "lName = ?"
                        + "password = ?"
                        + "dateOfBirth = ?"
                        + "phone = ?"
                        + "address = ?"
                        + "city = ?"
                        + "state_or_Region = ?"
                        + "country = ?"
                        + "zip = ?"
                        + ""
                        + "WHERE email_address= ?";   

        try
        {
            ps = connection.prepareStatement(query);
            ps.setString(1, user.getfName());
            ps.setString(2, user.getlName());
            ps.setString(3, user.getEmailAddress());
            ps.setString(4, user.getPassword());
            ps.setString(5, user.getDateOfBirth());
            ps.setString(5, user.getPhone());
            ps.setString(6, user.getAddress());
            ps.setString(7, user.getCity());
            ps.setString(8, user.getCountry());
            ps.setString(9, user.getState());
            ps.setString(10, user.getZipcode());
             //ps.setString(11, user.getAccountType()); 

            return ps.executeUpdate();
        }
        catch(SQLException e)
        {
            e.printStackTrace();
            return 0;
        }
        finally
        {
            DBUtil.closePreparedStatement(ps);
            pool.freeConnection(connection);
        }

    }


    public static int delete(User user)
    {
       ConnectionPool pool = ConnectionPool.getInstance();
       Connection connection = pool.getConnection();
       PreparedStatement ps = null;

       String query = "DELETE FROM User" +
                      "WHERE email_address = ?";
       try
       {
           ps = connection.prepareStatement(query);
           ps.setString(1, user.getEmailAddress());

           return ps.executeUpdate();

       }
       catch(SQLException e)
       {
           e.printStackTrace();
           return 0;
       }
       finally
       {
           DBUtil.closePreparedStatement(ps);
           pool.freeConnection(connection);
       }
    }



    public static boolean emailExists(String emailAddress)
    {
        ConnectionPool pool = ConnectionPool.getInstance();
        Connection connection = pool.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        String query = "SELECT email_address FROM User"+
                       "WHERE email_address = ?";


        try
        {
            ps = connection.prepareStatement(query);
            ps.setString(1, emailAddress);
            rs = ps.executeQuery();
            return rs.next();
        }
        catch(SQLException e)
        {
            e.printStackTrace();
            return false;            
        }
        finally
        {
            DBUtil.closeResultSet(rs);
            DBUtil.closePreparedStatement(ps);
            pool.freeConnection(connection);
        }
    }

    public static User selectUser(String emailAddress)
    {
        ConnectionPool pool = ConnectionPool.getInstance();
        Connection connection = pool.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        String query = "SELECT * FROM  User"+
                       "WHERE email_address = ?";

        try
        {
            ps = connection.prepareStatement(query);
            ps.setString(1, emailAddress);
            rs = ps.executeQuery();

            User user = null;
            if (rs.next())
            {
                user = new User();
                user.setfName(rs.getString("fName"));
                user.setlName(rs.getString("lName"));
                user.setEmailAddress(rs.getString("emailAddress"));
                user.setPassword(rs.getString("password"));
                user.setPhone(rs.getString("phone"));
                user.setDateOfBirth(rs.getString("dateOfBirth"));
                user.setAddress(rs.getString("address"));
                user.setCity(rs.getString("city"));
                user.setCountry(rs.getString("country"));
                user.setState(rs.getString("state"));
                user.setZipcode(rs.getString("zip"));
                // user.setAccountType(rs.getString("accountType")); -- Ask Jassin
            }
            return user;            
        }
        catch(SQLException e)
        {
            e.printStackTrace();
            return null;
        }
        finally
        {
            DBUtil.closeResultSet(rs);
            DBUtil.closePreparedStatement(ps);
            pool.freeConnection(connection);
        }

    }

}

我需要朝着正确的方向插入，希望得到任何建议。

Answer 1

基于查询

String query = 
            "INSERT INTO User (fName, lName, email_address, password, dataOfBirth, phone,"
            + " address, city,  state,  country,  zipcode, accountType)"
            + "VALUES (?,?,?,?,?,?,?,?,?,?,?)";

您应该使用以下查询，其中参数实际上是 12，而不是 11。

     String query = 
            "INSERT INTO User (fName, lName, email_address, password, dataOfBirth, phone,"
            + " address, city,  state,  country,  zipcode, accountType)"
            + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";

对于设置的参数，

         ps = connection.prepareStatement(query);
         ps.setString(1, user.getfName());
         ps.setString(2, user.getlName());
         ps.setString(3, user.getEmailAddress());
         ps.setString(4, user.getPassword());
         // ps.setString(5, user.getDateOfBirth());
         // ps.setString(5, user.getPhone());
         ps.setString(5, user.getDateOfBirth());
         ps.setString(6, user.getPhone());
         ps.setString(7, user.getAddress());
         ps.setString(8, user.getCity());
         ps.setString(9, user.getState());
         ps.setString(10, user.getCountry());
         ps.setString(11, user.getZipcode());
         ps.setString(12, user.getAccountType());

对于更新查询

    String query = "UPDATE User SET" 
                    + "fName = ?"
                    + "lName = ?"
                    + "password = ?"
                    + "dateOfBirth = ?"
                    + "phone = ?"
                    + "address = ?"
                    + "city = ?"
                    + "state_or_Region = ?"
                    + "country = ?"
                    + "zip = ?"
                    + ""
                    + "WHERE email_address= ?"; 

你也缺少一个参数

         ps.setString(1, user.getfName());
         ps.setString(2, user.getlName());
         ps.setString(3, user.getPassword());
         ps.setString(4, user.getDateOfBirth());
         ps.setString(5, user.getPhone());
         ps.setString(6, user.getAddress());
         ps.setString(7, user.getCity());
         ps.setString(8, user.getState());
         ps.setString(9, user.getCountry());
         ps.setString(10, user.getZipcode());
         ps.setString(11, user.getEmailAddress());