Redis查询&JDBC查询&Hibernate查询方式的效率比较...

CREATE TABLE `asset` (
  `assetid` int(100) NOT NULL AUTO_INCREMENT,
  `assetname` varchar(100) DEFAULT NULL,
  `region` varchar(50) DEFAULT NULL,
  `programtype` varchar(50) DEFAULT NULL,
  `releasetime` varchar(100) DEFAULT NULL,
  `director` varchar(50) DEFAULT NULL,
  `actor` varchar(500) DEFAULT NULL,
  `screenwriter` varchar(50) DEFAULT NULL,
  `language` varchar(50) DEFAULT NULL,
  `duration` varchar(50) DEFAULT NULL,
  `alias` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`assetid`)
) ENGINE=InnoDB AUTO_INCREMENT=579430 DEFAULT CHARSET=utf8

 1 import java.io.IOException;
 2 import java.util.Map;
 3 
 4 import javax.servlet.ServletException;
 5 import javax.servlet.http.HttpServlet;
 6 import javax.servlet.http.HttpServletRequest;
 7 import javax.servlet.http.HttpServletResponse;
 8 import javax.servlet.http.HttpSession;
 9 
10 
11 /**
12  * 测试Redis和JDBC查询效率
13  * @author CDV-DX7
14  *
15  */
16 public class RedisSqlSearchServlet extends HttpServlet {
17     private String redisSearchResult;
18     private String jdbcSerachResult;
19     private String hibernateSerachResult;
20 
21 
22     public void doGet(HttpServletRequest request, HttpServletResponse response)
23             throws ServletException, IOException {
24         /**
25          * 1.接收参数
26          */
27         request.setCharacterEncoding("UTF-8");
28         // 1.接收参数
29         Map<String,String[]> searchParaMap = request.getParameterMap();
30         String[] searchtypeArr = searchParaMap.get("searchtype");
31         String[] assetnameArr = searchParaMap.get("assetname");
32         String[] directorArr = searchParaMap.get("director");
33         String[] screenwriterArr = searchParaMap.get("screenwriter");
34         String[] actorArr = searchParaMap.get("actor");
35         String[] programtypeArr = searchParaMap.get("programtype");
36         String[] regionArr = searchParaMap.get("region");
37         String[] languageArr = searchParaMap.get("language");
38         
39         String searchtype = "";
40         String assetname = ""; 
41         String director = ""; 
42         String screenwriter = "";
43         String actor = "";
44         String programtype = "";
45         String region = "";
46         String language = "";
47         
48         if (searchtypeArr!= null){
49             searchtype = searchtypeArr[0];
50         }
51         if (assetnameArr!= null){
52             assetname = assetnameArr[0];
53         }
54         if (directorArr!= null){
55             director = directorArr[0];
56         }
57         if (screenwriterArr!= null){
58             screenwriter = screenwriterArr[0];
59         }
60         if (actorArr!= null){
61             actor = actorArr[0];
62         }
63         if (programtypeArr!= null){
64             programtype = programtypeArr[0];
65         }
66         if (regionArr!= null){
67             region = regionArr[0];
68         }
69         if (languageArr!= null){
70             language = languageArr[0];
71         }
72         
73         HttpSession session = request.getSession();
74         if("Redis查询".equals(searchtype)){
75             redisSearchResult = JedisSearchUtil.redisSearch(assetname, director, actor, screenwriter, region, language, programtype);
76             session.setAttribute("searchResult", redisSearchResult);
77             
78         }else if("JDBC查询Mysql".equals(searchtype)){
79             jdbcSerachResult = JdbcSqlSearchUtil.jdbcSerach(assetname, director, actor, screenwriter, region, language, programtype);
80             session.setAttribute("searchResult", jdbcSerachResult);
81         }else if("Hibernate查询Mysql".equals(searchtype)){
82             hibernateSerachResult = HibernateSearchUtil.hibernateSerach(assetname, director, actor, screenwriter, region, language, programtype);
83             session.setAttribute("searchResult", hibernateSerachResult);
84         }
85         response.sendRedirect(request.getContextPath()+"/searchResult.jsp");
86     }
87 
88     
89     public void doPost(HttpServletRequest request, HttpServletResponse response)
90             throws ServletException, IOException {
91         doGet(request, response);
92     }
93 }

  1 import java.util.ArrayList;
  2 import java.util.HashMap;
  3 import java.util.List;
  4 import java.util.Map;
  5 import java.util.Set;
  6 
  7 import redis.clients.jedis.Jedis;
  8 
  9 public class JedisSearchUtil {
 10     
 11     
 12     public static String redisSearch(String assetname,String director,String actor,
 13             String screenwriter,String region,String language,String programtype){
 14         Jedis jedis = RedisUtil.getJedis();
 15         //0------50万
 16         
 17         //1------100万
 18         jedis.select(1);
 19         
 20         //2------500万
 21         
 22         //3------1000万
 23         
 24         try{
 25             String assetnameKey = "";
 26             String screenwriterKey = "";
 27             String actorKey = "";
 28             String programtypeKey = "";
 29             String regionKey = "";
 30             String languageKey = "";
 31             String directorKey = "";
 32             
 33             List<String> keysList =  new ArrayList<String>();
 34             if(assetname != null && assetname != ""){
 35                 assetnameKey = "asset:assetname:"+ assetname; 
 36                 keysList.add(assetnameKey);
 37             }
 38             if(screenwriter != null && screenwriter != ""){
 39                 screenwriterKey = "asset:screenwriter:"+ screenwriter;
 40                 keysList.add(screenwriterKey);
 41             }
 42             if(actor != null && actor != ""){
 43                 actorKey = "asset:actor:"+ actor;
 44                 keysList.add(actorKey);
 45             }
 46             if(programtype != null && programtype != ""){
 47                 programtypeKey = "asset:programtype:"+ programtype; 
 48                 keysList.add(programtypeKey);
 49             }
 50             if(region != null && region != ""){
 51                 regionKey = "asset:region:"+ region;
 52                 keysList.add(regionKey);
 53             }
 54             if(language != null && language != ""){
 55                 languageKey = "asset:language:"+ language; 
 56                 keysList.add(languageKey);
 57             }
 58             if(director != null && director != ""){
 59                 directorKey = "asset:director:"+ director;
 60                 keysList.add(directorKey);
 61             }
 62             
 63             long start = System.currentTimeMillis();
 64             
 65             int keysListSize = keysList.size();
 66             String [] convertKeysArr = (String[])keysList.toArray(new String[keysListSize]); 
 67             
 68             Set<String> sinterIds = jedis.sinter(convertKeysArr);
 69             long findIdsEnd = System.currentTimeMillis();
 70             
 71             Map<String, String> asset = new HashMap<String, String>();
 72             
 73 //            String actor = "";
 74 //            String director = "";
 75 //            String screenwriter = "";
 76 //            String region = "";
 77 //            String programtype = "";
 78 //            String alias = "";
 79             
 80             long loopbegin = System.currentTimeMillis();
 81             for (String assetid : sinterIds) {
 82 //                long start1 = System.currentTimeMillis();
 83                 asset = jedis.hgetAll("asset:"+assetid);
 84 //                long start11 = System.currentTimeMillis();
 85                 //System.out.println("Redis中取到hash值用时"+(start11-start1));
 86                 
 87                 asset.get("actor");
 88                 asset.get("director");
 89                 asset.get("screenwriter");
 90                 asset.get("region");
 91                 asset.get("programtype");
 92                 asset.get("language");
 93                 //alias = asset.get("alias");
 94                 //System.out.println("主演:"+actor+",导演:"+director+",编剧:"+screenwriter+",地区:"+region+",节目类型:"+programtype+",别名"+alias);        
 95             }
 96             
 97             long loopend = System.currentTimeMillis();
 98             //Reids中共有"+Long.valueOf(jedis.dbSize())+"条键值对;"+"\n
 99             
100             String redisSearchResult = "Redis找到符合条件的id集合用时:"+(findIdsEnd-start)+
101                     "毫秒;\nRedis再遍历符合条件id集合使用时间:"+(loopend-loopbegin)+"毫秒;\nRedis总共花费时间:"+(loopend-start)+
102                     "毫秒;\nRedis共查询出"+sinterIds.size()+"个记录;";
103             return redisSearchResult;            
104         }finally{
105             RedisUtil.returnRedis(jedis);
106         }
107     }
108 }

  1 import java.sql.Connection;
  2 import java.sql.DriverManager;
  3 import java.sql.PreparedStatement;
  4 import java.sql.ResultSet;
  5 import java.sql.SQLException;
  6 import java.util.HashMap;
  7 import java.util.Map;
  8 /**
  9  * JDBC查询Mysql数据库并返回查询结果(查询花费的时间,查询到多少条数)
 10  * @author CDV-DX7
 11  *
 12  */
 13 public class JdbcSqlSearchUtil {
 14     
 15     public static void main(String[] args) {
 16          System.out.println(jdbcSerach("","冯小刚","葛优","顾晓阳","中国大陆","汉语普通话","喜剧"));
 17     }
 18     
 19     public static String jdbcSerach(String assetname,String director,String actor,
 20             String screenwriter,String region,String language,String programtype){
 21          Connection connection = null;
 22          PreparedStatement preparedStatement = null;
 23         //static Statement statement2 = null;//用来统计数据库中的总记录数    
 24          ResultSet resultSet = null;
 25         try {
 26             long start = System.currentTimeMillis();
 27             //加载数据库驱动
 28             Class.forName("com.mysql.jdbc.Driver");
 29             
 30             //通过驱动管理类获取数据库链接
 31             
 32             //50万数据对应的连接
 33             //connection =  DriverManager.getConnection("jdbc:mysql://localhost:3306/test?characterEncoding=utf-8", "root", "root");
 34             
 35             //100万数据对应的连接
 36             connection =  DriverManager.getConnection("jdbc:mysql://localhost:3306/1million?characterEncoding=utf-8", "root", "root");
 37             
 38             //查询数据库中的总记录数
 39             String allRecordSql = "select count(*) from asset";
 40             
 41             
 42             Map<String,Integer> countMap = new HashMap<String,Integer>();
 43             //定义sql语句 ?表示占位符
 44             StringBuilder sql = new StringBuilder();
 45             sql.append("select * from asset where ");
 46             if(assetname != null && assetname != ""){
 47                 sql.append(" assetname like ?");
 48                 countMap.put(assetname,countMap.size()+1);
 49             }
 50             if(director != null && director != ""){
 51                 if(countMap.size() < 1){
 52                     sql.append(" director like ?");
 53                 }else{
 54                     sql.append(" and director like ?");
 55                 }
 56                 countMap.put(director,countMap.size()+1);
 57             }
 58             if(programtype != null && programtype != ""){
 59                 if(countMap.size() < 1){
 60                     sql.append(" programtype like ?");
 61                 }else{
 62                     sql.append(" and programtype like ?");
 63                 }
 64                 countMap.put(programtype,countMap.size()+1);
 65             }
 66             if(actor != null && actor != ""){
 67                 if(countMap.size() < 1){
 68                     sql.append(" actor like ?");
 69                 }else{
 70                     sql.append(" and actor like ?");
 71                 }
 72                 countMap.put(actor,countMap.size()+1);
 73             }
 74             if(screenwriter != null && screenwriter != ""){
 75                 if(countMap.size() < 1){
 76                     sql.append(" screenwriter like ?");
 77                 }else{
 78                     sql.append(" and screenwriter like ?");
 79                 }                
 80                 
 81                 countMap.put(screenwriter,countMap.size()+1);
 82             }    
 83             if(region != null && region != ""){
 84                 if(countMap.size() < 1){
 85                     sql.append(" region like ?");
 86                 }else{
 87                     sql.append(" and region like ?");
 88                 }                
 89                 countMap.put(region,countMap.size()+1);
 90             }    
 91             if(language != null && language != ""){
 92                 if(countMap.size() < 1){
 93                     sql.append(" language like ?");
 94                 }else{
 95                     sql.append(" and language like ?");
 96                 }                
 97                 countMap.put(language,countMap.size()+1);
 98             }
 99             
100 //            StringBuilder sql = "select * from asset where assetname like ? and director like ? and programtype like ? " +
101 //                    "and actor like ? and screenwriter like ? and region like ? and language like ? ";
102             
103             //获取预处理statement
104             preparedStatement = connection.prepareStatement(sql.toString());
105             
106             //statement2 = connection.createStatement();
107             //设置参数，第一个参数为sql语句中参数的序号（从1开始），第二个参数为设置的参数值
108             if(assetname != null && assetname != ""){
109                 preparedStatement.setString(countMap.get(assetname), "%"+assetname+"%");
110                 //preparedStatement.setString(countMap.get(assetname), assetname);
111             }
112             if(director != null && director != ""){
113                 preparedStatement.setString(countMap.get(director), "%"+director+"%");
114                 //preparedStatement.setString(countMap.get(director),director);
115             }
116             if(programtype != null && programtype != ""){
117                 preparedStatement.setString(countMap.get(programtype), "%"+programtype+"%");
118                 //preparedStatement.setString(countMap.get(programtype), programtype);
119             }
120             if(actor != null && actor != ""){
121                 preparedStatement.setString(countMap.get(actor), "%"+actor+"%");
122                 //preparedStatement.setString(countMap.get(actor), actor);
123             }
124             if(screenwriter != null && screenwriter != ""){
125                 preparedStatement.setString(countMap.get(screenwriter), "%"+screenwriter+"%");
126                 //preparedStatement.setString(countMap.get(screenwriter), screenwriter);
127             }
128             if(region != null && region != ""){
129                 preparedStatement.setString(countMap.get(region), "%"+region+"%");
130                 //preparedStatement.setString(countMap.get(region), region);
131             }
132             if(language != null && language != ""){
133                 preparedStatement.setString(countMap.get(language), "%"+language+"%");
134                 //preparedStatement.setString(countMap.get(language), language);
135             }
136             
137             long searchend = System.currentTimeMillis();
138             
139             //向数据库发出sql执行查询，查询出结果集
140             resultSet =  preparedStatement.executeQuery();
141             //resultSet2 =  statement2.executeQuery(allRecordSql);
142             //遍历查询结果集
143             long loopstart = System.currentTimeMillis();
144             int i=0;
145             while(resultSet.next()){
146                 i++;
147                 resultSet.getString("director");
148                 resultSet.getString("actor");
149                 resultSet.getString("programtype");
150                 resultSet.getString("screenwriter");
151                 resultSet.getString("region");
152                 resultSet.getString("language");
153                 //System.out.println(resultSet.getString("director")+"  "+resultSet.getString("actor")+"  "+resultSet.getString("programtype")+"  "+resultSet.getString("screenwriter")
154                     //    +"  "+resultSet.getString("region")+"  "+resultSet.getString("language")+"  "+resultSet.getString("alias"));
155             }
156             long loopend = System.currentTimeMillis();
157             
158             String searchResult = "JDBC找到这些数据用时:"+ (searchend-start)+"毫秒;\nJDBC对结果循环遍历耗时时:"+(loopend-loopstart)
159                     +"毫秒;\nJDBC共耗时:"+(loopend-start)+"毫秒;\nJDBC共查找到"+i+"个记录";
160             return searchResult;
161         } catch (Exception e) {
162             e.printStackTrace();
163         }finally{
164             //释放资源
165             if(resultSet!=null){
166                 try {
167                     resultSet.close();
168                 } catch (SQLException e) {
169                     e.printStackTrace();
170                 }
171             }
172             if(preparedStatement!=null){
173                 try {
174                     preparedStatement.close();
175                 } catch (SQLException e) {
176                     e.printStackTrace();
177                 }
178             }
179             if(connection!=null){
180                 try {
181                     connection.close();
182                 } catch (SQLException e) {
183                     e.printStackTrace();
184                 }
185             }
186         }
187         return "-----";
188     }
189 }

