DAO设计模式

1. DAO简介

DAO的全称是：Data Access Object，数据访问对象。使用DAO设计模式来封装数据库持久层所有操作（CRUD） ，使低级的数据逻辑和高级的业务分离，达到解耦合的目的。

一个典型的DAO实现有如下的组件：

1）一个DAO接口

2）一个实现了DAO的具体实现类

3）一个DAO工厂类

4）数据传输对象（有时称为值对象）

以维护一个客户信息为例，具体的组件如下：

CustomerDao接口

CustomerDaoImpl（接口的具体实现类）

CustomerFactory（工厂类，实例化用）

Customer值对象（VO）

2. DAO实例

2.1 用户登录、注册

功能：用户登录、用户注册、检查用户唯一性。

UserDao.java

package com.amaker.dao;

import com.amaker.bean.User;

public interface UserDao {

	// login
	public User login(String username, String password);

	// register
	public void register(User u);

	// check
	public boolean check(String username);
}

UserDaoImpl.java

package com.amaker.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.amaker.bean.User;
import com.amaker.dao.UserDao;
import com.amaker.util.DBUtil;

public class UserDaoImpl implements UserDao {

	@Override
	public User login(String username, String password) {
		String sql = "select id,username,password from UserTable where username=? and password=?";
		DBUtil util = new DBUtil();
		Connection conn = util.openConnection();
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);

			pstmt.setString(1, username);
			pstmt.setString(2, password);

			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				int id = rs.getInt(1);

				User user = new User();
				user.setId(id);
				user.setUsername(username);
				user.setPassword(password);
				return user;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public void register(User u) {
		String sql = "insert into UserTable (id,username,password) values (?,?,?)";
		DBUtil util = new DBUtil();
		Connection conn = util.openConnection();
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);

			pstmt.setInt(1, u.getId());
			pstmt.setString(2, u.getUsername());
			pstmt.setString(3, u.getPassword());

			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			util.closeConnection(conn);
		}
	}

	@Override
	public boolean check(String username) {
		String sql = "select id,username,password from UserTable where username=?";
		DBUtil util = new DBUtil();
		Connection conn = util.openConnection();
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);

			pstmt.setString(1, username);

			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

}

User.java

package com.amaker.bean;

public class User {

	private int id;
	private String username;
	private String password;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

}

测试类：

package com.amaker.test;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import com.amaker.bean.User;
import com.amaker.dao.UserDao;
import com.amaker.dao.impl.UserDaoImpl;

public class UserDaoImplTest {
	UserDao dao = null;

	@Before
	public void setUp() throws Exception {
		dao = new UserDaoImpl();
	}

	@After
	public void tearDown() throws Exception {

	}

	@Test
	public void testRegister() {
		User u = new User();
		u.setId(1);
		u.setUsername("tom");
		u.setPassword("123");

		dao.register(u);
	}

	@Test
	public void testCheck() {
		boolean b = dao.check("tom");
		System.out.println(b);
	}

	@Test
	public void testLogin() {
		User u = dao.login("tom", "123");
		System.out.println(u.getUsername());
	}

}

测试结果：

2.2 订餐系统后台

功能：添加订餐信息、查询订餐信息。

创建MealTypeTbl表：

