Parsing SQLDate, java.util.Date and LocalDate

Question

I'm very confused working with dates could someone point me in the right direction on for the code below, it throws the following exception:

org.h2.jdbc.JdbcSQLException:Invalid value '11' for parameter "parameterIndex" [90008-193]

 import java.util.logging.Level;
 import java.util.logging.Logger;
 import java.sql.*;
 import javax.swing.*;
 import java.awt.*;
 import java.awt.event.ActionEvent;
 import java.awt.event.ActionListener;
 import java.time.LocalDate;
 import java.time.ZoneId;


 public class Database {

     public static void main(String[] args) throws SQLException {  
        Connection conn = null;
        Statement st = null;
        String URL = "jdbc:h2:~/registDB";
        String USER = "admin";
        String PASSWORD = "password";
        ZoneId z = ZoneId.systemDefault() ;
        LocalDate currentDate = LocalDate.now(z);
        LocalDate expiration = currentDate.plusDays(inputFld.getText()); //JTextField.getText()
        java.sql.Date expirationDate = java.sql.Date.valueOf(expiration);

        try {  
           Class.forName("org.h2.Driver").newInstance();
           conn = DriverManager.getConnection(URL,USER,PASS);
           String sql = "INSERT INTO data
                        (fullName,regNum,itemName,note,zHemjee,fee,
                         time,date,totalPay,expirationDate)"
                         + "VALUES"
                         + "(?,?,?,?,?,?,?,?,?,?)";
           pst = conn.prepareStatement(sql);  
                   // 1st index left not being modified on purpose which is ID auto_increment-ed     
           pst.setString(2, getFullName());   // Get methods are   
                                         // JTextField.getText() casted into proper data types
                                         // except the 11th row which is throwing SQLException
           pst.setString(3, getRegNum());
           pst.setString(4, getItemName());
           pst.setString(5, getNote());
           pst.setInt(6, getzHemjee());
           pst.setInt(7, getFee());
           pst.setInt(8, getTime());
           pst.setDate(9, java.sql.Date.valueOf(LocalDate.now()));
           pst.setDouble(10, getTotalPay());
           pst.setDate(11, expirationDate);
           pst.executeUpdate();
           pst.close();
           conn.close();

        } catch (ClassNotFoundException | SQLException ex) {
         Logger.getLogger(database.class.getName()).log(Level.SEVERE, null, ex);
        }      
    }
 }

Here are my SQL statement creating a table with it's column types:

  CREATE TABLE IF NOT EXISTS data "
            +"(id INT NOT NULL AUTO_INCREMENT,"             //int
            + " fullname varchar(30),"                      //String
            + " regNum varchar(10),"                        //String
            + " itemName varchar(30),"                      //String
            + " note varchar(30),"                          //String
            + " zHemjee int,"                            //int
            + " fee number,"                                //int
            + " time INT,"                                  //int
            + " date DATE,"                                 //Date
            + " totalPay BIGINT,"                           //int
            + " expirationDate DATE);");                    //Date

possible duplicate of http://stackoverflow.com/questions/1081234/java-date-insert-into-database — Japu_D_Cret, Mar 28 '17 at 08:34
Thank you for your comment but I tried converting it to String and I'm using PreparedStatement for my original version of the code. — Ashford Tulgaa, Mar 28 '17 at 08:48
@AshfordTulgaa 2011 = 2017 - 4 - 2. Your "date" is interpreted as a mathematical expression. The link above solves the problem. If the code you are using is different from what you posted, then please post your actual code... — assylias, Mar 28 '17 at 13:08
Where is the link you told me about? http://stackoverflow.com/questions/1081234/java-date-insert-into-database This one? — Ashford Tulgaa, Mar 28 '17 at 13:28
Unless your H2 version has a bug, with your current edit you should not be able to get this error. The code in your previous edit would, because you were doing plain concatenation into your query string. — Mark Rotteveel, Mar 28 '17 at 15:08

score 1 · Accepted Answer · answered Mar 28 '17 at 18:52

The issue is that you do you are entering the wrong index. You should be starting at 1 not at 2.
There are only 10 '?' and you have 11th parameter which does not exist.
This code should work:

String sql = "INSERT INTO data
                    (ovogNer,regNum,itemName,note,zHemjee,hvv,
                     hugatsaa,date,totalPay,expirationDate)"
                     + "VALUES"
                     + "(?,?,?,?,?,?,?,?,?,?)";
pst = conn.prepareStatement(sql);       
pst.setString(1, getFullName());  
pst.setString(2, getRegNum());
pst.setString(3, getItemName());
pst.setString(4, getNote());
pst.setInt(5, getzHemjee());
pst.setInt(6, getFee());
pst.setInt(7,  getTime());
pst.setDate(8, java.sql.Date.valueOf(LocalDate.now()));
pst.setDouble(9, getTotalPay());
pst.setDate(10, expirationDate);
pst.executeUpdate();

also you had weird "+" when setting the Integers.

Thank you for your reply but the 1st index is ID and it's AUTO_INCREMENTed so I left that on purpose. — Ashford Tulgaa, Mar 29 '17 at 05:17
Your INSERT query does not have the id in it. hence you should not insert it. I just noticed also ovegner should be fullname (guess a typo - please fix it). On Inserts you do not need to add the id at all. It will be auto incremented by the database. feel free to remove the id from the parameters. — Bojan Petkovic, Mar 29 '17 at 05:22

Chris Parker · Answer 2 · 2017-03-28T19:34:07.163

Bojan Petkovic's answer is correct. Having said that, whenever I have more than two or three parameters, I always use an index variable to prevent errors. Like this:

int index = 1;
String sql = "INSERT INTO data
                    (ovogNer,regNum,itemName,note,zHemjee,hvv,
                     hugatsaa,date,totalPay,expirationDate)"
                     + "VALUES"
                     + "(?,?,?,?,?,?,?,?,?,?)";
pst = conn.prepareStatement(sql);       
pst.setString(index++, getFullName());  
pst.setString(index++, getRegNum());
pst.setString(index++, getItemName());
pst.setString(index++, getNote());
pst.setInt(index++, getzHemjee());
pst.setInt(index++, getFee());
pst.setInt(index++,  getTime());
pst.setDate(index++, java.sql.Date.valueOf(LocalDate.now()));
pst.setDouble(index++, getTotalPay());
pst.setDate(index++, expirationDate);
pst.executeUpdate();

Of course that methodology depends on you putting everything in the proper order (which you did). Another option would be to make internal constants that work out to numbers. By the time you reach that point though, you are at a place where you really need to be using a framework for your SQL access. :)

[EDIT] Or at least the code is "correct" in the sense that the code works as described.

Parsing SQLDate, java.util.Date and LocalDate

2 Answers2