# Jdbc查询工具 **Repository Path**: congyingcy/jdbc-query-tool ## Basic Information - **Project Name**: Jdbc查询工具 - **Description**: 用jdbc 封装查询工具 - **Primary Language**: Java - **License**: Not specified - **Default Branch**: master - **Homepage**: None - **GVP Project**: No ## Statistics - **Stars**: 1 - **Forks**: 0 - **Created**: 2022-04-16 - **Last Updated**: 2023-03-30 ## Categories & Tags **Categories**: Uncategorized **Tags**: None ## README # 自定义 SQL 查询工具 1、获取连接，释放资源封装到一个工具类中 ```java package com.southwind.utils; import com.mchange.v2.c3p0.ComboPooledDataSource; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class C3P0Tools { private static ComboPooledDataSource dataSource; static { dataSource = new ComboPooledDataSource("testc3p0"); } /** * 获取连接对象 * @return */ public static Connection getConnection(){ Connection connection = null; try { connection = dataSource.getConnection(); } catch (SQLException throwables) { throwables.printStackTrace(); } return connection; } /** * 释放资源 * @param connection * @param statement * @param resultSet */ public static void release(Connection connection, Statement statement, ResultSet resultSet){ try { if(connection!=null){ connection.close(); } if(statement!=null){ statement.close(); } if(resultSet!=null){ resultSet.close(); } } catch (SQLException throwables) { throwables.printStackTrace(); } } } ``` 2、通过 SQL 查询某个对象的方法 ```java /** * 通过SQL查询某个对象 * @param connection * @param sql * @param clazz * @return */ public T getBean(Connection connection, String sql, Class clazz){ PreparedStatement statement = null; ResultSet resultSet = null; try { //查询SQL statement = connection.prepareStatement(sql); resultSet = statement.executeQuery(); //映射成JavaBean，反射 Object object = clazz.getConstructor(null).newInstance(null); //解析结果集 ResultSetMetaData metaData = resultSet.getMetaData(); int columnCount = metaData.getColumnCount(); if (resultSet.next()) { for (int i = 1; i <= columnCount; i++) { String columnName = metaData.getColumnName(i); String columnTypeName = metaData.getColumnTypeName(i); Object value = null; switch (columnTypeName){ case "INT": value = resultSet.getInt(columnName); break; case "VARCHAR": value = resultSet.getString(columnName); break; } //给属性赋值 //获取setter方法 String methodName = "set"+columnName.substring(0, 1).toUpperCase()+columnName.substring(1); Field field = clazz.getDeclaredField(columnName); Method method = clazz.getMethod(methodName, field.getType()); method.invoke(object,value); } } return (T)object; } catch (Exception throwables) { throwables.printStackTrace(); } finally { C3P0Tools.release(connection, statement, resultSet); } return null; } ``` 3、完成可以传 SQL 参数的方法，查询某个对象 ```java /** * 可以传SQL参数查询某个对象 * @param connection * @param sql * @param clazz * @param params * @return */ public T getBean(Connection connection,String sql,Class clazz,Object... params){ PreparedStatement statement = null; ResultSet resultSet = null; try { //查询SQL statement = connection.prepareStatement(sql); //替换参数 for (int i = 0; i < params.length; i++) { Object param = params[i]; String typeName = param.getClass().getTypeName(); switch (typeName){ case "java.lang.Integer": statement.setInt(i+1,(Integer) param); break; case "java.lang.String": statement.setString(i+1,(String) param); break; } } resultSet = statement.executeQuery(); //映射成JavaBean，反射 Object object = clazz.getConstructor(null).newInstance(null); //解析结果集 ResultSetMetaData metaData = resultSet.getMetaData(); int columnCount = metaData.getColumnCount(); while (resultSet.next()) { for (int i = 1; i <= columnCount; i++) { String columnName = metaData.getColumnName(i); String columnTypeName = metaData.getColumnTypeName(i); Object value = null; switch (columnTypeName){ case "INT": value = resultSet.getInt(columnName); break; case "VARCHAR": value = resultSet.getString(columnName); break; } //给属性赋值 //获取setter方法 String methodName = "set"+columnName.substring(0, 1).toUpperCase()+columnName.substring(1); Field field = clazz.getDeclaredField(columnName); Method method = clazz.getMethod(methodName, field.getType()); method.invoke(object,value); } } return (T)object; } catch (Exception throwables) { throwables.printStackTrace(); } finally { C3P0Tools.release(connection, statement, resultSet); } return null; } ``` 4、返回集合 ```java /** * 查询对象集合 * @param connection * @param sql * @param clazz * @return */ public List getBeans(Connection connection,String sql,Class clazz){ PreparedStatement statement = null; ResultSet resultSet = null; List list = new ArrayList<>(); try { //查询SQL statement = connection.prepareStatement(sql); resultSet = statement.executeQuery(); //解析结果集 ResultSetMetaData metaData = resultSet.getMetaData(); int columnCount = metaData.getColumnCount(); while (resultSet.next()) { //映射成JavaBean，反射 Object object = clazz.getConstructor(null).newInstance(null); for (int i = 1; i <= columnCount; i++) { String columnName = metaData.getColumnName(i); String columnTypeName = metaData.getColumnTypeName(i); Object value = null; switch (columnTypeName){ case "INT": value = resultSet.getInt(columnName); break; case "VARCHAR": value = resultSet.getString(columnName); break; } //给属性赋值 //获取setter方法 String methodName = "set"+columnName.substring(0, 1).toUpperCase()+columnName.substring(1); Field field = clazz.getDeclaredField(columnName); Method method = clazz.getMethod(methodName, field.getType()); method.invoke(object,value); } list.add((T)object); } } catch (Exception throwables) { throwables.printStackTrace(); } finally { C3P0Tools.release(connection, statement, resultSet); } return list; } ``` 5、可以传 SQL 参数的集合方法 ```java /** * 可以传SQL参数返回数据集合 * @param connection * @param sql * @param clazz * @param params * @return */ public List getBeans(Connection connection,String sql,Class clazz,Object... params){ PreparedStatement statement = null; ResultSet resultSet = null; List list = new ArrayList<>(); try { //查询SQL statement = connection.prepareStatement(sql); //替换参数 for (int i = 0; i < params.length; i++) { Object param = params[i]; String typeName = param.getClass().getTypeName(); switch (typeName){ case "java.lang.Integer": statement.setInt(i+1,(Integer) param); break; case "java.lang.String": statement.setString(i+1,(String) param); break; } } resultSet = statement.executeQuery(); //解析结果集 ResultSetMetaData metaData = resultSet.getMetaData(); int columnCount = metaData.getColumnCount(); while (resultSet.next()) { //映射成JavaBean，反射 Object object = clazz.getConstructor(null).newInstance(null); for (int i = 1; i <= columnCount; i++) { String columnName = metaData.getColumnName(i); String columnTypeName = metaData.getColumnTypeName(i); Object value = null; switch (columnTypeName){ case "INT": value = resultSet.getInt(columnName); break; case "VARCHAR": value = resultSet.getString(columnName); break; } //给属性赋值 //获取setter方法 String methodName = "set"+columnName.substring(0, 1).toUpperCase()+columnName.substring(1); Field field = clazz.getDeclaredField(columnName); Method method = clazz.getMethod(methodName, field.getType()); method.invoke(object,value); } list.add((T)object); } } catch (Exception throwables) { throwables.printStackTrace(); } finally { C3P0Tools.release(connection, statement, resultSet); } return list; } ``` 6、测试类 ```java package com.southwind.test; import com.southwind.entity.Account; import com.southwind.entity.Student; import com.southwind.utils.C3P0Tools; import com.southwind.utils.JDBCTools; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.List; public class Test { public static void main(String[] args) throws Exception { Connection connection = C3P0Tools.getConnection(); String sql = "select * from account"; JDBCTools jdbcTools = new JDBCTools<>(); List accounts = jdbcTools.getBeans(connection, sql, Account.class); for (Account account : accounts) { System.out.println(account); } } } ``` JDBCTools 完整代码 ```java package com.southwind.utils; import java.lang.reflect.Field; import java.lang.reflect.InvocationTargetException; import java.lang.reflect.Method; import java.sql.*; import java.util.ArrayList; import java.util.List; public class JDBCTools { /** * 可以传SQL参数返回数据集合 * @param connection * @param sql * @param clazz * @param params * @return */ public List getBeans(Connection connection,String sql,Class clazz,Object... params){ PreparedStatement statement = null; ResultSet resultSet = null; List list = new ArrayList<>(); try { //查询SQL statement = connection.prepareStatement(sql); //替换参数 fillParams(statement, params); //获取结果集 resultSet = statement.executeQuery(); //解析结果集 parseDataForList(resultSet, clazz, list); } catch (Exception throwables) { throwables.printStackTrace(); } finally { C3P0Tools.release(connection, statement, resultSet); } return list; } /** * 查询对象集合 * @param connection * @param sql * @param clazz * @return */ public List getBeans(Connection connection,String sql,Class clazz){ PreparedStatement statement = null; ResultSet resultSet = null; List list = new ArrayList<>(); try { //查询SQL statement = connection.prepareStatement(sql); //获取结果集 resultSet = statement.executeQuery(); //解析结果集 parseDataForList(resultSet, clazz, list); } catch (Exception throwables) { throwables.printStackTrace(); } finally { C3P0Tools.release(connection, statement, resultSet); } return list; } /** * 可以传SQL参数查询某个对象 * @param connection * @param sql * @param clazz * @param params * @return */ public T getBean(Connection connection,String sql,Class clazz,Object... params){ PreparedStatement statement = null; ResultSet resultSet = null; try { //查询SQL statement = connection.prepareStatement(sql); //替换参数 fillParams(statement, params); //获取结果集 resultSet = statement.executeQuery(); //映射成JavaBean，反射 Object object = clazz.getConstructor(null).newInstance(null); //解析结果集 parseDataForBean(resultSet, object); return (T)object; } catch (Exception throwables) { throwables.printStackTrace(); } finally { C3P0Tools.release(connection, statement, resultSet); } return null; } /** * 通过SQL查询某个对象 * @param connection * @param sql * @param clazz * @return */ public T getBean(Connection connection, String sql, Class clazz){ PreparedStatement statement = null; ResultSet resultSet = null; try { //查询SQL statement = connection.prepareStatement(sql); //获取结果集 resultSet = statement.executeQuery(); //映射成JavaBean，反射 Object object = clazz.getConstructor(null).newInstance(null); //解析结果集 parseDataForBean(resultSet, object); return (T)object; } catch (Exception throwables) { throwables.printStackTrace(); } finally { C3P0Tools.release(connection, statement, resultSet); } return null; } /** * 填充SQL参数 * @param statement * @param params */ public void fillParams(PreparedStatement statement,Object... params){ try { for (int i = 0; i < params.length; i++) { Object param = params[i]; String typeName = param.getClass().getTypeName(); switch (typeName){ case "java.lang.Integer": statement.setInt(i+1,(Integer) param); break; case "java.lang.String": statement.setString(i+1,(String) param); break; } } } catch (SQLException throwables) { throwables.printStackTrace(); } } /** * 解析结果集，返回数据集合 * @param resultSet * @param clazz * @param list */ public void parseDataForList(ResultSet resultSet,Class clazz,List list){ try { ResultSetMetaData metaData = resultSet.getMetaData(); int columnCount = metaData.getColumnCount(); while (resultSet.next()) { //映射成JavaBean，反射 Object object = clazz.getConstructor(null).newInstance(null); for (int i = 1; i <= columnCount; i++) { String columnName = metaData.getColumnName(i); String columnTypeName = metaData.getColumnTypeName(i); Object value = null; switch (columnTypeName){ case "INT": value = resultSet.getInt(columnName); break; case "VARCHAR": value = resultSet.getString(columnName); break; } //给属性赋值 //获取setter方法 String methodName = "set"+columnName.substring(0, 1).toUpperCase()+columnName.substring(1); Field field = clazz.getDeclaredField(columnName); Method method = clazz.getMethod(methodName, field.getType()); method.invoke(object,value); } list.add((T)object); } } catch (SQLException throwables) { throwables.printStackTrace(); } catch (InstantiationException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } catch (NoSuchMethodException e) { e.printStackTrace(); } catch (NoSuchFieldException e) { e.printStackTrace(); } } /** * 解析结果集，给对象赋值 * @param resultSet * @param object */ public void parseDataForBean(ResultSet resultSet,Object object){ try { ResultSetMetaData metaData = resultSet.getMetaData(); int columnCount = metaData.getColumnCount(); while (resultSet.next()) { for (int i = 1; i <= columnCount; i++) { String columnName = metaData.getColumnName(i); String columnTypeName = metaData.getColumnTypeName(i); Object value = null; switch (columnTypeName){ case "INT": value = resultSet.getInt(columnName); break; case "VARCHAR": value = resultSet.getString(columnName); break; } //给属性赋值 //获取setter方法 String methodName = "set"+columnName.substring(0, 1).toUpperCase()+columnName.substring(1); Field field = object.getClass().getDeclaredField(columnName); Method method = object.getClass().getMethod(methodName, field.getType()); method.invoke(object,value); } } } catch (SQLException throwables) { throwables.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } catch (NoSuchFieldException e){ e.printStackTrace(); } catch (SecurityException e){ e.printStackTrace(); } catch (NoSuchMethodException e){ e.printStackTrace(); } } } ```