Prepared statement in Java

Question

I have started to learn how to write statements in Java. I have seen on Stack Overflow prepared statements that have values, but I was wondering how I could write something like my statement to become a prepared statement. This is my code:

public void put(MySQLDatabase mysqlDB) {
    if (mysqlDB != null) {
      boolean bool = mysqlDB.setMySqLData("UPDATE Person SET personName = '" +
        personName +
        "', personDescr = '" + personDescr + "', personAge = " + personAge + "
        WHERE personId = " + personId);
    }
}

public void post(MySQLDatabase mysqlDB) {
  if (mysqlDB != null) {
    boolean bool = mysqlDB.setMySqLData("INSERT INTO person VALUES(" + personId +
      ", '" +
      personName + "', '" + personDescr + "', " + personAge +
      ")");
  }
}

public void delete(MySQLDatabase mysqlDB) {
  if (mysqlDB != null) {
    boolean bool = mysqlDB.setMySqLData("DELETE FROM person WHERE personId=" +
      personId);
  }
}

I don't know if it is possible to do it like this and I would like to know if I would have any benefits doing a prepared statement for these queries.

Sorry, this is my first post and i dont know how to indent it. In netbeans it is indented. — VasilijZ, Feb 12 '18 at 22:54
Have a look at this [meta post](https://meta.stackexchange.com/questions/22186/how-do-i-format-my-code-blocks) on how to format code blocks. — Micheal O'Dwyer, Feb 12 '18 at 22:55
@Phil thanks for help, and i looked at your link but i havent found querry like mine, i dont have here any values to insert. — VasilijZ, Feb 12 '18 at 23:06
@VasilijZ every single one of your queries has values supplied. Each of `personName`, `personDescr`, `personAge` and `personId` are _values_ — Phil, Feb 12 '18 at 23:13

score 3 · Answer 1 · answered Feb 12 '18 at 23:06

    // Creates a new connection
    Connection conn = DriverManager.getConnection(connectionUrl, user, password);

    // Prepares a PreparedStatement
    PreparedStatement prep = conn.prepareStatement("UPDATE Person SET personName = ?" +
            ", personDescr = ?, personAge = ? WHERE personId = ?");

    // Populate the statement with its parameters
    prep.setString(1, personName);
    prep.setString(2, personDescr);
    prep.setInt(3, personAge);
    prep.setLong(4, personId);

    // Execute the updates
    int numberOfRowsUpdated = prep.executeUpdate();

This requires that you use DriverManager.getConnection which requires you to register the MySQL Driver. This may be better than using MySQL Directly in terms of OOP, where you must depend on most abstraction (Depend on the interface, not specific implementation)

If you make 4 standard statements, and then just modify them in each method you are going to use them I think that would be a perfect answer. — LatheElHafy, Feb 12 '18 at 23:28

score 0 · Answer 2 · answered Feb 12 '18 at 22:58

0

I will leave an example here and the documentation so you can start, it is pretty simple, just use "?" to add placeholders and the proper methods to complete with indexes starting from 1

PreparedStatement pstmt = con.prepareStatement("UPDATE EMPLOYEES
                                     SET SALARY = ? WHERE ID = ?");
   pstmt.setBigDecimal(1, 153833.00)
   pstmt.setInt(2, 110592)

https://docs.oracle.com/javase/8/docs/api/java/sql/PreparedStatement.html

answered Feb 12 '18 at 22:58

Victor

3,520
3
38
58

If i have values i can use ? instead of querry, but if i dont have values to insert (like pstmt.setBigDecimal(1, 153833.00)) is it smart to use them? Is my code any faster if i use them, or should i just make statement like i did. – VasilijZ Feb 12 '18 at 23:04
better not to use values directly, as a rule of thumb, use ? and methods. This is even a cyber-security practice as well. It assures type safety and simplifies your logic, reducing even the need for extra sanitation. – Victor Feb 12 '18 at 23:07
Your code will likely not even blink in performance and assembling this query is extremely fast when compared to its execution. it would be silly to try squeeze performance that way, if you on that route you will and up storing parametized queries in the database itself, without real improvement for such simple query. – Victor Feb 12 '18 at 23:11
@VasilijZ nevermind performance: your code is vulnerable to [SQL injection](https://en.wikipedia.org/wiki/SQL_injection) (well, if your input comes from users) – giorgiga Feb 12 '18 at 23:17
exactly, changing to prepared statements is a win win here. – Victor Feb 12 '18 at 23:19
Thank you all for help, and now it is more clear how and why to use them. – VasilijZ Feb 13 '18 at 00:11
Greet and welcome to SO. Remember to accept an answer when satisfied. – Victor Feb 13 '18 at 00:49

Prepared statement in Java

2 Answers2