表结构:
t_person表 个人表
t_idcard表 身份证表
身份证与个人一对一关联
实体映射类:
@Data
public class IdCard {
private Integer id;
private String code;
}
@Data
public class Person {
private Integer id;
private String name;
private Integer age;
private String sex;
/**
* 个人证号ID
*/
private IdCard card;
}
配置mapper文件
IdCardMapper
<?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="ssm.mapper.IdCardMapper">
<!-- 根据id查询证件信息 -->
<select id="findCodeById" parameterType="Integer" resultType="ssm.pojo.IdCard">
SELECT * from t_idcard where id=#{id}
</select>
</mapper>
PersonMapper
<?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="ssm.mapper.PersonMapper">
<!-- 嵌套查询:通过执行另外一条SQL映射语句来返回预期的特殊类型 -->
<select id="findPersonById" parameterType="Integer"
resultMap="IdCardWithPersonResult">
SELECT * from t_person where id=#{id}
</select>
<resultMap type="ssm.pojo.Person" id="IdCardWithPersonResult">
<id property="id" column="id" />
<result property="name" column="name" />
<result property="age" column="age" />
<result property="sex" column="sex" />
<!-- card 对应的是IdCard card 值 javaType是类可简写
<association property="card" column="card_id" javaType="ssm.pojo.IdCard"
select="ssm.mapper.IdCardMapper.findCodeById" />
</resultMap>
<resultMap id="PersonAndIdCard" type="mybatis.pojo.Person" >
<id property="id" column="id" />
<result property="name" column="name" />
<result property="age" column="age" />
<result property="sex" column="sex" />
<association property="card" javaType="mybatis.pojo.IdCard">
<id property="id" column="cardId" />
<result property="code" column="code" />
</association>
</resultMap>
<!-- 嵌套结果:使用嵌套结果映射来处理重复的联合结果的子集 -->
<select id="findPersonById" parameterType="Integer"
resultMap="PersonAndIdCard">
SELECT p.id,p.age,p.name,p.sex,p.card_Id as cardId,idcard.code
from t_person p,t_idcard idcard
where p.card_id=idcard.id
and p.id= #{id}
</select>
</mapper>
测试执行:
2021-02-03 09:44:07 [ main:731 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==> Preparing: SELECT p.id,p.age,p.name,p.sex,p.card_Id as cardId,idcard.code from t_person p,t_idcard idcard where p.card_id=idcard.id and p.id= ?
2021-02-03 09:44:07 [ main:787 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==> Parameters: 1(Integer)
2021-02-03 09:44:07 [ main:819 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) <== Total: 1
2021-02-03 09:44:07 [ main:821 ] - [ DEBUG ] org.apache.ibatis.transaction.jdbc.JdbcTransaction.resetAutoCommit(JdbcTransaction.java:123) Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@16022d9d]
2021-02-03 09:44:07 [ main:822 ] - [ DEBUG ] org.apache.ibatis.transaction.jdbc.JdbcTransaction.close(JdbcTransaction.java:91) Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@16022d9d]
2021-02-03 09:44:07 [ main:823 ] - [ DEBUG ] org.apache.ibatis.datasource.pooled.PooledDataSource.pushConnection(PooledDataSource.java:363) Returned connection 369241501 to pool.
Person(id=1, name=小米, age=12, sex=男, card=IdCard(id=1, code=2222222))
代码:
/**
* @author Administrator
*/
public interface PersonDao {
/**
* 根据id查找
* @param id
* @return
*/
Person findPersonById(Integer id);
/**
* concat 拼接参数模糊查找
* @param name
* @return
*/
List<Person> findPersonByName(String name);
/**
* bind使用模糊查找
* @param name
* @return
*/
List<Person> findPersonByNameWithBound(Person person);
/**
* 使用like 模糊查询
* @param name
* @return
*/
List<Person> findPersonByNameByLike(String name);
}
public class PersonDaoImpl implements PersonDao {
private SqlSessionFactory sqlSessionFactory ;
public PersonDaoImpl(SqlSessionFactory sqlSessionFactory){
this.sqlSessionFactory = sqlSessionFactory;
}
@Override
public Person findPersonById(Integer id) {
SqlSession sqlSession = sqlSessionFactory.openSession();
Person person = sqlSession.selectOne("mybatis.mapper.PersonMapper.findPersonById",id);
sqlSession.close();
return person;
}
@Override
public List<Person> findPersonByName(String name) {
List<Person>personList = new ArrayList<>();
SqlSession sqlSession = sqlSessionFactory.openSession();
personList = sqlSession.selectList("mybatis.mapper.PersonMapper.findPersonByName",name);
sqlSession.close();
return personList;
}
@Override
public List<Person> findPersonByNameWithBound(Person person) {
List<Person>personList = new ArrayList<>();
SqlSession sqlSession = sqlSessionFactory.openSession();
personList = sqlSession.selectList("mybatis.mapper.PersonMapper.findPersonByNameWithBound",person);
sqlSession.close();
return personList;
}
@Override
public List<Person> findPersonByNameByLike(String name) {
List<Person>personList = new ArrayList<>();
SqlSession sqlSession = sqlSessionFactory.openSession();
personList = sqlSession.selectList("mybatis.mapper.PersonMapper.findPersonByNameWithBound",name);
sqlSession.close();
return personList;
}
}
<?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="mybatis.mapper.PersonMapper">
<resultMap id="PersonAndIdCard" type="mybatis.pojo.Person" >
<id property="id" column="id" />
<result property="name" column="name" />
<result property="age" column="age" />
<result property="sex" column="sex" />
<association property="card" javaType="mybatis.pojo.IdCard">
<id property="id" column="cardId" />
<result property="code" column="code" />
</association>
</resultMap>
<!-- 嵌套结果:使用嵌套结果映射来处理重复的联合结果的子集 -->
<select id="findPersonById" parameterType="Integer"
resultMap="PersonAndIdCard">
SELECT p.id,p.age,p.name,p.sex,p.card_Id as cardId,idcard.code
from t_person p,t_idcard idcard
where p.card_id=idcard.id
and p.id= #{id}
</select>
<select id="findPersonByNameByLike" parameterType="String" resultMap="PersonAndIdCard">
SELECT p.id,p.age,p.name,p.sex,p.card_Id as cardId,idcard.code
from t_person p,t_idcard idcard
where p.card_id=idcard.id
and p.name like '%${value}%'
</select>
<select id="findPersonByName" parameterType="String" resultMap="PersonAndIdCard">
SELECT p.id,p.age,p.name,p.sex,p.card_Id as cardId,idcard.code
from t_person p,t_idcard idcard
where p.card_id=idcard.id
and p.name like concat('%','${value}','%')
</select>
<select id="findPersonByNameWithBound" parameterType="mybatis.pojo.Person" resultMap="PersonAndIdCard">
<bind name="name" value="'%'+_parameter.getName()+'%'"/>
SELECT p.id,p.age,p.name,p.sex,p.card_Id as cardId,idcard.code
from t_person p,t_idcard idcard
where p.card_id=idcard.id
and p.name like #{name}
</select>
</mapper>
<?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="mybatis.mapper.IdCardMapper">
<!-- 根据id查询证件信息 -->
<select id="findCodeById" parameterType="Integer" resultType="mybatis.pojo.IdCard">
SELECT * from t_idcard where id=#{id}
</select>
<insert id="insertIdCard" parameterType="mybatis.pojo.IdCard" useGeneratedKeys="true" >
<selectKey keyColumn="id" order="AFTER" resultType="Integer" statementType="PREPARED">
SELECT LAST_INSERT_ID()
</selectKey>
INSERT INTO t_idcard (code)
VALUES (#{code})
</insert>
<!-- 对于不支持自动生成主键的数据库,或取消自主增长规则的数据库可以自定义主键生成规则 -->
<insert id="insertCard" parameterType="mybatis.pojo.IdCard">
<selectKey keyProperty="id" keyColumn="id" resultType="Integer" order="BEFORE">
select if(max(id) is null, 1, max(id) +1) as id from t_idcard
</selectKey>
insert into t_idcard(id,code) values(#{id},#{code})
</insert>
</mapper>
测试类:
public class MybatisTest {
private SqlSessionFactory sqlSessionFactory;
@Before
public void init() throws Exception {
SqlSessionFactoryBuilder sessionFactoryBuilder = new SqlSessionFactoryBuilder();
InputStream inputStream = Resources.getResourceAsStream("mybatis.xml");
sqlSessionFactory = sessionFactoryBuilder.build(inputStream);
}
@Test
public void TestfindPersonById() {
PersonDao personDao = new PersonDaoImpl(sqlSessionFactory);
Person person = personDao.findPersonById(1);
System.out.println(person);
}
@Test
public void TestfindPersonByName() {
PersonDao personDao = new PersonDaoImpl(sqlSessionFactory);
List<Person> persons = personDao.findPersonByName("张");
persons.forEach((person)-> System.out.println(person));;
}
}