Executing SQL statements through statement objects v42.5.4.2

After loading the EDB Postgres Advanced Server JDBC Connector driver and connecting to the server, the code in the sample application builds a JDBC Statement object, executes a 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 precompiled 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 a 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 encountered an error while executing the SQL statement provided, it returns an SQLException and doesn't return a ResultSet.

Using named notation with a CallableStatement object

The JDBC Connector (EDB Postgres Advanced Server version 10 and later) supports the use of named parameters when instantiating a CallableStatement object. This syntax is an extension of JDBC supported syntax and doesn't 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 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, 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 in a statement (?) represents an ordinal value. When using ordinal parameters, pass the actual parameter values to the statement in the order that the formal arguments are specified in 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 must correspond to a COLUMN_NAME returned by a call to the DatabaseMetaData.getProcedureColumns method. 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 show using the CallableStatement method to provide parameters to a procedure with the following signature:

CREATE OR REPLACE PROCEDURE hire_emp (ename VARCHAR2
empno NUMBER,
job VARCHAR2,
sal NUMBER,
hiredate DATE DEFAULT now(),
mgr NUMBER DEFAULT 7100,
deptno NUMBER
)
IS
	BEGIN
	    INSERT INTO emp VALUES (empno, ename, job, mgr, hiredate, sal, deptno);
	END;

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, Date.valueOf("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 reorder 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);