閱讀高手編寫的類似QQ聊天的服務端代碼DAO設計總結，服務端dao

閱讀高手編寫的類似QQ聊天的服務端代碼DAO設計總結，服務端dao

1、資料訪問層DAO的設計（已該工程為例）

（1）首先定義一個介面,規範演算法架構。（若子類和基類有很多公用的東西，則應該設計成抽象類別）

package com.way.chat.dao

public interface UserDao {//註冊成功返回使用者idpublic int register(User u);public ArrayList<User> login(User u);public ArrayList<User> refresh(int id);public void logout(int id);}

（2）在該介面的實作類別中定義一個單例的靜態Factory 方法（避免資料庫操作對象重複）來提供資料庫操作對象。

 

import com.way.chat.dao.UserDao;public class UserDaoFactory {private static <span style="color:#ff0000;">UserDa</span>o dao;public static UserDao getInstance() {if (dao == null) {dao = new <span style="color:#ff0000;">UserDaoImpl()</span>;}return dao;}}

（3）該介面的具體實作類別

package com.way.chat.dao.impl;

public class UserDaoImpl implements UserDao {@Overridepublic int register(User u) {int id;Connection con = DButil.connect();String sql1 = "insert into user(_name,_password,_email,_time) values(?,?,?,?)";String sql2 = "select _id from user";try {PreparedStatement ps = con.prepareStatement(sql1);ps.setString(1, u.getName());ps.setString(2, u.getPassword());ps.setString(3, u.getEmail());ps.setString(4, MyDate.getDateCN());int res = ps.executeUpdate();if (res > 0) {PreparedStatement ps2 = con.prepareStatement(sql2);ResultSet rs = ps2.executeQuery();if (rs.last()) {id = rs.getInt("_id");createFriendtable(id);// 註冊成功後，建立一個已使用者id為表名的表，用於存放好友資訊return id;}}} catch (SQLException e) { e.printStackTrace();} finally {DButil.close(con);}return Constants.REGISTER_FAIL;}@Overridepublic ArrayList<User> login(User u) {Connection con = DButil.connect();String sql = "select * from user where _id=? and _password=?";try {PreparedStatement ps = con.prepareStatement(sql);ps.setInt(1, u.getId());ps.setString(2, u.getPassword());ResultSet rs = ps.executeQuery();if (rs.first()) {setOnline(u.getId());// 更新表狀態為線上ArrayList<User> refreshList = refresh(u.getId());return refreshList;}} catch (SQLException e) {// e.printStackTrace();} finally {DButil.close(con);}return null;}/** * 尋找自己 */public User findMe(int id) {User me = new User();Connection con = DButil.connect();String sql = "select * from user where _id=?";PreparedStatement ps;try {ps = con.prepareStatement(sql);ps.setInt(1, id);ResultSet rs = ps.executeQuery();if (rs.first()) {me.setId(rs.getInt("_id"));me.setEmail(rs.getString("_email"));me.setName(rs.getString("_name"));me.setImg(rs.getInt("_img"));}return me;} catch (SQLException e) {// e.printStackTrace();} finally {DButil.close(con);}return null;}/** * 重新整理好友名單 */public ArrayList<User> refresh(int id) {ArrayList<User> list = new ArrayList<User>();User me = findMe(id);list.add(me);// 先添加自己Connection con = DButil.connect();String sql = "select * from _? ";PreparedStatement ps;try {ps = con.prepareStatement(sql);ps.setInt(1, id);ResultSet rs = ps.executeQuery();if (rs.first()) {do {User friend = new User();friend.setId(rs.getInt("_qq"));friend.setName(rs.getString("_name"));friend.setIsOnline(rs.getInt("_isOnline"));friend.setImg(rs.getInt("_img"));friend.setGroup(rs.getInt("_group"));list.add(friend);} while (rs.next());}return list;} catch (SQLException e) {// e.printStackTrace();} finally {DButil.close(con);}return null;}/** * 設定狀態為線上 *  * @param id */public void setOnline(int id) {Connection con = DButil.connect();try {String sql = "update user set _isOnline=1 where _id=?";PreparedStatement ps = con.prepareStatement(sql);ps.setInt(1, id);ps.executeUpdate();updateAllOn(id);// 更新所有表狀態為線上} catch (SQLException e) {// e.printStackTrace();} finally {DButil.close(con);}}/** * 註冊成功後，建立一個使用者表，儲存該使用者好友 *  * @param id */public void createFriendtable(int id) {Connection con = DButil.connect();try {String sql = "create table _" + id+ " (_id int auto_increment not null primary key,"+ "_name varchar(20) not null,"+ "_isOnline int(11) not null default 0,"+ "_group int(11) not null default 0,"+ "_qq int(11) not null default 0,"+ "_img int(11) not null default 0)";PreparedStatement ps = con.prepareStatement(sql);int res = ps.executeUpdate();System.out.println(res);} catch (SQLException e) {e.printStackTrace();} finally {DButil.close(con);}}@Override/** * 下線更新狀態為離線 */public void logout(int id) {Connection con = DButil.connect();try {String sql = "update user set _isOnline=0 where _id=?";PreparedStatement ps = con.prepareStatement(sql);ps.setInt(1, id);ps.executeUpdate();updateAllOff(id);// System.out.println(res);} catch (SQLException e) {// e.printStackTrace();} finally {DButil.close(con);}}/** * 更新所有使用者表狀態為離線 *  * @param id */public void updateAllOff(int id) {Connection con = DButil.connect();try {String sql = "update _? set _isOnline=0 where _qq=?";PreparedStatement ps = con.prepareStatement(sql);for (int offId : getAllId()) {ps.setInt(1, offId);ps.setInt(2, id);ps.executeUpdate();}} catch (SQLException e) {// e.printStackTrace();} finally {DButil.close(con);}}/** * 更新所有使用者狀態為上線 *  * @param id */public void updateAllOn(int id) {Connection con = DButil.connect();try {String sql = "update _? set _isOnline=1 where _qq=?";PreparedStatement ps = con.prepareStatement(sql);for (int OnId : getAllId()) {ps.setInt(1, OnId);ps.setInt(2, id);ps.executeUpdate();}} catch (SQLException e) {// e.printStackTrace();} finally {DButil.close(con);}}public List<Integer> getAllId() {Connection con = DButil.connect();List<Integer> list = new ArrayList<Integer>();try {String sql = "select _id from user";PreparedStatement ps = con.prepareStatement(sql);ResultSet rs = ps.executeQuery();if (rs.first()) {do {int id = rs.getInt("_id");list.add(id);} while (rs.next());}// System.out.println(list);return list;} catch (SQLException e) {// e.printStackTrace();} finally {DButil.close(con);}return null;}public static void main(String[] args) {User u = new User();UserDaoImpl dao = new UserDaoImpl();// u.setId(2016);// u.setName("qq");// u.setPassword("123");// u.setEmail("158342219@qq.com");// System.out.println(dao.register(u));// // System.out.println(dao.login(u));// // dao.logout(2016);// dao.setOnline(2016);// // dao.getAllId();List<User> list = dao.refresh(2016);System.out.println(list);}}

（4）資料庫利用屬性檔案進行載入驅動關閉串連的工具類：

public class DButil {/** * 串連資料庫 *  * @return 資料庫連接對象 */public static Connection connect() {Properties pro = new Properties();String driver = null;String url = null;String username = null;String password = null;try {InputStream is = DButil.class.getClassLoader().getResourceAsStream("DB.properties");// System.out.println(is.toString());pro.load(is);driver = pro.getProperty("driver");url = pro.getProperty("url");username = pro.getProperty("username");password = pro.getProperty("password");// System.out.println(driver + ":" + url + ":" + username + ":"// + password);Class.forName(driver);Connection conn = DriverManager.getConnection(url, username,password);return conn;} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}return null;}/** * 關閉資料庫 *  * @param conn *            傳入資料庫連接對象 */public static void close(Connection con) {if (con != null) {try {con.close();} catch (SQLException e) {e.printStackTrace();}}}//public static void main(String[] args) {//Connection con = new DButil().connect();//System.out.println(con);//}}

對應設定檔：DB.properties

driver=com.mysql.jdbc.Driverurl=jdbc:mysql://localhost:3306/qq?useUnicode=true&characterEncoding=utf-8username=rootpassword=admin