黑马Tlias JavaWeb后台管理系统 06 Java操作数据库

前言

JDBC:Java DataBase Connectivity。使用Java语言操作关系型数据库的一套API。

Mybatis:基于JDBC的封装的高级框架。

JDBC

介绍

  • sun公司官方定义的一套操作所有关系型数据库的规范,即接口
  • 各个数据库厂商去实现这套接口,提供数据库驱动jar包
  • 我们可以使用这套接口编程,jar包中的实现类去执行操作

示例:

查询数据

需求

基于JDBC实现用户登录功能。

本质是基于JDBC执行如下select语句,并将查询的结果输出到控制台。

select * from user where username = 'linchong' and password = '123456';

准备工作

  1. 创建一个Maven项目

  1. 创建一个数据库web,并在该数据库中创建user表
create table user(
    id int unsigned primary key auto_increment comment 'ID,主键',
    username varchar(20) comment '用户名',
    password varchar(32) comment '密码',
    name varchar(10) comment '姓名',
    age tinyint unsigned comment '年龄'
) comment '用户表';

insert into user(id, username, password, name, age) values (1, 'daqiao', '123456', '大乔', 22),
                                                           (2, 'xiaoqiao', '123456', '小乔', 18),
                                                           (3, 'diaochan', '123456', '貂蝉', 24),
                                                           (4, 'lvbu', '123456', '吕布', 28),
                                                           (5, 'zhaoyun', '12345678', '赵云', 27);

代码实现

  1. 在pom.xml文件中引入依赖
<dependencies>
  <!-- MySQL JDBC driver -->
  <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.30</version>
  </dependency>

  <dependency>
    <groupId>org.junit.jupiter</groupId>
    <artifactId>junit-jupiter</artifactId>
    <version>5.9.3</version>
    <scope>test</scope>
  </dependency>
</dependencies>

  1. 在test目录下编写测试类,定义测试方法
public class JDBCTest {

    /**
     * 编写JDBC程序, 查询数据
     */
    @Test
    public void testJdbc() throws Exception {
        // 获取连接
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/web", "root", "1234");
        // 创建预编译的PreparedStatement对象
        PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM user WHERE username = ? AND password = ?");
        // 设置参数
        pstmt.setString(1, "daqiao"); // 第一个问号对应的参数
        pstmt.setString(2, "123456"); // 第二个问号对应的参数
        // 执行查询
        ResultSet rs = pstmt.executeQuery();
        // 处理结果集
        while (rs.next()) {
            int id = rs.getInt("id");
            String uName = rs.getString("username");
            String pwd = rs.getString("password");
            String name = rs.getString("name");
            int age = rs.getInt("age");

            System.out.println("ID: " + id + ", Username: " + uName + ", Password: " + pwd + ", Name: " + name + ", Age: " + age);
        }
        // 关闭资源
        rs.close();
        pstmt.close();
        conn.close();
    }

}

PreparedStatement接口继承Statement,Statement的对象负责将SQL语句发送到数据库执行。而PreparedStatement对象已经预编译过,执行速度更快。

上述的单元测试中,用户名和密码都写死了,而这两个值应该是动态的,是将来页面传递到服务端的。那么我们可以使用JUnit中的参数化测试进行单元测试,代码如下:

public class JDBCTest {

    /**
     * 编写JDBC程序, 查询数据
     */
    @ParameterizedTest
    @CsvSource({"daqiao,123456"})
    public void testJdbc(String _username, String _password) throws Exception {
        // 获取连接
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/web", "root", "1234");
        // 创建预编译的PreparedStatement对象
        PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM user WHERE username = ? AND password = ?");
        // 设置参数
        pstmt.setString(1, _username); // 第一个问号对应的参数
        pstmt.setString(2, _password); // 第二个问号对应的参数
        // 执行查询
        ResultSet rs = pstmt.executeQuery();
        // 处理结果集
        while (rs.next()) {
            int id = rs.getInt("id");
            String uName = rs.getString("username");
            String pwd = rs.getString("password");
            String name = rs.getString("name");
            int age = rs.getInt("age");

            System.out.println("ID: " + id + ", Username: " + uName + ", Password: " + pwd + ", Name: " + name + ", Age: " + age);
        }
        // 关闭资源
        rs.close();
        pstmt.close();
        conn.close();
    }

}

