1. 对 jdbc 做一个简单的封装, select 可以返回一个 javabean 对象, 而不是 resultset. 主要用了反射. 这是我之前写的代码, 做了简单的修改.
实现功能: a. 对数据库的基本操作 增删改查
b. 对查询的单条记录返回一个指定类型的 javabean 对象, 利用 java 反射, jdbc ResultSet 类和 ResultSetMetaData 类
c. 对查到的结果集返回一个 List, 泛型
数据源: 用到的 数据库连接池是我自己简单实现的一个连接池:[java] 简单实现数据库连接池, 主要为了后续实现事务的简单实现
用到的 java 知识 : 反射, 泛型, jdbc
- import com.yeyeck.noob.ConnectionPollImpl;
- import com.yeyeck.noob.IConnectionPool;
- 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 DBUtil {
- public static int excuteUpdate(String sql, Object... objects) {
- IConnectionPool connectionPool = ConnectionPollImpl.getInstance();
- Connection connection = connectionPool.getConnection();
- PreparedStatement preparedStatement = null;
- try {
- preparedStatement = getStateMent(connection, sql, objects);
- return preparedStatement.executeUpdate(); // 执行 sql 并返回结果
- } catch (SQLException e) {
- e.printStackTrace();
- } finally {
- if (preparedStatement != null) {
- try {
- preparedStatement.close();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
- }
- return 0;
- }
- /**
- * 查询单条记录
- *
- * @param sql 查询语句
- * @param clazz 返回对象的 class
- * @param objects 需要的参数, 必须跟 sql 占位符的位置一一对应
- * @param <T> 泛型返回
- * @return 返回单个对象
- */
- public static <T> T queryForObject(String sql, Class<T> clazz, Object... objects) {
- IConnectionPool connectionPool = ConnectionPollImpl.getInstance();
- Connection connection = connectionPool.getConnection();
- PreparedStatement preparedStatement = null;
- ResultSet resultSet = null;
- T object = null;
- try {
- preparedStatement = getStateMent(connection, sql, objects);
- resultSet = getResultSet(preparedStatement);
- if (resultSet.next()) {
- object = invokeObject(resultSet, clazz);
- }
- } catch (SQLException | IllegalAccessException | InstantiationException
- | NoSuchFieldException | NoSuchMethodException | InvocationTargetException e) {
- e.printStackTrace();
- } finally {
- close(preparedStatement, resultSet); // 记得关闭
- }
- return object;
- }
- /**
- * 查询多条记录
- *
- * @param sql 查询语句
- * @param clazz 返回对象的 class
- * @param objects 需要的参数, 必须跟 sql 占位符的位置一一对应
- * @param <T> 泛型返回
- *
- * @return list
- */
- public static <T> List<T> queryForList(String sql, Class<T> clazz, Object... objects) {
- IConnectionPool connectionPool = ConnectionPollImpl.getInstance();
- Connection connection = connectionPool.getConnection();
- PreparedStatement preparedStatement = null;
- ResultSet resultSet = null;
- List<T> list = new ArrayList<>();
- try {
- preparedStatement = getStateMent(connection, sql, objects);
- resultSet = getResultSet(preparedStatement);
- while (resultSet.next()) {
- // 调用 invokeObject 方法, 把一条记录封装成一个对象, 添加到 list 中
- list.add(invokeObject(resultSet, clazz));
- }
- } catch (SQLException | IllegalAccessException | InstantiationException
- | NoSuchFieldException | NoSuchMethodException | InvocationTargetException e) {
- e.printStackTrace();
- } finally {
- close(preparedStatement, resultSet);
- }
- return list.size()> 0 ? list : null;
- }
- private static void close(PreparedStatement preparedStatement, ResultSet resultSet) {
- try {
- if(resultSet != null) {
- resultSet.close();
- }
- if (preparedStatement != null) {
- preparedStatement.close();
- }
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
- /**
- * 把数据库中的一条记录通过反射包装成相应的 Bean
- * @param resultSet
- * @param clazz
- * @param <T>
- * @return
- * @throws IllegalAccessException
- * @throws InstantiationException
- * @throws SQLException
- * @throws NoSuchFieldException
- * @throws NoSuchMethodException
- * @throws InvocationTargetException
- */
- private static <T> T invokeObject(ResultSet resultSet, Class<T> clazz) throws IllegalAccessException, InstantiationException,
- SQLException, NoSuchFieldException, NoSuchMethodException, InvocationTargetException {
- T object = clazz.getDeclaredConstructor().newInstance();
- ResultSetMetaData metaData = resultSet.getMetaData();
- for (int i = 0, count = metaData.getColumnCount(); i <count; i++) {
- String columnName = metaData.getColumnName(i + 1); // 数据库返回结果的列名
- String fieldName = StringUtil.camelName(columnName); // 去掉列名中的下划线 "_" 并转为驼峰命名
- Field field = clazz.getDeclaredField(fieldName); // 根据字段名获取 field
- String methName = setMethodName(fieldName); // 拼 set 方法名
- Class type = field.getType(); // 获取字段类型
- Method setMethod = clazz.getDeclaredMethod(methName, field.getType());
- Object value = resultSet.getObject(i + 1); // 获取字段值
- setMethod.invoke(object, type.cast(value)); // 强转并且赋值
- }
- return object;
- }
- private static PreparedStatement getStateMent(Connection connection, String sql, Object... objects) throws SQLException {
- PreparedStatement preparedStatement = connection.prepareStatement(sql);
- for (int i = 0, len = objects.length; i < len; i++) {
- preparedStatement.setObject(i + 1, objects[i]); // 给 sql 每个? 占位符填上数据
- }
- return preparedStatement;
- }
- private static ResultSet getResultSet(PreparedStatement statement) throws SQLException {
- if (statement == null) {
- return null;
- } else {
- return statement.executeQuery();
- }
- }
- private static String setMethodName(String str) {
- return "set" + StringUtil.firstUpperCase(str);
- }
- }
其中用到的 StringUtil 的几个方法
- public class StringUtil {
- /**
- * 转为驼峰命名
- * @param str
- * @return string
- */
- public static String camelName(String str) {
- if (!isEmpty(str)) {
- StringBuilder stringBuilder = new StringBuilder();
- for (int i = 0, len = str.length(); i < len; i++) {
- if (str.charAt(i) == '_') {
- while (str.charAt(i + 1) == '_') {
- i++;
- }
- stringBuilder.append(("" + str.charAt(++i)).toUpperCase());
- } else {
- stringBuilder.append(str.charAt(i));
- }
- }
- return stringBuilder.toString();
- }
- return str;
- }
- /**
- * 判断是否为空串
- *
- * @param str
- * @return
- */
- public static boolean isBlank(String str) {
- if (str != null && str.length()> 0) {
- for (int i = 0, len = str.length(); i <len; i++) {
- if (!Character.isSpaceChar(str.charAt(i))) {
- return false;
- }
- }
- }
- return true;
- }
- /**
- * 判断是否为空串 ?!!! 我怎么又写了个一样的方法?!!!
- * @param str
- * @return
- */
- public static boolean isEmpty(String str) {
- return str == null || str.length() == 0;
- }
- /**
- * 将第一个字母替换为大写
- * @param str
- * @return
- */
- public static String firstUpperCase(String str) {
- return str.substring(0, 1).toUpperCase() + str.substring(1, str.length());
- }
- }
测试
1. 测试用到的表
- CREATE TABLE `t_user` (
- `id` int(11) NOT NULL AUTO_INCREMENT,
- `username` varchar(50) DEFAULT NULL,
- `age` int(11) NOT NULL,
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
初始数据
测试代码
- import java.util.List;
- public class DBUtilTest {
- public static void main(String[] args) {
- List<User> users1 = DBUtil.queryForList("select * from t_user", User.class);
- System.out.println(users1);
- User user = new User();
- user.setUsername("刘能");
- user.setAge(12);
- String sql1 = "insert into `t_user`(username, age) values (?, ?)";
- System.out.println("insert 语句测试返回结果:" + DBUtil.excuteUpdate(sql1, user.getUsername(), user.getAge()));
- List<User> users2 = DBUtil.queryForList("select * from t_user", User.class);
- System.out.println(users2);
- String sql2 = "delete from t_user where id = ?";
- System.out.println("delete 语句测试返回结果:" + DBUtil.excuteUpdate(sql2, 3));
- String sql3 = "update t_user set age = ? where id = ?";
- System.out.println("update 语句测试返回结果:" + DBUtil.excuteUpdate(sql3, 100, 1));
- String sql4 = "select * from t_user where id = ?";
- User user2 = DBUtil.queryForObject(sql4, User.class, 2);
- System.out.println("select 语句测试返回结果:" + user2 );
- List<User> users3 = DBUtil.queryForList("select * from t_user", User.class);
- System.out.println(users3);
- }
- }
测试结果
[User{id=1, username='小明', age=100}, User{id=2, username='小红', age=15}, User{id=4, username='小二', age=12}, User{id=5, username='刘能', age=12}]
insert 语句测试返回结果: 1
[User{id=1, username='小明', age=100}, User{id=2, username='小红', age=15}, User{id=4, username='小二', age=12}, User{id=5, username='刘能', age=12}, User{id=6, username='二蛋', age=99}]
delete 语句测试返回结果: 1
update 语句测试返回结果: 1
select 语句测试返回结果: User{id=2, username='小红', age=15}
[User{id=1, username='小明', age=20}, User{id=2, username='小红', age=15}, User{id=4, username='小二', age=12}, User{id=6, username='二蛋', age=99}]
数据库数据
来源: https://www.cnblogs.com/yeyeck/p/9532688.html