JDBC Prepared Statements

A Prepared Statement forces a SQL statement to be set to the database immediately where it will be precompiled. This means that for subsequent calls, the SQL statement can just run the Prepared Statements’ SQL statement without having to compile it with every call. A Prepared Statement is most useful when using a SQL statement that accepts parameters that may change with every call. I’ll demonstrate this technique using a MySQL database.

Below is a method that sends a Prepared Statement to the database with two parameters: “userId” and “password”. Notice that it calls the getConnection () method from a class instance called dataUtil. We’ll talk about this class in a second for completeness.


    public ResultSet viewItem (int journalId, int userId) throws SQLException {
        ResultSet rs = null;
        String sql = "SELECT journal_id, date_added, journal_text FROM journal" +
                                                                 "WHERE journal_id = ? AND user_id = ?";

        Connection conn = dataUtil.getConnection();

        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setInt(1, journalId);
        preparedStatement.setInt(2, userId);

        rs = preparedStatement.executeQuery();

        return rs;
    }


As I mentioned above, I wanted to show you how to use the getConnection method, because after all, if you can’t connect to a database you can’t execute a Prepared Statement. It will return an open Connection object for our Prepared Statement to use. The SERVER and DATABASE constants you would replace with your server address and database name, and the USER and PASS constants would obviously be your database username and password. This method requires the MySQL Connector/J JDBC driver. You can grab this at http://www.mysql.com


    public Connection getConnection() {
        try {
            Class.forName("org.gjt.mm.mysql.Driver");

            Properties connProp = new Properties();
            connProp.put ("user", USER);
            connProp.put ("password", PASS);
            connProp.put ("useUnicode", "false");

            Connection con = DriverManager.getConnection("jdbc:mysql://" + SERVER + "/" + DATABASE, connProp);
            return con;

        }catch (ClassNotFoundException cnf){
            return null;
        }catch (SQLException ex) {
            return null;
        }
    }

Notice that we first declare a variable to represent the SQL statement, and assign the SQL to it:

                    String sql = "SELECT journal_id, date_added, journal_text 

						FROM journal WHERE journal_id = ? AND user_id = ?";
The values that we will be sending to the database are represented by "?". Next we call our

getConnection () method to get our connection object and then define our Prepared Statement,

and then append our parameters to the Prepared Statement with these lines.
			preparedStatement.setInt(1, journalId);

 		preparedStatement.setInt(2, userId);
Notice that you should use the correct set method depending on the data type of the field you are

assigning the value to (see References below). All that's left to do is to execute the Prepared Statement

and to return the result set.

Reference: java.sun.com


Blogged with Flock

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s