Mybatis笔记整理:
新建一个module
maven仓库引入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.23</version>
</dependency>
<!--mysql依赖-->
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.41</version>
</dependency>
<!--单元测试依赖-->
<!-- https://mvnrepository.com/artifact/junit/junit -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13</version>
<scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.12</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.3.10</version>
</dependency>
创建jdbc.properties文件: (ps:mysql版本是5.5, 8.0与这个不同)
mysql.driverClass = com.mysql.jdbc.Driver
mysql.url = jdbc:mysql://127.0.0.1:3306/test?serverTimezone=UTC
mysql.username = root
mysql.password = 123456
创建日志文件(log4j.properties)
log4j.rootLogger= debug, stdout
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p - %m%n
创建myBatisConfig.xml文件:
<?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>
<properties resource="jdbc.properties"/>
<typeAliases>
<package name="com.mybatis.pojo"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="com.mybatis.config.DruidDataSourceFactory">
<property name="driverClassName" value="${mysql.driverClass}"/> <property name="url" value="${mysql.url}"/>
<property name="username" value="${mysql.username}"/>
<property name="password" value="${mysql.password}"/>
</dataSource>
</environment>
</environments>
<package name="com.mybatis.mapper"/>
</mappers>
</configuration>
在src.main.java下创建com.mybatis目录:此时mybatis的初步配置已经完成,可以在mybatis下创建mapper包和pojo包,mapper包下写接口:
package com.mybatis.mapper;
import com.mybatis.pojo.Book;
import org.apache.ibatis.annotations.Param;
import java.util.HashMap;import java.util.List;
public interface BookMapper
{
List findAll();
T selectFindById(int id);
…
}
pojo包下写实体类:
package com.mybatis.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@NoArgsConstructor
@AllArgsConstructor
//规则:实体类的属性名一定要和数据库对应表的字段名一致(一模一样)
public class Book {
private Integer id;
private String book_author;
private String name;
private String createTime;
private Double price;
private Integer sales;
private Integer stock;
}
同时在resources目录下需要创建与src.main.java下相同的目录结构文件,以配合在myBatisConfig.xml文件下完成包扫描映射,接口名字必须和映射文件的名字相同 :在src.main.java.com.mybatis.mapper下写一个BookMapper接口,在resources.com.mybatis.mapper目录下对应写一个BookMapper.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.mybatis.mapper.BookMapper">
<resultMap id="baseResultMap" type="book">
<id column="id" property="id"/>
<result column="author" property="book_author"/>
</resultMap>
<select id="selectFindById" parameterType="int" resultMap="baseResultMap">
select * from book where id=#{id}
</select>
............................
</mapper>
***Mybatis底层没有实现druid/C3P0连接池,在使用时则需要自己去实现实现过程中只需要继承***UnpooledDataSourceFactory
public class DruidDataSourceFactory extends UnpooledDataSourceFactory {
public DruidDataSourceFactory() {
this.dataSource = new DruidDataSource();
}
}
在src.test目录下创建java.com.mybatis.test目录,该目录下写测试类:MyBatisTest
package com.mybatis.test;
import cn.hutool.core.date.DateUtil;
import com.mybatis.mapper.BookMapper;
import com.mybatis.mapper.StudentMapper;
import com.mybatis.pojo.Book;
import com.mybatis.pojo.Student;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.apache.ibatis.io.Resources;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;
public class MybatisTest {
SqlSession sqlSession = null;
BookMapper bookMapper = null;
StudentMapper studentMapper = null;
//此注解标准意思是:在执行某个方法前先执行此注解标注的方法
@Before
public void before()throws Exception{
//加载配置文件
InputStream inputStream = Resources.getResourceAsStream(“myBatisConfig.xml”); //获取sqlSessionFactory对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
sqlSession = sqlSessionFactory.openSession();
bookMapper = sqlSession.getMapper(BookMapper.class);
studentMapper = sqlSession.getMapper(StudentMapper.class);
}
…测试方法
@Test
public void selectById() throws IOException{
Book book = (Book) bookMapper.selectFindById(98);
System.out.println(book);
}
//关闭sqlSession
@After
public void after()throws Exception{
sqlSession.close();
}
}
在myBatisConfig.xml文件中:
<properties>用于指定数据库连接信息的位置
<properties resource="jdbc.properties"/>
<package>用于自定义扫描pojo里面的实体类名,定义别名,别名默认为类名(首字母小写)
<package name="com.mybatis.pojo"/>
放入配置数据库基本连接信息
<dataSource type="com.mybatis.config.DruidDataSourceFactory">
<property name="driverClassName" value="${mysql.driverClass}"/>
<property name="url" value="${mysql.url}"/>
<property name="username" value="${mysql.username}"/>
<property name="password" value="${mysql.password}"/>
</dataSource>
引入映射文件
<mappers>
包扫描映射
在包扫描中需要在resource目录中创建同名同目录结构的文件,这样才能扫描映射
接口名字必须和映射文件的名字相同
<package name="com.mybatis.mapper"/>
</mappers>
在BookMapper.xml文件中:
namespace:命名空间 对应接口的全限定名(包名+类名)
resultMap:指定字段与属性的对应映射关系
id: 指定主键字段
column:数据库表字段
property:指定数据库表字段名相对应的pojo中的属性名
<select id="findAll" resultMap="baseResultMap">
select * from book
</select>
id:唯一标识,在动态代理方式实现过程中,要求id的值和方法名保持一致
resultType:表示返回参数类型
parameterType:传入参数类型
#{}:表示一个占位符,能防止sql注入
${}:表示拼接sql语句串,将接收到的内容不加任何修饰直接拼接到sql语句中
总结;
sql语句动态生成的时候使用${}
sql语句中进行参数占位的时候使用#{}===>大多数
<insert id="save" parameterType="com.mybatis.pojo.Book" keyColumn="id" useGeneratedKeys="true" keyProperty="id">
insert into book(author,createTime,name,price,sales,stock) values (#{author},#{createTime},#{name},#{price},#{sales},#{stock})
</insert>
添加操作需要注意的问题:
1.主键自增
keyColumn 指定数据库表主键的字段
keyProperty:指数据库表主键字段对应的实体类属性名
useGeneratedKeys:开启主键回写(自增)
2.主键不自增(手动设置值)
3.不支持主键自增策略
主键不支持自增操作:
select UUID(); UUID算法生成主键
如果在insert标签中配置了selectkey子标签,那么insert标签上的useGeneratedKeys="true" keyProperty="id",配置默认就不生效
<insert id="save" parameterType="book">
<selectKey keyProperty="id" resultType="int" keyColumn="id" order="BEFORE">
select LAST_INSERT_ID()
</selectKey>
insert into book(author,createTime,name,price,sales,stock) values (#{book_author},#{createTime},#{name},#{price},#{sales},#{stock})
</insert>
在mybatis中多参数查询操作的解决方案:
多参数直接写arg0 arg1…
或者param1 param2…
select * from book where name=#{arg0} and stock=#{arg1}
select * from student where user_name = #{param1} and password=#{param2}
- 注解查询:
注解查询,给参数命名,参数命名后就能根据名字得到参数值,正确地将参数传入sql语句中
Book findByAuthorAndPrice(@Param(“author”)String author,@Param(“price”)double price);
select * from book where author=#{author} and price=#{price}
3.传入map:
Bookmapper:
Book findByAandP(HashMap map);
test:
HashMap map = new HashMap();
map.put("author","封歌");
map.put("price",188.8);
Book book = bookMapper.findByAandP(map);
System.out.println(book);
xml:
<select id="findByAandP" parameterType="map" resultMap="baseResultMap">
select * from book where author=#{author} and price=#{price}
</select>
模糊查询:
占位符 select ∗ from student where username like ′占位符select ∗ from student where user
namelike ′ {userName}%’
#{}连接 select * from student where user_name like concat(’%’,#{userName},’%’)
动态sql
对sql语句进行动态组装
常用动态sql标签
- 判断语句(只能做单分支判断)
2.类似java中的switch语句,用于多条件分支判断
多条件匹配的逻辑就要使用多分支判断标签
特征:choose标签包裹when otherwise标签
注意:choose标签中至少要有一个when标签 0个或一个otherwise标签
when和otherwise不能单独使用
需求:查询操作 当id有值时进行id查询
id没有值时进行username用户名查询
当id和username用户名都没有值时,查询无结果
3., 辅助标签,主要用于处理一些sql拼接
4.循环语句(重点掌握)
select * from student in(1,2,3);
1.批量查询
2.批量删除
3.动态更新(map)
5.用于构建变量
其主要用于模糊查询当中
concat()函数
if标签的应用:
<select id="findByStu1" parameterType="string" resultMap="baseResultMap">
select * from student where 1=1
<if test="userName !=null and userName !=''">
and user_name =#{userName}
</if>
<if test="password !=null and password !=''">
and password =#{password}
</if>
</select>
where标签:
作用解决where 1=1 的问题
如果where标签后面的字符串是以and或or开头就移除and或or
<select id="findByStu2" parameterType="string" resultMap="baseResultMap">
select * from student
<where>
<if test="userName !=null and userName !=''">
and user_name =#{userName}
</if>
<if test="password !=null and password !=''">
and password =#{password}
</if>
</where>
</select>
set标签
作用:移除更新条件后面的","
update student set user_name=?,password=? where id=?
<update id="updateByStu1" parameterType="student">
update student
<set>
<if test="userName !=null and userName !=''">
user_name=#{userName},
</if>
<if test="password !=null and password!=''">
password=#{password},
</if>
</set>
where id=#{id}
</update>
trim标签
where标签和set标签的功能都可以使用trim标签替代
where标签对应的trim标签的实现
prefix:当trim标签中包含内容时,会给内容添加prefix指定的前缀内容
prefixOverrides:把内容中匹配的前缀字符串移除
<select id="findByStu" parameterType="string" resultMap="baseResultMap">
select * from student
<trim prefix="where" prefixOverrides="and">
<if test="userName !=null and userName !=''">
and user_name =#{userName}
</if>
<if test="password !=null and password !=''">
and password =#{password}
</if>
</trim>
</select>
<update id="updateByStu" parameterType="student">
update student
<trim prefix="set" suffixOverrides=",">
<if test="userName !=null and userName !=''">
user_name=#{userName},
</if>
<if test="password !=null and password!=''">
password=#{password},
</if>
</trim>
where id=#{id}
</update>
foreach批量查询
select * from student where id in(4,5,6)
foreach包含的属性:collection:必填属性,值为迭代循环的属性名
item:变量名,值为迭代对象中取出的每一个值
index:索引,如果是map类型时,值为map的key值
open:循环开始的字符串
close:循环结束的字符串
如果传递的值是集合:list
<select id="selectByIds" parameterType="Integer" resultMap="baseResultMap">
select * from student where id in
<foreach collection="list" open="(" close=")" separator="," item="id">
#{id}
</foreach>
</select>
如果传递的类型是数组 array
select * from student where id in #{id}
<delete id="deleteByIds" parameterType="Integer">
delete from student where id in
<foreach collection="list" open="(" close=")" separator="," item="id">
#{id}
</foreach>
</delete>
<update id="updateByMap">
update student
<set>
<foreach collection="map" index="key" separator="," item="val">
${key} = #{val}
</foreach>
</set>
where id=#{map.id}
</update>
select * from student where user_name like ‘%${username}%’
不能防止sql注入
select * from student where user_name like concat(’%’,#{username},’%’)
mysql数据库才有效
:
<select id="findByLikename" parameterType="string" resultMap="baseResultMap">
select * from student
<where>
<if test="username !=null and username !=''">
<bind name="name" value="'%'+username+'%'"></bind>
and user_name like #{name}
</if>
</where>
</select>