JDBC Java Interview Questions | Eklavya Online

JDBC Java Interview Questions

JDBC is a set of Java API for executing SQL statements. This API consists of a set of classes and interfaces to enable programs to write pure Java Database applications.

JDBC technology drivers fit into one of four categories:

  • A JDBC-ODBC bridgeprovides JDBC API access via one or more ODBC drivers. Note that some ODBC native code and in many cases native database client code must be loaded on each client machine that uses this type of driver. Hence, this kind of driver is generally most appropriate when automatic installation and downloading of a Java technology application is not important. For information on the JDBC-ODBC bridge driver provided by Sun, see “http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/bridge.doc.html#996747” JDBC-ODBC Bridge Driver.
  • A native-API partly Java technology-enabled driverconverts JDBC calls into calls on the client API for Oracle, Sybase, Informix, DB2, or other DBMS. Note that, like the bridge driver, this style of driver requires that some binary code be loaded on each client machine.
  • A net-protocol fully Java technology-enabled drivertranslates JDBC API calls into a DBMS-independent net protocol which is then translated to a DBMS protocol by a server. This net server middleware is able to connect all of its Java technology-based clients to many different databases. The specific protocol used depends on the vendor. In general, this is the most flexible JDBC API alternative. It is likely that all vendors of this solution will provide products suitable for Intranet use. In order for these products to also support Internet access they must handle the additional requirements for security, access through firewalls, etc., that the Web imposes. Several vendors are adding JDBC technology-based drivers to their existing database middleware products.
  • A native-protocol fully Java technology-enabled driverconverts JDBC technology calls into the network protocol used by DBMSs directly. This allows a direct call from the client machine to the DBMS server and is a practical solution for Intranet access. Since many of these protocols are proprietary the database vendors themselves will be the primary source for this style of driver. Several database vendors have these in progress.

a) JDBC-ODBC Bridge driver

b) Native API Partly-Java driver

c) JDBC-Net Pure Java driver

d) Native-Protocol Pure Java driver

Here is the sample code that makes use of the default compareTo( ) provided in the String class as it implements the Comparable interface and the Collections utility class that provides a sorting method, which internally uses the efficient “merge sort” algorithm.

import java.util.Arrays;

import java.util.Collections;

import java.util.List;

 

public class Sort1 {

public static void main(String[] args) {

List<string> values = Arrays.asList(“JEE”, “Java”, “Servlets”, “JMS”, “JNDI”, “JDBC”, “JSP”, “EJB”);

Collections.sort(values); // uses the default compareTo(String anotherString)  in the String class

System.out.println(values);

}

}

Output:

[EJB, JDBC, JEE, JMS, JNDI, JSP, Java, Servlets]

Yes, 2 things — firstly, the above sort is case sensitive, that is the uppercase takes priority over lowercase pushing ‘Java’ after ‘JSP’. Secondly, if the collection had any null values, it will throw a NullpointerException.

These two issues can be rectified by providing a custom sorting implementation that ignores case and handles null values by pushing them to the end. The Collections class’s sort method takes a Comparator implementation as a second argument. In the code below, the Comparator has been implemented as an anonymous inner class. The compare(…) method will be called a number of times by the Collections.sort(list, comparator).

import java.util.Arrays;

import java.util.Collections;

import java.util.Comparator;

import java.util.List;

 

public class Sort2 {

 

public static void main(String[] args) {

List<String> values = Arrays.asList(“JEE”, “Java”, null,  “Servlets”, null, “JMS”, “JNDI”, “JDBC”, “JSP”, null,”EJB”);

 

//The comparator is defined as an anonymous inner class, but it can be

//defined in its own class. Handles nulls and ignores case

Collections.sort(values, new Comparator<String>() {

 

@Override

public int compare(String o1, String o2) {

//push the null values to the end

if(o1 == null){

if(o2 == null) {

return 0;

}

return 1;

}

else if(o2 == null){

return -1;

}

return o1.compareToIgnoreCase(o2);

}

}); // anonymous inner class end

 

System.out.println(values);

}

}

  1. a) OBDC is for Microsoft and JDBC is for Java applications. b) ODBC can’t be directly used with Java because it uses a C interface. c) ODBC makes use of pointers which have been removed totally from Java. d) ODBC mixes simple and advanced features together and has complex options for simple queries. But JDBC is designed to keep things simple while allowing advanced capabilities when required. e) ODBC requires manual installation of the ODBC driver manager and driver on all client machines. JDBC drivers are written in Java and JDBC code is automatically installable, secure, and portable on all platforms. f) JDBC API is a natural Java interface and is built on ODBC. JDBC retains some of the basic features of ODBC.

There are two types of JDBC Driver Models and they are: a) Two tier model and b) Three tier model Two tier model: In this model, Java applications interact directly with the database. A JDBC driver is required to communicate with the particular database management system that is being accessed. SQL statements are sent to the database and the results are given to user. This model is referred to as client/server configuration where user is the client and the machine that has the database is called as the server. Three tier model: A middle tier is introduced in this model. The functions of this model are: a) Collection of SQL statements from the client and handing it over to the database, b) Receiving results from database to the client and c) Maintaining control over accessing and updating of the above.

