MyBatis 的真正强大在于它的语句映射，这是它的魔力所在。 如果跟JDBC 代码进行对比，省掉了将近 95% 的代码。
1 select
CREATE TABLE `person` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(100) DEFAULT NULL,
`password` varchar(100) DEFAULT NULL,
`full_name` varchar(100) DEFAULT NULL,
`first_name` varchar(100) DEFAULT NULL,
`last_name` varchar(100) DEFAULT NULL,
`date_of_birth` date DEFAULT NULL,
`created_on` date DEFAULT NULL,
`update_on` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=211 DEFAULT CHARSET=utf8;
insert into `person`(`id`,`username`,`password`,`full_name`,`first_name`,`last_name`,`date_of_birth`,`created_on`,`update_on`) values (201,'emacarron','123456','爱德华多·马卡龙','爱德华多','马卡龙','2000-01-01','2020-01-01','2020-01-02'),(202,'mnesarco','123456','弗兰克·马丁内斯','弗兰克','马丁内斯','2000-01-01','2020-01-01','2020-01-02'),(203,'agustafson','123456','安德鲁·古斯塔夫森','安德鲁','古斯塔夫森','2000-01-01','2020-01-01','2020-01-02');
<select id="selectPerson" parameterType="int" resultType="hashmap">
SELECT * FROM PERSON WHERE ID = #{id}
</select>
<select
id="selectPerson"
parameterType="int"
parameterMap="deprecated"
resultType="hashmap"
resultMap="personResultMap"
flushCache="false"
useCache="true"
timeout="10"
fetchSize="256"
statementType="PREPARED"
resultSetType="FORWARD_ONLY">
2 insert, update 和 delete
CREATE TABLE `author` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`username` VARCHAR(100) DEFAULT NULL,
`password` VARCHAR(100) DEFAULT NULL,
`email` VARCHAR(100) DEFAULT NULL,
`bio` VARCHAR(100) DEFAULT NULL,
`favourite_section` VARCHAR(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=311 DEFAULT CHARSET=utf8;
INSERT INTO `author`(`id`,`username`,`password`,`email`,`bio`,`favourite_section`) VALUES (301,'克林顿','123456',' clinton.begin@gmail.com','MyBatis团队成员','打球'),(302,'布兰登','123456',' brandon.goodin@gmail.com','MyBatis团队成员','听歌'),(303,'亚当','123456','adam.gent@evocatus.com','MyBatis团队贡献者','游泳');
<insert
id="insertAuthor"
parameterType="org.mybatis.example.Author"
flushCache="true"
statementType="PREPARED"
keyProperty=""
keyColumn=""
useGeneratedKeys=""
timeout="20">
<update
id="updateAuthor"
parameterType="org.mybatis.example.Author"
flushCache="true"
statementType="PREPARED"
timeout="20">
<delete
id="deleteAuthor"
parameterType="org.mybatis.example.Author"
flushCache="true"
statementType="PREPARED"
timeout="20">
<insert id="insertAuthor">
insert into Author (id,username,password,email,bio)
values (#{id},#{username},#{password},#{email},#{bio})
</insert>
<update id="updateAuthor">
update Author set
username = #{username},
password = #{password},
email = #{email},
bio = #{bio}
where id = #{id}
</update>
<delete id="deleteAuthor">
delete from Author where id = #{id}
</delete>
<insert id="insertAuthor" useGeneratedKeys="true"
keyProperty="id">
insert into Author (username,password,email,bio)
values (#{username},#{password},#{email},#{bio})
</insert>
SQL
这个元素可以用来定义可重用的 SQL 代码片段，以便在其它语句中使用。
<sql id="userColumns"> ${alias}.id,${alias}.username,${alias}.password </sql>
<select id="selectUsers" resultType="map">
select
<include refid="userColumns"><property name="alias" value="t1"/></include>,
<include refid="userColumns"><property name="alias" value="t2"/></include>
from author t1
cross join author t2
</select>
<select id="selectUsers" resultType="map">
select
<include refid="userColumns"><property name="alias" value="t1"/></include>,
<include refid="userColumns"><property name="alias" value="t2"/></include>
from author t1
cross join author t2
</select>
3 参数
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(100) DEFAULT NULL,
`password` varchar(100) DEFAULT NULL,
`email` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=411 DEFAULT CHARSET=utf8;
insert into `users`(`id`,`username`,`password`,`email`) values (401,'admin','123456','admin@@gmail.com'),(402,'user','123456','user@gmail.com'),(403,'guest','123456','guest@gmail.com');
参数是 MyBatis 非常强大的元素。比如：
<select id="selectUsers" resultType="User">
select id, username, password
from users
where id = #{id}
</select>
如果传入一个复杂的对象，就会有点不一样了。比如：
<insert id="insertUser" parameterType="User">
insert into users (id, username, password)
values (#{id}, #{username}, #{password})
</insert>
字符串替换
有时想在 SQL 语句中直接插入一个不转义的字符串。 比如 ： ORDER BY ${columnName}
举个例子，如果你想 select 一个表任意一列的数据时，不需要这样写：
@Select("select * from users where id = #{id}")
User findById(@Param("id") long id);
@Select("select * from users where username= #{username}")
User findByUsername(@Param("username") String username);
@Select("select * from users where email = #{email}")
User findByEmail(@Param("email") String email);
而是可以只写这样一个方法：
@Select("select * from users where ${column} = #{value}")
User findByColumn(@Param("column") String column, @Param("value") String value);
其中 ${column} 会被直接替换，而 #{value} 会使用 ? 预处理。
User userOfId = userMapper.findByColumn("id", 401L);
User userOfUsername = userMapper.findByColumn("username", "guest");
User userOfEmail = userMapper.findByColumn("email", "guest@gmail.com");
————————————————
版权声明：本文为CSDN博主「daqiang012」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/daqiang012/article/details/105373611
