Prev
Home
Next
Menu

Beginning Java Database Connectivity

Java applications cannot communicate directly with a RDBMS to submit data and retrieve the results of queries. This is because a RDBMS can interpret only SQL statements and not the Java language statements. So, we need some kind of mechanism to translate Java statements into SQL statements. In Java programming domain, the API that enables database access and manipulation is called Java Database Connectivity (JDBC).

JDBC API has two parts: the JDBC core API and the JDBC optional package API. The JDBC Core API is the main part of JDBC and it takes the form of the classes and interfaces in the java.sql package. The JDBC optional package API is specified in the javax.sql package and it supports connection pooling, distributed transactions, row sets, and so forth.

When we develop JDBC applications, we need to use JDBC drivers to convert queries into a form that a particular database can interpret. The JDBC driver also retrieves the result of SQL statements and converts the result into equivalent JDBC API class objects that the Java application uses. As the JDBC driver only takes care of interactions with the database, any change made to the database does not affect the application.

Here, we begin with JDBC programming with JDK 8 and MySQL 5.0 RDBMS. This topic is also described with an in-depth study in the section named Advanced Java programming.

 

Developing Java Application for JDBC

MySQL is a popular RDBMS package now-a-days. We would like to establish a database connection with MySQL and to access and manipulate records from MySQL tables. We are using here MySQL 5.0 and the name of the database driver (Type 4 driver) is mysql-connector-java-5.1.5-bin.jar. For making JDBC connection we have created a Java program named JdbcTest.java. This program and the JAR file are placed under a directory named  “C:\Users\techguru” which is shown below:

 

 
The following is the list of commands that are executed in the command prompt (cmd) to create database and table and to populate table with records:
mysql> create database test;

mysql> use test;

mysql> create table student(roll int(8), name varchar(20), 
                            stream varchar(5), grade decimal(3,2), primary key(roll));

mysql> insert into student values(101, "Suharta Banerjee", "CSE", 7.85);

mysql> insert into student values(102, "Ruparna Bose", "ECE", 8.15);

mysql> insert into student values(103, "Tathagata Das", "IT", 9.25);

mysql> select * from student;
+------+------------------+--------+-------+
| roll | name             | stream | grade |
+------+------------------+--------+-------+
| 101  | Suharta Banerjee | CSE    | 7.85  |
| 102  | Ruparna Bose     | ECE    | 8.15  |
| 103  | Tathagata Das    | IT     | 9.25  |
+------+------------------+--------+-------+

 
 
The complete code is given below:~

JdbcTest.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JdbcTest {

    public static void main(String[] args) {
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            //Step 1: Load the Driver class
            Class.forName("org.gjt.mm.mysql.Driver").newInstance();

            //Step 2: Establish the Connection
            String url = "jdbc:mysql://localhost:3306/test";
            String username = "root";
            String password = "system";
            con = DriverManager.getConnection(url, username, password);
            if (con != null) {
                System.out.println("Successfully connected to " + "MySQL Server 5.0 using TCP/IP..");
            }

            //Step 3: Create the Statement
            stmt = con.createStatement();

            //Step 4: Execute query to get results
            rs = stmt.executeQuery("select * from student");
            while (rs.next()) {
                System.out.println("Roll = " + rs.getInt(1) + "; Name = " + rs.getString(2) + "; Stream = " + rs.getString(3) + "; Grade = " + rs.getDouble(4));
            }
        } 
        catch (Exception e) {
            System.out.println("Exception: " + e.getMessage());
        } 
        finally {
            //Step 5: Close the Connection
            try {
                if (con != null) {
                    con.close();
                }
            } 
            catch (SQLException e) {  
                System.out.println("Exception: " + e.getMessage());
            }
        }

    } //end of main()

} //end of class definition


 
The output of the code from the command prompt is given below:~
C:\Users\techguru> set classpath=C:\Users\techguru\JdbcApp\mysql-connector-java-5.1.5-bin.jar;

C:\Users\techguru> javac JdbcTest.java

C:\Users\techguru> java JdbcTest

Successfully connected to MySQL Server 5.0 using TCP/IP..
Roll = 101; Name = Suharta Banerjee; Stream = CSE; Grade = 7.85
Roll = 102; Name = Ruparna Bose; Stream = ECE; Grade = 8.15
Roll = 103; Name = Tathagata Das; Stream = IT; Grade = 9.25

 

NOTE: The JDBC concept is also described with an exhaustive study in the section named Advanced Java programming. There we will learn how to create Servlet and JSP programs for web application development using MySQL 8.0.15 (a newer version of MySQL RDBMS).

 

 

 

Prev
Home
Next