Using JDBC to Connect to MySQL Part 3

There is another kind of statement which comes in handy when you are issuing an INSERT or UPDATE query. This is the PreparedStatement. The purpose of this object is to handle any characters which would need escaping. For instance, in our UPDATE statement above, we could store our name and status values in variables and create our query like this.

String status = “writing articles”;
String name = “Bryan Young”;
PreparedStatement pStat = mysqlConn.prepareStatement(“UPDATE writers SET status = ? WHERE name = ?”);
pStat.setString(1, status);
pStat.setString(2, name);
pStat.executeUpdate();
pStat.close();

The ? serves as a placeholder for the values you add later. setString() takes its first argument as saying which placeholder it will replace (again, start counting at 1!), and the second argument is the variable it inserts there. Both executeQuery() and executeUpdate() will throw a SQLException in the case of an incorrect query. This exception contains the error codes you would get from a MySQL client program.

try {
	// insert code here
} catch (SQLException e) {
	System.err.println(“Error code: “ + e.getErrorCode());
	System.err.println(“Error message: “ + e.getMessage());
}

This feedback is especially important when you are creating a program that accepts user input as part of the query building process. A basic example of this would be writing your own MySQL client. Having the exact error message from the database to display out to the user means that they have the information they need to fix errors in their input.

Now that you are finished accessing your database, you simply need to clean up and you’re finished! Close your Statement object.

stat.close();

Now close your database connection.

mysqlconn.close();

In part 4 of this series, we will create a class to incorporate all the aspects of database connection we have covered.