CODEWITHSUNDEEP
javamysqlhtmljsp
toky
toky

Reputation: 119

Update MySQL table with Java

I am trying to update the prices of the cars but I am only able to update one of the items in the database. I think my logic is wrong.

Can someone suggest to me how to solve it.

MySQL Table:

 +-----------+-----------+
 | car       | price     |
 +-----------+-----------+
 | Bmw       |    434312 |
 | Audi      |    222121 |
 | Mercedes  |     33333 |
 +-----------+-----------+

HTML Code:

                       <td><input TYPE="text" NAME="Bmw"></td>

                       <td><input TYPE="text" NAME="Audi"></td>

                       <td><input TYPE="text" NAME="Mercedes"></td>

Java Code:

  String Bmw=(String)session.getAttribute("Bmw");
    session.setAttribute("Bmw",Bmw);
    Bmw=request.getParameter("Bmw");

 String Audi=(String)session.getAttribute("Audi");
    session.setAttribute("Audi",Audi);
    Audi=request.getParameter("Audi");

 String Mercedes=(String)session.getAttribute("Mercedes");
    session.setAttribute("Mercedes",Mercedes);
    Mercedes=request.getParameter("Mercedes");




 try {
    Connection conn = null;
    Class.forName("com.mysql.jdbc.Driver").newInstance();
    conn =       DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","root");
    Statement st = null;
    session.setAttribute("st",st);
    st = conn.createStatement();
    String sqlqueryCommand = "select * from item";
    ResultSet results = st.executeQuery(sqlqueryCommand);


    while(results.next()) {
            if(request.getParameter("Bmw")!=null &&    results.getString("car").equals("Bmw")) {
                    st.executeUpdate("update item set price='"+Bmw+"' where   car='"+results.getString("car")+"'");
                    conn.commit();
            }
            if(request.getParameter("Audi")!=null && results.getString("car").equals("Audi")) {
                    st.executeUpdate("update item set price='"+Audi+"' where car='"+results.getString("car")+"'");
                    conn.commit();
            }
            if(request.getParameter("Mercedes")!=null && results.getString("car").equals("Mercedes")) {
             st.executeUpdate("update item set price='"+Mercedes+"' where car='"+results.getString("car")+"'");
             conn.commit();
             }  
    }
    conn.close();
}
catch(Exception e) {
    System.out.println(e);
}

Upvotes: 2

Views: 4131

Answers (2)

mahery rafara
mahery rafara

Reputation: 368

Beware when you are traversing your results. You're a updating the table while reading it.

You don't need the Select query since you already have the database metadata.

results.getString("car").equals("Bmw")

rendering this line useless

where   car='"+results.getString("car")+"'"

both statement should be replaced by a simple Where statement:

where car='Bmw'

if you have hardcoded the car type once and shoud use this all along. This allows you to perform updates only. In doubt watch the state of your resultset after con.commit()

Upvotes: 1

yankee
yankee

Reputation: 40860

What do you need the select for? You know the results of that one in advance.

This part:

String Bmw=(String)session.getAttribute("Bmw"); session.setAttribute("Bmw",Bmw);

does not make too much sense. Maybe you did not want to retrieve the value from the session but from the request?

Beware of sql-injections. Use prepared statements and parameter placeholders instead.

Here is a proposal on how you can do things a little shorter and safer. It is from memory on how jdbc works, untestet.

String[] types = new String[]{"Bmw", "Audi", "Mercedes"};
PreparedStatement statement = conn.prepareStatement("update item set price=:price where car=:car");
for(String type : types)
{
  String parameterValue = request.getParameter(type);
  if (type != null)
  {
    statement.setObject("price", parameterValue);
    statement.setObject("car", type);
    statement.executeUpdate();
  }
}
statement.close();

Additionally in your catch block you might want to use e.printStackTrace() instead of System.out.println(e);, which will be way more helpful when debugging.

P.S.: Are you sure that your car column items do not contain any whitespace? Print your results.getString("car") on any loop.

Upvotes: 1

Related Questions