JDBC原生分页查询实现

工具类(PageFinderDTO)

package com.uxun.uxunplat.page;

import java.io.Serializable;
import java.util.List;

/**
 * 分页对象. 包含当前页数据及分页信息
 *
 * @author 
 *
 */
public class PageFinderDTO<T> implements Serializable {

    private static final long serialVersionUID = -3193453346742977879L;
    /**
	 * 当前页数
	 */
	private int pageNum;
	/**
	 * 每页条数
	 */
	private int pageSize;
    /**
     * 分页查询数据
     */
    private List<T> data;
    /**
     * 总条数
     */
    private int totalPageNum;
    /**
     * 总条数
     */
    private int totalCount;

    /**
     * 是否有上一页
     */
    private boolean hasPrevious = false;

    /**
     * 是否有下一页
     */
    private boolean hasNext = false;


    /**
     * 构造方法
     */
    public PageFinderDTO() {}

    /**
     * 构造方法
     * @param pageNum 当前页数
     * @param totalCount 总条数
     */
    public PageFinderDTO(int pageNum, int totalCount) {
		this.pageNum = pageNum;
		this.totalCount = totalCount;
		this.totalPageNum = getTotalPageCount();
        refresh();
	}


    /**
     * 构造方法
     * @param pageNum 当前页数
     * @param pageSize 每页条数
     * @param totalCount 总条数
     */
	public PageFinderDTO(int pageNum, int pageSize, int totalCount) {
		this.pageNum = pageNum;
		this.pageSize = pageSize;
		this.totalCount = totalCount;
		this.totalPageNum = getTotalPageCount();
        refresh();
	}

    /**
     * 构造方法
     * @param pageNum 当前页数
     * @param pageSize 每页条数
     * @param totalCount 总条数
     * @param data 分页查询数据
     */
	public PageFinderDTO(int pageNum, int pageSize, int totalCount, List<T> data) {
		this.pageNum = pageNum;
		this.pageSize = pageSize;
		this.totalCount = totalCount;
		this.totalPageNum = getTotalPageCount();
		this.data = data;
        refresh();
	}

	/**
	 * 获取总页数
	 */
	private final int getTotalPageCount() {
		if (totalCount % pageSize == 0) {
            return totalCount / pageSize;
        } else {
            return totalCount / pageSize + 1;
        }
	}

    /**
     * 刷新当前分页对象数据
     */
    private void refresh() {
        if (totalPageNum <= 1) {
            hasPrevious = false;
            hasNext = false;
        } else if (pageNum == 1) {
            hasPrevious = false;
            hasNext = true;
        } else if (pageNum == totalPageNum) {
            hasPrevious = true;
            hasNext = false;
        } else {
            hasPrevious = true;
            hasNext = true;
        }
    }

    public int getPageNum() {
        return pageNum;
    }

    public void setPageNum(int pageNum) {
        this.pageNum = pageNum;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }

    public List<T> getData() {
        return data;
    }

    public void setData(List<T> data) {
        this.data = data;
    }

    public int getTotalPageNum() {
        return totalPageNum;
    }

    public void setTotalPageNum(int totalPageNum) {
        this.totalPageNum = totalPageNum;
    }

    public int getTotalCount() {
        return totalCount;
    }

    public void setTotalCount(int totalCount) {
        this.totalCount = totalCount;
    }

    public boolean isHasPrevious() {
        return hasPrevious;
    }

    public void setHasPrevious(boolean hasPrevious) {
        this.hasPrevious = hasPrevious;
    }

    public boolean isHasNext() {
        return hasNext;
    }

    public void setHasNext(boolean hasNext) {
        this.hasNext = hasNext;
    }
}

工具类(QueryPageDTO)

package com.uxun.uxunplat.page;

/**
 * 分页对象
 * @author 
 */
public class QueryPageDTO {

    public static int DEFAULT_PAGE_NUM = 1;
    public static int DEFAULT_PAGE_SIZE = 10;
    public static int MAX_PAGE_SIZE = 100000;

    /**
     * 当前页数
     */
    private int pageNum;
    /**
     * 每页条数
     */
    private int pageSize;
    /**
     * 构造方法
     */
    public QueryPageDTO() {
        this.pageNum = DEFAULT_PAGE_NUM;
        this.pageSize = DEFAULT_PAGE_SIZE;
    }

