JDBC Introduction and Architecture

Introduction to JDBC

• JDBC (Java Database Connectivity) is a Java API that allows Java programs to access and manipulate data in a database.
• JDBC is used to connect to a database, execute SQL statements, and retrieve results.

JDBC Architecture

• JDBC consists of two parts:
  • JDBC API: a purely Java-based API
  • JDBC Driver Manager: communicates with vendor-specific drivers that interact with the database
• Translation to vendor format is performed on the client, and no changes are needed on the server.

JDBC Drivers

• JDBC drivers implement the defined interfaces in the JDBC API for interacting with a database server.
• Database vendors and third parties can produce JDBC drivers.
• There are four types of JDBC drivers:
  • Type 1: JDBC-to-ODBC Bridge Driver
  • Type 2: Native-API, Part Java Drivers
  • Type 3: JDBC-Net Pure Java Drivers
  • Type 4: Native-protocol Pure Java Drivers

Registering JDBC Drivers

• To use a JDBC driver, you must register it with the DriverManager object.
• There are three ways to register a JDBC driver:
  • Using Class.forName(String driverName).newInstance()
  • Using DriverManager.registerDriver(Driver driverName)
  • Using the jdbc.drivers property

Developing JDBC Programs

• To develop a JDBC program, you need to:
  1. Load the JDBC driver
  2. Establish a connection to the database
  3. Create a statement
  4. Execute a statement
  5. Process the result set

Statements

• The Statement interface provides methods to execute queries with the database.
• There are three types of statements:
  • Statement: used for simple SQL statements
  • PreparedStatement: used for precompiled SQL statements with parameters
  • CallableStatement: used for stored procedures that may contain both input and output parameters

Prepared Statements

• PreparedStatement enables you to create parameterized SQL statements.
• PreparedStatement objects are created using the prepareStatement method in the Connection interface.
• You can set parameters using set methods (e.g., setString, setInt) before executing the statement.

Result Sets

• A ResultSet object represents the result set of a database query.
• ResultSet interface methods can be broken down into three categories:
  • Navigational methods: used to move the cursor around
  • Get methods: used to view the data in the columns of the current row
  • Update methods: used to update the data in the columns of the current row### Viewing a Result Set
• The ResultSet interface contains dozens of methods for getting the data of the current row.
• There are two versions of each get method: one that takes a column name and one that takes a column index.
• Examples of get methods include:
  • getInt(String columnName)
  • getInt(int columnIndex)
  • getString(int columnIndex)
  • getBoolean(int columnIndex)
  • getByte(int columnIndex)
  • getShort(int columnIndex)
  • getLong(int columnIndex)
  • getFloat(int columnIndex)
  • getDouble(int columnIndex)
  • getDate(int columnIndex)
  • getTime(int columnIndex)
  • getTimestamp(int columnIndex)

Updating a Result Set

• The ResultSet interface contains a collection of update methods for updating the data of a result set.
• There are two update methods for each data type: one that takes a column name and one that takes a column index.
• Examples of update methods include:
  • updateString(int columnIndex, String s)
  • updateString(String columnName, String s)
• Updating a row in the result set changes the columns of the current row in the ResultSet object, but not in the underlying database.
• To update changes to the row in the database, you need to invoke one of the following methods:
  • updateRow()
  • deleteRow()
  • refreshRow()
  • cancelRowUpdates()
  • insertRow()

ResultSet Methods

• isNull(column) checks if a field is null.
• getMaxRows() and setMaxRows() set the maximum number of rows a ResultSet may contain.

Creating Statements with Desired ResultSet

• JDBC provides three methods to create statements with desired ResultSet:
  • createStatement(int RSType, int RSConcurrency)
  • prepareStatement(String SQL, int RSType, int RSConcurrency)
  • prepareCall(String sql, int RSType, int RSConcurrency)

Type of ResultSet

• The possible RSType values are:
  • ResultSet.TYPE_FORWARD_ONLY
  • ResultSet.TYPE_SCROLL_INSENSITIVE
  • ResultSet.TYPE_SCROLL_SENSITIVE

Concurrency of ResultSet

• The possible RSConcurrency values are:
  • ResultSet.CONCUR_READ_ONLY
  • ResultSet.CONCUR_UPDATABLE

Batch Processing in JDBC

• Allows you to group related statements into a batch and submit them with one call to the database.
• The java.sql.Statement and java.sql.PreparedStatement interfaces provide methods for batch processing.
• Advantage of batch processing: fast performance.
• Methods for batch processing:
  • void addBatch(String query)
  • int[] executeBatch()

CallableStatement

• The CallableStatement interface is designed to execute SQL-stored procedures.
• Procedures may have IN, OUT, or IN OUT parameters.
• Example of a stored procedure in Oracle PL/SQL:
  • create or replace procedure sampleProcedure (p1 in varchar, p2 out number, p3 in out integer) is ...

Learn about Java Database Connectivity (JDBC), a Java API that interacts with databases, and its architecture comprising of JDBC API and JDBC Driver Manager.