10.2.3　数据库操作封装

猜数字游戏涉及的数据库操作主要包括：保存（创建）游戏信息、更新游戏状态、获取用户最近一次创建的游戏、保存游戏回合信息和获取某局游戏的所有回合。笔者将所有数据库操作统一封装到工具类MySQLUtil中，该类的完整代码如下：

  1. package org.liufeng.course.util;
  2.  
  3. import java.sql.Connection;
  4. import java.sql.DriverManager;
  5. import java.sql.PreparedStatement;
  6. import java.sql.ResultSet;
  7. import java.sql.SQLException;
  8. import java.util.ArrayList;
  9. import java.util.HashMap;
  10. import java.util.List;
  11. import javax.servlet.http.HttpServletRequest;
  12. import org.liufeng.course.pojo.Game;
  13. import org.liufeng.course.pojo.GameRound;
  14.  
  15. /**
  16.  * MySQL数据库操作类
  17.  * 
  18.  * @author liufeng
  19.  * @date 2013-11-21
  20.  */
  21. public class MySQLUtil {
  22.     /**
  23.      * 获取MySQL数据库连接
  24.      * 
  25.      * @return Connection
  26.      */
  27.     private Connection getConn(HttpServletRequest request) {
  28.         Connection conn = null;
  29.  
  30.         // 从request请求头中取出IP、端口、用户名和密码
  31.         String host = request.getHeader("BAE_ENV_ADDR_SQL_IP");
  32.         String port = request.getHeader("BAE_ENV_ADDR_SQL_PORT");
  33.         String username = request.getHeader("BAE_ENV_AK");
  34.         String password = request.getHeader("BAE_ENV_SK");
  35.         // 数据库名称
  36.         String dbName = "FTGJUvPHrbXsLGsYpwlp";
  37.         // JDBC URL
  38.         String url = String.format("jdbc:mysql://%s:%s/%s", host, port, dbName);
  39.  
  40.         try {
  41.             // 加载MySQL驱动
  42.             Class.forName("com.mysql.jdbc.Driver");
  43.             // 获取数据库连接
  44.             conn = DriverManager.getConnection(url, username, password);
  45.         } catch (Exception e) {
  46.             e.printStackTrace();
  47.         }
  48.         return conn;
  49.     }
  50.  
  51.     /**
  52.      * 释放JDBC资源
  53.      * 
  54.      * @param conn 数据库连接
  55.      * @param ps
  56.      * @param rs 记录集
  57.      */
  58.     private void releaseResources(Connection conn, PreparedStatement ps, ResultSet rs) {
  59.         try {
  60.             if (null != rs)
  61.                 rs.close();
  62.             if (null != ps)
  63.                 ps.close();
  64.             if (null != conn)
  65.                 conn.close();
  66.         } catch (Exception e) {
  67.             e.printStackTrace();
  68.         }
  69.     }
  70.  
  71.     /**
  72.      * 保存游戏信息
  73.      * 
  74.      * @param request 请求对象
  75.      * @param game 游戏对象
  76.      * @return gameId
  77.      */
  78.     public static int saveGame(HttpServletRequest request, Game game) {
  79.         int gameId = -1;
  80.         String sql = "insert into game(open_id, game_answer, create_time, game_status, finish_time) values(?, ?, ?, ?, ?)";
  81.  
  82.         MySQLUtil mysqlUtil = new MySQLUtil();
  83.         Connection conn = null;
  84.         PreparedStatement ps = null;
  85.         ResultSet rs = null;
  86.         try {
  87.             conn = mysqlUtil.getConn(request);
  88.             // 保存游戏
  89.             ps = conn.prepareStatement(sql);
  90.             ps.setString(1, game.getOpenId());
  91.             ps.setString(2, game.getGameAnswer());
  92.             ps.setString(3, game.getCreateTime());
  93.             ps.setInt(4, game.getGameStatus());
  94.             ps.setString(5, game.getFinishTime());
  95.             ps.executeUpdate();
  96.             // 获取游戏的id
  97.             sql = "select game_id from game where open_id=? and game_answer=? order by game_id desc limit 0,1";
  98.             ps = conn.prepareStatement(sql);
  99.             ps.setString(1, game.getOpenId());
  100.             ps.setString(2, game.getGameAnswer());
  101.             rs = ps.executeQuery();
  102.             if (rs.next()) {
  103.                 gameId = rs.getInt("game_id");
  104.             }
  105.         } catch (Exception e) {
  106.             e.printStackTrace();
  107.         } finally {
  108.             // 释放资源
  109.             mysqlUtil.releaseResources(conn, ps, rs);
  110.         }
  111.         return gameId;
  112.     }
  113.  
  114.     /**
  115.      * 获取用户最近一次创建的游戏 <br>
  116.      * 
  117.      * @param request 请求对象
  118.      * @param openId 用户的OpendID
  119.      * @return
  120.      */
  121.     public static Game getLastGame(HttpServletRequest request, String openId) {
  122.         Game game = null;
  123.         String sql = "select * from game where open_id=? order by game_id desc limit 0,1";
  124.  
  125.         MySQLUtil mysqlUtil = new MySQLUtil();
  126.         Connection conn = null;
  127.         PreparedStatement ps = null;
  128.         ResultSet rs = null;
  129.         try {
  130.             conn = mysqlUtil.getConn(request);
  131.             ps = conn.prepareStatement(sql);
  132.             ps.setString(1, openId);
  133.             rs = ps.executeQuery();
  134.             if (rs.next()) {
  135.                 game = new Game();
  136.                 game.setGameId(rs.getInt("game_id"));
  137.                 game.setOpenId(rs.getString("open_id"));
  138.                 game.setGameAnswer(rs.getString("game_answer"));
  139.                 game.setCreateTime(rs.getString("create_time"));
  140.                 game.setGameStatus(rs.getInt("game_status"));
  141.                 game.setFinishTime(rs.getString("finish_time"));
  142.             }
  143.         } catch (SQLException e) {
  144.             game = null;
  145.             e.printStackTrace();
  146.         } finally {
  147.             // 释放资源
  148.             mysqlUtil.releaseResources(conn, ps, rs);
  149.         }
  150.         return game;
  151.     }
  152.  
  153.     /**
  154.      * 根据游戏id修改游戏状态和完成时间
  155.      * 
  156.      * @param request 请求对象
  157.      * @param gameId 游戏id
  158.      * @param gameStatus 游戏状态（0:游戏中 1:成功 2:失败 3:取消）
  159.      * @param finishTime 游戏完成时间
  160.      */
  161.     public static void updateGame(HttpServletRequest request, int gameId, int gameStatus, 
  162.     String finishTime) {
  163.         String sql = "update game set game_status=?, finish_time=? where game_id=?";
  164.         MySQLUtil mysqlUtil = new MySQLUtil();
  165.         Connection conn = null;
  166.         PreparedStatement ps = null;
  167.         try {
  168.             conn = mysqlUtil.getConn(request);
  169.             ps = conn.prepareStatement(sql);
  170.             ps.setInt(1, gameStatus);
  171.             ps.setString(2, finishTime);
  172.             ps.setInt(3, gameId);
  173.             ps.executeUpdate();
  174.         } catch (Exception e) {
  175.             e.printStackTrace();
  176.         } finally {
  177.             // 释放资源
  178.             mysqlUtil.releaseResources(conn, ps, null);
  179.         }
  180.     }
  181.  
  182.     /**
  183.      * 保存游戏的回合信息
  184.      * 
  185.      * @param request 请求对象
  186.      * @param gameRound 游戏回合对象
  187.      */
  188.     public static void saveGameRound(HttpServletRequest request, GameRound gameRound) {
  189.         String sql = "insert into game_round(game_id, open_id, guess_number, guess_time, guess_result) values (?, ?, ?, ?, ?)";
  190.         MySQLUtil mysqlUtil = new MySQLUtil();
  191.         Connection conn = null;
  192.         PreparedStatement ps = null;
  193.         try {
  194.             conn = mysqlUtil.getConn(request);
  195.             ps = conn.prepareStatement(sql);
  196.             ps.setInt(1, gameRound.getGameId());
  197.             ps.setString(2, gameRound.getOpenId());
  198.             ps.setString(3, gameRound.getGuessNumber());
  199.             ps.setString(4, gameRound.getGuessTime());
  200.             ps.setString(5, gameRound.getGuessResult());
  201.             ps.executeUpdate();
  202.         } catch (Exception e) {
  203.             e.printStackTrace();
  204.         } finally {
  205.             // 释放资源
  206.             mysqlUtil.releaseResources(conn, ps, null);
  207.         }
  208.     }
  209.  
  210.     /**
  211.      * 根据游戏id获取游戏的全部回合<br>
  212.      * 
  213.      * @param request 请求对象
  214.      * @param gameId 游戏id
  215.      * @return
  216.      */
  217.     public static List<GameRound> findAllRoundByGameId(HttpServletRequest request,
  218.     int gameId) {
  219.         List<GameRound> roundList = new ArrayList<GameRound>();
  220.         // 根据id升序排序
  221.         String sql = "select * from game_round where game_id=? order by id asc";
  222.         MySQLUtil mysqlUtil = new MySQLUtil();
  223.         Connection conn = null;
  224.         PreparedStatement ps = null;
  225.         ResultSet rs = null;
  226.         try {
  227.             conn = mysqlUtil.getConn(request);
  228.             ps = conn.prepareStatement(sql);
  229.             ps.setInt(1, gameId);
  230.             rs = ps.executeQuery();
  231.             GameRound round = null;
  232.             while (rs.next()) {
  233.                 round = new GameRound();
  234.                 round.setGameId(rs.getInt("game_id"));
  235.                 round.setOpenId(rs.getString("open_id"));
  236.                 round.setGuessNumber(rs.getString("guess_number"));
  237.                 round.setGuessTime(rs.getString("guess_time"));
  238.                 round.setGuessResult(rs.getString("guess_result"));
  239.                 roundList.add(round);
  240.             }
  241.         } catch (SQLException e) {
  242.             e.printStackTrace();
  243.         } finally {
  244.             // 释放资源
  245.             mysqlUtil.releaseResources(conn, ps, rs);
  246.         }
  247.         return roundList;
  248.     }
  249.  
  250.     /**
  251.      * 获取用户的战绩
  252.      * 
  253.      * @param request 请求对象
  254.      * @param openId 用户的OpenID
  255.      * @return HashMap<Integer, Integer>
  256.      */
  257.     public static HashMap<Integer, Integer> getScoreByOpenId(HttpServletRequest
  258.     request, String openId) {
  259.         HashMap<Integer, Integer> scoreMap = new HashMap<Integer, Integer>();
  260.         // 根据id升序排序
  261.         String sql = "select game_status,count(*) from game where open_id=? group 
  262.         by game_status order by game_status asc";
  263.         MySQLUtil mysqlUtil = new MySQLUtil();
  264.         Connection conn = null;
  265.         PreparedStatement ps = null;
  266.         ResultSet rs = null;
  267.         try {
  268.             conn = mysqlUtil.getConn(request);
  269.             ps = conn.prepareStatement(sql);
  270.             ps.setString(1, openId);
  271.             rs = ps.executeQuery();
  272.             while (rs.next()) {
  273.                 scoreMap.put(rs.getInt(1), rs.getInt(2));
  274.             }
  275.         } catch (SQLException e) {
  276.             e.printStackTrace();
  277.         } finally {
  278.             // 释放资源
  279.             mysqlUtil.releaseResources(conn, ps, rs);
  280.         }
  281.         return scoreMap;
  282.     }
  283. }

