Pages Navigation Menu

Coding is much easier than you think

JDBC CallableStatement Stored Procedure IN and OUT Parameter Example

JDBC CallableStatement Stored Procedure IN and OUT Parameter Example

Posted by in JDBC Tutorials | 1 comment

 
In this tutorial we will learn how to call Oracle stored procedure via JDBC CallableStatement, and how to get the returned OUT parameters from stored procedure.
 
** UPDATE: Complete JDBC tutorial now available here.
 
In order to retrieve the stored procedure returned output (via OUT parameters), we must use the below functions
 

CallableStatement.registerOutParameter(index,sqlType);
CallableStatement.getDataType(index);

 
Where the method registerOutParameter is used for registering the OUT parameter value and the method getDataType is used for retrieving the output from stored procedure.
 
Stored Procedure
 
Lets say we have a stored procedure getEMPLOYEEByUserId in Oracle database with IN and OUT parameters as shown below.
 

CREATE OR REPLACE PROCEDURE getEMPLOYEEByUserId(
	   userid IN EMPLOYEE.USER_ID%TYPE,
	   username OUT EMPLOYEE.USERNAME%TYPE,
	   createdby OUT  EMPLOYEE.CREATED_BY%TYPE) IS
BEGIN
 
  SELECT USERNAME, CREATED_BY
  INTO username, createdby
  FROM  EMPLOYEE WHERE USER_ID = userid;
 
END;
/

 
Now we shall call this stored procedure from our java program to select a record.

 
JDBC CallableStatement example for IN and OUT parameter.
 
File: JDBCCallableStatementOUTParameter.java
 

package com.simplecode.jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
 
public class JDBCCallableStatementOUTParameter
{
private static final String dbDriver = "oracle.jdbc.driver.OracleDriver";
private static String serverName = "127.0.0.1";
private static String portNumber = "1521";
private static String sid = "XE";
private static final String dbUrl = "jdbc:oracle:thin:@" + serverName + ":"
			+ portNumber + ":" + sid;
private static final String dbUser = "system";
private static final String dbPassword = "admin";
 
public static void main(String[] argc)
{
	try
	{
		callStoredProcOUTParameter();
	}
	catch (SQLException e)
	{
		System.err.println(e.getMessage());
	}
}

private static void callStoredProcOUTParameter() throws SQLException
{
	Connection dbConnection = null;
	CallableStatement callableStmt = null;

	String getByUserIdStroredProc = "{call getEMPLOYEEByUserId(?,?,?)}";

	try
	{
		dbConnection = getDBConnection();
		callableStmt = dbConnection.prepareCall(getByUserIdStroredProc);

		callableStmt.setInt(1, 1000);
		callableStmt.registerOutParameter(2, java.sql.Types.VARCHAR);
		callableStmt.registerOutParameter(3, java.sql.Types.VARCHAR);

		// execute getEMPLOYEEByUserId store procedure
		callableStmt.execute();
		
		String userName = callableStmt.getString(2);
		String createdBy = callableStmt.getString(3);

		System.out.println("UserName : " + userName);
		System.out.println("CreatedBy : " + createdBy);
	}
	
	catch (SQLException e)
	{
		System.err.println(e.getMessage());
	}
	
	finally
	{

		if (callableStmt != null)
		{
			callableStmt.close();
		}

		if (dbConnection != null)
		{
			dbConnection.close();
		}
	}
}

private static Connection getDBConnection()
{

	Connection dbConnection = null;
	try
	{
		Class.forName(dbDriver);
	}
	catch (ClassNotFoundException e)
	{
		System.err.println(e.getMessage());
	}

	try
	{
	dbConnection = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
	return dbConnection;
	}
	catch (SQLException e)
	{
		System.err.println(e.getMessage());
	}
	return dbConnection;
}
}

 

dwd2
Download It – JDBCCallableStatementOUTParameter

  Read More
JDBC CallableStatement – Stored Procedure IN parameter example

JDBC CallableStatement – Stored Procedure IN parameter example

Posted by in JDBC Tutorials

 
In this tutorial we will learn how to call Oracle stored procedure via JDBC CallableStatement, and how to pass IN parameters from Java to stored procedure.
 
Stored Procedure
 
Lets say we have a stored procedure insertEMPLOYEE in Oracle database, with IN parameters as shown below.
 

CREATE OR REPLACE PROCEDURE insertEMPLOYEE(
           userid IN EMPLOYEE.USER_ID%TYPE,
           username IN EMPLOYEE.USERNAME%TYPE,
           createdby IN EMPLOYEE.CREATED_BY%TYPE)
IS
BEGIN
  INSERT INTO EMPLOYEE ("USER_ID", "USERNAME", "CREATED_BY")
  VALUES (userid, username, createdby);
 
  COMMIT;
 
END;
/

 
Now we shall call this stored procedure from our java program to insert a user.
 
JDBC CallableStatement example for IN parameter.
 
File: JDBCCallableStatementINParameter.java
 
When below code is executed, a new record will be inserted into database via stored procedure “insertEMPLOYEE”.
 

package com.simplecode.jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
 
