The example is simple CRUD operation with Java JDBC and
MySQL Database. Add “mysql-connector-xxx.jar” into classpath.
DatabaseUtils.java
|
package
com.metadata.jdbc.connection;
import
java.sql.Connection;
import
java.sql.DriverManager;
import
java.sql.SQLException;
public class DatabaseUtils
{
private static String url = "jdbc:mysql://localhost:3306/<<dbname>>";
public static Connection
getConnection() {
Connection con = null;
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(url, "<<username>>", "<<password>>");
} catch (SQLException
e) {
e.printStackTrace();
} catch
(ClassNotFoundException e) {
e.printStackTrace();
}
return con;
}
}
|
Employee.java
|
package
com.metadata.jdbc.entity;
public class Employee {
private int employeeId;
private String employeeName;
private long employeeSalary;
private String employeePhoneNumbers;
private String employeeAddress;
private String employeeDepartment;
public Employee() {
}
public
Employee(String emp_name, long emp_salary, String emp_phone, String
emp_address, String emp_dept) {
this.employeeName = emp_name;
this.employeeSalary = emp_salary;
this.employeePhoneNumbers = emp_phone;
this.employeeAddress =
emp_address;
this.employeeDepartment = emp_dept;
}
public Employee(int emp_id,
String emp_name, long emp_salary, String emp_phone, String emp_address, String
emp_dept) {
this.employeeId = emp_id;
this.employeeName = emp_name;
this.employeeSalary = emp_salary;
this.employeePhoneNumbers = emp_phone;
this.employeeAddress =
emp_address;
this.employeeDepartment = emp_dept;
}
public int
getEmployeeId() {
return employeeId;
}
public void
setEmployeeId(int employeeId) {
this.employeeId = employeeId;
}
public String
getEmployeeName() {
return employeeName;
}
public void
setEmployeeName(String employeeName) {
this.employeeName =
employeeName;
}
public long
getEmployeeSalary() {
return employeeSalary;
}
public void
setEmployeeSalary(long employeeSalary) {
this.employeeSalary =
employeeSalary;
}
public String
getEmployeePhoneNumbers() {
return employeePhoneNumbers;
}
public void
setEmployeePhoneNumbers(String employeePhoneNumbers) {
this.employeePhoneNumbers =
employeePhoneNumbers;
}
public String getEmployeeAddress()
{
return employeeAddress;
}
public void
setEmployeeAddress(String employeeAddress) {
this.employeeAddress =
employeeAddress;
}
public String
getEmployeeDepartment() {
return employeeDepartment;
}
public void
setEmployeeDepartment(String employeeDepartment) {
this.employeeDepartment =
employeeDepartment;
}
}
|
EmployeeDAO.java
|
package
com.metadata.jdbc.dao;
import
java.sql.Connection;
import
java.sql.ResultSet;
import
java.sql.SQLException;
import
java.sql.Statement;
import
java.util.ArrayList;
import
java.util.List;
import
com.metadata.jdbc.connection.DatabaseUtils;
import
com.metadata.jdbc.entity.Employee;
public class EmployeeDAO {
private Connection con = null;
private Statement stmt;
public void
insert(Employee emp) {
String emp_Name =
emp.getEmployeeName();
long emp_Salary =
emp.getEmployeeSalary();
String emp_PhoneNumbers =
emp.getEmployeePhoneNumbers();
String emp_Address =
emp.getEmployeeAddress();
String emp_Department = emp.getEmployeeDepartment();
try {
con =
DatabaseUtils.getConnection();
stmt = con.createStatement();
stmt.executeUpdate("INSERT
INTO employee(emp_name, emp_salary, emp_phone, emp_address,
emp_department)"
+ "VALUES('" + emp_Name + "'," + emp_Salary
+ ",'" +
emp_PhoneNumbers + "','" + emp_Address + "','" +
emp_Department + "')");
} catch (SQLException
e) {
e.printStackTrace();
}
}
public void update(int emp_id) {
try {
con =
DatabaseUtils.getConnection();
stmt = con.createStatement();
stmt.executeUpdate("UPDATE
employee SET emp_address='San Chaung' WHERE emp_id= " + emp_id);
} catch (SQLException
e) {
e.printStackTrace();
}
}
public Employee
findById(int emp_id) {
List<Employee> empList = new
ArrayList<Employee>();
try {
con =
DatabaseUtils.getConnection();
ResultSet resultSet = stmt.executeQuery("SELECT
* FROM employee WHERE emp_id= "+ emp_id);
while
(resultSet.next()) {
int empId =
resultSet.getInt("emp_id");
String empName =
resultSet.getString("emp_name");
Long empSalary =
resultSet.getLong("emp_salary");
String empPhone =
resultSet.getString("emp_phone");
String empAddress =
resultSet.getString("emp_address");
String empDept =
resultSet.getString("emp_department");
empList.add(new
Employee(empId, empName, empSalary, empPhone, empAddress, empDept));
}
} catch (SQLException
e) {
e.printStackTrace();
}
return
empList.get(0);
}
public
List<Employee> findAll() {
List<Employee> empList = new
ArrayList<Employee>();
try {
con =
DatabaseUtils.getConnection();
ResultSet resultSet = stmt.executeQuery("SELECT
* FROM employee");
while
(resultSet.next()) {
int empId =
resultSet.getInt("emp_id");
String empName =
resultSet.getString("emp_name");
Long empSalary =
resultSet.getLong("emp_salary");
String empPhone =
resultSet.getString("emp_phone");
String empAddress =
resultSet.getString("emp_address");
String empDept =
resultSet.getString("emp_department");
empList.add(new
Employee(empId, empName, empSalary, empPhone, empAddress, empDept));
}
} catch (SQLException
e) {
e.printStackTrace();
}
return empList;
}
public void delete(int emp_id) {
try {
con = DatabaseUtils.getConnection();
stmt = con.createStatement();
stmt.executeUpdate("DELETE
FROM employee WHERE emp_id=" + emp_id);
} catch (SQLException
e) {
e.printStackTrace();
}
}
}
|
EmployeeDAOTest.java
|
package
com.metadata.jdbc.test;
import
com.metadata.jdbc.dao.EmployeeDAO;
import
com.metadata.jdbc.entity.Employee;
public class EmployeeDAOTest
{
private EmployeeDAO empDao = new
EmployeeDAO();
public void
insertEmployee() {
Employee empObj = new Employee("ZTO", 200000, "099
2560 147", "Yangon", "Metadata");
empDao.insert(empObj);
}
public void
updateEmployee() {
empDao.update(4);
}
public void
findEmployee() {
Employee emp = empDao.findById(1);
System.out.println("Employee
Id : " + emp.getEmployeeId());
System.out.println("Employee
Name : " + emp.getEmployeeName());
System.out.println("Employee
Salary : " + emp.getEmployeeSalary());
System.out.println("Employee
Phone : " + emp.getEmployeePhoneNumbers());
System.out.println("Employee
Address : " + emp.getEmployeeAddress());
System.out.println("Employee
Department : " + emp.getEmployeeDepartment());
}
public void
deleteEmployee() {
empDao.delete(3);
}
public static void main(String[]
args) {
EmployeeDAOTest empTest = new EmployeeDAOTest();
empTest.insertEmployee();
empTest.updateEmployee();
empTest.findEmployee();
empTest.deleteEmployee();
}
}
|
create.sql
|
CREATE TABLE EMPLOYEE (
EMP_ID INT(10) NOT NULL
AUTO_INCREMENT,
EMP_NAME VARCHAR(45) NOT NULL,
EMP_SALARY VARCHAR(45) NOT NULL,
EMP_PHONE VARCHAR(45) NOT NULL,
EMP_ADDRESS VARCHAR(45) NOT NULL,
EMP_DEPARTMENT VARCHAR(45) NOT NULL,
PRIMARY KEY (EMP_ID)
);
|
No comments:
Post a Comment