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;	}}