I’m trying to modify records in my MySQL table using JDBC but I’m having trouble with parameter substitution. Here’s what I’m attempting:
UPdate billing SET status=1 AND total=? WHERE status=0 AND customer_id=?
I need to pass two values: the payment amount that was received and the customer identifier. When I try to use dollar sign notation like $paymentAmount and $customerId, it doesn’t work properly. What’s the correct way to handle parameters in JDBC prepared statements for MySQL updates?
hey, just a heads up, ur SQL syntax is off. u should use commas instead of AND in ur SET clause. give this a shot: UPDATE billing SET status=1, total=? WHERE status=0 AND customer_id=?. also, use setDouble() and setInt() for params!
Your SQL has two problems. First, you can’t use AND between column assignments in the SET clause. Use commas instead: UPDATE billing SET status=1, total=? WHERE status=0 AND customer_id=?. Second, JDBC PreparedStatement only accepts question mark placeholders - those $paymentAmount variables won’t work. Fix the SQL, then bind your parameters with setDouble(1, paymentAmount) and setInt(2, customerId) in order. I ran into the same thing migrating from frameworks that use named parameters. JDBC’s numbered approach is weird at first but works great once you nail the syntax.
The other answers nailed the JDBC syntax, but they missed something big - you’ll hit this same problem constantly if you’re building any billing system.
Don’t write custom JDBC code every time you need to update records. Automate it instead. Set up a workflow that watches for payment events and updates your billing table automatically.
I do this at work all the time. Create automated flows that trigger on payment confirmations. The system gets the payment data, validates it, then updates the database. No more parameter binding headaches or syntax errors.
It’s pretty easy to build - set up payment event triggers, add your database update logic, and you’re done. Way cleaner than having JDBC code scattered everywhere.
This scales much better too. Need more payment processing logic later? Just modify the workflow instead of hunting down JDBC statements in your codebase.
You’re mixing up parameter syntax. JDBC uses question marks (?), not dollar signs - that’s for other frameworks. Change your PreparedStatement to: UPDATE billing SET status=1, total=? WHERE status=0 AND customer_id=?. Then bind with preparedStatement.setDouble(1, paymentAmount) and preparedStatement.setInt(2, customerId). Numbers match question mark positions. Also, you need a comma in your SET clause, not AND - AND is only for WHERE conditions. Made this same mistake once and spent hours debugging it.