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

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

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

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

} 

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

} 

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

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

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

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

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

} 

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

}