动态sql
1、动态sql-if
需求:
1、如果输入name则根据年龄对name进行模糊查询
2、如果未输入name则只根据年龄查询
sql语句形式:
1、输入name
select*from test1 where age = #{age} and name = #{name}
2、不输入name
select*from test1 where age = #{age}
使用动态sql
<select id="selectTest" resultMap="TestResultMap">
select*from test1 where age=#{age}
<if test="name != null and name != ''">
and name like '%${name}%'
</if>
</select>
2、动态sql-choose、when、otherwise
第一个when满足,则使用第一个不走下一步,否则依次向下查询
需求:
1、通过指定name来查询
2、若name为空则通过age来查询
配置文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"
"http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">
<mapper namespace="com.my.test.Test1Mapper">
<resultMap type="Test1" id="TestResultMap"></resultMap>
<select id="selectTest" parameterType="Test1" resultMap="TestResultMap">
select*from test1 where
<choose>
<when test="name != null and name != ''">
name = #{name}
</when>
<otherwise>
age = #{age}
</otherwise>
</choose>
</select>
</mapper>
3、动态sql-where
和choose when otherwise的区别在于,如果给条件设置值,则必须满足所有条件
**需求:**根据name、age进行多条件查询
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"
"http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">
<mapper namespace="com.my.test.Test1Mapper">
<resultMap type="Test1" id="TestResultMap"></resultMap>
<select id="selectTest" parameterType="Test1" resultMap="TestResultMap">
select*from test1
<where>
<if test="name != null and name != ''">
name = #{name}
</if>
<if test="age != null and age != ''">
and age like #{age}
</if>
</where>
</select>
</mapper>
4、动态sql-set
set只会修改满足条件的值,其余的值不会改变,注意加逗号
需求: 通过指定id进行数据update
配置文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"
"http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">
<mapper namespace="com.my.test.Test1Mapper">
<resultMap type="Test1" id="TestResultMap"></resultMap>
<update id="updateTestBySet" parameterType="Test1">
update
`test1`
<set>
<if test="name!=null">
`name` = #{name},
</if>
<if test="age!=null">
`age` = #{age}
</if>
</set>
where `id` = #{id}
</update>
</mapper>
5、动态sql-trim
prefix:在trim标签内sql语句加上前缀。
suffix:在trim标签内sql语句加上后缀。
suffixOverrides:指定去除多余的后缀内容,如:suffixOverrides=",",去除trim标签内sql语句多余的后缀","。
prefixOverrides:指定去除多余的前缀内容
trim替换where-if
a、where-if
<select id="selectTest" parameterType="Test1" resultMap="TestResultMap">
select*from test1
<where>
<if test="name != null and name != ''">
name = #{name}
</if>
<if test="age != null and age != ''">
and age like #{age}
</if>
</where>
</select>
b、trim
<select id="selectTestByTrim" parameterType="Test1" resultMap="TestResultMap">
select*from test1
<trim prefix="where" prefixOverrides="and | or">
<if test="name != null and name != ''">
name = #{name}
</if>
<if test="age != null and age != ''">
and age like #{age}
</if>
</trim>
</select>
trim替换set
a、set
<update id="updateTestBySet" parameterType="Test1">
update
`test1`
<set>
<if test="name!=null">
`name` = #{name},
</if>
<if test="age!=null">
`age` = #{age}
</if>
</set>
where `id` = #{id}
</update>
b、trim
<update id="updateTestBySet" parameterType="Test1">
update
`test1`
<trim prefix="set" suffixOverrides=",">
<if test="name!=null">
`name` = #{name},
</if>
<if test="age!=null">
`age` = #{age}
</if>
</trim>
where `id` = #{id}
</update>
6、动态sql-foreach
删除配置
<delete id="deleteTest1ByForeach" parameterType="list">
delete from test1 where id in
<foreach collection="list" item="item" open="(" close=")" separator=",">
#{item}
</foreach>
</delete>
7、sql片段
<sql id="mySql">
`id`,
`name`,
`age`
</sql>
<insert id="insertTest" parameterType="Test1">
insert into `test1`(
<include refid="mySql"/>
)values(
#{id},
#{name},
#{age}
)
</insert>
逆向工程
*Mybaits官方提供逆向工程,根据数据库表结构生成mapper.xml和mapper.java及pojo类。
*生成的mapper具备了单表的增、删、改、查的功能。
*使用java程序+xml配置执行逆向工程。
1、依赖
<dependency>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-core</artifactId>
<version>1.3.2</version>
</dependency>
2、编写相应的配置文件–generator.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>
<context id="DB2Tables" targetRuntime="MyBatis3">
<!-- 连接数据库 -->
<jdbcConnection driverClass="com.mysql.cj.jdbc.Driver"
connectionURL="jdbc:mysql://localhost:3306/my_test?serverTimezone=GMT%2B8"
userId="root"
password="666666">
</jdbcConnection>
<javaTypeResolver >
<property name="forceBigDecimals" value="false" />
</javaTypeResolver>
<!-- targetPackage:pojo文件位置 -->
<javaModelGenerator targetPackage="com.dhcc.my.pojo" targetProject="src/main/java">
<property name="enableSubPackages" value="true" />
<property name="trimStrings" value="true" />
</javaModelGenerator>
<!-- targetPackage:mapper映射文件位置 -->
<sqlMapGenerator targetPackage="com.dhcc.my.mapper" targetProject="src/main/resources">
<property name="enableSubPackages" value="true" />
</sqlMapGenerator>
<!-- targetPackage:mapper接口文件位置 -->
<javaClientGenerator type="XMLMAPPER" targetPackage="com.dhcc.my.mapper" targetProject="src/main/java">
<property name="enableSubPackages" value="true" />
</javaClientGenerator>
<!-- 表名 -->
<table schema="" tableName="test1"></table>
</context>
</generatorConfiguration>
3、编写java代码
public static void main(String[] args) {
try {
List<String> warnings = new ArrayList<String>();
boolean overwrite = true;
String filePath = Test1.class.getClassLoader().getResource("generator.xml").getFile();
File configFile = new File(filePath);
ConfigurationParser cp = new ConfigurationParser(warnings);
Configuration config = cp.parseConfiguration(configFile);
DefaultShellCallback callback = new DefaultShellCallback(overwrite);
MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config, callback, warnings);
myBatisGenerator.generate(null);
} catch (Exception e) {
e.printStackTrace();
}
}
4、手动创建相应的目录及包
5、运行主方法生成相关文件
6、将文件移植到项目中
也可直接在配好的文件中加入
a、tools工具类
b、db.properties
c、log4j.properties
d、mybatis-config.xml
7、注意
Mapper.xml文件已经存在时,重新生成后xml文件的内容不被覆盖而是进行内容追加,结果导致mybatis解析失败。
解决方法:删除原来已经生成的mapper xml文件再进行生成。
Mybatis自动生成的po及mapper.java文件不是内容而是直接覆盖没有此问题。