Java EE WEB工程师培训-JDBC+Servlet+JSP整合开发之09.JDBC DAO设计模式

–DAO 简介
–DAO 实例
• 用户登录、注册
• 订餐系统后台JDBC实现

################Michael分割线#####################

• DAO 简介
–DAO设计模式
• DAO的全称是：Data Access Object，数据访问对象。
• 使用DAO设计模式，来封装数据库持久层的所有操作（CRUD），使低级的数据逻辑和高级的业务逻辑分离，达到解耦合的目的。
–一个典型的DAO实现有如下的组件：
• 一个DAO 接口
• 数据传输对象(有时称为值对象)
• 一个实现了DAO 接口的具体类
• 一个DAO 工厂类
–以维护一个客户信息为例，具体组件如下所示：
• CustomerDao 接口
• Customer 值对象（VO)
• CustomerDaoImpl（接口的具体实现类）
• CustomerFactory（工厂类，实例化用）

• DAO 实例
–用户登录、注册
• 使用DAO设计模式的后台JDBC实现
–功能
» 登录
» 注册
» 检查用户唯一性

UserDao.java

package com.michael.dao;    

import com.michael.vo.User;    

public interface UserDao {    
        //login    
        public User login(String user,String password);    
        //register    
        public void register(User u);    
        //check    
        public boolean check(String user);    
}

UserDaoImpl.java

package com.michael.dao.impl;    

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

import com.michael.dao.UserDao;    
import com.michael.jdbc.ConnectionUtil;    
import com.michael.vo.User;    

public class UserDaoImpl implements UserDao {    
        //检查是否己存在    
        public boolean check(String user) {    
                Connection conn = new ConnectionUtil().openConnection();    
                String sql = "select id,user,password,age from UserTbl where user = ?";    
                try {    
                        PreparedStatement pstmt = conn.prepareStatement(sql);    
                        pstmt.setString(1, user);    
                        ResultSet rs = pstmt.executeQuery();    
                        //如果结果集有，则返回true，说明己存在    
                        if(rs.next()){    
                                return true;    
                        }    
                } catch (SQLException e) {    
                        e.printStackTrace();    
                }finally{    
                        try {    
                                conn.close();    
                        } catch (SQLException e) {    
                                e.printStackTrace();    
                        }    
                }    
                return false;    
        }    

        //登录    
        public User login(String user, String password) {    
                Connection conn = new ConnectionUtil().openConnection();    
                String sql = "select id,user,password,age from UserTbl where user = ? and password = ?";    
                try {    
                        PreparedStatement pstmt = conn.prepareStatement(sql);    
                        pstmt.setString(1, user);    
                        pstmt.setString(2, password);    
                        ResultSet rs = pstmt.executeQuery();    
                        if(rs.next()){    
                                int id = rs.getInt(1);    
                                int age = rs.getInt("age");    
                                //将数据封装到User对象中    
                                User u = new User();    
                                u.setId(id);    
                                u.setUser(user);    
                                u.setPassword(password);    
                                u.setAge(age);    
                                return u;    
                        }    
                } catch (SQLException e) {    
                        e.printStackTrace();    
                }finally{    
                        try {    
                                conn.close();    
                        } catch (SQLException e) {    
                                e.printStackTrace();    
                        }    
                }    
                return null;    
        }    

        //注册    
        public void register(User u) {    
                Connection conn = new ConnectionUtil().openConnection();    
                String sql = "insert into UserTbl(user,password,age) values(?,?,?)";    
                try {    
                        PreparedStatement pstmt = conn.prepareStatement(sql);    
                        pstmt.setString(1, u.getUser());    
                        pstmt.setString(2, u.getPassword());    
                        pstmt.setInt(3, u.getAge());    
                        pstmt.executeUpdate();    
                } catch (SQLException e) {    
                        e.printStackTrace();    
                }finally{    
                        try {    
                                conn.close();    
                        } catch (SQLException e) {    
                                e.printStackTrace();    
                        }    
                }    
        }    
}

ConnectionUtil.java

package com.michael.jdbc;    

import java.sql.Connection;    
import java.sql.DriverManager;    
import java.util.Properties;    

