JDBC Tutorial

JDBC

Introduction to JDBC:

JDBC stands for Java Database Connectivity. JDBC is a Java API used in providing interaction between Java application and database. In this API, consists of following classes and interfaces:

  • Classes:

    1. DriverManager
    2. Blob
    3. Clob
    4. Types

  • Interfaces:

    1. Driver
    2. Connection
    3. Statement
    4. PreparedStatement
    5. CallableStatement
    6. ResultSet
    7. ResultSetMetaData
    8. DatabaseMetaData
    9. RowSet

The following steps are the sequential order to connect JDBC:

  1. Import SQL package - First we need to import sql package.
    import java.sql.*;
  2. Load or register driver - After importing package, we need to load or register required database driver using Class.forName() statement.
    Class.forName(Database Driver Name);
  3. Establish the connection - DriverManager.getConnection() method is used to create connection object. getConnection() method accepts three parameters: URL of database, user name and password.
    Connection conn = DriverManager.getConnection(URL, username, password);
  4. Create a statement - Statement, PreparedStatement and CallableStatement are the interfaces that provides various methods to execute SQL commands.
    Statement stmt = conn.createStatement();
  5. Execute a query or update - Now executing SQL queries we have different methods: execute(), executeUpdate() and executeQuery() for different SQL commands.
    boolean execute(String SQL); //for create, alter and drop commands
    int executeUpdate(String SQL); //for insert, update and delete commands
    ResultSet executeQuery(String SQL); //for select command
  6. Process the results - executeQuery() returns result set means table of data where each row represents record and column represents field in the database. After getting result set cursor points to the current row, to navigate use next(), previous(), first() and last() methods. To retrieve data using getInt(), getString(), getDouble() etc., which depends type of column data.
    ResultSet rs = stmt.executeQuery(String SQL);
  7. Close the connection - After performing all the opeartions we need to close the connections to release the memory resource.
    rs.close(); //Close ResultSet
    stmt.close(); //Close Statement
    conn.close(); //Close Connection

Example-1: Below Java program demonstrates how to establish connection with MySQL database.

DBConnection.java
import java.sql.*;
public class DBConnection {
  public static void main(String[] args) throws ClassNotFoundException {
    try {
      //Establish connection
      Connection conn = null;
      conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/college", "root", "admin");
      if(conn==null) {
        System.out.println("Database error");
        System.exit(0);
      }
      System.out.println("Connection Success");
      //Close connection
      conn.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
}
Connection Success

Example-2: Below Java program demonstrates to create a table MySQL database.

CreateTable.java
import java.sql.*;
public class CreateTable {
  public static void main(String[] args) throws ClassNotFoundException{
    try {
      //Establish connection
      Connection conn = null;
      conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/college", "root", "admin");
      if(conn==null) {
        System.out.println("Database error");
        System.exit(0);
      }
      //Creating statement
      Statement stmt = conn.createStatement();
      String q = "create table IF NOT EXISTS student(id int primary key auto_increment, rollno varchar(10), studentname varchar(30), contact varchar(100));";
      //Executing query
      stmt.execute(q);
      System.out.println("Student table created in college database.");
      //Close connection
      stmt.close();
      conn.close();
    }catch (SQLException e) {
      e.printStackTrace();
    }
  }
}
Student table created in college database

Example-3: Below Java program demonstrates to insert record into table in MySQL database.

InsertRecord.java
import java.sql.*;
public class InsertRecord {
  public static void main(String[] args) throws ClassNotFoundException {
    try {
      //Establish connection
      Connection conn = null;
      conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/college", "root", "admin");
      if(conn==null) {
        System.out.println("Database error");
        System.exit(0);
      }
      //Creating statement
      Statement stmt = conn.createStatement();
      String q = "insert into student values(null, 'cs-101', 'Nani', 9876543210);";
      //Executing query
      int insertSQL = stmt.executeUpdate(q);
      if(insertSQL==0) {
        System.out.println("Record insertion failed.");
        System.exit(0);
      }
      System.out.println("Record inserted");
      //Close connection
      stmt.close();
      conn.close();
    }catch(SQLException e) {
      e.printStackTrace();
    }
  }
}
Record inserted

Example-4: Below Java program demonstrates to insert multiple records into table in MySQL database.

InsertMultipleRecords.java
import java.sql.*;
public class InsertMultipleRecords {
  public static void main(String[] args) throws ClassNotFoundException {
    try {
      //Establish connection
      Connection conn = null;
      conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/college", "root", "admin");
      if(conn==null) {
        System.out.println("Database error");
        System.exit(0);
      }
      //Creating statement
      Statement stmt = conn.createStatement();
      String q1 = "insert into student values(null, 'cs-102', 'Siva', 8765432109);";
      String q2 = "insert into student values(null, 'cs-103', 'Gopi', 7654321098);";
      stmt.addBatch(q1);
      stmt.addBatch(q2);
      //Executing set of queries
      stmt.executeBatch();
      System.out.println("Records inserted.");
      //Close connection
      stmt.close();
      conn.close();
    }catch(SQLException e) {
      e.printStackTrace();
    }
  }
}
Records inserted

Example-5: Below Java program demonstrates to fetch records from table in MySQL database and display.

FetchRecords.java
import java.sql.*;
public class FetchRecords {
  public static void main(String[] args) throws ClassNotFoundException {
    try {
      //Establish connection
      Connection conn = null;
      conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/college", "root", "admin");
      //Creating statement
      Statement stmt = conn.createStatement();
      String q = "select * from student;";
      //Executing query and store the result
      ResultSet rs = stmt.executeQuery(q);
      //Fetching Records
      while(rs.next()) {
        System.out.println("Student-" +rs.getInt("id"));
        System.out.println("\tRoll No.: " +rs.getString("rollno"));
        System.out.println("\tStudent Name: " +rs.getString("studentname"));
        System.out.println("\tContact: " +rs.getLong("contact"));
      }
      //Close connection
      rs.close();
      stmt.close();
      conn.close();
    }catch(SQLException e) {
      e.printStackTrace();
    }
  }
}
Student-1
    Roll No.: cs-101
    Student Name: Nani
    Contact: 9876543210
Student-2
    Roll No.: cs-102
    Student Name: Siva
    Contact: 8765432109
Student-3
    Roll No.: cs-103
    Student Name: Gopi
    Contact: 7654321098

Join in Telegram Channel: @GIT_TRAINING9

Posted by