public class JDBCCallableStatementINParameter
{
private static final String dbDriver = "oracle.jdbc.driver.OracleDriver";
private static String serverName = "127.0.0.1";
private static String portNumber = "1521";
private static String sid = "XE";
private static final String dbUrl = "jdbc:oracle:thin:@" + serverName + ":"
			+ portNumber + ":" + sid;
private static final String dbUser = "system";
private static final String dbPassword = "admin";
 
public static void main(String[] argc)
{
	try
	{
		callStoredPackINParameter();
	}
	catch (SQLException sqlE)
	{
		System.err.println(sqlE.getErrorCode());
	}
}

private static void callStoredPackINParameter() throws SQLException
{
	Connection con = null;
	CallableStatement callableStmt = null;
	String insertStoredPack = "{call insertEMPLOYEE(?,?,?)}";

	try
	{
		con = getConnection();
		callableStmt = con.prepareCall(insertStoredPack);
		callableStmt.setInt(1, 100);
		callableStmt.setString(2, "Franklin");
		callableStmt.setString(3, "Admin");

		// execute insertEMPLOYEE store procedure
		callableStmt.executeUpdate();
		System.out.println("Inserted records into EMPLOYEE table");
	}
	
	catch (SQLException sqlE)
	{
		System.err.println(sqlE.getErrorCode());
	}
	finally
	{
		if (callableStmt != null)
		{
			callableStmt.close();
		}

		if (con != null)
		{
			con.close();
		}
	}
}

private static Connection getConnection()
{

	Connection con = null;
	try
	{
		Class.forName(dbDriver);
	}
	catch (ClassNotFoundException cnf)
	{
		System.err.println(cnf.getStackTrace());
	}

	try
	{
	con = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
	return con;
	}
	catch (SQLException sqlE)
	{
		System.err.println(sqlE.getErrorCode());
	}
	return con;
}
}

 

dwd2
Download It – JDBCCallableStatementINParameter

  Read More
JDBC CallableStatement

JDBC CallableStatement

Posted by in JDBC Tutorials | 2 comments

 
JDBC CallableStatement provides a way to call the stored procedure of the database. These procedures stored in the database, which may increase the performance of some database operations, since it gets executed inside the database server.
 
The Stored procedures possess the following features:
1) They can have input and output parameters.
2) They can have a return value.
3) They have the ability to return multiple ResultSets.
 
In JDBC, a stored procedure call is a single call to the database, but it may process several database requests. The stored procedure may also perform several other programmatic tasks not typically done with SQL statements.
 
Handling parameters
 
In general a CallableStatement objects may take three types of parameters:
 
IN, OUT, INOUT
 
In the upcoming tutorial we will learn about calling a Oracle stored procedure via JDBC CallableStatement.
 

Read More

JDBC Statement example to Drop a table

Posted by in JDBC Tutorials

drop table
 
Here is an example to show you how to drop a table from database via JDBC statement.
 
To issue a drop statement, calls the Statement.executeUpdate() method as shown below :
 

Statement statement = connection.createStatement();
// execute delete  SQL stetement
statement.executeUpdate(dropTableQuery);

 
Full example…
File: JDBCStatementDropExample.java
 

package com.simplecode.jdbc;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.SQLException;

public class JDBCStatementDropExample
{

private static final String dbDriver = "oracle.jdbc.driver.OracleDriver";
private static String serverName = "127.0.0.1";
private static String portNumber = "1521";
private static String sid = "XE";
private static final String dbUrl ="jdbc:oracle:thin:@"+serverName+":"+ portNumber+":"+sid;
private static final String dbUser = "system";
private static final String dbPassword = "admin";


	public static void main(String[] argc)
	{
		try
		{
			dropTable();
		}
		catch (SQLException e)
		{
			System.err.println(e.getMessage());
		}
	}

	private static void dropTable() throws SQLException
	{
	Connection connection = null;
	Statement statement = null;

	String dropTableQuery = "DROP TABLE EMPLOYEE";

		try
		{
			connection = getDBConnection();
			statement = connection.createStatement();

			// execute delete SQL statement
			statement.executeUpdate(dropTableQuery);
			System.out.println("EMPLOYEE table is deleted from database!");

		}
		catch (SQLException e)
		{
			System.err.println(e.getMessage());

		}
		finally
		{
			if (statement != null)
			{
				statement.close();
			}

			if (connection != null)
			{
				connection.close();
			}
		}
	}

	private static Connection getDBConnection()
	{
		Connection dbConnection = null;
		try
		{
			Class.forName(dbDriver);
		}
		catch (ClassNotFoundException e)
		{
			System.err.println(e.getMessage());
		}

		try
		{
		dbConnection = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
		return dbConnection;
		}
		catch (SQLException e)
		{
			System.err.println(e.getMessage());
		}

	return dbConnection;
	}
}

 
download

Read More
JDBC PreparedStatement example to Update a Record

JDBC PreparedStatement example to Update a Record

Posted by in JDBC Tutorials

 
In this example let us learn how to update a record in table via JDBC PreparedStatement.
 
To issue a update statement, calls the PreparedStatement.executeUpdate() method as shown below :
 

