DAO学习笔记

1、DAO

Date Access Object，封装了数据访问的逻辑，调用者(一般是业务逻辑模块)不需要了解封装的细节，当细节发生改变时，不会影响调用者。

优势：提升代码的扩展性，方便后期维护。

 

2、如何写一个DAO

第一步：定义实体类

属性与表字段一一对应，并且提供get/set方法

 

第二步：定义DAO接口

定义一些与具体技术无关的方法

ResultSet findAll() throws Exception();//JDBC相关

List<Emp> findAll() throws SQLException();//JDBC相关

List<Emp> findAll() throws Exception();//OK

 

第三步：接口的实现类

使用具体的技术重写接口当中的方法

 

案例练习：请使用DAO对emp表增删改查功能改写

//步骤一：定义实体类（属性与表字段一一对应，并且提供get/set方法）
public class Emp {
	private int id;
	private String name;
	private double salary;
	private int age;
	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public double getSalary() {
		return salary;
	}
	public void setSalary(double salary) {
		this.salary = salary;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	public Emp() {
		super();
	}
	public Emp(String name, double salary, int age) {
		super();
		this.name = name;
		this.salary = salary;
		this.age = age;
	}
	public Emp(int id, String name, double salary, int age) {
		super();
		this.id = id;
		this.name = name;
		this.salary = salary;
		this.age = age;
	}	
}

//步骤二：定义DAO接口（定义一些与具体技术无关的方法）
public interface EmpDAO {
	//查找所有的记录
	List<Emp> findAll() throws Exception;
	
	//删除记录
	void delete(int id) throws Exception;
	
	//添加
	void add(Emp addEmp) throws Exception;
	
	//根据id查找某一记录
	Emp findById(int id) throws Exception;
	
	//更新
	void update(Emp emp) throws Exception;	
}

//为了提高代码的复用性，将数据库的连接与关闭代码进行封装
public class DBUtil {
	//获取连接
	public static Connection getConnection() {
		Connection con = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/jsd1704","root","1234");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return con;
	}
	
	//关闭连接
	public static void close(Connection con) {
		if (con != null) {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

//步骤三：接口的实现类（使用具体的技术重写接口当中的方法）
public class EmpDAOImpl implements EmpDAO{
	public List<Emp> findAll() throws Exception {
		Connection con = DBUtil.getConnection();//获取连接
		String sql = "select * from emp";
		PreparedStatement ps = con.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		List<Emp> list = new ArrayList<Emp>();
		Emp emp = null;
		while(rs.next()){
			emp = new Emp();
			emp.setId(rs.getInt("id"));
			emp.setName(rs.getString("name"));
			emp.setSalary(rs.getDouble("salary"));
			emp.setAge(rs.getInt("age"));
			list.add(emp);
		}
		DBUtil.close(con);//关闭连接
		return list;
	}

	public void delete(int id) throws Exception {
		Connection con = DBUtil.getConnection();
		String sql = "delete from emp where id=?";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, id);
		ps.executeUpdate();
		DBUtil.close(con);
	}

	public void add(Emp addEmp) throws Exception {
		Connection con = DBUtil.getConnection();
		String sql = "insert into emp values(null,?,?,?)";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, addEmp.getName());
		ps.setDouble(2, addEmp.getSalary());
		ps.setInt(3, addEmp.getAge());
		ps.executeUpdate();
		DBUtil.close(con);
		
	}
	public Emp findById(int id) throws Exception {
		Connection con = DBUtil.getConnection();
		String sql = "select * from emp where id=?";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, id);
		ResultSet rs = ps.executeQuery();
		Emp emp = new Emp();
		if (rs.next()) {
			emp.setName(rs.getString("name"));
			emp.setSalary(rs.getDouble("salary"));
			emp.setAge(rs.getInt("age")) ;
		}
		DBUtil.close(con);
		return emp;
	}

	public void update(Emp emp) throws Exception {
		Connection con = DBUtil.getConnection();
		String sql =  "update emp set name=?,salary=?,age=?  where id=?";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, emp.getName());
		ps.setDouble(2, emp.getSalary());
		ps.setInt(3, emp.getAge());
		ps.setInt(4, emp.getId());
		ps.executeUpdate();
		DBUtil.close(con);
	}
}

//以增加员工组件AddEmpServlet组件为例，演示增功能
public class AddEmpServlet extends HttpServlet{
	public void service(HttpServletRequest req,HttpServletResponse res)
		throws IOException,ServletException{
		
		req.setCharacterEncoding("utf-8");//设置请求编码格式
		res.setContentType("text/html;charset=utf-8");//输出编码
		PrintWriter out = res.getWriter();//获取输出流
		
		//获取表单里面数据
		String name = req.getParameter("name");
		double salary = Double.parseDouble(req.getParameter("salary"));				
                int age = Integer.parseInt(req.getParameter("age"));
		
		EmpDAO dao = new EmpDAOImpl();
		try {
			Emp addEmp = new Emp(name,salary,age);
			dao.add(addEmp);			
                        //重定向到list页面
			res.sendRedirect("list");
			
		} catch (Exception e) {
			e.printStackTrace();
			out.print("系统繁忙，稍后重试！");
		}
	}
}