SQLSyntaxError Java when running an INSERT statement

Question

CREATE TABLE "INVOICE" (
"INVOICENO" VARCHAR(200) NOT NULL PRIMARY KEY,
"DATE" DATE,
"COMPANY" VARCHAR(500),
"PRICE" DOUBLE,
"TYPE" VARCHAR(200),
"INVOICETYPE" VARCHAR(200),
"GENERATEDDOCID" INTEGER  GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) 


);

This is my database table code, when i want to enter values into the table with INSERT INTO ( ) VALUES (?,?,?) it is giving SQLSyntax Error.

I use the code below to insert the values:

      public void addData(addData addData) {
      private String sqlInsertStr = "INSERT INTO 
       NBUSER.INVOICE(INVOICENO,DATE,COMPANY,PRICE,TYPE,INVOICETYPE) VALUES(?,?,?,?,?,?);";
    try {

        stmt = conn.prepareStatement(sqlInsertStr);

        stmt.setString(1, addData.getInvoiceNo());
        stmt.setString(2, addData.getDate());
        stmt.setString(3, addData.getCompany());
        stmt.setDouble(4, addData.getPrice());
        stmt.setString(5, addData.getType());
        stmt.setString(6, addData.getInvoiceType());
        stmt = conn.prepareStatement(sqlInsertStr);
        stmt.executeUpdate();
    } catch (SQLException ex) {
        ex.getMessage();
    }
}

Answer 1

There are few probable source of errors (based on the code review):

1. The query should not be terminated by a semi-colon. Currently, the sql query in your code is being terminated by a semi-colon within the double-quote.

   private String sqlInsertStr = "INSERT INTO NBUSER.INVOICE
   (INVOICENO,DATE,COMPANY,PRICE,TYPE,INVOICETYPE)
    VALUES(?,?,?,?,?,?)";  // removed semi-colon at the end of query.
   

2. You shouldn't declare a column in table bearing the name of standard datatypes like DATE. You should rename it to something like INVOICEDATE, etc while declaring the table.

3. Your second parameter expects a parameter of Date type, whereas you're supplying a string value to the query. It will totally result in exception.

   stmt.setDate(2, new java.sql.Date(addData.getDate());// changed the type.