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
– juergen d, Commented Nov 4, 2014 at 12:29
I am using java as programming language ,yes want to insert a non changing value — Sandesh
– Sandesh, Commented Nov 4, 2014 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
– baao, Commented Nov 4, 2014 at 13:45

Community · Accepted Answer · 2023-11-17 20:07:23Z

1

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

edited Nov 17, 2023 at 20:07

CommunityBot

11 silver badge

answered Nov 4, 2014 at 13:29

dnshio

9341 gold badge8 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Joop Eggen Over a year ago

Yes, maybe making two PreparedStatements and decide dynamically which one to use.

Sandesh Over a year ago

Thanks all, Currently I prefer two preparedstatements and decide dynamically which one use..

Collectives™ on Stack Overflow

Mysql -Issue with preparedStatement insert query

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related