1 List<String> keysList =  new ArrayList<String>();//用一个List来接收前台参来的查询参数
2 keysList.add(screenwriterKey);
3 ..........................
4 int keysListSize = keysList.size();//得到共有多查询参数
5 String [] convertKeysArr = (String[])keysList.toArray(new String[keysListSize]); //这个List变成指定大小的数组
6 Set<String> sinterIds = jedis.sinter(convertKeysArr);//sinter(...)求多个集合的交集,可以接收动态参数...动态参数本质上是数组.

 1 原本查询语句是select * from asset where assetname like ? and director like ? and programtype like ? and actor like ? screenwriter like ? and region like ? ........
 2 但是查询条件不固定....不一定有对应的对应的查询项
 3 
 4 Map<String,Integer> countMap = new HashMap<String,Integer>();//因为预处理参数需要记录值和对应值在sql语句中的位置
 5                                                             //这里使用Map的方式,值是key,sql中的第几个数字作为value
 6 //定义sql语句 ?表示占位符
 7 StringBuilder sql = new StringBuilder();
 8 sql.append("select * from asset where ");
 9 if(assetname != null && assetname != ""){
10     sql.append(" assetname like ?");
11     countMap.put(assetname,countMap.size()+1);
12 }
13 if(director != null && director != ""){
14     if(countMap.size() < 1){
15         sql.append(" director like ?");
16     }else{
17         sql.append(" and director like ?");
18     }
19     countMap.put(director,countMap.size()+1);
20 }
21 if(programtype != null && programtype != ""){
22     if(countMap.size() < 1){
23         sql.append(" programtype like ?");
24     }else{
25         sql.append(" and programtype like ?");
26     }
27     countMap.put(programtype,countMap.size()+1);
28 }
29 .......................................
30 //设置参数preparedStatement，第一个参数为sql语句中参数的序号（从1开始），第二个参数为设置的参数值
31 if(assetname != null && assetname != ""){
32     preparedStatement.setString(countMap.get(assetname), "%"+assetname+"%");
33 }
34 if(director != null && director != ""){
35     preparedStatement.setString(countMap.get(director), "%"+director+"%");
36     //preparedStatement.setString(countMap.get(director),director);
37 }