Posts

Showing posts with the label JDBC Pool Resource

Call Oracle Stored Procedure using JDBC CallableStatement

Calling stored procedure is same as calling stored function as mentioned in previous post. In this example we will perform INSERT, UPDATE, DELETE on Account table. Code snippets to show you how to call a Oracle stored procedure via JDBC   CallableStatement , and how to pass IN parameters from Java to stored procedure. 1) Stored Procedure A stored procedure in Oracle database. Later, calls it via JDBC. create or replace procedure spAccount(p_acode number,p_aname varchar, p_obalance number,p_descflag varchar) IS begin --INSERTING METHOD IF p_descflag = 'I' THEN insert into account values (p_acode,p_aname,p_obalance); END IF; --UPDATING METHOD IF p_descflag = 'U' THEN update account aa set aa.acct_name = p_aname, aa.obalance = p_obalance where aa.acct_code = p_acode; END IF; --UPDATING METHOD IF p_descflag = 'D' THEN delete from account aa where aa.acct_code = p_acode; END IF; commit; end spAccount; ...

Call Oracle Stored Function using CallableStatement through Oracle JDBC Pool Recource

The Oracle PL/SQL language allows you to write functions and procedure to centralize the business logic and store the code in the database. This business logic may be call in many different ways including from a Glassfish Server's JDBC Pool Connection Resource. There are five steps involved in calling a PL/SQL Function from within a application: Create and prepare a JDBC CallableStatement object that contains a call to your PL/SQL function. Register the output parameter for your PL/SQL function. Provide all of the required parameter values to your PL/SQL function. Call the execute() method for your CallableStatement object, which then performs the call to your PL/SQL procedure. Read the returned value from your PL/SQL function.

Retrive Records through PreparedStatement using Oracle JDBC Pool Resource

Image
In this post i will show you to how to select data from database through PreparedStatement using Oracle JDBC Pool Resource. In this example we are selecting all records from Users table and using WHILE LOOP shows all records into browser. 1) Create JSP page using Netbean IDE. 2) Add following classes in your JSP page. <%@page import="java.sql.ResultSet"%> <%@page import="java.sql.PreparedStatement"% > <%@page import="java.sql.SQLException"%& > <%@page import="java.sql.Connection"% > <%@page import="javax.sql.DataSource"% > <%@page import="javax.naming.InitialContext"%> 3) Add the following code in body tag. This code contain JDBC Pool Resource connection, plain SQL Statement and PreparedStatement. <% InitialContext ctx = new InitialContext(); DataSource ODS = (DataSource) ctx.lookup("jdbc/__oracon"); Connection CN = ODS.getConnection(); if (CN == null) { throw new SQLExcepti...

How to use JDBC Connection Pool Resource in Java Application

Image
In the previous post we created a JDBC Pool Resource and now in this post i will show you that how to use JDBC connection pool resource your Java Application. 1) Import following Classes in your application. import java.sql.Connection; import javax.sql.DataSource; import javax.naming.InitialContext; 2) Create a JDBCconnection Class as mentioned public class JDBCconnection(){ private DataSource ODS; private Connection CN; public JDBCconnection(){ try { InitialContext ctx = new InitialContext(); /*jdbc/__oracon is a pooled resource name*/ ODS = (javax.sql.DataSource) ctx.lookup("jdbc/__oracon"); CN = ODS.getConnection(); if (CN== null){ System.out.println("Error occur while establishing connection"); } } catch (Exception e){ System.out.println("Oops! " + e.getMessage()); } } } Now using the Connection CN you can call both PreparedStatement Or CallableStatement to write SQL statement directly in your code or call Stored Function, Procedures etc.