一、学习内容
- SSM(Spring、SpringMVC、MyBatis)
- Apache Shiro
- SpringBoot
二、数据库事务
- 事务:若将 N 个数据库操作(CRUD)放到同一个事务中,则这 N 个数据库操作最终要么全都生效,要么全都不生效
- 🍀 开启事务【START TRANSACTION】
- 🍀 回滚事务:若事务中的某个数据库操作失败,其他所有数据库操作都需要回滚(恢复到开启事务之前的状态)【ROLLBACK】
- 🍀 提交事务:如果事务中的所有数据库操作都成功被执行,就提交事务(让这些操作正式生效)【COMMIT】
三、JDBC 的事务管理
🍀 JDBC 中用 Connection 对象来管理事务
🍀 connection.setAutoCommit(false)
:开启事务(自己管理事务)
🍀 connection.rollback()
:回滚事务 (若执行某条语句的过程中出现异常,之前执行过的语句恢复到最初状态)
🍀 connection.commit()
:
提交事务 (若所有语句都被正常执行,没有出现异常,会正常提交事务,让所有语句的修改都生效)
四、事务的四大特性
① 原子性(Atomicity)
🍀 事务作为一个整体被执行,包含在其中的对数据库的操作要么全部被执行,要么都不执行
② 一致性(Consistency)
🍀 事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是:数据库中的数据应满足完整性约束
③ 隔离性(Isolation)
🍀 多个事务并发执行时,一个事务的执行不应影响其他事务的执行
④ 持久性(Durability)
🍀 已被提交的事务对数据库的修改应该永久保存在数据库中
五、MyBatis
- MyBatis 是一款优秀的持久层(Dao层)的框架
中文官网:https://mybatis.org/mybatis-3/zh/index.html
六、MyBatis 核心配置文件和基本使用
(1) 🍀 添加依赖(MyBatis 依赖喝 MySQL 数据库连接驱动包)
<dependencies> <!-- 引入 JDBC, Java 连接 MySQL 的驱动包 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.49</version> </dependency> <!-- MyBatis 依赖 --> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.5</version> </dependency> </dependencies>
(2) 🍀 创建 MyBatis 的核心配置文件(文件名:mybatis-config.xml
)
XML 配置文件中包含了对 MyBatis 系统的核心设置,包括获取数据库连接实例的数据源(DataSource)以及决定事务作用域和控制方式的事务管理器(TransactionManager)
官方核心配置文件:https://mybatis.org/mybatis-3/zh/getting-started.html
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <!-- 环境 --> <!-- default:使用哪个环境下的数据库配置 --> <environments default="development"> <!-- 开发环境(开发调试阶段)--> <environment id="development"> <!-- 采用 JDBC 的事务管理方法 --> <transactionManager type="JDBC"/> <!-- POOLED:采取 MyBatis 的连接池管理数据库连接 --> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/study_mb"/> <property name="username" value="root"/> <property name="password" value="root"/> </dataSource> </environment> <!-- 生产环境(发布阶段)--> <environment id="production"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/test_mybatis"/> <property name="username" value="root"/> <property name="password" value="root"/> </dataSource> </environment> </environments> </configuration>
🍀 transactionManager 的 type 为 JDBC【采用 JDBC 方式来管理事务】
🍀 dataSource 的 type 为 POOLED【采用连接池的方式管理数据库连接】
CREATE TABLE `student` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(22) NOT NULL, `money` bigint(20) NOT NULL, `create_time` varchar(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4
public class Student { private Long id; private String name; private Long money; private String createTime; public String getCreateTime() { return createTime; } public void setCreateTime(String createTime) { this.createTime = createTime; } public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Long getMoney() { return money; } public void setMoney(Long money) { this.money = money; } @Override public String toString() { return "Student{" + "id=" + id + ", name='" + name + '\'' + ", money=" + money + ", createTime='" + createTime + '\'' + '}'; } }
七、查询 student 表
(1) 实体映射文件 mapper
- 实体映射文件
① 用于做表和实体类之间的映射
② 在 mapper 文件夹下创建
- 把映射文件的路径配置到核心配置文件(mybatis-config.xml)中
① 在核心配置文件中可通过<mappers></mappers>
标签配置多个实体映射文件路径
② 在<mappers></mappers>
标签中可配置多个<mapper/>
标签(每个<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="student"> <!-- id="list":后面可通过 list 对应该条 SQL 语句并执行该条 SQL 语句 --> <!-- resultType: 该条 SQL 语句的结果对应的 Java Bean --> <!-- 会自动将结果集映射为 List<Student> --> <select id="list" resultType="com.pojo.po.Student"> SELECT * FROM student </select> </mapper>
(2) 读取核心配置文件, 拿到 SqlSession
public class TestStudent { @Test public void testSelect() throws Exception { // 读取 MyBatis 的核心配置文件 Reader reader = Resources.getResourceAsReader("mybatis-config.xml"); // 创建工厂构建器 SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); // 创建工厂 SqlSessionFactory factory = builder.build(reader); // 创建 SqlSession SqlSession sqlSession = factory.openSession(); // 通过 sqlSession 执行 SQL 语句 // 参数是 <mapper> 标签中的 namespace 和 <select> 标签的 id List<Student> studentList = sqlSession.selectList("student.list"); for (Student student : studentList) { System.out.println("student = " + student); } // 关闭 session sqlSession.close(); } }
八、字段和属性名映射
(1) mapUnderscoreToCamelCase
是否开启驼峰命名自动映射,即从经典数据库列名 A_COLUMN 映射到经典 Java 属性名 aColumn
若不配置,数据库表中以下划线方式命名的字段查询出的结果是 null
<configuration> <settings> <setting name="mapUnderscoreToCamelCase" value="true"/> </settings> </configuration>
🍀 假如不设置驼峰下划线自动映射,也可在实体映射文件中通过 <resultMap> 进行 Java Bean 和表字段的映射
<?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="student"> <!-- id="list":后面可通过 list 对应该条 SQL 语句并执行该条 SQL 语句 --> <!-- resultType: 该条 SQL 语句的结果对应的 Java Bean --> <!-- 会自动将结果集映射为 List<Student> --> <!-- <select id="list" resultType="com.pojo.po.Student">--> <!-- SELECT * FROM student--> <!-- </select>--> <resultMap id="resultMapStudent" type="com.pojo.po.Student"> <!-- 属性名和字段名完全一样可不进行设置 --> <id property="id" column="id"/> <!-- <result property="id" column="id"/>--> <!-- <result property="name" column="name"/>--> <!-- <result property="money" column="money"/>--> <result property="createTime" column="create_time"/> </resultMap> <select id="list" resultMap="resultMapStudent"> SELECT * FROM student </select> </mapper>
(2) 完整的 settings 标签配置
<settings> <setting name="cacheEnabled" value="true"/> <setting name="lazyLoadingEnabled" value="true"/> <setting name="aggressiveLazyLoading" value="true"/> <setting name="multipleResultSetsEnabled" value="true"/> <setting name="useColumnLabel" value="true"/> <setting name="useGeneratedKeys" value="false"/> <setting name="autoMappingBehavior" value="PARTIAL"/> <setting name="autoMappingUnknownColumnBehavior" value="WARNING"/> <setting name="defaultExecutorType" value="SIMPLE"/> <setting name="defaultStatementTimeout" value="25"/> <setting name="defaultFetchSize" value="100"/> <setting name="safeRowBoundsEnabled" value="false"/> <setting name="safeResultHandlerEnabled" value="true"/> <setting name="mapUnderscoreToCamelCase" value="false"/> <setting name="localCacheScope" value="SESSION"/> <setting name="jdbcTypeForNull" value="OTHER"/> <setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString"/> <setting name="defaultScriptingLanguage" value="org.apache.ibatis.scripting.xmltags.XMLLanguageDriver"/> <setting name="defaultEnumTypeHandler" value="org.apache.ibatis.type.EnumTypeHandler"/> <setting name="callSettersOnNulls" value="false"/> <setting name="returnInstanceForEmptyRow" value="false"/> <setting name="logPrefix" value="exampleLogPreFix_"/> <setting name="logImpl" value="SLF4J | LOG4J | LOG4J2 | JDK_LOGGING | COMMONS_LOGGING | STDOUT_LOGGING | NO_LOGGING"/> <setting name="proxyFactory" value="CGLIB | JAVASSIST"/> <setting name="vfsImpl" value="org.mybatis.example.YourselfVfsImpl"/> <setting name="useActualParamName" value="true"/> <setting name="configurationFactory" value="org.mybatis.example.ConfigurationFactory"/> </settings>
九、SqlSessionFactory 细节
🍀 SqlSessionFactory 一旦被创建就应该在应用的运行期间一直存在,没有任何理由丢弃它或重新创建另一个实例。 使用 SqlSessionFactory 的最佳实践是在应用运行期间不要重复创建多次,多次重建 SqlSessionFactory 被视为一种代码“坏习惯”。因此 SqlSessionFactory 的最佳作用域是应用作用域。 有很多方法可以做到,最简单的就是使用单例模式或者静态单例模式。
/** * @author Larry Meadors */ public class SqlSessionManager implements SqlSessionFactory, SqlSession { private final SqlSessionFactory sqlSessionFactory; // 构造方法私有化 private SqlSessionManager(SqlSessionFactory sqlSessionFactory) { } public static SqlSessionManager newInstance(Reader reader) { return new SqlSessionManager(new SqlSessionFactoryBuilder().build(reader, null, null)); } public static SqlSessionManager newInstance(Reader reader, String environment) { return new SqlSessionManager(new SqlSessionFactoryBuilder().build(reader, environment, null)); } public static SqlSessionManager newInstance(SqlSessionFactory sqlSessionFactory) { return new SqlSessionManager(sqlSessionFactory); } }
public class MyBatisUtil { // 保留一份即可 private static SqlSessionFactory sqlSessionFactory; static { try (Reader reader = Resources.getResourceAsReader("mybatis-config.xml")) { sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader); } catch (Exception e) { e.printStackTrace(); } } /** * 返回一个 SqlSession * * @param autoCommit 是否自动提交事务 */ public static SqlSession openSession(boolean autoCommit) { return sqlSessionFactory.openSession(autoCommit); } }
静态代码块只会在类加载时被执行一次,与对象无关。静态代码块使用 static 修饰,与对象无关
十、参数传递
(1) 参数占位符
${}
:直接文本替换
#{}
:预编译传值,可防止 SQL 注入
(2) 代码测试
🍃 传递单个参数
/** * 传递单个参数 */ @Test public void testParam0() { try (SqlSession sqlSession = MyBatisUtil.openSession(true)) { Student studentById = sqlSession.selectOne("student.getById", 3); System.out.println("testParam0 studentById = " + studentById); } }
<?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="student"> <select id="getById" resultType="com.pojo.po.Student"> <!-- #{}: 参数占位符 --> SELECT * FROM student WHERE id = #{id} </select> </mapper>
🍃 多个参数的传递
public class TestStudent { /** * 传递多个参数 */ @Test public void testParam1() { try (SqlSession sqlSession = MyBatisUtil.openSession(true)) { Map<String, String> paramMap = new HashMap<>(); paramMap.put("id", "3"); paramMap.put("money", "7009900"); List<Student> studentList = sqlSession.selectList("student.listByIdAndMoney", paramMap); for (Student student : studentList) { System.out.println("testParam1 student = " + student); } } } @Test public void testParam2() { try (SqlSession sqlSession = MyBatisUtil.openSession(true)) { Student studentParams = new Student(); studentParams.setId(3L); studentParams.setMoney(7009900L); List<Student> studentList = sqlSession.selectList("student.listByIdAndMoney", studentParams); for (Student student : studentList) { System.out.println("testParam2 student = " + student); } } } }
<?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="student"> <select id="listByIdAndMoney" resultType="com.pojo.po.Student"> <!-- <【表示小于符号】 --> SELECT * FROM student WHERE id < #{id} OR money >= #{money} </select> </mapper>
模糊查询
public class TestStudent { @Test public void testParam3() { try (SqlSession sqlSession = MyBatisUtil.openSession(true)) { List<Student> studentList = sqlSession.selectList("student.getByName", "%张%"); for (Student student : studentList) { System.out.println("testParam3 student = " + student); } } } }
<?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="student"> <select id="getByName" resultType="com.pojo.po.Student"> SELECT * FROM student WHERE name LIKE #{name} </select> </mapper>
假如中文模糊查询没有结果,需要配置数据库的字符编码
十一、日志打印
🍀 (1) 在核心配置文件中配置打印日志
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <settings> <!-- 打印日志信息 --> <setting name="logImpl" value="STDOUT_LOGGING"/> </settings> </configuration>
🍀 (2) 第三方日志打印库
<dependency> <groupId>ch.qos.logback</groupId> <artifactId>logback-classic</artifactId> <version>1.2.3</version> <scope>test</scope> </dependency>
使用该第三方库的话需要取消核心配置文件中的配置
十二、多表查询
(1) 准备工作
--- 公司表 CREATE TABLE `company` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, `intro` varchar(1000) DEFAULT NULL, `create_time` varchar(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 --- 工作经验表 CREATE TABLE `experience` ( `id` int(11) NOT NULL AUTO_INCREMENT, `job` varchar(20) NOT NULL, `intro` varchar(1000) DEFAULT NULL, `company_id` int(11) NOT NULL, `create_time` varchar(20) NOT NULL, PRIMARY KEY (`id`), KEY `company_id` (`company_id`), CONSTRAINT `experience_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4
public class Company { private Long id; private String name; private String intro; private String createTime; @Override public String toString() { return "Company{" + "id=" + id + ", name='" + name + '\'' + ", intro='" + intro + '\'' + '}'; } }
public class Experience { private Long id; private String job; private String intro; private Company company; private String createTime; @Override public String toString() { return "Experience{" + "id=" + id + ", job='" + job + '\'' + ", intro='" + intro + '\'' + ", company=" + company + ", createTime='" + createTime + '\'' + '}'; } }
(2) 多表查询
查询 experience 表,同时查询出对应的 company 信息
写法1:
<?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="experience"> <resultMap id="rmExp" type="com.pojo.po.Experience"> <!-- c_id 会和 Experience 的 company 属性的 id 属性进行映射 --> <!-- c_name 会和 Experience 的 company 属性的 name 属性进行映射 --> <result property="company.id" column="c_id"/> <result property="company.name" column="c_name"/> </resultMap> <select id="list" resultMap="rmExp"> SELECT e.*, c.id c_id, c.NAME c_name FROM experience e JOIN company c ON c.id = e.company_id </select> </mapper>
写法2
<?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="experience"> <select id="list" resultType="com.pojo.po.Experience"> SELECT e.*, c.id `company.id`, c.NAME `company.name`, c.intro `company.intro` FROM experience e JOIN company c ON c.id = e.company_id </select> </mapper>