java mysql JDBC数据库操作类

@航空母舰

已于 2024-08-05 13:48:15 修改

阅读量253

点赞数

文章标签：数据库 java mysql 1024程序员节

于 2023-08-14 17:34:34 首次发布

本文链接：https://blog.csdn.net/hudeyong926/article/details/132280774

版权

批处理

批处理就是一批一批的处理，只针对更新（增，删，改）语句，不包括查询。

对于mysql 默认是关闭批处理的，需要在连接url中添加参数

rewriteBatchedStatements=true

Statement批处理
多次调用statement类的addBatch(String sql)方法，将需要执行的所有SQL语句添加到“批中”，然后调用executeBatch()方法来执行当前“批”中的语句。

void addBatch(String sql)：添加一条语句到“批”中；
int[] executeBatch()：执行“批”中所有语句。返回值表示每条语句所影响的行数据；
void clearBatch()：清空“批”中的所有语句。

jdbc数据库操作类

package com.demo.system.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLTimeoutException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map.Entry;
import org.apache.commons.lang3.StringUtils;

public class MySQLConnector {
  public static int BATCH_ROW = 100000;

  private String url;
  private String username;
  private String password;

  public MySQLConnector(String url, String username, String password) {
    this.url = url;
    this.username = username;
    this.password = password;
  }

