11.5.2　封装数据库操作

聊天机器人涉及的数据库操作主要包括获取问答知识表所有记录、获取上一次的聊天类别、根据知识id从问答知识分表随机获取一个答案、随机获取一条笑话和保存聊天记录。笔者将这些数据库操作统一封装到工具类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.List;
  10. import org.liufeng.course.pojo.Knowledge;
  11.  
  12. /**
  13.  * MySQL数据库操作类
  14.  * 
  15.  * @author liufeng
  16.  * @date 2013-12-01
  17.  */
  18. public class MySQLUtil {
  19.     /**
  20.      * 获取MySQL数据库连接
  21.      * 
  22.      * @return Connection
  23.      */
  24.     private Connection getConn() {
  25.         String url = "jdbc:mysql:// localhost:3306/liufeng";
  26.         String username = "liufeng";
  27.         String password = "lyq617617";
  28.         Connection conn = null;
  29.         try {
  30.             // 加载MySQL驱动
  31.             Class.forName("com.mysql.jdbc.Driver");
  32.             // 获取数据库连接
  33.             conn = DriverManager.getConnection(url, username, password);
  34.         } catch (Exception e) {
  35.             e.printStackTrace();
  36.         }
  37.         return conn;
  38.     }
  39.  
  40.     /**
  41.      * 释放JDBC资源
  42.      * 
  43.      * @param conn 数据库连接
  44.      * @param ps
  45.      * @param rs 记录集
  46.      */
  47.     private void releaseResources(Connection conn, PreparedStatement ps,
  48.     ResultSet rs) {
  49.         try {
  50.             if (null != rs)
  51.                 rs.close();
  52.             if (null != ps)
  53.                 ps.close();
  54.             if (null != conn)
  55.                 conn.close();
  56.         } catch (Exception e) {
  57.             e.printStackTrace();
  58.         }
  59.     }
  60.  
  61.     /**
  62.      * 获取问答知识表中所有记录
  63.      * 
  64.      * @return List<Knowledge>
  65.      */
  66.     public static List<Knowledge> findAllKnowledge() {
  67.         List<Knowledge> knowledgeList = new ArrayList<Knowledge>();
  68.         String sql = "select * from knowledge";
  69.         MySQLUtil mysqlUtil = new MySQLUtil();
  70.         Connection conn = null;
  71.         PreparedStatement ps = null;
  72.         ResultSet rs = null;
  73.         try {
  74.             conn = mysqlUtil.getConn();
  75.             ps = conn.prepareStatement(sql);
  76.             rs = ps.executeQuery();
  77.             while (rs.next()) {
  78.                 Knowledge knowledge = new Knowledge();
  79.                 knowledge.setId(rs.getInt("id"));
  80.                 knowledge.setQuestion(rs.getString("question"));
  81.                 knowledge.setAnswer(rs.getString("answer"));
  82.                 knowledge.setCategory(rs.getInt("category"));
  83.                 knowledgeList.add(knowledge);
  84.             }
  85.         } catch (SQLException e) {
  86.             e.printStackTrace();
  87.         } finally {
  88.             // 释放资源
  89.             mysqlUtil.releaseResources(conn, ps, rs);
  90.         }
  91.         return knowledgeList;
  92.     }
  93.  
  94.     /**
  95.      * 获取上一次的聊天类别
  96.      * 
  97.      * @param openId 用户的OpenID
  98.      * @return chatCategory
  99.      */
  100.     public static int getLastCategory(String openId) {
  101.         int chatCategory = -1;
  102.         String sql = "select chat_category from chat_log 
  103.         where open_id=? order by id desc limit 0,1";
  104.  
  105.         MySQLUtil mysqlUtil = new MySQLUtil();
  106.         Connection conn = null;
  107.         PreparedStatement ps = null;
  108.         ResultSet rs = null;
  109.         try {
  110.             conn = mysqlUtil.getConn();
  111.             ps = conn.prepareStatement(sql);
  112.             ps.setString(1, openId);
  113.             rs = ps.executeQuery();
  114.             if (rs.next()) {
  115.                 chatCategory = rs.getInt("chat_category");
  116.             }
  117.         } catch (SQLException e) {
  118.             e.printStackTrace();
  119.         } finally {
  120.             // 释放资源
  121.             mysqlUtil.releaseResources(conn, ps, rs);
  122.         }
  123.         return chatCategory;
  124.     }
  125.  
  126.     /**
  127.      * 根据知识id随机获取一个答案
  128.      * 
  129.      * @param knowledgeId 问答知识id
  130.      * @return
  131.      */
  132.     public static String getKnowledSub(int knowledgeId) {
  133.         String knowledgeAnswer = "";
  134.         String sql = "select answer from knowledge_sub 
  135.                     where pid=? order by rand() limit 0,1";
  136.  
  137.         MySQLUtil mysqlUtil = new MySQLUtil();
  138.         Connection conn = null;
  139.         PreparedStatement ps = null;
  140.         ResultSet rs = null;
  141.         try {
  142.             conn = mysqlUtil.getConn();
  143.             ps = conn.prepareStatement(sql);
  144.             ps.setInt(1, knowledgeId);
  145.             rs = ps.executeQuery();
  146.             if (rs.next()) {
  147.                 knowledgeAnswer = rs.getString("answer");
  148.             }
  149.         } catch (SQLException e) {
  150.             e.printStackTrace();
  151.         } finally {
  152.             // 释放资源
  153.             mysqlUtil.releaseResources(conn, ps, rs);
  154.         }
  155.         return knowledgeAnswer;
  156.     }
  157.  
  158.     /**
  159.      * 随机获取一条笑话
  160.      * 
  161.      * @return String
  162.      */
  163.     public static String getJoke() {
  164.         String jokeContent = "";
  165.         String sql = "select joke_content 
  166.                     from joke order by rand() limit 0,1";
  167.  
  168.         MySQLUtil mysqlUtil = new MySQLUtil();
  169.         Connection conn = null;
  170.         PreparedStatement ps = null;
  171.         ResultSet rs = null;
  172.         try {
  173.             conn = mysqlUtil.getConn();
  174.             ps = conn.prepareStatement(sql);
  175.             rs = ps.executeQuery();
  176.             if (rs.next()) {
  177.                 jokeContent = rs.getString("joke_content");
  178.             }
  179.         } catch (SQLException e) {
  180.             e.printStackTrace();
  181.         } finally {
  182.             // 释放资源
  183.             mysqlUtil.releaseResources(conn, ps, rs);
  184.         }
  185.         return jokeContent;
  186.     }
  187.  
  188.     /**
  189.      * 保存聊天记录
  190.      * 
  191.      * @param openId 用户的OpenID
  192.      * @param createTime 消息创建时间
  193.      * @param reqMsg 用户上行的消息
  194.      * @param respMsg 公众账号回复的消息
  195.      * @param chatCategory 聊天类别
  196.      */
  197.     public static void saveChatLog(String openId, 
  198.                         String createTime, String reqMsg, 
  199.                         String respMsg, int chatCategory) {
  200.         String sql = "insert into chat_log(open_id, 
  201.                         create_time, req_msg, resp_msg, chat_category) 
  202.                         values(?, ?, ?, ?, ?)";
  203.  
  204.         MySQLUtil mysqlUtil = new MySQLUtil();
  205.         Connection conn = null;
  206.         PreparedStatement ps = null;
  207.         ResultSet rs = null;
  208.         try {
  209.             conn = mysqlUtil.getConn();
  210.             ps = conn.prepareStatement(sql);
  211.             ps.setString(1, openId);
  212.             ps.setString(2, createTime);
  213.             ps.setString(3, reqMsg);
  214.             ps.setString(4, respMsg);
  215.             ps.setInt(5, chatCategory);
  216.             ps.executeUpdate();
  217.         } catch (Exception e) {
  218.             e.printStackTrace();
  219.         } finally {
  220.             // 释放资源
  221.             mysqlUtil.releaseResources(conn, ps, rs);
  222.         }
  223.     }
  224. }