public class ConnectionUtil {    
        //第一种方法    
        public Connection getConnection(){    
                Connection conn = null;    
                try {    
                        //Class.forName加载驱动    
                        Class.forName("com.mysql.jdbc.Driver");    
                        //DriverManager获得连接    
                        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc_db","root","mysqladmin");    
                        return conn;    
                } catch (Exception e) {    
                        e.printStackTrace();    
                }    
                return null;    
        }    
        //第二种方法    
        public Connection getConnection(String driver,String url,String user,String password){    
                Connection conn = null;    
                try {    
                        //Class.forName加载驱动    
                        Class.forName(driver);    
                        //DriverManager获得连接    
                        conn = DriverManager.getConnection(url,user,password);    
                        return conn;    
                } catch (Exception e) {    
                        e.printStackTrace();    
                }    
                return null;    
        }    
        //第三种方法    
        public Connection openConnection(){    
                String driver = "";    
                String url = "";    
                String user = "";    
                String password = "";    
                Properties prop = new Properties();    
                Connection conn = null;    
                try {    
                        //加载属性文件    
                        prop.load(this.getClass().getClassLoader().getResourceAsStream("DBConfig.properties"));    
                        driver = prop.getProperty("driver");    
                        url = prop.getProperty("url");    
                        user = prop.getProperty("user");    
                        password = prop.getProperty("password");    
                        //Class.forName加载驱动    
                        Class.forName(driver);    
                        //DriverManager获得连接    
                        conn = DriverManager.getConnection(url,user,password);    
                        return conn;    
                } catch (Exception e) {    
                        e.printStackTrace();    
                }    
                return null;    
        }    

}

Main.java(没有使用)

package com.michael.main;    

import com.michael.jdbc.ConnectionUtil;    

public class Main {    

        /**    
         * @param args    
         */    
        public static void main(String[] args) {    
                ConnectionUtil cu = new ConnectionUtil();    
                //第一种方法    
                System.out.println("第一种方法:"+cu.getConnection());    
                //第二种方法    
                System.out.println("第二种方法:"+cu.getConnection("com.mysql.jdbc.Driver","jdbc:mysql://localhost:3306/jdbc_db","root","mysqladmin"));    
                //第三种方法    
                System.out.println("第三种方法:"+cu.openConnection());    
        }    

}

UserDaoImplTest.java

package com.michael.test;    

import com.michael.dao.UserDao;    
import com.michael.dao.impl.UserDaoImpl;    
import com.michael.vo.User;    

import junit.framework.TestCase;    

public class UserDaoImplTest extends TestCase {    
        //Junit生命周期    
        UserDao dao;    
        protected void setUp() throws Exception {    
                dao = new UserDaoImpl();    
        }    

        protected void tearDown() throws Exception {    
        }    
        public void testRegister() {    
                User u = new User();    
                u.setUser("aaa");    
                u.setPassword("bbb");    
                u.setAge(22);    
                dao.register(u);    
        }    

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

        public void testLogin() {    
                User u = dao.login("aaa", "bbb");    
                System.out.println(u.getUser());    
        }    
}

Junit测试通过

数据库用户己增加

  
–订餐系统后台
• 使用DAO设计模式的后台JDBC实现
–添加订餐信息
–查询订餐信息

MealTypeTbl订餐类型表

 

 MealInfoTbl订餐信息表

 MealDao.java

package com.michael.dao;    

import java.util.List;    

import com.michael.vo.Meal;    

public interface MealDao {    
        // Add Meal    
        public void add(Meal m);    
        // Query Meal    
        public List query();    
}

Meal.java 

package com.michael.vo;    

public class Meal {    
        /*    
        CREATE TABLE `mealinfotbl` (    
            `id` int(11) NOT NULL auto_increment,    
            `createTime` varchar(11) default NULL,    
            `userID` int(11) default NULL,    
            `mealTypeID` int(11) default NULL,    
            `mealNum` int(11) default NULL,    
            `detail` varchar(20) default NULL,    
            PRIMARY KEY    (`id`)    
        ) ENGINE=InnoDB DEFAULT CHARSET=latin1;    
         */    
        private int id;    
        private String createTime;    
        private int userID;    
        private int mealTypeID;    
        private int mealNum;    
        private String detail;    
        public String getCreateTime() {    
                return createTime;    
        }    
        public void setCreateTime(String createTime) {    
                this.createTime = createTime;    
        }    
        public String getDetail() {    
                return detail;    
        }    
        public void setDetail(String detail) {    
                this.detail = detail;    
        }    
        public int getId() {    
                return id;    
        }    
        public void setId(int id) {    
                this.id = id;    
        }    
        public int getMealNum() {    
                return mealNum;    
        }    
        public void setMealNum(int mealNum) {    
                this.mealNum = mealNum;    
        }    
        public int getMealTypeID() {    
                return mealTypeID;    
        }    
        public void setMealTypeID(int mealTypeID) {    
                this.mealTypeID = mealTypeID;    
        }    
        public int getUserID() {    
                return userID;    
        }    
        public void setUserID(int userID) {    
                this.userID = userID;    
        }    
}

MealBean.java

