jdbc连接MySQL的Demo
pom文件
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>xyz.jangle</groupId>
<artifactId>jdbc</artifactId>
<version>0.0.1-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.22</version>
</dependency>
</dependencies>
<build>
<sourceDirectory>src\main\java</sourceDirectory>
<plugins>
<plugin>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.7.0</version>
<configuration>
<source>11</source>
<target>11</target>
</configuration>
</plugin>
</plugins>
</build>
</project>
Java代码
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
/**
* jdbc 连接 MySQL DEMO
* @author jangle
* @email jangle@jangle.xyz
* @time 2020年11月3日 上午8:35:37
*
*/
public class Main {
public static void main(String[] args) throws Exception {
// 加载MySQL驱动
Class.forName("com.mysql.cj.jdbc.Driver");
String password = "1234567";
String user = "jangle";
String url = "jdbc:mysql://mysql.jangle.xyz:3090/demon?characterEncoding=UTF8";
Connection connection = DriverManager.getConnection(url, user, password);
String sql ="select * from bs_test where id = ?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setInt(1, 10);
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
System.out.println("id:"+resultSet.getLong("id"));
}
}
}
更新记录statement(创建时不需要提供SQL,在执行的时候提供SQL语句):
Connection conn = null;
Statement stmt = null;
// 建立连接
conn = dataSource.getConnection();
// conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "username", "password");
// 禁用自动提交
conn.setAutoCommit(false);
// 创建Statement
stmt = conn.createStatement();
LocalDate today = LocalDate.now(); // 获取当前日期
LocalDate ago = today.minusMonths(3); // 当前日期减去三个月
// 执行一系列数据库操作
stmt.executeUpdate("INSERT INTO plc_zj_his SELECT * FROM plc_zj WHERE create_time < '"+ago+" 00:00:00' ");
stmt.executeUpdate("DELETE FROM plc_zj WHERE create_time < '"+ago+" 00:00:00' ");
stmt.executeUpdate("INSERT INTO plc_1672_his SELECT * FROM plc_1672 WHERE create_time < '"+ago+" 00:00:00' ");
stmt.executeUpdate("DELETE FROM plc_1672 WHERE create_time < '"+ago+" 00:00:00' ");
// 提交事务
conn.commit();
更新记录prepareStatement(创建时必须提供SQL进行初始化):
PreparedStatement ps3 = connection.prepareStatement("update plc_baobiao_error set age = 0 where name = ?");
ps3.setString(1, ip);
ps3.executeUpdate();