上述代码中，使用order by rand()随机获取记录，这种方式比较直观，但当数据量比较大时，查询效率会很低。另一种高效的随机获取记录的方法如下：

  1. SELECT joke_content FROM `joke` AS t1 
  2. JOIN (SELECT ROUND(RAND() * (SELECT MAX(joke_id) FROM `joke`)) AS joke_id) AS t2 
  3. WHERE t1.joke_id>= t2.joke_id 
  4. ORDER BY t1.joke_id ASC LIMIT 1

上面的SQL语句也能够从joke表中随机获取一条记录，虽然看上去实现比较复杂，但执行效率却提高了许多倍。

在MySQLUtil类中，findAllKnowledge()方法的返回值是Knowledge集合，Knowledge类的定义如下：

  1. package org.liufeng.course.pojo;
  2.  
  3. /**
  4.  * 问答知识model
  5.  * 
  6.  * @author liufeng
  7.  * @date 2013-12-01
  8.  */
  9. public class Knowledge {
  10.     private int id;
  11.     private String question;
  12.     private String answer;
  13.     private int category;
  14.  
  15.     public int getId() {
  16.         return id;
  17.     }
  18.  
  19.     public void setId(int id) {
  20.         this.id = id;
  21.     }
  22.  
  23.     public String getQuestion() {
  24.         return question;
  25.     }
  26.  
  27.     public void setQuestion(String question) {
  28.         this.question = question;
  29.     }
  30.  
  31.     public String getAnswer() {
  32.         return answer;
  33.     }
  34.  
  35.     public void setAnswer(String answer) {
  36.         this.answer = answer;
  37.     }
  38.  
  39.     public int getCategory() {
  40.         return category;
  41.     }
  42.  
  43.     public void setCategory(int category) {
  44.         this.category = category;
  45.     }
  46. }