Here's a guide about when and how to use prepared statements.
The first reason to use a prepared statement is to protect against SQL injections.
You can do this by removing all special characters, but there are times when that's not practical. (Example: you want to allow users who have apostrophes in their names to register for your website.) You can do it by escaping all of the strings that you pass into the SQL query, but it's easy to forget to sanitize a single field.
So how do you protect against SQL injection? Here is the simple way to never fall prey to SQL injection: never put a user-provided string into an SQL query, except as a parameter to a prepared statement.
The second, less important, reason is that it's better for performance. If you build a new SQL statement for every query that comes in, the SQL server needs to parse the query every single time. However, if you re-use the same PreparedStatement over and over, the SQL server doesn't need to do that.
In this example, I'll be querying a table called ducks
to find a duck named "Donald".
Step 1:
Make a connection. This is the same as a regular Statement.
Connection conn = DriverManager.getConnection(myUrl, user, pass);
Step 2:
Create a PreparedStatement.
String sql = "SELECT * FROM ducks WHERE name = ?;";
PreparedStatment statement = conn.prepareStatement(sql);
The ? is where our String is going to go.
Step 3:
Set the parameter.
String nameToSearchFor = "Donald";
statement.setString(1, nameToSearchFor);
The first argument to setString
is the position of the argument that you are setting. Note that the parameters are 1-indexed, not 0-indexed.
Step 4:
Run the query.
ResultSet result = statement.executeQuery();
Once you have the ResultSet, the rest is exactly the same as when you're using a regular Statement.