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 block
			System.err.println("加载数据库驱动失败");
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.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 block
			e.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添加到 List
			ResultSetMetaData 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添加到 List
			ResultSetMetaData 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 {

	@Override
	public boolean addContacts(Contacts contact) {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public boolean removeContactsById(int id) throws SQLException {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public boolean modifyContacts(Contacts contact) {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public 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;
	}

	@Override
	public Contacts queryContactsById(int id) {
		// TODO Auto-generated method stub
		return null;
	}

}

DaoFactory.Class

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


}