How to read, write, update and list data from a database using Java - JDBC

JDBC is a Java database connectivity technology to access databases. This technology is an API for the Java programming language that defines how a client may access a database. It provides methods for querying and updating data in a database A JDBC-to-ODBC bridge enables connections to any ODBC-accessible data source

The example given below demonstrate how to access a database using JDBC. In this example we uses three classes.


JDBCTest.java – Contains the main function and it uses the other two classes.

DBManager.java – This class is used to connect to database, read, write and update data and finally to close the database connection

Product.java – This is an abstraction of a Product and it contains methods to add, update, delete, and get details of products.

Entire code of this example is given below:

JDBCTest.java

import java.util.ArrayList;
import java.util.Iterator;

/**
 *
 * @author www.csnotes32.com
 */

public class JDBCTest {

       public static void main(String[] args) {
                Product p = new Product();
      
                //Opens database connection
                 DBManager.openConnection();
     
                  // Add new product
                 p.id(1);
                 p.title("Computer");      
                 p.add();
      
      
                //List products      
                ArrayList ps;
                Iterator e;
      
                ps = Product.get();      
                e = ps.iterator();
      
                 System.out.println("PID\tTitle");
                while (e.hasNext()) {
                     p = (Product) e.next();
          
                     System.out.println(p.id() + "\t" + p.title() );
               }      
              ps.clear();
      
      
             // Closes database connection
            DBManager.closeConnection();           
    }
}

DBManager.java

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

/**
 *
 * @author www.csnotes32.com
 */

public class DBManager {
    // Database settings
    public static final String DB_DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";
    public static final String DB_URL = "jdbc:odbc:dsnJDBC";
    public static final String DB_USER = "";
    public static final String DB_PWD = "";   
   
    // Connection object
    private static Connection m_cnn=null;
   
    // Function to open SQL connection
    public static boolean openConnection() {
       boolean retval = true;
    
       try {
           if (m_cnn ==null || m_cnn.isClosed()) {
                Class.forName(DBManager.DB_DRIVER);
                m_cnn = DriverManager.getConnection(DBManager.DB_URL, DBManager.DB_USER, DBManager.DB_PWD);                    
           }
       }
       catch (Exception e) {
           retval=false;
           System.out.println(e.toString());
       }
       
        return retval;
    }       
    // Function to close SQL connection
    public static boolean closeConnection() {
       boolean retval = true;
    
       try {
           if (!(m_cnn ==null || m_cnn.isClosed())) {
                m_cnn.close();
           }
       }
       catch (Exception e) {
           retval=false;
           System.out.println(e.toString());
       }
       
        return retval;
    }
    // Function to execute non select queries
    public static int  executeUpdate(String strSQL) {
       int retval = 0;    
    
       Statement st;      
      
       try{
            st = m_cnn.createStatement();
            retval = st.executeUpdate(strSQL);          
            st.close();                
       }
       catch (Exception e) {
           retval=0;
           System.out.println(e.toString());
       }
      
       return retval;
    }      
   
    // Function to execute  select query
    public static ResultSet  executeQuery(String strSQL) {      
       Statement st;  
       ResultSet rs = null;
      
       try{
           st = m_cnn.createStatement();                     
           rs = st.executeQuery(strSQL);                   
       }
       catch (Exception e) {
           rs = null;
           System.out.println(e.toString());
       }
      
       return rs;
    }
}

Product.java

import java.sql.ResultSet;
import java.util.ArrayList;

/**
 *
 * @author www.csnotes32.com
 */


public class Product {
    protected int m_id;
    protected String m_title;
   
    public void id(int m_id) {
        this.m_id = m_id;       
    }   
    public int id() {
        return this.m_id;       
    }   
    public void title(String  m_title) {
        this.m_title = m_title;       
    }   
    public String title() {
        return this.m_title;       
    }
         
    public boolean add() {
       // Function to add a product
       boolean retval = true;      
       String strSQL;
             
       try{          
           strSQL = "INSERT INTO tblProduct VALUES(" + this.m_id + ",'" + this.m_title + "');";
           DBManager.executeUpdate(strSQL);                             
       }
       catch (Exception e) {
           retval=false;
           System.out.println(e.toString());
       }
      
       return retval;
    }       
          
    public boolean update() {
       // Function to update a product
       boolean retval = true;     
       String strSQL;
      
       try{
           strSQL = "UPDATE tblProduct SET title='"  + this.m_title + "' WHERE id=" + this.m_id  ;
           DBManager.executeUpdate(strSQL);                 
       }
       catch (Exception e) {
           retval=false;
           System.out.println(e.toString());
       }      
       return retval;
    }  
       
    public static boolean  delete(int id) {
        // Function to delete a product
        boolean retval = true;       
        String strSQL;
      
       try{          
           strSQL = "DELETE FROM tblProduct  WHERE id=" + id  ;
           DBManager.executeUpdate(strSQL);                                        
       }
       catch (Exception e) {
           retval=false;
           System.out.println(e.toString());
       }      
       return retval;  
    }
           
    public static Product  get(int id) {     
       // Function to get a product
       ResultSet rs;
       Product p = null;
       String strSQL;
             
       try {          
           strSQL = "SELECT *  tblProduct  WHERE id=" + id  ;
           rs = DBManager.executeQuery(strSQL);
                     
           while (rs.next()) {
               p = new Product();
               p.m_id = rs.getInt(1);
               p.m_title = rs.getString(2);                      
           }                     
           rs.close();                     
       }
       catch (Exception e) {
           p = null;
           System.out.println(e.toString());
       }      
       return p;
    }    
              
    public static ArrayList  get() {  
       // Function to get all products
       ResultSet rs;
       Product p = null;   
       String strSQL;
      
       ArrayList ps = new ArrayList();                    
       try{                     
           strSQL = "SELECT *  FROM tblProduct;" ;
           rs = DBManager.executeQuery(strSQL);
          
           ps = new ArrayList();
          
           while (rs.next()) {
               p = new Product();
               p.m_id = rs.getInt(1);
               p.m_title = rs.getString(2);     
              
               ps.add(p);
           }          
           rs.close();                           
       }
       catch (Exception e) {
           p = null;
           ps = null;
           System.out.println(e.toString());
       }
      
       return ps;
    }   
           
}

Structure of Table used in this example

Tablename : tblProduct

FieldName    Data Type
pid                  numeric(5)
title                 varchar (50)


How to test on your computer?
 
1. Create a database in MS Access / MS SQL Server or similar product
2. Create the table  tblProduct in the above structure
3. Create a DSN to the database using Control Panel->Administrative Tools ->Data Sources (ODBC) .
4. Copy the above programs in to a single folder
5. Compile each class by using javac
6 . Run the program by  using java JDBCTest