DAO设计模式

DAO设计模式包括5个重要部分：数据库连接类、VO类、DAO接口、DAO实现类以及DAO工厂类。

1、Dao数据库连接类

    数据库连接类的主要功能是连接数据库并获得连接对象，以及关闭数据库。

package com.naruto.dragon;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;

public class DataBaseConnection {
    //定义数据库驱动类
    private final String DBDRIVER = "com.mysql.jdbc.Driver";
    //定义数据库连接URL
    private final String DBURL = "jdbc:mysql://localhost:3306/test";
    //定义数据库连接用户名
    private final String DBUSER = "root";
    //定义数据库连接密码
    private final String BDPASSWORD = "666";
    //定义数据库连接对象
    private Connection conn = null;
    
    //构造方法，加载驱动
    public DataBaseConnection() {
        
        try {
            Class.forName(DBDRIVER);
            this.conn = DriverManager.getConnection(DBURL, DBUSER, BDPASSWORD);
        }
        catch(java.lang.ClassNotFoundException e)
        {
            System.out.println("找不到指定的驱动程序类！");
        } catch (SQLException e) {
            System.out.println("加载驱动失败");
        }
    }
    //取得数据库连接
    public Connection getConnection() {
        return conn;
    }
    //关闭数据库连接
    public void close() {
        try {
            conn.close();
        }
        catch (Exception e) {
            System.out.println("数据库关闭失败");
        }
    }
}

2、VO类

  VO类是一个包含属性和表中字段完全对应的类。并在该类中提供setter和getter方法来设置并获得该类中的属性。

package com.naruto.dragon;

public class User {
    //用户id
    private int userid;
    //用户姓名
    private String username;
    //用户密码
    private String password;
    public int getUserid() {
        return userid;
    }
    public void setUserid(int userid) {
        this.userid = userid;
    }
    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;
    }
    
    
}

3、DAO接口

      DAO接口定义了所有的用户的操作。

package com.naruto.dragon;
import java.util.List;

public interface UserDao {
    //添加操作
    public void insert(User user) throws Exception;
    //修改操作
    public void update(User user) throws Exception;
    //删除操作
    public void delete(int userid) throws Exception;
    //按ID查询操作
    public User queryById(int userid) throws Exception;
    //查询全部
    public List queryAll() throws Exception;
}

4、DAO实现类

    DAO实现类实现了DAO接口。

