Open In App

JDBC Using Model Object and Singleton Class

Last Updated : 31 Oct, 2023
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.


Similar Reads

Spring Boot - Spring JDBC vs Spring Data JDBC
Spring JDBC Spring can perform JDBC operations by having connectivity with any one of jars of RDBMS like MySQL, Oracle, or SQL Server, etc., For example, if we are connecting with MySQL, then we need to connect "mysql-connector-java". Let us see how a pom.xml file of a maven project looks like. C/C++ Code &lt;?xml version=&quot;1.0&quot; encoding=
4 min read
Difference Between Singleton Pattern and Static Class in Java
Singleton Pattern belongs to Creational type pattern. As the name suggests, the creational design type deals with object creation mechanisms. Basically, to simplify this, creational pattern explains to us the creation of objects in a manner suitable to a given situation. Singleton design pattern is used when we need to ensure that only one object o
6 min read
Java Program to Demonstrate the Nested Initialization For Singleton Class
A Singleton Class is capable of producing just a single instance. Every Singleton class has a getInstance method which returns its object. When the getInstance method is called for the first time, an object of the class is generated, stored, and then returned. On subsequent calls to getInstance, the same object generated earlier gets returned. Nest
4 min read
Java Program to Demonstrate the Double-Check Locking For Singleton Class
One of the key challenges faced by junior developers is the way to keep Singleton class as Singleton i.e. the way to prevent multiple instances of Singleton class. Double checked locking of Singleton is a way to make sure that only one instance of Singleton class is created through an application life cycle. In double-checked locking, code checks f
3 min read
Singleton Class in Android
The Singleton Pattern is a software design pattern that restricts the instantiation of a class to just "one" instance. It is used in Android Applications when an item needs to be created just once and used across the board. The main reason for this is that repeatedly creating these objects, uses up system resources. The identical object should ther
4 min read
How to Create a Statement Object in JDBC ?
JDBC stands for Java Database Connectivity. It is used in Java-based API which allows Java Applications to interact and perform operations on relational databases. Creating a "Statement" object in JDBC is an important step while dealing with a database in Java. A "Statement" object is used to execute SQL queries in Java Programming Language. In thi
4 min read
Advantages and Disadvantages of using Enum as Singleton in Java
Enum Singletons are new ways of using Enum with only one instance to implement the Singleton pattern in Java. While there has been a Singleton pattern in Java for a long time, Enum Singletons are a comparatively recent term and in use since the implementation of Enum as a keyword and function from Java 5 onwards. Advantages of using Enum as Singlet
3 min read
Object Model | Object Oriented Analysis & Design
Object-Oriented Programming (OOP) is a fundamental paradigm in modern software development that has transformed the way we design, build, and maintain software systems. OOP is centered around the concept of objects, which are self-contained, reusable units that encapsulate both data and the operations that can be performed on that data. This approa
12 min read
Singleton and Prototype Bean Scopes in Java Spring
Bean Scopes refers to the lifecycle of Bean that means when the object of Bean will be instantiated, how long does that object live, and how many objects will be created for that bean throughout. Basically, it controls the instance creation of the bean and it is managed by the spring container.Bean Scopes in Spring The spring framework provides fiv
8 min read
Difference Between Singleton and Factory Design Pattern in Java
Design patterns are essential tools for creating maintainable and scalable software. Two commonly used design patterns in Java are the Singleton and Factory patterns. This article provides an introduction to the Singleton and Factory design patterns with examples. Singleton Design Pattern in JavaThe Singleton pattern is a creational design pattern
3 min read
Practice Tags :