1.原始方式
/** * 测试使用QueryVo作为查询条件 */ @Test public void testFindByVo(){ QueryVo vo = new QueryVo(); User user = new User(); user.setUserName("%王%"); vo.setUser(user); //5.执行查询一个方法 List<User> users = userDao.findUserByVo(vo); for(User u : users){ System.out.println(u); } } }
public class QueryVo { private User user; public User getUser() { return user; } public void setUser(User user) { this.user = user; } }
public class User implements Serializable { private Integer userId; private String userName; private String userAddress; private String userSex; private Date userBirthday; }
<?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.itheima.dao.IUserDao"> <!-- 配置 查询结果的列名和实体类的属性名的对应关系 --> <resultMap id="userMap" type="uSeR"> <!-- 主键字段的对应 --> <id property="userId" column="id"></id> <!--非主键字段的对应--> <result property="userName" column="username"></result> <result property="userAddress" column="address"></result> <result property="userSex" column="sex"></result> <result property="userBirthday" column="birthday"></result> </resultMap> <!-- 根据queryVo的条件查询用户 --> <select id="findUserByVo" parameterType="com.itheima.domain.QueryVo" resultMap="userMap"> select * from user where username like #{user.userName} </select> </mapper>
2.一对一，一对多
public class AccountTest { private InputStream in; private SqlSession sqlSession; private IAccountDao accountDao; @Before//用于在测试方法执行之前执行 public void init()throws Exception{ //1.读取配置文件，生成字节输入流 in = Resources.getResourceAsStream("SqlMapConfig.xml"); //2.获取SqlSessionFactory SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in); //3.获取SqlSession对象 sqlSession = factory.openSession(true); //4.获取dao的代理对象 accountDao = sqlSession.getMapper(IAccountDao.class); } @After//用于在测试方法执行之后执行 public void destroy()throws Exception{ //提交事务 // sqlSession.commit(); //6.释放资源 sqlSession.close(); in.close(); } /** * 测试查询所有 */ @Test public void testFindAll(){ List<Account> accounts = accountDao.findAll(); for(Account account : accounts){ System.out.println("--------每个account的信息------------"); System.out.println(account); System.out.println(account.getUser()); } } /** * 测试查询所有账户，同时包含用户名称和地址 */ @Test public void testFindAllAccountUser(){ List<AccountUser> aus = accountDao.findAllAccount(); for(AccountUser au : aus){ System.out.println(au); } } }
public class Account implements Serializable { private Integer id; private Integer uid; private Double money; //从表实体应该包含一个主表实体的对象引用 private User user; }
<?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.itheima.dao.IAccountDao"> <!-- 定义封装account和user的resultMap --> <resultMap id="accountUserMap" type="account"> <id property="id" column="aid"></id> <result property="uid" column="uid"></result> <result property="money" column="money"></result> <!-- 一对一的关系映射：配置封装user的内容--> <association property="user" javaType="user"> <id property="id" column="id"></id> <result column="username" property="username"></result> <result column="address" property="address"></result> <result column="sex" property="sex"></result> <result column="birthday" property="birthday"></result> </association> </resultMap> <!-- 查询所有 --> <select id="findAll" resultMap="accountUserMap"> select u.*,a.id as aid,a.uid,a.money from account a , user u where u.id = a.uid; </select> <!--查询所有账户同时包含用户名和地址信息--> <select id="findAllAccount" resultType="accountuser"> select a.*,u.username,u.address from account a , user u where u.id = a.uid; </select> </mapper>
public class UserTest { private InputStream in; private SqlSession sqlSession; private IUserDao userDao; @Before//用于在测试方法执行之前执行 public void init()throws Exception{ //1.读取配置文件，生成字节输入流 in = Resources.getResourceAsStream("SqlMapConfig.xml"); //2.获取SqlSessionFactory SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in); //3.获取SqlSession对象 sqlSession = factory.openSession(true); //4.获取dao的代理对象 userDao = sqlSession.getMapper(IUserDao.class); } @After//用于在测试方法执行之后执行 public void destroy()throws Exception{ //提交事务 // sqlSession.commit(); //6.释放资源 sqlSession.close(); in.close(); } /** * 测试查询所有 */ @Test public void testFindAll(){ List<User> users = userDao.findAll(); for(User user : users){ System.out.println("-----每个用户的信息------"); System.out.println(user); System.out.println(user.getAccounts()); } } }
public class User implements Serializable { private Integer id; private String username; private String address; private String sex; private Date birthday; //一对多关系映射：主表实体应该包含从表实体的集合引用 private List<Account> accounts; }
<?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.itheima.dao.IUserDao"> <!-- 定义User的resultMap--> <resultMap id="userAccountMap" type="user"> <id property="id" column="id"></id> <result property="username" column="username"></result> <result property="address" column="address"></result> <result property="sex" column="sex"></result> <result property="birthday" column="birthday"></result> <!-- 配置user对象中accounts集合的映射 --> <collection property="accounts" ofType="account"> <id column="aid" property="id"></id> <result column="uid" property="uid"></result> <result column="money" property="money"></result> </collection> </resultMap> <!-- 查询所有 --> <select id="findAll" resultMap="userAccountMap"> select u.*,a.id aid,a.uid uid,a.money money from user u left outer join account a on u.id = a.uid </select> <!-- 根据id查询用户 --> <select id="findById" parameterType="INT" resultType="user"> select * from user where id = #{uid} </select> </mapper>
3.多对多
public class UserTest { private InputStream in; private SqlSession sqlSession; private IUserDao userDao; @Before//用于在测试方法执行之前执行 public void init()throws Exception{ //1.读取配置文件，生成字节输入流 in = Resources.getResourceAsStream("SqlMapConfig.xml"); //2.获取SqlSessionFactory SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in); //3.获取SqlSession对象 sqlSession = factory.openSession(true); //4.获取dao的代理对象 userDao = sqlSession.getMapper(IUserDao.class); } @After//用于在测试方法执行之后执行 public void destroy()throws Exception{ //提交事务 // sqlSession.commit(); //6.释放资源 sqlSession.close(); in.close(); } /** * 测试查询所有 */ @Test public void testFindAll(){ List<User> users = userDao.findAll(); for(User user : users){ System.out.println("-----每个用户的信息------"); System.out.println(user); System.out.println(user.getRoles()); } } }
public class User implements Serializable { private Integer id; private String username; private String address; private String sex; private Date birthday; //多对多的关系映射：一个用户可以具备多个角色 private List<Role> roles; }
<?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.itheima.dao.IUserDao"> <!-- 定义User的resultMap--> <resultMap id="userMap" type="user"> <id property="id" column="id"></id> <result property="username" column="username"></result> <result property="address" column="address"></result> <result property="sex" column="sex"></result> <result property="birthday" column="birthday"></result> <!-- 配置角色集合的映射 --> <collection property="roles" ofType="role"> <id property="roleId" column="rid"></id> <result property="roleName" column="role_name"></result> <result property="roleDesc" column="role_desc"></result> </collection> </resultMap> <!-- 查询所有 --> <select id="findAll" resultMap="userMap"> select u.*,r.id as rid,r.role_name,r.role_desc from user u left outer join user_role ur on u.id = ur.uid left outer join role r on r.id = ur.rid </select> <!-- 根据id查询用户 --> <select id="findById" parameterType="INT" resultType="user"> select * from user where id = #{uid} </select> </mapper>
public class RoleTest { private InputStream in; private SqlSession sqlSession; private IRoleDao roleDao; @Before//用于在测试方法执行之前执行 public void init()throws Exception{ //1.读取配置文件，生成字节输入流 in = Resources.getResourceAsStream("SqlMapConfig.xml"); //2.获取SqlSessionFactory SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in); //3.获取SqlSession对象 sqlSession = factory.openSession(true); //4.获取dao的代理对象 roleDao = sqlSession.getMapper(IRoleDao.class); } @After//用于在测试方法执行之后执行 public void destroy()throws Exception{ //提交事务 // sqlSession.commit(); //6.释放资源 sqlSession.close(); in.close(); } /** * 测试查询所有 */ @Test public void testFindAll(){ List<Role> roles = roleDao.findAll(); for(Role role : roles){ System.out.println("---每个角色的信息----"); System.out.println(role); System.out.println(role.getUsers()); } } }
public class Role implements Serializable { private Integer roleId; private String roleName; private String roleDesc; //多对多的关系映射：一个角色可以赋予多个用户 private List<User> users; }
<?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.itheima.dao.IRoleDao"> <!--定义role表的ResultMap--> <resultMap id="roleMap" type="role"> <id property="roleId" column="rid"></id> <result property="roleName" column="role_name"></result> <result property="roleDesc" column="role_desc"></result> <collection property="users" ofType="user"> <id column="id" property="id"></id> <result column="username" property="username"></result> <result column="address" property="address"></result> <result column="sex" property="sex"></result> <result column="birthday" property="birthday"></result> </collection> </resultMap> <!--查询所有--> <select id="findAll" resultMap="roleMap"> select u.*,r.id as rid,r.role_name,r.role_desc from role r left outer join user_role ur on r.id = ur.rid left outer join user u on u.id = ur.uid </select> </mapper>