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 參數(shù)
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');
參數(shù)是 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 一個表任意一列的數(shù)據(jù)時，不需要這樣寫：
@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」的原創(chuàng)文章，遵循 CC 4.0 BY-SA 版權協(xié)議，轉載請附上原文出處鏈接及本聲明。
原文鏈接：https://blog.csdn.net/daqiang012/article/details/105373611
