Connect to Oracle from Java via JDBC for calling Stored Procedure

My application runs on postgre. I had spring-hibernate connection from my JSF to Postgre. But, for login I needed to connect to the mother application. My core application runs on oracle. User authentication can be found through a stored procedure. I couldn’t find anything, so that I connect to databases at a time. So, took the help of JDBC. I needed the library jar for the connection to oracle. I took the ojdbc14.jar. In the stored procedure there were 5 parameters. First 2 of the are in parameter and last 2 are out parameter including one cursor. 2 IN parameter will carry the username and password and will bring back user authentication. 3rd parameter will indicate whether it is valid user (0 for valid user), 4th parameter will carry error message(if any). And if it is valid user 4th parameter will carry user related information.

String driver = “oracle.jdbc.driver.OracleDriver”;
String url = “jdbc:oracle:thin:@127.0.0.1:1521:mydatabase”;
String dbuser = “oracle”;
String dbpass = “oracle”;

Class.forName(driver); // load Oracle driver
Connection conn = DriverManager.getConnection(url, dbuser, dbpass);

String proc3StoredProcedure = “{ call usr_login_info(?, ?, ?, ?, ?) }”;
CallableStatement cs = conn.prepareCall(proc3StoredProcedure);
cs.setString(1, userName);
cs.setString(2, password);
cs.registerOutParameter(3, java.sql.Types.DOUBLE);
cs.registerOutParameter(4, java.sql.Types.VARCHAR);
cs.registerOutParameter(5, OracleTypes.CURSOR);
cs.execute();
Double param3 = cs.getDouble(3);
String errorMessage = cs.getString(4);

User user=new User();

if (param3.equals(new Double(“0.0″)))

{

      ResultSet rs = (ResultSet) cs.getObject(5);
      while (rs.next())
      {
            user.setUserId(rs.getString(1));
            user.setDeptId(rs.getString(2));
            user.setUserName(rs.getString(3));
            user.setAddress(rs.getString(4));
      }

}

else
{
      System.out.println(“failed”);
      user.setLoginMessage(errorMessage);
}

conn.close();

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: