本文是在基于已经搞定对数据库连接的教程,当然没搞定也没事,下面JDBCUtils工具类也实现了MySQl8.0的数据库连接操作
获取数据库连接文章推荐阅读:JDBC:Java获取MySQL8.0数据库连接的五种方法—超全
注意事项:
- 以下关于增删改查的操作都是通用的,并不是针对于单个类实现
- 增删改操作由于没返回值所以归为一类,查询单独归为一类
- 若之前项目没有添加MySQL8.0驱动的jar包,需下载mysql-connector-java-8.* 具体版本号的jar包放入工程当中
- 具体驱动下载地址:https://dev.mysql.com/downloads/file/?id=477058
增、删、改(所有需要注意的点均在代码注释)
//通用增删改操作方法,只需传入sql语句,以及占位符参数
public static void update(String sql, Object ...args) {
Connection connection = null;
PreparedStatement ps = null;
try {
//1、获取连接(获取链接的JDBCUtils工具代码在后面已经给出)
connection = JDBCUtils.getConnection();
//2、预编译sql语句
ps = connection.prepareStatement(sql);
//3、填充占位符
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]); //注意要i+1
}
//4、执行操作
ps.execute();
} catch (SQLException e) {
e.printStackTrace();
} finally {
//5、资源关闭(具体工具类代码在后面已经给出)
JDBCUtils.closeResource(connection, ps);
}
}
查询(所有需要注意的点均在代码注释)
//通用的查询操作
public static <T> List<T> queryInstance(Class<T> clazz, String sql, Object ...args) {
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
List<T> list = new ArrayList<>();
try {
//获取连接
connection = JDBCUtils.getConnection();
//预编译sql语句
ps = connection.prepareStatement(sql);
//填充占位符
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
//执行查询返回结果集
rs = ps.executeQuery();
ResultSetMetaData metaData = rs.getMetaData();
//获取查询列数
int columnCount = metaData.getColumnCount();
while (rs.next()) {
T t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
//获取返回值
Object columnValue = rs.getObject(i + 1);
//获取该返回值的标签名
String columnLabel = metaData.getColumnLabel(i + 1);
//通过反射进行封装
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columnValue);
}
list.add(t);
}
return list;
} catch (Exception e) {
e.printStackTrace();
} finally {
//关闭资源
JDBCUtils.closeResource(connection, ps, rs);
}
return null;
}
测试语句
//增、删、改
@Test
public void test01() {
//更新
String sql = "update author set id = ? where id = ?";
update(sql, 10, 3);
//删除
sql = "delete from author where id = ?";
update(sql, 10);
//添加
sql = "insert into author values(?, ?, ?)";
update(sql, 3, "WYQ", "中国");
}
//查
@Test
public void test02() {
String sql = "select * from author";
List<Author> authors = queryInstance(Author.class, sql);//通过范型来判断返回值,因此第一个参数需传入要查询类的类型
if (authors != null) {
authors.forEach(System.out::println);
}
}
文章中所用的的JDBCUtils类
package com.atWYQ.preparedStatement;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
* @author WYQ
* @creat 2021-03-2021/3/20-10:40
*/
public class JDBCUtils {
//获取数据库连接
public static Connection getConnection() {
//1、读取配置文件的基本信息
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("com/atWYQ/connect/jdbc.properties");
Properties pros = new Properties();
try {
pros.load(is);
} catch (IOException e) {
e.printStackTrace();
}
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driver = pros.getProperty("driver");
//2、加载驱动
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
//3、获取链接
Connection connection = null;
try {
connection = DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
//关闭资源
public static void closeResource(Connection connection, PreparedStatement ps) {
try {
if(ps != null) {
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
//关闭资源
public static void closeResource(Connection connection, PreparedStatement ps, ResultSet rs) {
try {
if(ps != null) {
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
Properties配置文件
user=root
password=123456
url=jdbc:mysql://localhost:3306/test?useSSL=false&characterEncoding=utf-8&serverTimezone=Hongkong
driver=com.mysql.cj.jdbc.Driver
注意:MySQL8.0的driver和url与5.*版本有很大不同
测试所用到的数据库码源
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for author
-- ----------------------------
DROP TABLE IF EXISTS `author`;
CREATE TABLE `author` (
`id` int NULL DEFAULT NULL,
`au_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`nation` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of author
-- ----------------------------
INSERT INTO `author` VALUES (1, '春上椿树', '日本');
INSERT INTO `author` VALUES (2, '莫言', '中国');
INSERT INTO `author` VALUES (3, 'WYQ', '中国');
SET FOREIGN_KEY_CHECKS = 1;
如有什么不懂得,或者遇到什么异常,欢迎评论讨论