How to update with dynamic variable?

[mr_anderson]

Hi
We all know that the statement used to update the database using (JDBC with mysql) looks like this:
"assume the table name is NODES and the fields to be updated are value1 and value2"

statement.executeUpdate("update NODES set value1='101', value2='110' where index='30'");

now assume that I have this variable defined in my java program:
String var="999";
how can I put it in value1 or value2, I tried to put it using this statement but I get an error:

statement.executeUpdate("update NODES set value1=var, value2='110' where index='30'");

java.sql.SQLException: Unknown column 'var' in 'field list'

[Norm]

When you build the String, you need to exclude the variable name from the String to have its value put into the String:
"....." + var + "...."

[JosAH]

When you build the String, you need to exclude the variable name from the String to have its value put into the String:
"....." + var + "...."

That can be extremely dangerous (sql injection attacks); better use a PreparedStatement. See this.

kind regards,

Jos

[mr_anderson]

Thank you guys very very much
Norm do you mean to put it like this?

statement.executeUpdate("update NODES set value1="+var+", value2='110' where index='30'");

JosAH can you please check the link again.

By the way the value of the variable var is taken from a JTextField:

JTextField textIn=new JTextField(10);
String var=textIn.getText();

Thank you gain guys
Regards.
Anderson.

[JosAH]

JosAH can you please check the link again.

That link works fine for me and shows and xkcd cartoon: it shows one of the finest examples of a sql injection attack. Read all about the PreparedStatement API documentation.

kind regards,

Jos

[Norm]

Not knowing anything about databases, I understood the OPs problem to be how to build a String with the variable contents vs the name of the variable.
I can understand that an unedited String inserted into a command String could have undesired consequences. Its up to the OP to verify the contents of var.

[JosAH]

Not knowing anything about databases, I understood the OPs problem to be how to build a String with the variable contents vs the name of the variable.
I can understand that an unedited String inserted into a command String could have undesired consequences. Its up to the OP to verify the contents of var.

That's exactly what a PreparedStatement does for you (and better) because it takes the structure of the sql query in account and sets a parameter to the String value you supply; sql injection is powerless against that.

kind regards,

Jos

[Norm]

Sorry, you're not going to educate me on databases. It just runs off like water off a duck's back.

[travishein]

It has been so long since I have used Statement; I always use PreparedStatement when I do JDBC'ing.

To help out the OP in the quest for building better data accessors, Below is a complete example of using prepared statements for this update query, as well as the 'proper' handling of closing of the connection and statement objects after use.

Connection con = null;
PreparedStatement stmt = null;
try {
  con = dataSource.getConnection(); // or how ever else you get a connection object.
  
  // the first difference when using prepared statements is to specify the query string with con.prepareStatement().
  // note, one string, we use ? as place holders where variables will go.
  stmt = con.prepareStatement("update NODES set value1=?, value2=? where index=?");
  
  // now its important to bind the variables to the ? place holders in the query string above.
  // for some reason, jdbc uses 1-based indexes.
  stmt.setInt(1, 101); // see API docs, setString, setDate, set(etc)
  stmt.setInt(2, 110);
  stmt.setInt(3, 30);
  
  // now we may execute the statement, what we are used to do, note no query string here, as we did this above
  stmt.executeUpdate();
}
catch (SQLException ex) {
  // error handling here
}
finally {
  // close the statement handle now, faster than waiting for garbage collection to get around to it,
  // and better if working in connection pooled environments
  if (stmt != null) {
    try {
      stmt.close();
    } catch (SQLException ex) {
      // ignore
    }
  }
  // close the connection, required for connection pool environments
  if (con != null) {
    try {
      con.close();
    }  catch (SQLException ex) {
      // ignore
    }
  }
}

[mr_anderson]

Thank you very much this is really well explained and easy to use example.
Best Regards.
Anderson.