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

Using Java Database Connectivity (JDBC) with Oracle

 

Use JDBC to access a relational database from a Java application, no matter where the application is running or where the database is. Bulusu Lakshman describes Oracle database access in Java using JDBC. Learn the details, from the querying and returning of result sets to executing DML from the Oracle 8i database. Oracle JDBC extensions are also discussed and a case study is presented to illustrate the concepts.

Microsoft Releases New JDBC Driver for SQLServer

 

Microsoft released an update to its freely distributable JDBC driver for the SQLServer database engine. Microsoft released a new version of the JDBC driver for its SQLServer database engine. The new driver provides a Type 4 JDBC interface to the latest version of SQLServer, and supports JDK releases above 1.4:

“In this release we re-architected the communication stack resulting in significant performance and scalability improvements, as well as minimizing the driver memory footprint usage, especially with multiple active connections and result sets. These enhancements are most visible when the “responseBuffering=adaptive” connection property is used. The driver now supports SQL Server SSL encryption as part of our continual commitment to security.”

Original article here.

What do you think of SQLServer as a database for enterprise Java applications?