    /**
     *  构造方法
     * @param pageNum 当前页数
     * @param pageSize 每页条数
     */
    public QueryPageDTO(int pageNum, int pageSize) {
        this.pageNum = pageNum;
        this.pageSize = pageSize;
    }

    public int getPageNum() {
        return pageNum;
    }

    public void setPageNum(int pageNum) {
        this.pageNum = pageNum;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }


}

工具类(JdbcAction)

package com.uxun.uxunplat.page;

import com.alibaba.fastjson.JSON;
import com.uxun.uxunplat.service.SpringContextUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.sql.DataSource;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.Reader;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 数据库操作工具,实现基本的数据库操作,包括删除,更新以及查询
 *
 *@author 
 *
 */
public class JdbcAction {

    private static Logger logger = LoggerFactory.getLogger(JdbcAction.class);

    private static String CONNECTION_ERROR = "数据库连接失败[database connection refused]";
    private static String TRANSFORM_ERROR = "数据对象转换失败[data transform error]";

    /**
     * 分页查询
     *
     *    注意事项:数据库查询脚本的字段必须和类对象clazz的属性值一致,才能被正确赋值
     *
     * @param searchSql 查询脚本,查询参数使用?占位符,防止sql脚本注入
     * @param clazz 查询对象转换成实体类
     * @param params  可变查询参数
     * @return
     */
    public List search(String searchSql, Class clazz, Object... params) throws Exception {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            con = getConnection();
            if(con==null){
                throw new Exception(JdbcAction.CONNECTION_ERROR);
            }
            pstmt = con.prepareStatement(searchSql);
            for (int i = 0; i < params.length; i++) {
                // 参数从下标1开始
                pstmt.setObject(i + 1, params[i]);
            }
            rs = pstmt.executeQuery();
            /* 取记录集的column名集合 */
            ResultSetMetaData rsmd = rs.getMetaData();
            logger.info("打印查询脚本:executeSql=" +printExecuteSql(searchSql, params));
            List dataList = formatDataList(rsmd, rs, clazz);
            //logger.info("打印查询数据:dataList="+ JSON.toJSONString(dataList));
            return dataList;
        }catch (SQLException se) {
            logger.error("查询异常", se);
            new Exception(JdbcAction.CONNECTION_ERROR);
        } catch (Exception e) {
            logger.error("查询异常", e);
            new Exception(JdbcAction.CONNECTION_ERROR);
        }finally {
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (con != null) {
                con.close();
            }
        }
        return null;
    }

    /**
     * 分页查询
     *
     *    注意事项:数据库查询脚本的字段必须和类对象clazz的属性值一致,才能被正确赋值
     *
     * @param searchSql 查询脚本,查询参数使用?占位符,防止sql脚本注入
     * @param clazz 查询对象转换成实体类
     * @param queryPageDTO 查询分页实体
     * @param params 可变查询参数
     * @return
     * @throws Exception
     */
    public PageFinderDTO getPage(String searchSql, Class clazz, QueryPageDTO queryPageDTO, Object... params) throws Exception {
        return getPage(searchSql, clazz, queryPageDTO.getPageNum(), queryPageDTO.getPageSize(), params);
    }


    /**
     * 分页查询
     *
     *    注意事项:数据库查询脚本的字段必须和类对象clazz的属性值一致,才能被正确赋值
     *
     * @param searchSql 查询脚本,查询参数使用?占位符,防止sql脚本注入
     * @param clazz 查询对象转换成实体类
     * @param pageNum 当前页数
     * @param pageSize 每页条数
     * @param params  可变查询参数
     * @return
     */
    public PageFinderDTO getPage(String searchSql, Class clazz, int pageNum, int pageSize, Object... params) throws Exception {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        //logger.info("打印分页查询脚本:开始时间: startTime = "+ DateUtils.curTime());
        try {
            con = getConnection();
            if(con==null){
                throw new Exception(JdbcAction.CONNECTION_ERROR);
            }
            // 查询总数
            Long totalCount = getTotalCount(con, searchSql, params);
            /**
             * 分页查询,设置参数
             *
             * @params sql 执行脚本
             * @params resultSetType 设置游标移动属性
             *          -ResultSet.TYPE_FORWARD_ONLY: 默认属性,只能向前滚动
             *          -ResultSet.TYPE_SCROLL_INSENSITIVE:前后可以任意滚动,对于修改不敏感
             *          -ResultSet.TYPE_SCROLL_SENSITIVE: 前后可以任意滚动,对于修改不敏感
             * @params resultSetConcurrency 设置对象的修改属性
             *          -ResultSet.CONCUR_READ_ONLY:只读取类型的参数
             *          -ResultSet.CONCUR_UPDATABLE:可修改类型的参数
             */
            pstmt = con.prepareStatement(searchSql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            //pstmt = con.prepareStatement(searchSql);
            for (int i = 0; i < params.length; i++) {
                // 参数从下标1开始
                pstmt.setObject(i + 1, params[i]);
            }
            //logger.info("打印分页查询脚本:executeSql=" +printExecuteSql(searchSql, params));
            // MaxRows 返回数据条数(要查询的总数)
            pstmt.setMaxRows(pageNum*pageSize);
            rs = pstmt.executeQuery();
            // 查询分页数据
            int beginIndex = (pageNum - 1) * pageSize;
            if(beginIndex > 0){
                // 设置读取数据的起始位置
                rs.absolute(beginIndex);
            }
            /* 取记录集的column名集合 */
            ResultSetMetaData rsmd = rs.getMetaData();
            List dataList = formatDataList(rsmd, rs, clazz);
            logger.info("打印分页查询脚本:executeSql=" +printExecuteSql(searchSql, params));
            //logger.info("打印分页查询数据:dataList="+ JSON.toJSONString(dataList));
            PageFinderDTO pageFinder = new PageFinderDTO(pageNum, pageSize, new BigDecimal(totalCount).intValue(), dataList);
            //logger.info("打印分页查询脚本:结束时间: endTime = "+ DateUtils.curTime());
            return pageFinder;
        }catch (SQLException se) {
            logger.error("分页查询异常", se);
            new Exception(JdbcAction.CONNECTION_ERROR);
        } catch (Exception e) {
            logger.error("分页查询异常", e);
            new Exception(JdbcAction.CONNECTION_ERROR);
        }finally {
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (con != null) {
                con.close();
            }
        }
        return null;
    }

    /**
     * 将查询的数据转换成实体类
     *
     * @param rsmd 查询column名集合
     * @param rs 查询结果集
     * @param clazz 转换实体类
     * @return
     * @throws Exception
     */
    private List formatDataList(ResultSetMetaData rsmd, ResultSet rs, Class clazz) throws Exception {
        List dataList = new ArrayList();
        try {
            // 查询结果列数
            int columnCount = rsmd.getColumnCount();
            if(columnCount > 0){
                // 获取类声明的属性
                Field[] fields = clazz.getDeclaredFields();
                while (rs.next()){
                    // 构造类实例对象
                    Object clazzInstance = clazz.newInstance();
                    // 寻找列名对应的对象属性并赋值
                    for(int col=1; col<= columnCount; col++){
                        Object columnVal = rs.getObject(col);
                        for(int i=0; i< fields.length; i++){
                            // 获取类申明的属性值
                            Field field = fields[i];
                            // 如果匹配进行赋值
                            if(field.getName().equalsIgnoreCase(rsmd.getColumnName(col))){
                                boolean accessible = field.isAccessible();
                                field.setAccessible(true);
                                // 从数据库中获取到的是BigBigDecimal类型的数据,需要转换成包装类的对象
                                if(columnVal instanceof BigDecimal){
                                    Constructor<?> dc = field.getType().getDeclaredConstructor(String.class);
                                    columnVal = dc.newInstance(columnVal.toString());
                                    field.set(clazzInstance, columnVal);
                                }else if(columnVal instanceof Clob){
                                    field.set(clazzInstance, clobToString((Clob)columnVal));
                                } else{
                                    field.set(clazzInstance, columnVal);
                                }
                                //f.set(clazzInstance, columnVal);
                                field.setAccessible(accessible);
                            }
                        }
                    }
                    dataList.add(clazzInstance);
                }
            }
        } catch (SQLException se){
            logger.error("查询数据转换异常", se);
            new Exception(JdbcAction.TRANSFORM_ERROR);
        }catch (Exception e){
            logger.error("查询数据转换异常", e);
            new Exception(JdbcAction.TRANSFORM_ERROR);
        }
        return dataList;
    }


    /**
     * 查询总数
     *
     * @param searchSql 查询脚本,查询参数使用?占位符,防止sql脚本注入
     * @param params 可变查询参数
     * @return
     */
    public Long getTotalCount(String searchSql, Object... params) throws Exception {
        long totalCount = 0;
        Connection con = null;
        try {
            StringBuffer countSql = new StringBuffer("select count(1) totalCount from ( ").append(searchSql).append(" )");
            con = getConnection();
            if(con==null){
                throw new Exception(JdbcAction.CONNECTION_ERROR);
            }
            totalCount = getTotalCount(con, searchSql, params);

        }catch (SQLException se) {
            logger.error("数据库连接异常", se);
            new Exception(JdbcAction.CONNECTION_ERROR);
        } catch (Exception e) {
            logger.error("数据库连接异常", e);
            new Exception(JdbcAction.CONNECTION_ERROR);
        }finally {
            if (con != null) {
                con.close();
            }
        }
        return totalCount;
    }

    /**
     * 查询总数
     *
     * @param searchSql 查询脚本,查询参数使用?占位符,防止sql脚本注入
     * @param params 可变查询参数
     * @return
     */
    public Long getTotalCount(Connection con, String searchSql, Object... params) throws Exception {
        long totalCount = 0;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try{
            StringBuffer countSql = new StringBuffer("select count(1) totalCount from ( ").append(searchSql).append(" )");
            pstmt = con.prepareStatement(countSql.toString());
            for (int i = 0; i < params.length; i++) {
                // 参数从下标1开始
                pstmt.setObject(i + 1, params[i]);
            }
            rs = pstmt.executeQuery();
            boolean next = rs.next();
            totalCount = rs.getLong(1);
            String executeSql = printExecuteSql(countSql.toString(), params);
            logger.info("打印查询总数:totalCount="+totalCount+", \n 执行脚本:executeSql="+executeSql+" \n");
        }catch (SQLException se) {
            logger.error("数据库连接异常", se);
            new Exception(JdbcAction.CONNECTION_ERROR);
        } catch (Exception e) {
            logger.error("数据库连接异常", e);
            new Exception(JdbcAction.CONNECTION_ERROR);
        }finally {
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
        }
        return totalCount;
    }

    /**
     * 将clob数据库字段转换成字符串
     * @param clob
     * @return
     * @throws SQLException
     * @throws IOException
     */
    private String clobToString(Clob clob) throws  SQLException, IOException {
        StringBuilder buff = new StringBuilder();
        if(clob!=null){
            Reader reader = clob.getCharacterStream();
            BufferedReader buffReader = new BufferedReader(reader);
            String readLine = buffReader.readLine();
            while (readLine!=null && readLine.length()>0){
                buff.append(readLine);
                readLine = buffReader.readLine();
            }
        }
        return buff.toString();
    }


    /**
     * 建立数据库连接
     *
     * @return
     */
    private Connection getConnection () {
        try {
            //获取SSH框架定义的数据库连接池连接
            DataSource ds = (DataSource) SpringContextUtil.getBean( "dataSource") ;
            return ds.getConnection();
        } catch (SQLException se){
            logger.error("获取数据库连接失败,数据库连接异常信息, se={}", se);
        }catch (Exception e){
            logger.error("获取数据库连接失败,异常信息, se={}", e);
        }
        return null ;
    }

    /*private Connection getConnection () {
        try {
            //获取SSH框架定义的数据库连接池连接
            //DataSource ds = (DataSource)SpringContextUtil.getBean( "dataSource") ;
            String url = "jdbc:oracle:thin:@10.184.96.45:1521/orcl";
            String username = "emall";
            String password = "emall";
            String driverClass = "oracle.jdbc.driver.OracleDriver";
            Class.forName(driverClass);
            Connection con = DriverManager.getConnection(url, username, password);
            return con;
        } catch (SQLException se){
            logger.error("获取数据库连接失败,数据库连接异常信息, se={}", se);
        }catch (Exception e){
            logger.error("获取数据库连接失败,异常信息, se={}", e);
        }
        return null ;
    }*/

    /**
     * 打印数据库查询脚本
     *
     * @param sql 数据库脚本,参数使用?占位符,防止sql脚本注入
     * @param params 可变查询参数
     */
    private String printExecuteSql(String sql, Object... params){
        String executeSql = sql;
        for (Object obj : params) {
            executeSql = executeSql.replaceFirst("[?]", obj.toString());
        }
        return executeSql;
    }


}

