The JDBC API

The JDBC API

Important interface and classes of java.sql package

• java.sql.DriverManager:

- Handles the loading and unloading of the appropriate database drivers required to make the connection.

• java.sql.Connection:

- Handles connection.

• java.sql.Statement:

- Used for executing SQL statements using a connection.

• java.sql.ResultSet:

- It stores data returned from SQL query.

DATABASE OPERATION (CRUD - Create, Read, Update, Delete operation)

Steps for JDBC connection are:

STEP 1: Loading JDBC Drivers.

STEP 2: Creating connection to DBMS

STEP 3: Creating statements

STEP 4: Executing statements.

STEP 5: Closing the connection with DBMS.

1) Register the driver class:

• The forName() method of class is used to register the driver class.
• This method is used to dynamically load the driver class.
• Syntax of forName() method
• public static void forName(String className)throws ClassNotFoundException

• Example to register the OraclDriver class

                    Class.forName("oracle.jdbc.driver.OracleDriver");

Driver names for popular RDBMS are:

RDBMS	JDBC Driver name
MySQL	com.mysql.jdbc.Driver
ORACLE	oracle.jdbc.driver.OracleDriver
MS-ACCESS	sun.jdbc.odbc.JdbcOdbcDriver

                     

2) Create the connection object

• After you have loaded driver, you can establish connection using the DriverManager class (java.sql.DriverManager)
• The getConnection() method of DriverManager class is used to establish connection with database.
• Syntax of getConnection() method 
• public static Connection getConnetion(String url)throws SQLException
• public static Connection getConnection(String url, String name, String password) throws SQLException
•  Example tot establish connection with oracle database

          Connection c=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","password");

• Database URLs for popular RDBMS are:

RDBMS	Database URL format
MySQL	jdbc:mysql://hostname/databasename
ORACLE	jdbc:oracle:thin:@hostname:port:databasename
MS-ACCESS	jdbc:odbc:databasename

  

3) Create the statement object

• Once connection is obtained, we can interact with database.
• The createStatement() method of Connection interface is used to create statement.
• The object of statement is responsible to execute queries with the database.
• Syntax of createStatement() method 

            public Statement createStatement() throws SQLException

• Example to create statement object

            Statement s = con.createStatement();

4) Execute the query

• For executing query executeQuery() and executeUpdate() methods are used.

executeQuery() method:

• The executeQuery() method of Statement interface is used to execute queries to the database.
• This method returns the object of ResultSet that can be used to get all the records of a table. (SELECT method)
• Syntax of executeQuery() method

      public Resultset executeQuery(String sql)throws SQLException

• Example to execute query

                Resultset rs=s.executeQuery("select * from student");

executeUpdate() method:

• It executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement.
• Example:

              s.executeUpdate("insert into student  values(4,'abc')");

5) Close the connection object

• By closing connection object statement and ResultSet will be closed automatically.
• The close() method of Connection interface is used to close the connection.
• Syntax:

              public void close() throws SQLException

• Example:

                   1)   con.close();

                   2)   rs.close();