My Id is autoIncrement and other than Id from 1st position,i want to insert the name,price-CodePudding

this is the code where I'm trying to use the insert query of mysql

Connection con=getConnection();
    PreparedStatement ps=con.prepareStatement("insert into itemsList (null,name,amt,expire) values (?,?,?,?);");//when I use the 4?s it displays [Exception in thread "main" java.sql.SQLException: No value specified for parameter 1] and if I use 3?s it shows out of range.
        
        //ps.setInt(1,id);//I declared the id as auto-Increment and primary key
        ps.setString(2,name);
        ps.setDouble(3,amt);
        ps.setString(4,exp);
        
        int n=ps.executeUpdate();
        
        return n;
        
    }

`I don't understand that where I'm going wrong please help me to understand it

getConnection() is where I created the JDBC driver, driver manager till connection and it is returning the connection object to other methods`

I can read data easily but when it comes to exp line its shows exception`

Choose Option :
1.Add Item              2.View all Items
3.Place Order           4.NA
5.View Orders           6.Exit
-------------------------------------------------------------------------------------------
1
Choose type of Item :
1.Normal item   2.Food
2
Enter Item name :
dragon
Enter the Price :
70
Enter the Expire date :
12/11
Exception in thread "main" java.sql.SQLException: No value specified for parameter 1
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1084)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1009)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1320)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:994)
        at com.fixitytech.isodb.ItemDAO.insertItems(ItemDAO.java:37)
        at com.fixitytech.isodb.ShopdbUI.main(ShopdbUI.java:90)

CodePudding user response：

Don't pass null in insert query.

PreparedStatement ps=con.prepareStatement("insert into itemsList (name,amt,expire) values (?,?,?);")
       
        ps.setString(1,name);
        ps.setDouble(2,amt);
        ps.setString(3,exp);

CodePudding user response：

You define 4 columns in your statement, one is using an invalid column name null. And accordingly you define 4 placeholders, waiting for values to be assigned.

But you only supply 3. That‘s the cause for the error.

Solution is to fix the SQL statement and assign values to placeholders at their new position:


PreparedStatement ps=con
  .prepareStatement("insert into itemsList (name,amt,expire) values (?,?,?)");

ps.setString(1,name);
ps.setDouble(2,amt);
ps.setString(3,exp);