mysql> create table MealTypeTbl(id int,name varchar(20),price int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into MealTypeTbl (id,name,price) values (1,"big",10);
Query OK, 1 row affected (0.00 sec)

mysql> insert into MealTypeTbl (id,name,price) values (2,"small",8);
Query OK, 1 row affected (0.00 sec)

mysql> select * from MealTypeTbl;
+------+-------+-------+
| id   | name  | price |
+------+-------+-------+
|    1 | big   |    10 |
|    2 | small |     8 |
+------+-------+-------+
2 rows in set (0.00 sec)

创建MealTbl表：

mysql> desc MealTbl;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(11)     | YES  |     | NULL    |       |
| createTime | varchar(20) | YES  |     | NULL    |       |
| UserID     | int(11)     | YES  |     | NULL    |       |
| MealTypeID | int(11)     | YES  |     | NULL    |       |
| num        | int(11)     | YES  |     | NULL    |       |
| comment    | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> select * from MealTbl;
+------+------------+--------+------------+------+---------+
| id   | createTime | UserID | MealTypeID | num  | comment |
+------+------------+--------+------------+------+---------+
|    1 | 2016-2-18  |      2 |          1 |    2 | 0k      |
+------+------------+--------+------------+------+---------+
1 row in set (0.00 sec)

MealDao.java

package com.amaker.dao;

import java.util.List;

import com.amaker.bean.Meal;
import com.amaker.bean.MealBean;

public interface MealDao {

	// add
	public void add(Meal meal);
	
	// query
	public List<MealBean> list();

}

MealDaoImpl.java

package com.amaker.dao.impl;

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.amaker.bean.Meal;
import com.amaker.bean.MealBean;
import com.amaker.dao.MealDao;
import com.amaker.util.DBUtil;

public class MealDaoImpl implements MealDao {

	@Override
	public void add(Meal meal) {
		DBUtil util = new DBUtil();
		Connection conn = util.openConnection();
		String sql = "insert into MealTbl (id,createTime,UserID,MealTypeID,num,comment) values (?,?,?,?,?,?) ";

		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);

			pstmt.setInt(1, meal.getId());
			pstmt.setString(2, meal.getCreateTime());
			pstmt.setInt(3, meal.getUserID());
			pstmt.setInt(4, meal.getMealType());
			pstmt.setInt(5, meal.getNum());
			pstmt.setString(6, meal.getComment());

			pstmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			util.closeConnection(conn);
		}

	}

	@Override
	public List<MealBean> list() {
		DBUtil util = new DBUtil();
		Connection conn = util.openConnection();
		String sql = "select mt.id, mt.createTime, ut.username, mtt.name, mtt.price, mt.num, mtt.price*mt.num as total, mt.comment from MealTbl as mt left join UserTable as ut on mt.UserID=ut.id left join MealTypeTbl as mtt on mt.MealTypeID=mtt.id";

		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);

			List<MealBean> list = new ArrayList<MealBean>();

			while (rs.next()) {
				int id = rs.getInt(1);
				String createTime = rs.getString(2);
				String username = rs.getString(3);
				String name = rs.getString(4);
				int price = rs.getInt(5);
				int num = rs.getInt(6);
				int total = rs.getInt(7);
				String comment = rs.getString(8);

				MealBean mb = new MealBean();
				mb.setId(id);
				mb.setCreateTime(createTime);
				mb.setUsername(username);
				mb.setName(name);
				mb.setPrice(price);
				mb.setNum(num);
				mb.setTotal(total);
				mb.setComment(comment);

				list.add(mb);
			}

			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			util.closeConnection(conn);
		}

		return null;
	}
}

Meal.java

package com.amaker.bean;

public class Meal {

	private int id;
	private String createTime;
	private int userID;
	private int mealType;
	private int num;
	private String comment;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getCreateTime() {
		return createTime;
	}

	public void setCreateTime(String createTime) {
		this.createTime = createTime;
	}

	public int getUserID() {
		return userID;
	}

	public void setUserID(int userID) {
		this.userID = userID;
	}

	public int getMealType() {
		return mealType;
	}

	public void setMealType(int mealType) {
		this.mealType = mealType;
	}

	public int getNum() {
		return num;
	}

	public void setNum(int num) {
		this.num = num;
	}

	public String getComment() {
		return comment;
	}

	public void setComment(String comment) {
		this.comment = comment;
	}

}

MealTpye.java

package com.amaker.bean;

public class MealType {

	private int id;
	private String name;
	private int price;

	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 int getPrice() {
		return price;
	}

	public void setPrice(int price) {
		this.price = price;
	}

}

MealBean.java

package com.amaker.bean;

public class MealBean {
	private int id;
	private String createTime;
	private String username;
	private String name;
	private int price;
	private int num;
	private int total;
	private String comment;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getCreateTime() {
		return createTime;
	}

	public void setCreateTime(String createTime) {
		this.createTime = createTime;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public int getPrice() {
		return price;
	}

	public void setPrice(int price) {
		this.price = price;
	}

	public int getNum() {
		return num;
	}

	public void setNum(int num) {
		this.num = num;
	}

	public int getTotal() {
		return total;
	}

	public void setTotal(int total) {
		this.total = total;
	}

	public String getComment() {
		return comment;
	}

	public void setComment(String comment) {
		this.comment = comment;
	}

	@Override
	public String toString() {
		return "MealBean [id=" + id + ", createTime=" + createTime
				+ ", username=" + username + ", name=" + name + ", price="
				+ price + ", num=" + num + ", total=" + total + ", comment="
				+ comment + "]";
	}

}