SQL INSERT system date into table

Question

I have created a table called "myTable" with a column called CURRENT_DATE and it's data type is "DATE". When I programatically update my table, I also want to place a timestamp or system date in the CURRENT_DATE field. A portion of what I am doing is shown below, but it is not working. I would think this would be easy, but... Can you help?

//System date is captured for placement in the CURRENT_DATE field in myTable
 java.util.Date currentDate = new java.util.Date();
...
stmt.executeQuery("INSERT INTO myTable (NAME, CURRENT_DATE) VALUES (' " + userName + " ', ' " + currentDate + " ')  ");      

Answer 1

You really should be doing this as a prepared statement using parameters, it makes things a lot easier and cuts out a few very simple SQL injection threats.

Connection con = ...;
PreparedStatement statement = con.prepareStatement("INSERT INTO myTable (NAME, CURRENT_DATE) VALUES ( ?, ?)");
statement.setString(1, userName);
statement.setDate(2, currentDate );
statement.execute();

There is plenty of info on how to use prepared statements properly. For example: http://www.jdbc-tutorial.com/jdbc-prepared-statements.htm

Answer 2

If you just want the current date, you can retrieve that from the SQL server, without submitting it as a variable. It varies a little depending on what server you're using, but in MS SQL Server there's a function called getdate().

stmt.executeQuery("INSERT INTO myTable (NAME, CURRENT_DATE) VALUES (' " + userName + " ', getdate()");      

You can also set getdate() as the default value for that field, so that you can omit the field entirely:

stmt.executeQuery("INSERT INTO myTable (NAME) VALUES (' " + userName + " '" ')  ");      

Answer 3

Don't put strings in the SQL. Use a prepared statement and set parameters.