CommonUtil.java
package com.atguigu.jdbc; import java.lang.reflect.Field; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; public class CommonUtil { /** * 把结果集中的每一行都放入Object对象数组中, 再把所有的Object对象数组放入一个List集合中. * @throws SQLException */ public static List<Object[]> query(Connection connection, String sql, Object... values) throws SQLException { PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { preparedStatement = connection.prepareStatement(sql); fillArguments(preparedStatement, values); resultSet = preparedStatement.executeQuery(); List<Object[]> list = new ArrayList<Object[]>(); int cols = resultSet.getMetaData().getColumnCount(); while (resultSet.next()) { Object[] dataRow = new Object[cols]; for (int i = 0; i < dataRow.length; i++) { dataRow[i] = resultSet.getObject(i + 1); } list.add(dataRow); } return list; } finally { JdbcUtil.close(null, preparedStatement, resultSet); } } /** * 把结果集中的第一行数据,全放入一个对象数组中 * @throws SQLException */ public static Object[] queryValueArray(Connection connection, String sql, Object... values) throws SQLException { PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { preparedStatement = connection.prepareStatement(sql); fillArguments(preparedStatement, values); resultSet = preparedStatement.executeQuery(); if (resultSet.next()) { Object[] dataRow = new Object[resultSet.getMetaData().getColumnCount()]; for (int i = 0; i < dataRow.length; i++) { dataRow[i] = resultSet.getObject(i + 1); } return dataRow; } else { return null; } } finally { JdbcUtil.close(null, preparedStatement, resultSet); } } /** * 从结果集中获取第一行的第一列 * @throws SQLException */ public static Object queryValue(Connection connection, String sql, Object... values) throws SQLException { PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { preparedStatement = connection.prepareStatement(sql); fillArguments(preparedStatement, values); resultSet = preparedStatement.executeQuery(); if (resultSet.next()) { return resultSet.getObject(1); } else { return null; } } finally { JdbcUtil.close(null, preparedStatement, resultSet); } } /** * 把结果集中第一行转换为对象返回 * @throws SQLException * @throws SecurityException * @throws NoSuchFieldException * @throws IllegalAccessException * @throws InstantiationException */ public static <T> T queryBean(Connection connection, String sql, Class<T> clazz, Object... values) throws SQLException, NoSuchFieldException, SecurityException, InstantiationException, IllegalAccessException { PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { preparedStatement = connection.prepareStatement(sql); fillArguments(preparedStatement, values); resultSet = preparedStatement.executeQuery(); if (resultSet.next()) { T t = clazz.newInstance(); ResultSetMetaData metaData = resultSet.getMetaData(); int cols = metaData.getColumnCount(); for (int i = 0; i < cols; i++) { String label = metaData.getColumnLabel(i + 1); Object value = resultSet.getObject(label); Field field = clazz.getDeclaredField(label); field.setAccessible(true); field.set(t, value); } return t; } else { return null; } } finally { JdbcUtil.close(null, preparedStatement, resultSet); } } /** * 把结果集的所有记录都封装成对象,并把所有对象放在一个List集合中 * @throws SQLException * @throws IllegalAccessException * @throws InstantiationException * @throws SecurityException * @throws NoSuchFieldException */ public static <T> List<T> query(Connection connection, String sql, Class<T> clazz, Object... values) throws SQLException, InstantiationException, IllegalAccessException, NoSuchFieldException, SecurityException { PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { preparedStatement = connection.prepareStatement(sql); fillArguments(preparedStatement, values); resultSet = preparedStatement.executeQuery(); List<T> list = new ArrayList<T>(); ResultSetMetaData metaData = resultSet.getMetaData(); int cols = metaData.getColumnCount(); while (resultSet.next()) { T t = clazz.newInstance(); for (int i = 0; i < cols; i++) { String label = metaData.getColumnLabel(i + 1); Object value = resultSet.getObject(label); if (value != null) { Field field = clazz.getDeclaredField(label); field.setAccessible(true); field.set(t, value); } } list.add(t); } return list; } finally { JdbcUtil.close(null, preparedStatement, resultSet); } } /** * 通用更新操作 * @throws SQLException */ public static int update(Connection connection, String sql, Object... values) throws SQLException { PreparedStatement preparedStatement = null; try { preparedStatement = connection.prepareStatement(sql); fillArguments(preparedStatement, values); return preparedStatement.executeUpdate(); } finally { JdbcUtil.close(null, preparedStatement); } } public static void fillArguments(PreparedStatement preparedStatement, Object... values) throws SQLException { for (int i = 0; i < values.length; i++) { preparedStatement.setObject(i + 1, values[i]); } } } BaseDAO