使用CsvSource注解来传递一组测试的参数。

代码剖析

ResultSet

ResultSet(结果集对象):封装了DQL查询语句查询的结果。

  • next():将光标从当前位置向前移动一行,并判断当前行是否为有效行,返回值为boolean。
  • getXxx(…):获取数据,可以根据列的编号获取,也可以根据列名获取(推荐)。
预编译SQL

编写SQL时有两种风格:

静态SQL(参数硬编码):

conn.prepareStatement("SELECT * FROM user WHERE username = 'daqiao' AND password = '123456'");
ResultSet resultSet = pstmt.executeQuery();

预编译SQL(参数动态传递):

conn.prepareStatement("SELECT * FROM user WHERE username = ? AND password = ?");
pstmt.setString(1, "daqiao");
pstmt.setString(2, "123456");
ResultSet resultSet = pstmt.executeQuery();

使用 ? 对参数值进行占位,再指定每一个占位符对应的值是多少。最终程序会将SQL语句以及参数值发送给数据库,执行时再使用参数值将 ? 替换。

项目开发中最好使用预编译的SQL。主要作用有两个:

  • 防止SQL注入
  • 性能更高
SQL注入

通过控制输入来修改事先定义好的SQL语句,以达到执行代码对服务器进行攻击的方法。

最典型的场景就是用户登录功能。

注入演示

  1. 打开课程资料中的文件夹 <font style="color:rgb(46,161,33);">资料/02. SQL注入演示</font>,运行其中的jar包 <font style="color:rgb(46,161,33);">sql_Injection_demo-0.0.1-SNAPSHOT.jar</font>,进入该目录后,执行命令:
java -jar sql_Injection_demo-0.0.1-SNAPSHOT.jar

这里mysql root用户名的密码要改为1234,否则连不上

  1. 浏览器访问http://localhost:9090/,测试正常的用户名和密码

songjiang 123456

  1. 测试错误的用户名和密码

提示错误

  1. 控制表单输入,来修改事先定义好的SQL语句的含义,进而攻击服务器

此时可以成功登录

这是因为只要我们查询到了数据,就说明用户名密码是对的。

我们精心设计的' or '1' = '1拼接到了原来的SQL语句后面,产生了一个新的SQL语句,如下图所示。or 是或的关系,两者满足其一就能通过。这时候穗软用户名和密码都是错误的,但是却可以返回查询的结果。

SQL注入解决

而通过预编译 select * from user where username = ? and password = ?

就可以直接解决上述的SQL注入的问题。

  1. 打开课程资料中的文件夹 资料/02. SQL注入演示,运行其中的jar包 sql_prepared_demo-0.0.1-SNAPSHOT.jar,进入该目录后,执行命令
java -jar sql_prepared_demo-0.0.1-SNAPSHOT.jar

  1. http://localhost:9090/,测试

正常的用户名密码

此时通过SQL注入的方式不能登录

可以看到在预编译的SQL语句中,当我们执行的时候,会把整个<font style="color:rgb(216,57,49);">' or '1'='1</font>作为一个完整的参数,赋值给第二个问号,此时便查询不到对应的数据。

性能更高

增删改数据

需求

基于JDBC程序,执行如下update语句。

update user set password = '123456', gender = 2 where id = 1;

代码实现

@ParameterizedTest
@CsvSource({"1,123456,25"})
public void testUpdate(int userId, String newPassword, int newAge) throws Exception {
    // 建立数据库连接
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/web", "root", "1234");
    // SQL 更新语句
    String sql = "UPDATE user SET password = ?, age = ? WHERE id = ?";
    // 创建预编译的PreparedStatement对象
    PreparedStatement pstmt = conn.prepareStatement(sql);

    // 设置参数
    pstmt.setString(1, newPassword); // 第一个问号对应的参数
    pstmt.setInt(2, newAge);      // 第二个问号对应的参数
    pstmt.setInt(3, userId);         // 第三个问号对应的参数

    // 执行更新
    int rowsUpdated = pstmt.executeUpdate();

    // 输出结果
    System.out.println(rowsUpdated + " row(s) updated.");

    // 关闭资源
    pstmt.close();
    conn.close();
}

:::info

  • JDBC程序执行DML语句:int rowsUpdated = pstmt.executeUpdate();

返回的是影响的记录数

  • JDBC程序执行DQL语句:ResultSet resultSet = pstmt.executeQuery();

返回值是查询结果集

:::

Mybatis

介绍

  • MyBatis是一款优秀的持久层 框架,用于简化JDBC的开发
  • MyBatis本是Apache的一个开源项目iBatis,2010年这个项目由apache迁移到了google code,并且改名为MyBatis 。2013年11月迁移到Github。
  • https://mybatis.org/mybatis-3/zh/index.html 官网

通过MyBatis可以简化原生的JDBC程序的代码编写

快速入门

需求:使用MyBatis查询所有用户数据

  1. 创建springboot工程,并导入mybatis的起步依赖、mysql的驱动包、lombok

如图xml文件已经自动导入了依赖

  1. 数据准备:创建用户表user,并创建对应的实体类User
create table user(
    id int unsigned primary key auto_increment comment 'ID,主键',
    username varchar(20) comment '用户名',
    password varchar(32) comment '密码',
    name varchar(10) comment '姓名',
    age tinyint unsigned comment '年龄'
) comment '用户表';

insert into user(id, username, password, name, age) values (1, 'daqiao', '123456', '大乔', 22),
                                                           (2, 'xiaoqiao', '123456', '小乔', 18),
                                                           (3, 'diaochan', '123456', '貂蝉', 24),
                                                           (4, 'lvbu', '123456', '吕布', 28),
                                                           (5, 'zhaoyun', '12345678', '赵云', 27);

  1. 配置MyBatis

application.properties中配置数据库的连接信息。

#数据库访问的url地址
spring.datasource.url=jdbc:mysql://localhost:3306/web
#数据库驱动类类名
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#访问数据库-用户名
spring.datasource.username=root
#访问数据库-密码
spring.datasource.password=1234

  1. 编写MyBatis程序:编写MyBatis的持久层接口,定义SQL语句(注解)

在创建出来的springboot工程中,在引导类所在包下,再创建一个包 mapper。在mapper包下创建一个接口UserMapper,这是一个持久层接口(规范:XxxMapper)。

内容如下:

import com.itheima.pojo.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import java.util.List;

@Mapper
public interface UserMapper {
    /**
     * 查询全部
     */
    @Select("select * from user")
    public List<User> findAll();
}

  • @Mapper注解:表示是mybatis中的Mapper接口

程序运行时,框架会自动生成接口的实现类对象(代理对象),并交给Spring的IOC容器管理。

  • @Select注解:代表的就是select查询,用于书写select查询语句
  1. 单元测试

在src下的test目录下,已经自动帮我们创建好了测试类,并且在测试类上已经添加了注解@SpringBootTest,代表该测试类已经与SpringBoot整合。

在测试类运行时会自动通过引导类加载Spring的环境(IOC容器)。我们要测试那个bean对象,就可以直接通过@Autowired注解直接将其注入运行,然后就可以测试了。

测试类代码如下:

@SpringBootTest
class SpringbootMybatisQuickstartApplicationTests {

    @Autowired
    private UserMapper userMapper;

    @Test
    public void testFindAll(){
        List<User> userList = userMapper.findAll();
        for (User user : userList) {
            System.out.println(user);
        }
    }
}

结果:

辅助配置

配置SQL提示

在UserMapper接口中的@Select注解中编写的SQL语句默认是没有提示的。

右击SQL语句按照如下配置

选择MySQL

这时候SQL关键字有提示了,但是还不能识别表名、列名

原因:IDEA和数据库没有加建立连接,不识别表信息

解决方案:在IDEA中配置MySQL数据库连接

这时鼠标悬停就可以查看信息

配置MyBatis日志输出

默认情况下我们看不到SQL语句的执行日志。

在application.properties中加入如下配置,即可查看日志

#mybatis的配置
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

再次运行单元测试,即可看到控制台输出的SQL语句

