JDBC连接数据库步骤(超详细)
目录
什么是JDBC
连接步骤
增删改查
环境准备
statement方式
preparedStatement方式
获取主键
批量插入
MySQL事务
Druid数据库连接池使用
手写数据库连接工具类
JDBCUtils工具类
通用DAO类封装
博主致力于将企业开发模式运用于项目学习中，实现点-线-面完整学习知识。商务合作|源码获取|学习交流私聊我。
运行环境
jdk 1.8
maven 3.3.9
idea 2020.1
mysql 5.7.32
什么是JDBC
JDBC(Java DataBase Connectivity,Java数据库连接技术)是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成。简单说通过JDBC可以实现数据库的增删改查。
连接步骤
注册驱动
获取连接
执行sql
解析结果
关闭资源
增删改查
环境准备
创建表
CREATE TABLE `account` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(255) COLLATE utf8mb4_bin DEFAULT '' COMMENT '姓名',
`password` varchar(255) COLLATE utf8mb4_bin DEFAULT '' COMMENT '密码',
`balance` bigint(255) DEFAULT '0' COMMENT '余额',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
idea新建项目
引入依赖
statement方式
查询
public class StatementTest {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//输入url、用户名、密码创建连接
//jdbc:mysql:是固定的写法，后面跟主机名localhost，3306是默认的MySQL端口号demo是数据库名称
//useUnicode=true是指是否使用Unicode字符集，赋值为true
//serverTimezone=UTC是指定时区时间为世界统一时间
//characterEncoding=utf-8是指定字符编码格式为UTF8
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai", "root", "123456");
//sql查询语句
String sql = "select * from account where name='" + "zhangsan" + "' and password = '" + "123" + "';";
Statement statement = connection.createStatement();
//执行查询语句
ResultSet resultSet = statement.executeQuery(sql);
//结果遍历
while (resultSet.next()) {
Object id = resultSet.getObject("id");
Object name = resultSet.getObject("name");
Object password = resultSet.getObject("password");
Object balance = resultSet.getObject("balance");
System.out.println(id + "-" + name + "-" + password + "-" + balance);
}
//关闭资源
resultSet.close();
statement.close();
connection.close();
}
}
控制台输出结果如下
statement方式存在sql注入问题，所以日常使用都preparedStatement方式
sql注入问题如下，通过拼接sql的or条件查询出了所有数据
preparedStatement方式
查询
public class PreparedStatementTest {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//输入url、用户名、密码创建连接
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai", "root", "123456");
//sql查询语句,?为占位符
String sql = "select * from account where name = ? and password = ? ;";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//设置占位符参数
preparedStatement.setObject(1, "zhangsan");
preparedStatement.setObject(2, "123");
//执行查询语句
ResultSet resultSet = preparedStatement.executeQuery();
//结果遍历
System.out.println("查询结果");
while (resultSet.next()) {
Object id = resultSet.getObject("id");
Object name = resultSet.getObject("name");
Object password = resultSet.getObject("password");
Object balance = resultSet.getObject("balance");
System.out.println(id + "-" + name + "-" + password + "-" + balance);
}
//关闭资源
resultSet.close();
preparedStatement.close();
connection.close();
}
}
控制台输出如下
查询结果
1-zhangsan-123-200
增加
public class PreparedStatementTest {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//输入url、用户名、密码创建连接
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai", "root", "123456");
//sql查询语句,?为占位符
String sql = "insert into account(name, password, balance) values(?, ?, ?) ;";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//设置占位符参数
preparedStatement.setObject(1, "wangwu");
preparedStatement.setObject(2, "123");
preparedStatement.setObject(3, "200");
//执行查询语句
int i = preparedStatement.executeUpdate();
//结果遍历
System.out.println("执行结果：" + i);
//关闭资源
preparedStatement.close();
connection.close();
}
}
控制台输出如下
执行结果：1
数据库结果
修改
public class PreparedStatementTest {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//输入url、用户名、密码创建连接
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai", "root", "123456");
//sql查询语句,?为占位符
String sql = "update account set balance = ? where id = ? ;";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//设置占位符参数
preparedStatement.setObject(1, 300);
preparedStatement.setObject(2, 3);
//执行查询语句
int i = preparedStatement.executeUpdate();
//结果遍历
System.out.println("执行结果：" + i);
//关闭资源
preparedStatement.close();
connection.close();
}
}
控制台输出如下
执行结果：1
删除
public class PreparedStatementTest {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//输入url、用户名、密码创建连接
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai", "root", "123456");
//sql查询语句,?为占位符
String sql = "delete from account where id = ? ;";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//设置占位符参数
preparedStatement.setObject(1, 3);
//执行查询语句
int i = preparedStatement.executeUpdate();
//结果遍历
System.out.println("执行结果：" + i);
//关闭资源
preparedStatement.close();
connection.close();
}
}
控制台输出如下
执行结果：1
获取主键
在实际开发中，新增操作，需要获取自动后的主键用于别的业务
public class PreparedStatementTest {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//输入url、用户名、密码创建连接
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai", "root", "123456");
//sql查询语句,?为占位符
String sql = "insert into account(name, password, balance) values(?, ?, ?) ;";
PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
//设置占位符参数
preparedStatement.setObject(1, "wangwu");
preparedStatement.setObject(2, "123");
preparedStatement.setObject(3, "200");
//执行查询语句
int i = preparedStatement.executeUpdate();
//结果遍历
System.out.println("执行结果：" + i);
if (i > 0){
ResultSet generatedKeys = preparedStatement.getGeneratedKeys();
generatedKeys.next();
Object key = generatedKeys.getObject(1);
System.out.println("主键：" + key);
}
//关闭资源
preparedStatement.close();
connection.close();
}
}
控制台输出如下
执行结果：1
主键：4
批量插入
采用循环插入数据，由于需要创建连接和关闭连接非常耗时，MySQL支持批量插入，极大地提高新增效率
public class PreparedStatementTest {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//输入url、用户名、密码创建连接
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai", "root", "123456");
//sql查询语句,?为占位符
String sql = "insert into account(name, password, balance) values(?, ?, ?) ";
PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
for (int i = 0; i < 50000; i++) {
System.out.println(i);
//设置占位符参数
preparedStatement.setObject(1, "wangwu"+i);
preparedStatement.setObject(2, "123");
preparedStatement.setObject(3, "200");
//执行查询语句
preparedStatement.addBatch();
}
//结果遍历
preparedStatement.executeBatch();
//关闭资源
preparedStatement.close();
connection.close();
}
}
MySQL事务
事务特性如下
原子性：事务中的操作要么都发生，要么都不发生
一致性：状态永远是一个一致性状态变到另一个一致性状态
隔离性：不同事务是不会互相干扰
持久性：事务提交后，就是永久性的变更
用户之间转账场景，张三给李四转10元，张三余额-10，然后李四余额+10。如果中途流程失败，则张三余额-10，李四余额不变，导致数据不准确的问题。MySQL支持事务的特性，很好的解决这个问题，要么都成功，要么都失败，保证数据的一致性。
未开启事务版本
public class PreparedStatementTest {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//输入url、用户名、密码创建连接
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai", "root", "123456");
//sql查询语句,?为占位符
String sql = "update account set balance = balance - 10 where name = 'zhangsan'";
String sql2 = "update account set balance = balance + 10 where name = 'lisi'";
PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
PreparedStatement preparedStatement2 = connection.prepareStatement(sql2, Statement.RETURN_GENERATED_KEYS);
preparedStatement.executeUpdate();
//模拟执行异常
int a = 1 / 0;
preparedStatement2.executeUpdate();
//关闭资源
preparedStatement.close();
preparedStatement2.close();
connection.close();
}
}
执行前
执行后
开启事务版本
public class PreparedStatementTest {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//输入url、用户名、密码创建连接
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai", "root", "123456");
//开启事务
connection.setAutoCommit(Boolean.FALSE);
String sql = "update account set balance = balance - 10 where name = 'zhangsan'";
String sql2 = "update account set balance = balance + 10 where name = 'lisi'";
PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
PreparedStatement preparedStatement2 = connection.prepareStatement(sql2, Statement.RETURN_GENERATED_KEYS);
preparedStatement.executeUpdate();
//模拟执行异常
int a = 1 / 0;
preparedStatement2.executeUpdate();
//提交事务
connection.commit();
//关闭资源
preparedStatement.close();
preparedStatement2.close();
connection.close();
}
}
执行前
执行后
Druid数据库连接池使用
数据库操作初始化连接资源，释放资源是非常耗时的操作，因此数据库连接池可以实现数据连接的重复理由。JDBC数据库连接池使用的是javax.sql.DataSource接口规范，常见的数据库连接池有DBCP、C3P0和Druid等。
Druid是阿里巴巴开源平台上一个数据库连接池，Java语言中最好的数据库连接池。Druid能够提供强大的监控和扩展功能。数据库连接池不会去关闭连接，而是去回收连接。
POM引入依赖
代码
public class DruidTest {
public static void main(String[] args) throws SQLException {
DruidDataSource dataSource = new DruidDataSource();
//设置数据库连接相关信息
dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai");
dataSource.setUsername("root");
dataSource.setPassword("123456");
//初始化连接池大小
dataSource.setInitialSize(2);
//最大允许连接池大小
dataSource.setMaxActive(5);
DruidPooledConnection connection = dataSource.getConnection();
System.out.println("数据库连接成功");
//回收连接，而不是关闭连接
connection.close();
}
}
手写数据库连接工具类
JDBCUtils工具类
工具类
public class JDBCUtils {
//数据库连接池
private static DruidDataSource dataSource;
//数据库连接，同个线程使用同一个连接
private static ThreadLocal
static {
//初始化连接信息
dataSource = new DruidDataSource();
//设置数据库连接相关信息
dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai");
dataSource.setUsername("root");
dataSource.setPassword("123456");
//初始化连接池大小
dataSource.setInitialSize(2);
//最大允许连接池大小
dataSource.setMaxActive(5);
}
/**
* 获取数据库连接
*
* @return 数据库连接
* @throws SQLException
*/
public static Connection getConnection() {
Connection connection = threadLocal.get();
if (Objects.nonNull(connection)) {
return connection;
}
try {
connection = dataSource.getConnection();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
//设置连接到当前线程
threadLocal.set(connection);
return connection;
}
/**
* 释放数据库连接，而非关闭连接
*
* @return
* @throws SQLException
*/
public static void freeConnection() throws SQLException {
Connection connection = threadLocal.get();
if (Objects.isNull(connection)) {
return;
}
threadLocal.remove();
//恢复事务为true
connection.setAutoCommit(Boolean.TRUE);
connection.close();
}
}
实践
public class JDBCUtilsTest {
public static void main(String[] args) throws SQLException {
Connection connection = JDBCUtils.getConnection();
//sql查询语句,?为占位符
String sql = "insert into account(name, password, balance) values(?, ?, ?) ;";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//设置占位符参数
preparedStatement.setObject(1, "wangwu");
preparedStatement.setObject(2, "123");
preparedStatement.setObject(3, "200");
//执行查询语句
int i = preparedStatement.executeUpdate();
//结果遍历
System.out.println("执行结果：" + i);
//关闭资源
preparedStatement.close();
JDBCUtils.freeConnection();
}
}
通用DAO类封装
Account
@Data
public class Account {
private Long id;
private String name;
private String password;
private Long balance;
}
BaseDAO
public abstract class BaseDAO{
/**
* 增删改操作
* @param sql
* @param params
* @return
*/
public Integer update(String sql, Object... params) {
Connection connection = JDBCUtils.getConnection();
//sql查询语句,?为占位符
PreparedStatement preparedStatement = null;
int rows = 0;
try {
preparedStatement = connection.prepareStatement(sql);
if (Objects.nonNull(params) && params.length > 0) {
for (int i = 0; i < params.length; i++) {
try {
preparedStatement.setObject(i + 1, params[i]);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
rows = preparedStatement.executeUpdate();
//关闭资源
preparedStatement.close();
if (connection.getAutoCommit()) {
//没有开启事务才去释放资源
JDBCUtils.freeConnection();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return rows;
}
/**
* 查询操作
* @param clazz
* @param sql
* @param params
* @param
* @return
*/
public
Connection connection = JDBCUtils.getConnection();
//sql查询语句,?为占位符
PreparedStatement preparedStatement = null;
List
try {
preparedStatement = connection.prepareStatement(sql);
if (Objects.nonNull(params) && params.length > 0) {
for (int i = 0; i < params.length; i++) {
try {
preparedStatement.setObject(i + 1, params[i]);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
ResultSet resultSet = preparedStatement.executeQuery();
//获取元数据
ResultSetMetaData metaData = resultSet.getMetaData();
//获取列数
int columnCount = metaData.getColumnCount();
while (resultSet.next()){
//反射对象
T instance = clazz.newInstance();
for (int i = 1; i <= columnCount; i++) {
Field declaredField = clazz.getDeclaredField(metaData.getColumnLabel(i));
declaredField.setAccessible(Boolean.TRUE);
declaredField.set(instance, resultSet.getObject(i));
}
result.add(instance);
}
//关闭资源
resultSet.close();
preparedStatement.close();
if (connection.getAutoCommit()) {
//没有开启事务才去释放资源
JDBCUtils.freeConnection();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (NoSuchFieldException e) {
e.printStackTrace();
}
return result;
}
}
AccountDAO
public class AccountDAO extends BaseDAO {
}
实践
public class AccountDAOTest {
public static void main(String[] args) {
AccountDAO accountDAO = new AccountDAO();
String insertSql = "insert into account(name, password, balance) values(?, ?, ?) ;";
String querySql = "select * from account where name = ? and password = ? ;";
Integer rows = accountDAO.update(insertSql, "wangwu", "123", "200");
System.out.println("新增结果");
System.out.println(rows);
List
System.out.println("查询结果");
System.out.println(accountList);
}
}
控制台输出如下
新增结果
1
查询结果
[Account(id=236362, name=wangwu, password=123, balance=200)]
觉得不错就点赞、收藏、关注、评论 吧。