java获取mysql数据库数据_Java获取数据库各种查询结果_MySQL

在查询时候有时候要一条数据，有时候要的是一个结果集，然而有时候返回就是一个统计值，通过对ResultSet和ResultSetMetaData的变换得到各类所需的查询结果，因为没有利用连接池数据链接管理比较麻烦，所以谢了一个工具类，

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