What Are Dynamic Parameters? JDBC

You can prepare SQL queries and statements that are allowed to have dynamic parameters for which the value is not specified when the statement or query is prepared by using PreparedStatement methods in the JDBC API. These parameters, known as dynamic parameters, are represented by a ?. The JDBC refers to dynamic parameters as IN (input), INOUT (input and output), or OUT (output) parameters. In SQL, they are always IN (input) parameters. Before you execute queries by PreparedStatement object, you must specify values for all input parameters. The values specified must match the types expected.

Also, dynamic parameters can be used with CallableStatement (this interface extends the PreparedStatement interface) objects (which represents a stored procedure or function in a database server). A CallableStatement object may have any number of IN (input), INOUT(input and output), or OUT (output) parameters.

Next, I provide a dynamic parameter example.

MySQL Database Setup

mysql> use octopus;
Database changed
mysql> desc employees;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | varchar(8) | | PRI | | |
| name | varchar(16) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> select * from employees;
+-----+--------+------+
| id | name | age |
+-----+--------+------+
| 88 | Peter | 80 |
| 77 | Donald | 70 |
| 33 | Mary | 30 |
| 44 | Monica | 40 |
| 999 | Andre | 90 |
+-----+--------+------+
5 rows in set (0.00 sec)

Using Dynamic Parameters

import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.Connection;
import jcb.util.DatabaseUtil;
import jcb.db.VeryBasicConnectionManager;
public class DemoDynamicParams {
public static void main(String[] args) throws Exception {
ResultSet rs = null;
Connection conn = null;
PreparedStatement ps = null;
// the following SQL query has two dynamic input parameters:
// first parameter is for "id" column and second one is for "age" column
String query =
"select id, name, age from employees where id < ? and age > ?";
String dbVendor = args[0];
try {
// get a valid connection object
conn = VeryBasicConnectionManager.getConnection(dbVendor);
// prepare a SQL statement, which can have parameters; note that
// a PreparedStatement object may be used any number of times
ps = conn.prepareStatement(query);
// specify values for all input parameters
ps.setInt(1, 100); // set the first dynamic parameter: id
ps.setInt(2, 30); // set the second dynamic parameter: age
// now, PreparedStatement object is ready to be executed.
rs = ps.executeQuery();
// iterate the result set object
displayResultSet(rs);
// NOTE: you may use PreparedStatement as many times as you want
// here we use it for another set of parameters:
ps.setInt(1, 110); // set the first dynamic parameter: id
ps.setInt(2, 70); // set the second dynamic parameter: age
// now, PreparedStatement object is ready to be executed.
rs = ps.executeQuery();
// iterate the result set object
displayResultSet(rs);
}
finally {
// close resources: ResultSet, PreparedStatement, Connection
DatabaseUtil.close(rs);
DatabaseUtil.close(ps);
DatabaseUtil.close(conn);
}
}
public static void displayResultSet(ResultSet rs) throws Exception {
if (rs == null) {
return;
}
while (rs.next()) {
int id = rs.getInt(1);
String name = rs.getString(2);
int age = rs.getInt(3);
System.out.println("[id="+id+"][name="+name+"][age="+age+"]");
}
System.out.println("-------------");
}
}

Running the Solution for a MySQL Database

$ javac DemoDynamicParams.java
$ java DemoDynamicParams mysql
[id=88][name=Peter][age=80]
[id=77][name=Donald][age=70]
[id=44][name=Monica][age=40]
-------------
[id=88][name=Peter][age=80]
-------------

Oracle Database Setup

SQL> desc employees;
Name Null? Type
--------------------- -------- ------------
ID NOT NULL VARCHAR2(10)
NAME NOT NULL VARCHAR2(20)
AGE NUMBER(38)
SQL> select * from employees;
ID NAME AGE
---------- -------------------- ----------
11 Alex Smith 25
22 Don Knuth 65
33 Mary Kent 35
44 Monica Seles 30
99 Alex Edison
100 Al Sumner 120
105 Al Sumner 90
7 rows selected.

Running the Solution for an Oracle Database

$ java DemoDynamicParams oracle
[id=22][name=Don Knuth][age=65]
[id=33][name=Mary Kent][age=35]
-------------
[id=100][name=Al Sumner][age=120]
[id=105][name=Al Sumner][age=90]
-------------

For details on dynamic parameters, you should refer to your database vendor’s JDBC and SQL documentations (each vendor might have some restrictions or additions for their dynamic parameters). In general, you can use dynamic parameters anywhere in an expression where their data type can be easily deduced.



Face Book Twitter Google Plus Instagram Youtube Linkedin Myspace Pinterest Soundcloud Wikipedia

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

JDBC Topics