JDBC即：Java DataBase Connectivity（Java语言连接数据库）
是Java语言中用来规范客户端程序如何来访问数据库的
应用程序接口，提供了诸如查询和更新数据库中数据的方法。
目录
1.概念
核心API和使用路线
2.核心API
2.1使用步骤总结
2.1.1注册驱动
DriverManager.registerDriver(new Driver());2.1.2获取连接
Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/atguigu", "root", "ykxykx");2.1.3创建发送sql语句对象
Statement statement = connection.createStatement();2.1.4发送sql语句并获取返回结果
String sql = "selevt *from t_user";
statement.executeQuery(sql);2.1.5结果集解析
while (resultSet.next()){
int id = resultSet.getInt("id");
String account = resultSet.getString("account");
String password = resultSet.getString("password");
String nickname = resultSet.getString("nickname");
System.out.println(id + account + password + nickname);
}2.1.6资源关闭
（从内往外关，也就是从下往上关）
resultSet.close();
statement.close();
connection.close();2.2statement演示
package com.ykx.api.statement;
import com.mysql.cj.jdbc.Driver;
import java.sql.*;
/**
* @author: yangkx
* @Title: StatementQueryPart
* @ProjectName: JDBCTest
* @Description:
* @date: 2023-06-01 16:06
*/
public class StatementQueryPart {
public static void main(String[] args) throws SQLException {
//1.注册驱动
DriverManager.registerDriver(new Driver());
//2.获取连接
Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/atguigu", "root", "ykxykx");
//3.创建statement
Statement statement = connection.createStatement();
//4.发送sql，获取返回结果
String sql = "selevt *from t_user";
ResultSet resultSet = statement.executeQuery(sql);
//5.结果集解析
while (resultSet.next()){
int id = resultSet.getInt("id");
String account = resultSet.getString("account");
String password = resultSet.getString("password");
String nickname = resultSet.getString("nickname");
System.out.println(id + account + password + nickname);
}
//6.关闭资源
resultSet.close();
statement.close();
connection.close();
}
}2.3statement详解（模拟登陆）
public class StatementUserLoginPart {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
//1.获取用户输入信息
Scanner scanner = new Scanner(System.in);
System.out.println("输入账号：");
String account = scanner.nextLine();
System.out.println("输入密码：");
String password = scanner.nextLine();
//2.注册驱动
//方式一： 不建议
//DriverManager.registerDriver(new Driver());
//方式二： 建议
Class.forName("com.mysql.cj.jdbc.Driver");
//3.获取连接
Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/atguigu", "root", "ykxykx");
//4.创建statement
Statement statement = connection.createStatement();
//5.发送sql，获取返回结果
String sql = "select * from t_user where account = '"+account+"' and password = '"+password+"';";
ResultSet resultSet = statement.executeQuery(sql);
//6.结果集解析
// while (resultSet.next()){
// int id = resultSet.getInt("id");
// String account1 = resultSet.getString("account");
// String password1 = resultSet.getString("password");
// String nickname = resultSet.getString("nickname");
// }
if(resultSet.next()){
System.out.println("登录成功！");
}else{
System.out.println("登录失败");
}
//7.关闭资源
resultSet.close();
statement.close();
connection.close();
}
}2.4preparedstatement基本使用流程
对比获取连接之后的不同步骤
public class PSUserLoginPart {
public static void main(String[] args) throws Exception {
//1.获取用户输入信息
Scanner scanner = new Scanner(System.in);
System.out.println("输入账号：");
String account = scanner.nextLine();
System.out.println("输入密码：");
String password = scanner.nextLine();
//PS的数据库流程
//2.注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//3.获取连接
Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/atguigu", "root", "ykxykx");
//4.编写SQL语句结构
String sql = "select *from t_user where account = ? and password = ?;";
//5.创建PS 并设置SQL语句结构
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//6.占位符赋值
preparedStatement.setObject(1, account);
preparedStatement.setObject(2, password);
//7.发送SQL语句，并获取返回结果,这里eQ()不用再传sql了！！！
ResultSet resultSet = preparedStatement.executeQuery();
if(resultSet.next()){
System.out.println("登录成功！");
}else{
System.out.println("登录失败");
}
//8.关闭资源
resultSet.close();
preparedStatement.close();
connection.close();
}
}
2.4.1preparedstatement执行dml语句
public class PSCURDPart {
@Test
public void testInsert() throws Exception {
//1.注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.获取连接
Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/atguigu", "root", "ykxykx");
//3.编写SQL语句结构
String sql = "insert into t_user(account,password,nickname) values(?,?,?);";
//4.创建PS 并设置SQL语句结构
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//5.占位符赋值
preparedStatement.setObject(1, "test");
preparedStatement.setObject(2, "test");
preparedStatement.setObject(3, "二狗子");
//6.发送SQL语句，并获取返回结果,这里eQ()不用再传sql了！！！
int i = preparedStatement.executeUpdate();
//7.输出结果
if(i > 0) System.out.println("执行行数：" + i);
else System.out.println("插入失败~");
//8.关闭资源
preparedStatement.close();
connection.close();
}
@Test
public void testUpdate() throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/atguigu", "root", "ykxykx");
String sql = "update t_user set nickname = ? where id = ?;";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1, "三狗子");
preparedStatement.setObject(2, 3);
int i = preparedStatement.executeUpdate();
if(i > 0) System.out.println("执行行数：" + i);
else System.out.println("更新失败~");
preparedStatement.close();
connection.close();
}
@Test
public void testDelete() throws Exception{
Class.forName("com.mysql.cj.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/atguigu", "root", "ykxykx");
String sql = "delete from t_user where id = ?;";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1, 3);
int i = preparedStatement.executeUpdate();
if(i > 0) System.out.println("执行行数：" + i);
else System.out.println("删除失败~");
preparedStatement.close();
connection.close();
}
}2.4.2preparedstatement执行dql语句
@Test
public void testSelect() throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/atguigu", "root", "ykxykx");
String sql = "select *from t_user";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
ResultSet resultSet = preparedStatement.executeQuery();
List<Map> list = new ArrayList<>();
//metaData 装的当前结果集列的信息对象
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount(); //获取有多少列
while(resultSet.next()){
Map map = new HashMap();
//下标从1开始
for(int i = 1; i <= columnCount; i++){
//获取指定列下角标的名称
String columnLabel = metaData.getColumnLabel(i);
//获取指定列下角标的值
Object object = resultSet.getObject(i);
map.put(columnLabel,object);
}
list.add(map);
}
System.out.println(list);
resultSet.close();
preparedStatement.close();
connection.close();
}3.扩展提升
3.1主键回显和主键值获取
@Test
public void test() throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/atguigu", "root", "ykxykx");
String sql = "insert into t_user(account,password,nickname) values (?,?,?);";
PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
preparedStatement.setObject(1, "test1");
preparedStatement.setObject(2, "123456");
preparedStatement.setObject(3, "哈士奇");
int i = preparedStatement.executeUpdate();
if(i > 0){
System.out.println("数据插入成功");
ResultSet generatedKeys = preparedStatement.getGeneratedKeys();
generatedKeys.next();
int index = generatedKeys.getInt(1);
System.out.println("id = " + index);
}else{
System.out.println("数据插入失败");
}
preparedStatement.close();
connection.close();
}3.2批量插入数据优化
@Test
public void test() throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/atguigu?rewriteBatchedStatements=true", "root", "ykxykx");
String sql = "insert into t_user(account,password,nickname) values (?,?,?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
for(int i = 0; i < 10; i++){
preparedStatement.setObject(1, "test1"+i);
preparedStatement.setObject(2, "123456"+i);
preparedStatement.setObject(3, "哈士奇"+i);
preparedStatement.addBatch();
}
preparedStatement.executeBatch();
preparedStatement.close();
connection.close();
}3.3事务和设计转账类结构
4.Druid连接池
druid.properties
#druid连接池需要的配置参数，key固定命名
driverClassName=com.mysql.cj.jdbc.Driver
username=root
password=ykxykx
url=jdbc:mysql://127.0.0.1:3306/atguigu