Java
JDBC
The JDBC API has a class called PreparedStatement, which allows the programmer to safely insert user-supplied data into a SQL query. The location of each input value in the query string is marked with a question mark. The various set*() methods are then used to safely perform the insertion.
String name = //user input
int age = //user input
Connection connection = DriverManager.getConnection(...);
PreparedStatement statement = connection.prepareStatement("SELECT * FROM people WHERE lastName = ? AND age > ?");
statement.setString(1, name); //lastName is a VARCHAR
statement.setInt(2, age); //age is an INT
ResultSet rs = statement.executeQuery();
while (rs.next()){
//...
}
Once a PreparedStatement object has been created, it can be reused multiple times for multiple queries (for example, when using the same query to update multiple rows in a table). However, they are not thread-safe because of the many method calls involved in setting the parameters and executing the query. Therefore, you should only define PreparedStatement objects as method-level variables (as opposed to class-level variables) to avoid concurrency issues.
List<Person> people = //user input
Connection connection = DriverManager.getConnection(...);
connection.setAutoCommit(false);
try {
PreparedStatement statement = connection.prepareStatement("UPDATE people SET lastName = ?, age = ? WHERE id = ?");
for (Person person : people){
statement.setString(1, person.getLastName());
statement.setInt(2, person.getAge());
statement.setInt(3, person.getId());
statement.execute();
}
connection.commit();
} catch (SQLException e) {
connection.rollback();
}
More information on PreparedStatement can be found in the Oracle JDBC tutorial.
Hibernate
Hibernate also supports prepared statements. See the Hibernate Manual for more information.
MyBatis
TODO
