SQL query for updating column with values from a local variable

Question

How can I update my SQL Table column with the value that is stored in a local variable.

In my program I have taken value from the HTML page using the following statement:

String idd=request.getParameter("id");
String report=request.getParameter("rprt");

So now I have to update the value of report in my database table named "ptest" and I am using the following query:

Class.forName("com.mysql.jdbc.Driver"); 
java.sql.Connection con = 
DriverManager.getConnection("jdbc:mysql://localhost:3306/tcs","root","root"); 
Statement st= con.createStatement(); 
ResultSet rs; 
int i=st.executeUpdate("update ptest set result = @reprt where patient_id= 
@idd");


out.println("Successfully Entered");

But the value is not being stored in the database instead NULL is being stored.

I have already seen this question and got no help. Question

Please ignore my mistakes if any in this question as I am new to MYSQL.

idd is used to get the value of patient_id from the html page and reprt is used to get the report from the html page — Piyush Agarwal, Jun 29 '18 at 13:46
@MarkRotteveel I think he started doing that by asking the question — Mark Giaconia, Jun 29 '18 at 14:26
@markg Learning is better done by following a good tutorial or book, than by asking questions like this. — Mark Rotteveel, Jun 29 '18 at 14:33

Farhan Qasim · Accepted Answer · 2018-06-29T14:01:39.847

2

You can use prepared statements in java.

setString or setInt can set different data types into your prepared statements.

The parameter 1, 2 are basically the positions of the question mark. setString(1,report) means that it would set the string report in the 1st question mark in your query.

Hope this code helps you in achieving what you want.

String query = "update ptest set result = ? where patient_id = ?";
PreparedStatement preparedStatement = con.prepareStatement(query);

preparedStatement.setString(1, report);
preparedStatement.setString(2, idd);

preparedStatement.executeUpdate();

edited Jun 29 '18 at 14:01

answered Jun 29 '18 at 13:50

Farhan Qasim

990
5
18

how to use this statement as it is showing the following error PreparedStatment cannot be resolved to a type – Piyush Agarwal Jun 29 '18 at 13:54
@PiyushAgarwal please tell what is the error you are getting so I can cater the answer more correctly. – Farhan Qasim Jun 29 '18 at 13:55
1

`PreparedStatment preparedStatement` should be `PreparedStatement preparedStatement` (Typo in the type declaration) – Jun 29 '18 at 14:01
@FarhanQasim The error: Can not issue data manipulation statements with executeQuery() – Piyush Agarwal Jun 29 '18 at 14:03
@PiyushAgarwal yes I have already edited it and used executeUpdate. sorry for the mistakes, but it would work correctly now. – Farhan Qasim Jun 29 '18 at 14:04

Mark Giaconia · Answer 2 · 2018-06-29T14:23:30.270

0

In JDBC, you use ? as placeholders for where you want to inject values into a statement. So you should do something like this ...

Class.forName("com.mysql.jdbc.Driver"); 
java.sql.Connection con = 
DriverManager.getConnection("jdbc:mysql://localhost:3306/tcs","root","root"); 
PreparedStatement st= con.prepareCall("update ptest set result = ? where patient_id= 
?"); 

///now set the params in order
st.setString(1, report);
st.setString(2, idd);
//then execute
st.executeUpdate();

Doing a string concat with the values is dangerous due to sql injection possibilities, so I typically make statement text static and final, and also if your value has a ' in it that could blow up your sql syntax etc. Also, notice the use of executeUpdate rather than query. Hope this helps

edited Jun 29 '18 at 14:23

answered Jun 29 '18 at 13:57

Mark Giaconia

3,844
5
20
42

there is an error : The method createStatement() in the type Connection is not applicable for the arguments (String) – Piyush Agarwal Jun 29 '18 at 14:06
oops, forgot to change to prepareCall, I changed it so it should be ok now – Mark Giaconia Jun 29 '18 at 14:23

SQL query for updating column with values from a local variable

2 Answers2