Open In App

JDBC Using Model Object and Singleton Class

Improve
Improve
Like Article
Like
Save
Share
Report

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




    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;
        }
    }

    
    

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

    Model Class




    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 + "]";
        }
    }

    
    

  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 retrieve data
    • executeUpdate(): used to insert, update, delete
    • execute(): used to create

    EmployeeDao Interface




    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;
    }

    
    

    EmployeeDaoImplementation Class




    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();
        }
    }

    
    

  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




    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);
        }
    }

    
    

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.


Last Updated : 31 Oct, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads