MyBatis的XML语法的常用用法
MyBatis的XML语法的常用用法
项目开发中，经常遇到哪个写法忘记了，找之前写的找了好半天。所以，都累积在这，找的时候好找，持续更新~~
mysql数据库
一、数据库层面
<!--
大于：>
小于：<
大于等于：>=
小于等于：<=
-->
<!-- 查询是否有某个表，参数里面加% 就是查询表头一致的表名-->
<select id="isHaveTable" resultType="java.lang.String" parameterType="java.lang.String">
SHOW TABLES LIKE '${tableName}';
</select>
<!-- 删除某个表-->
<update id="deleteTable">
DROP TABLE `${tableName}`
</update>
<!-- 清空某个表-->
<update id="truncateTable">
truncate table `${tableName}`
</update>
<!-- 创建表-->
<update id="createTableForCirdataAutoDefinefieldValue">
CREATE TABLE `${tableName}` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`col1` int(11) NULL DEFAULT NULL COMMENT '备注',
`col2` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
`col3` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`id`) USING BTREE
,INDEX `index_col1` (`col1`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
</update>
二、增加相关
<!-- 增加 -->
<insert id="addTestTable" parameterType="java.util.Map" keyProperty="id" useGeneratedKeys="true" >
insert into test_table ( col1, col2,col3)
VALUES
( #{col1}, #{col2}, #{col3} )
</insert>
<!--批量添加数据到固定表,不需要获取自增的id-->
<insert id="addBacth" parameterType="java.util.List">
insert into table (col1,col2,col3) values
<foreach collection="list" item="item" separator=" , " open="" close="">
( #{item.col1},#{item.col2},#{item.col3})
</foreach>
</insert>
<!-- 批量添加指定表：需要把自增的id带回List<****Entity> data中-->
<!-- mapper接口对应的方法 void addBacthDynamic(@Param("data") List<****Entity> data);-->
<insert id="addBacthDynamic" parameterType="com.***.***.****Entity" keyProperty="id" useGeneratedKeys="true" >
insert into table (col1,col2,col3) values
<foreach collection="data" item="item" separator=" , " open="" close="">
( #{item.col1},#{item.col2},#{item.col3})
</foreach>
</insert>
<!-- 批量添加动态表:需要把自增的id带回List<****Entity> data中，注意keyProperty的写法-->
<!-- mapper接口对应的方法 void addBacthDynamic(@Param("tableName") String tableName, @Param("data") List<****Entity> data);-->
<insert id="addBacthDynamic" parameterType="com.***.***.****Entity" keyProperty="data.id" useGeneratedKeys="true" >
insert into ${tableName} (col1,col2,col3) values
<foreach collection="data" item="item" separator=" , " open="" close="">
( #{item.col1},#{item.col2},#{item.col3})
</foreach>
</insert>
三、修改和删除相关
<!-- 修改 -->
<update id = "updateTestTable" parameterType="java.util.Map">
update test_table set
col1=#{col1},
col2=#{col2},
col3=#{col3}
where id=#{id}
</update>
<!-- 修改，带条件判断的 -->
<update id = "updateTestTable" parameterType="java.util.Map">
update test_table set
<if test="col1!= null and col1!= '' and col1!= 'null' ">
col1=#{col1},
</if>
<if test="col2!= null and col2!= '' and col2!= 'null' ">
col2=#{col2},
</if>
<if test="col3!= null and col3!= '' and col3!= 'null' ">
col3=#{col3},
</if>
id=#{id}
where id=#{id}
</update>
<!-- 修改表 ,某个字段修改的时候和之前的值拼接起来-->
<update id="updateFinalPlan" parameterType="java.util.Map">
update `${tableName}` set
<if test="col1!= null and col1!= '' and col1!= 'null' ">
col1=#{col1},
</if>
<if test="col2!= null and col2!= '' and col2!= 'null' ">
col2=#{col2},
</if>
<if test="col3!= null and col3!= '' and col3!= 'null' ">
col3=CONCAT(col3,#{col3}),
</if>
id=#{id}
where id=#{id}
</update>
<!-- 删除 -->
<delete id="deleteTestTable" parameterType="java.util.Map">
delete from test_table where id=#{id}
</delete>
四、查询相关
4.1 不使用resultMap
<select id="findAllByPage" parameterType="java.util.Map" resultType="java.util.Map">
select * from test_table where 1=1
<if test="col1!= null and col1!= '' and col1!= 'null' ">
and col1=#{col1},
</if>
<if test="col2!= null and col2!= '' and col2!= 'null' ">
and col2=#{col2},
</if>
<if test="col3!= null and col3!= '' and col3!= 'null' ">
and col3=#{col3},
</if>
limit #{start}, #{pageSize}
</select>
<!-- 分表之后的查询 -->
<select id="findDynamicData" parameterType="java.util.Map" resultType="java.util.Map">
select * from (
<foreach collection="tableNameList" item="tableName" separator=" union all " open="" close="">
(
select * , '${tableName}' as table_name
from ${tableName} where 1=1
<if test="col1!= null and col1!= '' and col1!= 'null' ">
col1=#{col1},
</if>
<if test="col2!= null and col2!= '' and col2!= 'null' ">
col2=#{col2},
</if>
<if test="ids != null ">
and other_id in
<foreach collection="ids" item="item" separator=" , " open=" (" close=")">
#{item}
</foreach>
</if>
)
</foreach>
) as alltable
limit #{start}, #{pageSize}
</select>
<!-- 多个条件判断如下 ,-->
<!-- CASE WHEN SCORE = 'A' THEN '优'
WHEN SCORE = 'B' THEN '良'
WHEN SCORE = 'C' THEN '中' ELSE '不及格' END-->
<!-- 查询结果带拼接的，外连接指示是否有的 ,-->
<select id="findAll" parameterType="java.util.Map" resultType="java.util.Map">
select *
from (
select IFNULL(T16.col11 ,0) ttttt1, ,T1.*,
IFNULL(DATEDIFF(T2.col5 ,T2.col4 ),-999) `day`
,case when T2.id is null then '0' else '1' end as have_col
, CONCAT(T3.col5 ,'-',T1.col1 ) as ttttt2
from (
table1 T1 left join table2 T2 on T1.id= T9.id
where 1=1
<if test='col1== "1" '>
and ifnull(col1,-1) = -1
</if>
<if test="col2 != null and col2 != '' and col2 != 'null' ">
and DATEDIFF(T2.col3 ,T2.col2 ) >= #{col2 }
</if>
<if test="col5 != null and col5 != '' and col5 != 'null' ">
and T6.col5 like concat ('%', #{col5 } ,'%')
</if>
order by `day` desc
) aaaa
where 1=1
limit #{start}, #{pageSize}
</select>
4.2 使用resultMap
1、自定义实体类
<!-- 主的-->
<resultMap id="info" type="com.***.***.***Entity">
<id property="id" column="id"/>
<result property="col1" column="col1"/>
<result property="col2" column="col2"/>
<collection property="childList" resultMap="childs"/>
</resultMap>
<!-- 字集，用id列来区分是否唯一 -->
<resultMap id="childs" type="com.***.***.***Entity">
<id property="lid" column="lid"/>
<result property="col1" column="col1"/>
<result property="col2" column="col2"/>
</resultMap>
<select id="getList" parameterType="java.util.Map" resultMap="info">
select T1.*,T2.*
from table1 T1
left join table1 T2 on T2.id=T1.id
</select>
2、用Map的方式
<!-- childList属性，要加上javaType，这样才是一个集合-->
<resultMap id="info" type="java.util.Map">
<id property="id" column="id"/>
<result property="col1" column="col1"/>
<result property="col2" column="col2"/>
<collection property="childList" javaType="java.util.ArrayList" resultMap="childs"/>
</resultMap>
<!-- child_id来唯一区分-->
<resultMap id="childs" type="java.util.Map">
<id property="child_id" column="child_id"/>
<result property="col1" column="col1"/>
</resultMap>
<select id="getList" parameterType="java.util.Map" resultMap="info">
select T1.*,T2.*
from table1 T1
left join table1 T2 on T2.id=T1.id
</select>
4.3 模糊查询的写法
1、mysql的方式
where 1=1
<if test="name != null ">
and name like concat ( '%',#{name},'%')
</if>
2、sqlite的方式
where 1=1
<if test="name != null ">
and name like '%'||#{name}||'%'
</if>
五、新增修改合并一起写：如果传入的map里面，不存在id属性则新增，存在则修改
<insert id="addOrUpdate" parameterType="java.util.Map" keyProperty="id" useGeneratedKeys="true">
INSERT INTO table(id,col1,col2)
VALUES (#{id},#{col1},#{col2})
ON DUPLICATE KEY UPDATE
col1= #{col1},col2=#{col2}
</insert>
sqlite3数据库
一、设置
如果不用springboot的方式，需要设置下面这些
mybatis-config.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>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="org.sqlite.JDBC"/>
<property name="url" value="jdbc:sqlite:data.db"/>
</dataSource>
</environment>
</environments>
<!-- 将包内的映射器接口实现全部注册为映射器 -->
<mappers>
<package name="com.**.****.mapper"/>
</mappers>
</configuration>
pom文件的build如下
<build>
<resources>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
<include>readme.txt</include>
</includes>
<filtering>true</filtering>
</resource>
<resource>
<directory>src/main/java/</directory>
<includes>
<include>**/*.xml</include>
</includes>
<filtering>true</filtering>
</resource>
</resources>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>1.8</source>
<target>1.8</target>
</configuration>
</plugin>
</plugins>
</build>
DataSourceUtil数据库连接工具
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
/**
* @Auther: wd
*/
public class DataSourceUtil {
private static SqlSessionFactory ssf;
private static SqlSessionFactory getSsf() {
if (ssf == null) {
synchronized (DataSourceUtil.class) {
if (ssf == null) {
try {
String resource = "mybatis-config.xml";
InputStream inputStream = null;
inputStream = Resources.getResourceAsStream(resource);
ssf = new SqlSessionFactoryBuilder().build(inputStream);
} catch (Exception e) {
System.out.println("设置数据库打开属性失败：" + e.getMessage());
Log.error("【数据库】：设置数据库打开属性失败",e.getMessage());
ssf = null;
}
}
}
}
return ssf;
}
public static SqlSession getSqlSession() {
try {
return getSsf().openSession();
} catch (Exception e) {
Log.error("【数据库】：打开SqlSession异常",e.getMessage());
return null;
}
}
}
mapper接口
import java.util.Map;
public interface DataMapper {
Integer add(Map data);
}
mapper和xml放一起
service类
/**
* @Auther: wd
*/
public class DataService {
public boolean add(Map data) {
SqlSession session = DataSourceUtil.getSqlSession();
try {
DataMapper mapper = session.getMapper(DataMapper.class);
Integer add = mapper.add(data);
session.commit();
if (add == 0) {
return false;
} else {
return true;
}
} catch (Exception e) {
Log.error("【写数据库】添加数据时异常，数据是" + data);
Log.error(e.getMessage());
return false;
} finally {
session.close();
}
}
}
二、新增，修改，删除，查询，基本都与mysql一样
三、不一样的
1.多表查询:注意生产的sql语句union all前后是不带()的
<select id="findData" parameterType="java.util.Map" resultType="java.util.Map">
select * from (
<foreach collection="tableNameList" item="tableName" separator=" union all " open="" close="">
select * from ${tableName} where 1=1
<if test="col1!= null ">
and col1= #{col1}
</if>
<if test="col2!= null ">
and col2= #{col2}
</if>
</foreach>
) as alltable
</select>
2.创建表时，索引需要单独增加
<!-- 创建表 -->
<update id="createTableForData" parameterType="java.lang.String">
CREATE TABLE ${tableName} (
"id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
"col1" integer,
"col2" integer,
"col3" text
);
</update>
<!-- 创建索引的 SQL 语句 -->
<update id="createIndex" parameterType="java.lang.String">
CREATE INDEX "index_col1_col2"
ON ${tableName} (
"col1" ASC,
"col2" ASC
);
</update>
3.查询有没有相关的表名
<!-- 有没有指定的表名 -->
<select id="isHavingTable" resultType="java.lang.Integer" parameterType="java.lang.String">
select count(*) from sqlite_master where name=#{tableName}
</select>
<!-- 查询title开头相关的表名 -->
<select id="findDataTableNames" resultType="java.lang.String">
select name from sqlite_master where type='table' and name like 'title%' order by name
</select>
扫一扫
942
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
