JDBC
1.连接配置
首先引入数据库驱动：
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.26</version>
</dependency>
以mysql8.0版本为例，数据库连接程序编写如下：
@Test
public void test() throws ClassNotFoundException, SQLException, IOException {
// 读取配置文件中信息(读取类路径下配置)
InputStream is = TestClass.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties properties = new Properties();
properties.load(is);
System.out.println(properties);
System.out.println(properties.getProperty("driverClass"));
// driverClass=com.mysql.cj.jdbc.Driver
// url=jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=GMT%2B8，但是更推荐使用jdbc:mysql://localhost:3306?useSSL=false&characterEncoding=UTF8&serverTimezone=Asia/Shanghai
Class.forName(properties.getProperty("driverClass"));
String url = properties.getProperty("url");
String username = properties.getProperty("username");
String password = properties.getProperty("password");
Connection connection = DriverManager.getConnection(url, username, password);
System.out.println(connection);
}
Driver在静态代码块中有所配置，当加载此类时，会自动进行注册
2.操作数据库
2.1.新增操作
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
// 读取配置文件中信息(读取类路径下配置)
InputStream is = TestClass.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties properties = new Properties();
properties.load(is);
System.out.println(properties);
System.out.println(properties.getProperty("driverClass"));
// driverClass=com.mysql.cj.jdbc.Driver
// url=jdbc:mysql://localhost:3306/test?useSSL=false&serverTimeZone=UTC
Class.forName(properties.getProperty("driverClass"));
String url = properties.getProperty("url");
String username = properties.getProperty("username");
String password = properties.getProperty("password");
connection = DriverManager.getConnection(url, username, password);
System.out.println(connection);
String sql = "insert into book(bookName,state,date) values(?,?,?)";
Scanner scanner = new Scanner(System.in);
System.out.println("状态:");
String state = scanner.nextLine();
System.out.println("书名:");
String bookName = scanner.nextLine();
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, bookName.trim());
preparedStatement.setString(2, state.trim());
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
java.util.Date date = simpleDateFormat.parse("2021-01-01 12:32:01");
// 此种方式只能存入日期，不能存入事件，如果数据库对应字段为datetime，则时间会变为`00:00:00`
preparedStatement.setDate(3, new Date(date.getTime()));
preparedStatement.execute();
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} catch (ParseException e) {
e.printStackTrace();
} finally {
// 关闭资源
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
注意，占位符的索引从1开始
在idea中使用ctrl + alt + T即可快速套用try...catch
如果希望存入datetime，可以用下面的方式：
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
LocalDateTime localDateTime = LocalDateTime.parse("2022-03-28 20:55:00", formatter);
// 或者直接将new Date().getTime()作为参数传入
preparedStatement.setTimestamp(3, new Timestamp(localDateTime.toEpochSecond(ZoneOffset.ofHours(8)) * 1000));
2.2.通用操作封装
public class JDBCUtils {
private static Connection connection = null;
public static Connection getConnection() {
if (connection == null) {
synchronized (JDBCUtils.class){
if (connection == null) {
try {
InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties properties = new Properties();
properties.load(is);
Class.forName(properties.getProperty("driverClass"));
String url = properties.getProperty("url");
String username = properties.getProperty("username");
String password = properties.getProperty("password");
connection = DriverManager.getConnection(url, username, password);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
return connection;
}
// 通用的增删改操作
public static void update(String sql, Object... params) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
preparedStatement.setObject(i + 1, params[i]);
}
preparedStatement.execute();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtils.closeResources(preparedStatement, connection);
}
}
public static void closeResources(PreparedStatement preparedStatement, Connection connection) {
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
public static void closeResources(ResultSet resultSet, PreparedStatement preparedStatement, Connection connection) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
2.3.查询操作
public void test() {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtils.getConnection();
String sql = "select * from book where bookId = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, 10);
resultSet = preparedStatement.executeQuery();
// 获取结果集的元数据
ResultSetMetaData metaData = resultSet.getMetaData();
// 获取结果集中的列数
int columnsCount = metaData.getColumnCount();
while (resultSet.next()) {
// 判断结果集的下一条是否有数据，如果有则返回true，且指针下移，没有则返回false且指针不会下移
Book book = new Book();
for (int i = 0; i < columnsCount; i++) {
String columnName = metaData.getColumnName(i + 1);
Field field = Book.class.getDeclaredField(columnName);
field.setAccessible(true);
field.set(book, resultSet.getObject(i + 1));
}
System.out.println(book);
}
} catch (SQLException | NoSuchFieldException | IllegalAccessException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtils.closeResources(resultSet, preparedStatement, connection);
}
}
注：getColumnName只能获取数据库表中字段名，即使在sql中配置别名，也获取不到别名，而使用getColumnLabel则可以获取sql中编写的别名
preparedStatement.execute()方法在进行查询时返回true，在进行增删改时返回false
而preparedStatement.executeUpdate()，将返回被改变的行数（注意是空参函数）
3.操作Blob类型
MySQL中四种Blob类型：
|类型|大小（字节：单位）|
|TinyBlob|最大255|
|Blob|最大65k|
|MediumBlob|最大16M|
|LongBlob|最大4G|
插入时需要使用输入流：
FileInputStream fis = new FileInputStream(new File("src/a.mp4"));
preparedStatement.setObject(2, fis);
获取时得到输出流：
Blob blob = resultSet.getBlob(3);
InputStream binaryStream = blob.getBinaryStream();
FileOutputStream fos = new FileOutputStream("a1 .mp4");
byte[] bytes = new byte[1024];
int len = -1;
while ((len = binaryStream.read(bytes)) != -1) {
fos.write(bytes, 0, len);
}
有时上传Blob大小有限制，可以在配置文件中添加：max_allowed_packet=16M
4.批量操作：
addBatch、executeBatch、clearBatch
Connection connection = JDBCUtils.getConnection();
connection.setAutoCommit(false);
String sql = "insert into book(bookName) values(?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i <= 20000; i++) {
preparedStatement.setObject(1, "name_" + i);
preparedStatement.addBatch();
if (i % 500 == 0) {
// 批量执行
preparedStatement.executeBatch();
// 清空batch
preparedStatement.clearBatch();
}
}
connection.commit();
JDBCUtils.closeResources(preparedStatement, connection);
MySQL默认不支持batch，需要通过一个参数，开启批处理的支持：在配置文件的url后，添加?rewriteBatchedStatements=true;
同时无需开启自动提交，最后一起提交即可，能够提升效率（因为每500条存入到数据库还需要提交，花费一定时间）
浙公网安备 33010602011771号