(二) - 通过Mapper代理实现自定义接口
上一篇当中使用的是原生接口, 但实际开发过程中更多使用 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(); } }
结果:
