JDBC (Java Database Connectivity) is an API (Application Programming Interface) which is used to connect java to any database. It is used to communicate with various types of databases such as Oracle, MS Access, My SQL and SQL Server. It is a platform-independent interface between Java and database. Execution of the SQL queries and retrieval of the results is done through JDBC. JDBC is similar to ODBC, which is language independent interface but JDBC is specifically designed for Java. Fig.1 Database connectivity through JDBC. Java Database Connectivity (JDBC) The Java Database Connectivity (JDBC) defines a number of industry standard rules that are used for connectivity of Java programming language with the database by defining interfaces and classes for writing database applications in java. It allows communication between a wide range of databases such as SQL, PL/SQL and other tabular data sources, such as spreadsheets or flat files to almost any relational database. A JDBC-to-ODBC bridge enables connections to any ODBC-accessible data source. It actually defines how a client may access a database and provides methods for querying and updating the database. Along with Windows OS, Mac OS, it works on various versions of UNIX. You can also use the JDBC API from a servlet or a JSP page to access the database directly without going through an enterprise bean. The JDBC API has two parts: An application-level interface used by the application components to access a Database. A service provider interface to join a JDBC driver to the Java EE platform. JDBC usage in our application can be divided into major steps: Obtaining a database connection. Execute queries against the connected database and receive results. Process the received results. Java Naming and Directory Interface API Naming and directory functionality enables the applications to access different naming and directory services such as LDAP, DNS and NIS is provided by JNDI API. Standard directory operations for applications such as associating attributes with objects are provided by JNDI API. Customization of a component is driven by a naming component without changing the component’s source code. User-defined objects such as enterprise beans, JDBC data-source objects, environment entries and message connections are named on Java EE platform. An object should be named within a subcontext of the naming environment according to the type of the object. For example, enterprise beans are named within the subcontext java:com/dt/ejbeans, and JDBC Data Source references are named within the subcontext java:com/dt/jdbc. Functionality JDBC can be implemented in many ways and can be used by the same application. Correct java packages are dynamically loaded through a mechanism provided by this API and then these java packages are registered with JDBC driver manager. The Driver Manager is used as a connection factory for creating JDBC connections. Execution and creation of statements is supported by JDBC connections. These may be update statements such as SQL's CREATE, INSERT, UPDATE and DELETE, or they may be query statements such as SELECT. In addition to this a JDBC connection may also invoke stored procedures. Basically statement is an interface. JDBC represents statements using one of the following classes: Statement – the statement is sent to the database server each and every time. Prepared Statement – the statement is cached and then its execution path is pre-determined on the database server allowing it to be executed multiple times in an efficient manner. Callable Statement – used for executing stored procedures on the database. The statements such as INSERT, UPDATE and DELETE return an update count indicating the number of rows affected in the database. Besides this, no information is returned by these statements. JDBC row result set is returned by the query statements. We can retrieve individual columns in a row either by name or by column number. There may be any number of rows in the result set. The row result set has metadata that describes the names of the columns and their types. There is an extension to the basic JDBC API in the javax.sql. JDBC Driver JDBC driver is a software component which enables any java application to interact with database. There are four types of JDBC drivers: Type 1: developed in C/C++, platform dependent and they lack in security. Example: JDBC-ODBC. Type 2: Native – API driver, developed partially in C/C++ and Java, is platform dependent and is also not secure. Type 3: Purely Java-based drivers and use NET protocols, more flexible than the first two but not efficient as compared to the fourth type. Type 4: the purely java based driver that uses database native protocol. These drivers are more efficient and compatible but are not as flexible as compared to third type. Almost all the JDBC applications use this driver. In case of Type 4 driver, we need a connector.jar file which is provided by vendor. We have one more type of driver which is called as Internal JDBC driver, which is embedded with JRE in java enabled SQL databases. It is used for java stored procedures. It does not belong to the above classification. Java Database Connectivity Steps 1. Loading database driver First of all, driver class is loaded by calling Class.forName() with the driver class name as an argument. Driver class creates an instance once it gets loaded. We can connect to the database server through JDBC driver. The return type of the Class.forName (String ClassName) method is “Class”. Class is a class in java.lang package. Code: try { Class.forName("com.mysql.jdbc.Driver"); //load and register JDBC drivers } catch(Exception e) { System.out.println(“Can’t load the driver class. ”+e); } 2. Establish Connection Objects which can connect java applications to a JDBC driver is defined by JDBC DriverManager which can be considered as the essential component of the JDBC architecture. Whole management of JDBC drivers is managed by DriverManager class. Its getConnection() method is used to establish a connection to a database. A username, password, and a jdbc url is used to establish a connection to the database and then it returns a connection object. A Connection object provides metadata i.e. information about the database, tables, and fields. We need to pass username, password of database and a connection URL for getConnection. JDBC Connection URL varies depending on the database used. Following are for some of the popular databases: MySQL JDBC Connection URL: jdbc:mysql://hostname:3306/ – 3306 is MySQL default port Java DB Connection URL: jdbc:derby:testdb;create=true Microsoft SQL Server JDBC Connection URL - jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=testdb Oracle JDBC connection URL – jdbcracle:thinhostname:1521:testdb PostgresSQL JDBC Connection URL – jdbc:postgresql:template1 DB2 JDBC Connection URL – jdbc:db2:test Derby JDBC Connection URL – jdbc:derby:/test;create=true H2 Connection URL: jdbc:h2:tcp://localhost:9092/test In the given URL, dbtest is the name of the database form jdbc:subprotocol:subname which is to be connected with java application. As part of its initialization, the DriverManager class will attempt to load the driver classes referenced in the jdbc.drivers system property. A suitable driver is located by the DriverManager when getConnection is called with all those which are loaded initially and also explicitly using the same classloader as the current application. Code: try{ //define a url connection String url="jdbc:mysql:///dbtest"; //establish connection with the database conn=DriverManager.getConnection(url,"root","");//empty braces } catch( SQLException e ){ System.out.println( “Couldn’t get connection!” +e); } 3. Creating a jdbc statement object We can interact with the database once a connection is obtained. Methods are defined through the connection interface for interacting with the database after the connection gets established. We can instantiate statement object from our connection object by using the createStatement() method for executing SQL statements. There are three types of statements: Statement: It is an interface which creates an object that is used to execute static SQL statements and obtain the result produced by it.. Executes simple query without any parameter. createStatement() creates an SQL statement object. Code: Statement stmt=null; stmt=conn.createStatement(); PreparedStatement: It executes precompiled sql queries with or without parameters. A SQL statement is given to a PreparedStatement object, unlike a Statement object, when it is created. In most cases, this SQL statement is sent to the DBMS right away, where it is compiled. The PreparedStatement object contains not just a SQL statement, but a precompiled SQL statement. This means that when PreparedStatement is executed, the DBMS can just run PreparedStatement SQL statement without having to compile it first. PreparedStatement objects can be used for SQL statements with no parameters, but they must be used with SQL statements having parameters. One can use the same statement and supply it with different values every time it gets executed which is the main advantage of PreparedStatement. For example: prepareStatement(String sql) returns a new PreparedStatement object. PreparedStatement objects are precompiled SQL statements. Code: PreparedStatement pstmt=null; try { pstmt=conn.prepareStatement("select * from t1"); } catch(Exception e) { System.out.println(e); } Callable Statement: a callable statement creates CallableStatement object just as a Connection object creates the Statement and PreparedStatement objects which is used to execute a call to a database stored procedure. CallableStatement prepareCall(String sql) returns a new CallableStatement object. CallableStatement objects are SQL stored procedure call statements. 4. Executing an SQL statement Statement interface defines methods that are used to interact with database via the execution of SQL statements. The Statement class has three methods for executing statements: executeQuery(), executeUpdate(), and execute(). For DDL statements like INSERT, DELETE and UPDATE executeUpdate() method is used. For a SELECT statement, the method to use is executeQuery(). For statements that create or modify tables, the method to use is executeUpdate(). execute() executes an SQL statement that is written as String object. Statement: Code: stmt=conn.createStatement(); int i=stmt.executeUpdate("insert into t1 values(101, ’ram’, ’3000’); Prepared Statement: Code: PreparedStatement pstmt=null; try { pstmt=conn.prepareStatement("select * from t1"); rs=pstmt.executeQuery(); while(rs.next()) { System.out.println("ID Is:"+rs.getInt("id")); System.out.println("Name Is:"+rs.getString("name")); System.out.println("Sal:"+rs.getDouble("salary")); System.out.println("******************************"); } } catch(Exception e) { System.out.println(e); } 5.Closing connection: Closing the connection closes the object statement and ResultSet automatically. The close() method of Connection interface is used to close the connection. All the connections at the end of JDBC program every connection to the database and each database session has to be closed explicitly. One must not rely on the garbage collection especially when we do database programming as it is considered to be a poor programming practice. Any connection, which is associated with any connection object, must be closed using close() method. One must ensure that connection is closed and for that finally block must be inserted in the code. A finally block always executes, regardless if an exception occurs or not. To close above opened connection you should call close() method as follows: Code: finally { try { conn.close(); pstmt.close(); rs.close(); } catch(Exception e) { System.out.println(e); } } Complete example to select a record in database using PreparedStatement with Java Database Connectivity (JDBC) Code: import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; class JDBCDemo2 { public static void main(String ar[]) { Connection conn=null; PreparedStatement pstmt=null; try { Class.forName("com.mysql.jdbc.Driver"); String url="jdbc:mysql:///dbtest1"; conn=DriverManager.getConnection(url,"root",""); pstmt=conn.prepareStatement("insert into t1 values(?,?,?)"); pstmt.setInt(1,104); pstmt.setString(2,"Nikhil"); pstmt.setDouble(3,28000); int i = pstmt.executeUpdate(); if(i>0) { System.out.println("Record Inserted"); } else { System.out.println("Record Not Inserted"); } } catch(Exception e) { System.out.println(e); } finally { try { pstmt.close(); conn.close(); } catch(Exception ex) { System.out.println(ex); } } } } To generate the following output following steps are followed: Compile java file. In windows we set the class path as: java –cp .;“path” class file name. In Linux/Mac we set the class path as: java –cp .:“path” class file name. We have to specify the class path, as drivers must be loaded in order to execute the class file as shown below (cp-class path). Complete example to insert a record in database using Statement with Java Database Connectivity (JDBC) Code: import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; class JDBCDemo1 { public static void main(String ar[]) { Connection conn=null; Statement stmt=null; ResultSet rs=null; try { Class.forName("com.mysql.jdbc.Driver"); String url="jdbc:mysql:///dbtest1"; conn=DriverManager.getConnection(url,"root",""); stmt=conn.createStatement(); rs=stmt.executeQuery("select * from t1"); while (rs.next()) { System.out.print("ID is : "+rs.getInt("id")); System.out.print("\t Name is : "+rs.getString("name")); System.out.print(" \t \t Salary is : "+rs.getDouble("salary")); System.out.println(); } } catch(Exception e) { System.out.println(e); } finally { try { rs.close(); stmt.close(); conn.close(); } catch(Exception ex) { System.out.println(ex); } } } } Output:
