MyBatis实现CRUD

MyBatis日志

在application.yml中配置MyBatis日志

mybatis-plus:
  mapper-locations: classpath:mappers/*.xml
  configuration:
    # 配置MyBatis日志
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

MyBatis输入类型parameterType#{}与${}的区别

#{}

1. #{}实现的是想prepareStatement中的预处理语句中设计参数值，sql语句中#{}表示一个占位符即？。
2. 使用占位符#{}可以有效的防止sql注入，在使用时不需要关心参数值的类型，MyBatis会自动进行java类型和jdbc类型的转换。
3. #{}可以接收简单类型值或pojo属性值，如果parameterType传输单个简单类型值，#{}括号中可以是value或是其他名称

${}

1. 通过${}可以将parameterType传入的内容拼接在sql中且不进行jdbc类型转换

2. $可以接收简单型值或pojo属性值，如果parameterType传输单个简单类型值，${}括号中只能是value。

3. 使用${}不能防止sql注入

MyBatis动态拼接sql

1. if标签

	<select id="selectByMoney" resultType="demo.entity.User">
        select  * from t_user
        <where>
            <if test="money != null and money != ''">
                and money>1000
            </if>
        </where>
    </select>

2. foreach标签

	<select id="selectUserByIds" resultType="demo.entity.User">
        select * from t_user
        <where>
            <foreach collection="ids" open="and id in (" item="id" separator="," close=")">
                #{id}
            </foreach>
        </where>
    </select>

3. sql标签

	<sql id="sql_temp">
        select id,user_name,passwd,money from t_user
    </sql>
    
    <select id="login" resultType="demo.entity.User">
        <include refid="sql_temp"></include>
        where user_name= #{userName} and passwd= #{passwd}
    </select>

条件构造器

1.组装查询条件

@Test
public void test01(){ 
    //查询用户名包含a，年龄在20到30之间，并且邮箱不为null的用户信息
    //SELECT id,username AS name,age,email,is_deleted FROM t_user WHEREis_deleted=0 AND (username LIKE ? AND age BETWEEN ? AND ? AND email IS NOT NULL)
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.like("username", "a")
        .between("age", 20, 30)
        .isNotNull("email");
    List<User> list = userMapper.selectList(queryWrapper);
    list.forEach(System.out::println);
}

2.组装排序条件

@Test
public void test02(){
    //按年龄降序查询用户，如果年龄相同则按id升序排列
    //SELECT id,username AS name,age,email,is_deleted FROM t_user WHEREis_deleted=0 ORDER BY age DESC,id ASC
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper
        .orderByDesc("age")
        .orderByAsc("id");
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}

3.组装删除条件

@Test
public void test03(){
	
            //删除email为空的用户
	//DELETE FROM t_user WHERE (email IS NULL) 
        	QueryWrapper<User> queryWrapper = new QueryWrapper<>(); 
        	queryWrapper.isNull("email"); //条件构造器也可以构建删除语句的条件
	
            int result = userMapper.delete(queryWrapper); 
        	System.out.println("受影响的行数:" + result); 
        } 

4.条件的优先级

@Test
public void test04() {
	QueryWrapper<User> queryWrapper = new QueryWrapper<>(); //将(年龄大于20并且用户名中包含有a)或邮箱为null的用户信息修改
	//UPDATE t_user SET age=?, email=? WHERE (username LIKE ? AND age > ? OR email IS NULL)
	queryWrapper
		.like("username", "a")
		.gt("age", 20) .or() 
		.isNull("email");
	User user = new User();
	user.setAge(18); user.setEmail("user@atguigu.com");
	int result = userMapper.update(user, queryWrapper); 
    System.out.println("受影响的行数:" + result);
}

5.实现子查询

@Test
public void test06() {
    //查询id小于等于3的用户信息
    //SELECT id,username AS name,age,email,is_deleted FROM t_user WHERE (id IN (select id from t_user where id <= 3))
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.inSql("id", "select id from t_user where id <= 3");
    List<User> list = userMapper.selectList(queryWrapper);
    list.forEach(System.out::println);
}