用apache来玩JDBC

pojo层emp对象

import java.sql.Date;
public class Emp {
	private int empno;
	private String ename;
	private String job;
	private int mgr;
	private Date hiredate;
	private double sal;
	private double comm;
	private int deptno;
	public Emp() {
		super();
	}
	public Emp(int empno, String ename, String job, int mgr, Date hiredate, double sal, double comm, int deptno) {
		super();
		this.empno = empno;
		this.ename = ename;
		this.job = job;
		this.mgr = mgr;
		this.hiredate = hiredate;
		this.sal = sal;
		this.comm = comm;
		this.deptno = deptno;
	}
	public int getEmpno() {
		return empno;
	}
	public void setEmpno(int empno) {
		this.empno = empno;
	}
	public String getEname() {
		return ename;
	}
	public void setEname(String ename) {
		this.ename = ename;
	}
	public String getJob() {
		return job;
	}
	public void setJob(String job) {
		this.job = job;
	}
	public int getMgr() {
		return mgr;
	}
	public void setMgr(int mgr) {
		this.mgr = mgr;
	}
	public Date getHiredate() {
		return hiredate;
	}
	public void setHiredate(Date hiredate) {
		this.hiredate = hiredate;
	}
	public double getSal() {
		return sal;
	}
	public void setSal(double sal) {
		this.sal = sal;
	}
	public double getComm() {
		return comm;
	}
	public void setComm(double comm) {
		this.comm = comm;
	}
	public int getDeptno() {
		return deptno;
	}
	public void setDeptno(int deptno) {
		this.deptno = deptno;
	}
	@Override
	public String toString() {
		return "Emp [empno=" + empno + ", ename=" + ename + ", job=" + job + ", mgr=" + mgr + ", hiredate=" + hiredate
				+ ", sal=" + sal + ", comm=" + comm + ", deptno=" + deptno + "]";
	}
}

 
util层的连接数据库工具：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
 * 获得连接 与 释放资源
 *
 * @author pyb
 *
 */
public class DBUtil {
	private static final String DERIVER = "oracle.jdbc.driver.OracleDriver";
	private static final String URL = "jdbc:oracle:thin:127.0.0.1:ORCL:1521";
	private static final String USERNAME = "scott";
	private static final String PASSWORD = "614";
	private static Connection conn = null;
	private DBUtil() {}
	static {
		try {
			Class.forName(DERIVER);
			conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	// 获取Connection对象
	public static Connection getConnection() {
		return conn;
	}
	// 关闭的方法
	public static void close(Connection c, Statement s, ResultSet r) throws SQLException {
		if (c != null && !c.isClosed()) {
			c.close();
		}
		if (s != null && !s.isClosed()) {
			s.close();
		}
		if (r != null && !r.isClosed()) {
			r.close();
		}
	}
}

 
dao层的数据操作：

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import com.yibobo.pojo.Emp;
import com.yibobo.util.DBUtil;
public class EmpDAO {
	private Connection conn = DBUtil.getConnection();
	private static EmpDAO empdao = new EmpDAO();
	private EmpDAO(){};
	//饿汉式单例
	public static EmpDAO getInstance(){
		return empdao;
	}
	/**
	 * 使用apache的包来进行查询所有数据
	 * @return 一个集合
	 */
	public List<Emp> queryAll(){
		List<Emp> emps = new ArrayList();
		QueryRunner qr = new QueryRunner();
		try {
			emps = qr.query
					(conn,"SELECT * FROM emp_pyb", new BeanListHandler<>(Emp.class));
			//返回集合用BeanListHandler，返回一个对象就直接用BeanHandler
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try {
				DBUtil.close(conn, null, null);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return emps;
	}
	/**
	 * 增加一行数据
	 * @param e
	 */
	public void insert(Emp e){
		QueryRunner qr = new QueryRunner();
		try {
			qr.update(conn,"INSERT INTO emp_pyb VALUES(?,?,?,?,?,?,?,?)",
					new Object[]{e.getEmpno(),e.getEname(),e.getJob(),e.getMgr(),
							e.getHiredate(),e.getSal(),e.getComm(),e.getDeptno()});
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
	}
	/**
	 * 通过empno删除一行数据
	 * @param empno
	 */
	public void delete(int empno){
		QueryRunner qr = new QueryRunner();
		try {
			qr.update(conn,"DELETE  emp_pyb WHERE empno=?",empno);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	/**
	 * 修改数据 通过传进来的deptno定位
	 * 修改为传进来的Emp
	 * @param e
	 * @param deptno
	 */
	public void update(Emp e,int empno){
		QueryRunner qr = new QueryRunner();
		String sql = "UPDATE emp_pyb SET empno=?,ename=?,job=?,mgr=?,hiredate=?,sal=?,comm=?,deptno=? WHERE empno=?";
		try {
			qr.update(conn,sql,new Object[]{e.getEmpno(),e.getEname(),e.getJob(),e.getMgr(),
								e.getHiredate(),e.getSal(),e.getComm(),e.getDeptno(),empno});
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
	}
}

 
 
视图层就不贴了。确认过都能用。用这个工具是真的方便啊