  public Connection getConnection() {
    Connection conn = null;
    try {
      conn = getConnectionWithRetry(url, username, password);
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return conn;
  }

  /**
   * 根据提供的参数信息，尝试获取数据库连接，支持重试
   *
   * @param url 数据库连接URL
   * @param username 数据库用户名
   * @param password 数据库密码
   * @return 数据库连接对象
   * @throws SQLException 如果无法建立数据库连接
   */
  private Connection getConnectionWithRetry(String url, String username, String password) throws SQLException {
    int maxAttempts = 3;
    for (int attempt = 1; attempt <= maxAttempts; attempt++) {
      try {
        return DriverManager.getConnection(url, username, password);
      } catch (SQLException e) {
        if (e instanceof SQLTimeoutException) {
          // 如果已达到最大重试次数，则抛出异常
          if (attempt == maxAttempts) {
            throw e;
          }
          // 输出重试信息
          System.out.println("Connection attempt " + attempt + " failed. Retrying...");
          try {
            // 等待5秒再尝试
            Thread.sleep(5000);
          } catch (InterruptedException ex) {
            // 如果线程被中断，重新设置中断状态
            Thread.currentThread().interrupt();
          }
        } else {
          throw e;
        }
      }
    }
    throw new IllegalStateException("This should never happen");
  }

  /**
   * insert
   *
   * @param tableName
   * @param columns
   * @param values
   * @return
   */
  public int insert(String tableName, String[] columns, String[] values) {
    StringBuilder sb = new StringBuilder();
    sb.append("INSERT INTO `").append(tableName).append("` (`");
    for (int i = 0; i < columns.length; i++) {
      sb.append(columns[i]);
      if (i < columns.length - 1) {
        sb.append("`, `");
      }
    }
    sb.append("`) VALUES (");
    for (int i = 0; i < values.length; i++) {
      sb.append("'").append(values[i]).append("'");
      if (i < values.length - 1) {
        sb.append(", ");
      }
    }
    sb.append(")");
    String sql = sb.toString();

    return execSql(sql);
  }

  /**
   * 删除操作
   *
   * @param tableName
   * @param where
   * @return
   */
  public int delete(String tableName, String where) {
    String sql = "DELETE FROM `" + tableName + "`";
    if (where != null && !where.isEmpty()) {
      sql = sql + " WHERE " + where;
    }
    return execSql(sql);
  }

  /**
   * update
   *
   * @param tableName
   * @param columns
   * @param values
   * @param where
   * @return
   */
  public int update(String tableName, String[] columns, String[] values, String where) {
    StringBuilder sb = new StringBuilder();
    sb.append("UPDATE `").append(tableName).append("` SET ");
    for (int i = 0; i < columns.length; i++) {
      sb.append(String.format("`%s`", columns[i])).append(" = '").append(values[i]).append("'");
      if (i < columns.length - 1) {
        sb.append(", ");
      }
    }
    if (where != null && !where.isEmpty()) {
      sb.append(" WHERE ").append(where);
    }
    String sql = sb.toString();

    return execSql(sql);
  }

  /**
   * 查询多条记录
   *
   * @param tableName
   * @param columns
   * @param where
   * @return
   */
  public LinkedHashMap<Integer, LinkedHashMap<String, String>> select(String tableName, String[] columns, String where) {
    StringBuilder sb = new StringBuilder();
    sb.append("SELECT ");
    if (columns != null && columns.length > 0) {
      for (int i = 0; i < columns.length; i++) {
        if (StringUtils.containsIgnoreCase(columns[i], "as")) {
          sb.append(String.format("%s", columns[i]));
        } else {
          sb.append(String.format("`%s`", columns[i]));
        }
        if (i < columns.length - 1) {
          sb.append(", ");
        }
      }
    } else {
      sb.append("*");
      columns = getAllFields(tableName); //将*替换成字段数组
    }
    sb.append(" FROM ").append(String.format("`%s`", tableName));
    if (where != null && !where.isEmpty()) {
      sb.append(" WHERE ").append(where);
    }
    String sql = sb.toString();
    System.out.println("执行sql:" + sql);

    ResultSet rs = querySql(sql);
    LinkedHashMap<Integer, LinkedHashMap<String, String>> result = new LinkedHashMap();
    try {
      while (rs.next()) {
        LinkedHashMap<String, String> row = new LinkedHashMap();
        for (int i = 0; i < columns.length; i++) {
          String key = columns[i];
          if (StringUtils.containsIgnoreCase(key, "as")) {
            key = StringUtils.replaceEach(key, new String[]{"As", "aS", "AS"}, new String[]{"as", "as", "as"});
            key = StringUtils.deleteWhitespace(key.split("as")[1]);
          }
          row.put(key, rs.getString(key));
        }
        result.put(rs.getRow(), row);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return result;
  }
  /**
   * 批量insert
   *
   * @param tableName
   * @param columns
   * @param values
   * @return
   */
  public int batchInsert(String tableName, LinkedHashMap<String, String> columns,
      HashMap<Integer, LinkedHashMap<String, String>> values) {
    StringBuilder sb = new StringBuilder();
    sb.append("INSERT INTO `" + tableName + "` (");
    columns.forEach((key, value) -> {
      sb.append(String.format("`%s`,", key));
    });
    sb.append(") VALUES (");
    columns.forEach((key, value) -> {
      sb.append("?,");
    });
    sb.append(")");
    String sql = sb.toString();
    sql = StringUtils.replace(sql, ",)", ")");
    try {
      Connection conn = new MySQLConnector(url, username, password).getConnection();
      PreparedStatement stmt = conn.prepareStatement(sql);

      int batchSize = 4000;
      int count = 0;
      for (Entry<Integer, LinkedHashMap<String, String>> rows : values.entrySet()) {
        int rowIndex = 1;
        for (Entry<String, String> entry : rows.getValue().entrySet()) {
          String key = entry.getKey();
          if (StringUtils.equalsIgnoreCase(columns.get(key), "int")) {
            stmt.setInt(rowIndex, Integer.parseInt(entry.getValue()));
          }
          if (StringUtils.equalsIgnoreCase(columns.get(key), "string")) {
            stmt.setString(rowIndex, entry.getValue());
          }
          rowIndex++;
        }

        stmt.addBatch();
        count++;
        if (count % batchSize == 0) {
          stmt.executeBatch();
          stmt.clearBatch();
          count = 0;
        }
      }
      if (count > 0) {
        stmt.executeBatch();
        stmt.clearBatch();
      }
      stmt.close(); //关闭数据的连接
    } catch (SQLException e) {
      e.printStackTrace();
    }

    return 1;
  }

  /**
   * 分步执行sql，解决慢查询
   *
   * @param table
   * @param sql
   * @return
   */
  public Boolean operatorTableDataByStep(String table, String sql) {
    int page = 1;
    String maxSql = "select max(id) as k from `" + table + "` limit 1";
    int maxId = 0;
    String max = selectOne(maxSql);
    if (StringUtils.isNotEmpty(max)) {
      maxId = Integer.parseInt(max);
    }
    Boolean result = false;
    int end = 0;
    do {
      int start = (page - 1) * BATCH_ROW;
      end = page * BATCH_ROW;
      String opertor = StringUtils.containsIgnoreCase(sql, "where") ? "and" : "where";
      String stepSql = sql + " " + opertor + " id>" + start + " and id<=" + end + ";";
      ++page;
      if (execSql(stepSql) > 0) {
        result = true;
      }
    } while (end < maxId);

    return result;
  }

  /**
   * 执行sql语句,返回数据集合
   *
   * @param sql
   * @return
   */
  public String selectOne(String sql) {
    String result = null;
    Connection conn = new MySQLConnector(url, username, password).getConnection();
    if (conn != null) {
      try {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        while (rs.next()) {
          result = rs.getString("k");
        }
        stmt.close(); //关闭数据的连接
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }

    return result;
  }

  /**
   * 执行sql语句，返回受影响的条数
   *
   * @param sql
   * @return
   */
  public int execSql(String sql) {
    int rs = 0;
    Connection conn = new MySQLConnector(url, username, password).getConnection();
    if (conn != null) {
      try {
        Statement stmt = conn.createStatement();
        System.out.println("执行sql:" + sql);
        rs = stmt.executeUpdate(sql);
        stmt.close(); //关闭数据的连接
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }

    return rs;
  }

  /**
   * 获取表的所有字段
   *
   * @param tableName
   * @return
   */
  private String[] getAllFields(String tableName) {
    String[] columns = null;
    ResultSet fields = querySql(String.format("SHOW COLUMNS FROM `%s`", tableName));
    try {
      List<String> list = new ArrayList();
      while (fields.next()) {
        list.add(fields.getString("Field"));
      }
      columns = list.toArray(new String[list.size()]);
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return columns;
  }

  /**
   * 执行sql语句,返回数据集合
   *
   * @param sql
   * @return
   */
  private ResultSet querySql(String sql) {
    ResultSet rs = null;
    Connection conn = new MySQLConnector(url, username, password).getConnection();
    if (conn != null) {
      try {
        Statement stmt = conn.createStatement();
        rs = stmt.executeQuery(sql);
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }

    return rs;
  }

  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/database_name";
    String username = "your_username";
    String password = "your_password";

    MySQLConnector connector = new MySQLConnector(url, username, password);

    connector.execSql("CREATE TABLE `table_name` (\n"
        + "  `id` int(11) NOT NULL AUTO_INCREMENT,\n"
        + "  `name` varchar(30) DEFAULT NULL,\n"
        + "  `age` int(11) DEFAULT NULL,\n"
        + "  PRIMARY KEY (`id`)\n"
        + ")");

    // 插入数据
    String[] columns = {"name", "age"};
    String[] values = {"John", "25"};
    connector.insert("table_name", columns, values);

    // 删除数据
    String condition = "age > 30";
    connector.delete("table_name", condition);

    // 更新数据
    String[] updateColumns = {"name", "age"};
    String[] updateValues = {"Jane", "28"};
    String updateCondition = "id = 1";
    connector.update("table_name", updateColumns, updateValues, updateCondition);

    // 查询数据
    String[] selectColumns = {"sum(money) as money", "age"};
    String selectCondition = "age < 30";
    LinkedHashMap<Integer, LinkedHashMap<String, String>> rows = connector
        .select("table_name", selectColumns, selectCondition);
    System.out.println(rows);

    // 批量插入数据
//    HashMap<Integer, LinkedHashMap<String, String>> values = new HashMap();
//    LinkedHashMap<String, String> row = new LinkedHashMap();
//    row.put("name", "Tom");
//    row.put("age", "23");
//    values.put(1, row);
//    LinkedHashMap<String, String> row2 = new LinkedHashMap();
//    row2.put("name", "afaf");
//    row2.put("age", "66");
//    values.put(2, row2);
//    connector.batchInsert("table_name", columns, values);
  }
}

使用mysql8 并兼容链接mysql5

修改maven的依赖包，由原来的5.x，修改成8.x，maven地址参考
链接URL修改，mysql8.x 必须要加上时区的配置，serverTimezone=Asia/Shanghai

常见错误

JDBC数据库链接：Communications link failure失败

mysql的版本是5.7之后的版本必须要加上useSSL=false,mysql5.7以及之前的版本则不用进行添加useSSL=false，会默认为false，一般情况下都是使用useSSL=false

java.sql.SQLException: Unknown system variable 'query_cache_size'

mysql-connector-java查看pom.xml文件后发现，mysql驱动程序的版本为5.1.38，Mysql数据库版本为8.0.15，故原因为mysql驱动程序与mysql数据库版本不匹配。

区别：

5.x 驱动com.mysql.jdbc.Driver
8.x 驱动com.mysql.cj.jdbc.Driver
8.x 需要增加时区的配置serverTimezone=UTC

Spring—JdbcTemplate

Spring 框架对 JDBC 进行封装，使用 JdbcTemplate 方便实现对数据库操作

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;

/**
 * jdbc数据源
 *
 */
@Configuration
public class DataSourceJdbcConfig {

  @Bean(name = "monitorDataSource")    //声明其为Bean实例
  @ConfigurationProperties(prefix = "spring.datasource.monitor")
  public DataSource tkDataSource() {
    DataSource dataSource = DataSourceBuilder.create()
        .type(com.alibaba.druid.pool.DruidDataSource.class)
        .build();
    return dataSource;
  }

  @Bean(name = "monitorJdbcTemplate")
  public JdbcTemplate manageJdbcTemplate(
      @Qualifier("monitorDataSource") DataSource dataSource) {
    return new JdbcTemplate(dataSource);
  }

}

asfdas

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class BookDaoImp implements BookDao {

  //按类型、自动注入了JdbcTemplate对象
  @Autowired
  @Qualifier("monitorJdbcTemplate")
  private JdbcTemplate jdbcTemplate;

  @Override
  public void add(Book book) {
    //创建添加的sql语句
    String addsql = "insert into Book values(?,?,?)";
    //调用jdbcTemplate.update实现添加，book.getUserid()、book.getUsername()、book.getUstatus()对应values(?,?,?)里的三个问号
    Object[] args = {book.getUserid(), book.getUsername(), book.getUstatus()};
    int update = jdbcTemplate.update(addsql, args);
    //返回值update代表添加了几行
    System.out.println(update);

    String sql = "create table sys_user(id int AUTO_INCREMENT,username varchar(255),password varchar(255),age int,PRIMARY KEY (id))ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_0900_ai_ci;";
    jdbcTemplate.execute(sql);

    String sql = "select * from user where id=?";
    User user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class),1);

    String sql = "select * from user where id>?";
    List<User> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class),1);

    String sql = "select * from user where id=?";
    Map<String, Object> map = jdbcTemplate.queryForMap(sql,1);
  }

  @Override
  public void update(Book book) {
    //根据id修改username ustatus
    String updatesql = "update Book set username=?,ustatus=?where user_id=?";
    Object[] args = {book.getUsername(), book.getUstatus(), book.getUserid(),};//注意参数顺序
    int update = jdbcTemplate.update(updatesql, args);
  }

  @Override
  public void delete(String id) {
    //根据user_id删除
    String deletesql = "delete from Book where user_id=?";
    int update = jdbcTemplate.update(deletesql, id);
  }

  @Override
  public void TestOneValue() {
    //count(内写字段就是count这个字段不为空的元素总数),如果是count(数字)也视为count(*)
    String sql = "select count(1) from users";
    //返回对象写法jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper(写入要映射的类),传的值(可不写))
    Long count = jdbcTemplate.queryForObject(sql, long.class);
    System.out.println(count);
  }

  @Override
  public void batchInsert() {
    String sql = "insert into users(real_name,age) values(?,?)";
    //准备数据并添加到List集合
    Object[] objects1 = {"小花", 30};
    Object[] objects2 = {"小叶", 30};
    Object[] objects3 = {"小瑞", 30};
    //添加到List集合
    List lists = new ArrayList();
    lists.add(objects1);
    lists.add(objects2);
    lists.add(objects3);
    int[] ints = jdbcTemplate.batchUpdate(sql, lists);
    System.out.println(Arrays.toString(ints));
  }

  @Override
  public void batchUpdate() {
    String sql = "update users set real_name = ?,age = ? where id = ?";
    //准备数据
    Object[] objects = {"占", 35, 3};
    Object[] objects1 = {"OP", 35, 4};
    Object[] objects2 = {"SB", 35, 5};
    //添加到List集合
    List<Object[]> list = new ArrayList<>();
    list.add(objects);
    list.add(objects1);
    list.add(objects2);
    //执行SQL语句
    int[] ints = jdbcTemplate.batchUpdate(sql, list);
    System.out.println(Arrays.toString(ints));
  }

  @Override
  public void batchDelete() {
    String sql = "delete from users where id = ?";
    //准备数据
    Object[] objects = {9};
    Object[] objects1 = {10};
    //添加到List集合
    List<Object[]> list = new ArrayList<>();
    list.add(objects);
    list.add(objects1);
    int[] ints = jdbcTemplate.batchUpdate(sql, list);
    System.out.println(Arrays.toString(ints));
  }
}