使mybatis开发更加整洁
匹配返回的数据名
< resultMap id = " PersonMap" type = " com.lihaozhe.pojo.Person" >
< id property = " id" column = " id" />
< result property = " id" column = " id" />
< result property = " uuid" column = " uuid" />
< result property = " mobile" column = " mobile" />
< result property = " nickname" column = " nickname" />
< result property = " idCard" column = " id_card" />
</ resultMap>
.....
< select id = " selectAll4m" resultMap = " PersonMap" >
SELECT *
FROM `person`
</ select>
在控制台显示mybatis信息
< settings>
< setting name = " logImpl" value = " STDOUT_LOGGING" />
</ settings>
化简类的完全限定名
< typeAliases>
< package name = " com.lihaozhe.pojo" />
</ typeAliases>
编写mybatis工具类
public class SessionUtils {
private SqlSession sqlSession;
public static void openSqlSession ( ) throws IOException {
String resource = "mybatis/mybatis-config.xml" ;
Reader reader = Resources . getResourceAsReader ( resource) ;
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder ( ) . build ( reader) ;
sqlSession = sqlSessionFactory. openSession ( ) ;
}
}
查询数据
查询数据
package com. lihaozhe. mapper ;
import com. lihaozhe. pojo. Person ;
import org. apache. ibatis. annotations. Param ;
import java. util. List ;
public interface PersonMapper {
Person selectByFiled ( @Param ( "column" ) String column, @Param ( "value" ) String value) ;
}
<?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 = " com.lihaozhe.mapper.PersonMapper" >
< select id = " selectByFiled" resultType = " com.lihaozhe.pojo.Person" >
SELECT *
FROM `person`
WHERE ${column} = #{value}
</ select>
</ mapper>
@Test
public void selectByFiled ( ) {
PersonMapper mapper = sqlSession. getMapper ( PersonMapper . class ) ;
Person person = mapper. selectByFiled ( "mobile" , "18400880850" ) ;
sqlSession. close ( ) ;
System . out. println ( person) ;
}
复数查询数据
package com. lihaozhe. mapper ;
import com. lihaozhe. pojo. Person ;
import org. apache. ibatis. annotations. Param ;
import java. util. List ;
public interface PersonMapper {
List < Person > selectByIds ( @Param ( "ids" ) Long . . . ids) ;
}
<?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 = " com.lihaozhe.mapper.PersonMapper" >
< sql id = " select_person" >
SELECT *
FROM `person`
</ sql>
< select id = " selectByIds" resultType = " person" >
< include refid = " select_person" > </ include>
< where>
`id` IN
< foreach collection = " ids" index = " index" item = " id" open = " (" separator = " ," close = " )" >
#{id}
</ foreach>
</ where>
</ select>
</ mapper>
package com. lihaozhe. mapper ;
import cn. binarywang. tools. generator. ChineseIDCardNumberGenerator ;
import cn. binarywang. tools. generator. ChineseMobileNumberGenerator ;
import cn. binarywang. tools. generator. ChineseNameGenerator ;
import cn. hutool. core. util. IdUtil ;
import com. lihaozhe. pojo. Person ;
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. jupiter. api. BeforeEach ;
import org. junit. jupiter. api. Test ;
import java. io. IOException ;
import java. io. Reader ;
import java. util. ArrayList ;
import java. util. List ;
public class PersonMapperTest {
private SqlSession sqlSession;
@BeforeEach
public void openSqlSession ( ) throws IOException {
String resource = "mybatis/mybatis-config.xml" ;
Reader reader = Resources . getResourceAsReader ( resource) ;
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder ( ) . build ( reader) ;
sqlSession = sqlSessionFactory. openSession ( ) ;
}
@Test
public void selectByIds ( ) {
PersonMapper mapper = sqlSession. getMapper ( PersonMapper . class ) ;
List < Person > personList = mapper. selectByIds ( 1L , 3L , 5L ) ;
sqlSession. close ( ) ;
personList. forEach ( System . out:: println ) ;
}
}
模糊查询数据
package com. lihaozhe. mapper ;
import com. lihaozhe. pojo. Person ;
import org. apache. ibatis. annotations. Param ;
import java. util. List ;
public interface PersonMapper {
List < Person > selectByNickname ( @Param ( "nickname" ) String nickname) ;
}
< sql id = " select_person" >
SELECT *
FROM `person`
</ sql>
< select id = " selectByNickname" resultType = " person" >
< select id = " selectByNickname" resultType = " person" >
< bind name = " like_nickname" value = " ' %' + nickname + ' %' " />
< include refid = " select_person" > </ include>
WHERE `nickname` like #{like_nickname}
</ select>
</ select>
package com. lihaozhe. mapper ;
import com. lihaozhe. pojo. Person ;
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. jupiter. api. BeforeEach ;
import org. junit. jupiter. api. Test ;
import java. io. IOException ;
import java. io. Reader ;
import java. util. List ;
public class PersonMapperTest {
private SqlSession sqlSession;
@BeforeEach
public void getSqlSession ( ) throws IOException {
String resource = "mybatis/mybatis-config.xml" ;
Reader reader = Resources . getResourceAsReader ( resource) ;
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder ( ) . build ( reader) ;
sqlSession = sqlSessionFactory. openSession ( ) ;
}
@Test
public void selectByNickname ( ) {
PersonMapper mapper = sqlSession. getMapper ( PersonMapper . class ) ;
String nickname = "孙" ;
StringBuilder param = new StringBuilder ( ) ;
param. append ( "%" ) ;
if ( ! StringUtils . isEmpty ( nickname) ) {
param. append ( nickname) ;
}
param. append ( "%" ) ;
List < Person > personList = mapper. selectByNickname ( param. toString ( ) ) ;
sqlSession. close ( ) ;
personList. forEach ( System . out:: println ) ;
}
}
增加或减少限制(不为0)
package com. lihaozhe. mapper ;
import com. lihaozhe. pojo. Person ;
import org. apache. ibatis. annotations. Param ;
import java. util. List ;
public interface PersonMapper {
List < Person > selectByGenderAndNickname ( @Param ( "gender" ) Integer gender, @Param ( "nickname" ) String nickname) ;
}
<?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 = " com.lihaozhe.mapper.PersonMapper" >
< sql id = " select_person" >
SELECT *
FROM `person`
</ sql>
< select id = " selectByGenderAndNickname" resultType = " person" >
< include refid = " select_person" > </ include>
WHERE
< if test = " gender != null" >
if(mod(substr(id_card, 17, 1), 2), 1, 0) = #{gender}
</ if>
< if test = " nickname != null and nickname.length > 0" >
< bind name = " like_nickname" value = " ' %' + nickname + ' %' " />
AND `nickname` like #{like_nickname}
</ if>
</ select>
</ mapper>
package com. lihaozhe. mapper ;
import com. lihaozhe. pojo. Person ;
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. jupiter. api. BeforeEach ;
import org. junit. jupiter. api. Test ;
import java. io. IOException ;
import java. io. Reader ;
import java. util. List ;
public class PersonMapperTest {
private SqlSession sqlSession;
@BeforeEach
public void oepnSqlSession ( ) throws IOException {
String resource = "mybatis/mybatis-config.xml" ;
Reader reader = Resources . getResourceAsReader ( resource) ;
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder ( ) . build ( reader) ;
sqlSession = sqlSessionFactory. openSession ( ) ;
}
@Test
public void selectByGenderAndNickname ( ) {
PersonMapper mapper = sqlSession. getMapper ( PersonMapper . class ) ;
List < Person > personList = mapper. selectByGenderAndNickname ( 1 , "孙" ) ;
sqlSession. close ( ) ;
personList. forEach ( System . out:: println ) ;
}
}
增加或减少限制(chooose-when用法)
package com. lihaozhe. mapper ;
import com. lihaozhe. pojo. Person ;
import org. apache. ibatis. annotations. Param ;
import java. util. List ;
public interface PersonMapper {
Person selectByIdOrUuidOrMobile ( @Param ( "person" ) Person person) ;
}
< sql id = " select_person" >
SELECT *
FROM `person`
</ sql>
< select id = " selectByIdOrUuidOrMobile" resultType = " person" >
< include refid = " select_person" > </ include>
< where>
< choose>
< when test = " person.id != null and person.id > 0" >
`id` = #{person.id}
</ when>
< when test = " person.uuid != null and person.uuid.length > 0" >
`uuid` = #{person.uuid}
</ when>
< otherwise>
`mobile` = #{person.mobile}
</ otherwise>
</ choose>
</ where>
</ select>
package com. lihaozhe. mapper ;
import com. lihaozhe. pojo. Person ;
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. jupiter. api. BeforeEach ;
import org. junit. jupiter. api. Test ;
import java. io. IOException ;
import java. io. Reader ;
import java. util. List ;
public class PersonMapperTest {
private SqlSession sqlSession;
@BeforeEach
public void openSqlSession ( ) throws IOException {
String resource = "mybatis/mybatis-config.xml" ;
Reader reader = Resources . getResourceAsReader ( resource) ;
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder ( ) . build ( reader) ;
sqlSession = sqlSessionFactory. openSession ( ) ;
}
@Test
public void selectByIdOrUuidOrMobile ( ) {
PersonMapper mapper = sqlSession. getMapper ( PersonMapper . class ) ;
Person person = new Person ( ) ;
person. setMobile ( "13352587230" ) ;
person. setUuid ( "2032a25e5245454894e192d8d5b17147" ) ;
person. setId ( 33 ) ;
person = mapper. selectByIdOrUuidOrMobile ( person) ;
sqlSession. close ( ) ;
System . out. println ( person) ;
}
}
增加或减少限制(可为0)
package com. lihaozhe. mapper ;
import com. lihaozhe. pojo. Emp ;
import org. apache. ibatis. annotations. Param ;
import java. util. List ;
public interface EmpMapper {
List < Emp > selcetByDidAndEname ( @Param ( "emp" ) Emp emp) ;
}
<?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 = " com.lihaozhe.mapper.EmpMapper" >
< select id = " selectByDidAndEname" resultType = " emp" >
SELECT * FROM `emp`
< trim prefix = " where" prefixOverrides = " AND|OR" >
< if test = " emp.did != null and emp.did > 0" >
`did` = #{emp.did}
</ if>
< if test = " emp.ename != null and emp.ename.length > 0" >
< bind name = " like_ename" value = " ' %' + emp.ename + ' %' " />
AND `ename` like #{like_ename}
</ if>
</ trim>
</ select>
</ mapper>
package com. lihaozhe. mapper ;
import com. lihaozhe. pojo. Emp ;
import com. lihaozhe. util. mybatis. MyBatisUtil ;
import org. apache. ibatis. session. SqlSession ;
import org. junit. jupiter. api. Test ;
import java. util. List ;
public class EmpMapperTest {
@Test
public void selcetByDidAndEname ( ) {
SqlSession sqlSession = MyBatisUtil . getSqlSession ( ) ;
EmpMapper mapper = sqlSession. getMapper ( EmpMapper . class ) ;
Emp emp = new Emp ( ) ;
emp. setEname ( "孙" ) ;
emp. setDid ( 3 ) ;
List < Emp > emps = mapper. selcetByDidAndEname ( emp) ;
MyBatisUtil . close ( ) ;
emps. forEach ( System . out:: println ) ;
}
}
多表对一bean查询数据
package com. lihaozhe. mapper ;
import com. lihaozhe. vo. SonVo ;
import org. apache. ibatis. annotations. Param ;
public interface SonMapper {
SonVo selectSonVoBySid ( @Param ( "sid" ) long sid) ;
}
<?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 = " com.lihaozhe.mapper.SonMapper" >
< resultMap id = " SonVoMap" type = " sonVo" >
< id property = " sId" column = " s_id" />
< result property = " sName" column = " s_name" />
< result property = " fId" column = " fid" />
< association property = " father" >
< id property = " fId" column = " fid" />
< result property = " fName" column = " f_name" />
</ association>
</ resultMap>
< select id = " selectSonVoBySid" resultMap = " SonVoMap" >
SELECT s_id, s_name, s.f_id AS fid, f_name
FROM son s
INNER JOIN father f ON s.f_id = f.f_id AND s_id = #{sid}
</ select>
</ mapper>
package com. lihaozhe. mapper ;
import com. lihaozhe. util. mybatis. MyBatisUtil ;
import com. lihaozhe. vo. SonVo ;
import org. apache. ibatis. session. SqlSession ;
import org. junit. jupiter. api. Test ;
public class SonMapperTest {
@Test
public void selectSonVoBySid ( ) {
SqlSession sqlSession = MyBatisUtil . openSqlSession ( ) ;
SonMapper mapper = sqlSession. getMapper ( SonMapper . class ) ;
SonVo sonVo = mapper. selectSonVoBySid ( 3L ) ;
MyBatisUtil . close ( ) ;
System . out. println ( sonVo) ;
}
}
添加或更新数据
修改数据(有数据对自身的限制)
package com. lihaozhe. mapper ;
import com. lihaozhe. pojo. Person ;
import org. apache. ibatis. annotations. Param ;
import java. util. List ;
public interface PersonMapper {
int updateById ( @Param ( "person" ) Person person) ;
}
<?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 = " com.lihaozhe.mapper.PersonMapper" >
< update id = " updateById" parameterType = " person" >
UPDATE `person`
< set>
< if test = " person.nickname != null and person.nickname.length > 0" >
`nickname` = #{person.nickname},
</ if>
< if test = " person.mobile != null and person.mobile.length == 11" >
`mobile` = #{person.mobile},
</ if>
< if test = " person.idCard != null and (person.idCard.length == 18 or person.idCard.length == 15)" >
`id_card` = #{person.idCard}
</ if>
</ set>
WHERE `id` = #{person.id}
</ update>
</ mapper>
package com. lihaozhe. mapper ;
import cn. binarywang. tools. generator. ChineseIDCardNumberGenerator ;
import cn. binarywang. tools. generator. ChineseMobileNumberGenerator ;
import cn. binarywang. tools. generator. ChineseNameGenerator ;
import cn. hutool. core. util. IdUtil ;
import com. lihaozhe. pojo. Person ;
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. jupiter. api. BeforeEach ;
import org. junit. jupiter. api. Test ;
import java. io. IOException ;
import java. io. Reader ;
import java. util. ArrayList ;
import java. util. List ;
public class PersonMapperTest {
private SqlSession sqlSession;
@BeforeEach
public void openSqlSession ( ) throws IOException {
String resource = "mybatis/mybatis-config.xml" ;
Reader reader = Resources . getResourceAsReader ( resource) ;
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder ( ) . build ( reader) ;
sqlSession = sqlSessionFactory. openSession ( ) ;
}
@Test
public void updateById ( ) {
PersonMapper mapper = sqlSession. getMapper ( PersonMapper . class ) ;
Person person = new Person ( ) ;
String mobile = ChineseMobileNumberGenerator . getInstance ( ) . generate ( ) ;
System . out. println ( mobile) ;
person. setMobile ( mobile) ;
person. setId ( 3 ) ;
mapper. updateById ( person) ;
sqlSession. commit ( ) ;
sqlSession. close ( ) ;
if ( status > 0 ) {
System . out. println ( "☺" ) ;
} else {
System . out. println ( "o(╯□╰)o" ) ;
}
}
}
封装前置查询语句(SQL片段)
<?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 = " com.lihaozhe.mapper.PersonMapper" >
< sql id = " select_person" >
SELECT *
FROM `person`
</ sql>
< select id = " selectById" resultType = " person" >
< include refid = " select_person" > </ include>
WHERE `id` = #{id}
</ select>
</ mapper>