java查询数据类_java 数据库查询工具类.

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;

}

}

原文：http://xuliangjun.blog.51cto.com/7398089/1735285