Java获取数据库各种查询结果_MySQL

package com.sky.connect;import java.lang.reflect.InvocationTargetException;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import org.apache.commons.beanutils.BeanUtils;import com.mysql.jdbc.Connection;import com.mysql.jdbc.PreparedStatement;import com.mysql.jdbc.ResultSetMetaData;/** * DAO设计模式 *  * @author 潘琢文 *  */public class DAO {	/**	 * 更新数据库操作	 * 	 * @param sql	 * @param args	 */	public void update(String sql, Object... args) {		Connection connection = null;		PreparedStatement preparedStatement = null;		try {			connection = JDBCTools.getConnection();			preparedStatement = (PreparedStatement) connection					.prepareStatement(sql);			for (int i = 0; i < args.length; i++) {				preparedStatement.setObject(i + 1, args[i]);			}			preparedStatement.executeUpdate();		} catch (Exception e) {			e.printStackTrace();		} finally {			JDBCTools.release(preparedStatement, connection);		}	}	/**	 * 通用查询方法，返回一条记录	 * 	 * @param clazz	 * @param sql	 * @param args	 * @return	 */	public  T get(Class clazz, String sql, Object... args) {		T entity = null;		Connection connection = null;		PreparedStatement preparedStatement = null;		ResultSet result = null;		try {			connection = JDBCTools.getConnection();			preparedStatement = (PreparedStatement) connection					.prepareStatement(sql);			for (int i = 0; i < args.length; i++) {				preparedStatement.setObject(i + 1, args[i]);			}			result = preparedStatement.executeQuery();			Map map = new HashMap();			ResultSetMetaData rsmd = (ResultSetMetaData) result.getMetaData();			if (result.next()) {				for (int i = 0; i < rsmd.getColumnCount(); i++) {					String columnLabel = rsmd.getColumnLabel(i + 1);					Object value = result.getObject(i + 1);					map.put(columnLabel, value);				}			}			if (map.size() > 0) {				entity = clazz.newInstance();				for (Map.Entry entry : map.entrySet()) {					String filedName = entry.getKey();					Object filedObject = entry.getValue();					BeanUtils.setProperty(entity, filedName, filedObject);				}			}		} catch (Exception e) {			e.printStackTrace();		} finally {			JDBCTools.release(result, preparedStatement, connection);		}		return entity;	}	/**	 * 通用查询方法，返回一个结果集	 * 	 * @param clazz	 * @param sql	 * @param args	 * @return	 */	public  List getForList(Class clazz, String sql, Object... args) {		List list = new ArrayList();		Connection connection = null;		PreparedStatement preparedStatement = null;		ResultSet result = null;		try {			connection = JDBCTools.getConnection();			preparedStatement = (PreparedStatement) connection					.prepareStatement(sql);			for (int i = 0; i < args.length; i++) {				preparedStatement.setObject(i + 1, args[i]);			}			result = preparedStatement.executeQuery();			List> values = handleResultSetToMapList(result);			list = transfterMapListToBeanList(clazz, values);		} catch (Exception e) {			e.printStackTrace();		} finally {			JDBCTools.release(result, preparedStatement, connection);		}		return list;	}	/**	 * 	 * @param clazz	 * @param values	 * @return	 * @throws InstantiationException	 * @throws IllegalAccessException	 * @throws InvocationTargetException	 */	public  List transfterMapListToBeanList(Class clazz,			List> values) throws InstantiationException,			IllegalAccessException, InvocationTargetException {		List result = new ArrayList();		T bean = null;		if (values.size() > 0) {			for (Map m : values) {				bean = clazz.newInstance();				for (Map.Entry entry : m.entrySet()) {					String propertyName = entry.getKey();					Object value = entry.getValue();					BeanUtils.setProperty(bean, propertyName, value);				}				// 13. 把 Object 对象放入到 list 中.				result.add(bean);			}		}		return result;	}	/**	 * 	 * @param resultSet	 * @return	 * @throws SQLException	 */	public List> handleResultSetToMapList(			ResultSet resultSet) throws SQLException {		List> values = new ArrayList>();		List columnLabels = getColumnLabels(resultSet);		Map map = null;		while (resultSet.next()) {			map = new HashMap();			for (String columnLabel : columnLabels) {				Object value = resultSet.getObject(columnLabel);				map.put(columnLabel, value);			}			values.add(map);		}		return values;	}	/**	 * 	 * @param resultSet	 * @return	 * @throws SQLException	 */	private List getColumnLabels(ResultSet resultSet)			throws SQLException {		List labels = new ArrayList();		ResultSetMetaData rsmd = (ResultSetMetaData) resultSet.getMetaData();		for (int i = 0; i < rsmd.getColumnCount(); i++) {			labels.add(rsmd.getColumnLabel(i + 1));		}		return labels;	}	/**	 * 通用查询方法，返回一个值（可能是统计值）	 * 	 * @param sql	 * @param args	 * @return	 */	@SuppressWarnings("unchecked")	public  E getForValue(String sql, Object... args) {		Connection connection = null;		PreparedStatement preparedStatement = null;		ResultSet resultSet = null;		try {			connection = JDBCTools.getConnection();			preparedStatement = (PreparedStatement) connection					.prepareStatement(sql);			for (int i = 0; i < args.length; i++) {				preparedStatement.setObject(i + 1, args[i]);			}			resultSet = preparedStatement.executeQuery();			if (resultSet.next()) {				return (E) resultSet.getObject(1);			}		} catch (Exception e) {			e.printStackTrace();		} finally {			JDBCTools.release(resultSet, preparedStatement, connection);		}		return null;	}}

package com.sky.connect;import java.io.IOException;import java.io.InputStream;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.util.Properties;import com.mysql.jdbc.Connection;import com.mysql.jdbc.Driver;import com.mysql.jdbc.PreparedStatement;import com.mysql.jdbc.Statement;/** * JDBC操作的工具类 版本 1.0 *  * @author 潘琢文 *  */public class JDBCTools {	/**	 * 使用preparedStatement进行数据更新	 * 	 * @param sql	 * @param args	 */	public static void update(String sql, Object ... args) {		Connection connection = null;		PreparedStatement preparedStatement = null;		try {			connection = JDBCTools.getConnection();			preparedStatement = (PreparedStatement) connection					.prepareStatement(sql);			for (int i = 0; i < args.length; i++) {				preparedStatement.setObject(i + 1, args[i]);			}			preparedStatement.executeUpdate();		} catch (Exception e) {			e.printStackTrace();		} finally {			JDBCTools.release(preparedStatement, connection);		}	}	/**	 * 结果查询关闭	 * 	 * @param rs	 * @param statement	 * @param conn	 */	public static void release(ResultSet rs, Statement statement,			Connection conn) {		if (rs != null) {			try {				rs.close();			} catch (SQLException e) {				e.printStackTrace();			}		}		if (statement != null) {			try {				statement.close();			} catch (Exception e2) {				e2.printStackTrace();			}		}		if (conn != null) {			try {				conn.close();			} catch (Exception e2) {				e2.printStackTrace();			}		}	}	/**	 * 数据库更新方法	 * 	 * @param sql	 */	public void uodate(String sql) {		Connection connection = null;		Statement statement = null;		try {			connection = JDBCTools.getConnection();			statement = (Statement) connection.createStatement();			statement.executeUpdate(sql);		} catch (Exception e) {			e.printStackTrace();		} finally {			JDBCTools.release(statement, connection);		}	}	/**	 * 关闭数据库连接的方法	 * 	 * @param statement	 * @param conn	 */	public static void release(Statement statement, Connection conn) {		if (statement != null) {			try {				statement.close();			} catch (Exception e2) {				e2.printStackTrace();			}		}		if (conn != null) {			try {				conn.close();			} catch (Exception e2) {				e2.printStackTrace();			}		}	}	/**	 * 编写通用方法获取任意数据库链接，不用修改源程序	 * 	 * @return	 * @throws ClassNotFoundException	 * @throws IllegalAccessException	 * @throws InstantiationException	 * @throws SQLException	 * @throws IOException	 */	public static Connection getConnection() throws InstantiationException,			IllegalAccessException, ClassNotFoundException, SQLException,			IOException {		String driverClass = null;		String jdbcUrl = null;		String user = null;		String password = null;		// 读取properties文件		InputStream in = JDBCTools.class.getClassLoader().getResourceAsStream(				"jdbc.properties");		Properties properties = new Properties();		properties.load(in);		driverClass = properties.getProperty("driver");		jdbcUrl = properties.getProperty("url");		user = properties.getProperty("user");		password = properties.getProperty("password");		Class.forName(driverClass);		Connection connection = (Connection) DriverManager.getConnection(				jdbcUrl, user, password);		return connection;	}}