package dbtest;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class DbTest {
public static void main(String[] args) {
Employee employee1 =new Employee();
employee1.setEmpno(555);
employee1.setEname("hakly");
employee1.setSal(5400);
employee1.setHiredate(new Date());
addEmployee(employee1);
List<Employee>employees=getEmployees();
for(Employee employee:employees){
System.out.println(employee);
}
Employee employee =new Employee();
employee.setEmpno(999);
employee.setEname("jack");
employee.setSal(5000);
employee.setHiredate(new Date());
addEmployee(employee);
}
public static List<Employee> getEmployees() {
ResultSet rs=null;
Connection conn=null;
Statement stat=null;
List<Employee> employees=new ArrayList<Employee>();
try{
Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/test2?useUnicode=true&characterEncoding=utf-8";
String user="root";
String password="root";
conn= DriverManager.getConnection(url,user,password);
stat=conn.createStatement();
String sql="select * from emp";
rs=stat.executeQuery(sql);
Employee employee=null;
while(rs.next()){
employee=new Employee();
employee.setEmpno(rs.getInt("empno"));
employee.setEname(rs.getString("ename"));
employee.setSal(rs.getDouble("sal"));
employee.setHiredate(rs.getDate("hiredate"));
employees.add(employee);
}
}catch(Exception e ){
e.printStackTrace();
}finally{
try {
if(conn!=null){
conn.close();
}
}catch (SQLException e) {
e.printStackTrace();
}
}
return employees;
}
<!--more-->
public static void addEmployee(Employee employee) {
Connection conn = null;
Statement stat = null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/test2?useUnicode=true&characterEncoding=utf-8";
String user = "root";
String password = "root";
conn = DriverManager.getConnection(url, user, password);
stat = conn.createStatement();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String sql = "insert into emp(empno,ename,sal,hiredate) values(" + employee.getEmpno() + ",'"
+ employee.getEname() + "'," + employee.getSal() + ",'" + sdf.format(employee.getHiredate()) + "')";
int i = stat.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void updateEmployee(Employee employee) {
Connection conn = null;
Statement stat = null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/test2?useUnicode=true&characterEncoding=utf-8";
String user = "root";
String password = "root";
conn = DriverManager.getConnection(url, user, password);
stat = conn.createStatement();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String sql = "update emp set ename='"+employee.getEname()+"empno"+employee.getEmpno()+"sal"+employee.getSal();
int i = stat.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}