CREATE VIEW TRAVEL (ROWKEY VARCHAR PRIMARY KEY,INFO.SP VARCHAR,INFO.EP VARCHAR,INFO.ST VARCHAR,INFO.ET VARCHAR);
CREATE SEQUENCE SEQ;
CREATE TABLE TRAVEL (ROWKEY VARCHAR PRIMARY KEY,INFO.SP VARCHAR,INFO.EP VARCHAR,INFO.ST VARCHAR,INFO.ET VARCHAR,PAGEID BIGINT);
UPSERT INTO PAGETRAVEL SELECT ROWKEY,SP,EP,ST,ET,NEXT VALUE FOR SEQ FROM TRAVEL;
SELECT PAGEID FROM TRAVEL WHERE ROWKEY LIKE '澳门三亚%' AND ST >= '2016-01-02';
SELECT * FROM TRVELS WHERE PAGEID > PAGEID*(PAGENUM-1)*N ORDER BY PAGEID LIMIT N WHERE ROWKEY LIKE '澳门三亚%';
(PAGENUM为当前页数)
关键代码实现:
public List findPageRecords(int currentPageNum, int pageSize,String ST,String SP,String EP) {
//得到每页起始键的偏移量,第一个参数为SQL语句,第二个参数为返回值类型的Class对象,第三个参数为占位符值(为可变参数) int startkey = jdbcTemplate.queryForObject("SELECT * FROM TRAVELS WHERE ST >= ? AND ROWKEY LIKE ? LIMIT 1",Integer.class,ST,SP+EP+"%"); //第一个参数为SQL语句,第二参数的RowMapper将每一行结果映射成一个Java对象,方便将其他封装到JavaBean中,第三个参数为占位符值(为可变参数) List<Travel> travels = jdbcTemplate.query("SELECT * FROM TRAVELS where PAGEID >= ? AND ST >= ? AND ROWKEY LIKE ? limit 8", new RowMapper<Travel>() { public Travel mapRow(ResultSet rs, int rowNum) throws SQLException { Travel travel = new Travel(); travel.setROWKEY(rs.getString("ROWKEY")); travel.setURL(rs.getString("URL")); travel.setSP(rs.getString("SP")); travel.setEP(rs.getString("EP")); travel.setST(rs.getString("ST")); travel.setET(rs.getString("ET")); travel.setSIGHTS(rs.getString("SIGHTS")); travel.setALLDATE(rs.getString("ALLDATE")); travel.setHOTEL(rs.getString("HOTEL")); travel.setTOTALPRICE(rs.getString("TOTALPRICE")); travel.setTRAFFIC(rs.getString("TRAFFIC")); travel.setTRAVELTYPE(rs.getString("TRAVELTYPE")); travel.setSUPPLIER(rs.getString("SUPPLIER")); return travel; } },startkey+(currentPageNum - 1)*pageSize,ST,SP+EP+"%"); return travels; }
总结:通过与Spring JDBC的集成,利用Phoneix可以很轻易的实现HBase分页极大地提高开发效率,并且Phoenix还提供了二级索引的功能。
联系客服