package com.naruto.dragon;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao {

    public void insert(User user) throws Exception {
        String sql = "INSERT INTO user(username,password) VALUES(?,?)";
        java.sql.PreparedStatement pstmt = null;
        DataBaseConnection dbc = null;
        try {
            dbc = new DataBaseConnection();
            pstmt = dbc.getConnection().prepareStatement(sql);
            pstmt.setString(1,user.getUsername());
            pstmt.setString(2, user.getPassword());
            pstmt.executeUpdate();
            pstmt.close();
        }
        catch (Exception e) {
            throw new Exception("操作出现异常");
        }
        finally {
            dbc.close();
        }
    }

    public void update(User user) throws Exception {
        String sql = "UPDATE user SET username = ?,password = ? WHERE userid = ?";
        java.sql.PreparedStatement pstmt = null;
        DataBaseConnection dbc = null;
        try {
            dbc = new DataBaseConnection();
            pstmt = dbc.getConnection().prepareStatement(sql);
            pstmt.setString(1, user.getUsername());    
            pstmt.setString(2, user.getPassword());    
            pstmt.setInt(3, user.getUserid());    
            pstmt.executeUpdate();
            pstmt.close();
        }
        catch (Exception e) {
            throw new Exception("操作出现异常");
        }
        finally {
            dbc.close();
        }
    }

    public void delete(int userid) throws Exception {
        String sql = "DELETE FROM user WHERE userid = ?";
        java.sql.PreparedStatement pstmt = null;
        DataBaseConnection dbc = null;
        try {
            dbc = new DataBaseConnection();
            pstmt = dbc.getConnection().prepareStatement(sql);
            pstmt.setInt(1, userid);
            pstmt.executeUpdate();
            pstmt.close();
            
        } catch (Exception e) {
            throw new Exception("操作出现异常");
        }
        finally {
            dbc.close();
        }
    }

    public User queryById(int userid) throws Exception {
        User user = null;
        String sql = "SELECT * FROM user WHERE userid = ?";
        java.sql.PreparedStatement pstmt = null;
        DataBaseConnection dbc = null;
        try {
            dbc = new DataBaseConnection();
            pstmt = dbc.getConnection().prepareStatement(sql);
            pstmt.setInt(1, userid);
            ResultSet rs = pstmt.executeQuery();
            if(rs.next()) {
                user = new User();
                user.setUserid(rs.getInt(1));
                user.setUsername(rs.getString(2));
                user.setPassword(rs.getString(3));
            }
            rs.close();
            pstmt.cancel();
        } catch (Exception e) {
            throw new Exception("操作出现异常");
        }
        finally {
            dbc.close();
        }
        return user;
    }
    public List<User> queryAll() throws Exception {
        List<User> all = new ArrayList<User>();
        String sql = "SELECT * FROM user";
        java.sql.PreparedStatement pstmt = null;
        DataBaseConnection dbc = null;
        try {
            dbc = new DataBaseConnection();
            pstmt = dbc.getConnection().prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()) {
                User user = new User();
                user.setUserid(rs.getInt(1));
                user.setUsername(rs.getString(2));
                user.setPassword(rs.getString(3));
                all.add(user);
            }
            rs.close();
            pstmt.close();
        } catch (Exception e) {
            throw new Exception("操作出现异常");
        }
        finally {
            dbc.close();
        }
        return all;
    }

}

5、DAO工厂类

package com.naruto.dragon;

public class DAOFactory {
    public static UserDao getUserDaoInstance() {
        return new UserDaoImpl();
    }
}

测试：

1、添加记录

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import = "com.naruto.dragon.*" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>添加用户记录</title>
</head>
<body>
    <%
        UserDao userDao = DAOFactory.getUserDaoInstance();
        User user = new User();
        user.setUsername("dao");
        user.setPassword("123");
        userDao.insert(user);
    %>
</body>
</html>

2、更新操作

<%@ page language="java" import = "com.naruto.dragon.*" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>更新用户记录</title>
</head>
<body>
     <%
         UserDao userdao = DAOFactory.getUserDaoInstance();
        User user = new User();
        user.setUserid(7);
        user.setUsername("naruto");
        user.setPassword("666");
        userdao.update(user);
     %>
</body>
</html>

3、删除记录

<%@ page language="java" import = "com.naruto.dragon.*" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>删除用户记录</title>
</head>
<body>
     <%
         UserDao userdao = DAOFactory.getUserDaoInstance();
        userdao.delete(3);
     %>
</body>
</html>

4、按ID查询记录

<%@ page language="java" import = "com.naruto.dragon.*" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>按ID查询</title>
</head>
<body>
    <%
        UserDao userdao = DAOFactory.getUserDaoInstance();
        User user = userdao.queryById(7);
        out.println("用户名:" + user.getUsername());
        out.println("密码:" + user.getPassword());
    %>
</body>
</html>

5、查询所有记录的示例

<%@page import="java.util.*"%>
<%@ page language="java" import = "com.naruto.dragon.*" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>查询所有记录</title>
</head>
<body>
      <%
        UserDao userdao = DAOFactory.getUserDaoInstance();
        List<User> all = userdao.queryAll();
        Iterator<User> iter = all.iterator();
        while(iter.hasNext()){
                User user = iter.next();
                out.println("用户名:" + user.getUsername());
                out.println("密码:" + user.getPassword() + "<br>");
        }
      %>
</body>
</html>