import java.util.List;
import java.util.Map;
/**
* 数据库查询工具类.
*
*/
public class QueryTool {
/**
* Checks if is condition.
*
* @param obj the obj
* @return true, if is condition
*/
public static boolean isCondition(Object obj) {
if ("".equals(getString(obj)))
return false;
else
return true;
}
/**
* Gets the string from Object,null值转换为空字符串.
*
* @param obj the obj
* @return the string
*/
public static String getString(Object obj) {
return obj != null ? obj.toString() : "";
}
/**
* 拼接sql in条件.
*
* @param columnKey the column key
* @param columnValueList the column value list
* @param joinOp the join op
* @param sb the sb
*/
public static void appendConditionIn(String columnKey, List columnValueList,
String joinOp, StringBuffer sb) {
if (QueryTool.isCondition(columnValueList)) {
sb.append(" " + joinOp + " " + columnKey + " in " + QueryTool.listToSqlIn(columnValueList));
}
}
/**
* 拼接sql 条件.
*
* @param columnKey the column key
* @param columnValue the column value
* @param joinOp the join op
* @param op the op
* @param sb the sb
* @param paramsList the params list
*/
public static void appendCondition(String columnKey, Object columnValue,
String joinOp, String op, StringBuffer sb, List paramsList) {
if (QueryTool.isCondition(columnValue)) {
sb.append(" " + joinOp + " " + columnKey + " " + op + " ?");
paramsList.add(columnValue);
}
}
/**
* 生产分页sql.
*
* @param sql the sql
* @param pageIndex the page index
* @param pageSize the page size
* @param paramsList the params list
* @return the string
*/
public static String buildPageSql(String sql, int pageIndex, long pageSize,
List paramsList) {
long fromRowNum = pageSize * (pageIndex - 1) + 1;
long toRowNum = pageSize * pageIndex;
StringBuffer sbNewSql = new StringBuffer();
sbNewSql.append("select x.*");
sbNewSql.append(" from (select rownum x_rownum,o.* from (").append(sql)
.append(") o) x");
sbNewSql.append(" where x_rownum>= ?").append(" and x_rownum<= ?");
sbNewSql.append(" order by x.x_rownum");
paramsList.add(fromRowNum);
paramsList.add(toRowNum);
return sbNewSql.toString();
}
/**
* 生成统计sql.
*
* @param sql the sql
* @return the string
*/
public static String buildCountSql(String sql) {
return "select count(*) from (" + sql + ")";
}
/**
* 取得map中对应数据,为null或空时转换为缺省值.
*
* @param map the map
* @param name the name
* @param replace the replace
* @return the property
*/
public static String getProperty(Map map, String name,
String replace) {
return map.get(name) != null
&& map.get(name).toString().trim().length() > 0 ? map.get(name)
.toString() : replace;
}
/**
* List to sql in.
*
* @param stringList the string list
* @return the string
*/
public static String listToSqlIn(List stringList) {
StringBuilder sb = new StringBuilder();
if (stringList == null || stringList.size() == 0)
return "";
for (String string : stringList) {
if (sb.length() == 0)
sb.append(" ('").append(string).append("'");
else
sb.append(",'").append(string).append("'");
}
sb.append(") ");
return sb.toString();
}
/**
* 根据***取得性别.
*
* @param val the val
* @return the sex from id
*/
public static boolean getSexFromID(String val) {
boolean sex = false;
// 15位***号码
if (15 == val.length()) {
if (Integer.valueOf(val.charAt(14) / 2) * 2 != val.charAt(14))
sex = true;
else
sex = false;
}
// 18位***号码
if (18 == val.length()) {
if (Integer.valueOf(val.charAt(16) / 2) * 2 != val.charAt(16))
sex = true;
else
sex = false;
}
return sex;
}
/**
* ***号15转18位
*
* @author cjj 2010-5-13下午01:04:53
* @param id
* @return
*/
public static final String getNewEighteenId(String id) {
final int[] W = { 7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2,
1 };
final String[] A = { "1", "0", "X", "9", "8", "7", "6", "5", "4", "3",
"2" };
int i, j, s = 0;
String newid;
newid = id;
newid = newid.substring(0, 6) + "19" + newid.substring(6, id.length());
for (i = 0; i < newid.length(); i++) {
j = Integer.parseInt(newid.substring(i, i + 1)) * W[i];
s = s + j;
}
s = s % 11;
newid = newid + A[s];
return newid;
}
/**
* ***号18转15位
*
* @author cjj 2010-5-13下午02:06:51
* @param id
* @return
*/
public static String getNewFifteenId(String id) {
String newid = id.substring(0, 6) + id.substring(8, id.length() - 1);
return newid;
}
/**
* ***号15,18位互换.
*
* @param id the id
* @return the exchange p id
*/
public static String getExchangePId(String id) {
if (id.length()==15)
return getNewEighteenId(id);
else if (id.length()==18)
return getNewFifteenId(id);
else
return id;
}
}
使用:
public Page findLoginRecordList(String machineCode,String pageIndex,String pagesize){
int pageIndexInt=Integer.valueOf(pageIndex);
int pagesizeInt=Integer.valueOf(pagesize);
List paramsList = new ArrayList(); //定义一个拼接参数的集合
StringBuffer s = new StringBuffer();
s.append("select t.certNo, t.CLIENT,t.TYPE, to_char(t.inouttime,'mm/dd hh24:mi:ss') as INOUTTIME from DC_LOGINRECORD t where 1=1 ");
QueryTool.appendCondition("t.machinecode",machineCode,"AND","=" ,s, paramsList);//拼接条件
s.append(" order by t.inouttime desc");
String sql = QueryTool.buildCountSql(s.toString()); //查询总数的sql
long totalSize = this.queryForInt(sql,paramsList.toArray(new Object[paramsList.size()])); //jdbc查询
if(totalSize > 0){
String sl = QueryTool.buildPageSql(s.toString(), pageIndexInt,pagesizeInt , paramsList);//拼接分页
List> list = this.queryForList(sl,paramsList.toArray(new Object[paramsList.size()])); //jdbc查询
int start = pagesizeInt *(pageIndexInt -1)+1; //本页数据在数据库中的起始位置
return new Page(pagesizeInt *(start -1)+1, totalSize, pagesizeInt, list);
}else{
return new Page(0, 0, 10, new ArrayList());
}
}
备注:
分页的类:
import java.io.Serializable;import java.util.ArrayList;/** * 分页对象. 包含数据及分页信息. */public class Page implements Serializable { static private int DEFAULT_PAGE_SIZE = 20; /** * 每页的记录数 */ private int pageSize = DEFAULT_PAGE_SIZE; /** * 当前页第一条数据在List中的位置,从0开始 */ private int start; /** * 当前页中存放的记录,类型一般为List */ private Object data; /** * 总记录数 */ private long totalCount; /** * 构造方法,只构造空页 */ public Page() { this(0, 0, DEFAULT_PAGE_SIZE, new ArrayList()); } /** * 默认构造方法 * * @param start * 本页数据在数据库中的起始位置 * @param totalSize * 数据库中总记录条数 * @param pageSize * 本页容量 * @param data * 本页包含的数据 */ public Page(int start, long totalSize, int pageSize, Object data) { this.pageSize = pageSize; this.start = start; this.totalCount = totalSize; this.data = data; } /** * 取数据库中包含的总记录数 */ public long getTotalCount() { return this.totalCount; } /** * 取总页数 */ public long getTotalPageCount() { if (totalCount % pageSize == 0) return totalCount / pageSize; else return totalCount / pageSize + 1; } /** * 取每页数据容量 */ public int getPageSize() { return pageSize; } /** * 当前页中的记录 */ public Object getResult() { return data; } /** * 取当前页码,页码从1开始 */ public int getCurrentPageNo() { return start / pageSize + 1; } /** * 是否有下一页 */ public boolean hasNextPage() { return this.getCurrentPageNo() < this.getTotalPageCount() - 1; } /** * 是否有上一页 */ public boolean hasPreviousPage() { return this.getCurrentPageNo() > 1; } /** * 获取任一页第一条数据的位置,每页条数使用默认值 */ protected static int getStartOfPage(int pageNo) { return getStartOfPage(pageNo, DEFAULT_PAGE_SIZE); } /** * 获取任一页第一条数据的位置,startIndex从0开始 */ public static int getStartOfPage(int pageNo, int pageSize) { return (pageNo - 1) * pageSize; }}