Auto_increment ID in Java-Sqlite code

Question

I'm trying to use SQLite with java, I wrote a piece of code that given three String(s)

• name
• sname1
• sname2

has to populate the table

    |---------------------|
    |ID|NAME|SNAME1|SNAME2|
    |1 |Ale |test  |test2 |
    |---------------------|

How could I auto_increment the ID? I tried to use auto_increment in the creation of the table but I got exceptions in populating the table, so I'm looking for a good way to do it.

As you can see from the code, at the moment everytime I execute the program I can INSERT only one row.

package database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

/**
 *
 * @author MITROTTA
 */
public class Database {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) throws SQLException {

        Connection c = null;
        Statement stmt = null;
        String sql;

        //Variabili Database
        String name;
        String sname1, sname2;

        Scanner in = new Scanner(System.in);

        try {
            Class.forName("org.sqlite.JDBC");
            c = DriverManager.getConnection("jdbc:sqlite:test.db");
            c.setAutoCommit(false);
            System.out.println("Opened database successfully");

            //Creazione tabella
            stmt = c.createStatement();
            sql = "CREATE TABLE IF NOT EXISTS COMPANY " +
                   "(ID INT PRIMARY KEY     NOT NULL," +
                   " NAME          CHAR(30)    NOT NULL, " + 
                   " SNAME1         CHAR(30)    NOT NULL, " + 
                   " SNAME2        TEXT)"; 
            stmt.executeUpdate(sql);

            System.out.print("Type name: ");
            name = in.nextLine();
            System.out.print("Type sname1: ");
            sname1 = in.nextLine();
            System.out.print("Type sname2: ");
            sname2 = in.nextLine();

            sql = "INSERT INTO COMPANY (ID,NAME,SNAME1,SNAME2) " +
                   "VALUES (2, '" + name + "','" + sname1 + "','" + sname2 + "');"; 
            stmt.executeUpdate(sql);

            ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
            while ( rs.next() ) {
                int id = rs.getInt("ID");
                String  name1 = rs.getString("NAME");
                String name2  = rs.getString("SNAME1");
                String  name3 = rs.getString("SNAME2");

                System.out.println( "ID = " + id );
                System.out.println( "Name = " + name1 );
                System.out.println( "SName1 = " + name2 );
                System.out.println( "SName3 = " + name3 );
                System.out.println();
            }

            rs.close();
            stmt.close();
            c.commit();
            c.close();
        } catch ( Exception e ) {
            System.err.println( e.getClass().getName() + ": " + e.getMessage() );
            System.exit(0);
        }
    System.out.println("Table created successfully");
    }
}

Answer 1

To get an autoincrementing column, you need to declare it as INTEGER PRIMARY KEY (not INT).

Then you can just insert a NULL value, or omit the column from the INSERT statement.

Answer 2

Just use NULL value instead of id during insert and probably you shouldn't declare it as NOT NULL.
Source: http://www.sqlite.org/faq.html#q1