What are stored procedures? How is it useful
A stored procedure is a set of statements/commands which reside in the database. The stored procedure is pre-compiled and saves the database the effort of parsing and compiling sql statements every time a query is run. Each database has its own stored procedure language, usually a variant of C with a SQL preproceesor. Newer versions of db’s support writing stored procedures in Java and Perl too. Before the advent of 3-tier/n-tier architecture it was pretty common for stored procs to implement the business logic( A lot of systems still do it). The biggest advantage is of course speed. Also certain kind of data manipulations are not achieved in SQL. Stored procs provide a mechanism to do these manipulations. Stored procs are also useful when you want to do Batch updates/exports/houseKeeping kind of stuff on the db. The overhead of a JDBC Connection may be significant in these cases.
What is Metadata and why should I use it
JDBC API has 2 Metadata interfaces DatabaseMetaData & ResultSetMetaData. The DatabaseMetaData provides Comprehensive information about the database as a whole. 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 technology (“JDBC driver”) that is used with it. Below is a sample code which demonstrates how we can use the DatabaseMetaData
DatabaseMetaData md = conn.getMetaData();
System.out.println(“Database Name: ” + md.getDatabaseProductName());
System.out.println(“Database Version: ” + md.getDatabaseProductVersion());
System.out.println(“Driver Name: ” + md.getDriverName());
System.out.println(“Driver Version: ” + md.getDriverVersion());
The ResultSetMetaData is an object that can be used to get information about the types and properties of the columns in a ResultSet object. Use DatabaseMetaData to find information about your database, such as its capabilities and structure. Use ResultSetMetaData to find information about the results of an SQL query, such as size and types of columns. Below a sample code which demonstrates how we can use the ResultSetMetaData
ResultSet rs = stmt.executeQuery(“SELECT a, b, c FROM TABLE2”);
ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();
boolean b = rsmd.isSearchable(1);
Explain about transaction file
Transactions denote a work file which can save changes made or revert back the changes. A transaction can be started by session.beginTransaction() and it uses JDBC connection, CORBA or JTA. When this session starts several transactions may occur.
Explain about ServletConfig Interface
- ServletConfig a ServletConfig object is used to obtain configuration data when it is loaded.
- There can be multiple ServletConfig objects in a single web application.
- This object defines how a servlet is to be configured is passed to a servlet in its init method.
- Most servlet containers provide a way to configure a servlet at run-time (usually through flat file) and set up its initial parameters.
- The container, in turn, passes these parameters to the servlet via the ServetConfig.
<web-app>
<servlet>
<servlet-name>TestServlet</servlet-name>
<servlet-class>TestServlet</servlet-class>
<init-param>
<param-name>driverclassname</param-name>
<param-value>sun.jdbc.odbc.JdbcOdbcDriver</param-value>
</init-param>
<init-param>
<param-name>dburl</param-name>
<param-value>jdbc:odbc:MySQLODBC</param-value>
</init-param>
</servlet>
</web-app>
publicvoidinit()
{
ServletConfig config = getServletConfig();
String driverClassName = config.getInitParameter(“driverclassname”);
String dbURL = config.getInitParameter(“dburl”);
Class.forName(driverClassName);
dbConnection = DriverManager.getConnection(dbURL,username,password);
}
How to do database connection using JDBC thin driver
This is one of the most commonly asked questions from JDBC fundamentals, and knowing all the steps of JDBC connection is important.
import java.sql.*;
class JDBCTest {
public static void main (String args []) throws Exception
{
//Load driver class
Class.forName (“oracle.jdbc.driver.OracleDriver”);
//Create connection
Connection conn = DriverManager.getConnection
(“jdbc:oracle:thin:@hostname:1526:testdb”, “scott”, “tiger”);
// @machineName:port:SID, userid, password
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(“select ‘Hi’ from dual”);
while (rs.next())
System.out.println (rs.getString(1)); // Print col 1 => Hi
stmt.close();
}
}
What is RowSet? or What is the difference between RowSet and ResultSet? or Why do we need RowSet? or What are the advantages of using RowSet over ResultSet
RowSet is a interface that adds support to the JDBC API for the JavaBeans component model. A rowset, which 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. The RowSet interface provides a set of JavaBeans properties that allow a RowSet instance to be configured to connect to a JDBC data source and read some data from the data source. A group of setter methods (setInt, setBytes, setString, and so on) provide a way to pass input parameters to a rowset’s command property. This command is the SQL query the rowset uses when it gets its data from a relational database, which is generally the case. Rowsets are easy to use since the RowSet interface extends the standard java.sql.ResultSet interface so it has all the methods of ResultSet. There are two clear advantages of using RowSet over ResultSet
- RowSet makes it possible to use the ResultSet object as a JavaBeans component. As a consequence, a result set can, for example, be a component in a Swing application.
- RowSet 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 RowSet object implementation (e.g. JdbcRowSet) with the data of a ResultSet object and then operate on the RowSet object as if it were the ResultSet object.
What is a connected RowSet? or What is the difference between connected RowSet and disconnected RowSet? or Connected vs Disconnected RowSet, which one should I use and when?
Connected RowSet
A RowSet object may make a connection with a data source and maintain that connection throughout its life cycle, in which case it is called a connected rowset. A rowset may also make a connection with a data source, get data from it, and then close the connection. Such a rowset is called a disconnected rowset. A disconnected rowset may make changes to its data while it is disconnected and then send the changes back to the original source of the data, but it must reestablish a connection to do so. Example of Connected RowSet: A JdbcRowSet object is a example of connected RowSet, which means it continually maintains its connection to a database using a JDBC technology-enabled driver.
Disconnected RowSet
A disconnected rowset may have a reader (a RowSetReader object) and a writer (a RowSetWriter object) associated with it. The reader may be implemented in many different ways to populate a rowset with data, including getting data from a non-relational data source. The writer can also be implemented in many different ways to propagate changes made to the rowset’s data back to the underlying data source.Example of Disconnected RowSet: A CachedRowSet object is a example of disconnected rowset, which means that it makes use of a connection to its data source only briefly. It connects to its data source while it is reading data to populate itself with rows and again while it is propagating changes back to its underlying data source. The rest of the time, a CachedRowSet object is disconnected, including while its data is being modified. Being disconnected makes a RowSet object much leaner and therefore much easier to pass to another component. For example, a disconnected RowSet object can be serialized and passed over the wire to a thin client such as a personal digital assistant (PDA).
What does Class.forName() method do
Method forName() is a static method of java.lang.Class. This can be used to dynamically load a class at run-time. Class.forName() loads the class if its not already loaded. It also executes the static block of loaded class. Then this method returns an instance of the loaded class. So a call to Class.forName(‘MyClass’) is going to do following:
- Load the class MyClass.
- Execute any static block code of MyClass.
- Return an instance of MyClass.
JDBC Driver loading using Class.forName is a good example of best use of this method. The driver loading is done like this
Class.forName(“org.mysql.Driver”);
All JDBC Drivers have a static block that registers itself with DriverManager and DriverManager has static initializer method registerDriver() which can be called in a static blocks of Driver class. A MySQL JDBC Driver has a static initializer which looks like this:
static {
try {
java.sql.DriverManager.registerDriver(new Driver());
} catch (SQLException E) {
throw new RuntimeException(“Can’t register driver!”);
}
}
Class.forName() loads driver class and executes the static block and the Driver registers itself with the DriverManager.
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
What are the types of statements in JDBC
the JDBC API has 3 Interfaces, (1. Statement, 2. PreparedStatement, 3. CallableStatement ). The key features of these are as follows: Statement
- This interface is used for executing a static SQL statement and returning the results it produces.
- The object of Statement class can be created using Connection.createStatement() method.
- PreparedStatement
- A SQL statement is pre-compiled and stored in a PreparedStatement object.
- This object can then be used to efficiently execute this statement multiple times.
- The object of PreparedStatement class can be created using Connection.prepareStatement() method. This extends Statement interface.
- CallableStatement
- This interface is used to execute SQL stored procedures.
- This extends PreparedStatement interface.
- The object of CallableStatement class can be created using Connection.prepareCall() method.
Statement: to be used createStatement() method for executing single SQL statement
PreparedStatement: To be used preparedStatement() method for executing same SQL statement over and over.
CallableStatement: To be used prepareCall() method for multiple SQL statements over and over.