What is the benefit of having JdbcRowSet implementation? Why do we need a JdbcRowSet like wrapper around ResultSet

The JdbcRowSet implementation is a wrapper around a ResultSet object that has following advantages over ResultSet

  • This implementation makes it possible to use the ResultSet object as a JavaBeans component. A JdbcRowSet can be used as a JavaBeans component in a visual Bean development environment, can be created and configured at design time and executed at run time.
  • It can be used to make a ResultSet object scrollable and updatable. All RowSet objects are by default scrollable and updatable. If the driver and database being used do not support scrolling and/or updating of result sets, an application can populate a JdbcRowSet object with the data of a ResultSet object and then operate on the JdbcRowSet object as if it were the ResultSet object.

CallableStatement Example in Java

The JDBC CallableStatement interface extends PreparedStatement and provides support for output and input/output parameters. The CallableStatement interface also has support for input parameters that is provided by the PreparedStatement interface.

The CallableStatement interface allows the use of SQL statements to call stored procedures. Stored procedures are programs that have a database interface. These programs possess the following:

  • They can have input and output parameters, or parameters that are both input and output.
  • They can have a return value.
  • They have the ability to return multiple ResultSets.

CREATE PROCEDURE GetImmediateManager

@employeeID INT,

@managerID INT OUTPUT

AS

BEGIN

SELECT @managerID = ManagerID

FROM HumanResources.Employee

WHERE EmployeeID = @employeeID

END

Conceptually in JDBC, a stored procedure call is a single call to the database, but the program associated with the stored procedure may process hundreds of database requests. The stored procedure program may also perform a number of other programmatic tasks not typically done with SQL statements.

public static void executeStoredProcedure(

Connection con) {

try {

CallableStatement cstmt = con.prepareCall(“{call dbo.GetImmediateManager(?, ?)}”);

cstmt.setInt(1, 5);

cstmt.registerOutParameter(2, java.sql.Types.INTEGER);

cstmt.execute();

System.out.println(“MANAGER ID: ” + cstmt.getInt(2));

}

catch (Exception e) {

e.printStackTrace();

}

}

Get CLOB Object Using JDBC

package com.Javastuff.jdbc;

import java.sql.*;

import java.util.*;

import java.io.*;

public class GetEmployeeDetails {

public static void main(String s[]) throws Exception {

Driver d= (Driver) ( Class.forName(

“oracle.jdbc.driver.OracleDriver”).newInstance());

Properties p=new Properties ();

p.put(“user”,”scott”);

p.put(“password”,”tiger”);

Connection con=d.connect(

“jdbc:oracle:thin:@mysys:1521:javastuff”,p);

Statement st=con.createStatement();

ResultSet rs=st.executeQuery( “select profile from empprofiles where empno=”+s[0]);

while (rs.next()) {

Reader r=rs.getCharacterStream(1);

FileWriter fw=new FileWriter(“ProfileOf”+s[0]+”.doc”);

int i=r.read();

while (i!=-1){

fw.write(i);

i=r.read();

}//while

}//while

System.out.println(“Profile retrived”);

con.close();

}//main

}//class

How to convert blob to byte array in java

ByteArrayOutputStream baos = new ByteArrayOutputStream();

byte[] buf = new byte[1024];

InputStream in = blob.getBinaryStream();

int n = 0;

while ((n=in.read(buf))>=0)

{

baos.write(buf, 0, n);

}

in.close();

byte[] bytes = baos.toByteArray();

BatchUpdate in JDBC

The JDBC drivers that support JDBC 2.0 and above support batch updates. Batch updates is a option given by the JDBC in which application developers can submit a set of SQL update statements as batch to the database.

The following methods used for creating, executing, and removing a batch of SQL updates:

  • addBatch
  • executeBatch
  • clearBatch

package com.javstuff.jdbc;

 

import java.sql.*;

import java.util.*;

import java.io.*;

