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();
}
}
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();
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
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
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