Mysql -Issue with preparedStatement insert query

Question

I have a table in a MySQL database which is 2 columns,

String insertquery = "INSERT INTO Table (Col1, Col2) VALUES (?, ?)";
PreparedStatement preparedStatement = dbConnection.prepareStatement(insertSQL);
preparedStatement.setString(1, "Val 1");
preparedStatement.setString(2, "Val 2");
preparedStatement.executeUpdate();

The thing is that the second column values decide at run time, where to insert value or keep default. how to set setString as default .

preparedStatement.setString(2, default );

or can i write two seperate sql depends upon condition ? is there any way to handle this senario?

same issue for update sql.

String updatequery = "UPDATE Table SET Col1=?, Col2=?;"
PreparedStatement preparedStatement = dbConnection.prepareStatement(updatequery);
preparedStatement.setString(1, "Val 1");
preparedStatement.setString(2, "Val 2");
preparedStatement.executeUpdate();

I am able to set like this preparedStatement.setNull(2, '');

I have to kept old value as it is, not update as null

Thank you...

What language are you using? If possible in your programming language you can try putting `null` as second parameter — juergen d, Nov 04 '14 at 12:29
I am using java as programming language ,yes want to insert a non changing value — Sandesh, Nov 04 '14 at 12:40
In php I would do it like this, not sure if it helps you-- if not sorry -- INSERT INTO Table (Col1, Col2) VALUES (?, 'value_to_insert') and bind only one param thereafter — baao, Nov 04 '14 at 13:45

score 1 · Answer 1 · edited May 23 '17 at 12:13

To achieve this, you have to leave out the column name and MySQL will then use the default value for any column names that have been omitted.

So when you want the value of Col2 to be the default, then you would write your query like so:

"INSERT INTO Table (Col1) VALUES (?)";

I think your best choice is to actually prepare the statement itself at run-time based on the columns that you want to change.

I'm not really sure how easily this can be done in Java but with PHP this could be done so easily by having an array of column names and adding/removing items off the array at run-time depending on what columns needs to be updated and then using the PHP Implode function to glue each item of the array with a comma and finally concatenating the resulting string to your final query.

Maybe you might be able to find solutions that are more suited for java by reading through this related stack-overflow answer.

Good luck

Yes, maybe making two PreparedStatements and decide dynamically which one to use. — Joop Eggen, Nov 04 '14 at 15:31
Thanks all, Currently I prefer two preparedstatements and decide dynamically which one use.. — Sandesh, Nov 04 '14 at 16:55

Mysql -Issue with preparedStatement insert query

1 Answers1