yiding_he's opinions

永久域名 http://yiding-he.javaeye.com

在 JavaScript 中如何创建多行字符串(JavaS ... | 可遍历的全排列

2007-03-07

DAO 设计2、查询方式的设计

关键字: DAO

创建了一个 DAO 类用来做所有的事情，包括查询。查询方法如下：

java 代码

public List query(Class clazz, String sql, List params) throws DAOException;

第一个参数是用来接受封装的类。
有时候仅查询一个字段，根本不用封装：

java 代码

public List query(String sql, List params) throws DAOException;

如果封装，就要考虑查询结果字段到对象属性的映射关系。根据公司的数据库设计规范，单词之间用下划线连起来。所以简单的替换一下就可以了。比如 USER_NAME 字段就映射到 userName 属性。

如果查询结果中的字段有多，那这个字段的值就只好丢弃；如果类的属性有多，也不会给它赋值。

分页查询。有时候会用到分页查询，所以添加了一个方法：

java 代码

public Page queryPage(Class clazz, String sql, List params, int pageIndex, int pageSize)
throws DAOException;

Page 对象除了包含查询结果外还有 totalNum 属性，表示查询总结果数。

表格封装的查询。有时候查询语句是动态生成的，没法确定查询结果的字段个数，用类来封装显然不合适。于是定义了一个通用的表格结构用来封装，并添加了 queryTable()

方法：

java 代码

public DataTable queryTable(String sql) throws DAOException;
public DataTable queryTable(String sql, List params, int pageIndex, int pageSize)
throws DAOException;

DataTable 对象包含的其实就是一堆 HashMap。不过它同 Page 一样有一个查询总结果数，而且添加了一些方法方便提取数据。这种查询的使用方式如下：

java 代码

DAO dao = DAO.getDAO(SOURCE_NAME);
DataTable table = dao.queryTable("select * from tt_test");
assertEquals(6, table.getColumns());
Map row = table.query("name", "张三丰"); // 在查询结果中搜索
assertNotNull(row);

静态字段作为映射关系配置。有些字典表或配置表，一开始就被全部读入并缓存起来。为了少写代码，DAO 提供将静态字段 TN 作为表名来查询的方式。如果类中存在 String 类型的静态字段 TN，则可以使用这个方法：

java 代码

public List query(Class clazz) throws DAOException;

17:44
浏览 (3717)
评论 (7)
分类: DAO 设计
进入论坛
相关推荐


package com.scitel.gdnumcommon.utils;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.scitel.gdnumcommon.entity.Pagination;
import com.scitel.gdnumcommon.entity.BaseVO;

public class BaseDAO {
	private static final Log log = LogFactory.getLog(BaseDAO.class);

	/**
	 * 保存数据,新建和修改都用这个
	 *
	 * @param con
	 * @param SQL
	 * @param params
	 * @throws Exception
	 * @author 
	 */
	public void save(Connection con, String SQL, List params)
			throws Exception {
		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(SQL);
			if (SQL == null) {
				throw new Exception();
			}

			if (params != null && params.size() > 0) {
				int count = 0;
				for (Iterator iterator = params.iterator(); iterator.hasNext(); count++) {
					Object object = iterator.next();
					setObjectValue(ps, count + 1, object);
				}
			}
			ps.executeUpdate();
		} catch (Exception e) {
			log.error(e);
			throw e;
		} finally {
			try{
				if(ps != null) {
					ps.close();
				}
			}catch(Exception e){

			}
		}
	}

	/**
	 * 保存数据,新建和修改都用这个,通过字段名称匹配类型
	 * @param con
	 * @param SQL
	 * @param voclass
	 * @param paramMap
	 * @throws Exception
	 * @author 
	 */
	public void save(Connection con, String SQL, Class voclass, Map paramMap)
			throws Exception {
		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(SQL);
			if (SQL == null) {
				throw new Exception();
			}

			if (paramMap != null && paramMap.size() > 0) {
				int count = 0;
				for (Iterator iterator = paramMap.keySet().iterator(); iterator.hasNext(); count++) {

					String key = (String)iterator.next();
					Object object = paramMap.get(key);
					setObjectValue(ps, voclass, count+1, key, object);
				}
			}
			ps.executeUpdate();
		} catch (Exception e) {
			log.error(e);
			throw e;
		} finally {
			try{
				if(ps != null) {
					ps.close();
				}
			}catch(Exception e){

			}
		}
	}

	/**
	 * 删除数据
	 *
	 * @param con
	 * @param SQL
	 * @param params
	 * @throws Exception
	 * @author 
	 */
	public void remove(Connection con, String SQL, List params)
			throws Exception {
		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(SQL);
			if (SQL == null) {
				throw new Exception();
			}
			if (params != null && params.size() > 0) {
				int count = 0;
				for (Iterator iterator = params.iterator(); iterator.hasNext(); count++) {
					Object object = iterator.next();
					setObjectValue(ps, count + 1, object);
				}
			}
			ps.executeUpdate();
		} catch (Exception e) {
			log.error(e);
			throw e;
		} finally {
			try{
				if(ps != null) {
					ps.close();
				}
			}catch(Exception e){

			}
		}
	}

	/**
	 * 根据ID选择数据
	 *
	 * @param con
	 * @param SQL
	 * @param id
	 * @param voclass
	 * @return
	 * @throws Exception
	 * @author 
	 */
	public BaseVO selectById(Connection con, String SQL, String id,
			Class voclass) throws Exception {
		Object po = null; // 承载值对象
		PreparedStatement ps = null;
		ResultSet rs = null;
		ResultSetMetaData rsm = null;
		try {
			ps = con.prepareStatement(SQL);
			if (SQL == null) {
				throw new Exception();
			}

			ps.setString(1, id);

			rs = ps.executeQuery();
			rsm = rs.getMetaData();
			if (rs.next()) {
				Map entity = new HashMap();
				for (int i = 1; i <= rsm.getColumnCount(); i++) {
					String columnName = rsm.getColumnName(i).toLowerCase();
					Object columnValue = getObjectValue(rs, voclass, i, columnName);
					entity.put(columnName, columnValue);
				}
				if (voclass != null) {
					po = voclass.newInstance();
					BeanUtils.populate(po, entity);
				}
				 
			}
		} catch (Exception e) {
			log.error(e);
			throw e;
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
			} catch (Exception e) {

			}
			try {
				if (ps != null) {
					ps.close();
				}
			} catch (Exception e) {

			}
		}
		return (BaseVO) po;
	}

	/**
	 * 选择记录,不分页
	 * @param con
	 * @param SQL
	 * @param params
	 * @param voclass
	 * @return
	 * @throws Exception
	 * @author 
	 */
	public List select(Connection con, String SQL, List params, Class voclass)
			throws Exception {
		Object vo = null; // 承载值对象
		PreparedStatement ps = null;
		ResultSet rs = null;
		ResultSetMetaData rsm = null;
		List relist = null;
		try {
			ps = con.prepareStatement(SQL);
			if (SQL == null) {
				throw new Exception();
			}
			if (params != null && params.size() > 0) {
				int count = 0;
				for (Iterator iterator = params.iterator(); iterator.hasNext(); count++) {
					Object object = iterator.next();
					setObjectValue(ps, count + 1, object);
				}
			}
			rs = ps.executeQuery();
			rsm = rs.getMetaData();
			relist = new ArrayList();
			while (rs.next()) {
				Map entity = new HashMap();
				for (int i = 1; i <= rsm.getColumnCount(); i++) {
					String columnName = rsm.getColumnName(i).toLowerCase();
					Object columnValue = getObjectValue(rs, voclass, i, columnName);
					entity.put(columnName, columnValue);
					
				}
				if (voclass != null) {
					vo = voclass.newInstance();
					BeanUtils.populate(vo, entity);
					relist.add(vo);
				} else {
					relist.add(entity);
				}
			}
		} catch (Exception e) {
			log.error(e);
			throw e;
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
			} catch (Exception e) {

			}
			try {
				if (ps != null) {
					ps.close();
				}
			} catch (Exception e) {

			}
			
		}
		return relist;
	}

	/**
	 * 分页查询
	 *
	 * @param con
	 * @param SQL
	 * @param params
	 * @param voclass
	 * @param pagination
	 * @return
	 * @throws Exception
	 * @author 
	 */
	public List selectPagination(Connection con, String SQL, List params,
			Class voclass, Pagination pagination) throws Exception {
		if (SQL == null) {
			throw new NullPointerException("SQL不能为空!");
		}
		if (pagination == null) {
			throw new NullPointerException("分页类不能为空!");
		}

		// TODO Auto-generated method stub
		Object vo = null; // 承载值对象
		PreparedStatement ps = null;
		ResultSet rs = null;
		ResultSetMetaData rsm = null;
		List relist = null;
		try {

			ps = con.prepareStatement("select count(1) as count_ from ( " + SQL + " )");

			if (params != null && params.size() > 0) {
				int count = 0;
				for (Iterator iterator = params.iterator(); iterator.hasNext(); count++) {
					Object object = iterator.next();
					setObjectValue(ps, count + 1, object);
				}
			}

			rs = ps.executeQuery();
			if (rs.next()) {
				pagination.setTotalCount(rs.getInt(1));
				
			}
			

			if (pagination.getTotalCount() > 0) {
				/* 组成分页内容 */
				StringBuffer pagingSelect = new StringBuffer(100);
				pagingSelect
						.append("select * from ( select row_.*, rownum rownum_ from ( ");
				pagingSelect.append(SQL);
				pagingSelect
						.append(" ) row_ where rownum <= ?) where rownum_ > ?");

				ps = con.prepareStatement(pagingSelect.toString());
				int count = 0;
				if (params != null && params.size() > 0) {
					for (Iterator iterator = params.iterator(); iterator.hasNext(); count++) {
						Object object = iterator.next();
						setObjectValue(ps, count + 1, object);
					}
				}
				
				ps.setInt(count + 1, pagination.getPage()
						* pagination.getCount());
				ps.setInt(count + 2, (pagination.getPage() - 1)
						* pagination.getCount());

				log.info("pagination.getPage():" + pagination.getPage());
				log.info("pagination.getCount():" + pagination.getCount());
				rs = ps.executeQuery();
				rsm = rs.getMetaData();
				relist = new ArrayList();
				while (rs.next()) {
					Map entity = new HashMap();
					for (int i = 1; i <= rsm.getColumnCount(); i++) {
						String columnName = rsm.getColumnName(i).toLowerCase();
						Object columnValue = getObjectValue(rs, voclass, i, columnName);
						entity.put(columnName, columnValue);
					}
					if (voclass != null) {
						vo = voclass.newInstance();
						BeanUtils.populate(vo, entity);
						relist.add(vo);
					} else {
						relist.add(entity);
					}
				}
			}
		} catch (Exception e) {
			log.error(e);
			throw e;
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
			} catch (Exception e) {

			}
			try {
				if (ps != null) {
					ps.close();
				}
			} catch (Exception e) {

			}

		}
		return relist;
	}

	/**
	 * 获得SequenceValue
	 * @param sequenceName
	 * @return
	 * @throws Exception
	 * @author 
	 */
	public Long getSequenceValue(Connection con, String sequenceName)throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		Long sequenceValue = null;
		try{
			ps = con.prepareStatement("select " + sequenceName + ".nextval from dual");
			rs = ps.executeQuery();
			if(rs.next()) {
				sequenceValue = new Long(rs.getLong(1));
			}
		}catch(Exception e){
			log.error(e);
			throw e;
		}finally{
			try {
				if (rs != null) {
					rs.close();
				}
			} catch (Exception e) {

			}
			try {
				if (ps != null) {
					ps.close();
				}
			} catch (Exception e) {

			}
		}
		return sequenceValue;
	}
	/**
	 * 把对象传入数据库
	 * @param ps
	 * @param count
	 * @param object
	 * @author 
	 */
	private final void setObjectValue(PreparedStatement ps, int count, Object object) throws Exception {
		log.debug("count is " + count + " object is " + object);
		if(object != null) {
			if(object instanceof Integer){
				ps.setInt(count, ((Integer)object).intValue());
			}else if(object instanceof Long) {
				ps.setLong(count, ((Long)object).longValue());
			}else if(object instanceof BigDecimal){
				ps.setBigDecimal(count, (BigDecimal)object);
			}else if(object instanceof String){
				ps.setString(count, (String)object);
			}else if(object instanceof java.util.Date) {
				if(object!=null){
					long time = ((java.util.Date)object).getTime();
					ps.setDate(count, new java.sql.Date(time));
				}else{
					ps.setDate(count, null);
				}
			}else{
				ps.setObject(count, object);
			}
		}else{
			ps.setNull(count, Types.INTEGER);
		}
	}

	/**
	 * 把对象传入数据库
	 * @param ps
	 * @param clazz
	 * @param count
	 * @param columnName
	 * @param object
	 * @throws Exception
	 * @author 
	 */
	private final void setObjectValue(PreparedStatement ps, Class clazz, int count,
			String columnName, Object object)throws Exception {
		log.debug("count is " + count + " columnName is " + columnName + " object is " + object);
		String classType = clazz.getDeclaredField(columnName).getType().getName();
		if(classType.equals("java.lang.Integer")){
			if(object != null) {
				ps.setInt(count, ((Integer)object).intValue());
			}else{
				ps.setNull(count, Types.INTEGER);
			}
		}else if(classType.equals("java.lang.Long")) {
			if(object != null ) {
				ps.setLong(count, ((Long)object).longValue());
			}else{
				ps.setNull(count, Types.INTEGER);
			}
		}else if(classType.equals("java.math.BigDecimal")){
			if(object != null) {
				ps.setBigDecimal(count, (BigDecimal)object);
			}else{
				ps.setNull(count, Types.NUMERIC);
			}
		}else if(classType.equals("java.lang.String")){
			if(object != null) {
				ps.setString(count, (String)object);
			}else{
				ps.setString(count, null);
			}
		}else if(classType.equals("java.util.Date")) {
			if(object!=null){
				long time = ((java.util.Date)object).getTime();
				ps.setDate(count, new java.sql.Date(time));
			}else{
				ps.setDate(count, null);
			}
		}else{
			ps.setObject(count, object);
		}
	}

	/**
	 * 把数据从数据取出来
	 * @param rs
	 * @param clazz
	 * @param count
	 * @param columnName
	 * @return
	 * @throws Exception
	 * @author 
	 */
	private final Object getObjectValue(ResultSet rs, Class clazz, int count, String columnName) throws Exception {
		Object fieldValue = null;
		log.debug("columnName is " + columnName + " count is " + count);
		if(columnName != null) {
			if("rownum".equals(columnName)) {
				fieldValue = new Long(rs.getLong(count));
			}else if("rownum_".equals(columnName)) {
				fieldValue = new Long(rs.getLong(count));
			}else if("count_".equals(columnName)) {
				fieldValue = new Long(rs.getLong(count));
			}else{
				String classType = clazz.getDeclaredField(columnName).getType().getName();

				if(classType.equals("java.lang.Integer")){
					fieldValue =new Integer( rs.getInt(count));
				}else if(classType.equals("java.lang.Long")) {
					fieldValue =new Long( rs.getLong(count));
				}else if(classType.equals("java.math.BigDecimal")){
					fieldValue = rs.getBigDecimal(count);
				}else if(classType.equals("java.lang.String")){
					fieldValue = rs.getString(count);
				}else if(classType.equals("java.util.Date")) {
					java.sql.Date date = rs.getDate(count);
					if(date!= null){
						fieldValue = new java.util.Date(date.getTime());
					}
				}else{
					fieldValue = rs.getString(count);
				}
			}
		}
		return fieldValue;
	}


}

yiding_he 2007-03-28

Annotation 确实是个好东西，可惜我们的项目目前没有打算升级到 java 5.0 的迹象。ADO.net 几乎是一个离线的数据库，十分全面而且庞大，要模仿它我还没那个本事。特别是并发控制，我竭力避免同这种东西打交道。

zealic 2007-03-28

照此深入下去
就是 .Net 中的 ADO.Net(OO版) 了

不错,Java 中的重复太多了
垄断和自由都有其优势和劣势

希望以后的什么框架多用 Annotation

hagensas 2007-03-28

我认为这不是一个dao，而是一个dataadpter

yiding_he

浏览: 127817 次
性别:
来自: 湖南
详细资料

yiding_he's opinions

DAO 设计2、查询方式的设计

评论

搜索本博客

博客分类

我的相册

其他分类

最近加入圈子

存档

最新评论

评论排行榜