上述代码的最后定义了一个getScoreByOpenId()方法，用于统计指定用户的游戏战绩，可以统计出每种状态（游戏中、胜利和失败）的游戏局数。

在MySQLUtil类中，还用到了两个自定义的类Game和GameRound，分别与10.1.4节中设计的数据表game、game_round相对应。Game类的定义如下：

  1. package org.liufeng.course.pojo;
  2.  
  3. /**
  4.  * 游戏model
  5.  * 
  6.  * @author liufeng
  7.  * @date 2013-11-21
  8.  */
  9. public class Game {
  10.     private int gameId;
  11.     private String openId;
  12.     private String gameAnswer;
  13.     private String createTime;
  14.     private int gameStatus;
  15.     private String finishTime;
  16.  
  17.     public int getGameId() {
  18.         return gameId;
  19.     }
  20.  
  21.     public void setGameId(int gameId) {
  22.         this.gameId = gameId;
  23.     }
  24.  
  25.     public String getOpenId() {
  26.         return openId;
  27.     }
  28.  
  29.     public void setOpenId(String openId) {
  30.         this.openId = openId;
  31.     }
  32.  
  33.     public String getGameAnswer() {
  34.         return gameAnswer;
  35.     }
  36.  
  37.     public void setGameAnswer(String gameAnswer) {
  38.         this.gameAnswer = gameAnswer;
  39.     }
  40.  
  41.     public String getCreateTime() {
  42.         return createTime;
  43.     }
  44.     public void setCreateTime(String createTime) {
  45.         this.createTime = createTime;
  46.     }
  47.  
  48.     public int getGameStatus() {
  49.         return gameStatus;
  50.     }
  51.  
  52.     public void setGameStatus(int gameStatus) {
  53.         this.gameStatus = gameStatus;
  54.     }
  55.  
  56.     public String getFinishTime() {
  57.         return finishTime;
  58.     }
  59.  
  60.     public void setFinishTime(String finishTime) {
  61.         this.finishTime = finishTime;
  62.     }
  63. }

