Person:员工的实体类
PersonDao:接口文件
PersonDaoImpl:dao 的实现类
DaoTest:测试类,里面有主函数
工具类
package com.oracle.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class ConnectionTool {
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
private static String url="jdbc:mysql://localhost:3306/1210";
private static String user="root";
private static String password="root";
public static Connection getConnection(){
Connection conn=null;
try {
conn=DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void close(ResultSet rs, Statement ps, Connection conn) {
try {
if (rs != null) {
rs.close();
}
if(ps!=null){
ps.close();
}
if(conn!=null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
package com.oracle.entity;
import java.util.Date;
public class Person {
private int personid;
private String personname;
private String degree;
private Date birth;
private double sal;
public Person(int personid, String personname, String degree, Date birth, double sal) {
super();
this.personid = personid;
this.personname = personname;
this.degree = degree;
this.birth = birth;
this.sal = sal;
}
@Override
public String toString() {
return "Person [personid=" + personid + ", personname=" + personname + ", degree=" + degree + ", birth=" + birth
+ ", sal=" + sal + "]";
}
public Person() {
super();
}
public int getPersonid() {
return personid;
}
public void setPersonid(int personid) {
this.personid = personid;
}
public String getPersonname() {
return personname;
}
public void setPersonname(String personname) {
this.personname = personname;
}
public String getDegree() {
return degree;
}
public void setDegree(String degree) {
this.degree = degree;
}
public java.sql.Date getBirth() {
return new java.sql.Date(this.birth.getTime());
}
public void setBirth(Date birth) {
this.birth = birth;
}
public double getSal() {
return sal;
}
public void setSal(double sal) {
this.sal = sal;
}
}
package com.oracle.dao;
import java.sql.Connection;
import java.util.List;
import com.oracle.entity.Person;
public interface PersonDao {
/**
* 插入一个人员
* @param person:被插入的人员对象
*/
public void insert(Person person,Connection conn);
/**
* 修改一个人员:
* @param person:被修改的人员对象,人员对象只含有人员编号,修改此编号的员工信息
*/
public void update(Person person,Connection conn);
/**
* 根据人员编号删除多个人员
* @param ids:被删除的人员编号数组
*/
public void delete(int[] ids,Connection conn);
/**
* 查询所有的人员,将查询出的人员存储在List中
* @return :所有人员对象的集合
*/
public List<Person> getAll(Connection conn);
/**
* 查询某一页的人员
* @param pagesize:每页的记录条数
* @param pageOrder:页号(即第几页)
* @return
*/
public List<Person> getPersonForPage(int pagesize,int pageOrder,Connection conn);
/**
* 根据人员编号,查询出此编号对应的人员
* @return
*/
public Person getPersonById(int id,Connection conn);
}
package com.oracle.daoImpl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.oracle.dao.PersonDao;
import com.oracle.entity.Person;
import com.oracle.util.ConnectionTool;
public class PersonDaoImpl implements PersonDao {
private PreparedStatement ps;
private ResultSet rs;
@Override
public void insert(Person person,Connection conn) {
conn = ConnectionTool.getConnection();
String sql = "insert into person(personid,personname,degree,birth,sal) values(?,?,?,?,?)";
try {
conn.setAutoCommit(false);
ps = conn.prepareStatement(sql);
ps.setInt(1, person.getPersonid());
ps.setString(2, person.getPersonname());
ps.setString(3, person.getDegree());
ps.setDate(4, person.getBirth());
ps.setDouble(5, person.getSal());
System.out.println(ps);
ps.executeUpdate();
conn.commit();
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@Override
public void update(Person person,Connection conn) {
conn = ConnectionTool.getConnection();
String sql = "update person set personname=?,degree=?,birth=?,sal=? where personid=?";
try {
conn.setAutoCommit(false);
ps = conn.prepareStatement(sql);
ps.setInt(5, person.getPersonid());
ps.setString(1, person.getPersonname());
ps.setString(2, person.getDegree());
ps.setDate(3, person.getBirth());
ps.setDouble(4, person.getSal());
System.out.println(ps);
ps.executeUpdate();
conn.commit();
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@Override
public void delete(int[] ids,Connection conn) {
conn = ConnectionTool.getConnection();
String sql = "delete from person where personid in ";
String s = "(";
for (int a : ids) {
s = s + a + ",";
}
s = s.substring(0, s.length() - 1) + ")";
sql = sql + s;
try {
conn.setAutoCommit(false);
Statement st = conn.createStatement();
System.out.println(sql);
st.executeUpdate(sql);
conn.commit();
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
e.printStackTrace();
}
}
@Override
public List<Person> getAll(Connection conn) {
List<Person> list = new ArrayList<>();
conn = ConnectionTool.getConnection();
String sql = "select * from person";
try {
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
Person person = new Person();
person.setPersonid(rs.getInt("personid"));
person.setBirth(rs.getDate("birth"));
person.setDegree(rs.getString("degree"));
person.setPersonname(rs.getString("personname"));
person.setSal(rs.getDouble("sal"));
list.add(person);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
@Override
public List<Person> getPersonForPage(int pagesize, int pageOrder,Connection conn) {
List<Person> list = new ArrayList<>();
conn = ConnectionTool.getConnection();
String sql = "select * from person limit ?,?";
try {
ps = conn.prepareStatement(sql);
ps.setInt(1, pagesize * (pageOrder - 1));
ps.setInt(2, pagesize);
rs = ps.executeQuery();
while (rs.next()) {
Person person = new Person();
person.setPersonid(rs.getInt("personid"));
person.setBirth(rs.getDate("birth"));
person.setDegree(rs.getString("degree"));
person.setPersonname(rs.getString("personname"));
person.setSal(rs.getDouble("sal"));
list.add(person);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
@Override
public Person getPersonById(int id,Connection conn) {
conn = ConnectionTool.getConnection();
Person person = null;
String sql = "select * from person where personid=?";
try {
ps = conn.prepareStatement(sql);
ps.setInt(1, id);
rs = ps.executeQuery();
if (rs.next()) {
person = new Person();
person.setPersonid(rs.getInt("personid"));
person.setBirth(rs.getDate("birth"));
person.setDegree(rs.getString("degree"));
person.setPersonname(rs.getString("personname"));
person.setSal(rs.getDouble("sal"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return person;
}
}
package com.oracle.test;
import java.sql.Connection;
import java.util.Date;
import java.util.List;
import com.oracle.dao.PersonDao;
import com.oracle.daoImpl.PersonDaoImpl;
import com.oracle.entity.Person;
import com.oracle.util.ConnectionTool;
public class TestPerson {
public static void main(String[] args) {
Person person=new Person(4,"djin","本科",new Date(),50000);
PersonDao pd=new PersonDaoImpl();
Connection conn=ConnectionTool.getConnection();
//pd.insert(person,conn);
//pd.update(person,conn);
// int ids[]={1,3};
// pd.delete(ids,conn);
// List<Person> list=pd.getAll(conn);
// List<Person> list=pd.getPersonForPage(2, 1,conn);
// for(Person l:list){
// System.out.println(l);
// }
person=pd.getPersonById(3,conn);
System.out.println(person);
ConnectionTool.close(null, null, conn);
}
}