String updateTableQuery = "UPDATE EMPLOYEE SET USERNAME = ? WHERE USER_ID = ?";
connection = getDBConnection();
prepareStmt = connection.prepareStatement(updateTableQuery);
prepareStmt.setString(1, "Nilafar Nisha");
prepareStmt.setInt(2, 1000);
 
// execute update SQL statement
prepareStmt.executeUpdate();

 
Full example.
 

package com.simplecode.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class JDBCPreparedStmtUpdateExample
{
private static final String dbDriver = "oracle.jdbc.driver.OracleDriver";
private static String serverName = "127.0.0.1";
private static String portNumber = "1521";
private static String sid = "XE";
private static final String dbUrl = "jdbc:oracle:thin:@" + serverName + ":"
		+ portNumber + ":" + sid;
private static final String dbUser = "system";
private static final String dbPassword = "admin";

public static void main(String[] argc)
{
	try
	{
		insertRecordIntoTable();
	}
	catch (SQLException e)
	{
		System.out.println(e.getMessage());
	}
}

private static void insertRecordIntoTable() throws SQLException
{
	Connection connection = null;
	PreparedStatement prepareStmt = null;
	String updateTableQuery = "UPDATE EMPLOYEE SET USERNAME = ? WHERE USER_ID = ?";

	try
	{
		connection = getDBConnection();
		prepareStmt = connection.prepareStatement(updateTableQuery);

		prepareStmt.setString(1, "Nilafar Nisha");
		prepareStmt.setInt(2, 1000);
		// execute update SQL statement
		prepareStmt.executeUpdate();
		System.out.println("Record is updated to EMPLOYEE table!");
	}
	catch (SQLException e)
	{
		System.err.println(e.getMessage());
	}
		
	finally
	{
		if (prepareStmt != null)
		{
			prepareStmt.close();
		}
		if (connection != null)
		{
			connection.close();
		}
		}
}

private static Connection getDBConnection()
{
	Connection dbConnection = null;
	try
	{
		Class.forName(dbDriver);
	}
	catch (ClassNotFoundException e)
	{
		System.err.println(e.getMessage());
	}

	try
	{
	dbConnection = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
	return dbConnection;
	}
	catch (SQLException e)
	{
		System.err.println("Db "+e.getMessage());
	}
	return dbConnection;
}
}

 

Read More
JDBC PreparedStatement example to Select list of the records

JDBC PreparedStatement example to Select list of the records

Posted by in JDBC Tutorials

 
In this example let us learn how to select records from table via JDBC PreparedStatement, and display the records via a ResultSet object.
 
To issue a select query, call the PreparedStatement.executeQuery() method like this :
 

String selectQuery = "SELECT USER_ID, USERNAME FROM EMPLOYEE WHERE USER_ID = ?";
PreparedStatement preparedStatement = connection.prepareStatement(selectSQL);
preparedStatement.setInt(1, 1000);
ResultSet rs = preparedStatement.executeQuery(selectQuery);
while (rs.next())
{
	String userid = rs.getString("USER_ID");
	String username = rs.getString("USERNAME");
	String createdBy = rs.getString("CREATED_BY");
}

 
Full example…
 

package com.simplecode.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JDBCPreparedStmtSelectExample
{
private static final String dbDriver = "oracle.jdbc.driver.OracleDriver";
private static String serverName = "127.0.0.1";
private static String portNumber = "1521";
private static String sid = "XE";
private static final String dbUrl = "jdbc:oracle:thin:@" + serverName + ":"
		+ portNumber + ":" + sid;
private static final String dbUser = "system";
private static final String dbPassword = "admin";

public static void main(String[] argc)
{
	try
	{
		selectRecordsFromTable();
	}
	catch (SQLException e)
	{
		System.out.println(e.getMessage());
	}
}

private static void selectRecordsFromTable() throws SQLException
{
	Connection connection = null;
	PreparedStatement prepareStmt = null;

	String selectQuery = "SELECT USER_ID, USERNAME FROM EMPLOYEE WHERE USER_ID = ?";

try
{
	connection = getDBConnection();
	prepareStmt = connection.prepareStatement(selectQuery);
	prepareStmt.setInt(1, 1001);
	// execute select SQL stetement
	ResultSet rs = prepareStmt.executeQuery();

	while (rs.next())
	{
		String userid = rs.getString("USER_ID");
		String username = rs.getString("USERNAME");
		System.out.println("userid : " + userid);
		System.out.println("username : " + username);
       }
}
catch (SQLException e)
{
	System.err.println(e.getMessage());
}
	
finally
{
	if (prepareStmt != null)
	{
		prepareStmt.close();
	}
	if (connection != null)
	{
		connection.close();
	}
	}
}

private static Connection getDBConnection()
{
	Connection dbConnection = null;
	try
	{
		Class.forName(dbDriver);
	}
	
	catch (ClassNotFoundException e)
	{
		System.err.println(e.getMessage());
	}

	try
	{
	dbConnection = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
	return dbConnection;

	}
	catch (SQLException e)
	{
		System.err.println("Db "+e.getMessage());
	}

	return dbConnection;
}
}

 

Read More
Page 1 of 41234