INSERT values to TABLE in java

Question

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:

Change autocommit setting for database
If I try to do manual commit by conn.commit() it throws an exception

org.postgresql.util.PSQLException: Cannot commit when autoCommit is enabled

It is probably throwing an exception and you are ignoring it in the `catch`. Show more of your code, please. I don't know what the types of the values are, but that time stamp value, since you are using a plain statement rather than a prepared statement, is supposed to be quoted but isn't. And quotes in SQL are single quotes - double quotes are only placed around identifiers which are case sensitive. So the quotes you have around `value1` and `value2` are also incorrect. — RealSkeptic, Aug 11 '16 at 21:20
SQL uses single quotes for string literals, double quotes are for identifiers (such as table and column names). But really, `PreparedStatement` is the only sane approach. — mu is too short, Aug 11 '16 at 21:24
I sure would like to know if my answer had any positive influence :) . — Aleksandar Stojadinovic, Aug 16 '16 at 14:24

score 3 · Answer 1 · edited May 23 '17 at 11:44

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.

True. I was more concentrated on substance than form. Thanks. — Aleksandar Stojadinovic, Aug 11 '16 at 23:04

INSERT values to TABLE in java

1 Answers1