the speed of querying directly on the client is very fast, but the speed in the code is much slower, and the amount of data is about 1 million
.this is the query code:
List<String> result = new ArrayList<>();
String sql = "SELECT * FROM (SELECT KEYWORDS FROM KEYWORD_PERSONAL_HEAT WHERE USER_ID = ""+userId+"" AND KEYWORDS LIKE "%"+keyword+"%" GROUP BY KEYWORDS ORDER BY max(TIMES) DESC) WHERE ROWNUM <="+rowNum;
List<Map> searchResult = JdbcUtils.findBySql(this.getEntityManager(), sql);
for(Map map:searchResult){
result.add((String) map.get("KEYWORDS"));
}
this is the underlying query code:
public static List<Map> findBySql(EntityManager entityManager,String sql){
List<Map> result=new ArrayList<Map>();
Connection conn=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
try{
conn=openConn(entityManager);
pstmt=openPstmt(conn,sql);
ResultSetMetaData rsmd=pstmt.getMetaData();
rs=openRs(pstmt);
while(rs.next()){
Map data=new HashMap();
for(int i=1;i<=rsmd.getColumnCount();iPP){
String columnName=rsmd.getColumnName(i);
data.put(columnName,rs.getObject(columnName));
}
result.add(data);
}
}catch (SQLException e){
e.printStackTrace();
}finally {
closeConn(rs,pstmt,conn);
}
return result;
}