JDBC

Concepts :

1.  Java DataBase Connectivity
2.  JDBC API
3.  Different drivers
4.  Set up a connection to a database from Java
5.  Create a database application

JDBC :

• The JDBC(Java Database Connectivity) API helps a java program to access a database in a standard way
• JDBC is a specification that tells the database vendors how to write a driver program to interface Java programs with their database.
• A Driver written according to this standard is called the JDBC Driver
• All related classes and interfaces are present in the java.sql package
• All JDBC Drivers implements the interfaces of java.sql

JDBC Drivers

• There are 4 types of drivers –Type1, Type2, Type3, Type 4

• Type1 Driver (JDBC-ODBC Bridge Driver)
• Type2 Driver (Native-API Driver)
• Type3 Driver (Network-protocol Driver)
• Type4 Driver(Native-protocol Driver)

Database interaction

•     The steps involved in a database interaction are:

• Loading the specific Driver
• Making a connection to the Database
• Sending SQL statements to the Database
• Processing the results
• Statement

                     A statement object is used to send SQL  statements to a Database.

•  Three kinds :

 Statement
-    Execute  simple SQL without parameters
 PreparedStatement
-    Used for pre-compiled SQL statements with or without parameters
 CallableStatement
-    Execute a call to a database stored procedure or function

JDBC – Classes and Interfaces

   DriverManager  class –
-    Manage all the JDBC Drivers that are loaded in the memory
-    Helps in dynamic loading of Drivers
              
    Methods in DriverManager class –
-    getConnection(): to establish a connection to a database.
                            Connection getConnection(String url, Properties info)
                            Connection getConnection(String url)
Connection getConnection(String url , String userID, String password)
-    registerDriver(java.sql.Driver)

Interfaces in JDBC :

1. Connection interface – defines method for interacting with the database via the established connection.
-    A connection object represents a connection with a database
-    A connection session includes the SQL statements that are executed and the results that are returned over that connection.
-    A single application can have one or more connections with a single database, or it can have many connections with many different databases.
The different methods of connections interfaces are:
-    close() – closes the database connection
-    createStatement() – creates an SQL statement object
-    prepareStatement() – creates an SQL PreparedStatement object. (preparedStatement objects are precompiled SQL statements)
-    prepareCall() – creates an SQL CallableStatement object using an SQL string. (CallableStatement objects are stored procedure call statements)

2. Statement interface – defines methods that are used to interact with database via the execution of SQL statements.
The different methods are:
-    executeQuery(String sql) – executes an SQL statement (SELECT)  that queries a database and returns a ResultSet object.
-    executeUpdate(String sql) – executes an SQL statement (INSERT, UPDATE, Or DELETE) that updates the database and returns an int, the row count associated with the SQL statement
-    execute(String sql) – executes an SQL statement that is written as String object
-    getResultSet() – used to retrieve the ResultSet object

3. ResultSet Interface – maintains a pointer to a row within the tabular results. The next() method is used to sucessively step through the rows of the tabular results.
The different methods are:
-    getBoolean(int) – get the value of a column in the current row as a Java Boolean.
-    getByte(int) - get the value of a column in the current row as a Java byte.
-    getDouble(int) - get the value of a column in the current row as a Java double.
-    getInt(int) - get the value of a column in the current row as a Java int.
 

4. PreparedStatement

•     PreparedStatement interface – helps us to work with precompiled SQL statements
•     Precompiled SQL statements are faster than normal statements
•     So, if a SQL statement is to repeated, it is better to use PreparedStatement
•     Some values of the statement can be represented by a ? character which can be replaced later using setXXX method.

5.    CallableStatement interface – helps us to call stored procedures and functions

  CallableStatement callableStatement = callableStatement.prepareCall(“execute proc ?”);
  CallableStatement.setInt(50);
CallableStatement.execute();

• The out parameters are to be registered

                   -CallableStatement.registerOutParameter(int parameterIndex, int SQL Type);

• To get the value stored in the out parameter—

                   -CallableStatement.getXXX(int parameterIndex);

Using CallableStatement

•  Example – calling a stored procedure named GetSalary. The procedure queries on the Employee table and returns the salary of an employee. It has one input parameter that takes the EmpCode and an out parameter that returns the salary

6.ResultSetMetaData Interface – holds information on the types and properties of the columns in a ResultSet. Provides information about the database as a whole. Constructed from the connection object

• The different methods are:

-    getColumnName()
-    getColumnType()
-    getColumnLabel(count)

Transaction Management using JDBC
    ---By default, auto commit mode of the connection reference is set to true
    ---A transaction can be done as follows using methods of the Connection interface

Summary

   Java Data Base Connectivity
-    JDBC API
-    Different Drivers
-    Set up a connection to a database from Java
-    Create a database application