Jdbc Person

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;}@Overridepublic 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;@Overridepublic 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 blocke1.printStackTrace();}// TODO Auto-generated catch blocke.printStackTrace();}}@Overridepublic 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 blocke1.printStackTrace();}// TODO Auto-generated catch blocke.printStackTrace();}}@Overridepublic 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 blocke1.printStackTrace();}e.printStackTrace();}}@Overridepublic 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;}@Overridepublic 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;}@Overridepublic 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 blocke.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);}}