dbutils.java

本文论述了一个前台笔记本销售系统的开发过程

package com.set.db;

import java.beans.PropertyDescriptor;
import java.lang.reflect.InvocationTargetException;
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 javax.sql.DataSource;

import org.apache.commons.beanutils.PropertyUtils;
import org.omg.PortableInterceptor.SYSTEM_EXCEPTION;
import org.springframework.beans.BeanUtils;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;

import com.set.appframe.business.SpringBeanFactory;
import com.set.appframe.data.GenericValueObject;
import com.set.appframe.data.SearchResult;
import com.set.utils.ConvertUtil;
import com.set.utils.PagerUtil;

/**
 * the utils to deal with the database operations
 * 
 * @author tommy.zeng
 * 
 */
public class DBUtils {
	private static DataSource dataSource = null;;

	static {
		try {
			dataSource = (DataSource) SpringBeanFactory.getInstance().getBean(
					"datasource");
		} catch (Exception ex) {
			ex.printStackTrace();
		}

	}

	/**
	 * get a connection from connection pool(DataSource)
	 * 
	 * @throws DBAccessException
	 * @return Connection
	 */
	public static Connection getConnection() throws DBAccessException {
		Connection conn = null;

		try {
			if (null != dataSource) {
				conn = dataSource.getConnection();

			}
		} catch (Exception ex) {
			throw new DBAccessException("db.error");
		}

		return conn;
	}

	/**
	 * close connection,statement or resultset quietly
	 * 
	 * @param o
	 */
	public static void closeQuietly(Object o) {
		try {
			if (o instanceof Connection) {
				((Connection) o).close();
			} else if (o instanceof Statement) {
				((Statement) o).close();
			} else if (o instanceof ResultSet) {
				((ResultSet) o).close();
			}
		} catch (Exception e) {

		}
	}

	public static GenericValueObject queryForBean(final String sql,
			final Object[] params) {
		JdbcTemplate jt = getJdbcTemplate();
		Map map = null;
		try {
			map = jt.queryForMap(sql, params);
		} catch (Exception e) {
			map = null;
		}

		return ConvertUtil.convertToDynaBean(map);
	}

	public static GenericValueObject queryForBean(final String sql) {
		JdbcTemplate jt = getJdbcTemplate();
		Map map = null;
		try {
			map = jt.queryForMap(sql);
		} catch (Exception e) {
			map = null;
		}

		return ConvertUtil.convertToDynaBean(map);
	}

	public static List queryForList(final String sql, final Object[] params) {
		JdbcTemplate jt = getJdbcTemplate();

		List list = jt.queryForList(sql, params);

		return ConvertUtil.convertToDynaBeanList(list);
	}

	public static List queryForList(final String sql) {
		JdbcTemplate jt = getJdbcTemplate();

		List list = jt.queryForList(sql);

		return ConvertUtil.convertToDynaBeanList(list);
	}

	public static List pagedQueryForList(final String sql,
			final Object[] params, int pageNo, int pageSize) {
		String newSql = PagerUtil.getPageSql(sql, pageNo, pageSize);

		JdbcTemplate jt = getJdbcTemplate();
		List list = jt.queryForList(newSql, params);

		return ConvertUtil.convertToDynaBeanList(list);
	}

	public static SearchResult getSearchResult(final String sql,
			final Object[] params, int pageNo, int pageSize) {
		SearchResult sr = new SearchResult();
		List voList = pagedQueryForList(sql, params, pageNo, pageSize);
		int totalCnt = 0;
		if (voList.size() > 0) {
			totalCnt = getTotalCnt(sql, params);
		}
		sr.setCount(totalCnt);
		sr.setResultList(voList);
		return sr;
	}

	public static SearchResult getSearchResult(final String sql,
			final List params, int pageNo, int pageSize) {
		return getSearchResult(sql, params.toArray(), pageNo, pageSize);
	}

	public static int update(final String sql, final Object[] params) {
		JdbcTemplate jt = getJdbcTemplate();
		return jt.update(sql, params);
	}

	public static int update(final String sql) {
		JdbcTemplate jt = getJdbcTemplate();
		return jt.update(sql);
	}

	public static int update(final String sql, final List params) {
		Object para[] = null;
		if (null != params) {
			para = params.toArray();
		}
		return update(sql, para);
	}

	public static int[] batchUpdate(final String sql, final List params) {
		JdbcTemplate jt = getJdbcTemplate();
		BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() {
			public int getBatchSize() {
				return params.size();
			}

			public void setValues(PreparedStatement ps, int arg1)
					throws SQLException {
				if (true) {
					List object = (List) params.get(arg1);
					for (int i = 0; i < object.size(); i++) {
						try {
							Object o = object.get(i);
							if (o == null) {
								ps.setString(i + 1, "0");
							} else if (o instanceof java.lang.Integer) {
								ps.setInt(i + 1, (Integer) o);
							} else if (o instanceof java.lang.String) {
								ps.setString(i + 1, o.toString());
							} else if (o instanceof java.lang.Long) {
								ps.setLong(i + 1, (Long) o);
							} else {
								ps.setObject(i + 1, o);
							}
						} catch (Exception e) {
							e.printStackTrace();
						}
					}
				}
			}
		};
		return jt.batchUpdate(sql, setter);
	}

	private static int getTotalCnt(String sql, Object[] params) {
		String newSql = PagerUtil.getQueryCountSql(sql);
		JdbcTemplate jt = getJdbcTemplate();
		return jt.queryForInt(newSql, params);
	}

	private static JdbcTemplate getJdbcTemplate() {
		return new JdbcTemplate(dataSource);
	}

	public static List queryForList(String sql, List params) {
		Object para[] = null;
		if (null != params) {
			para = params.toArray();
		}
		return queryForList(sql, para);
	}

	public static List pagedQueryForList(String sql, List params, int page,
			int pageSize) {
		Object para[] = null;
		if (null != params) {
			para = params.toArray();
		}
		return pagedQueryForList(sql, para, page, pageSize);
	}

}