GameRound类的定义如下：

  1. package org.liufeng.course.pojo;
  2.  
  3. /**
  4.  * 游戏回合model
  5.  * 
  6.  * @author liufeng
  7.  * @date 2013-11-21
  8.  */
  9. public class GameRound {
  10.     private int id;
  11.     private int gameId;
  12.     private String openId;
  13.     private String guessNumber;
  14.     private String guessTime;
  15.     private String guessResult;
  16.  
  17.     public int getId() {
  18.         return id;
  19.     }
  20.  
  21.     public void setId(int id) {
  22.         this.id = id;
  23.     }
  24.  
  25.     public int getGameId() {
  26.         return gameId;
  27.     }
  28.     public void setGameId(int gameId) {
  29.         this.gameId = gameId;
  30.     }
  31.  
  32.     public String getOpenId() {
  33.         return openId;
  34.     }
  35.  
  36.     public void setOpenId(String openId) {
  37.         this.openId = openId;
  38.     }
  39.  
  40.     public String getGuessNumber() {
  41.         return guessNumber;
  42.     }
  43.  
  44.     public void setGuessNumber(String guessNumber) {
  45.         this.guessNumber = guessNumber;
  46.     }
  47.  
  48.     public String getGuessTime() {
  49.         return guessTime;
  50.     }
  51.  
  52.     public void setGuessTime(String guessTime) {
  53.         this.guessTime = guessTime;
  54.     }
  55.  
  56.     public String getGuessResult() {
  57.         return guessResult;
  58.     }
  59.  
  60.     public void setGuessResult(String guessResult) {
  61.         this.guessResult = guessResult;
  62.     }
  63. }