(二)

上一篇当中使用的是原生接口, 但实际开发过程中更多使用 MyBaits 的 Mapper 代理实现自定义接口, 用法如下:

新建一个包, 在其中创建接口 UserRepository.java, 在这个接口中定义要实现的 sql 操作, 然后创建这个接口文件的同名配置文件(XML), 在该配置文件中写 sql.

UserRepository.java:

package com.ryan.repository;

import com.ryan.javaClass.User;
import java.util.List;

public interface UserRepository {
    public int insert(User user);
    public int update(User user);
    public int deleteById(long id);
    public List<User> findAll();
    public User findById(long id);
}

UserRepository.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="com.ryan.repository.UserRepository">
    <insert id="insert" parameterType="com.ryan.javaClass.User">
        insert into student(id,name,phoneNumber)values(#{id},#{name},#{phoneNumber})
    </insert>

    <update id="update" parameterType="com.ryan.javaClass.User">
        update student set name = #{name}, phoneNumber = #{phoneNumber} where id = #{id}
    </update>

    <delete id="deleteById" parameterType="long">
        delete from student where id = #{id};
    </delete>

    <select id="findAll" parameterType="com.ryan.javaClass.User" resultType="com.ryan.javaClass.User">
        select * from student;
    </select>

    <select id="findById" parameterType="long" resultType="com.ryan.javaClass.User">
        select * from student where id = #{id};
    </select>
</mapper>

在 Mybatis 的配置文件中注册此 mapper:

...
        </environment>
    </environments>

    <!--注册userMapper.xml-->
    <mappers>
        <mapper resource="com/ryan/mapper/userMapper.xml"></mapper>
        <mapper resource="com/ryan/repository/UserRepository.xml"></mapper>
    </mappers>
</configuration>

创建 TestRepository.java:

public class TestRepository {

    public static void main(String[] args) {
        InputStream inputStream = TestRepository.class.getClassLoader().getResourceAsStream("mybatis-config.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();

        //获取实现接口的代理对象
        UserRepository userRepository = sqlSession.getMapper(UserRepository.class);
//        //添加对象
//        User user = new User(9999, "织田信长", 601937);
//        int result = userRepository.insert(user);
//        sqlSession.commit();
//
//        //查询全部对象
        List<User> list = userRepository.findAll();
        for (User user1:list) {
            System.out.println(user1);
        }
//        //不改变数据的操作不需要commit
//        sqlSession.close();
//
//        //通过id查询对象
        User user1 = userRepository.findById(5877);
        System.out.println("");
        System.out.println(user1);
        sqlSession.close();
//
//        //修改对象
//        User user2 = userRepository.findById(5876);
//        user2.setName("吕布");
//        user2.setPhoneNumber(600001);
//        int result2 = userRepository.update(user2);
//        sqlSession.commit();
//        System.out.println(result2);
//        sqlSession.close();
//
//        //通过id删除对象
//        int result3 = userRepository.deleteById(1433);
//        System.out.println(result3);
//        sqlSession.commit();
//        sqlSession.close();
    }
}

查询结果展示:

---

多条件查询

在 xml 文件中, #{ } 相当于一个占位符, 如果只按一个字段查询, 里面的内容可以随便写, 如:

    <select id="findById" parameterType="long" resultType="com.ryan.javaClass.User">
        select * from student where id = #{xxx};
    </select>

但如果要按多个字段查询, 则写法有规则, 如下例示:

    <select id="findByNameAndPhone" resultType="com.ryan.javaClass.User">
        select * from student where name = #{param1} and phoneNumber = #{param2};
    </select>

//        通过多个参数查询
        User user = userRepository.findByNameAndPhone("Dobby", 666888);
        System.out.println(user);
        sqlSession.close();

---

一对多查询

有时, 我们在跨表查询的时候会有这样的需求: 在把最终查询到的结果集映射成 Java 对象的时候, 我们想要对象中的一个属性对映结果集中的多个字段, 此时要如何操作呢? 以下例示:

数据库中有两张表, student 和 country:

 

 创建 CountryInfo.java:

@Data
public class CountryInfo {
    private int id;
    private String country;
}

创建 CompleteInfo.java:

@Data
public class CompleteInfo {
    private int id;
    private String name;
    private int phoneNumber;
    private CountryInfo countryInfo;
}

在接口中定义查询方法名:

public interface UserRepository {

    public CompleteInfo findBothById(int id);
}

*编辑配置文件:

...
    </select>

    <resultMap id="completeInfoMap" type="com.ryan.javaClass.CompleteInfo">
<!--        主键用 id 标签, 其他的用 result 标签-->
        <id column="id" property="id"></id>
        <result column="name" property="name"></result>
        <result column="phoneNumber" property="phoneNumber"></result>
<!--        使用association标签实现整合多个字段, 对映到一个对象-->
        <association property="countryInfo" javaType="com.ryan.javaClass.CountryInfo">
            <id column="id" property="id"></id>
            <result column="country" property="country"></result>
        </association>
    </resultMap>
<!--    使用resultMap声明返回类型-->
    <select id="findBothById" parameterType="int" resultMap="completeInfoMap">
        select * from student s, country c where s.id=c.id and s.id=#{id};
    </select>
</mapper>

测试:

    public static void main(String[] args) {
        InputStream inputStream = TestRepository.class.getClassLoader().getResourceAsStream("mybatis-config.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();

        //获取实现接口的代理对象
        UserRepository userRepository = sqlSession.getMapper(UserRepository.class);


        //一对多查询
        CompleteInfo completeInfo = userRepository.findBothById(4399);
        System.out.println(completeInfo);
        sqlSession.close();
    }

结果:

---

反向一对多查询

与一对多相对地, 我们有时也会有这样的需求: 查询的结果集中包含多条数据, 我们想要将多条数据放到一个集合中, 再对映到一个 Java 对象进行展示, 如下示例:

依然是上面两张表, 创建 Country.java:

@Data
public class Country {

    private int id;
    private String country;
    private List<Student> students;
}

创建 Student.java:

@Data
public class Student {

    private int id;
    private String name;
    private int phoneNumber;
}

在接口中定义查询方法名:

public interface UserRepository {

    public List<Country> findCountry(String country);
}

*编辑配置文件:

...


    <resultMap id="country" type="com.ryan.javaClass.Country">
        <id column="id" property="id"></id>
        <result column="country" property="country"></result>
<!--        将多条数据整合成一个List需要用 collection 和 ofTyep-->
        <collection property="students" ofType="com.ryan.javaClass.Student">
            <id column="id" property="id"></id>
            <result column="name" property="name"></result>
            <result column="phoneNumber" property="phoneNumber"></result>
        </collection>
    </resultMap>
    <select id="findCountry" parameterType="String" resultMap="country">
        select s.id, s.name, s.phoneNumber, c.country from student s, country c where s.id=c.id and c.country=#{country};
    </select>
</mapper>

测试:

    public static void main(String[] args) {
        InputStream inputStream = TestRepository.class.getClassLoader().getResourceAsStream("mybatis-config.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();

        //获取实现接口的代理对象
        UserRepository userRepository = sqlSession.getMapper(UserRepository.class);

        //反向一对多
        List<Country> country = userRepository.findCountry("中国");
        System.out.println(country);
        sqlSession.close();
    }

}

结果: