What is jdbc?

JDBC stands for Java Database Connectivity, which is a standard Java API for database-independent connectivity between the Java programming language and a wide range of databases.

JDBC has been developed under the Java Community Process that allows multiple implementations to exist and be used by the same application. JDBC provides methods for querying and updating the data in relational Database Management system such as SQL, Oracle etc.

The Java application programming interface provides a mechanism for dynamically loading the correct Java packages and drivers and registering them with the JDBC Driver Manager that is used as a connection factory for creating JDBC connections which supports creating and executing statements such as SQL INSERT, UPDATE and DELETE. Driver Manager is the backbone of the jdbc architecture.

Generally all Relational Database Management System supports SQL and we all know that Java is platform independent, so JDBC makes it possible to write a single database application that can run on different platforms and interact with different Database Management Systems.

Java Database Connectivity is similar to Open Database Connectivity (ODBC) which is used for accessing and managing database, but the difference is that JDBC is designed specifically for Java programs, whereas ODBC is not depended upon any language.

In short JDBC helps the programmers to write java applications that manage these three programming activities:

1. It helps us to connect to a data source, like a database.
2. It helps us in sending queries and updating statements to the database and
3. Retrieving and processing the results received from the database in terms of answering to your query.

Transactions in Jdbc

Transactions are a mechanism to group operations together so that either all of them complete together successfully or none of them do. This avoids database consistency problems that can occur if some groups of operations are only partly completed. Think of a bank transfer that requires withdrawing money from one account to deposit in another. If the withdraw is completed but the deposit fails then the customer is likely to be very unhappy. If the deposit succeeds but the withdraw fails then the bank is likely to be very unhappy. Actually, transactions handle other aspects of consistency as well. For example, ensuring that a second transaction sees the database as if either the first transaction has completely finished or as if it has not started yet but not as if some of the first transaction's operations have completed but not others - even if both transactions are running simultaneously.
When a Connection is obtained, by default its AutoCommit property is set to true. This means that every query execution is committed immediately after it is executed and before the next one is executed. To enable grouping of operations in transactions, you have to switch the AutoCommit property off:

con.setAutoCommit(false) ;

