Jdbc BaseDao 博客分类： java JDBCSQLJavaDAOApache

package com.oa.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

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

import com.oa.core.DynaForm;
import com.oa.dao.Dao;
import com.oa.jdbc.ConnectionFactory;
import com.oa.jdbc.DaoAccessException;
import com.oa.jdbc.DybaBean;
import com.oa.jdbc.JdbcPage;
import com.oa.jdbc.Page;
import com.oa.jdbc.RowsDynaClass;

/**
 * 基类DAO，实现一些基本共有方法
 * 
 * @author Gao_wx Mar 26, 2008
 */
public class BaseDao implements Dao {
	protected Log logger = LogFactory.getLog(BaseDao.class);

	// 获取数据库链接
	protected Connection getConnection() {
		return ConnectionFactory.getConnection();
	}

	// 在此链接上，根据sql语句获取List
	protected List<DybaBean> getList(Connection conn, String sql)
			throws DaoAccessException {
		try {
			Statement st = conn.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			ResultSet rs = st.executeQuery(sql);
			return getList(rs);
		} catch (SQLException e) {
			throw new DaoAccessException(sql, e);
		}
	}

	// 根据Result获取List
	protected List<DybaBean> getList(ResultSet rs) throws SQLException {
		RowsDynaClass rsdc = new RowsDynaClass(rs);
		return rsdc.getRows();

	}

	// 根据sql语句获取List
	protected List<DybaBean> getList(String sql) throws DaoAccessException {
		Connection conn = getConnection();
		return getList(conn, sql);
	}

	// 在此连接上，根据sql语句，id加载一条记录
	protected DybaBean loadById(Connection conn, String sql, int id)
			throws DaoAccessException {
		try {
			PreparedStatement pst = conn.prepareStatement(sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			pst.setInt(1, id);
			ResultSet rs = pst.executeQuery();
			List list = getList(rs);
			if (list.size() > 1) {
				logger.warn("加载了2条以上的记录，请检查sql：" + sql);
			}
			if (list.size() == 0) {
				logger.warn("数据库没有此记录！");
				return null;
			} else {
				return (DybaBean) list.get(0);
			}
		} catch (SQLException e) {
			throw new DaoAccessException(sql, e);
		}
	}

	// 根据sql语句，id，加载一条记录
	protected DybaBean loadById(String sql, int id) throws DaoAccessException {
		Connection conn = getConnection();
		return loadById(conn, sql, id);
	}

	// 在此连接上，根据Sql和id删除一条记录，返回影响行数
	protected int deleteById(Connection conn, String sql, int id)
			throws SQLException {
		PreparedStatement pst = conn.prepareStatement(sql,
				ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		pst.setInt(1, id);
		int del = pst.executeUpdate();
		return del;
	}

	// 根据sql语句，id删除一条记录
	protected int deleteById(String sql, int id) throws DaoAccessException {
		Connection conn = getConnection();
		try {
			int del = deleteById(conn, sql, id);
			return del;
		} catch (SQLException e) {
			throw new DaoAccessException(sql, e);
		}

	}

	protected Page getPage(DynaForm form, String sql) throws DaoAccessException {
		Connection conn = getConnection();
		Page page = getPage(conn, form, sql);
		return page;
	}

	// 获取分页对象
	protected Page getPage(Connection conn, DynaForm form, String sql)
			throws DaoAccessException {
		int p = form.getInt("p");
		if (p < 1) {
			p = 1;
		}
		int size = form.getInt("size");
		if (size < 10) {
			size = 10;
		}
		Page page;
		try {
			page = new JdbcPage(conn, sql, p, size);
		} catch (SQLException e) {
			throw new DaoAccessException(sql, e);
		}
		return page;
	}
}

所有的select sql，不管是一对多，还是一对一，还是多对一，尽可能通过视图操作数据库！
数据库要改动的时候，改动视图即可！
如果表经常可能改动。那么请用动态表
http://supttkl.iteye.com/admin/blogs/181411