Loading the driver : To load the driver, Class. forName() method is used. Class. forName(”sun. jdbc. odbc. JdbcOdbcDriver”); When the driver is loaded, it registers itself with the java. sql. DriverManager class as an available database driver. b) Making a connection with database: To open a connection to a given database, DriverManager. getConnection() method is used. Connection con = DriverManager. getConnection (”jdbc:odbc:somedb”, “user”, “password”); c) Executing SQL statements : To execute a SQL query, java. sql. statements class is used. createStatement() method of Connection to obtain a new Statement object. Statement stmt = con. createStatement(); A query that returns data can be executed using the executeQuery() method of Statement. This method executes the statement and returns a java. sql. ResultSet that encapsulates the retrieved data: ResultSet rs = stmt. executeQuery(”SELECT * FROM some table”); d) Process the results : ResultSet returns one row at a time. Next() method of ResultSet object can be called to move to the next row. The getString() and getObject() methods are used for retrieving column values: while(rs. next()) { String event = rs. getString(”event”); Object count = (Integer) rs. getObject(”count”);

A thin wrapper around a millisecond value that allows JDBC to identify this as an SQL DATE value. A milliseconds value represents the number of milliseconds that have passed since January 1, 1970 00:00:00.000 GMT. To conform with the definition of SQL DATE, the millisecond values wrapped inside a java.sql.Date instance must be ‘normalized’ by setting the hours, minutes, seconds, and milliseconds to zero.

Explanation: A java.util.Date represents date and time of day, a java.sql.Date only represents a date (the complement of java.sql.Date is java.sql.Time, which only represents a time of day, but also extends java.util.Date).

JDBC is a set of Java API for executing SQL statements. This API consists of a set of classes and interfaces to enable programs to write pure Java Database applications.

JDBC-ODBC Bridge driver (is a driver that uses native(C language) libraries and makes calls to an existing ODBC driver to access a database engine).

?– (1) Request Lifetime: Using this technique to pass beans, a request dispatcher (using either “include” or forward”) can be called. This bean will disappear after processing this request has been completed. Servlet: request. setAttribute(”theBean”, myBean); RequestDispatcher rd = getServletContext(). getRequestDispatcher(”thepage. jsp”); rd. forward(request, response); JSP PAGE:<jsp: useBean id=”theBean” scope=”request” class=”. . . . . ” />(2) Session Lifetime: Using this technique to pass beans that are relevant to a particular session (such as in individual user login) over a number of requests. This bean will disappear when the session is invalidated or it times out, or when you remove it. Servlet: HttpSession session = request. getSession(true); session. putValue(”theBean”, myBean); /* You can do a request dispatcher here, or just let the bean be visible on the next request */ JSP Page:<jsp:useBean id=”theBean” scope=”session” class=”. . . ” /> 3) Application Lifetime: Using this technique to pass beans that are relevant to all servlets and JSP pages in a particular app, for all users. For example, I use this to make a JDBC connection pool object available to the various servlets and JSP pages in my apps. This bean will disappear when the servlet engine is shut down, or when you remove it. Servlet: GetServletContext(). setAttribute(”theBean”, myBean); JSP PAGE:<jsp:useBean id=”theBean” scope=”application” class=”. . . ” />

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.

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

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.

  • 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);

}

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

}

}

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.

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).

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.

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

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.

A JDBC connection is created in auto-commit mode by default. This means that each individual SQL statement is treated as a transaction and will be automatically committed as soon as it is executed. If you require two or more statements to be grouped into a transaction then you need to disable auto-commit mode using below command.

con.setAutoCommit(false);

Once auto-commit mode is disabled, no SQL statements will be committed until you explicitly call the commit method. A Simple transaction with use of autocommit flag is demonstrated below.

con.setAutoCommit(false);

PreparedStatement updateStmt =

con.prepareStatement( “UPDATE EMPLOYEE SET SALARY = ? WHERE EMP_NAME LIKE ?”);

updateStmt.setInt(1, 5000); updateSales.setString(2, “Jack”);

updateStmt.executeUpdate();

updateStmt.setInt(1, 6000); updateSales.setString(2, “Tom”);

updateStmt.executeUpdate();

con.commit();

con.setAutoCommit(true);

Warnings are issued by database to notify user of a problem which may not be very severe. Database warnings do not stop the execution of SQL statements. In JDBC SQLWarning is an exception that provides information on database access warnings. Warnings are silently chained to the object whose method caused it to be reported. Warnings may be retrieved from Connection, Statement, and ResultSet objects.

Handling SQLWarning from connection object

//Retrieving warning from connection object

SQLWarning warning = conn.getWarnings();

//Retrieving next warning from warning object itself

SQLWarning nextWarning = warning.getNextWarning();

//Clear all warnings reported for this Connection object.

conn.clearWarnings();

Handling SQLWarning from Statement object

//Retrieving warning from statement object