JDBC VS MyBatis

JDBC的缺点:

  • url、username、password等相关参数全部硬编码在java代码中
  • 查询结果的解析、封装比较繁琐
  • 每次操作数据库之前先获取连接,操作完毕再关闭连接。频繁的获取连接、释放连接造成资源浪费

MyBatis的解决方案

  • 数据库连接四要素(驱动、链接、用户名、密码),都配置在springboot默认的配置文件application.properties中
  • 查询结果的解析和封装 由MyBatis自动完成映射封装,我们无须关注
  • 在MyBatis中使用了数据库连接池技术,从而避免了频繁地创建连接、销毁连接而带来的资源浪费

对于MyBatis来说,我们开发持久层程序操作数据库时,只需要重点关注以下2个方面

  1. application.properties
#驱动类名称
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#数据库连接的url
spring.datasource.url=jdbc:mysql://localhost:3306/web
#连接数据库的用户名
spring.datasource.username=root
#连接数据库的密码
spring.datasource.password=1234
  1. Mapper接口(SQL语句)
@Mapper
public interface UserMapper {
    @Select("select * from user")
    public List<User> list();
}

数据库连接池

介绍
  1. 没有连接池

用户执行SQL语句要先新创建一个连接对象,然后执行SQL语句,SQL语句执行后又需要关闭连接对象从而释放资源。频繁地创建销毁过程耗费计算机的性能。

  1. 有数据库连接池

数据库连接池是一个容器,负责分配、管理数据库连接(Connection)

  • 程序在启动时,会在数据库连接池(容器)中,创建一定数量的Connection对象

允许应用程序重复使用一个现有的数据库连接,而不是再建立一个

  • 客户端在执行SQL时,先从连接池中获取一个Connection对象,然后再执行SQL语句,SQL语句执行完之后,释放Connection时就会把Connection对象归还给连接池(复用)

释放空闲时间超过最大空闲时间的连接,来避免因为没有释放连接而引起的数据库连接遗漏

  • 客户端获取到Connection对象了,但是Connection对象并没有去访问数据库(处于空闲),数据库连接池返现Connection对象的空闲时间>连接池中预设的最大空闲时间,此时数据库连接池就会自动释放掉这个连接对象。

好处:

  • 资源重用
  • 提升系统响应速度
  • 避免数据库连接遗漏
产品

如何实现数据库连接池

官方(sun)提供了数据库连接池标准(javax.sql.DataSource接口)

功能:获取连接

public Connection getConnection() throws SQLException;

第三方组织必须按照DataSource接口实现

常见的数据库连接池:C3P0、DBCP、Druid(性能更好)、Hikari(springboot默认)


  1. Hikari [默认连接池]

  1. Druid

阿里巴巴开源的数据库连接池项目

https://github.com/alibaba/druid/tree/master/druid-spring-boot-starter

切换成Druid连接池

xml引入依赖

<dependency>
  <!-- Druid连接池依赖 -->
  <groupId>com.alibaba</groupId>
  <artifactId>druid-spring-boot-starter</artifactId>
  <version>1.2.19</version>
</dependency>

properties引入数据库连接配置

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.druid.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.druid.url=jdbc:mysql://localhost:3306/web
spring.datasource.druid.username=root
spring.datasource.druid.password=1234

增删改

删除

SQL:delete from user where id = 5;

接口UserMapper中增加以下方法

/**
 * 根据id删除
 */
@Delete("delete from user where id = #{id}")
public Integer deleteById(Integer id);

在MyBatis中,我们可以通过参数占位符#{...}来占位,在调用deleteById方法时,传递的参数值最终会替换占位符。

单元测试类中增加以下语句

@Test
public void testDeleteById(){
        Integer i = userMapper.deleteById(4);
        System.out.println("执行完毕,影响的记录数" + i);
    }

新增
  • SQL:insert into user(username,password,name,age) values(‘zhouyu’,‘123456’,‘周瑜’,20);
/**
 * 添加用户
 */
@Insert("insert into user(username,password,name,age) values(#{username},#{password},#{name},#{age})")
public void insert(User user);
@Test
public void testInsert(){
        User user = new User(null,"gaoyuanyuan","666888","高圆圆", 18);
        userMapper.insert(user);
    }

