SQLException：值数不相同

Question

我正在使用Apache Derby作为嵌入式数据库。

DatabaseCheck.java

此类用于创建表。

package normal;

//This class if s for checking the database. If the database doesn't exists, this class will create one

import java.sql.*;

public class DatabaseCheck
{
    private Connection con;

    public DatabaseCheck()
    {
        createConnection();
        try
        {
            Statement st = con.createStatement();
            st.executeQuery("select * from PhoneData");

            new MainForm();
        }
        catch(Exception e)
        {
            System.out.println(e.getLocalizedMessage());

            if(e.getLocalizedMessage().equals("Schema 'SA' does not exist"))
            {
                try
                {
                PreparedStatement ps = con.prepareStatement("create table PhoneData(ids integer generated always as identity constraint pkId primary key,names varchar(20),mobileNumber1 varchar(20),mobileNumber2 varchar(20),landNumber1 varchar(20),landNumber2 varchar(20),address varchar(100),category varchar(20),nickName varchar(20),email varchar(20),middleName varchar(20),lastName varchar(20),city varchar(20),country varchar(20))");
                ps.execute();

                PreparedStatement ps2 = con.prepareStatement("create table Emails(accountType varchar(10) constraint pk_user primary key,userName varchar(50) ,passwords varchar(50))");
                ps2.execute();

                new MainForm();

                }
                catch(Exception e2)
                {
                    e2.printStackTrace();
                }
            }
        }
        finally
        {
            closeConnection();
        }
    }

     public void createConnection()
    {
        try
        {
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver");

            con = DriverManager.getConnection("jdbc:derby:PhoneBook;create=true","sa","sasasa");
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }

      public void closeConnection()
    {
        try
        {
            con.close();
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }
}

创建表之后，我正在另一个类中使用方法插入数据。

 public void createConnection()
    {
        try
        {
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver");

            con = DriverManager.getConnection("jdbc:derby:PhoneBook","sa","sasasa");
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }



    @Override
    public void closeConnection()
    {
        try
        {
            con.close();
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }



    @Override
    public String insertData(String firstName, String mobileNumber1, String mobileNumber2, String landNumber1, String landNumber2, String streetAddress, String category, String nickName, String email, String middleName, String lastName, String city, String country)
    {
        String resultString="";
        try
        {
            createConnection();

            con.setAutoCommit(false);
            PreparedStatement ps = con.prepareStatement("insert into PhoneData values(?,?,?,?,?,?,?,?,?,?,?,?,?)");
            ps.setString(1, firstName);
            ps.setString(2, mobileNumber1);
            ps.setString(3, mobileNumber2);
            ps.setString(4, landNumber1);
            ps.setString(5,landNumber2);
            ps.setString(6,streetAddress);
            ps.setString(7,category);
            ps.setString(8,nickName);
            ps.setString(9,email);
            ps.setString(10,middleName);
            ps.setString(11,lastName);
            ps.setString(12,city);
            ps.setString(13,country);

            int result = ps.executeUpdate();

            if(result>0)
            {
                resultString =  "Data Entered Successfully";
            }
            else
            {
                resultString =  "Data Enter Failed";
            }
            con.commit();
        }
        catch(SQLException se)
        {
            try
            {
                con.rollback();
                resultString = "Data rollbacked because of an error";
                se.printStackTrace();
            }
            catch(Exception e)
            {
                resultString = "Data insertion failed and rollback failed because of an error";
                e.printStackTrace();
            }
        }
        finally
        {
            closeConnection();
        }

        return resultString;
    }

由于以下错误，我从未设法插入任何内容

java.sql.SQLSyntaxErrorException: The number of values assigned is not the same as the number of specified or implied columns.
    at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source)
    at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
    at normal.DatabaseHandler.insertData(DatabaseHandler.java:56)
    at normal.InsertForm$InsertDataAction.actionPerformed(InsertForm.java:385)
    at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2018)
    at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2341)
    at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
    at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
    at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:252)
    at java.awt.Component.processMouseEvent(Component.java:6504)
    at javax.swing.JComponent.processMouseEvent(JComponent.java:3321)
    at java.awt.Component.processEvent(Component.java:6269)
    at java.awt.Container.processEvent(Container.java:2229)
    at java.awt.Component.dispatchEventImpl(Component.java:4860)
    at java.awt.Container.dispatchEventImpl(Container.java:2287)
    at java.awt.Component.dispatchEvent(Component.java:4686)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4832)
    at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4492)
    at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4422)
    at java.awt.Container.dispatchEventImpl(Container.java:2273)
    at java.awt.Window.dispatchEventImpl(Window.java:2713)
    at java.awt.Component.dispatchEvent(Component.java:4686)
    at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:707)
    at java.awt.EventQueue.access$000(EventQueue.java:101)
    at java.awt.EventQueue$3.run(EventQueue.java:666)
    at java.awt.EventQueue$3.run(EventQueue.java:664)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:87)
    at java.awt.EventQueue$4.run(EventQueue.java:680)
    at java.awt.EventQueue$4.run(EventQueue.java:678)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:677)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:211)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:128)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:117)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:113)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:105)
    at java.awt.EventDispatchThread.run(EventDispatchThread.java:90)
Caused by: java.sql.SQLException: The number of values assigned is not the same as the number of specified or implied columns.
    at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
    ... 51 more
Caused by: ERROR 42802: The number of values assigned is not the same as the number of specified or implied columns.
    at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
    at org.apache.derby.impl.sql.compile.InsertNode.bindStatement(Unknown Source)
    at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
    at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
    at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
    ... 45 more
BUILD SUCCESSFUL (total time: 10 seconds)

为什么是这样？

Answer 1

您的INSERT查询不正确，当您使用该类型的语法时，假设所有文件都必须具有值，并且由于您具有auto_incremented列，因此未包含它会使它无效。

因此，要更正语法，您必须显式声明所有列，例如

INSERT INTO tableName(col1, col2) VALUES (?,?)

如果我没记错的话，您的表中有14列，PhoneData而您只为13列提供了13个值（不包括auto_increment）ID。准备好的语句的正确查询应如下所示：

INSERT INTO PhoneData( Names, mobileNumber1,..., country) VALUES (?,?,...,?)