JDBC Tutorial


JDBC Programming

Before Going further , you need to examine 2 packages for database communication. i.e

  • java.sql
  • javax.sql

These 2 packages has common set of classes and interfaces , to connect to any relational database. These(classes and interfaces) are specifications only. Actual implementation done at JDBC driver jar side. Applications should use vendor specific JDBC driver.

Java Clients Can be SWING Application or AWT application, Android App or webAPP.

java.sql package is core API for JDBC, mainly used in Desktop Applications/Stand alone applications
javax.sql package has classes for connection pool,rowsets etc., used in WebApplications or Server side applications

Connecting to Database


Steps required to connect to Database

  1. Register JDBC Driver
  2. Connect to Database
  3. Create Statement for SQL processing
  4. Execute the SQL Statement
  5. Close the Resources

1. Register Database Driver

       Jdbc driver should be registered with DriverManager class. Same Driver is responsible for database communication and processing SQL statements. Registering driver is essential for external running databases like Oracle,mySQL, DB2 etc., For in-memory databases Driver Registeration may be optional. If you are using older version of java java 6 below versions this step is mandatory, for other java releases this step is optional.

Registering Oracle JDBC Driver

there are 2 ways to register JDBC Driver

1. Class.forName("oracle.jdbc.driver.OracleDriver")
2. DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver())

This class is located in ojdbc8.jar

JDBC Driver downloadable from oracle.com website


2. Connect to Database

     Connecting to Database requires JDBC URL, and username nd password. JDBC URL differs from one database to another database. Programmer has to check corresponding database vendor specific website for JDBC URL format:

for ex: Oracle has thin and thick JDBC Drivers. thin drivers are pure java drivers. Thick drivers depends on odbc(open database connectivity). Latest versions of java uses thin drivers.

oracle jdbc format: jdbc:oracle:thin:@localhost:1521:xe

jdbc:oracle is a protocol which commuincates with Oracle server.
thin tells, thin JDBC driver, i.e pure java implementation.
localhost is a machine name.
1521 is default port number for Oracle
here XE is the database instance name.


3. Create Statement for SQL processing

      Connection Object has method called CreateStatment, which returns statement object. This Statement Object reponsible for sending SQL statements to Database Server. Database Server parses the Query, checks syntax and semantics of the QUERY and finally executes the SQL statement.

If there is any Syntax errors in the SQL Query java.sql.SQLSyntaxErrorException: will be fired.

Same statement object can be used to for DDL command and DML commands. for ex: Programmer can create a one Table and fetch data from another Table etc.,


      Statement stmt = conn.createStatement();


4. Execute the SQL Statement.


        Statement Object has method called executeQuery to execute DRL command. i.e SELECT statement. executeQuery executes SQL Query ,returns single resultset Object.

ResultSet object is a snap shot of the given SQL statement. It has rows and columns and Data. Each column has type. i.e Resultset Object has Data+metadata. And also Resultset object has cursor object, Intially it points to before the first row of the data. Using next method cursor moves to first row of the data. Once cursor points to first row of the Data, programmer can fetch records. Each SQL type has equivalent Java class.

   for ex: Integer datatype in Oracle, rs.getInt returns Integer value.
for Date class in Oracle equivalent class in Java is java.util.Date class rs.getDate() etc.,

Execute DDL command

        Boolean ret=stmt.execute("create table table_name(col1 type, col2 type ...)");

Execute SQL statement

        ResultSet rs=stmt.executeQuery("select * from Table_name");



5. Close the Resources

        Once Database operations are done, Programmer has to close the resources.
using stmt.close()
conn.close()

JDBC Program to Connect to Oracle Database

import java.sql.*;

public class JDBCDemo{

        public static void main(String[] args)
        {
                String URL = "jdbc:oracle:thin:@localhost:1521/XE";
                String user = "scott";
                String passwd = "tiger";

                String sql =  " SELECT * FROM EMP " ;

                //STEP 1
                try
                {
                        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

                //STEP 2
                try( Connection conn = DriverManager.getConnection( URL, user,  passwd ))
                {
                        //STEP 3
                        try( Statement stmt = conn.createStatement() )
                        {
                                //STEP 4
                                ResultSet rs = stmt.executeQuery( sql );

                               while (  rs.next() )

                                {
                                        System.out.println( rs.getInt(1) + " "+ rs.getString(2) +" "+
                                        rs.getString(3) +" "+ rs.getInt(4) +" "+ rs.getDate(5) +" "+
                                        rs.getFloat(6) +" "+rs.getFloat(7)+ "  "+rs.getInt(8) );

                                }
                        }


                }
        }
                catch(SQLException ex)
                {
                        System.out.println(ex);

                }
        }
}
		

Compile and Execute JDBCDemo.java from command prompt(Linux)

       javac to compile java program, java to execute compiled program. Here you need to set classpath for jdbc driver. In Linux -cp .~/jdbctutorial/ojdbc8.jar , assuming ojdbc8.jar file stored in home directory/jdbctutorial directory. Both compile time and execution time jar file must be specified in classpath. Because Driver class located in ojdbc8.jar file. i.e DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

 $javac  -cp .:~/jdbctutorial/ojdbc8.jar JDBCDemo.java
 $java  -cp .:~/jdbctutorial/ojdbc8.jar JDBCDemo

Passing URL, username and password in Command Prompt

$javac  JDBCDemo.java
$java .:~/jdbctutorial/ojdbc8.jar -Ddriver=oracle.jdbc.driver.OracleDriver -Duser=scott -Dpassword=tiger JDBCDemo

Using java.util.Properties for storing sensitive information URL,username and password

Using Eclipse IDE to Compile & Execute

Using Eclipse IDE application.properties file to store Database URL, username and Password

ADS