六、Java持久化基础-基于JDBC轻量级分页工具集_JAVA_编程开发_程序员俱乐部

Java持久化基础—基于JDBC轻量级分页工具集

?

一、工具集包含了以下内容：

1、封装了C3P0连接池，提取数据源获取，不用修改Java源代码，只需配置c3p0-config.xml和dataSource.properties两个文件轻松实现各种常用数据库连接；

2、优化了查询结果集，直接返回一个包含查询对象的集合，方便使用；

3、封装SQL生成器，即使不会SQL语句也能轻松实现基本查询包括分页查询；

4、主要针对分页进行了封装优化，以对象的方式实现分页；

class="MsoNormal" style="margin: 0cm 0cm 0pt;">com.ran.db包下：

JDBCUtil.java //JDBC数据库连接、增删改查操作类 DBManager.java //C3P0连接池类，管理连接 PageDBManager.java //分页管理类，对数据求和和分页查询 PageBean.java?? //分页信息对象类

com.ran.generic包下：

ResultSetToObject.java //通过反射封装结果集对象的工具类 SimpleSQLGenerator.java //SQL生成器工具类

com.ran.po包下：

AddressBookPO.java?? //与数据表ADDRESS对应的实体类

com.ran.dao包下：

? MyDAO.java //分页测试类 ?配置文件：? ?c3p0-config.xml 、dataSource.properties ?配置数据源和连接池等信息

?

?

二、简单实现分页

实现分页很简单其实就只需关注三个单词：what、how、use

1、what：我需要一个什么样结果的分页，使用SimpleSQLGenerator.java类定义我的查询规则，包括查询目标、查询条件和排序方式等；

2、how：定义了一个查询规则，我就得去执行，使用PageDBManager.java类来执行我定义的规则最终得到我想要的结果；

3、use：通过执行规定好的查询得到一个分页对象PageBean，可以直接使用PageBean获取分页的所有信息，包括总页数、当前页、总记录，还有最重要的结果集LIST；

实现分页只需调用上面三个类即可，其它类已封装好不用调用，欢迎大家对上面类进行修改优化。

?

三、代码实例

(因代码较多，建议大家将代码拷至项目中查看,后面我会上传整个项目“JavaJDBC” )

JDBCUtil.java

package com.ran.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.ran.generic.ResultSetToObject;


public class JDBCUtil {
	
 	public Connection conn = null;
	public  PreparedStatement ps = null;
	public  ResultSet rs = null;
	private List entityList = null;
	
	public JDBCUtil(){}
	
	/**
	 * 获取一个连接对象
	 */
	public Connection getConnection(){
		try {
			 conn = DBManager.getInstance().getConnection();  //C3P0连接池控制Connection
		} catch (Exception e) {
			System.out.println("数据库连接失败！");
			e.printStackTrace();
		}
		return conn;
	}
	
	/**
	 * 优化后的query,只需传递sql命令和需要返回的对象类型就可获得一个对象集合，
	 * 在展示层只需变量集合直接操作对象获取属性值，完全面向对象的方式
	 * @param sql —— 查询SQL命令
	 * @param parms —— 查询条件
	 * @param classPo —— 需要返回结果集合的类型
	 * @return —— 返回一个LIST容器装PO对象，前台可直接遍历操作对象
	 */
	public List query( String sql , List parms,Class classPo ){
		conn = this.getConnection();
		entityList = new ArrayList();
		//Map结构：key:表字段名   value：表字段值
		//LIST结果：获取每一行数据，
		//如：[{PHONE=13441231244, ADDRESS=成都市, SEX=男, YNAME=张三}, {PHONE=13551234123, ADDRESS=成都市, SEX=男, YNAME=李大}]
		List<Map<String,Object>> resultList = new  ArrayList<Map<String,Object>>();
		try {
			ps = conn.prepareStatement(sql);    //预编译SQL
			if( 0!=parms.size() ){
				for( int i = 0; i<parms.size(); i++ ){
					ps.setObject(i+1, parms.get(i));   //循环设置参数
				}
			}
			rs = ps.executeQuery();   //执行查询操作
			//下面开始封装每一行数据放入MAP，并将每行数据放入LIST
			if( rs != null ){
				ResultSetMetaData rsm = rs.getMetaData();   //用于获取结果集中列的类型和属性信息对象   
				while( rs.next() ){
					Map<String,Object> map = new HashMap<String,Object>();
					for( int i = 1; i<=rsm.getColumnCount();i++ ){	
						map.put(rsm.getColumnName(i), rs.getObject(i));  //字段名称——字段值
					}
					resultList.add(map);   //将一行的数据放入LIST
				}
			}
			//利用反射来封住数据返回一个指定对象类型的数据集
			//LIST结构：[AddressBookPO@9446e4, AddressBookPO@ba5c7a, AddressBookPO@10d593e]
			  entityList = ResultSetToObject.ToObjectList(classPo, resultList);  
		} catch (Exception e) {
			e.printStackTrace();
		}
		try {
			this.close();   //关闭所有对象
		} catch (Exception e) {
			e.printStackTrace();
		}
		return entityList;
	}
	
