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, UltraDev lets you create prepared statements without writing a single line of Java code.

The following JSP code 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.

Note: You must use different methods to assign non-string values to SQL variables. For example, to assign an integer to the variable, you would use the mystatement.setInt() method.

Finally, you generate the recordset, as follows:

ResultSet myresults = mystatement.execute();

The rest of this article describes how to create JSP prepared statements using the rapid application development (RAD) tools in UltraDev. These tools let you create prepared statements without writing a line of JSP code.