课堂封装的数据库操类(实现CRUD) |
package dao; import com.alibaba.druid.pool.DruidDataSourceFactory; import org.apache.commons.beanutils.BeanUtils; import javax.sql.DataSource; import java.io.InputStream; import java.lang.reflect.InvocationTargetException; import java.sql.*; import java.util.*; public class JdbcTools3 { public static Connection connection; public static DataSource dataSource; /** * 德鲁伊数据库连接池 */ static { try { InputStream inputStream = JdbcTools3.class.getClassLoader().getResourceAsStream("dridok.properties"); Properties properties = new Properties(); properties.load(inputStream); dataSource = DruidDataSourceFactory.createDataSource(properties); connection = dataSource.getConnection(); System.out.println("my-connection = " + connection); } catch (Exception e) { e.printStackTrace(); } } /** * 2.获取完整的预编译好的sql对象 * * @param sql 预编译的sql语句 * @param connection db链接对象 * @param parameters sql替换的参数 * @return 预编译好的sql对象 * @throws SQLException */ public static PreparedStatement getPreparedStatement(String sql, Connection connection, Object... parameters) { PreparedStatement statement = null; try { statement = connection.prepareStatement(sql); //1.获取元数据 ParameterMetaData parameterMetaData = statement.getParameterMetaData(); //2.获取字符串需要的参数个数 int count = parameterMetaData.getParameterCount(); //3.判断是否传参,并且设置参数值 if (count != 0 && parameters != null && parameters.length == count) for (int i = 0; i < count; i++) { statement.setObject(i + 1, parameters[i]); } } catch (SQLException throwables) { throwables.printStackTrace(); } return statement; } /** * 3.实现更新 * * @param sql * @param parameters * @return * @throws SQLException */ public static int update1(String sql, Object... parameters) { PreparedStatement preparedStatement = getPreparedStatement(sql, connection, parameters); int i = 0; try { i = preparedStatement.executeUpdate(); } catch (SQLException throwables) { throwables.printStackTrace(); } return i; } /** * 4. 关闭对象,释放资源 * * @param resources */ public static void closeAll(AutoCloseable... resources) { if (resources != null && resources.length > 0) { Arrays.stream(resources).forEach(source -> { if (source != null) { System.out.println("source = " + source); try { source.close(); } catch (Exception e) { e.printStackTrace(); } } }); } } /** * 5.根据条件查询一条数据,以传入的javaBean类型返回 null * * @param sql * @param cls * @param parameters * @param <T> * @return * @throws SQLException * @throws InstantiationException * @throws IllegalAccessException * @throws InvocationTargetException */ public static <T> T queryBean(String sql, Class<T> cls, Object... parameters) { //1.定义需要的变量 PreparedStatement preparedStatement = null; ResultSet resultSet = null; T t = null;//初始值 //2.获取链接 //3.获取sql的预编译对象 preparedStatement = JdbcTools3.getPreparedStatement(sql, connection, parameters); //4.执行查询,得到结果集 try { resultSet = preparedStatement.executeQuery(); //5.反编译对象,从结果集中获取表的元数据 ResultSetMetaData metaData = resultSet.getMetaData(); System.out.println("metaData = " + metaData); if (resultSet.next())//false { t = cls.newInstance();//实例化一个bean的空对象,用来封装数据 int count = metaData.getColumnCount(); for (int i = 1; i <= count; i++) { BeanUtils.setProperty(t, metaData.getColumnName(i), resultSet.getObject(i)); } // JdbcTools1.closeAll(connection, preparedStatement, resultSet); return t; } } catch (SQLException throwables) { throwables.printStackTrace(); } catch (InstantiationException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } return t; } /** * 6.根据条件查询多条数据 以集合的形式返回 * * @param sql String * @param cls Class<T> * @param parameters 替换函数 * @param <T> * @return * @throws SQLException * @throws InstantiationException * @throws IllegalAccessException * @throws InvocationTargetException */ public static <T> List<T> queryBeanList(String sql, Class<T> cls, Object... parameters) { //1.定义需要的变量 // Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; ArrayList<T> arrayList = new ArrayList<>(); T t = null; //2.获取链接 //3.获取sql的预编译对象 preparedStatement = JdbcTools3.getPreparedStatement(sql, connection, parameters); try { //4.执行查询,得到结果集 resultSet = preparedStatement.executeQuery(); //5.反编译对象,从结果集中获取表的元数据 ResultSetMetaData metaData = resultSet.getMetaData(); System.out.println("metaData = " + metaData); int count = metaData.getColumnCount(); while (resultSet.next()) { t = cls.newInstance();//实例化一个bean的空对象,用来封装数据 for (int i = 1; i <= count; i++) { BeanUtils.setProperty(t, metaData.getColumnName(i), resultSet.getObject(i)); } arrayList.add(t); } } catch (SQLException throwables) { throwables.printStackTrace(); } catch (InstantiationException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } JdbcTools1.closeAll(preparedStatement, resultSet); return arrayList; } /** * 7.查询多条记录,以Map集合的形式返回。 * * @param sql * @param parameters * @return * @throws SQLException */ public static ArrayList<Map> queryMapList(String sql, Object... parameters) { //1.定义需要的变量 Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; ArrayList<Map> maps = new ArrayList<>(); //2.获取链接 //3.获取sql的预编译对象 preparedStatement = JdbcTools3.getPreparedStatement(sql, connection, parameters); try { //4.执行查询,得到结果集 resultSet = preparedStatement.executeQuery(); //5.反编译对象,从结果集中获取表的元数据 ResultSetMetaData metaData = resultSet.getMetaData(); int count = metaData.getColumnCount(); while (resultSet.next()) { HashMap<String, Object> map = new HashMap<>(); for (int i = 1; i <= count; i++) { map.put(metaData.getColumnName(i), resultSet.getObject(i)); } maps.add(map); } } catch (SQLException throwables) { throwables.printStackTrace(); } JdbcTools1.closeAll(preparedStatement, resultSet); return maps; } /** * 8.查询一条记录,以数组形式返回 * * @param sql * @param parameters * @return Array * @throws SQLException */ public static Object[] queryArray(String sql, Object... parameters) { //1.定义需要的变量 Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; //2.获取链接 //3.获取sql的预编译对象 preparedStatement = JdbcTools3.getPreparedStatement(sql, connection, parameters); //4.执行查询,得到结果集 Object[] o = new Object[0];//定义长度为count的空数组 try { resultSet = preparedStatement.executeQuery(); //5.反编译对象,从结果集中获取表的元数据 ResultSetMetaData metaData = resultSet.getMetaData(); int count = metaData.getColumnCount(); o = new Object[count]; resultSet.next(); for (int i = 0; i < count; i++) { o[i] = resultSet.getObject(i + 1); } } catch (SQLException throwables) { throwables.printStackTrace(); } // JdbcTools1.closeAll(connection, preparedStatement, resultSet); return o; } /** * 9.查询多条记录,以数组集合(集合中的数据是数组)的数据状态返回 * * @param sql sql查询语句 * @param parameters 替换参数 * @return List * @throws SQLException */ public static List<Object[]> queryArrayList(String sql, Object... parameters) { //1.定义需要的变量 Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; List<Object[]> objects = new ArrayList<>(); //2.获取链接 //3.获取sql的预编译对象 preparedStatement = JdbcTools3.getPreparedStatement(sql, connection, parameters); try { //4.执行查询,得到结果集 resultSet = preparedStatement.executeQuery(); //5.反编译对象,从结果集中获取表的元数据 ResultSetMetaData metaData = resultSet.getMetaData(); int count = metaData.getColumnCount(); while (resultSet.next()) { Object[] o = new Object[count];//定义长度为count的空数组 for (int i = 0; i < count; i++) { o[i] = resultSet.getObject(i + 1); } objects.add(o); } } catch (SQLException throwables) { throwables.printStackTrace(); } // JdbcTools1.closeAll(connection, preparedStatement, resultSet); return objects; } /** * 10.查询一条记录,以键值对形式返回 * * @param sql * @param parameters * @return * @throws SQLException */ public static Map<String, Object> queryMap(String sql, Object... parameters) { //1.定义需要的变量 Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; //2.获取链接 //3.获取sql的预编译对象 preparedStatement = JdbcTools3.getPreparedStatement(sql, connection, parameters); HashMap<String, Object> map = null; try { //4.执行查询,得到结果集 resultSet = preparedStatement.executeQuery(); //5.反编译对象,从结果集中获取表的元数据 ResultSetMetaData metaData = resultSet.getMetaData(); int count = metaData.getColumnCount(); map = new HashMap<>(); while (resultSet.next()) { for (int i = 1; i <= count; i++) { map.put(metaData.getColumnName(i), resultSet.getObject(i)); } } } catch (SQLException throwables) { throwables.printStackTrace(); } JdbcTools1.closeAll(preparedStatement, resultSet); return map; } /** * 11.删除 * * @param sql * @param parameters * @return * @throws SQLException */ public static int del(String sql, Object... parameters) { PreparedStatement preparedStatement = getPreparedStatement(sql, connection, parameters); int i = 0; try { i = preparedStatement.executeUpdate(); } catch (SQLException throwables) { throwables.printStackTrace(); } return i; } } |
二、依赖配置文件
【pom.xml】
<dependencies>
<!-- servletApi的依赖-->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>4.0.1</version>
<scope>provided</scope>
</dependency>
<!-- mysql数据库的依赖-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.33</version>
</dependency>
<!-- 德鲁伊的依赖-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.23</version>
</dependency>
<dependency>
<groupId>commons-beanutils</groupId>
<artifactId>commons-beanutils</artifactId>
<version>1.9.4</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter-api</artifactId>
<version>5.7.2</version>
<scope>test</scope>
</dependency>
</dependencies>
三、【德鲁伊的配置文件】
driverClassName = com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3308/lulaoshi
username=root
password=12345678
#初始化时建立物理连接的个数
initialSize=10
#最大连接池数量
maxActive=20
#获取连接时最大等待时间
maxWait=10000