stmt.getWarnings();

//Retrieving next warning from warning object itself

SQLWarning nextWarning = warning.getNextWarning();

//Clear all warnings reported for this Statement object.

stmt.clearWarnings();

Handling SQLWarning from ResultSet object

//Retrieving warning from resultset object

rs.getWarnings();

//Retrieving next warning from warning object itself

SQLWarning nextWarning = warning.getNextWarning();

//Clear all warnings reported for this resultset object.

rs.clearWarnings();

The call to getWarnings() method in any of above way retrieves the first warning reported by calls on this object. If there is more than one warning, subsequent warnings will be chained to the first one and can be retrieved by calling the method SQLWarning.getNextWarning on the warning that was retrieved previously. A call to clearWarnings() method clears all warnings reported for this object. After a call to this method, the method getWarnings returns null until a new warning is reported for this object. Trying to callgetWarning() on a connection after it has been closed will cause an SQLException to be thrown. Similarly, trying to retrieve a warning on a statement after it has been closed or on a result set after it has been closed will cause an SQLException to be thrown. Note that closing a statement also closes a result set that it might have produced.

Stored procedure is a group of SQL statements that forms a logical unit and performs a particular task. Stored Procedures are used to encapsulate a set of operations or queries to execute on database. Stored procedures can be compiled and executed with different parameters and results and may have any combination of input/output parameters. Stored procedures can be called using CallableStatement class in JDBC API. Below code snippet shows how this can be achieved.

  • CallableStatement cs = con.prepareCall(“{call MY_STORED_PROC_NAME}”);
  • ResultSet rs = cs.executeQuery();

Spring has become very huge and bulky. So, don’t over do it by using all its features because of the hype that Spring is good. Look at what parts of Spring really provides some benefits for your project and use those parts. In most cases, it is much better to use proven frameworks like Spring than create your own equivalent solution from a maintenance and applying the best practices perspective. For example, all spring templates (jdbc, rest, jpa etc.) have the following advantages — perform common setup routines for you, let you skip the boilerplate and concentrate on the logic you want.

Spring MVC is probably not the best Web framework. There are other alternatives like Struts 2, Wicket, and JSF.  Having said this, Spring integrates well with the other Web frameworks like Struts, JSF, etc.

The XML files can get bloated. This can be minimized by carefully considering other options like annotations, JavaConfig, and having separate XML configuration files.

Spring has a layered architecture with over 20 modules to choose from. This means, use what you need and leave what you don’t need now. Spring simplifies JEE through POJO programming. There is no behind the scene magic in Spring as in JEE programming. POJO programming enables continuous integration and testability.

 

Spring framework’s core functionality is dependency injection (DI). Dependency injection promotes easy unit testing and more maintainable and flexible code. DI code is much easier to test. The functionality expressed by the object can be tested in a black box by building ‘mock’ objects implementing the interfaces expected by yourapplication logic. DI code is much easier to reuse as the ‘depended’ functionality is extrapolated into well defined interfaces, allowing separate objects whose configuration is handled by a suitable application platform to be plugged into other objects at will. DI code is more flexible. It is innately loosely coupled code to an extreme. This allows the programmer to pick and choose how objects are connected based exclusively on their required interfaces on one end and their expressed interfaces on the other.

Spring supports Aspect Oriented Programming (AOP), which enables cohesive development by separatingapplication business logic from system services. Supporting functionalities like auditing, gathering performance and memory metrics, etc can be enabled through AOP.

Spring also provides a lot of templates which act as base classes to make using the JEE standard technologies a breeze to work with. For example, the JdbcTemplate works well with JDBC, the JpaTemplate does good things with JPA, JmsTemplate makes JMS pretty straightforward. The RestTemplate is simply awesome in it’s simplicity. Simplicity means more readable and maintainable code.When writing software these days, it is important to try and decouple as much middleware code from your business logic as possible. The best approach when using remoting is to use Spring Remoting which can then use any messaging or remoting technology under the covers. Apache Camel is a powerful open source integration framework based on known Enterprise Integration Patterns with powerful Bean Integration. Apache Camel is designed to work nicely with the Spring Framework in a number of ways.

It also provides declarative transactions, job scheduling, authentication, a fully-fledged MVC webframework, and integration to other frameworks likeHibernate,iBatis,JasperReports,JSF,Struts,Tapestry,Seam, Quartz job scheduler, etc.

Spring beans can be shared between different JVMs using Terracotta. This allows you to take existing beans and spread them across a cluster, turn Spring application context events into distributed events, export clustered beans via Spring JMX, and make your Spring applications highly available and clustered. Spring also integrate well with other clustering solutions like Oracle’s Coherance.Spring favors unchecked exceptions and eliminates unsightly try, catch, and finally (and some times try/catch within finally itself) blocks. The Spring templates like JpaTemplate takes care of closing or releasing a database connection. This prevents any potential resource leaks and promotes more readable code.It prevents the proliferation of factory and singleton pattern classes that need to be created to promote loose coupling if not for using a DI framework like Spring or Guice.