JavaWeb(后端)

数据库基础概念概念说明数据库DB存储和管理数据的仓库英文DataBase数据库管理系统DBMS操作和管理数据库的大型软件英文DataBase Management SystemSQL结构化查询语言操作关系型数据库的编程语言定义了统一操作标准英文Structured Query Language目前主流的数据库OracleMySQLSQL ServerPostgreSQLDB2SQLLiteMariaDB数据模型关系型数据库(RDBMS)定义建立在关系模型基础上由多张相互连接的二维表组成的数据库二维表由行记录和列字段组成的表格例员工表、部门表MySQL 的操作逻辑通过 MySQL 客户端连接 DBMS向 DBMS 发送 SQL 语句由 DBMS 操作数据库的表结构与数据层级关系数据库服务器 → 多个数据库 → 单个数据库包含多张表 → 单张表包含多行记录SQL 语句SQL结构化查询语言是一门操作关系型数据库的编程语言定义操作所有关系型数据库的统一标准按功能被分为四大类分类英文全称作用DDLData Definition Language数据定义语言定义数据库对象库、表DMLData Manipulation Language数据操作语言操作表中数据增、删、改DQLData Query Language数据查询语言查询表中数据DCLData Control Language数据控制语言管理数据库权限DDL 语句用于操作数据库、表的结构数据库操作查询数据库查询所有数据库show databases;查询当前数据库select database();注意要操作某一个数据库必须要切换到对应的数据库中创建数据库create database [ if not exists ] 数据库名 [default charset 字符集];创建数据库时可以不指定字符集默认注意在同一个数据库服务器中不能创建两个名称相同的数据库否则将会报错if not exists如果数据库不存在则创建该数据库存在则不创建使用数据库use 数据库名;操作表前需切换到对应数据库删除数据库drop database [ if exists ] 数据库名;注意如果删除一个不存在的数据库将会报错if exists如果数据库存在再执行删除否则不执行删除表操作创建表create table 表名( 字段1 字段1类型 [约束] [comment 字段1注释 ], 字段2 字段2类型 [约束] [comment 字段2注释 ], ...... 字段n 字段n类型 [约束] [comment 字段n注释 ] ) [ comment 表注释 ] ;约束作用于字段的规则限制存储数据约束描述关键字非空约束限制该字段取值不能为 nullnot null唯一约束保证字段的所有数据都是唯一、不重复的unique主键约束主键是一行数据的唯一标识要求非空且唯一primary key默认约束保存数据时如果未指定该字段值则采用默认值default外键约束让两张表的数据建立连接保证数据的一致性和完整性foreign keycreate table user( id int primary key auto_increment comment ID,唯一标识, #主键自动增长 username varchar(20) not null unique comment 用户名, name varchar(10) not null comment 姓名, age int comment 年龄, gender char(1) default 男 comment 性别 ) comment 用户表;主键自增auto_increment:作用插入新数据行时数据库会自动为主键字段生成递增的数值规则自增序列从正整数 1 开始每次插入新记录时自动加一数据类型在 MySQL 中主要分为三类数值类型tinyint、smallint、int、bigint、float、double、decimal精确小数字符串类型char定长、varchar变长、text长文本、blob二进制数据日期时间类型date日期、time时间、datetime日期时间、timestamp带时区的日期时间查询表-- 查询当前数据库的所有表 show tables; -- 查看指定的表结构 desc 表名; -- 可以查看指定表的字段、字段的类型、是否可以为NULL、是否存在默认值等信息 -- 查询指定表的建表语句 show create table 表名;修改表添加字段alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];修改字段-- 修改字段类型 alter table 表名 modify 字段名 新数据类型(长度); -- 修改字段名 alter table 表名 change 旧字段名 新字段名删除字段alter table 表名 drop 字段名;修改表名rename table 表名 to 新表名;删除表drop table [ if exists ] 表名;注意关于表结构的查看、修改、删除操作一般都是直接基于图形化界面操作DML 语句用于操作表中数据插入数据:向指定字段添加数据:insert into 表名 (字段名1, 字段名2) values (值1, 值2);全部字段添加数据:-- 全部字段插入字段顺序与表结构一致 insert into 表名 values (值1, 值2, ...);批量添加数据(指定字段):insert into 表名 (字段名1, 字段名2) values (值1, 值2), (值1, 值2);批量添加数据(全部字段):insert into 表名 values (值1, 值2, ...), (值1, 值2, ...);注意值的顺序 / 类型需与字段匹配长度不能超过字段限制修改数据:update 表名 set 字段名1 值1 , 字段名2 值2 , .... [where 条件] ;注意无 where 条件会修改表中所有数据建议添加 update_time 字段记录修改时间删除数据delete from 表名 [where 条件] ;注意无 where 条件会修改表中所有数据不能删除单个字段可用 update 设为 NULLDQL 语句用于查询表中数据select 字段列表 from 表名列表 where 条件列表 group by 分组字段列表 having 分组后条件列表 order by 排序字段列表 limit 分页参数基本查询查询多个字段select 字段1, 字段2, 字段3 from 表名;查询所有字段(通配符)select * from 表名;设置别名select 字段1 [ as 别名1 ] , 字段2 [ as 别名2 ] from 表名;-- 别名中有特殊字符时使用或包含去除重复记录select distinct 字段列表 from 表名;条件查询select 字段列表 from 表名 where 条件列表 ; -- 条件列表意味着可以有多个条件在 SQL 语句当中构造条件的运算符分为比较运算符和逻辑运算符常用的比较运算符:常用的逻辑运算符聚合函数对一列数据计算后返回一个结果常用的聚合函数函数作用count()计数max()最大值min()最小值avg()平均值sum()求和注意 : 聚合函数会忽略空值对 NULL 值不作为统计分组查询按照某一列或者某几列把相同的数据进行合并输出通常会使用聚合函数进行计算select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];注意分组后只能查询分组字段和聚合函数having 用于过滤分组结果排查查询select 字段列表 from 表名 [where 条件列表] [group by 分组字段 ] order by 字段1 排序方式1 , 字段2 排序方式2 … ;排序方式asc升序默认desc降序注意多字段排序前一个字段相同时按后一个字段排序分页查询select 字段列表 from 表名 limit 起始索引, 查询记录数 ;注意起始索引 查询页码 - 1* 每页显示记录数JDBC全称Java DataBase ConnectivityJava 数据库连接本质Sun 公司定义的操作关系型数据库的接口规范各数据库厂商实现该接口提供数据库驱动包开发者通过 JDBC 接口编程实际执行的是驱动包中的实现代码操作数据库准备工作创建 Maven 项目引入 MySQL 驱动依赖dependencies !-- MySQL JDBC driver -- dependency groupIdmysql/groupId artifactIdmysql-connector-java/artifactId version8.0.30/version /dependency /dependencies创建数据库 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, wangbian, 123456, 王扁, 22), (2, wujing, 123456, 吴京, 28), (3, danshen, 123456, 蛋神, 24), (4, mowan, 123456, 魔丸, 28), (5, lingzhu, 123456, 灵珠, 27);代码实现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, wangbian);//第一个问号对应的参数 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(); } }参数化测试动态传参public class JDBCTest{ //编写JDBC程序, 查询数据 ParameterizedTest CsvSource({wangbian,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(); } }代码剖析ResultSet结果集封装查询结果next()游标移动到下一行返回 true有数据/ false无数据getXxx(字段名/索引)获取对应字段的数据PreparedStatement预编译 Statement:作用防止 SQL 注入、提高性能原理先将带占位符?的 SQL 发送给数据库编译再传递参数执行SQL 执行方式方式特点缺点静态 SQLStatement参数直接拼接在 SQL 中存在 SQL 注入风险预编译 SQLPreparedStatement用?占位参数动态传递无修改数据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(); }MyBatis:一款持久层框架用于简化 JDBC 开发持久层即数据访问层DAO负责操作数据库框架是一个半成品软件是一套可重用的、通用的、软件基础代码模型在框架的基础上进行软件开发更加高效、规范、通用、可拓展入门程序需求查询所有用户环境准备创建 SpringBoot 工程引入依赖MyBatis Starter、MySQL 驱动、Lombok创建数据库表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, wangbian, 123456, 王扁, 22), (2, wujing, 123456, 吴京, 28), (3, zhangbing, 123456, 张兵, 24), (4, mowan, 123456, 魔丸, 28), (5, lingzhu, 123456, 灵珠, 27);代码实现编写实体类 UserData//自动生成get/set/toString等方法 NoArgsConstructor AllArgsConstructor public class User { private Integer id; //ID private String username; //用户名 private String password; //密码 private String name; //姓名 private Integer age; //年龄 }配置数据库连接application.properties#数据库访问的url地址 spring.datasource.urljdbc:mysql://localhost:3306/web #数据库驱动类类名 spring.datasource.driver-class-namecom.mysql.cj.jdbc.Driver #访问数据库-用户名 spring.datasource.usernameroot #访问数据库-密码 spring.datasource.passwordroot1234编写 Mapper 接口数据访问接口Mapper//标识为MyBatis Mapper接口框架自动生成代理对象并交给Spring管理 public interface UserMapper{ //查询全部 Select(select * from user)//编写查询SQL public ListUser findAll(); }单元测试SpringBootTest class SpringbootMybatisApplicationTests { Autowired//自动注入UserMapper代理对象 private UserMapper userMapper; Test public void testFindAll(){ ListUser userList userMapper.findAll(); for (User user : userList) { System.out.println(user); } } }运行结果注意测试类所在包需要与引导类所在包相同辅助配置IDEA SQL 提示配置在 IDEA 中关联 MySQL 数据库可实现 Mapper 接口中 SQL 语句的语法提示配置完成后还存在不识别表名列名的情况需要配置 MySQL 数据库连接MyBatis 日志配置在 application.properties 中添加配置可查看 SQL 执行日志#mybatis的配置 mybatis.configuration.log-implorg.apache.ibatis.logging.stdout.StdOutImpl再次运行单元测试数据库连接池避免频繁的创建连接、销毁连接而带来的资源浪费工作原理没有数据库连接池的情况客户端执行 SQL 语句要先创建一个新的连接对象然后执行 SQL 语句SQL 语句执行后又需要关闭连接对象从而释放资源每次执行 SQL 时都需要创建连接、销毁链接这种频繁的重复创建销毁的过程是比较耗费计算机的性能有数据库连接池的情况数据库连接池是个容器负责分配、管理数据库连接Connection程序在启动时会在数据库连接池容器中创建一定数量的 Connection 对象允许应用程序重复使用一个现有的数据库连接而不是再重新建立一个客户端在执行 SQL 时先从连接池中获取一个 Connection 对象然后在执行 SQL 语句SQL 语句执行完之后释放 Connection 时就会把 Connection 对象归还给连接池可复用释放空闲时间超过最大空闲时间的连接来避免因为没有释放连接而引起的数据库连接遗漏客户端获取到 Connection 对象了但是 Connection 对象并没有去访问数据库处于空闲数据库连接池发现 Connection 对象的空闲时间 连接池中预设的最大空闲时间此时数据库连接池就会自动释放掉这个连接对象优势资源重用提升系统响应速度避免数据库连接遗漏常见连接池官方定义了 javax.sql.DataSource 接口第三方实现包括C3P0、DBCP、Druid、HikariSpringBoot 默认功能获取连接public Connection getConnection() throws SQLException;切换连接池以 Druid 为例引入依赖dependency !-- Druid连接池依赖 -- groupIdcom.alibaba/groupId artifactIddruid-spring-boot-starter/artifactId version1.2.19/version /dependency配置连接池application.propertiesspring.datasource.typecom.alibaba.druid.pool.DruidDataSource spring.datasource.druid.driver-class-namecom.mysql.cj.jdbc.Driver spring.datasource.druid.urljdbc:mysql://localhost:3306/web spring.datasource.druid.usernameroot spring.datasource.druid.password1234增删改查操作MyBatis 提供两种参数占位方式符号说明场景优缺点#{…}占位符执行时会将 #{…} 替换为?生成预编译SQL参数值传递安全、性能高(推荐${…}拼接符直接将参数拼接在 SQL 语句中存在 SQL 注入问题表名、字段名动态设置时使用不安全、性能低删除Delete(delete from user where id #{id}) //返回值为受影响行数 Integer deleteById(Integer id);新增Insert(insert into user(username,password,name,age) values(#{username},#{password},#{name},#{age})) void insert(User user);修改Update(update user set username #{username},password #{password},name #{name},age #{age} where id #{id}) public void update(User user);查询Select(select * from user where username #{username} and password #{password}) public User findByUsernameAndPassword(Param(username) String username, Param(password) String password);Param 作用为接口方法的参数指定名称确保 SQL 中能正确匹配参数由于用户名是唯一的所以查询返回的结果最多只有一个可以直接封装到一个对象中在 SpringBoot 官方脚手架创建的项目中接口编译时会保留方法参数名此时 Parram 注解可省略XML 映射配置MyBatis 有两种开发方式注解方式适合简单 SQL、XML 方式适合复杂 SQL 功能XML 配置文件规范文件位置XML 文件名与 Mapper 接口名一致且放置在同包下同包名namespace 属性XML 的 namespace 需与 Mapper 接口的全限定名一致SQL 语句对应XML 中 SQL 语句的 ID 与 Mapper 接口的方法名一致且返回类型一致XML 配置文件实现:创建 XML 映射文件:编写 XML 映射文件:?xml version1.0 encodingUTF-8 ? !DOCTYPE mapper PUBLIC -//mybatis.org//DTD Mapper 3.0//EN https://mybatis.org/dtd/mybatis-3-mapper.dtd !-- namespace填写Mapper接口全限定名 -- mapper namespaceorg.example.mapper.UserMapper !-- 编写SQL语句 -- /mapper编写 SQL 语句?xml version1.0 encodingUTF-8 ? !DOCTYPE mapper PUBLIC -//mybatis.org//DTD Mapper 3.0//EN https://mybatis.org/dtd/mybatis-3-mapper.dtd mapper namespaceorg.example.mapper.UserMapper !--查询操作-- !-- select标签用于编写select查询语句 -- !-- resultType属性指的是查询返回的单条记录所封装的类型 -- select idfindAll resultTypeorg.example.pojo.User select * from user /select /mapper辅助配置在 application.properties 中配置 XML 映射文件的位置#指定XML映射配置文件的位置 mybatis.mapper-locationsclasspath:mapper/*.xmlMybatisXMybatisX 是 IDEA 的 MyBatis 快速开发插件支持Mapper 接口与 XML 文件之间的快速跳转XML 中 SQL 语句的自动提示如实体类属性、表字段YAML对比SpringBoot 支持 properties 和 yaml 两种配置格式两者核心差异:语法大小写敏感数值与符号间需空格层级用缩进表示常见数据格式:对象 / Map 集合user: name: hei age: 18 password: 123456数组 / List / Set集合hobby: - sing - dance - rap - basketball注意若配置项的值以 0 开头需用引号包裹避免被解析为八进制数示例# 应用配置 spring: application: name: springboot-mybatis # 数据库配置 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