JSP prepared statements

A JSP prepared statement is a reusable server object that contains a SQL statement. You can place any valid SQL statement in a prepared statement. For example, a prepared statement can contain a SQL statement that returns a recordset, or one that inserts, updates, or deletes records in a database.

A prepared statement is reusable in the sense that the application server uses a single instance of the prepared statement object to query the database a number of times. Unlike the JSP statement object, a new instance of the prepared statement object is not created for each new database query. If you know the statement will be executed more than a few times, having a single instance of the object can make database operations more efficient and take up less server memory.

A prepared statement object is created by a Java scriptlet on a JSP page. However, Dreamweaver lets you create prepared statements without writing a single line of Java code.

If you’re interested in the code, the following scriptlet creates a prepared statement:

String myquery = "SELECT * FROM EMPLOYEES WHERE DEPARTMENT = ?";
PreparedStatement mystatement = connection.prepareStatement(myquery);

The first line stores the SQL statement in a string variable called myquery, with a question mark serving as a placeholder for the SQL variable value. The second line creates a prepared statement object called mystatement.

Next, you assign a value to the SQL variable, as follows:

mystatement.setString(1, request.getParameter("myURLparam"));

The setString method assigns the value to the variable and takes two arguments. The first argument specifies the affected variable by its position (here, the first position in the SQL statement). The second argument specifies the variable’s value. In this example, the value is provided by a URL parameter passed to the page.

Finally, you generate the recordset, as follows:

ResultSet myresults = mystatement.execute();

For more information on how to create JSP prepared statements using rapid application development (RAD) tools in Dreamweaver, see Using JSP prepared statements to modify a database.