dataadapter.java

网络硬盘

package com.zte.webfile.dal;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Vector;

import javax.sql.DataSource;

import org.apache.log4j.Logger;

public class DataAdapter {

	private final static Logger log = Logger.getLogger(DataAdapter.class);

	/**
	 * 根据传入的sql语句，获取PreparedStatement对象
	 * 
	 * @param sql
	 *            sql语句
	 * @return
	 * @throws Exception 
	 */
	protected static PreparedStatement getPreparedStatement(String sql)
			throws Exception {
		DataSource ds = JNDIDataSourceFactory.getDataSource();
		Connection conn = ds.getConnection();
		PreparedStatement statement = conn.prepareStatement(sql,
				ResultSet.TYPE_SCROLL_INSENSITIVE,
				// 使游标的精确移动功能有效
				ResultSet.CONCUR_UPDATABLE);
		System.out.println();
		return statement;
	}

	protected static CallableStatement getPrepareCall(String sql) throws Exception {
		DataSource ds = JNDIDataSourceFactory.getDataSource();
		Connection conn = ds.getConnection();
		CallableStatement cs = conn.prepareCall(sql,
				ResultSet.TYPE_SCROLL_INSENSITIVE,
				// 使游标的精确移动功能有效
				ResultSet.CONCUR_UPDATABLE);
		return cs;
	}

	/**
	 * 执行数据查询操作，返回分页后的结果集。若pageSize小于0，则不分页。 使用方法： <code>
	 * 	      // sql语句
	 *        String sql = "select * from testTable where condi1 = ? " +
	 *        			"and condi2 = ?";
	 *        // 添加参数
	 *        Vector params = new Vector();
	 *        params.add("p1");
	 *        params.add("p2");
	 *        
	 *        // 执行查询
	 *        Vector datas = DataAdapter.getData(sql, params);
	 *        
	 *        // 解析数据
	 *        if(datas != null) {
	 *	        for(int i = 0; i < datas.size; i++) {
	 *				Vector row = (Vector) datas.get[i];
	 *				for(int j = 0; j < row.size; j++) {
	 *					System.out.print(row.get[j] + "  ");
	 *				}
	 *				System.out.println();
	 *			}
	 *        }
	 * </code>
	 * @param sql
	 *            待执行的sql语句
	 * @param args
	 *            sql语句中的参数列表
	 * @param pageSize
	 *            数据集分页大小
	 * @param pageNumber
	 *            数据集分页页号
	 * @return
	 * @throws Exception 
	 */
	public static Vector getData(String sql, Vector params, int pageSize,
			int pageNumber) throws Exception {
		Vector datas = new Vector();

		try {
			PreparedStatement ps = getPreparedStatement(sql);

			// 设置查询参数
			if (params != null) {
				for (int i = 0; i < params.size(); i++) {
					//log.info(params.get(i));
					ps.setObject(i + 1, params.get(i));
				}

			}
			// 执行查询
			Pageable rs = new PageableResultSet(ps.executeQuery());
			rs.setPageSize(pageSize);
			rs.gotoPage(pageNumber);
			// 放入缓冲列表
			ResultSetMetaData rsmd = rs.getMetaData();
			int i = 0;
			rs.previous();
			for (; i < rs.getPageRowsCount() && rs.next(); i++) {
				Vector row = new Vector();
				for (int j = 1; j < rsmd.getColumnCount() + 1; j++) {
					row.add(rs.getObject(j));
				}
				datas.add(row);
			}
			if (i == 0)
				return null;
			rs.close();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
			log.error(e.getMessage());
			return null;
		}
		return datas;
	}

	public static Vector getDataByStoreProcedure(String sql, Vector params,
			int pageSize, int pageNumber) throws Exception {
		Vector datas = new Vector();

		try {
			CallableStatement cs = getPrepareCall(sql);

			// 设置查询参数
			if (params != null) {
				for (int i = 0; i < params.size(); i++) {
					// log.info(params.get(i));
					cs.setObject(i + 1, params.get(i));
				}

			}
			// 执行查询
			Pageable rs = new PageableResultSet(cs.executeQuery());
			rs.setPageSize(pageSize);
			rs.gotoPage(pageNumber);
			// 放入缓冲列表
			ResultSetMetaData rsmd = rs.getMetaData();
			int i = 0;
			rs.previous();
			for (; i < rs.getPageRowsCount() && rs.next(); i++) {
				Vector row = new Vector();
				for (int j = 1; j < rsmd.getColumnCount() + 1; j++) {
					row.add(rs.getObject(j));
				}
				datas.add(row);
			}
			if (i == 0)
				return null;
			rs.close();
			cs.close();
		} catch (SQLException e) {
			e.printStackTrace();
			log.error(e.getMessage());
			return null;
		}
		return datas;
	}

	/**
	 * 执行数据查询操作，不分页。
	 * 
	 * @param sql
	 *            待执行的sql语句
	 * @param args
	 *            sql语句中的参数列表
	 * @return
	 * @throws Exception 
	 */
	public static Vector getData(String sql, Vector params) throws Exception {
		return getData(sql, params, 0, 0);
	}
	
	public static Vector getDataByStoreProcedure(String sql, Vector params) throws Exception {
		return getDataByStoreProcedure(sql, params, 0, 0);
	}

	/**
	 * 执行数据查询操作，不提供参数
	 * 
	 * @param sql
	 *            待执行的sql语句
	 * @return
	 * @throws Exception 
	 */
	public static Vector getData(String sql) throws Exception {
		return getData(sql, null);
	}

	/**
	 * 执行单值查询，对于只想得到一个数据的查询，可以使用此方法。若没有 有效数据，则返回null
	 * 
	 * @param sql
	 *            待执行的sql语句
	 * @param params
	 *            参数列表
	 * @return 数据查询的唯一值
	 * @throws Exception 
	 */
	public static Object getSingleValue(String sql, Vector params) throws Exception {
		Vector datas = getData(sql, params);
		if ((datas == null) || (datas.size() == 0))
			return null;
		Vector row = (Vector) datas.get(0);
		if ((row == null) || (row.size() == 0))
			return null;
		return row.get(0);
	}

	/**
	 * 执行数据查询操作
	 * 
	 * @param sql
	 *            待执行的sql语句
	 * @param params
	 *            sql语句中的参数列表
	 * @throws Exception 
	 */
	public static int update(String sql, Vector params) throws Exception {
		int returnValue = -1;
		try {
			PreparedStatement ps = getPreparedStatement(sql);

			// 设置查询参数
			if (params != null) {
				for (int i = 0; i < params.size(); i++) {
					// log.info(params.get(i));
					ps.setObject(i + 1, params.get(i));
				}
			}

			returnValue = ps.executeUpdate();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
			log.error(e.getMessage());
		}
		return returnValue;
	}

}