MyBatis表关联一对一简单项目练习

左手的ㄟ右手 2021-09-02 07:37 316阅读 0赞

【功能】对person数据库实现以下功能

1.  增(insertPerson,insertPersons)
2.  删(deletePersonByPkId,deletePersonsById)
3.  改(updatePersonByPkId,updatePersonById)
4.  查(countPersons,getPersonByName,listPersons,listPersonsByNameOrPhone,listPersonsBySex)

【步骤】

【项目创建步骤】创建一个javaweb项目

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 1][] 输入项目名称，选择路径存储项目，点击finish。 如下所示为项目结构图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 2][]

【数据库】

![20201015141913314.png][]

【代码】

1.sqlMapConfig.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="logImpl" value="STDOUT_LOGGING"/>
        </settings>
        <environments default="test">
            <environment id="test">
                <transactionManager type="JDBC"></transactionManager>    
                <dataSource type="POOLED">    
                    <property name="driver" value="org.postgresql.Driver"/>
                    <property name="url" value="jdbc:postgresql://localhost:5432/people?characterEncoding=UTF-8" />
                    <property name="username" value="postgres" />
                    <property name="password" value="root" />
                </dataSource>
            </environment>
        </environments>
    
        
        <mappers>
            <mapper class="main.java.com.mybatisstudy.mapper.PersonMapper"/>
        </mappers>
    </configuration>

2.实体类

package main.java.com.mybatisstudy.entity;
    
    import java.util.UUID;
    
    public class PersonDO {
        private Long id;
        private String personName;
        private String sex;
        private String phone;
    
        public PersonDO() {
        }
    
        public PersonDO(Long id, String personName, String sex, String phone) {
            this.id = id;
            this.personName = personName;
            this.sex = sex;
            this.phone = phone;
        }
    
        public Long getId() {
            return id;
        }
    
        public void setId(Long id) {
            this.id = id;
        }
    
        public String getPersonName() {
            return personName;
        }
    
        public void setPersonName(String personName) {
            this.personName = personName;
        }
    
        public String getSex() {
            return sex;
        }
    
        public void setSex(String sex) {
            this.sex = sex;
        }
    
        public String getPhone() {
            return phone;
        }
    
        public void setPhone(String phone) {
            this.phone = phone;
        }
    
        @Override
        public String toString() {
            return "PersonDO{" +
                    "id=" + id +
                    ", personName='" + personName + '\'' +
                    ", sex=" + sex +
                    ", phone='" + phone + '\'' +
                    '}';
        }
    }

3.创建PeopleMapper.xml

