@Slf4j
public class MysqlUtil {
public static Connection connection(Zen zen, String database) {
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(zen.getMysql().getHost() + "/" + database, zen.getMysql().getUser(), zen.getMysql().getPwd());
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
/**
* 1、清空 数据库数据
*/
public static void truncateTable(Zen zen, String database, List<String> tables) {
Connection conn = connection(zen, database);
ResultSet rs = null;
Statement statement = null;
Statement statement2 = null;
try {
statement = conn.createStatement();
rs = statement.executeQuery("show tables");
while (rs.next()) {
String tableName = rs.getString("Tables_in_" + database);
if (!tables.contains(tableName)) {
String sql = "TRUNCATE " + tableName;
log.info(sql);
statement2 = conn.createStatement();
statement2.execute(sql);
}
}
rs.close();
statement.close();
statement2.close();
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
public static void execute(Zen zen, String database, List<String> tables) {
Connection conn = connection(zen, database);
ResultSet rs = null;
Statement statement = null;
Statement statement2 = null;
try {
statement = conn.createStatement();
rs = statement.executeQuery("show tables");
while (rs.next()) {
String tableName = rs.getString("Tables_in_" + database);
if (!tables.contains(tableName)) {
String sql = "TRUNCATE " + tableName;
log.info(sql);
statement2 = conn.createStatement();
statement2.execute(sql);
}
}
rs.close();
statement.close();
statement2.close();
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
//修改操作
public static void update(Zen zen, String database, String sql) {
Connection conn = connection(zen, database);
PreparedStatement statement = null;
try {
statement = conn.prepareStatement(sql);
//执行SQL
int ret = statement.executeUpdate();
//回收资源
statement.close();
conn.close();
} catch (Exception e) {
throw new RuntimeException(e);
}
}
//修改操作
public static String select(Zen zen, String database, String sql) throws Exception {
Connection conn = connection(zen, database);
PreparedStatement statement = null;
ResultSet resultSet = null;
statement = conn.prepareStatement(sql);
resultSet = statement.executeQuery();
String name = "";
//遍历结果集合,结果集合非常类似于迭代器
while (resultSet.next()) {
// name = resultSet.getString("name");
name = resultSet.getString(1);
}
//释放资源
resultSet.close();
statement.close();
conn.close();
return name;
}
}
pom.xml文件
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.24</version>
</dependency>