使用DBUtils写个通用CURD小工具
依赖配置
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion> <groupId>yofc</groupId>
<artifactId>jdbc</artifactId>
<version>1.0-SNAPSHOT</version> <dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.15</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.14</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-nop</artifactId>
<version>1.7.26</version>
</dependency>
<dependency>
<groupId>commons-dbutils</groupId>
<artifactId>commons-dbutils</artifactId>
<version>1.7</version>
</dependency>
</dependencies> <build>
<plugins>
<!-- 指定jdk -->
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>1.8</source>
<target>1.8</target>
</configuration>
</plugin>
</plugins>
</build>
</project>
druid.properties
username=root
password=root
url=jdbc:mysql://192.168.8.136:3306/jdbc
driverClassName=com.mysql.cj.jdbc.Driver
接口
package com.jdbc.dao; import java.sql.Connection;
import java.sql.SQLException;
import java.util.List; /**
* 访问数据的 DAO 接口
* @param <T> DAO 处理的实体类的类型
*/
public interface DAO<T> { /**
* 批量处理的方法
* @param args: 填充占位符的参数
*/
void batch(Connection connection, String sql, Object[]... args) throws SQLException; /**
* 返回具体的一个值,
*/
<E> E getForValue(Connection connection, String sql, Object... args) throws SQLException; /**
* 返回 T 的一个集合
*/
List<T> getForList(Connection connection, String sql, Object... args) throws SQLException; /**
* 返回一个 T 的对象
*/
T get(Connection connection, String sql, Object... args) throws SQLException; /**
* INSRET, UPDATE, DELETE 方法
* @param connection: 数据库连接
* @param sql: SQL 语句
* @param args: 填充占位符的可变参数
* @throws SQLException
*/
void update(Connection connection, String sql, Object... args) throws SQLException;
}
JDBC的实现
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List; import com.jdbc.dao.DAO;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler; /**
* 使用 QueryRunner 提供其具体的实现
* @param <T>: 子类需传入的泛型类型.
*/
public class JdbcDaoImpl<T> implements DAO<T> { private QueryRunner queryRunner = null;
private Class<T> type; /**
* 通过反射获得定义 Class 时声明的父类的泛型参数的类型
*/
public static Class getSuperClassGenricType(Class clazz, int index) {
Type genType = clazz.getGenericSuperclass();
if (!(genType instanceof ParameterizedType)) {
return Object.class;
}
Type[] params = ((ParameterizedType) genType).getActualTypeArguments();
if (index >= params.length || index < 0) {
return Object.class;
}
if (!(params[index] instanceof Class)) {
return Object.class;
}
return (Class) params[index];
} public JdbcDaoImpl() {
queryRunner = new QueryRunner();
type = getSuperClassGenricType(getClass(),0);
} @Override
public void batch(Connection connection, String sql, Object[]... args) throws SQLException {
queryRunner.batch(connection, sql, args);
} @Override
public <E> E getForValue(Connection connection, String sql, Object... args) throws SQLException {
return (E) queryRunner.query(connection, sql, new ScalarHandler(), args);
} @Override
public List<T> getForList(Connection connection, String sql, Object... args) throws SQLException {
return queryRunner.query(connection, sql, new BeanListHandler<>(type), args);
} @Override
public T get(Connection connection, String sql, Object... args) throws SQLException {
return queryRunner.query(connection, sql, new BeanHandler<>(type), args);
} @Override
public void update(Connection connection, String sql, Object... args) throws SQLException {
queryRunner.update(connection, sql, args);
}
}
数据连接池
package com.jdbc.utils; import com.alibaba.druid.pool.DruidDataSourceFactory; import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties; public class DruidDataPoolUtils { private static Properties properties;
private static DataSource dataSource; static {
try {
properties = new Properties();
properties.load(DruidDataPoolUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
dataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
} public static Connection getConnection() {
try {
return dataSource.getConnection();
} catch (Exception e) {
e.printStackTrace();
}
return null;
} /**
* 注意参数顺序
* 开始：ResultSet
* 中间：Statement、PreparedStatement、CallableStatement
* 最后：Connection
*
* @param closed
*/
public static void close(Object... closed) {
try {
for (Object object : closed) {
if (object instanceof ResultSet) {
((ResultSet) object).close();
} else if (object instanceof Statement) {
((Statement) object).close();
} else if (object instanceof Connection) {
((Connection) object).close();
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
测试
UserDao
public class UserDao extends JdbcDaoImpl<User> {
}
Main
public static void main(String[] args) throws SQLException {
UserDao userDao = new UserDao();
Connection connection = null;
try {
connection = DruidDataPoolUtils.getConnection();
String sql = "select * from user WHERE id = ?";
User user = userDao.get(connection, sql, 19);
System.out.println(user);
} catch (Exception e) {
e.printStackTrace();
} finally{
DruidDataPoolUtils.close(connection);
}
}
项目结构