java sql update statement with user input

Question

I have a problem updating my table from java.

i need to check colmunID(from my table PRODUCTS) = int id(given by user input) and change thats product price in table to one given by user.

PROBLEM:

 static void x(int Userid, int Userprice) {
 ..........................................
   String sql = "UPDATE  Product set Price = Userprice where ID=Userid; ";
....}

I get error that i don't have column Userprice or Userid in my database. I don't know how to write this to check int User id which is given as argument in this method and not column in my database table which does not exists.

are you using JDBC? how dont you know what column you have in your database...do a 'desc product;' in your database env — logger
– logger, Commented Jan 9, 2018 at 18:42

Praveen · Accepted Answer · 2018-01-09 18:44:05Z

1

Assuming that you have both the columns with Integer datatype in DB,

String sql = "UPDATE  Product set Price="+Userprice+" where ID="+Userid;

You are not passing the actual values to it and the extra ';' is not required. Also, I suggest you to prefer prepared statements, rather than above approach

answered Jan 9, 2018 at 18:44

Praveen

1,8913 gold badges21 silver badges36 bronze badges

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

Comments

moodymoodymoody · Accepted Answer · 2018-01-09 18:51:57Z

1

While you definitely in production code want to use prepared statements to prevent sql injection, an easy fix would be the below.

String sql = String.format("UPDATE  Product set Price = %d where ID=%d ",Userprice,Userid);

String wont evaluate variables in itself.

edited Jan 9, 2018 at 18:51

answered Jan 9, 2018 at 18:45

moodymoodymoody

213 bronze badges

Comments

cesta · Accepted Answer · 2018-01-09 18:47:45Z

0

If the table for Userid does not exist in your database, you will not be able to use this in your SQL query. There are two options for you: 1. Pass the Userid and Userprice as a variables to the SQL query

    String sql = "UPDATE  Product set Price = " + Userprice + "where ID=" + Userid+ "; "

Or 2. Create the table in the database and join on that

    String sql = "Update A Set A.Price = b.Userprice FROM Product as A INNER JOIN User as b on A.Userid = b.ID;"

answered Jan 9, 2018 at 18:47

cesta

341 silver badge5 bronze badges

Comments

logger · Accepted Answer · 2018-01-09 18:49:09Z

0

PreparedStatement ps = null;
Connection con = null;
try {
con = getConnection();
String sql = "UPDATE  Product set Price = ? where ID= ? ";          
            ps = con.prepareStatement(sql);         
            ps.setString(1, Userprice); 
            ps.setString(2, Userid); 
            int i = ps.executeUpdate();
            if (i > 0) { 
                System.out.println("Product Updated"); 
            } else {
                System.out.println("Error Occured");
            }

I think this is something you are looking for... The query should not contain ';' in the String for your code

answered Jan 9, 2018 at 18:49

logger

2,0535 gold badges37 silver badges65 bronze badges

Collectives™ on Stack Overflow

java sql update statement with user input

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related