@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); }