Java之JDBC01
1.JDBC的概念
- JDBC（Java DataBase Connectivity：java数据库连接）是一种用于执行SQL语句的Java API，可以为多种关系型数据库提供统一访问，它是由一组用Java语言编写的类和接口组成的。
- JDBC的作用：可以通过java代码操作数据库
2.JDBC的使用
//1.加载驱动 如果不写 就会默认去配置文件中读取 但是在web环境下 一定要写
//获取指定类的字节码文件
Class.forName("com.mysql.cj.jdbc.Driver");
//2.建立连接
String url = "jdbc:mysql://127.0.0.1:3306/jdbctest?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, username, password);
//3.预处理指令 可以理解成我们已经连接到数据库了 开始写sql语句
String sql = "insert into student values(null,'张三',18,'北京')";
PreparedStatement ps = conn.prepareStatement(sql);
//4.执行 如果sql是DML(update delete insert)此时调用executeUpdate
// DQL select 调用executeQuery
int i = ps.executeUpdate();
System.out.println(i);
//5.关闭资源
ps.close();
conn.close();
3.DriverManager
-
DriverManager：驱动管理对象
-
注册驱动(告诉程序该使用哪一个数据库驱动)
-
注册给定的驱动程序：static void registerDriver(Driver driver) （DriverManager的方法）
我们在刚刚的入门案例中并没有注册驱动，也成功了，咋回事呢
这是因为我们使用了Class.forName：Class.forName(“com.mysql.jdbc.Driver”)
我们通过了给forName指定了是mysql的驱动 -
在com.mysql.jdbc.Driver类中存在静态代码块:
public class Driver extends NonRegisteringDriver implements java.sql.Driver {
public Driver() throws SQLException {
}
static {
try {
DriverManager.registerDriver(new Driver());
} catch (SQLException var1) {
throw new RuntimeException("Can't register driver!");
}
}
}
我们不需要通过DriverManager调用静态方法registerDriver()，因为只要Driver类被使用，则会执行其静态代码块完成注册驱动
4.Connection
-
Connection：数据库连接对象
- 获取执行者对象
- 获取普通执行者对象：Statement createStatement();
- 获取预编译执行者对象：PreparedStatement prepareStatement(String sql);
- 获取执行者对象
5.Statement
- Statement：执行sql语句的对象
- 执行DML语句：int executeUpdate(String sql)
- 执行DML语句：int executeUpdate(String sql)
- 执行DML语句：int executeUpdate(String sql) - 执行DQL语句：ResultSet executeQuery(String sql);
- 返回值ResultSet：封装查询的结果
- 参数sql：可以执行select语句 - 释放资源
- 立即将执行者对象释放：void close()
6.ResultSet
ResultSet：结果集对象
判断结果集中是否还有数据：boolean next();
有数据返回true，并将索引向下移动一行
没有数据返回false
获取结果集中的数据：XXX getXxx(“列名”);
XXX代表数据类型(要获取某列数据，这一列的数据类型)
例如：String getString(“name”); int getInt(“age”);
释放资源
立即将结果集对象释放：void close();
7.学生类CURD
/**
* @Author Grg
* @Date 2023/8/14 8:53
* @PackageName:PACKAGE_NAME
* @ClassName: Student
* @Description: 又是码代码的一天
* @Version plus max 宇宙无敌终极版本
*/
public class Student {
private int id ;
private String name;
private int age;
private String address;
public Student() {
}
public Student(int id, String name, int age, String address) {
this.name = name;
this.id = id;
this.age = age;
this.address = address;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "Student{" +
"name='" + name + '\'' +
", id=" + id +
", age=" + age +
", address='" + address + '\'' +
'}';
}
}
7.1增and占位符
@Test
public void test1() throws Exception{
//增
//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.建立连接
String url = "jdbc:mysql://127.0.0.1:3306/jdbctest?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, username, password);
//3.预处理指令
String sql = "insert into student values(null, '李四',22 ,'郑州')";
PreparedStatement ps = conn.prepareStatement(sql);
//4.执行
int i = ps.executeUpdate();
System.out.println(i);
//5.关闭资源
conn.close();
ps.close();
}
@Test
public void test05() throws Exception {
Student student = new Student(1, "haha", 18, "上海");
// 1 加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
// 2建立连接
String url = "jdbc:mysql://127.0.0.1:3306/jdbctest?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, username, password);
//字符串拼接
// String sql = "insert into student values(null,'"+student.getName()+"', "+student.getAge()+" , '"+student.getAddress()+"')";
//占位符传参 如果数据需要拼接的时候 可以用占位符来执行
String sql = "insert into student values(null , ?, ? , ?)";
PreparedStatement ps = conn.prepareStatement(sql);
//占位符的替换
ps.setObject(1, student.getName());
ps.setObject(2, student.getAge());
ps.setObject(3, student.getAddress());
int i = ps.executeUpdate();
ps.close();
conn.close();
System.out.println(i);
}
7.2删
@Test
public void test2() throws Exception{
//删
//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.建立连接
String url = "jdbc:mysql://127.0.0.1:3306/jdbctest?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, username, password);
//3.预处理指令
String sql = "delete from student where id = '6'";
PreparedStatement ps = conn.prepareStatement(sql);
//4.执行
int i = ps.executeUpdate();
System.out.println(i);
//5.关闭资源
ps.close();
conn.close();
}
7.3改
@Test
public void test3() throws Exception{
//改
//1.添加驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.建立连接
String url = "jdbc:mysql://127.0.0.1:3306/jdbctest?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8";
String name = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url,name,password);
//3.预处理sql
String sql = "update student set age = 25 where id = 8 ";
PreparedStatement ps = conn.prepareStatement(sql);
//4.执行
int i = ps.executeUpdate();
System.out.println(i);
//5.关闭资源
ps.close();
conn.close();
}
7.4查
@Test
public void test4() throws Exception{
//1.添加驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.建立连接
String url = "jdbc:mysql://127.0.0.1:3306/jdbctest?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url,username,password);
//3.预处理sql
String sql = "select * from student";
PreparedStatement ps = conn.prepareStatement(sql);
//4.执行
ResultSet resultSet = ps.executeQuery();
while ( resultSet.next() ){
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
int age = resultSet.getInt("age");
String address = resultSet.getString("address");
System.out.println(id + "---" + name + "---" + age + "---" + address);
}
//5.关闭资源
resultSet.close();
conn.close();
ps.close();
}
7.5优化select
@Test
public void test6() throws Exception{
//优化select查询
// 1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
// 2.建立连接
String url = "jdbc:mysql://127.0.0.1:3306/jdbctest?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, username, password);
//3.预处理sql
String sql = "select * from student";
PreparedStatement ps = conn.prepareStatement(sql);
//4.执行
ResultSet resultSet = ps.executeQuery();
List<HashMap<Object, Object>> data = new ArrayList<>();
while (resultSet.next()){
HashMap<Object, Object> hashMap = new HashMap<>();
for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++) {
hashMap.put(resultSet.getMetaData().getColumnLabel(i),
resultSet.getObject(i));
data.add(hashMap);
}
}
//5.关闭资源
resultSet.close();
ps.close();
conn.close();
System.out.println(data);
}
8.工具类封装
8.1连接函数的封装
import java.sql.Connection;
import java.sql.DriverManager;
public class DAOUtil {
/**
* 获取连接对象
*/
public static Connection getConnection() throws Exception{
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/jdbctest?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8";
String username = "root";
String password = "123456";
return DriverManager.getConnection(url, username, password);
}
}
8.2加载驱动代码封装
Class.forName("com.mysql.cj.jdbc.Driver");
驱动注册代码 只需要执行一次就可以了
static {
try{
Class.forName("com.mysql.cj.jdbc.Driver");
}catch(Exception e){
e.printStackTrace();
}
}
8.3通过常量解决魔法值
public class DAOUtil {
private static final String CLASS_NAME = "com.mysql.cj.jdbc.Driver";
private static final String URL = "jdbc:mysql://127.0.0.1:3306/jdbctest?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8";
private static final String USER_NAME = "root";
private static final String PASS_WORD = "123456";
static {
try{
Class.forName(CLASS_NAME);
}catch(Exception e){
e.printStackTrace();
}
}
/**
* 获取连接对象
*/
public static Connection getConnection() throws Exception{
return DriverManager.getConnection(URL, USER_NAME, PASS_WORD);
}
}
8.4增删改封装
public static int executeUpadate(String sql, Object[] params) {
Connection conn = null;
try {
conn = DAOUtil.getConnection();
PreparedStatement ps = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
int i = ps.executeUpdate();
return i;
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (conn != null) {
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
return 0;
}
8.5查询封装
public static List executeQuery(String sql, Object[] params) {
Connection conn = null;
try {
conn = DAOUtil.getConnection();
PreparedStatement ps = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
ResultSet set = ps.executeQuery();
int columnCount = set.getMetaData().getColumnCount();
List<Map<String, Object>> data = new ArrayList<>();
while (set.next()) {
Map<String, Object> map = new HashMap<>();
for (int i = 1; i <= columnCount; i++) {
map.put(set.getMetaData().getColumnLabel(i),
set.getObject(i));
}
data.add(map);
}
return data;
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (conn != null) {
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
return null;
}
@Test
public void test11(){
String sql = "select * from student where id = ? ";
Object[] params = {4};
List<Map<String, Object>> list = DAOUtil.executeQuery(sql, params);
System.out.println(list);
}
8.6可变参数代替数组
@Test
public void test12(){
String sql = "select * from student ";
Object[] params = {};
List<Map<String, Object>> list = DAOUtil.executeQuery(sql, params);
System.out.println(list);
}
我们进行全部查询的时候 即使没有参数 也需要创建一个 对象数组 ，因为 java中规定 有参数的 函数
调用的时候一定要传参。
A 方法重载
B 可变参数
public static int executeUpdate(String sql ,Object... params)
public static List<Map<String,Object>> executeQuery(String sql,Object...
params )
可变参数的底层其实就是一个语法糖 底层还是 数组 只不过调用的时候 不需要程序猿自己创建
9.事务
conn.setAutoCommit(false);
...
conn.commit();
ACID四大特性:
原子性 atomicity
事务中的操作 要么都做 要么都不做
一致性 consistency
隔离性 isolation
持久性 durability
9.1获取主键
ResultSet generatedKeys = ps.getGeneratedKeys();
while(generatedKeys.next()){
int anInt = generatedKeys.getInt(1);
System.out.println(anInt);
}
10.数据库连接池
- 数据库连接池是个容器，负责分配、管理数据库连接(Connection);
- 它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个;
- 释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏
- 资源重用
- 提升系统响应速度
- 避免数据库连接遗漏
扫一扫
3174
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
