三天撸完了MyBatis，各位随便问！！（冰河吐血整理，建议收藏）
MyBatis-Plus，为简化开发而生，基于3.0.5版本
基于 SpringBoot + MyBatis 的在线五子棋对战
MyBatis使用步骤总结
- 配置mybatis-config.xml 全局的配置文件 (1、数据源，2、外部的mapper)
- 创建XxxMapper.xml 映射文件(写sql语句)
- 创建SqlSessionFactory
- 通过SqlSessionFactory创建SqlSession对象
- 通过SqlSession操作数据库 CRUD
- 调用session.commit()提交事务
- 调用session.close()关闭会话
Mybatis的功能相对而言还是比较弱的，缺少了好多必要的辅助库，字符串处理等等，扩展也比较困难，一般也就可能对返回值进行一些处理。因此最好仅仅把它作为单纯的SQL配置文件，以及简单的ORM框架。不要尝试在Mybatis中做过多的动态SQL，否则会导致后续的维护非常恶心。到这就要知道一下，Mybatis plus 不香吗它是在 MyBatis 的基础上只做增强不做改变，为简化开发、提高效率而生。
1.特性
无侵入：只做增强不做改变，引入它不会对现有工程产生影响，如丝般顺滑
损耗小：启动即会自动注入基本 CURD，性能基本无损耗，直接面向对象操作
强大的 CRUD 操作：内置通用 Mapper、通用 Service，仅仅通过少量配置即可实现单表大部分 CRUD 操作，更有强大的条件构造器，满足各类使用需求
支持 Lambda 形式调用：通过 Lambda 表达式，方便的编写各类查询条件，无需再担心字段写错
支持主键自动生成：支持多达 4 种主键策略（内含分布式唯一 ID 生成器 - Sequence），可自由配置，完美解决主键问题
支持 ActiveRecord 模式：支持 ActiveRecord 形式调用，实体类只需继承 Model 类即可进行强大的 CRUD 操作
支持自定义全局通用操作：支持全局通用方法注入（ Write once, use anywhere ）
内置代码生成器：采用代码或者 Maven 插件可快速生成 Mapper 、 Model 、 Service 、 Controller 层代码，支持模板引擎，更有超多自定义配置等您来使用
内置分页插件：基于 MyBatis 物理分页，开发者无需关心具体操作，配置好插件之后，写分页等同于普通 List 查询
分页插件支持多种数据库：支持 MySQL、MariaDB、Oracle、DB2、H2、HSQL、SQLite、Postgre、SQLServer 等多种数据库
内置性能分析插件：可输出 SQL 语句以及其执行时间，建议开发测试时启用该功能，能快速揪出慢查询
内置全局拦截插件：提供全表 delete 、 update 操作智能分析阻断，也可自定义拦截规则，预防误操作
总结三大突出特性
1）润物无声
只做增强不做改变，引入它不会对现有工程产生影响，如丝般顺滑。
2）效率至上
只需简单配置，即可快速进行单表 CRUD 操作，从而节省大量时间。
3）丰富功能
代码生成、物理分页、性能分析等功能一应俱全。
2. 支持数据库
mysql 、mariadb 、oracle 、db2 、h2 、hsql 、sqlite 、postgresql 、sqlserver 、presto 、Gauss 、Firebird
Phoenix 、clickhouse 、Sybase ASE 、 OceanBase 、达梦数据库 、虚谷数据库 、人大金仓数据库 、南大通用数据库
3. 框架结构
10. 动态sql
UserMapper
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.itcast.mybatis.mapper.UserMapper">
<!-- <sql id="commonSql">
id,user_name,
password,
name,
age,
sex,
birthday,
created,
updated
</sql> -->
<select id="loginByMap" resultType="User">
select * from tb_user where user_name=#{userName} and password=#{password}
</select>
<select id="login" resultType="User">
select * from tb_user where user_name=#{userName} and password=#{password}
</select>
<select id="queryUsersByTableName" resultType="User">
select * from ${tableName}
</select>
<select id="queryUserById" resultType="User" parameterType="long">
select * from tb_user where id = #{id}
</select>
<resultMap id="userMap" type="User" autoMapping="true">
<id column="id" property="id"/>
</resultMap>
<select id="queryUserById1" resultMap="userMap">
select * from tb_user where id = #{id}
</select>
<select id="queryUserAll" resultType="User" >
select * from tb_user
</select>
<insert id="insertUser" parameterType="User">
INSERT INTO tb_user (
user_name,
password,
name,
age,
sex,
birthday,
created,
updated
)
VALUES
(
#{userName},
#{password},
#{name},
#{age},
#{sex},
#{birthday},
NOW(),
NOW()
);
</insert>
<update id="updateUser" parameterType="User">
UPDATE tb_user
SET
user_name = #{userName},
password = #{password},
name = #{name},
age = #{age},
sex = #{sex},
birthday = #{birthday},
updated = NOW()
WHERE
(id = #{id});
</update>
<delete id="deleteUserById" parameterType="java.lang.Long">
delete from tb_user where id=#{id}
</delete>
<select id="queryUsersLikeUserName" resultType="User">
<!-- select <include refid="CommonSQL.commonSql"></include> from tb_user where user_name like '%' #{userName} '%'-->
<!-- if:判断 test：OGNL表达式-->
select * from tb_user where sex=3
<if test="userName!=null and userName.trim()!=''">
and user_name like '%' #{userName} '%'
</if>
</select>
<select id="queryUserListLikeUserNameOrAge" resultType="User">
select * from tb_user where sex=3
<!-- choose:条件选择
when：test-判断条件，一旦有一个when成立，后续的when都不再执行
otherwise：所有的when都不成立时，才会执行
-->
<choose>
<when test="userName!=null and userName.trim()!=''">and user_name like '%' #{userName} '%'</when>
<when test="age != null">and age = #{age}</when>
<otherwise>and user_name = 'zhangsan' </otherwise>
</choose>
</select>
<select id="queryUserListLikeUserNameAndAge" resultType="User">
select * from tb_user
<!--
自动添加where关键字
有一定的纠错功能：去掉sql语句块之前多余的一个and|or
通常结合if或者choose使用
-->
<where>
<if test="userName!=null and userName.trim()!=''">user_name like '%' #{userName} '%'</if>
<if test="age!=null">and age = #{age}</if>
</where>
</select>
<select id="queryUserListByIds" resultType="User">
select * from tb_user where id in
<!--
foreach:遍历集合
collection：接收的集合参数
item：遍历的集合中的一个元素
separator:分隔符
open:以什么开始
close：以什么结束
-->
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</select>
<update id="updateUserSelective">
UPDATE tb_user
<!--
set自动添加set关键字
也有一定的纠错功能：自动去掉sql语句块之后多余的一个逗号
-->
<set>
<if test="userName!=null and userName.trim()!=''">user_name = #{userName},</if>
<if test="password!=null and password.trim()!=''">password = #{password},</if>
<if test="name!=null and name.trim()!=''">name = #{name},</if>
<if test="age!=null">age = #{age},</if>
<if test="sex!=null">sex = #{sex},</if>
updated = now(),
</set>
WHERE
(id = #{id});
</update>
</mapper>
UserMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.itcast.mybatis.mapper.UserMapper">
<!-- <sql id="commonSql">
id,user_name,
password,
name,
age,
sex,
birthday,
created,
updated
</sql> -->
<select id="loginByMap" resultType="User">
select * from tb_user where user_name=#{userName} and password=#{password}
</select>
<select id="login" resultType="User">
select * from tb_user where user_name=#{userName} and password=#{password}
</select>
<select id="queryUsersByTableName" resultType="User">
select * from ${tableName}
</select>
<select id="queryUserById" resultType="User" parameterType="long">
select * from tb_user where id = #{id}
</select>
<resultMap id="userMap" type="User" autoMapping="true">
<id column="id" property="id"/>
</resultMap>
<select id="queryUserById1" resultMap="userMap">
select * from tb_user where id = #{id}
</select>
<select id="queryUserAll" resultType="User" >
select * from tb_user
</select>
<insert id="insertUser" parameterType="User">
INSERT INTO tb_user (
user_name,
password,
name,
age,
sex,
birthday,
created,
updated
)
VALUES
(
#{userName},
#{password},
#{name},
#{age},
#{sex},
#{birthday},
NOW(),
NOW()
);
</insert>
<update id="updateUser" parameterType="User">
UPDATE tb_user
SET
user_name = #{userName},
password = #{password},
name = #{name},
age = #{age},
sex = #{sex},
birthday = #{birthday},
updated = NOW()
WHERE
(id = #{id});
</update>
<delete id="deleteUserById" parameterType="java.lang.Long">
delete from tb_user where id=#{id}
</delete>
<select id="queryUsersLikeUserName" resultType="User">
<!-- select <include refid="CommonSQL.commonSql"></include> from tb_user where user_name like '%' #{userName} '%'-->
<!-- if:判断 test：OGNL表达式-->
select * from tb_user where sex=3
<if test="userName!=null and userName.trim()!=''">
and user_name like '%' #{userName} '%'
</if>
</select>
<select id="queryUserListLikeUserNameOrAge" resultType="User">
select * from tb_user where sex=3
<!-- choose:条件选择
when：test-判断条件，一旦有一个when成立，后续的when都不再执行
otherwise：所有的when都不成立时，才会执行
-->
<choose>
<when test="userName!=null and userName.trim()!=''">and user_name like '%' #{userName} '%'</when>
<when test="age != null">and age = #{age}</when>
<otherwise>and user_name = 'zhangsan' </otherwise>
</choose>
</select>
<select id="queryUserListLikeUserNameAndAge" resultType="User">
select * from tb_user
<!--
自动添加where关键字
有一定的纠错功能：去掉sql语句块之前多余的一个and|or
通常结合if或者choose使用
-->
<where>
<if test="userName!=null and userName.trim()!=''">user_name like '%' #{userName} '%'</if>
<if test="age!=null">and age = #{age}</if>
</where>
</select>
<select id="queryUserListByIds" resultType="User">
select * from tb_user where id in
<!--
foreach:遍历集合
collection：接收的集合参数
item：遍历的集合中的一个元素
separator:分隔符
open:以什么开始
close：以什么结束
-->
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</select>
<update id="updateUserSelective">
UPDATE tb_user
<!--
set自动添加set关键字
也有一定的纠错功能：自动去掉sql语句块之后多余的一个逗号
-->
<set>
<if test="userName!=null and userName.trim()!=''">user_name = #{userName},</if>
<if test="password!=null and password.trim()!=''">password = #{password},</if>
<if test="name!=null and name.trim()!=''">name = #{name},</if>
<if test="age!=null">age = #{age},</if>
<if test="sex!=null">sex = #{sex},</if>
updated = now(),
</set>
WHERE
(id = #{id});
</update>
</mapper>
User
package cn.itcast.mybatis.pojo;
import java.util.Date;
public class User {
private Long id;
// 用户名
private String userName;
// 密码
private String password;
// 姓名
private String name;
// 年龄
private Integer age;
// 性别，1男性，2女性
private Integer sex;
// 出生日期
private Date birthday;
// 创建时间
private Date created;
// 更新时间
private Date updated;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Integer getSex() {
return sex;
}
public void setSex(Integer sex) {
this.sex = sex;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public Date getCreated() {
return created;
}
public void setCreated(Date created) {
this.created = created;
}
public Date getUpdated() {
return updated;
}
public void setUpdated(Date updated) {
this.updated = updated;
}
@Override
public String toString() {
return "User [id=" + id + ", userName=" + userName + ", password=" + password + ", name=" + name
+ ", age=" + age + ", sex=" + sex + ", birthday=" + birthday + ", created=" + created
+ ", updated=" + updated + "]";
}
}
UserMapperTest
package cn.itcast.mybatis.mapper;
import cn.itcast.mybatis.pojo.User;
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 org.junit.Before;
import org.junit.Test;
import java.io.InputStream;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class UserMapperTest {
private UserMapper userMapper;
@Before
public void setUp() throws Exception {
// 读取mybatis的全局配置文件
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
// 构建sqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream,"test");
// 获取sqlSession会话, true 表示自动提交事务
SqlSession sqlSession = sqlSessionFactory.openSession(true);
// 初始化userDao
this.userMapper = sqlSession.getMapper(UserMapper.class);
}
@Test
public void testQueryUsersLikeUserName(){
List<User> users = this.userMapper.queryUsersLikeUserName("rick");
for (User user : users) {
System.out.println(user);
}
}
@Test
public void testMap(){
Map<String,Object> map = new HashMap<>();
map.put("userName", "zhangsan");
map.put("password", "00000");
System.out.println(this.userMapper.loginByMap(map));
}
@Test
public void testLogin(){
System.out.println(this.userMapper.login("lisi", "456"));
}
@Test
public void testQueryUsersByTableName(){
List<User> users = this.userMapper.queryUsersByTableName("tb_user");
for (User user : users) {
System.out.println(user);
}
}
@Test
public void queryUserById() {
User user = this.userMapper.queryUserById1(1L);
System.out.println(user);
}
@Test
public void testQueryUserAll() {
List<User> userList = this.userMapper.queryUserAll();
for (User user : userList) {
System.out.println(user);
}
}
@Test
public void testQueryUserListLikeUserNameOrAge(){
List<User> users = this.userMapper.queryUserListLikeUserNameOrAge(null, 18);
for (User user : users) {
System.out.println(user);
}
}
@Test
public void testQueryUserListLikeUserNameAndAge(){
List<User> users = this.userMapper.queryUserListLikeUserNameAndAge("yanyan", 18);
for (User user : users) {
System.out.println(user);
}
}
@Test
public void testUpdateUserSelective(){
User user = new User();
user.setAge(16);
user.setName("Lydia");
user.setPassword("789");
user.setUserName("yanyan2");
user.setSex(3);
user.setBirthday(new Date());
user.setId(11L);
this.userMapper.updateUserSelective(user);
}
@Test
public void testQueryUserListByIds(){
List<User> users = this.userMapper.queryUserListByIds(new Long[]{1L,2L,3L,4L});
for (User user : users) {
System.out.println(user);
}
}
}
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>
<!-- 引入外部资源文件，resource：相对路径，url：绝对路径 -->
<properties resource="jdbc.properties"/>
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<typeAliases>
<!-- <typeAlias type="cn.itcast.mybatis.pojo.User" alias="user"/> -->
<package name="cn.itcast.mybatis.pojo"/>
</typeAliases>
<environments default="test">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
<environment id="test">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="UserMapper.xml"/>
<mapper resource="UserDaoMapper.xml"/>
<mapper resource="CommonSQL.xml"/>
<!-- <mapper class="cn.itcast.mybatis.mapper.UserMapper"/>
<package name="cn.itcast.mybatis.mapper"/> -->
</mappers>
</configuration>
jdbc.properties
jdbc.driver = com.mysql.jdbc.Driver
jdbc.url = jdbc:mysql:///user_manager
jdbc.username = root
jdbc.password = root
3163
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