方法调用(WebserviceDao)

package com.uxun.uxunplat.dao.webservice;

import com.uxun.bcms.data.voUxunECodeAuth;
import com.uxun.net.message.AccVoucher;
import com.uxun.net.message.*;
import com.uxun.net.message.shortmsg.ShortMsgInfo;
import com.uxun.net.utils.PageToObj;
import com.uxun.uxunplat.entity.*;
import com.uxun.uxunplat.page.JdbcAction;
import com.uxun.uxunplat.page.PageFinderDTO;
import com.uxun.uxunplat.service.SpringContextUtil;
import com.uxun.uxunplat.service.pointsbusiness.ICustomersService;
import com.uxun.uxunplat.util.*;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.*;

@Repository
public class WebserviceDao {

    Logger log = Logger.getLogger(WebserviceDao.class);

    /**
     * 查询积分账户信息:
     *
     * @param customersId 客户号
     * @param pointsTypeNo 积分类型
     *
     * @throws Exception
     */
    public List<PointsDataInfo> getPointsDataList(String customersId, String pointsTypeNo) throws Exception {
        List<Object> paramsList = new ArrayList<Object>();
        StringBuffer buff = new StringBuffer(" select ");
        buff.append("   a.pointsTypeNo pointType, b.PointsTypeName pointTypeName, a.pointsBalance pointCount, ");
        buff.append("   a.freezingPoints freezPoint, (a.pointsBalance-a.freezingPoints) availablePoint, b.description ");
        buff.append(" from PointsAccountInfo a ");
        buff.append(" left join PointsType b on a.pointsTypeNo = b.pointsTypeNo ");
        buff.append(" where 1=1 ");
        if(StringUtils.isNotEmpty(customersId)){
            buff.append(" and a.customersId = ? ");
            paramsList.add(customersId);
        }
        if(StringUtils.isNotEmpty(pointsTypeNo)){
            buff.append(" and a.pointsTypeNo = ? ");
            paramsList.add(pointsTypeNo);
        }
        buff.append(" and a.pointsAccountStatus = '1' order by a.pointsTypeNo ");
        log.info("执行getPointsDataList()方法,查询客户积分账户相关信息: sql = " + buff.toString());
        JdbcAction jdbcAction = new JdbcAction();
        List<PointsDataInfo> resultList = jdbcAction.search(buff.toString(), PointsDataInfo.class, paramsList.toArray(new Object[]{}));
        return resultList;
    }


