Calling a Stored Procedure in a Database

This example demonstrates how to call stored procedures with IN, OUT, and IN/OUT parameters.
CallableStatement cs;
try {
// Call a procedure with no parameters
cs = connection.prepareCall("{call myproc}");
cs.execute();

// Call a procedure with one IN parameter
cs = connection.prepareCall("{call myprocin(?)}");

// Set the value for the IN parameter
cs.setString(1, "a string");

// Execute the stored procedure
cs.execute();

// Call a procedure with one OUT parameter
cs = connection.prepareCall("{call myprocout(?)}");

// Register the type of the OUT parameter
cs.registerOutParameter(1, Types.VARCHAR);

// Execute the stored procedure and retrieve the OUT value
cs.execute();
String outParam = cs.getString(1); // OUT parameter

// Call a procedure with one IN/OUT parameter
cs = connection.prepareCall("{call myprocinout(?)}");

// Register the type of the IN/OUT parameter
cs.registerOutParameter(1, Types.VARCHAR);

// Set the value for the IN/OUT parameter
cs.setString(1, "a string");

// Execute the stored procedure and retrieve the IN/OUT value
cs.execute();
outParam = cs.getString(1); // OUT parameter
} catch (SQLException e) {
}

Connecting to a SQLServer Database

This example connects to a SQLServer database using the NetDirect JDBC driver. For information about this driver, see e232 Getting JDBC Drivers for a Database.
Connection connection = null;
try {
String driverName = "com.jnetdirect.jsql.JSQLDriver"; // NetDirect JDBC driver
String serverName = "127.0.0.1";
String portNumber = "1433";
String mydatabase = serverName + ":" + portNumber;
String url = "jdbc:JSQLConnect://" + mydatabase; // a JDBC url
String username = "username";
String password = "password";

// Load the JDBC driver
Class.forName(driverName);

// Create a connection to the database
connection = DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
// Could not find the database driver
} catch (SQLException e) {
// Could not connect to the database
}

Connecting to a MySQL Database

This example connects to a MySQL database using the MM JDBC driver for MySQL. You need to have an account in MySQL database to run this example. To create an account, you can connect to MySQL database on your platform as root, and run the following command:
mysql> GRANT ALL PRIVILEGES ON *.* TO username@localhost
IDENTIFIED BY 'password' WITH GRANT OPTION;


Connection connection = null;
try {
// Load the JDBC driver
String driverName = "org.gjt.mm.mysql.Driver"; // MySQL MM JDBC driver
Class.forName(driverName);

// Create a connection to the database
String serverName = "localhost";
String mydatabase = "mydatabase";
String url = "jdbc:mysql://" + serverName + "/" + mydatabase; // a JDBC url
String username = "username";
String password = "password";
connection = DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
// Could not find the database driver
} catch (SQLException e) {
// Could not connect to the database
}

Connecting to an Oracle Database

This example uses an Oracle JDBC driver to connect to an Oracle database instance located at 128.0.0.0:1521 with an sid called mydatabase.

Connection connection = null;
try {
// Load the JDBC driver
String driverName = "oracle.jdbc.driver.OracleDriver";
Class.forName(driverName);

// Create a connection to the database
String serverName = "127.0.0.1";
String portNumber = "1521";
String sid = "mydatabase";
String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
String username = "username";
String password = "password";
connection = DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
// Could not find the database driver
} catch (SQLException e) {
// Could not connect to the database
}

Listing All Loaded JDBC Drivers

This example lists all loaded JDBC drivers and gets information about each one.

List drivers = Collections.list(DriverManager.getDrivers());
for (int i=0; i < drivers.size(); i++) {
Driver driver = (Driver)drivers.get(i);

// Get name of driver
String name = driver.getClass().getName();

// Get version info
int majorVersion = driver.getMajorVersion();
int minorVersion = driver.getMinorVersion();
boolean isJdbcCompliant = driver.jdbcCompliant();
}

Loading a JDBC Driver

Before a connection to a database can be established, the JDBC driver for that database must be loaded. Drivers automatically register themselves with the JDBC system when loaded. There are two ways to load a JDBC driver. The first is to specify the driver or colon-separated list of drivers on the command line:
> java -Djdbc.drivers=com.company1.Driver:com.company2.Driver MyApp


The second, and recommended method, is to call Class.forName() within the code:
try {
// Load the JDBC driver
String driverName = "org.gjt.mm.mysql.Driver";
Class.forName(driverName);
} catch (ClassNotFoundException e) {
// Could not find the driver
}