Executing SQL statements through statement objects v42.5.1.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 withoutIN
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:
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:
The following example uses ordinal notation to provide parameters:
The following example uses named notation to provide parameters. Using named notation, you can omit parameters that have default values or reorder parameters: