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;    }}