Java Database Connection-JDBC-tutorial

Java Database Connectivity (JDBC) Tutorial

 Table of Contents

1. Two-Tier architecture
2. Three-Tier architecture
3. Types of Drivers
4. JDBC Connection
5. java.sql Package Classes
   
6. JDBC program

 

In this tutorial, you are going to learn how a Java program connects to a database.  You will also learn about different types of architectures and also Drivers that are used to communicate java program with database.

Two-Tier architecture

in this tier, Java program and Database.
 

• Client program 

• Database

 

MoveTOC

Three-Tier architecture 

• Client program 

• Server  

• Database 

In this architecture, the client program interacts with the server program and the server program interacts with the database. The server program retrieves data and sends it to the client. 

 →Client Program (ex: HTML form)  can't directly interact with database

→Server program acts as a mediator between the client and database. It also processes the data as per the client request.(ex:Servlet, JSP) 

→ Database      ex: MySQL, MSSQLServer, Oracle. 

 

GoTop

 

 Types of Drivers

There are many possible implementations of JDBC drivers. These implementations are main categorized into 4 types.

Type 1: JDBC-ODBC Bridge driver (Bridge):

Drivers that implement the JDBC API as a mapping to another data access API, such as ODBC. Drivers of this type are generally dependent on a native library, which limits their portability. The JDBC-ODBC Bridge driver is an example of a Type 1 driver. 

Type 2: Native-API/partly Java driver (Native):

Drivers that are written partly in the Java programming language and partly in native code. These drivers use a native client library specific to the data source to which they connect.

Again, because of the code, their portability is limited. 

Type 3: All Java/Net-protocol driver (Middleware):

Drivers that use a pure Java client and communicate with a middleware server using a database-independent protocol. The middleware server then communicates to the client, requests to the data source. 

Type 4: All Java/Native-protocol driver (Pure):

Drivers that are pure Java and implement the network protocol for a specific data source. The client connects directly to the data source. All the JDBC related classes are available in the package called java.sql. 

reference

 

 

MoveTop

JDBC Connection: 

 

JDBC is a library, allows Java Program to Connect to DATABASE. To connect with any database using java program, we need to import a package called java.sql.

 

Classes available in java.sql Package

To work with database using java program mainly we use 4 classes.

1. DriverManager →Loads Specified Driver  
2. Connection→creates a connection with specified database 
3. Statement→represents any SQL statement(query) 
4. ResultSet→after executing, the query result is stored in the database

ResultSet object. Here, the statement can be any of these three interfaces. 

Statement→used to list database records

PreparedStatement→used with Insert, update, delete

CallableStatement→to call database procedures. 

 

Menu

 

These are the steps to be followed while writing JDBC program:

1. Loading Driver 
2. Establishing Connection 
3. Executing Statements 
4. Getting Results 
5. Closing Database Connection

1. Loading the Driver.

Loading the driver.
 

syn: Class.forName("DriverName"); 

Ex: Class.forName(sun.jdbc.odbc.JdbcOdbcDriver);

2. Making the Connection:

Using the DriverManager Class. 

 

It is responsible for finding a driver and connecting to the corresponding data source with the help of connection URL.

The getConnection() method used to establish a connection:

syn1: Connection conn = DriverManager.getConnection("connectionURL");  

 

ex1: connect=DriverManager.getConnection("jdbc:odbc:test","","");  

syn2: Connection con = DriverManager.getConnection( ("url+db","user","pwd") :

 

ex2: 

Connection con = DriverManager.getConnection("jdbc:odbc:testdb","myLogin","myPassword");

3. Createing statement:

Statement stmt = con.createStatement();
 

 

GotoTop

4. Obtaing result:

ResultSet rs = stmt.executeQuery("SELECT col1, col2, col3 FROM
Table1");
while(rs.next()) {
int x = rs.getInt(col1);
String s = rs.getString(col2);
float f = rs.getFloat(col3);
}

5. Closing Database Connection.

con.close();

The following program establishes a connection with MySQL Database.

 

Example:

Demo.java

 

import java.sql.Connection;
import java.sql.DriverManager;

public class Demo {

    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = 

            DriverManager.getConnection("jdbc:mysql://localhost/empdb", "root", "");

            System.out.println("driverfond");

            System.out.println("connected");
            conn.close();
        } catch (Exception e) {
            System.out.println(e.toString());
        }

    }
}

Program displays all the records from the employee table:

import java.sql.*;

public class JdbcTest {
public static void main(String args[]){
 

try{ 

     Class.forName("com.mysql.jdbc.Driver");
    

     Connection conn = 

        DriverManager.getConnection("jdbc:mysql://localhost/empdb", "root", "");
     

     Statement st = con.createStatement();
      

     ResultSet res = st.executeQuery("SELECT * FROM employee");

    while(rs.next()){
       

        System.out.println(rs.getInt(1));
        System.out.println(rs.getString(2));
        System.out.println(rs.getInt(3));
    }
    }
    catch(Exception ex){
        System.err.println("SQLException information");
    } 

    con.close();
    }
}

 

 

 

 Java Tutorial Home