---------封装代码---------------
package jdbc.fanshe; import org.apache.commons.beanutils.BeanUtils; import java.io.IOException; import java.io.InputStream; import java.lang.reflect.InvocationTargetException; import java.sql.*; import java.util.*; public class My_JDBC_Crud { private static String driverClass; private static String url; private static String username; private static String password; static { //运行阶段动态的加载类配置 InputStream resourceAsStream = My_JDBC_Crud.class.getClassLoader().getResourceAsStream("jdbc.properties"); //创建一个能够获取键值对的内部属性对象 Properties properties = new Properties(); //从数据流对象中获取配置信息 try { properties.load(resourceAsStream); } catch (IOException e) { throw new RuntimeException(e); } //解构值 driverClass = properties.getProperty("driverClassName"); url = properties.getProperty("url"); username = properties.getProperty("username"); password = properties.getProperty("password"); //加载驱动 try { Class.forName(driverClass); } catch (ClassNotFoundException e) { throw new RuntimeException(e); } } /** * 1.静态方法 获取数据库的链接 * * @return 链接对象 * @throws SQLException */ public static Connection getConnection() throws SQLException { return DriverManager.getConnection(url, username, password); } /** * 封装预编译对象 * * @param sql sql语句 * @param connection 链接 * @param parameters 参数 * @return * @throws SQLException */ public static PreparedStatement getPreparedStatement(String sql, Connection connection, Object... parameters) throws SQLException { PreparedStatement prestatement = null; ParameterMetaData parameterMetaData = null; int count = 0; //获取预编译对象 prestatement = connection.prepareStatement(sql); //获取预编译对象的元数据 parameterMetaData = prestatement.getParameterMetaData(); count = parameterMetaData.getParameterCount(); if (count != 0 && parameters != null && parameters.length == count) { for (int i = 0; i < count; i++) { prestatement.setObject(i + 1, parameters[i]); } } return prestatement; } /** * 2.关闭数据库操作对象 * @param conn * @param statement * @param resultSet * @throws SQLException */ public static void close(Connection conn, PreparedStatement statement, ResultSet resultSet) throws SQLException { if (conn != null) { conn.close(); } if (statement != null) { statement.close(); } if (resultSet != null) { resultSet.close(); } } /** * 3.测试更新 * * @param sql * @param parameters 参数 * @return 结果集 * @throws SQLException */ public static int updata1(String sql, Object... parameters) throws SQLException { //链接数据库 Connection connection = My_JDBC_Crud.getConnection(); PreparedStatement preStatement = My_JDBC_Crud.getPreparedStatement(sql, connection, parameters); // //获取预编译对象 // PreparedStatement preStatement = connection.prepareStatement(sql); // int num = preStatement.getParameterMetaData().getParameterCount(); // // if (num != 0 && parameters != null && parameters.length == num) { // for (int i = 0; i < num; i++) { // preStatement.setObject(i + 1, parameters[i]); // } // } int result = preStatement.executeUpdate(); My_JDBC_Crud.close(connection, preStatement, null); return result; } /*** 4.以指定的类型返回一条数据 * @param sql * @param cls * @param parameters * @param <T> * @return * @throws SQLException * @throws NoSuchMethodException * @throws InvocationTargetException * @throws InstantiationException * @throws IllegalAccessException */ public static <T> T queryBean(String sql, Class<T> cls, Object... parameters) throws SQLException, NoSuchMethodException, InvocationTargetException, InstantiationException, IllegalAccessException { //1.定义查询对象 Connection connection = null; PreparedStatement statement = null; ResultSet resultSet = null; //定义泛型变量,类型取决传值 T t = null; connection = My_JDBC_Crud.getConnection(); statement = My_JDBC_Crud.getPreparedStatement(sql, connection, parameters); resultSet = statement.executeQuery(); ResultSetMetaData metaData = resultSet.getMetaData(); while (resultSet.next()) { t = cls.getConstructor().newInstance(); int columnCount = metaData.getColumnCount(); for (int i = 1; i <= columnCount; i++) { BeanUtils.setProperty(t, metaData.getColumnName(i), resultSet.getObject(i)); } } My_JDBC_Crud.close(connection, statement, resultSet); return t; } /** * * 5.以指定的类型返回list集合数据 返回结果 * * @param sql * @param cls * @param parameters * @param <T> * @return * @throws SQLException * @throws NoSuchMethodException * @throws InvocationTargetException * @throws InstantiationException * @throws IllegalAccessException */ public static <T> ArrayList<T> queryBeanList(String sql, Class<T> cls, Object... parameters) throws SQLException, NoSuchMethodException, InvocationTargetException, InstantiationException, IllegalAccessException { //1.定义查询对象 Connection connection = null; PreparedStatement statement = null; ResultSet resultSet = null; //2.创建list集合 ArrayList<T> arrayList = new ArrayList<>(); connection = My_JDBC_Crud.getConnection(); statement = My_JDBC_Crud.getPreparedStatement(sql, connection, parameters); resultSet = statement.executeQuery(); ResultSetMetaData metaData = resultSet.getMetaData(); while (resultSet.next()) { T t = cls.getConstructor().newInstance(); int columnCount = metaData.getColumnCount(); for (int i = 1; i <= columnCount; i++) { BeanUtils.setProperty(t, metaData.getColumnName(i), resultSet.getObject(i)); } arrayList.add(t); } My_JDBC_Crud.close(connection, statement, resultSet); return arrayList; } /** * * 6.以HashMap形式返回一条键值对数据 * @param sql * @param parameters * @return * @throws SQLException */ public static Map<String, Object> queryMap(String sql, Object... parameters) throws SQLException { Connection connection = getConnection(); PreparedStatement preparedStatement = getPreparedStatement(sql, connection, parameters); ResultSet resultSet = preparedStatement.executeQuery(); ResultSetMetaData metaData = resultSet.getMetaData(); int count = metaData.getColumnCount(); HashMap<String, Object> map = new HashMap<>(); while (resultSet.next()) { for (int i = 0; i < count; i++) { map.put(metaData.getColumnName(i + 1), resultSet.getObject(i + 1)); } } close(connection, preparedStatement, resultSet); return map; } /** * * 7.返回集合中数据是HashMap的list集合 * @param sql * @param parameters * @return * @throws SQLException */ public static ArrayList<Map> queryMapList(String sql, Object... parameters) throws SQLException { Connection connection = getConnection(); PreparedStatement preparedStatement = getPreparedStatement(sql, connection, parameters); ResultSet resultSet = preparedStatement.executeQuery(); ResultSetMetaData metaData = resultSet.getMetaData(); int count = metaData.getColumnCount(); ArrayList<Map> maps = new ArrayList<>(); while (resultSet.next()) { HashMap<String, Object> map = new HashMap<>(); for (int i = 0; i < count; i++) { map.put(metaData.getColumnName(i + 1), resultSet.getObject(i + 1)); } maps.add(map); } close(connection, preparedStatement, resultSet); return maps; } /** * * 8.获取一条记录以对象的形式返回 * * @param sql * @param parameters * @return arrays * @throws SQLException */ public static Object[] queryArray(String sql, Object... parameters) throws SQLException { Connection connection = getConnection(); PreparedStatement preparedStatement = getPreparedStatement(sql, connection, parameters); ResultSet resultSet = preparedStatement.executeQuery(); ResultSetMetaData metaData = resultSet.getMetaData(); int count = metaData.getColumnCount(); Object[] objects = new Object[count]; while (resultSet.next()) { for (int i = 0; i < count; i++) { objects[i] = resultSet.getObject(i + 1); } } close(connection, preparedStatement, resultSet); return objects; } /** ** 9.返回元素是数组的list集合框架 * @param sql * @param parameters * @return * @throws SQLException */ public static List<Object[]> queryArrayList(String sql, Object... parameters) throws SQLException { Connection connection = getConnection(); PreparedStatement preparedStatement = getPreparedStatement(sql, connection, parameters); ResultSet resultSet = preparedStatement.executeQuery(); ResultSetMetaData metaData = resultSet.getMetaData(); int count = metaData.getColumnCount(); ArrayList<Object[]> objectslist = new ArrayList<>(); while (resultSet.next()) { Object[] objects = new Object[count]; for (int i = 0; i < count; i++) { objects[i] = resultSet.getObject(i + 1); } objectslist.add(objects); } close(connection, preparedStatement, resultSet); return objectslist; } }
----------测试代码-----------------
package jdbc; import jdbc.fanshe.My_JDBC_Crud; import java.lang.reflect.InvocationTargetException; import java.sql.SQLException; import java.util.List; public class TestJdbc { public static void main(String[] args) throws SQLException, InvocationTargetException, NoSuchMethodException, InstantiationException, IllegalAccessException { // System.out.println("My_JDBC_Crud.getConnection() = " + My_JDBC_Crud.getConnection()); // int i = My_JDBC_Crud.updata1("update user set nickname=? where id=?", "远野贵树", 5); // System.out.println("i = " + i); // ArrayList<user> user = My_JDBC_Crud.queryBeanList("select * from user where id=?", user.class, 3); // System.out.println("user = " + user); // Map<String, Object> aaa = My_JDBC_Crud.queryMap("select * from user", null); // System.out.println("aaa = " + aaa); // ArrayList<Map> maps = My_JDBC_Crud.queryMapList("select * from user", null); // System.out.println("aaa = " + maps); // Object[] aas = My_JDBC_Crud.queryArray("select * FROM user", null); // System.out.println("aas = " + aas); // for (int i = 0; i < aas.length; i++) { // for (Object o : aas) { // System.out.println("o = " + o); // } // } List<Object[]> objects = My_JDBC_Crud.queryArrayList("select * from user", null); for (int i = 0; i < objects.size(); i++) { for (int j = 0; j < objects.get(i).length; j++) { System.out.println("objects.get(i)[j] = " + objects.get(i)[j]); } System.out.println("------------"); } } }