<?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="main.java.com.mybatisstudy.mapper.PersonMapper">
        <resultMap id="PersonDO" type="main.java.com.mybatisstudy.entity.PersonDO">
            <result column="id" property="id"/>
            <result column="person_name" property="personName"></result>
            <result column="sex" property="sex"></result>
            <result column="phone" property="phone"></result>
        </resultMap>
    
        <insert id="insertPerson" parameterType="main.java.com.mybatisstudy.entity.PersonDO">
            insert into person (id,person_name,sex,phone) values (#{id},#{personName},#{sex},#{phone});
        </insert>
    
        <sql id="property">
            id,person_name,sex,phone
        </sql>
    
        <insert id="insertPersons" parameterType="main.java.com.mybatisstudy.entity.PersonDO">
            insert into person(<include refid="property"/>)
            values
            <foreach collection="list" item="personDO" index="index" separator=",">
                (#{personDO.id},#{personDO.personName},#{personDO.sex},#{personDO.phone})
            </foreach>
        </insert>
    
    
        <delete id="deletePersonByPkId" parameterType="Long">
            delete from person where id=#{id}
        </delete>
    
        <update id="updatePersonByPkId">
            update person set phone=#{phone} where id=#{id}
        </update>
    
        <select id="countPersons" resultType="Map">
            select count(*) from person;
        </select>
    
        <select id="getPersonByName" parameterType="String" resultMap="PersonDO">
            select * from person where person_name=#{personName}
        </select>
    
        <select id="listPersonsBySex" parameterType="String" resultMap="PersonDO">
            select * from person where sex=#{sex}
        </select>
    
        <select id="listPersons" resultMap="PersonDO">
            select * from person
        </select>
    
        <select id="listPersonsByNameOrPhone" resultMap="PersonDO">
            select * from person
            <where>
                <if test="personName!=null and personName!=''">
                    person_name=#{personName}
                </if>
                <if test="phone!=null and phone!=''">
                    and phone=#{phone}
                </if>
            </where>
        </select>
    
        <update id="updatePersonById" parameterType="main.java.com.mybatisstudy.entity.PersonDO">
            update person
            <set>
                <if test="personName!=null and personName!=''">
                    person_name=#{personName},
                </if>
                <if test="sex!=null and sex!=''">
                    sex=#{sex},
                </if>
                <if test="phone!=null and phone!=''">
                    phone=#{phone}
                </if>
            </set>
            where id=#{id}
        </update>
    
        <delete id="deletePersonsById" parameterType="Long">
            delete from person where id in
            <foreach collection="list" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
        </delete>
    
    </mapper>

4.创建PeopleMapper接口

package main.java.com.mybatisstudy.mapper;
    
    import main.java.com.mybatisstudy.entity.PersonDO;
    import org.apache.ibatis.annotations.Param;
    
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    public interface PersonMapper {
    
        /**
         * 添加人员
         * @param personDO
         * @return 结果
         */
        int insertPerson(PersonDO personDO);
    
    
        /**
         * 批量添加人员
         * @param personDOs
         * @return 结果
         */
        int insertPersons(List<PersonDO> personDOs);
    
    
        /**
         * 通过pkId删除人员
         * @param id
         * @return 结果
         */
        int deletePersonByPkId(Long id);
    
        /**
         * 通过pkId修改人员信息
         * @param id
         * @param phone
         * @return 结果
         */
        int updatePersonByPkId(@Param("phone") String phone, @Param("id") Long id);
    
        /**
         * 统计人员总数
         * @return 结果
         */
        Map<String,Integer> countPersons();
    
        /**
         * 通过人员姓名获取人员信息（暂不考虑同名人）
         * @param personName
         * @return 人员信息
         */
        PersonDO getPersonByName(String personName);
    
        /**
         * 通过性别获取所有人员
         * @param sex
         * @return 某性别的人员信息列表
         */
        List<PersonDO> listPersonsBySex(String sex);
    
        /**
         * 获取所有人员
         * @return 所有人员信息
         */
        List<PersonDO> listPersons();
    
        /**
         * 动态查询
         * 通过人员姓名或人员电话获取人员信息（考虑同名人员）
         * @param personName
         * @param phone
         * @return 满足条件人员信息
         */
        List<PersonDO> listPersonsByNameOrPhone(@Param("personName") String personName,@Param("phone") String phone);
    
        /**
         * 根据id动态修改人员信息
         * @param personDO
         * @return
         */
        int updatePersonById(PersonDO personDO);
    
        /**
         * 批量删除人员信息表
         * @param ids
         * @return
         */
        int deletePersonsById(List<Long> ids);
    }

5.编写单元测试类一

package test.java.com.mybatisstudy;
    
    import main.java.com.mybatisstudy.entity.PersonDO;
    import main.java.com.mybatisstudy.mapper.PersonMapper;
    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.Test;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.*;
    
    public class MybatisStudy {
    
        @Test
        public void listPersonsTest(){
            SqlSession sqlSession=null;
            try {
                sqlSession=getSqlSession();
                PersonMapper personMapper=sqlSession.getMapper(PersonMapper.class);
                List<PersonDO> persons = personMapper.listPersons();
                if(persons!=null){
                    for(PersonDO personDO:persons){
                        System.out.println(personDO.toString());
                    }
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
    
        }
    
        @Test
        public void listPersonsBySexTest(){
            SqlSession sqlSession= null;
            try {
                sqlSession = getSqlSession();
                PersonMapper personMapper=sqlSession.getMapper(PersonMapper.class);
                List<PersonDO> persons = personMapper.listPersonsBySex("男");
                sqlSession.commit();
                if(persons!=null){
                    for(PersonDO personDo:persons){
                        System.out.println(personDo.toString());
                    }
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    
    
        @Test
        public void getPersonByNameTest(){
            SqlSession sqlSession= null;
            try {
                sqlSession = getSqlSession();
                PersonMapper personMapper=sqlSession.getMapper(PersonMapper.class);
                PersonDO person = personMapper.getPersonByName("HAHA");
                sqlSession.commit();
                System.out.println(person.toString());
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    
        @Test
        public void countPersonsTest(){
            try {
                SqlSession sqlSession=getSqlSession();
                PersonMapper personMapper=sqlSession.getMapper(PersonMapper.class);
                Map<String,Integer> result = personMapper.countPersons();
                System.out.println("共有"+result.get("count")+"条人员信息");
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    
        @Test
        public void updatePersonByPkIdTest(){
            try {
                SqlSession sqlSession=getSqlSession();
                PersonMapper personMapper=sqlSession.getMapper(PersonMapper.class);
                int result=personMapper.updatePersonByPkId("12345",1L);
                sqlSession.commit();
                System.out.println("修改的结果为："+result);
    
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    
        @Test
        public void deletePersonByPkIdTest(){
            try {
                SqlSession sqlSession=getSqlSession();
                PersonMapper personMapper=sqlSession.getMapper(PersonMapper.class);
                int result=personMapper.deletePersonByPkId(3L);
                sqlSession.commit();
                System.out.println("删除的结果为："+result);
            } catch (IOException e) {
                e.printStackTrace();
            }
    
        }
    
        @Test
        public void insertPersonsTest(){
            List<PersonDO> personDOS=new ArrayList<>();
            personDOS.add(new PersonDO(13L,"王二小","男","12309787"));
            personDOS.add(new PersonDO(14L,"王小","女","12323209787"));
            personDOS.add(new PersonDO(15L,"钱钱","男","129999787"));
            personDOS.add(new PersonDO(163L,"吼吼","男","17777787"));
            try {
                SqlSession sqlSession=getSqlSession();
                PersonMapper personMapper=sqlSession.getMapper(PersonMapper.class);
                int result=personMapper.insertPersons(personDOS);
                sqlSession.commit();
                if(result>0){
                    System.out.println("添加人员信息成功");
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
    
        }
    
        @Test
        public void insertPersonTest(){
            PersonDO personDO=new PersonDO();
            personDO.setId(4L);
            personDO.setPersonName("王五");
            //personDO.setSex("男");
            personDO.setPhone("12345678901");
    
            try {
                SqlSession sqlSession = getSqlSession();
                PersonMapper mapper=sqlSession.getMapper(PersonMapper.class);
                int result = mapper.insertPerson(personDO);
                sqlSession.commit();
                if(result==1){
                    System.out.println("添加人员信息成功");
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    
    
        public SqlSession getSqlSession() throws IOException {
            // 定位核心配置文件
            String resource = "main/java/com/mybatisstudy/sqlMapConfig.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            // 创建 SqlSessionFactory
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            // 获取到 SqlSession
            SqlSession sqlSession = sqlSessionFactory.openSession();
            return sqlSession;
        }
    }

6.编写单元测试类二（动态）

完结。。。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70]: /images/20210811/213979756da64b0e92804818eb124641.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 1]: /images/20210811/86808f5c1fc4474b886debd49853ec75.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 2]: /images/20210811/7c13e922687a4f31881fc7b79066536c.png
[20201015141913314.png]: /images/20210811/e4fb36722a544fa4b20450ccee9ef387.png