I am getting java.lang.ArrayIndexOutOfBoundsException: Index -1 out of bounds for length 0 whenever I tried to insert an element in the database.

This is my code:

private final String createTable = "CREATE TABLE demo2 ( id INTEGER PRIMARY KEY AUTOINCREMENT, contract_nr INTEGER UNIQUE, name VARCHAR(30),surname VARCHAR(30), address VARCHAR(50), tel VARCHAR(15), date_of_birth DATE, license_nr VARCHAR(30), nationality VARCHAR(10), driver1 VARCHAR(30), driver2 VARCHAR(30), pickup_date DATE, drop_date DATE, renting_days int, pickup_time VARCHAR(10), drop_time VARCHAR(10), price double, included_km double, effected_km double, mail VARCHAR(30), type VARCHAR(10), valid bool, created_by VARCHAR(30))";

private final String insertSQL = "INSERT INTO demo2 (contract_nr, name, surname, address, tel, date_of_birth, license_nr, nationality, driver1, driver2, pickup_date, drop_date, renting_days, pickup_time, drop_time, price, included_km, effected_km, mail, type, valid, created_by) VALUES ('?','?','?','?','?','?','?','?','?','?','?','?','?','?','?','?','?','?','?','?','?','?') ";

public void createNewDatabase() {
    String currentDirPDFpath = System.getProperty("user.dir") "/db/test.db";
    
    String url = "jdbc:sqlite:"  currentDirPDFpath;

    try (Connection conn = DriverManager.getConnection(url)) {
        if (conn != null) {
            DatabaseMetaData meta = conn.getMetaData();
            System.out.println("A new database has been created.");
            
            Statement stmt = null;
            
              stmt = conn.createStatement();
              
              stmt.executeUpdate(createTable);    
              stmt.close();    
              conn.close();
           
            
        }

    } catch (SQLException e) {
        System.out.println(e.getMessage());
    }
}

public void insertContract(Contract c) {
    try {
        Connection conn = Connect.connect();
        if (conn != null) {
            PreparedStatement pstmt = conn.prepareStatement(insertSQL, Statement.RETURN_GENERATED_KEYS);
            pstmt.setInt(1, c.getContractNr());
            pstmt.setString(2, c.getName());
            pstmt.setString(3, c.getSurname());
            pstmt.setString(4, c.getAddress());
            pstmt.setString(5, c.getTel());
            pstmt.setDate(6, Date.valueOf(c.getDateOfBirth()) );
            pstmt.setString(7, c.getLicenseNr());
            pstmt.setString(8, c.getNationality());
            pstmt.setString(9, c.getDriver1());
            pstmt.setString(10, c.getDriver2());
            pstmt.setDate(11, Date.valueOf(c.getPickupDate()));
            pstmt.setDate(12, Date.valueOf(c.getDropDate()));
            pstmt.setDouble(13, c.getRentingDays());
            pstmt.setString(14, c.getPickupTime());
            pstmt.setString(15, c.getDropTime());
            pstmt.setDouble(16, c.getPrice());
            pstmt.setDouble(17, c.getIncludedKM());
            pstmt.setDouble(18, c.getDrivenKM());
            pstmt.setString(19, c.getMail());
            pstmt.setString(20, c.getTypeVeichle().toString());
            pstmt.setBoolean(22, c.getValid());
            pstmt.setString(23, c.getCreatedBy());

            pstmt.executeUpdate();

        } 
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

When executing the line pstmt.setInt(1, c.getContractNr()); I got this execption. Going through the code I saw that:

protected void batch(int pos, Object value) throws SQLException {
    checkOpen();
    if (batch == null) {
        batch = new Object[paramCount];
    }
    batch[batchPos   pos - 1] = value;
}

in this method batch is an empty array. Any suggestion where I am getting wrong?

Stactrace:

java.lang.ArrayIndexOutOfBoundsException: Index 1 out of bounds for length 0
    at org.sqlite.core.CorePreparedStatement.batch(CorePreparedStatement.java:106)
    at org.sqlite.jdbc3.JDBC3PreparedStatement.setInt(JDBC3PreparedStatement.java:323)
    at utils.DBManager.insertContract(DBManager.java:86)
    at VNoleggioMain.generateTableTab(VNoleggioMain.java:135)
    at VNoleggioMain.initialize(VNoleggioMain.java:101)
    at VNoleggioMain.<init>(VNoleggioMain.java:70)
    at VNoleggioMain$1.run(VNoleggioMain.java:57)
    at java.desktop/java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:318)
    at java.desktop/java.awt.EventQueue.dispatchEventImpl(EventQueue.java:771)
    at java.desktop/java.awt.EventQueue$4.run(EventQueue.java:722)
    at java.desktop/java.awt.EventQueue$4.run(EventQueue.java:716)
    at java.base/java.security.AccessController.doPrivileged(AccessController.java:399)
    at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:86)
    at java.desktop/java.awt.EventQueue.dispatchEvent(EventQueue.java:741)
    at java.desktop/java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:203)
    at java.desktop/java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:124)
    at java.desktop/java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:113)
    at java.desktop/java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:109)
    at java.desktop/java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
    at java.desktop/java.awt.EventDispatchThread.run(EventDispatchThread.java:90)

CodePudding user response：

Prepared statement escaped the parameters:

private final String insertSQL = "INSERT INTO demo2 (contract_nr, name, surname, address, tel, date_of_birth, license_nr, nationality, driver1, driver2, pickup_date, drop_date, renting_days, pickup_time, drop_time, price, included_km, effected_km, mail, type, valid, created_by) "
    "VALUES ('?','?','?','?','?','?','?','?','?','?','?','?','?','?','?','?','?','?','?','?','?','?')";

It needed to be replaced with:

private final String insertSQL = "INSERT INTO demo2 (contract_nr, name, surname, address, tel, date_of_birth, license_nr, nationality, driver1, driver2, pickup_date, drop_date, renting_days, pickup_time, drop_time, price, included_km, effected_km, mail, type, valid, created_by) "
    "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

CodePudding user response：

The answer to this question is that we do not need ' around ? in insert sql