JDBC编程——DAO设计模式

DAO(Data Access Object)是一个数据访问接口，数据访问：顼名思义就是不数据库打交道。夹在业务逻辑不数据库资源中间。

一个典型的DAO通常有以下几个组件

1、一个DAO工厂

2、一个DAO接口

3、一个DAO接口的具体实现类

4、数据传递对象

DAO的设计结构

pojo:从数据库表中映射过来的实体类——ContactsVO

bd:存放数据库管理类：1、连接打开、资源关闭——数据库访问管理类DBManager

2、Jdbc具体执行sql访问的优化——DBOperator

dao.itel:根据一个数据库表设计的dao接口——如ContactsDao

dao.impl :dao接口的实现类，实现增删改查的方法——如ContactsDaoImpl

dao.factory: dao的工厂类——DaoFactory

Test:测试类

Contacts.Class

public class ContactsVO {<span style="white-space:pre"></span>private int id;<span style="white-space:pre"></span>private String name;<span style="white-space:pre"></span>private String phone;<span style="white-space:pre"></span>private String email;<span style="white-space:pre"></span>private String address;<span style="white-space:pre"></span>private int groupId;<span style="white-space:pre"></span>private String gName;<span style="white-space:pre"></span>public ContactsVO(int id, String name, String phone, String email,<span style="white-space:pre"></span>String address, int groupId, String gName) {<span style="white-space:pre"></span>super();<span style="white-space:pre"></span>this.id = id;<span style="white-space:pre"></span>this.name = name;<span style="white-space:pre"></span>this.phone = phone;<span style="white-space:pre"></span>this.email = email;<span style="white-space:pre"></span>this.address = address;<span style="white-space:pre"></span>this.groupId = groupId;<span style="white-space:pre"></span>this.gName = gName;<span style="white-space:pre"></span>}<span style="white-space:pre"></span>public int getId() {<span style="white-space:pre"></span>return id;<span style="white-space:pre"></span>}<span style="white-space:pre"></span>public void setId(int id) {<span style="white-space:pre"></span>this.id = id;<span style="white-space:pre"></span>}<span style="white-space:pre"></span>public String getName() {<span style="white-space:pre"></span>return name;<span style="white-space:pre"></span>}<span style="white-space:pre"></span>public void setName(String name) {<span style="white-space:pre"></span>this.name = name;<span style="white-space:pre"></span>}<span style="white-space:pre"></span>public String getPhone() {<span style="white-space:pre"></span>return phone;<span style="white-space:pre"></span>}<span style="white-space:pre"></span>public void setPhone(String phone) {<span style="white-space:pre"></span>this.phone = phone;<span style="white-space:pre"></span>}<span style="white-space:pre"></span>public String getEmail() {<span style="white-space:pre"></span>return email;<span style="white-space:pre"></span>}<span style="white-space:pre"></span>public void setEmail(String email) {<span style="white-space:pre"></span>this.email = email;<span style="white-space:pre"></span>}<span style="white-space:pre"></span>public String getAddress() {<span style="white-space:pre"></span>return address;<span style="white-space:pre"></span>}<span style="white-space:pre"></span>public void setAddress(String address) {<span style="white-space:pre"></span>this.address = address;<span style="white-space:pre"></span>}<span style="white-space:pre"></span>public int getGroupId() {<span style="white-space:pre"></span>return groupId;<span style="white-space:pre"></span>}<span style="white-space:pre"></span>public void setGroupId(int groupId) {<span style="white-space:pre"></span>this.groupId = groupId;<span style="white-space:pre"></span>}<span style="white-space:pre"></span>public String getgName() {<span style="white-space:pre"></span>return gName;<span style="white-space:pre"></span>}<span style="white-space:pre"></span>public void setgName(String gName) {<span style="white-space:pre"></span>this.gName = gName;<span style="white-space:pre"></span>}<span style="white-space:pre"></span>}

DBManager.Class

