This article explains how to perform JDBC operation using a
Model
object and a
connection class from a
.
is an
Application Programming Interface
for Java which connects a Java application with a database to perform
CRUD
operations.
Pre requisite:
- JDK 7+ (Click here to download)
- MySQL Database (Click here to download)
- MySQL J Connector (Click here to download)
- JDBC basic (Click here to learn JDBC)
- Eclipse or any other IDE
:
The model class is highly used in the
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 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.
-
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)
); -
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
-
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 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;
}
}
-
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 + "
]";
}
}
-
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 Interfacepackage
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 Classpackage
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();
}
}
-
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.