I have to develop a small program that inserts some data into Oracle database.
Unfortunately, I have some trouble with a SQL statement, and the execution of it. This is the code I am using:
db.execute(
String.format("INSERT INTO tops VALUES (%d, '%s', %d, %f.00, '%s', TO_TIMESTAMP('%s', 'YYYY-MM-DD HH24:MI:SS.FF'))",
item.getID(),
item.getTitle(),
this.elements,
item.getSize(),
item.getEntity(),
timestamp.toString()));
This is the part where the execution should work, but I get the following error:
java.sql.SQLException: ORA-00913: Too many values
You can use prepared statements like this as suggested by Guallaume on the comment;
PreparedStatement pstmt = null;
Connection conn = null;
try{
//if you have a method that creates a connection for you.
conn = getConnection();
pstmt = conn.prepareStatement("INSERT INTO tops(id, title, elements, size, entity, timeStamp) VALUES(?,?,?,?,?,?)");
pstmt.setInt(1,item.getID());
//Assuming that title is a String data type
pstmt.setString(2,item.getTitle());
pstmt.setString(3,this.elements);
pstmt.setDouble(4,item.getSize()); // <--- JDBC will make sure this works
//assuming Entity data type is String
pstmt.setString(5,item.getEntity());
//if your timestamp's string format is
//well formed, you may insert as a string.
pstmt.setString(6,timestamp.toString());
pstmt.executeUpdate();
}catch(Exception e){
e.printStackTrace();
}finally{
try{
pstmt.close();
}catch(Exception e){}
try{
conn.close();
}catch(Exception e){}
}