import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class DBManager {static final String url = "jdbc:mysql://localhost:3306/java03";static final String user = "root";static final String password = "123456";/** * 创建数据库连接 */public static Connection connectionDB() {Connection conn = null;try {Class.forName("com.mysql.jdbc.Driver");conn = DriverManager.getConnection(url, user, password);} catch (ClassNotFoundException e) {// TODO Auto-generated catch blockSystem.err.println("加载数据库驱动失败");e.printStackTrace();} catch (SQLException e) {// TODO Auto-generated catch blockSystem.err.println("数据库连接失败");e.printStackTrace();}return conn;}/** * 关闭数据库连接 *  * @param conn * @param stmt * @param rs */public static void closeConnection(Connection conn, Statement stmt,ResultSet rs) {try {if (rs != null) {rs.close();}if (stmt != null) {stmt.close();}if (conn != null) {conn.close();}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}

DBOperator.Class

import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.HashMap;import java.util.List;/** *  * @author chenmefly *  */public class DBOperator {/** * 增删改 *  * @param conn * @param sql * @return */public static boolean excuteUpdate(Connection conn, String sql) {boolean flag = false;Statement stmt = null;try {stmt = conn.createStatement();flag = stmt.executeUpdate(sql) > 0 ? true : false;} catch (SQLException e) {System.err.println("sql执行失败");e.printStackTrace();} finally {DBManager.closeConnection(conn, stmt, null);}return flag;}/** * 查询 *  * @param conn * @param sql * @return */public static List<HashMap<String, Object>> excuteQuery(Connection conn, String sql) {Statement stmt = null;ResultSet rs = null;List<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();try {stmt = conn.createStatement();rs = stmt.executeQuery(sql);// 解析结果集， 把每一行数据 放到 HashMap，然后把 所有的HashMap添加到 ListResultSetMetaData rsmd = rs.getMetaData();while (rs.next()) {HashMap<String, Object> hashMap = new HashMap<String, Object>();for (int i = 1; i <= rsmd.getColumnCount(); i++) {hashMap.put(rsmd.getColumnName(i), rs.getObject(i));}list.add(hashMap);}} catch (SQLException e) {System.err.println("sql查询失败");e.printStackTrace();} finally {DBManager.closeConnection(conn, stmt, rs);}return list;}/** * 增删改 * <span style="font-family: Arial, Helvetica, sans-serif;">PreparedStatement</span> * @param stmt * @return */public static boolean excuteUpdate(PreparedStatement stmt) {boolean flag = false;try {flag = stmt.executeUpdate() > 0 ? true : false;} catch (SQLException e) {System.err.println("sql执行失败");e.printStackTrace();}return flag;}/** * <span style="font-family: Arial, Helvetica, sans-serif;">PreparedStatement</span>查询 *  * @param stmt * @return */public static List<HashMap<String, Object>> excuteQuery(PreparedStatement stmt) {ResultSet rs = null;List<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();try {rs = stmt.executeQuery();// 解析结果集， 把每一行数据 放到 HashMap，然后把 所有的HashMap添加到 ListResultSetMetaData rsmd = rs.getMetaData();while (rs.next()) {HashMap<String, Object> hashMap = new HashMap<String, Object>();for (int i = 1; i <= rsmd.getColumnCount(); i++) {hashMap.put(rsmd.getColumnName(i), rs.getObject(i));}list.add(hashMap);}} catch (SQLException e) {System.err.println("sql查询失败");e.printStackTrace();} finally {DBManager.closeConnection(null, null, rs);}return list;}}

ContactsDao接口

public interface ContactsDao {//数据库操作 要设计那些方法？public boolean addContacts(Contacts contact);public boolean removeContactsById(int id) throws SQLException ;public boolean modifyContacts(Contacts contact);public List<ContactsVO> queryContactsAll();public Contacts queryContactsById(int id);}

ContactsDaoImpl.Class

继承DBOperator类实现Contacts接口

public class ContactsDaoImpl extends DBOperator implements ContactsDao {@Overridepublic boolean addContacts(Contacts contact) {// TODO Auto-generated method stubreturn false;}@Overridepublic boolean removeContactsById(int id) throws SQLException {// TODO Auto-generated method stubreturn false;}@Overridepublic boolean modifyContacts(Contacts contact) {// TODO Auto-generated method stubreturn false;}@Overridepublic List<ContactsVO> queryContactsAll() {List<ContactsVO> contacts = new ArrayList<ContactsVO>();String sql = "select a.*,b.gname from contacts a, groups b where a.groupid=b.id";List<HashMap<String, Object>> list = super.executeQuery(DBManager.getConnection(), sql);if(list!=null && list.size()>0){for (HashMap<String, Object> hashMap : list) {ContactsVO contact = new ContactsVO((int)hashMap.get("id"), (String)hashMap.get("name"),  (String)hashMap.get("phone"),  (String)hashMap.get("email"),  (String)hashMap.get("address"), (int)hashMap.get("groupid"),(String)hashMap.get("gname"));contacts.add(contact);}}return contacts;}@Overridepublic Contacts queryContactsById(int id) {// TODO Auto-generated method stubreturn null;}}

DaoFactory.Class

public class DAOFactory {public static ContactsDao createContactsDao(){return new ContactsDaoImpl();}}