Java 连接数据库操作

上个月正好完成openTCS添加数据库支持这个小项目，今天有空，将自己写的java操作数据库的代码发表出来，如果觉得对大家有用就给个好评。多余的话不说了，上代码。

/**
 *
 * @author Li
 * @data 2023-04-02
 */
public class DataSourceUtil {
  

    private DataSourceUtil() {

    }

    public static DataSource getDruidDataSource() {
           DataSource dataSource =null;
           try {
              InputStream in = DataSourceUtil.class.getClassLoader().getResourceAsStream("db\\dataSource.properties");
               Properties pro = new Properties();
               pro.load(in);
               dataSource = DruidDataSourceFactory.createDataSource(pro);
            System.out.println( "get dataSource : "+ dataSource);
        } catch (Exception var1) {
            var1.printStackTrace();
        }
           return dataSource;
    }


/**
 *
 * @author Li
 * @data 2023-04-02
 */
@SuppressWarnings("unchecked")
public class JdbcTools {
  private static DataSource dataSource;
  private static ThreadLocal<Connection> threadLocal;
  private static PreparedStatement psmt;
  private static ResultSet rs;
  private static Connection conn;

  static {
    try {
      dataSource = DataSourceUtil.getDruidDataSource();
      threadLocal = new ThreadLocal<>();
    } catch (Exception var1) {
      var1.printStackTrace();
    }
  }

  public JdbcTools() {

  }
//获得连接
  protected static Connection getConn() {
    conn = threadLocal.get();
    if (conn == null) {
      //创建连接对象
      try {
        conn = dataSource.getConnection();
        System.out.println("打印出连接：" + conn);
        threadLocal.set(conn);
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
    return conn;
  }
 
  protected static void close(ResultSet rs, PreparedStatement psmt, Connection conn) {
    try {
      if (rs != null) {
        rs.close();
      }
      if (psmt != null) {
        psmt.close();
      }
      if (conn != null && !conn.isClosed()) {
        conn.close();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  //给预处理命令对象设置参数
  private static void setParams(PreparedStatement psmt, Object... params) throws SQLException {
    if (params != null && params.length > 0) {
      for (int i = 0; i < params.length; i++) {
        psmt.setObject(i + 1, params[i]);
      }
    }
  }
  /**
   *执行添加与更新操作
   */
  public static int executeUpdate(String sql, Object... params) {
    boolean insertFlag ;
    insertFlag = sql.trim().toUpperCase().startsWith("INSERT");
    try {
      conn = getConn();
      if (insertFlag) {
        psmt = conn.prepareStatement(sql,        Statement.RETURN_GENERATED_KEYS);
      } else {
        psmt = conn.prepareStatement(sql);
      }
      setParams(psmt, params);
      int count = psmt.executeUpdate();
      rs = psmt.getGeneratedKeys();
      if (rs.next()) {
        return ((Long) rs.getLong(1)).intValue();
      }
       System.out.println("data update success !! "+count);
      return count;
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return 0;
  }

  private static <T> void setValue(T obj, String property, Object propertyValue) {
    Class<?> clazz = obj.getClass();
    try {
      Field field = clazz.getDeclaredField(property);
      if (field != null) {
        field.setAccessible(true);
        field.set(obj, propertyValue);
      }
    } catch (NoSuchFieldException | IllegalAccessException e) {
      e.printStackTrace();
    }
  }

  public static Object[] executeCountQuery(String sql, Object... params) {
    try {
      conn = getConn();
      psmt = conn.prepareStatement(sql);
      setParams(psmt, params);
      rs = psmt.executeQuery();

      ResultSetMetaData rsmd = rs.getMetaData();

      int columnCount = rsmd.getColumnCount();
      Object[] columnValueArr = new Object[columnCount];

      if (rs.next()) {
        for (int i = 0; i < columnCount; i++) {
          Object columnValue = rs.getObject(i + 1);
          columnValueArr[i] = columnValue;
        }
        return columnValueArr;
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return null;
  }
/**
 *获取单行记录
 */
  public static <T> T getBean(String sql, Class<T> type, Object... params)  {
    T t;
    conn = getConn();
    t = beanHandler(conn, sql, type, params);
    return t;
  }
/**
 * 获取多行记录
 */
  public static <T> List<T> getBeanList(String sql, Class<T> type, Object... params) {
    List<T> list;
    conn = getConn();
    list = beansHandler(conn, sql, type, params);
    return list;
  }

  private static <T> T beanHandler(Connection conn, String sql, Class<T> type, Object... params) {
    T entity = null;
    try {
      psmt = conn.prepareStatement(sql);
      setParams(psmt, params);
      rs = psmt.executeQuery();

      ResultSetMetaData rsmd = rs.getMetaData();

      int columnCount = rsmd.getColumnCount();
      entity = type.getDeclaredConstructor().newInstance();
      if (rs.next()) {
        for (int i = 0; i < columnCount; i++) {
          String columnName = rsmd.getColumnName(i + 1);
          Object columnValue = rs.getObject(i + 1);
          setValue(entity, columnName, columnValue);
        }
      }
    } catch (SQLException | InstantiationException | IllegalAccessException |InvocationTargetException |NoSuchMethodException  e) {
      throw new RuntimeException(e);
    }
    return entity;
  }

  private static <T> List<T> beansHandler(Connection conn, String sql, Class<T> type, Object... params) {
    List<T> list = new ArrayList<>();
    try {
      psmt = conn.prepareStatement(sql);
      setParams(psmt, params);
      rs = psmt.executeQuery();

      ResultSetMetaData rsmd = rs.getMetaData();

      int columnCount = rsmd.getColumnCount();

      while (rs.next()) {
        T entity = type.getDeclaredConstructor().newInstance();
        for (int i = 0; i < columnCount; i++) {
          String columnName = rsmd.getColumnName(i + 1);
          Object columnValue = rs.getObject(i + 1);
          setValue(entity, columnName, columnValue);
        }
        list.add(entity);
      }
    } catch (SQLException | InstantiationException | IllegalAccessException |InvocationTargetException |NoSuchMethodException  e) {
      throw new RuntimeException(e);
    }
    return list;
  }
}