package com.michael.vo;    
//有外键，封装查询Bean    
public class MealBean {    
        private String createTime;    
        private String user;    
        private String mealTypeName;    
        private int price;    
        private int mealNum;    
        private int total;    
        private String detail;    
        public String getCreateTime() {    
                return createTime;    
        }    
        public void setCreateTime(String createTime) {    
                this.createTime = createTime;    
        }    
        public String getDetal() {    
                return detail;    
        }    
        public void setDetal(String detal) {    
                this.detail = detal;    
        }    
        public int getMealNum() {    
                return mealNum;    
        }    
        public void setMealNum(int mealNum) {    
                this.mealNum = mealNum;    
        }    
        public int getPrice() {    
                return price;    
        }    
        public void setPrice(int price) {    
                this.price = price;    
        }    
        public int getTotal() {    
                return total;    
        }    
        public void setTotal(int total) {    
                this.total = total;    
        }    
        public String getUser() {    
                return user;    
        }    
        public void setUser(String user) {    
                this.user = user;    
        }    
        public String getDetail() {    
                return detail;    
        }    
        public void setDetail(String detail) {    
                this.detail = detail;    
        }    
        public String getMealTypeName() {    
                return mealTypeName;    
        }    
        public void setMealTypeName(String mealTypeName) {    
                this.mealTypeName = mealTypeName;    
        }    
}

MealDaoImpl.java

package com.michael.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.michael.dao.MealDao;    
import com.michael.jdbc.ConnectionUtil;    
import com.michael.vo.Meal;    
import com.michael.vo.MealBean;    

public class MealDaoImpl implements MealDao {    

        public void add(Meal m) {    
                Connection conn = new ConnectionUtil().openConnection();    
                String sql = "insert into MealInfoTbl(createTime,userID,mealTypeID,mealNum,detail) values(?,?,?,?,?)";    
                try {    
                        PreparedStatement pstmt = conn.prepareStatement(sql);    
                        pstmt.setString(1, m.getCreateTime());    
                        pstmt.setInt(2,m.getUserID());    
                        pstmt.setInt(3, m.getMealTypeID());    
                        pstmt.setInt(4, m.getMealNum());    
                        pstmt.setString(5, m.getDetail());    
                        pstmt.executeUpdate();    
                } catch (SQLException e) {    
                        e.printStackTrace();    
                }finally{    
                        try {    
                                conn.close();    
                        } catch (SQLException e) {    
                                e.printStackTrace();    
                        }    
                }    
        }    

        public List query() {    
                String sql = " select mi.`createTime`, "+    
                                         " u.`user`, "+    
                                         " mt.`name`, "+    
                                         " mt.`price`, "+    
                                         " mi.`mealNum`, "+    
                                         " (mt.`price`*mi.`mealNum`) as total, "+    
                                         " mi.`detail` "+    
                                         " from MealInfoTbl as mi "+    
                                         " left join UserTbl as u "+    
                                         " on mi.`userID` = u.`id` "+    
                                         " left join MealTypeTbl as mt "+    
                                         " on mi.`mealTypeID` = mt.`id` ";    
                Connection conn = new ConnectionUtil().openConnection();    
                try {    
                        Statement stmt = conn.createStatement();    
                        ResultSet rs = stmt.executeQuery(sql);    
                        //封装结果集    
                        List list = new ArrayList();    
                        //遍历结果集    
                        while(rs.next()){    
                                String createTime = rs.getString(1);    
                                String user = rs.getString(2);    
                                String mealTypeName = rs.getString(3);    
                                int price = rs.getInt(4);    
                                int mealNum = rs.getInt(5);    
                                int total = rs.getInt(6);    
                                String detail = rs.getString(7);    
                                MealBean mb = new MealBean();    
                                mb.setCreateTime(createTime);    
                                mb.setUser(user);    
                                mb.setMealTypeName(mealTypeName);    
                                mb.setPrice(price);    
                                mb.setMealNum(mealNum);    
                                mb.setTotal(total);    
                                mb.setDetail(detail);    
                                //向list容器添加mb    
                                list.add(mb);    
                        }    
                        return list;    
                } catch (SQLException e) {    
                        e.printStackTrace();    
                }    
                return null;    
        }    

}

MealDaoImplTest.java

package com.michael.test;    

import java.util.List;    

import com.michael.dao.MealDao;    
import com.michael.dao.impl.MealDaoImpl;    
import com.michael.vo.Meal;    

import junit.framework.TestCase;    

public class MealDaoImplTest extends TestCase {    
        MealDao dao;    
        protected void setUp() throws Exception {    
                super.setUp();    
                //实例化DAO    
                dao = new MealDaoImpl();    
        }    

        protected void tearDown() throws Exception {    
                super.tearDown();    
        }    

        public void testAdd() {    
                Meal m = new Meal();    
                m.setCreateTime("2009-06-09");    
                m.setUserID(5);    
                m.setMealTypeID(2);    
                m.setMealNum(5);    
                m.setDetail("very nice!");    
                dao.add(m);    
        }    

        public void testQuery() {    
                List list = dao.query();    
                System.out.println(list.size());    
        }    

}

数据库己增加到数据，显示有四条记录

 

################Michael分割线#####################