JDBC Using Model Object and Singleton Class

This article explains how to perform JDBC operation using a Model object and a Singleton connection class from a MySQL database.

JDBC is an Application Programming Interface for Java which connects a Java application with a database to perform CRUD operations.

Pre requisite:

Model Class: The model class is highly used in the MVC pattern where it works as an intermediate medium between business logic and the view. Usually, a model contains some variables and methods of a specific entity.

Singleton Class: Singleton class is a software design pattern that ensures there will be one single instance of that class. There are multiple ways to achieve a singleton class.



Approach: We are going to create an Organization database that contains an Employee table. We will also create a java application that will connect with the Organization database. The connection will be established by a singleton class which will contain all the necessary driver information. From the java application, we will perform some data manipulation tasks like insert, delete, update and retrieve using a model object of Employee.

  1. Creating a MySQL database and a table:
    create database org;
    use org;
    create table employee(
        emp_id int auto_increment,
        emp_name varchar(400),
        emp_address varchar(400),
        primary key (emp_id)
    );
    
  2. Project setup in eclipse:
    • Create a project in eclipse named ‘jdbc’
    • Create a folder on that project named ‘jars’ and paste MySQL J Connector on that folder
    • Add jars to java build path
    • Create 4 package: com.jdbc.util, com.jdbc.dao, com.jdbc.model and com.jdbc.main

  3. Database connection: Create a singleton connection class DatabaseConnection in com.jdbc.util package. Use your MySQL username and password on the variable user and pass. Look carefully at the last part of the “url” variable. It is mandatory to keep the name the same as the database name. The name of my database is “org” that’s why I put “org” in the URL variable.

    DatabaseConnection Class

    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    package com.jdbc.util;
      
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
      
    public class DatabaseConnection {
      
        private static Connection con = null;
      
        static
        {
            String url = "jdbc:mysql:// localhost:3306/org";
            String user = "root";
            String pass = "root";
            try {
                Class.forName("com.mysql.jdbc.Driver");
                con = DriverManager.getConnection(url, user, pass);
            }
            catch (ClassNotFoundException | SQLException e) {
                e.printStackTrace();
            }
        }
        public static Connection getConnection()
        {
            return con;
        }
    }

    chevron_right

    
    

  4. Model class: Create a model class named Employee in com.jdbc.model package. It should contain all the attributes as variables.

    Model Class

    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    package com.jdbc.model;
      
    public class Employee {
      
        int emp_id;
        String emp_name;
        String emp_address;
      
        public Employee() {}
      
        public Employee(String emp_name, String emp_address)
        {
            this.emp_name = emp_name;
            this.emp_address = emp_address;
        }
      
        public int getEmp_id()
        {
            return emp_id;
        }
      
        public void setEmp_id(int emp_id)
        {
            this.emp_id = emp_id;
        }
      
        public String getEmp_name()
        {
            return emp_name;
        }
      
        public void setEmp_name(String emp_name)
        {
            this.emp_name = emp_name;
        }
      
        public String getEmp_address()
        {
            return emp_address;
        }
      
        public void setEmp_address(String emp_address)
        {
            this.emp_address = emp_address;
        }
      
        @Override
        public String toString()
        {
            return "Employee [emp_id=" + emp_id + ", 
    emp_name=" + emp_name + "
    emp_address=" + emp_address + "]";
        }
    }

    chevron_right

    
    

  5. Database Access Object(DAO): We will create an EmployeeDao interface and another class EmployeeDaoImplementation which implements EmployeeDao. This implemented class will be used as a DAO to perform CRUD operations. We will use PreparedStatement to execute the query. PreparedStatement has 3 special methods:
    • executeQuery(): used to retrive data
    • executeUpdate(): used to insert, update, delete
    • execute(): used to create

    EmployeeDao Interface

    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    package com.jdbc.dao;
      
    import java.sql.SQLException;
    import java.util.List;
      
    import com.jdbc.model.Employee;
      
    public interface EmployeeDao {
      
        public int add(Employee emp)
            throws SQLException;
        public void delete(int id)
            throws SQLException;
        public Employee getEmployee(int id)
            throws SQLException;
        public List<Employee> getEmployees()
            throws SQLException;
        public void update(Employee emp)
            throws SQLException;
    }

    chevron_right

    
    

    EmployeeDaoImplementation Class

    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    package com.jdbc.dao;
      
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.List;
      
    import com.jdbc.model.Employee;
    import com.jdbc.util.DatabaseConnection;
      
    public class EmployeeDaoImplementation
        implements EmployeeDao {
      
        static Connection con
            = DatabaseConnection.getConnection();
      
        @Override
        public int add(Employee emp)
            throws SQLException
        {
      
            String query
                = "insert into employee(emp_name, "
                  + "emp_address) VALUES (?, ?)";
            PreparedStatement ps
                = con.prepareStatement(query);
            ps.setString(1, emp.getEmp_name());
            ps.setString(2, emp.getEmp_address());
            int n = ps.executeUpdate();
            return n;
        }
      
        @Override
        public void delete(int id)
            throws SQLException
        {
            String query
                = "delete from employee where emp_id =?";
            PreparedStatement ps
                = con.prepareStatement(query);
            ps.setInt(1, id);
            ps.executeUpdate();
        }
      
        @Override
        public Employee getEmployee(int id)
            throws SQLException
        {
      
            String query
                = "select * from employee where emp_id= ?";
            PreparedStatement ps
                = con.prepareStatement(query);
      
            ps.setInt(1, id);
            Employee emp = new Employee();
            ResultSet rs = ps.executeQuery();
            boolean check = false;
      
            while (rs.next()) {
                check = true;
                emp.setEmp_id(rs.getInt("emp_id"));
                emp.setEmp_name(rs.getString("emp_name"));
                emp.setEmp_address(rs.getString("emp_address"));
            }
      
            if (check == true) {
                return emp;
            }
            else
                return null;
        }
      
        @Override
        public List<Employee> getEmployees()
            throws SQLException
        {
            String query = "select * from employee";
            PreparedStatement ps
                = con.prepareStatement(query);
            ResultSet rs = ps.executeQuery();
            List<Employee> ls = new ArrayList();
      
            while (rs.next()) {
                Employee emp = new Employee();
                emp.setEmp_id(rs.getInt("emp_id"));
                emp.setEmp_name(rs.getString("emp_name"));
                emp.setEmp_address(rs.getString("emp_address"));
                ls.add(emp);
            }
            return ls;
        }
      
        @Override
        public void update(Employee emp)
            throws SQLException
        {
      
            String query
                = "update employee set emp_name=?, "
                  + " emp_address= ? where emp_id = ?";
            PreparedStatement ps
                = con.prepareStatement(query);
            ps.setString(1, emp.getEmp_name());
            ps.setString(2, emp.getEmp_address());
            ps.setInt(3, emp.getEmp_id());
            ps.executeUpdate();
        }
    }

    chevron_right

    
    

  6. Test the application: Finally, its time to perform the CRUD application using all the methods of EmployeeDaoImplementation. Create a class Application in com.jdbc.main package.

    Driver code

    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    package com.jdbc.main;
      
    import java.sql.SQLException;
    import java.util.List;
      
    import com.jdbc.dao.EmployeeDaoImplementation;
    import com.jdbc.model.Employee;
      
    public class Application {
      
        public static void main(String[] args)
            throws SQLException
        {
      
            Employee emp = new Employee();
            emp.setEmp_name("Haider");
            emp.setEmp_address("Mars");
            EmployeeDaoImplementation empDao
                = new EmployeeDaoImplementation();
      
            // add
            empDao.add(emp);
      
            // read
            Employee e = empDao.getEmployee(1);
            System.out.println(e.getEmp_id() + " "
                               + e.getEmp_name() + " "
                               + e.getEmp_address());
      
            // read All
            List<Employee> ls = empDao.getEmployees();
            for (Employee allEmp : ls) {
                System.out.println(allEmp);
            }
      
            // update
            Employee tempEmployee = empDao.getEmployee(1);
            tempEmployee.setEmp_address("Asgard");
            empDao.update(tempEmployee);
      
            // delete
            empDao.delete(1);
        }
    }

    chevron_right

    
    

Output:

  • For the insert operation, you have to look to your Employee table in the Org database.
  • For read one item, use id to fetch data and print it to console.
  • For displaying all the items, just call the method and print it to console.
  • The update operation should change the updated value into the database.
  • The delete operation will delete the information of that id from the database.

design-pattern-img




My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.