修改
  • SQL:update user set username = ‘zhouyu’, password = ‘123456’, name = ‘周瑜’, age = 20 where id = 1;
/**
 * 根据id更新用户信息
 */
@Update("update user set username = #{username},password = #{password},name = #{name},age = #{age} where id = #{id}")
public void update(User user);
@Test
public void testUpdate(){
        User user = new User(1,"zhouyu","666888","周瑜", 20);
        userMapper.update(user);
    }

查询
  • SQL:select*fromuser whereusername = ‘zhouyu’ and password = ‘123456’
/**
 * 根据用户名和密码查询用户信息
 */
@Select("select * from user where username = #{username} and password = #{password}")
public User findByUsernameAndPassword(@Param("username") String username, @Param("password") String password);
@Test
public void testFindByUsernameAndPassword(){
        User user = userMapper.findByUsernameAndPassword("zhouyu", "666888");
        System.out.println(user);
    }

XML映射配置

MyBatis有两种开发方式

  1. 注解
  2. 映射
XML配置文件规范

注解的方式主要来完成一些简单的增删改查功能,如果要实现复杂的SQL功能,最好使用XML来配置映射语句。

:::info
XML映射文件规范:

  • XML映射文件的名称与Mapper接口名称一致,并且将XML映射文件和Mapper接口放置在相同包下(同包同名)。
  • XML映射文件的namespace属性为Mapper接口全限定名一致
  • XML映射文件中的SQL语句的id与Mapper接口中的方法名一致,并保持返回类型一致

:::

resultType属性,指的是查询返回的单挑记录所封装的类型。

XML配置文件实现
  1. 创建XML文件

注意,一定要使用/进行分隔,才能创建出对应的目录,而不能用 ‘.’ !!!!!

虽然创建出来后还是看到 ‘.’ 来分隔

  1. 编写XML映射文件

xml映射文件中的dtd约束,直接从mybatis官网复制即可

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="">

</mapper>
  1. 配置

namespace属性为Mapper接口全限定名

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.itheima.mapper.UserMapper">

</mapper>

sql语句的id与Mapper接口中的方法名一致,并保持返回类型一致

<!--查询操作-->
    <select id="findAll" resultType="com.itheima.pojo.User">
        select * from user
    </select>

resultType属性的值,与查询返回的单条记录封装的类型一致

结果

MyBatisX的使用

是一款基于IDEA的快速开发MyBatis的插件

IDEA直接下载不了可以去官网下载

https://plugins.jetbrains.com/plugin/10119-mybatisx/versions

SpringBoot配置文件

介绍

之前我们一直使用Springboot创建后自带的application.properties进行属性的配置。而如果在项目中我们需要配置大量的属性,采用properties配置文件这种key = value的形式会显得配置文件的层级结构不清晰,也比较臃肿。

这时候我们就采用yml格式的配置文件(Yet Another Markup Language,另一种配置语言),后缀名为yml或yaml。使用树形结构,以数据为中心,更简洁。

语法

  • 大小写敏感
  • 数值前面必须有空格,作为分隔符
  • 使用缩进表示层级关系,缩进时不允许使用Tab键,只能使用空格(idea中会自动将Tab缩进转换为空格)
  • 缩进的空格数目不重要,只要相同层级的元素左侧对其即可
  • #表示注释,从这个字符一直到行尾都会被解析器忽略

yml文件中常见的数据格式:

  • 对象/Map集合
user:
  name: zhangsan
  age: 18
  password: 123456
  • 数组/List/Set集合
hobby: 
  - java
  - game
  - sport

:::info
yml中如果配置项的值是以0开头的,值需要使用" " 引起来,因为以0开头在yml中表示8进制的数据

:::

案例

  1. 修改原配置文件名为_application.properties(让他无法被加载)
  2. 创建新的application.yml文件
#数据源配置
spring:
  #应用名称
  application:
    name: springboot-mybatis-quickstart 
  #数据库配置
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/web
    username: root
    password: 1234
#mybatis配置
mybatis:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

中文无法显示的,Editor -> File Encodings这个位置更改为UTF-8

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值