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;
	}
	
	@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);
	}

}