	/**
	 * 查询记录总数
	 * @param sql   
	 * @param parms
	 * @return
	 */
	public int countQuery( String sql,List parms){
		conn = this.getConnection();
		int count = 0;
		try {
			ps = conn.prepareStatement(sql);  //预编译SQL
			if( null!= parms && 0!= parms.size() ){
				for( int i = 0; i<parms.size(); i++ ){
					ps.setObject(i+1, parms.get(i));   //循环设置参数
				}
			}
			rs = ps.executeQuery();   //执行查询操作
			if( rs.next() ){
				count = rs.getInt(1);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return count;
	}
	
	/**
	 * 增删改操作
	 * @param sql  SQL查询语句
	 * @param pares  判断条件
	 * @return
	 */
	public int edit( String sql, List pares ){
		conn = this.getConnection();
		int hasEffect = 0;
		try {
			ps = conn.prepareStatement(sql);  //预编译SQL
			if( 0 != pares.size() ){
				for( int i = 0; i<pares.size(); i++ ){
					System.out.println(pares.get(i).getClass().getName());
					ps.setObject(i+1, pares.get(i));   //循环设置参数
				}
			}
			hasEffect = ps.executeUpdate();  //执行增删改返回影响行数
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			this.close();   //关闭所有对象
		} catch (Exception e) {
			e.printStackTrace();
		}
		return hasEffect;
	}
	
	/**
	 * 关闭所有对象
	 * @throws Exception
	 */
	public void close() throws Exception{
		if( rs != null ){
			rs.close();
		}
		if( ps != null ){
			ps.close();
		}
		if( conn != null ){
			conn.close();
		}
	}
}

?DBManager.java

package com.ran.db;
import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

import com.mchange.v2.c3p0.ComboPooledDataSource;

/**
 * C3P0连接池类
 * @author Administrator
 *
 */
public class DBManager {
	
	private static final Properties pro = new Properties();     //属性文件对象
	private static final String FILEDIR = "src/dataSource.properties";   //指定属性文件地址
	public static String DBSOURCE;  //数据源
	
	private static final DBManager dbm = new DBManager();
	static ComboPooledDataSource cpds = null;
	private Connection conn = null;
	
	private DBManager(){}
	
	public static DBManager getInstance(){
		return dbm;
	}
	
	//通过静态代码块读取数据源，保证只执行一次
	static{
		try {
			FileInputStream connFileStream =new FileInputStream(new File(FILEDIR));
			pro.load(connFileStream);   //读取属性文件
		} catch (Exception e) {
			e.printStackTrace();
		}
		DBSOURCE = pro.getProperty("DATASOURCE");
		cpds = new ComboPooledDataSource(DBSOURCE);
	}
	
	/**
	 * 获取连接
	 * @return
	 */
	public synchronized Connection getConnection(){
		try {
			conn = cpds.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
}

?PageDBManager.java

package com.ran.db;

import java.util.ArrayList;
import java.util.List;
import com.ran.generic.SimpleSQLGenerator;

/**
 * 分页管理类
 * @author Administrator
 */
public class PageDBManager {
	
	private static final JDBCUtil jdbc = new JDBCUtil();    //数据库JDBC操作类
	private PageBean pageBean = null;
	private List fieldsValue = null;
	
	public PageBean getPageBean() {
		return pageBean;
	}

	public void setPageBean(PageBean pageBean) {
		this.pageBean = pageBean;
	}

	public PageDBManager(){}
	
	public PageDBManager( int pageRecord,int currentPage,SimpleSQLGenerator sqlGen ){
		pageBean = new PageBean(pageRecord);  //构建一个PageBean对象并初始化每页记录数
		pageBean.setCurrentPage(currentPage); //设置当前页
		this.searchAllRecord(sqlGen);  //查询总记录数
		pageBean.initTotalPage();     //初始化总页数
	}
	
	/**
	 * 初始化总页数
	 * @param sqlGen   SQL构造器
	 */
	public void searchAllRecord( SimpleSQLGenerator sqlGen ){
		if( null != sqlGen.getFields() ){
			fieldsValue = new ArrayList( sqlGen.getFields().values() );
		}
		pageBean.setAllRecord(jdbc.countQuery(sqlGen.countSQL(), fieldsValue));
	}
	
	/**
	 * 条件分页查询
	 * @param sqlGen    SQL构造器
	 * @param classPo   结果集类型
	 * @return
	 */
	public List queryRecordList( SimpleSQLGenerator sqlGen,Class classPo ){
		List recordList = new ArrayList();
		if( null != sqlGen.getFields() ){
			fieldsValue = new ArrayList( sqlGen.getFields().values() );
		}
		//判断数据源
		if( "mysql".equals(DBManager.DBSOURCE) ){
			recordList = jdbc.query(sqlGen.pageSQLForMySQL(pageBean.getPageRecord(), pageBean.getCurrentPage()), fieldsValue, classPo);
		}else if( "oracle".equals(DBManager.DBSOURCE) ){
			recordList = jdbc.query(sqlGen.pageSQLForOracle(pageBean.getPageRecord(), pageBean.getCurrentPage()), fieldsValue, classPo);
		}
		return recordList;
	}
	
	

}

?PageBean.java

package com.ran.db;

import java.util.List;

public class PageBean {

	private int currentPage;  //当前页
	
	private int totalPage;   //总页数
	
	private int pageRecord;  //每页记录数
	
	private int allRecord;  //总记录数
	
	private List resultList = null;  //结果集
	
	public PageBean(){} 
	
	public PageBean( int pageRecord ){
		this.pageRecord = pageRecord;
		if( this.currentPage == 0 ){
			this.currentPage = 1;
		}
	}
	
	public List getResultList() {
		return resultList;
	}

	public void setResultList(List resultList) {
		this.resultList = resultList;
	}

	public int getCurrentPage() {
		return currentPage;
	}

	public void setCurrentPage(int currentPage) {
		this.currentPage = currentPage;
	}

	public int getTotalPage() {
		return totalPage;
	}

	public void setTotalPage(int totalPage) {
		this.totalPage = totalPage;
	}

	public int getPageRecord() {
		return pageRecord;
	}

	public void setPageRecord(int pageRecord) {
		this.pageRecord = pageRecord;
	} 
	
	public int getAllRecord() {
		return allRecord;
	}

	public void setAllRecord(int allRecord) {
		this.allRecord = allRecord;
	}

	/**
	 * 初始化总页数
	 * @return  返回总页数
	 */
	public void initTotalPage(){
		if( this.allRecord % this.pageRecord == 0 ){
			this.totalPage = allRecord / pageRecord;
		}else{
			this.totalPage = allRecord / pageRecord + 1;
		}
	}

}

?ResultSetToObject.java

package com.ran.generic;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 封装实体类-将制定对象类型数据通过反射赋值初始化对象
 * @author Administrator
 */
public class ResultSetToObject {
	
	private static List resultList = null;
	private static Object o = null;
	
	/**
	 * 下面这个方法有个弊端：实体对象的属性必须和数据库对应表的字段名称一样，如USER对象有一个name属性
	 * 那么其对于数据表TUSER的字段名称也必须是NAME。
	 * 如果属性名称和字段名称不一致怎么办？
	 * 解决办法：可以将对象的属性和对应数据表的字段名做一个映射，最简单的就是创建一个属性文件以键值对方式存放
	 * 如：name:USERNAME，age:USERAGE。通过读取属性文件来匹配属性和字段的映射。
	 * @param c
	 * @param resultSetList
	 * @return
	 * @throws Exception
	 */
	public static List ToObjectList( Class c, List<Map<String,Object>> resultSetList ) throws Exception{
		
		resultList = new ArrayList();
		Field[] fields = c.getDeclaredFields();
		
		for( Map<String,Object> map : resultSetList ){
			o = c.newInstance();
			for( Field f:fields ){
				String fieldName = f.getName();
				String methodName = "set"+fieldName.substring(0,1).toUpperCase() + fieldName.substring(1);
				Method method = c.getMethod(methodName, new Class[]{f.getType()});
				method.invoke(o, map.get(f.getName().toUpperCase()));
			}
			resultList.add(o);
		}		
			return resultList;
	}
	
}

?SimpleSQLGenerator.java

package com.ran.generic;

import java.util.HashMap;
import java.util.Map;
import java.util.Set;

public class SimpleSQLGenerator {
	
	private String tableName;    //目标表
	
	private int editFlag;       //1=insert,2=update,3=delete
	
	private String[] queryFields;  //查询字段组: select String[0],String[1],String[2] from table
	
	private Map<String,Object> updateFields = null;  //UPDATE字段-字段值 ：SET name="zhangsan",sex="男"
	
	private Map<String,Object> insertFields = null;  //INSERT字段-字段值 ：INSERT INTO TABLE(KEYSET) VALUES(VALUES);
	
	private Map<String,Object> fields = null;  //WHERE条件字段-字段值 ：WHERE name="zhangsan" 
	
	private boolean isOrder;   //是否排序
	
	private String orderField;  //排序字段名 order by orderField
	
	private String sort;    //升序或降序
	
	public static final String ASC = "ASC";  //升序
	
	public static final String DESC = "DESC";  //降序
	
	private String sql;        //生成SQLxxssss
	
	public String getTableName() {
		return tableName;
	}

	public void setTableName(String tableName) {
		this.tableName = tableName;
	}

	public int getEditFlag() {
		return editFlag;
	}

	public void setEditFlag(int editFlag) {
		this.editFlag = editFlag;
	}

	public String[] getQueryFields() {
		return queryFields;
	}

	public void setQueryFields(String[] queryFields) {
		this.queryFields = queryFields;
	}

	public Map<String, Object> getUpdateFields() {
		return updateFields;
	}

	public void setUpdateFields(Map<String, Object> updateFields) {
		this.updateFields = updateFields;
	}

	public Map<String, Object> getInsertFields() {
		return insertFields;
	}

	public void setInsertFields(Map<String, Object> insertFields) {
		this.insertFields = insertFields;
	}

	public Map<String, Object> getFields() {
		return fields;
	}

	public void setFields(Map<String, Object> fields) {
		this.fields = fields;
	}

	public boolean isOrder() {
		return isOrder;
	}

	public void setOrder(boolean isOrder) {
		this.isOrder = isOrder;
	}

	public String getOrderField() {
		return orderField;
	}

	public void setOrderField(String orderField) {
		this.orderField = orderField;
	}

	public String getSort() {
		return sort;
	}

	public void setSort(String sort) {
		this.sort = sort;
	}

	public SimpleSQLGenerator(){}
	
	public SimpleSQLGenerator( String tableName,String[] queryFields,Map<String,Object> updateFields,Map<String,Object> insertFields,Map<String,Object> fields, 
			boolean isOrder,String orderField,String sort){
		
		this.tableName = tableName;
		this.queryFields = queryFields;
		this.updateFields = updateFields;
		this.insertFields = insertFields;
		this.fields = fields;
		this.isOrder = isOrder;
		this.orderField = orderField;
		this.sort = sort;
	}
	
	public boolean isNULLForMap( Map map ){
		if( null != map && map.keySet().size() > 0 ){
			return true;
		}else{
			return false;
		}
	}
	
	//拼接WHERE后的条件字段 ： WHERE NAME=?,SEX=?,ADDRESS=?......
	public void spiltWHERE(){
		if( this.isNULLForMap(fields) ){
			Set<String> fieldSet = fields.keySet();
			sql += "where ";
			for(String field:fieldSet  ){
				sql += " "+field+" =? and";
			}
			sql = sql.substring(0,sql.lastIndexOf("and"));
		}
	}
	
	
	//生成聚合countSQL用于查询结果集总记录数
	public String countSQL(){
		sql = "select count(*) from "+tableName+" t ";
		this.spiltWHERE();
		return sql;
	}
	
	//增删改SQL生成器,参数editFlag：0=insert,1=update,2=delete
	public String editSQL( int editFlag ){
		if( editFlag == 0 ){
			if( this.isNULLForMap(insertFields) ){
				Set<String> insertFieldSet = insertFields.keySet();
				int filedsSize = insertFieldSet.size();
				//构建INSERT语句
				if( filedsSize != 0 ){
					sql = "insert into "+tableName+" ( ";
					for( String field : insertFieldSet  ){
						sql += field+",";
					}
					sql = sql.substring(0, sql.length()-1)+" ) values( ";
					for( String field : insertFieldSet  ){
						sql += "?,";
					}
					sql = sql.substring(0, sql.length()-1)+" )";
				}
			}
		//构建UPDATE语句	
		}else if( editFlag == 1 ){
			sql = "UPDATE "+tableName+" SET ";
			Set<String> updateFieldSet = updateFields.keySet();
			for( String field : updateFieldSet  ){
				sql += field+"=? ,";
			}
			sql = sql.substring(0,sql.length()-1);
			this.spiltWHERE();
		//构建DELETE语句
		}else if( editFlag == 2 ){
			sql = "DELETE FROM "+tableName+" ";
			this.spiltWHERE();
		}
		return sql;
	}
	
	//条件查询SQL
	public String querySQL(){
		sql = "SELECT ";
		if( queryFields.length > 0 ){
			for( String queryField:queryFields ){
				sql += queryField+" , ";
			}
			sql = sql.substring(0,sql.lastIndexOf(","))+" FROM "+tableName+" "; 
		}else{
			sql = "SELECT * FROM  "+tableName+" ";
		}
		this.spiltWHERE();
		if( this.isOrder ){
			sql += " ORDER BY "+orderField+" "+sort;
		}
		return sql;
	}
	
	//Oracle分页SQL:基本满足大部分功能性分页，如有性能瑕疵欢迎帮忙改进
	public String pageSQLForOracle( int pageRecord , int currentPage ){
		//排序分页：select * from ( select rownum rn, t.* from ( select * from ADDDRESSBOOK order by SEX ) t where rownum <= 5) where rn > 0
		//无排序分页：select * from ( select rownum rn, t.* from ADDDRESSBOOK t where rownum <= 5) where rn > 0
		if( this.isOrder ){
			sql = "select * from ( select rownum rn, t.* from ( select * from "+tableName+" ";
			this.spiltWHERE();
			sql += " ORDER BY "+orderField+" "+sort+" ) t where rownum <= "+currentPage*pageRecord+" ) where rn > "+(currentPage-1)*pageRecord;
		}else{
			sql = "select * from ( select rownum rn,* from "+tableName+" where ";
			this.spiltWHERE();
			sql += " and rownum <= "+currentPage*pageRecord+" ) where rn > "+(currentPage-1)*pageRecord;
		}
		return sql;
	}
	
	//MySQL分页SQL:基本满足大部分功能性分页，如有性能瑕疵欢迎帮忙改进
	public String pageSQLForMySQL( int pageRecord , int currentPage ){
		//排序分页：select * from ADDRESSBOOK order by createtime limit 0,5
		//无排序分页：select * from ADDRESSBOOK order limit 0,5
		sql = "select * from "+tableName+" t ";
		this.spiltWHERE();
		if( this.isOrder ){
			sql += " ORDER BY "+orderField+" "+sort;
		}
		sql += " limit "+(currentPage-1)*pageRecord + ","+pageRecord;
		return sql;
	}
}

AddressBookPO.java和MyDAO.java代码请查看下面附件“JavaJDBC”项目源代码。 数据表ADDRESS请参考下面的表结构：

? ?

• 
• 大小: 92.6 KB

• JavaJDBC.rar (4.8 MB)
• 描述: eclipse项目
• 下载次数: 1

• 
• 大小: 355.1 KB

• 查看图片附件