    /**
     * 查询用户积分交易记录
     *
     * @param customersId 客户号
     * @param pointsTypeNo 积分类型
     * @param startDate 开始时间:格式yyyyMMdd
     * @param endDate  结束时间:格式yyyyMMdd
     * @param pageNum 页数
     * @param pageSize 每页条数
     * @return
     *
     * @throws Exception
     */
    public PageFinderDTO<PointsTransData> getPointsTransPageFider(String customersId, String pointsTypeNo, String startDate, String endDate, int pageNum, int pageSize) throws Exception {

        List<Object> paramsList = new ArrayList<Object>();
        StringBuffer sqlBuff = new StringBuffer("select t.pointsTypeNo pointType,p.pointsTypeName pointTypeName,t.pointsAmount,t.pointsBalance,");
        sqlBuff.append("   t.debitorCredit,t.transDate,t.transTime,t.description, ");
        sqlBuff.append("   t.transTypeNo,m.description transTypeName ");
        sqlBuff.append(" from pointstrans t ");
        sqlBuff.append(" left join pointstype p on p.pointstypeno=t.pointstypeno ");
        sqlBuff.append(" left join transtype m on m.transtypeno=t.transtypeno ");
        sqlBuff.append(" where 1=1 ");
        if(StringUtils.isNotEmpty(customersId)){
            sqlBuff.append(" and t.customersid = ? ");
            paramsList.add(customersId);
        }
        if(StringUtils.isNotEmpty(pointsTypeNo)){
            sqlBuff.append(" and t.pointstypeno = ? ");
            paramsList.add(pointsTypeNo);
        }
        if(StringUtils.isNotEmpty(startDate)){
            sqlBuff.append(" and t.transdate >= ? ");
            paramsList.add(startDate);
        }
        if(StringUtils.isNotEmpty(endDate)){
            sqlBuff.append(" and t.transdate <= ? ");
            paramsList.add(endDate);
        }
        sqlBuff.append(" order by t.transno desc ");
        JdbcAction jdbcAction = new JdbcAction();
        PageFinderDTO pageFinder = jdbcAction.getPage(sqlBuff.toString(), PointsTransData.class, pageNum, pageSize, paramsList.toArray(new Object[]{}));
        return pageFinder;
    }


}

上一篇:[CF936D] World of Tank


下一篇:oss对象存储-minio初体验