JavaWeb博客管理系统02

JDBDUtils 工具类

package com.demo.utils;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;

import java.lang.reflect.Field;
import java.sql.*;

public class JDBDUtils {

    /**
     * @return java.sql.Connection
     * @Author lufeifan
     * @Description //数据库连接
     * @Date 10:19 10:19
     * @Param []
     **/

    public static Connection connection() {
        String driverName = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://127.0.0.1:3307/test";
        String username = "root";
        String password = "root";
        Connection connection = null;
        try {
            Class.forName(driverName);
            connection = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }

    /**
     * @return void
     * @Author lufeifan
     * @Description //通用增删改
     * @Date 10:19 10:19
     * @Param [sql, args]
     **/
    public static boolean update(String sql, Object... args) {
        Connection connection = connection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
//            遍历 args 数组
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }
//            成功返回 false；
            if (preparedStatement.executeUpdate() == 1){
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeResource(connection, preparedStatement);
        }
        return false;
    }

    /**
     * @return java.lang.String
     * @Author lufeifan
     * @Description //查询多条数据
     * @Date 2021/6/19 10:40
     * @Param * @param: sql
     * @param: clazz
     * @param: args
     **/
    public static <T> String queryList(String sql, Class<T> clazz, Object... args) {
        Connection connection = connection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        JSONArray jsonArray = new JSONArray();
        try {
            preparedStatement = connection.prepareStatement(sql);
//            遍历 args 数组
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }
            resultSet = preparedStatement.executeQuery();
//            ResultSet 元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
//           列数
            int columnCount = metaData.getColumnCount();

            while (resultSet.next()) {
                T t = clazz.newInstance();
                for (int i = 0; i < columnCount; i++) {
//                列名
                    String columnName = metaData.getColumnName(i + 1);
//                    处理下划线反射不对问题
                    columnName = CamelUnderlineUtil.underlineToCamel(columnName);
                    Object columnValue = resultSet.getObject(i + 1);
//                    反射
                    Field field = clazz.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(t, columnValue);
                }
                jsonArray.add(t);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeResource(connection, preparedStatement, resultSet);
        }
        return JSON.toJSONString(jsonArray);
    }

    /**
     * @Author lufeifan
     * @Description // 返回一条数据
     * @Date 2021/6/19 10:42
     * @Param * @param: sql
     * @param: clazz
     * @param: args
     * @return java.lang.String
     **/
    public static <T> String queryOne(String sql, Class<T> clazz, Object... args) {
        Connection connection = connection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        T t = null;
        try {
            t = clazz.newInstance();
            preparedStatement = connection.prepareStatement(sql);
//            遍历 args 数组
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }
            resultSet = preparedStatement.executeQuery();
//            ResultSet 元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
//           列数
            int columnCount = metaData.getColumnCount();

            if (resultSet.next()) {
                for (int i = 0; i < columnCount; i++) {
//                列名
                    String columnName = metaData.getColumnName(i + 1);
//                    处理下划线反射不对问题
                    columnName = CamelUnderlineUtil.underlineToCamel(columnName);
                    Object columnValue = resultSet.getObject(i + 1);
//                    反射
                    Field field = clazz.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(t, columnValue);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeResource(connection, preparedStatement, resultSet);
        }
        return JSON.toJSONString(t);
    }

    /**
     * @Author lufeifan
     * @Description // 数据库关闭
     * @Date 2021/6/19 10:42
     * @Param * @param: connection
     * @param: preparedStatement
     * @param: resultSet
     * @return void
     **/
    private static void closeResource(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet) {
        try {
            connection.close();
            preparedStatement.close();
            resultSet.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * @Author lufeifan
     * @Description // 数据库关闭
     * @Date 2021/6/19 10:43
     * @Param * @param: connection
     * @param: preparedStatement
     * @return void
     **/
    public static void closeResource(Connection connection, PreparedStatement preparedStatement) {
        try {
            connection.close();
            preparedStatement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

下划线处理类

package com.demo.utils;

import org.apache.commons.lang3.StringUtils;

public class CamelUnderlineUtil {
 
	private static final char UNDERLINE ='_';
	public static String camelToUnderline(String param) {
		if (StringUtils.isEmpty(param)) {
			return "";
		}
		StringBuilder sb = new StringBuilder();
		int len = param.length();
		for (int i = 0; i < len; i++) {
			char c = param.charAt(i);
			if (Character.isUpperCase(c)) {
				sb.append(UNDERLINE);
				sb.append(Character.toLowerCase(c));
			} else {
				sb.append(c);
			}
		}
		return sb.toString();
	}
	
	public static String underlineToCamel(String param){
		if (StringUtils.isEmpty(param)) {
			return "";
		}
		StringBuilder sb = new StringBuilder();
		int len = param.length();
		for (int i = 0; i < len; i++) {
			char c = param.charAt(i);
			if (c==UNDERLINE) {
				if(++i<len){
					sb.append(Character.toUpperCase(param.charAt(i)));
				}
			} else {
				sb.append(c);
			}
		}
		return sb.toString();
	}
}

测试

package com.demo.test;

import com.alibaba.fastjson.JSONArray;
import com.demo.entity.User;
import com.demo.utils.JDBDUtils;
import org.junit.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class JDBCTest {

//    数据库连接测试
    @Test
    public void test(){
        Connection connection = JDBDUtils.connection();
        String sql = "select * from user";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                System.out.println(resultSet.getString("username"));
            }
        } catch (Exception throwables) {

        }
    }

//    数据库更新测试
    @Test
    public void test01(){
        String sql = "UPDATE user SET username = ? WHERE id = ?";

        JDBDUtils.update(sql, "123213", 53);
    }

//    数据库查询测试
    @Test
    public void test02(){
        String sql = "select * from user where id = ?";
        String query = JDBDUtils.queryOne(sql, User.class,8);
        System.out.println(query);
    }

    @Test
    public void test03(){
        String sql = "select * from user";
        String query = JDBDUtils.queryList(sql, User.class);
        System.out.println(query);
    }
}