public class BatchUpdateExample {

public static void main(String s[]) throws Exception {

Driver d= (Driver) ( Class.forName(

“oracle.jdbc.driver.OracleDriver”).newInstance());

Properties p=new Properties ();

p.put(“user”,”scott”);

p.put(“password”,”tiger”);

Connection con=d.connect(“jdbc:oracle:thin:@mysys:1521:javastuff”,p);

Statement st=con.createStatement();

//statement1

st.addBatch(“insert into emp(empno,sal,deptno) values(“+s[0]+”,1000,10)”);

//statement2

st.addBatch(“update emp set sal=2000 where empno=”+s[0]);

//statement3

st.addBatch(“insert into emp(empno,sal,deptno) values(202,1000,10)”);

//statement4

st.addBatch(“insert into emp(empno,sal,deptno) values(203,1000,10)”);

try {

int[] counts=st.executeBatch();

System.out.println(“Batch Executed Successfully”);

for (int i=0;i<counts.length;i++){

System.out.println(“Number of records effected by statement”+(i+1)+”: “+counts[i]);

}//for

}//try

catch(BatchUpdateException e){

System.out.println(“Batch terminated with an abnormal condition”);

int[] counts=e.getUpdateCounts();

System.out.println(“Batch terminated at statement”+ (counts.length+1));

for (int i=0;i<counts.length;i++) {

System.out.println(“Number of records effected by the statement”+ (i+1)+”: “+counts[i]);

}//for

}//catch

con.close();

}//main

}//class

Arrays in JDBC

Array, one of the SQL 99 datatypes. offers you the facility to include an ordered list of values within the column. The java.sql.Array interface to store the values of the array types. To store the Array first we need to create a User-Defined-Type Array. this can be done by creating a UDT as array in database.

package com.Javastuff.jdbc;

 

import java.sql.*;

import java.util.*;

import oracle.sql.*;

 

public class InsertEmpPassportDetails {

public static void main(String s[]) throws Exception {

Driver d= (Driver) ( Class.forName(

“oracle.jdbc.driver.OracleDriver”).newInstance());

Properties p=new Properties ();

p.put(“user”,”scott”);

p.put(“password”,”tiger”);

Connection con=d.connect(

“jdbc:oracle:thin:@Javastuff:1521:sandb”,p);

PreparedStatement ps=con.prepareStatement(“insert into emppassportDetails values(?,?,?)”);

ps.setInt(1,7934);

ps.setString(2,”12345A134″);

String s1[]={“v1″,”v2″,”v3″,”v4″,”v5”};

ArrayDescriptor ad=ArrayDescriptor.createDescriptor(“VISA_NOS”,con);

ARRAY a=new ARRAY(ad,con,s1);

ps.setArray(3,a);

int i=ps.executeUpdate();

System.out.println(“Row Inserted, count : “+i);

con.close();

}//main

}//class

DatabaseMetaData in JDBC

The DatabaseMetaData class is used to determine the capabilities of a JDBC driver and it database during runtime. If a given method of this interface is not supported by the JDBC driver, the method will either throw an SQLException, or in the case of a method that returns a result set, it may return null. Some of the methods take search patterns as its arguments. The pattern values used can be the SQL wildcard characters % and _. Other search arguments accept an empty set (“”) when the argument is not applicable, or null to drop the argument from the search criteria.

This interface is implemented by driver vendors to let users know the capabilities of a Database Management System (DBMS) in combination with the driver based on JDBC driver that is used with it. Different relational DBMSs often support different features, implement features in different ways, and use different data types. In addition, a driver may implement a feature on top of what the DBMS offers. Information returned by methods in this interface applies to the capabilities of a particular driver and a particular DBMS working together.

The JDBC API enables you to uncover metadata about a database using the DatabaseMetaData interfaces. The DatabaseMetaData interface enables you to obtain information about your database’s attributes and make runtime decisions based around that information.

package com.javastuff.jdbc;

import java.sql.*;

import java.util.*;

import java.io.*;

public class DataBaseMetaDataExample {

public static void main(String s[]) throws Exception {

Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);

Connection con =

DriverManager.getConnection(“jdbc:odbc:javastuff”, “scott”,”tiger”);

DatabaseMetaData db= con.getMetaData();

System.out.println(“Database name : “+db.getDatabaseProductName());

System.out.println(“Database version : “+db.getDatabaseProductVersion());

System.out.println(“\nDriver Name : “+ db.getDriverName());

System.out.println(“Driver Version : “+ db.getDriverVersion());

con.close();

}//main

}//class