Now you have to obtain new statement objects from the connection (the old ones won't work), and query or update as usual. When all operations that you want to group together have completed, you must commit the updates to the database:

con.commit() ;

At this point you can continue with more operations which will be grouped into a new transaction or you can switch AutoCommit back on:

con.setAutoCommit(true) ;

If anything goes wrong during a transaction (e.g. an Exception is thrown or an error means that you cannot complete your group of operations) then you have to undo all operations in your transaction so far:

con.rollBack() ;

Note
If the database or the machine crashes, rollBack will (essentially) be called for you automatically to clean up uncommitted transactions when the database is restarted.

You should make every effort to minimise the length of time that you have open transactions running (i.e. the time after the first operation in a transaction until the call of the commit or rollback that terminates it) as they hold expensive resources and, in particular, locks in the database system which may stop any other competing transactions from proceeding.
Getting all this working correctly requires careful attention to your Exception handling. You must embed a transaction in a try clause so that any exception will trigger a rollback. If you do have to handle an exception (and therefore rollback) in a method that normally closes open connections before returning, make sure that this does not create a loop hole that allows the method to return without closing the connection. You can use a finally block to ensure that this is handled correctly.
Finally, note that when you modify the AutoCommit status of a connection, all operations by any thread using that connection object are run in the same transaction. Therefore you have to be very careful about sharing connection objects between different threads (particularly important in servlet and JSP code). The simple rule is that you can share without problems a connection which has been set to auto commit. Do not share non auto committing connections unless you use some other mechanism to make sure that you don't end up merging different transactions into one - with consequences for committing and roll backs.

Prepared Statements in Jdbc

Rather than Statement objects, PreparedStatement objects can be used. This have the advantages over plain Statement objects of:

• For repetitive queries that are very similar except for some parameter values, they are considerably more efficient because the SQL is compiled once and then executed many times, with the parameter values substituted in each execution
• The mechanism for inserting parameter values takes care of all necessary special character quoting in the correct manner for the connected database

The PreparedStatement has its SQL text set when it is constructed. The parameters are specified as '?' characters. After creation, the parameters can be cleared using clearParameters and set using setInt, setString, etc. methods (parameter positions start at 1) and the statement can then be executed using execute, executeUpdate or executeQuery methods as for Statement and with the same return types but with no arguments (as the SQL text has already been set when the statement was created):

PreparedStatement pstmt = con.prepareStatement(
                              "INSERT INTO Customers " +
                              "(CustomerFirstName, CustomerLastName, CustomerAddress) "+
                              "VALUES (?, ?, ?)") ;

pstmt.clearParameters() ;
pstmt.setString(1, "Joan") ;
pstmt.setString(2, "D'Arc") ;
pstmt.setString(3, "Tower of London") ;
count = pstmt.executeUpdate() ;
System.out.println ("\nInserted " + count + " record successfully\n") ;

pstmt.clearParameters() ;
pstmt.setString(1, "John") ;
pstmt.setString(2, "D'Orc") ;
pstmt.setString(3, "Houses of Parliament, London") ;
count = pstmt.executeUpdate() ;
System.out.println ("\nInserted " + count + " record successfully\n") ;

JDBC: retrieve values from BLOB or CLOB

There are four pairs of methods in the java.sql.ResultSet class that deal with BLOBs/CLOBs:

1. Reader getCharacterStream(int columnIndex)   /
Reader getCharacterStream(String columnName)
2. InputStream getBinaryStream(int columnIndex)  /
   InputStream getBinaryStream(String columnName)
3. Blob getBlob(int i)/Blob getBlob(String colName)
4. Clob getClob(int i)/Clob getClob(String colName)

Example code:

PreparedStatement pstmt = conn.prepareStatement("SELECT image FROM photos where id = ?");
pstmt.setInt(100);
ResultSet rs= pstmt.executeQuery();
while(rs.next()) {
    InputStream in = rs.getBinaryStream(1);
    ...
} 
rs.close();

JDBC: insert BLOB or CLOB

Use java.sql.PreparedStatement to insert a BLOB or CLOB field:

1. void setAsciiStream(int parameterIndex, InputStream x, int length)
   Sets the designated parameter to the given input stream, which will have the specified number of bytes.
2. void setBinaryStream(int parameterIndex, InputStream x, int length)
   Sets the designated parameter to the given input stream, which will have the specified number of bytes.
3. void setCharacterStream(int parameterIndex, Reader reader, int length)
   Sets the designated parameter to the given Reader object, which is the given number of characters long.
4. void setClob(int i, Clob x)
   Sets the designated parameter to the given Clob object.
5. void setBlob(int i, Blob x)
   Sets the designated parameter to the given Blob object.

Example code:

PreparedStatement pstmt = 
            con.prepareStatement("INSERT INTO photos (id, image) VALUES( ?, ? )");
File inFile = new File("PHOTO12.jpg");
FileInputStream in = new FileInputStream(inFile);
long len = inFile.length();
pstmt.setInt(1, 101);
pstmt.setBinaryStream(2, in, (int)len);
pstmt.executeUpdate();
in.close();

Working with ResultSets

If you do not know exactly the table structure (the schema) of the ResultSet, you can obtain it via a ResultSetMetaData object.

ResultSetMetaData rsmd = rs.getMetaData() ;
int colCount = rsmd.getColumnCount() ;

for (int i = 1 ; i <= colCount ; i++)
{
    if (i > 1)
        out.print(", ");
    out.print(rsmd.getColumnLabel(i)) ;
}
out.println() ;

Once a ResultSet has been obtained, you can step through it to obtain its rows, or, more specifically, the fields of its rows:

while (rs.next())
{
    for (int i = 1 ; i <= colCount ; i++)
    {
        if (i > 1)
        out.print(", ");
        out.print(rs.getObject(i)) ;
    }
    out.println() ;
}

Note that the column numbers start at 1, not 0 as in Java arrays. More conveniently, if slightly less efficiently, there is a getObject method for ResultSet which takes a String argument containing the column name. There are also getxxx methods that take the String name of the column instead of the column number. Thus the above code could have been written:

while (rs.next())
{
    out.println(rs.getObject("CustomerID")   + ", " +
                rs.getObject("CustomerFirstName") + ", " +
                rs.getObject("CustomerLastName")  + ", " +
                rs.getObject("CustomerAddress") ) ;
}

Instead of getObject, you can use type specific methods, getInt, getString, etc. However, these have a major disadvantage: if the field is of primitive type such as int, float etc., then if the field is actually null in the database, then there is no value that can be returned that is indistinguishable from some valid value. There is a mechanism for finding out whether the last value obtained was really null or not: wasNull, but this must be called immediately after the getXxx method and before the next such call. If you use getObject, then if the field was null then the object value returned will be null so you can pass this value around and check for it at your convenience. Note also that printing is the most common thing to do with retrieved values, and passing a null to print will print the string "null". Thus for many cases no extra processing of nulls will be necessary.

Using Statements in Jdbc

A Statement is obtained from a Connection:

Statement stmt = con.createStatement() ;

Once you have a Statement, you can use it to execute, and control the execution of, various kinds of SQL queries.

• Use stmt.executeUpdate with a string argument containing the text of an SQL update query (INSERT, DELETE or UPDATE). This returns an integer count of the number of rows updated.
• Use stmt.executeQuery with a string argument containing the text of an SQL SELECT query. This returns a ResultSet object which is used to access the rows of the query results.
• You can use stmt.execute to execute an arbitrary SQL statement which may be of any type. However, extracting the results, whether an integer or a ResultSet, is less convenient. This is usually only used where you want a generalized access to the database that allows programmatic generation of queries.
  

  int count = stmt.executeUpdate("INSERT INTO Customers " +
                        "(CustomerFirstName, CustomerLastName, CustomerAddress) "
                        "VALUES ('Tony', 'Blair', '10 Downing Street, London')") ;
  ResultSet rs = stmt.executeQuery("SELECT * FROM Customers") ;
  // do something with count and RS

The syntax of the SQL string passed as an argument must match the syntax of the database being used. In particular, appropriate quoting of special characters must be used. For example, if a name, O'Neill, is to be inserted, it has to be entered as

ResultSet rs = stmt.executeQuery("SELECT * FROM Customers" +
                                 "WHERE CustomerLastName = 'O''Neill'") ;

Basic JDBC Steps

JDBC (the Java Database Connection) is the standard method of accessing databases from Java. Sun developed the JDBC library after considering Microsoft's ODBC. Their aims were to get something similar but easier to learn and use: ODBC is complex because it has a few very complex calls. JDBC has split up this complexity into many more calls, but with each of them being relatively simple.
Accessing a database using JDBC involves a number of steps:

1. Get a Connection object connected to a database
2. Get a Statement object from an open Connection object
3. Get a ResultSet from a Statement's query execution
4. Process the rows from the ResultSet

While the above is the standard pattern, there are variations: Instead of a Statement, you can get a PreparedStatement (which allows a query to be pre-compiled for extra performance when executed repetitively). An update query may only return a count of the number of rows updated, inserted or deleted instead of a ResultSet. There are calls to get information about the database and about ResultSet objects (e.g. the number, names and types of columns). There are calls to support transactional updates to the database.

How to setup H2 database for database connections in java?

Download H2 database
Download H2 database from - H2 download, I downloaded the platform independent version. Extract the zip to some directory.
Run the H2 engine
Now migrate to bin extracted folder, and go to bin folder. Double click on h2.bat and the database engine starts.
Add the jars 
Add H2 jar present in the same bin folder to the java project and add following class to the project and run it.

Now test the code:

package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class H2Test
{
  public static void main( String args[] ) throws Exception
  {
    Class.forName( "org.h2.Driver" );
    Connection conn = null;
    Statement stmt = null;

    try
    {
      conn = DriverManager.getConnection( "jdbc:h2:file:db/test", "sa", "" );
      stmt = conn.createStatement();
      stmt.executeUpdate( "create table person(name varchar(10));" );
    }
    catch( Exception e )
    {
      e.printStackTrace();
    }
    finally
    {
      if( stmt != null )
      {
        try
        {
          stmt.close();
          System.out.println("Test done");
        }
        catch( Exception e )
        {
          e.printStackTrace();
        }
      }

      if( conn != null )
      {
        conn.close();
      }

    }

  }
}

List of JDBC drivers for Java

If you need to access a database with Java, you need a driver. This is a list of the drivers available, what database they can access, who makes it, and how to contact them.

IBM DB2
jdbc:db2://HOST:PORT/DB
COM.ibm.db2.jdbc.app.DB2Driver

JDBC-ODBC Bridge
jdbc:odbc:DB
sun.jdbc.odbc.JdbcOdbcDriver

Microsoft SQL Server
jdbc:weblogic:mssqlserver4:DB@HOST:PORT
weblogic.jdbc.mssqlserver4.Driver

Oracle Thin
jdbc:oracle:thin:@HOST:PORT:SID
oracle.jdbc.driver.OracleDriver

PointBase Embedded Server
jdbc:pointbase://embedded:PORT/DB
com.pointbase.jdbc.jdbcUniversalDriver

Cloudscape
jdbc:cloudscape:DB
COM.cloudscape.core.JDBCDriver

Cloudscape RMI
jdbc:rmi://HOST:PORT/jdbc:cloudscape:DB
RmiJdbc.RJDriver

Firebird (JCA/JDBC Driver)
jdbc:firebirdsql://HOST:PORT/DB
org.firebirdsql.jdbc.FBDriver

Informix Dynamic Server
jdbc:informix-sqli://HOST:PORT/DB:INFORMIXSERVER=SERVER_NAME
com.informix.jdbc.IfxDriver

Hypersonic SQL (v1.2 and earlier)
jdbc:HypersonicSQL:DB
hSql.hDriver

Hypersonic SQL (v1.3 and later)
jdbc:HypersonicSQL:DB
org.hsql.jdbcDriver

Microsoft SQL Server (JTurbo Driver)
jdbc:JTurbo://HOST:PORT/DB
com.ashna.jturbo.driver.Driver

Microsoft SQL Server (Sprinta Driver)
jdbc:inetdae:HOST:PORT?database=DB
com.inet.tds.TdsDriver

Microsoft SQL Server 2000 (Microsoft Driver)
jdbc:microsoft:sqlserver://HOST:PORT;DatabaseName=DB
com.microsoft.sqlserver.jdbc.SQLServerDriver

MySQL (MM.MySQL Driver)
jdbc:mysql://HOST:PORT/DB
org.gjt.mm.mysql.Driver

Oracle OCI 8i
jdbc:oracle:oci8:@SID
oracle.jdbc.driver.OracleDriver

Oracle OCI 9i
jdbc:oracle:oci:@SID
oracle.jdbc.driver.OracleDriver

H2 Database (in memory type database)
jdbc:h2:file:db/test
org.h2.Driver

To test your driver once it's installed, try the following java code:

try{
  Class.forName("Driver name");
  Connection con = DriverManager.getConnenction("jdbcurl","username","password");
  //other manipulation using jdbc commands
}
catch(Exception e){
}

Hibernate vs JDBC

Abstraction
One of the objectives of Hibernate and other ORM tools is to abstract the persistence of an object. Hibernate works hard to provide as much flexibility as JDBC offers while at the same time removing much of the maintenance headaches that are associated with managing SQL statements embedded into an application.

Flexible Configuration with ORM
There are some additional considerations and complexity that must be tackled, but this quickly becomes a non-issue as a developer becomes adept with the framework. The complexities that were ones a problem with JDBC (connection management, connection pooling, caching, lazy-loading associations, etc.) become configuration driven and no longer an issue for a developer to contend with.
With HQL (Hibernate Query Language) the developer can achieve nearly all of the same semantics that are available in SQL. All the power of the relational database is still present and can even be reinforced in the application through column definition, foreign keys, etc. on the object meta-data (which is amazingly simple with annotations).
If you additionally leverage Spring in conjunction with Hibernate (or JPA) you will also reap the benefit of springs transaction management, connection management, etc. and still have access to the underlying JDBC connection if you find a reason to need it.

Transparent Persistence
The automatic mapping of Java objects with database tables and vice versa is called Transparent Persistence. Hibernate provides transparent persistence and developer does not need to write code explicitly to map database tables tuples to application objects during interaction with RDBMS. With JDBC this conversion is to be taken care of by the developer manually with lines of code.

JDBC Hello World program

Register the driver you want:

static{

// register Oracle JDBC driver with DriverManager
     Class.forName("oracle.jdbc.driver.OracleDriver");

}

Create a connection object:

//Create a method, in which we will create connection

public void createConnection(){

Connection conn=null;

Now just open the connection:

try{  // get a connection to the database
     conn = DriverManager.getConnection(
   "jdbc:oracle:thin:@localhost:1521:XE", 
   "scott", 
   "tiger");

     // construct a Statement
     Statement stmt = conn.createStatement();

     // run query
     ResultSet rs = stmt.executeQuery("SELECT 'Hello World' FROM DUAL;");

     // iterate through the result set
     while (rs.next()) {
           System.out.println(rs.getString(1));
     }
}
catch(Exception ex){//deal with exception}

}//end method 

JDBC vs ORM

Since entities form the heart of the JPA, they have some unique characteristics like persistability, identity and transactionability. The property of persistability deals with the storing and retrieving of entity from and to a persistent medium like database. Identity property is usually used to identity one unique entity among multiple entities (or multiple entity instances) in a database. All the CRUD operations (Create, Update and Delete) for entity objects will occur within a transactional context and it is one of the major characteristic for an entity object as the real state of an entity depends whether a transaction completes (commits/fails) or not.

Example

import  java.sql.*;

public class  AllTableName{
   public static void  main(String[] args) {
     System.out.println( "Listing all table name in Database!" );
     Connection con =  null ;
     String url =  "jdbc:mysql://localhost:3306/" ;
     String db =  "jdbctutorial" ;
     String driver =  "com.mysql.jdbc.Driver" ;
     String user =  "root" ;
     String pass =  "root" ;
     try {
       Class.forName(driver);
       con = DriverManager.getConnection(url+db, user, pass);
       try {
         DatabaseMetaData dbm = con.getMetaData();
         String[] types = { "TABLE" };
         ResultSet rs = dbm.getTables(null,null, "%" ,types);
         System.out.println( "Table name:" );
         while  (rs.next()){
           String table = rs.getString( "TABLE_NAME" );
           System.out.println(table);
           con.close();
         }
       }
       catch  (SQLException s){
         System.out.println( "No any table in the database" );
       }
     }
     catch  (Exception e){
       e.printStackTrace();
     }
   }
}

Above program retrieves the names of all tables present in the database and then displays on the console.

Advantages of JDBC

• Clean and easily for small programs
  
• JDBC provides good performance with large amount of data
  
• Small JDBC programs can be developed very easily
  
• Very good for small applications

Disadvantages of JDBC

• JDBC is not easily if it is used in large projects. There is a big programming overhead.
  
• Programmer must hardcode the Transactions and concurrency code in the application.
  
• Handling the JDBC connections and properly closing the connection is also a big issue. Properly closing the connection is must.
  
• JDBC is not good for big applications

ORM

ORM stands for Object Relational Mapping, is another technology to access the data databases. Here business object is directly mapped to the database tables with the help of ORM framework.

ere are the benefits of ORM technology

• No need to deal with the SQL Queries to save and retrieve the data
  
• Simple configuration
  
• Standardized API to persist the business objects
  
• Fast development of application
  
• Concurrency support
  
• Excellent cashing support for better performance of the application
  
• Injected transaction management
  
• Configurable logging
  
• Easy to learn and use

Disadvantages of ORM

• Slow performance in case of large batch updates
  
• Little slower than JDBC 

Generic database insert and select code

public class J850sjdbc {

public static String insert(String names, String values) {

java.sql.Connection conn = linktodata();

String todo = ("INSERT into staff " +
"(" + names + ") values (" + values + ")");

try {
java.sql.Statement s = conn.createStatement();
int r = s.executeUpdate (todo);
}
catch (Exception e) {
return ("Oh oops - code 003\n"+e);
}

return (todo);

}

public static String select(String [] fields, String selector) {

java.sql.Connection conn = linktodata();

StringBuffer reply = new StringBuffer("<table border=1>");

String todo = ("SELECT * "+
" from staff " + selector);

try {
java.sql.Statement s = conn.createStatement();
java.sql.ResultSet r = s.executeQuery (todo);
while(r.next()) {
reply.append("<tr>");
for (int i=0;i<fields.length;i++) {
reply.append(tabit(r.getString(fields[i])));
}
reply.append("</tr>");
}
reply.append("</table>");
}
catch (Exception e) {
return ("Oh oops - code 003\n"+e);
}

return (reply.toString());

}

private static String tabit(String box) {
return ("<td>"+box+"</td>");
}

private static java.sql.Connection linktodata () {

java.sql.Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
}
catch (Exception e) {
return conn;
// return "Oh dear - code 001 and a quarter";
}
try {
conn = java.sql.DriverManager.getConnection(
"jdbc:mysql://bhajee/J850a?user=jtest&password=");
}
catch (Exception e) {
return conn;
// return "Oh dear - code 001 and a half";
}
return conn;
}
}