C. Pereira C. Pereira - 6 months ago 20
SQL Question

How to pass variables into SQLite in java

I'm trying to create a SQLite for my game, and its working fine untill i try to put some variables in the table HighScores.
If i try to put a variable it only works if i delete the "NOT NULL".

public void SQLite(){
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");

stmt = c.createStatement();
String sql = "CREATE TABLE HighScores " +
"(ID INT PRIMARY KEY NOT NULL," +
" POINTS INT NOT NULL, " +
" NAME CHAR(50) NOT NULL, " +
" TIME INT NOT NULL, " +
" LEVEL INT NOT NULL)";
stmt.executeUpdate(sql);


c.setAutoCommit(false);
System.out.println("Opened database successfully");

stmt = c.createStatement();
String sql2 = "INSERT INTO HIGHSCORES (ID,POINTS,NAME,TIME,LEVEL) " +
"VALUES (1, ?, 'rodrigo', 99, 1 );";
PreparedStatement ps = c.prepareStatement(sql2);
ps.setInt(1, 5);

stmt.executeUpdate(sql2);

stmt.close();
c.commit();
c.close();

} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
}


Opened database successfully
java.sql.SQLException: NOT NULL constraint failed: HighScores.POINTS

Answer

You are calling executeUpdate on stmt instead of the prepared statement. Since sql2 does not have any value for POINTS, it try to insert null hence the exception.

Change :

stmt.executeUpdate(sql2);

to

ps.executeUpdate();