@Select

1、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>
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <setting name="useGeneratedKeys" value="true"/>
    </settings>

    <typeAliases>
        <package name="com.forest.owl.entity"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC">
                <property name="" value=""/>
            </transactionManager>
            <dataSource type="UNPOOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/forest?useSSL=false"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <package name="com.forest.owl.mapper"/>
    </mappers>
</configuration>

2、编写UserMapper接口

package com.forest.owl.mapper;

import com.forest.owl.entity.User;
import org.apache.ibatis.annotations.Select;

public interface UserMapper {
    @Select("SELECT * FROM user WHERE id=#{id}")
    User selectUserById(Long id);
}

3、测试

    @Test
    public void UserMapperTest(){
        SqlSession sqlSession = getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = userMapper.selectUserById((long) 2);
        System.out.println(user.getUserName());
        System.out.println(user.getHeadImg());
        System.out.println(user.getCreateTime());
    }

4、关联查询

    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "userName", column = "user_name"),
            @Result(property = "userPassword", column = "user_password"),
            @Result(property = "userPhone", column = "user_phone"),
            @Result(property = "userEmail", column = "user_email"),
            @Result(property = "headImg", column = "head_img"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "updateTime", column = "update_time"),
            @Result(property = "role.roleName", column = "role_name")
    })
    @Select("SELECT u.* ,r.role_name FROM user u INNER JOIN user_role ur on ur.user_id=u.id INNER JOIN role r on r.id=ur.role_id " +
            "WHERE u.id=#{id}")
    List<User> selectUserById(Long id);

关于注解形式的查询,也可以通过Provider进行sql的动态生成。如:

新建UserProvider类:

package com.forest.owl.provider;

import org.apache.ibatis.jdbc.SQL;

public class UserProvider {
    public String selectUserById(Long id){
        return new SQL(){
            {
                SELECT("u.* ,r.role_name");
                FROM("user u");
                LEFT_OUTER_JOIN("user_role ur on ur.user_id=u.id");
                LEFT_OUTER_JOIN("role r on r.id=ur.role_id");
                WHERE("u.id=#{id}");
            }
        }.toString();
    }
}

此时UserMapper可以这么写:

public interface UserMapper {
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "userName", column = "user_name"),
            @Result(property = "userPassword", column = "user_password"),
            @Result(property = "userPhone", column = "user_phone"),
            @Result(property = "userEmail", column = "user_email"),
            @Result(property = "headImg", column = "head_img"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "updateTime", column = "update_time"),
            @Result(property = "role.roleName", column = "role_name")
    })
    @SelectProvider(type = UserProvider.class, method = "selectUserById")
    List<User> selectUserById(Long id);
}

@insert

这里介绍普通插入和返回主键方式的插入

普通插入数据

package com.forest.owl.mapper;

import com.forest.owl.entity.User;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Select;

public interface UserMapper {
    @Insert("INSERT INTO user (user_name, user_password, user_phone, user_email, head_img, create_time, update_time) VALUES (#{userName}, #{userPassword}, #{userPhone}, #{userEmail}, #{headImg}, #{createTime}, #{updateTime})")int insertUser(User user);
}

回写主键方式的插入(注意:哪怕mybatis-config.xml配置中已经配置了useGeneratedKeys,在@Options中仍旧需要再配置一次)

package com.forest.owl.mapper;

import com.forest.owl.entity.User;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Select;

public interface UserMapper {
    @Insert("INSERT INTO user (user_name, user_password, user_phone, user_email, head_img, create_time, update_time) VALUES (#{userName}, #{userPassword}, #{userPhone}, #{userEmail}, #{headImg}, #{createTime}, #{updateTime})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insertUser(User user);
}
05-01 01:54