Executing SQL Statements through Statement Objects

After loading the Advanced Server JDBC Connector driver and connecting to the server, the code in the sample application builds a JDBC Statement object, executes an SQL query, and displays the results.

A Statement object sends SQL statements to a database. There are three kinds of Statement objects. Each is specialized to send a particular type of SQL statement:

  • A Statement object is used to execute a simple SQL statement with no parameters.

  • A PreparedStatement object is used to execute a pre-compiled SQL statement with or without IN parameters.

  • A CallableStatement object is used to execute a call to a database stored procedure.

You must construct a Statement object before executing an SQL statement. The Statement object offers a way to send a SQL statement to the server (and gain access to the result set). Each Statement object belongs to a Connection; use the createStatement() method to ask the Connection to create the Statement object.

A Statement object defines several methods to execute different types of SQL statements. In the sample application, the executeQuery() method executes a SELECT statement:

Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM emp");

The executeQuery() method expects a single argument: the SQL statement that you want to execute. executeQuery() returns data from the query in a ResultSet object. If the server encounters an error while executing the SQL statement provided, it throws an SQLException (and does not return a ResultSet).

Using Named Notation with a CallableStatement Object

The JDBC Connector (Advanced Server version 9.6 and later) supports the use of named parameters when instantiating a CallableStatement object. This syntax is an extension of JDBC supported syntax, and does not conform to the JDBC standard.

You can use a CallableStatement object to pass parameter values to a stored procedure. You can assign values to IN, OUT, and INOUT parameters with a CallableStatement object.

When using the CallableStatement class, you can use ordinal notation or named notation to specify values for an actual arguments. You must set a value for each IN or INOUT parameter marker in a statement.

When using ordinal notation to pass values to a CallableStatement object, you should use the setter method that corresponds to the parameter type. For example, when passing a STRING value, use the setString setter method. Each parameter marker within a statement (?) represents an ordinal value. When using ordinal parameters, you should pass the actual parameter values to the statement in the order that the formal arguments are specified within the procedure definition.

You can also use named parameter notation when specifying argument values for a CallableStatement object. Named parameter notation allows you to supply values for only those parameters that are required by the procedure, omitting any parameters that have acceptable default values. You can also specify named parameters in any order.

When using named notation, each parameter name should correspond to a COLUMN_NAME returned by a call to the DatabaseMetaData.getProcedureColumns method. You should use the => token when including a named parameter in a statement call.

Use the registerOutParameter method to identify each OUT or INOUT parameter marker in the statement.

Examples

The following examples demonstrate using the CallableStatement method to provide parameters to a procedure with the following signature:

PROCEDURE hire_emp (ename VARCHAR2
empno NUMBER
job VARCHAR2
sal NUMBER
hiredate DATE DEFAULT now(),
mgr NUMBER DEFAULT 7100,
deptno NUMBER
)

The following example uses ordinal notation to provide parameters:

CallableStatement cstmt = con.prepareCall
("{CALL hire_emp(?,?,?,?,?,?,?)}");
//Bind a value to each parameter.
cstmt.setString(1, "SMITH");
cstmt.setInt(2, 8888);
cstmt.setString(3, "Sales");
cstmt.setInt(4, 5500);
cstmt.setDate(5, "2016-06-01");
cstmt.setInt(6, 7566);
cstmt.setInt(7, 30);

The following example uses named notation to provide parameters; using named notation, you can omit parameters that have default values or re-order parameters:

CallableStatement cstmt = con.prepareCall
("{CALL hire_emp(ename => ?,
job => ?,
empno => ?,
sal => ?,
deptno => ?
)}");

//Bind a value to each parameter.
cstmt.setString("ename", "SMITH");
cstmt.setInt("empno", 8888);
cstmt.setString("job", "Sales");
cstmt.setInt("sal", 5500);
cstmt.setInt("deptno", 30);