目录
1:使用Druid数据源
1.1:pom.xml
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.2.6</version>
</dependency>
1.2:application.xml
spring:
datasource:
druid:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/ssm_db?serverTimezone=UTC
username: root
password: root
1.3:测试
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
//classes:设置SpringBoot启动类
@SpringBootTest(classes = Application.class)
public class SpringTest {
}
2:整合Mybatis
2.1:mybatis入门案例
2.1.1:pom.xml
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.20</version>
</dependency>
<!--1.导入对应的starter-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.0</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
2.1.2:application.xml
#2.配置相关信息
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC
username: root
password: root
# 配置mybatis规则
mybatis:
mapper-locations: classpath:mapper/*.xml #sql映射文件位置
type-aliases-package: com.pojo #对应实体类entity层路径(别名)
configuration:
map-underscore-to-camel-case: true #开启驼峰命名 处理下划线映射到实体
logging.level.com.mapper.UserMapper: debug #mybatis配置日志文件,选择自己的mapper层路径
2.1.3:mapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mapper.UserMapper">
</mapper>
2.2:MyBatis参数
2.2.1:单个字面量类型的参数
- 此时可以使用任意的名称获取参数的值
- parameterType 写不写都行
User getUserById(Integer id);
<select id="getUserById" parameterType="long" resultType="User">
select * from t_user where id = #{id}
</select>
2.2.2:多个字面量类型的参数
- MyBatis会自动将这些参数放在一个map集合,以param1,param2…为键,以参数为值;
- 只需要通过#{}访问map集合的键就可以获取相对应的值
User getUserByIdAndName(Long id,String name);
<select id="getUserByIdAndName" resultType="User">
select * from t_user where id = #{id} and name = #{name}
</select>
2.2.3:实体类类型的参数
通过访问实体类对象中的属性名获取属性值
int insertUser(User user);
<insert id="insertUser" parameterType="user">
insert into t_user values(null,#{name},#{userName},#{age},#{birthday},#{time})
</insert>
2.2.4:map集合类型的参数
通过访问map集合的键就可以获取相对应的值
int login(Map<String, Object> map);
<insert id="login" parameterType="user">
insert into t_user(id,name) values(null,#{name})
</insert>
2.2.5:使用@Param标识参数
将这些参数放在map集合中,以@Param注解的value属性值为键,以参数为值;
User selectByUserName(@Param("username") String username);
<select id="selectByUserName" resultType="User">
select * from t_user where username = #{username}
</select>
2.3:MyBatis返回值
2.3.1:查询单个数据
int getCount();
<select id="getCount" resultType="_integer">
select count(id) from t_user
</select>
2.3.2:查询一个实体类对象
User getUserById(@Param("id") int id);
<select id="getUserById" resultType="User">
select * from t_user where id = #{id}
</select>
2.3.3:查询一个list集合
List<User> getUserList();
<select id="getUserList" resultType="User">
select * from t_user
</select>
2.3.4:查询一条数据为map集合
Map<String, Object> getUserToMap(@Param("id") int id);
<select id="getUserToMap" resultType="map">
select * from t_user where id = #{id}
</select>
<!--结果:{password=123456, sex=男, id=1, age=23, username=admin}-->
2.3.5:查询多条数据为map集合
将表中的数据以map集合的方式查询,一条数据对应一个map;
若有多条数据,就会产生多个map集合,此时可以将这些map放在一个list集合中获取
List<Map<String, Object>> getAllUserToMap();
<select id="getAllUserToMap" resultType="map">
select * from t_user
</select>
@MapKey("id")
Map<String, Object> getAllUserToMap();
<select id="getAllUserToMap" resultType="map">
select * from t_user
</select>
1={password=123456, sex=男, id=1, age=23, username=admin},
2={password=123456, sex=男, id=2, age=23, username=张三},
3={password=123456, sex=男, id=3, age=23, username=张三}
2.4:MyBatis增删改查
2.4.1:添加
2.4.1.1:添加功能获取自增的主键
int insertUser(User user);
<insert id="insertUser" useGeneratedKeys="true" keyProperty="id">
insert into t_user values(null,#{username},#{password},#{age},#{sex})
</insert>
2.4.2:删除
2.4.2.1:批量删除
int deleteMore(@Param("ids") String ids);//"1,2"
<delete id="deleteMore">
delete from t_user where id in (${ids})
</delete>
2.4.3:修改
2.4.4:查询
2.4.4.1:模糊查询
List<User> testMohu(@Param("mohu") String mohu);
<select id="testMohu" resultType="User">
<!--select * from t_user where username like '%${mohu}%'-->
<!--select * from t_user where username like concat('%',#{mohu},'%')-->
select * from t_user where username like "%"#{mohu}"%"
</select>
2.5:MyBatis多表
2.5.1:一对一查询
2.5.1.1:一对一查询的模型
2.5.1.2:实体类
2.5.1.3:级联方式处理映射关系
<resultMap id="orderMap" type="com.domain.Order">
<result column="uid" property="user.id"></result>
<result column="username" property="user.username"></result>
<result column="password" property="user.password"></result>
<result column="birthday" property="user.birthday"></result>
</resultMap>
<select id="findAll" resultMap="orderMap">
select * from orders o,user u where o.uid=u.id
</select>
2.5.1.4:association
<resultMap id="orderMap" type="com.domain.Order">
<result property="id" column="id"></result>
<result property="ordertime" column="ordertime"></result>
<result property="total" column="total"></result>
<association property="user" javaType="com.domain.User">
<result column="uid" property="id"></result>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<result column="birthday" property="birthday"></result>
</association>
</resultMap>
2.5.2:一对多查询
2.5.2.1:一对多查询的模型
2.5.2.2:实体类
2.5.2.3:collection
<resultMap id="userMap" type="com.domain.User">
<result column="id" property="id"></result>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<result column="birthday" property="birthday"></result>
<collection property="orderList" ofType="com.domain.Order">
<result column="oid" property="id"></result>
<result column="ordertime" property="ordertime"></result>
<result column="total" property="total"></result>
</collection>
</resultMap>
<select id="findAll" resultMap="userMap">
select *,o.id oid from user u left join orders o on u.id=o.uid
</select>
2.5.2.4::分步查询
1)查询员工信息
Emp getEmpByStep(@Param("eid") int eid);
<resultMap id="empDeptStepMap" type="Emp">
<id column="eid" property="eid"></id>
<result column="ename" property="ename"></result>
<result column="age" property="age"></result>
<result column="sex" property="sex"></result>
<!--
select:设置分步查询,查询某个属性的值的sql的标识(namespace.sqlId)
column:将sql以及查询结果中的某个字段设置为分步查询的条件
-->
<association property="dept"
select="com.atguigu.MyBatis.mapper.DeptMapper.getEmpDeptByStep" column="did">
</association>
</resultMap>
<!--Emp getEmpByStep(@Param("eid") int eid);-->
<select id="getEmpByStep" resultMap="empDeptStepMap">
select * from t_emp where eid = #{eid}
</select>
2)根据员工所对应的部门id查询部门信息
Dept getEmpDeptByStep(@Param("did") int did);
<select id="getEmpDeptByStep" resultType="Dept">
select * from t_dept where did = #{did}
</select>
2.6:动态SQL
2.6.1:if
<!--List<Emp> getEmpListByMoreTJ(Emp emp);-->
<select id="getEmpListByMoreTJ" resultType="Emp">
select * from t_emp where 1=1
<if test="ename != '' and ename != null">
and ename = #{ename}
</if>
<if test="age != '' and age != null">
and age = #{age}
</if>
<if test="sex != '' and sex != null">
and sex = #{sex}
</if>
</select>
2.6.2:where
- 若where标签中的if条件都不满足,则where标签没有任何功能
- 若where标签中的if条件满足,则where标签会自动添加where关键字,并将条件最前方多余的
and去掉
<select id="getEmpListByMoreTJ2" resultType="Emp">
select * from t_emp
<where>
<if test="ename != '' and ename != null">
ename = #{ename}
</if>
<if test="age != '' and age != null">
and age = #{age}
</if>
<if test="sex != '' and sex != null">
and sex = #{sex}
</if>
</where>
</select>
2.6.3:trim
- trim用于去掉或添加标签中的内容
- prefix:在trim标签中的内容的前面添加某些内容
- prefixOverrides:在trim标签中的内容的前面去掉某些内容
- suffix:在trim标签中的内容的后面添加某些内容
- suffixOverrides:在trim标签中的内容的后面去掉某些内容
<select id="getEmpListByMoreTJ" resultType="Emp">
select * from t_emp
<trim prefix="where" suffixOverrides="and">
<if test="ename != '' and ename != null">
ename = #{ename} and
</if>
<if test="age != '' and age != null">
age = #{age} and
</if>
<if test="sex != '' and sex != null">
sex = #{sex}
</if>
</trim>
</select>
2.6.4:choose、when、otherwise
- 当 when 中有条件满足的时候,就会跳出 choose
- 所有的 when 和 otherwise 条件中,只有一个会输出
<!--List<Emp> getEmpListByChoose(Emp emp);-->
<select id="getEmpListByChoose" resultType="Emp">
select <include refid="empColumns"></include> from t_emp
<where>
<choose>
<when test="ename != '' and ename != null">
ename = #{ename}
</when>
<when test="age != '' and age != null">
age = #{age}
</when>
<when test="sex != '' and sex != null">
sex = #{sex}
</when>
<when test="email != '' and email != null">
email = #{email}
</when>
</choose>
</where>
</select>
2.6.5:foreach
- collection:设置要循环的数组或集合
- item:表示集合或数组中的每一个数据
- separator:设置循环体之间的分隔符
- open:设置foreach标签中的内容的开始符
- close:设置foreach标签中的内容的结束符
<!--int insertMoreEmp(List<Emp> emps);-->
<insert id="insertMoreEmp">
insert into t_emp values
<foreach collection="emps" item="emp" separator=",">
(null,#{emp.ename},#{emp.age},#{emp.sex},#{emp.email},null)
</foreach>
</insert>
<!--int deleteMoreByArray(int[] eids);-->
<delete id="deleteMoreByArray">
delete from t_emp where
<foreach collection="eids" item="eid" separator="or">
eid = #{eid}
</foreach>
</delete>
2.6.6:SQL片段
sql片段,可以记录一段公共sql片段,在使用的地方通过include标签进行引入
<sql id="empColumns">
eid,ename,age,sex,did
</sql>
select <include refid="empColumns"></include> from t_emp
2.7:MyBatis的逆向工程
2.7.1:pom.xml
<!-- 依赖MyBatis核心包 -->
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.7</version>
</dependency>
</dependencies>
<!-- 控制Maven在构建过程中相关配置 -->
<build>
<!-- 构建过程中用到的插件 -->
<plugins>
<!-- 具体插件,逆向工程的操作是以构建过程中插件形式出现的 -->
<plugin>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-maven-plugin</artifactId>
<version>1.3.0</version>
<!-- 插件的依赖 -->
<dependencies>
<!-- 逆向工程的核心依赖 -->
<dependency>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-core</artifactId>
<version>1.3.2</version>
</dependency>
<!-- 数据库连接池 -->
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.2</version>
</dependency>
<!-- MySQL驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.8</version>
</dependency>
</dependencies>
</plugin>
</plugins>
</build>
2.7.2:generatorConfig.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
<!--
targetRuntime: 执行生成的逆向工程的版本
MyBatis3Simple: 生成基本的CRUD(清新简洁版)
MyBatis3: 生成带条件的CRUD(奢华尊享版)
-->
<context id="DB2Tables" targetRuntime="MyBatis3Simple">
<!-- 数据库的连接信息 -->
<jdbcConnection driverClass="com.mysql.jdbc.Driver"
connectionURL="jdbc:mysql://localhost:3306/mybatis"
userId="root"
password="123456">
</jdbcConnection>
<!-- javaBean的生成策略-->
<javaModelGenerator targetPackage="com.atguigu.mybatis.bean"
targetProject=".\src\main\java">
<property name="enableSubPackages" value="true" />
<property name="trimStrings" value="true" />
</javaModelGenerator>
<!-- SQL映射文件的生成策略 -->
<sqlMapGenerator targetPackage="com.atguigu.mybatis.mapper"
targetProject=".\src\main\resources">
<property name="enableSubPackages" value="true" />
</sqlMapGenerator>
<!-- Mapper接口的生成策略 -->
<javaClientGenerator type="XMLMAPPER"
targetPackage="com.atguigu.mybatis.mapper" targetProject=".\src\main\java">
<property name="enableSubPackages" value="true" />
</javaClientGenerator>
<!-- 逆向分析的表 -->
<!-- tableName设置为*号,可以对应所有表,此时不写domainObjectName -->
<!-- domainObjectName属性指定生成出来的实体类的类名 -->
<table tableName="t_emp" domainObjectName="Emp"/>
<table tableName="t_dept" domainObjectName="Dept"/>
</context>
</generatorConfiguration>