CODEWITHSUNDEEP
javadatabasepostgresqlsql-insertpsql
Nitin Goel
Nitin Goel

Reputation: 21

INSERT values to TABLE in java

I am trying to insert values into my postgres database 

url:"jdbc:postgresql://"+SERVER_NAME+":"+PORT_NUMBER+"/"+DATABSE_NAME,DATABASE_UN,DATABASE_PASS

I am able to read from the database. But when I write to database (no exceptions) there are no rows inserted to postgres database:

                sqlQuery = "INSERT INTO tmp_recommend (id, v1, v2, v3, timestamp, v4, v5) VALUES("
                        + "\""+ID+"\", "
                                + "\""+value1+"\", "
                                + "\""+value2+"\", "
                                + value3+", "
                                + new Timestamp((new Date().getTime()))+", "
                                + "\"value4\", "
                                + "\"value5\" "
                                + ")";
                st.executeUpdate(sqlQuery);

NOTE - I tried the following but didn't help:

Upvotes: 0

Views: 315

Answers (1)

Aleksandar Stojadinovic
Aleksandar Stojadinovic

Reputation: 5049

I'll just stop you immediately. Never ever generate any data manipulation SQL statements using simple string concatenation. Not even in exercise because if you get used to it you have some nasty problems, starting with SQL injection. Use the PreparedStatement accordingly. In your case, like this:

PreparedStatement st = conn.prepareStatement("INSERT INTO tmp_recommend (id, v1, v2, v3, timestamp, v4, v5) VALUES(?, ?, ?, ?, ?, ?, ?)");
st.setString(1, ID);
st.setString(2, value1);
st.setString(3, value2);
st.setString(4, value3);
st.setDate(5, new Timestamp((new Date().getTime())));
st.setString(6, value4);
st.setString(7, value5);
st.executeUpdate()

Note that I did not actually compile the code, you might have to modify something. Also, I assumed you work only with textual data. Also see: How does a PreparedStatement avoid or prevent SQL injection?

Besides preventing SQL injection attacks and some more type-safety, you probably won't encounter bugs like you have now too often. With manually creating statements you can easily make a statement which will do something, but not what you expect. In the best case it will be nothing, in worst, you just dropped a production database.

And I'm not even touching performance. Prepared statements (depending on the database, but usually) include some sort of pre-compiling.

Edit: I actually noticed with a delay (while editing my own mistake made because miscounting while reading your code) that you have written "value4" and "value5" in your original code as literals, not variables (notice the double-quotes). Others have noticed similar issues with value1 and value2. This only comes to show how cumbersome and error-prone is manual concatenating anything, not only SQL.

Upvotes: 3

Related Questions