环境配置
参考官网:入门_MyBatis中文网
-
创建一个空的maven项目
-
在pom.xml文件添加mybatis,mysql,junit等依赖
<!--mybatis依赖--> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.5</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.32</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.13</version> <scope>test</scope> </dependency>
-
在IDEA中安装MybatisX插件
-
创建mybatis配置文件:mybatis-config.xml,并且放入resource目录
<?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> <!--配置类型别名(要放到最前面),引入pojo包内的文件时只需要输入名字即可--> <typeAliases > <package name="com.xzh.pojo"/> </typeAliases> <!--环境配置,可以连接多个数据库--> <environments default="development"> <!--mysql-jdbc环境配置--> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql:///mybatis?useSSL=false"/> <property name="username" value="root"/> <property name="password" value="512512"/> </dataSource> </environment> </environments> <mappers> <!--加载映射文件--> <!--<mapper resource="com/xzh/mapper/UserMapper.xml"/>--> <!--如果Mapper接口名称和SQL映射文件名称相同,并在同一目录下,则可以使用包扫描的方式简化SQL映射文件的加载--> <package name="com.xzh.mapper"/> </mappers> </configuration>
-
创建pojo包,创建User类并且放入
-
使用mapper进行代理开发
1.在resource文件夹内创建和pojo包前缀相同的目录,如(com.xzh.pojo包),目录为com\xzh\ 2.在resource文件夹创建com\xzh\mapper目录,创建UserMapper.xml,存放sql语句 <mapper namespace="com.xzh.mapper.UserMapper"> <select id="selectAll" resultType="User"> select * from tb_user; </select> </mapper> 3.在java文件夹创建com.xzh.mapper包,创建同名的UserMapper接口 public interface UserMapper { List<User> selectAll(); } 4.配置好后,对应接口和sql语句位置会出现蓝红鸟对应(mybatisx插件功能),这样就配置好了
-
在测试类里面创建com.xzh.test包,创建对应测试类。
@Test public void testSelectAll() throws IOException { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); SqlSession sqlSession = sqlSessionFactory.openSession(); BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class); List<Brand> brands = brandMapper.selectAll(); System.out.println(brands); sqlSession.close(); }
-
如果数据库字段和pojo里面定义类的字段不一致,需要用resultMap进行封装
<mapper namespace="com.xzh.mapper.BrandMapper"> <!--column是数据库字段的名字,property是pojo内封装的名字--> <resultMap id="brandResultMap" type="brand"> <result column="brand_name" property="brandName"></result> <result column="company_name" property="companyName"></result> </resultMap> <select id="selectAll" resultMap="brandResultMap"> select * from tb_brand; </select> </mapper>
实现演示
查询-查询所有数据
-
编写接口方法,然后按alt+enter会自动跳转到sql语句文件(mybatisx插件的功能)
List<Brand> selectAll();
-
在配置文件内编写sql语句,如果数据库字段和pojo里面定义类的字段不一致,需要用resultMap进行封装
<resultMap id="brandResultMap" type="brand"> <result column="brand_name" property="brandName"></result> <result column="company_name" property="companyName"></result> </resultMap> <select id="selectAll" resultMap="brandResultMap"> select * from tb_brand; </select>
-
执行方法,测试
查询-根据id查询
sql映射文件:
<select id="selectById" resultMap="brandResultMap">
select * from tb_brand where id = #{id};
</select>
参数占位符
- #{}:执行SQL时,会将#{}占位符替换为? 将来自动设置参数值
- ${}:拼SQL。会存在SQL注入问题
使用时机:
- 参数传递,都使用#{}
- 如果要对表名、列名进行动态设置,只能使用${}进行sql拼接
特殊字符的处理
在xml内如果要输入小于符合会引起冲突,因此使用以下方法解决:
- 转义字符,使用html内的特殊字符
- <![CDATA[ 内容 ]]>:输入CD有提示(常用)
查询-多条件查询
sql映射文件
<!--动态sql写法 -->
<select id="selectByCondition" resultMap="brandResultMap">
select * from tb_brand
<where>
<if test="status != null">
and status = #{status}
</if>
<if test="brandName != null and brandName != ''">
and brand_name like #{brandName}
</if>
<if test="companyName != null and companyName != ''">
and company_name like #{companyName}
</if>
</where>
</select>
多参数设置的方法
-
散装参数:需要用@Param(“SQL中的参数名称”) *使用麻烦
List<Brand> selectByCondition(@Param("status") int status, @Param("companyName") String companyName, @Param("brandName") String brandName);
-
实体类封装参数 *只需要保证SQL中的参数名和实体类属性名对应上就行
List<Brand> selectByCondition(Brand brand);
@Test public void testSelectByCondition() throws IOException { int status = 1; String companyName = "华为"; String brandName = "华为"; //模糊查询需要用% companyName = "%" + companyName + "%"; brandName = "%" + brandName + "%"; Brand brand = new Brand(); brand.setStatus(status); brand.setCompanyName(companyName); brand.setBrandName(brandName); String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); SqlSession sqlSession = sqlSessionFactory.openSession(); BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class); List<Brand> brands = brandMapper.selectByCondition(brand); System.out.println(brands); sqlSession.close(); }
-
map集合 *只需要保证SQL中的参数名和map集合的键对的上即可
List<Brand> selectByCondition(Map<String,String> map);
@Test public void testSelectByCondition() throws IOException { int status = 1; String companyName = "华为"; String brandName = "华为"; companyName = "%" + companyName + "%"; brandName = "%" + brandName + "%"; Map<String, String> map = new HashMap<>(); map.put("status", status + ""); map.put("companyName", companyName); map.put("brandName", brandName); String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); SqlSession sqlSession = sqlSessionFactory.openSession(); BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class); List<Brand> brands = brandMapper.selectByCondition(map); System.out.println(brands); sqlSession.close(); }
动态sql
where关键字
以下语句如果用户只给了brandName的值 那么语句就会出错(where and brand_name like #{brandName})
<select id="selectByCondition" resultMap="brandResultMap">
select * from tb_brand
where
status = #{status}
and brand_name like #{brandName}
and company_name like #{companyName};
</select>
解决方案:
-
使用恒等式让所有条件格式都一样(where 1=1) *旧方案
-
标签替换 where 关键字
<select id="selectByCondition" resultMap="brandResultMap"> select * from tb_brand <where> <if test="status != null"> and status = #{status} </if> <if test="brandName != null and brandName != ''"> and brand_name like #{brandName} </if> <if test="companyName != null and companyName != ''"> and company_name like #{companyName} </if> </where> </select>
choose关键字
<select id="selectByConditionSingle" resultMap="brandResultMap">
select * from tb_brand
<!--用where包起来,如果没有查询条件就会自动去掉where关键字-->
<where>
<choose><!--类似switch-->
<when test="status != null"> <!--类似case-->
status = #{status}
</when>
<when test="brandName != null and brandName != ''">
brand_name like #{brandName}
</when>
<when test="companyName != null and companyName != ''">
company_name like #{companyName}
</when>
</choose>
</where>
</select>
添加-添加单条数据
1.编写Mapper接口方法
void add(Brand brand);
2.编写SQL语句:SQL映射文件
<insert id="add">
insert into tb_brand(brand_name, company_name, ordered, description, status)
VALUES(#{brandName},#{companyName},#{ordered},#{description},#{status});
</insert>
3.编写测试用例
注意:mybatis默认关闭自动提交,如果需要添加语句生效,需要手动提交事务(sqlSession.commit();)或者设置为自动提交事务(openSession(true))
主键返回
当添加操作完成后,返回主键id值以便于后续关联操作
返回的id值会封装到传入的Brand类内
<insert id="add" useGeneratedKeys="true" keyProperty="id">
insert into tb_brand(brand_name, company_name, ordered, description, status)
VALUES (#{brandName}, #{companyName}, #{ordered}, #{description}, #{status});
</insert>
@Test
public void testAdd() throws IOException {
String brandName = "飒飒是";
String companyName = "电视动画";
int ordered = 10;
String description = "sdsdssds";
int status = 1;
Brand brand = new Brand();
brand.setStatus(status);
brand.setCompanyName(companyName);
brand.setBrandName(brandName);
brand.setDescription(description);
brand.setOrdered(ordered);
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
brandMapper.add(brand);
System.out.println("add done id="+brand.getId());
sqlSession.commit();
sqlSession.close();
}
修改-动态修改
使用配合完成动态修改,因为不用动态set的话,如果只修改单个参数,那么sql语句就会出错(会多出逗号)
<update id="update">
update tb_brand
<set>
<if test="brandName != null and brandName != ''">
brand_name = #{brandName},
</if>
<if test="companyName != null and companyName != ''">
company_name = #{companyName},
</if>
<if test="ordered != null">
ordered = #{ordered},
</if>
<if test="description !=null and description != '' ">
description = #{description},
</if>
<if test="status != null">
status = #{status},
</if>
</set>
where id = #{id};
</update>
删除-批量删除
接口写法:使用注解把主键名称改成ids,以便于后续调用
void deleteByIds(@Param("ids") int[] ids);
SQL:使用遍历数据,并且将数据动态拼接
<delete id="deleteByIds">
delete from tb_brand
where id
in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>
Mybatis底层参数封装
*单个参数
-
pojo类:直接使用,只需让 属性名 和 占位参数名称 一致。
-
Map集合:直接使用,只需让 键名 和 占位参数名称 一致。
-
Collection:封装为Map集合
map.put(“arg0”,collection集合);
map.put(“collection”,collection集合);
-
List:
map.put(“arg0”,list集合);
map.put(“collection”,list集合);
map.put(“list”,list集合);
-
Array:
map.put(“arg0”,array数组);
map.put(“array”,array数组);
-
其他类型:int char 等 ,直接使用
*多个参数:封装为Map集合,可以使用@Param()注解替换map中默认的arg键名
map.put(“arg0”,参数1);
map.put(“param1”,参数1);
map.put(“arg1”,参数2);
map.put(“param2”,参数2);
-----------使用@Param(“username”) 注解后
map.put(“username”,参数1);
map.put(“param1”,参数1);
map.put(“arg1”,参数2);
map.put(“param2”,参数2);
注解SQL语句
提示:注解完成简单sql 配置文件完成复杂sql
@Select(value="select * from tb_user where id=#{id}")
public User select(int id)