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>
