小知识,大挑战!本文正在参与“程序员必备小知识”创作活动。
环境准备
开发背景
现在我们有四张表, 一个用户表, 一个订单表, 一个角色表, 一个用户角色中间表
假设一个用户可以有多个订单
一个订单只对应一个用户
一个用户可以有多个角色
一个角色可以分配给多个用户
准备数据库表
users表
orders表
roles表
user_role表
准备java实体类
一定要实现它们的setter和getter, 这里我就不贴完整代码占篇幅了
User实体类
public class User {
private int id;
private String username;
private String password;
private List<Order> orderList;
private List<Role> roleList;
}
Order实体类
public class Order {
private int id;
private Date ordertime;
private int total;
private User user;
}
Role实体类
public class Role {
private int id;
private String roleName;
private String roleDesc;
}
准备java接口类
UserMapper, 该接口用来定义查询用户及其角色时的一些方法声明
public interface UserMapper {
public List<User> findAll();
public List<User> findUserRole();
}
OrderMapper, 该接口用来定义查询订单及其所属用户时的一些方法声明
public interface OrderMapper {
public List<Order> findAll();
}
准备UserMapper配置文件
该配置文件用来定义与UserMapper接口类的映射
其中mapper namespace="com.raylee.mapper.UserMapper"用来映射名为UserMapper的接口类
其中resultMap id="userMap" type="User"用来将findAll查询的结果与对应的实体类进行属性的映射
其中resultMap id="userRoleMap" type="user"用来将findUserRole查询的结果与对应的实体类进行属性的映射
其中select id="findAll" resultMap="userMap"用来查询users表中所有的用户
其中select id="findUserRole" resultMap="userMap"用来查询users表中所有的用户以及它们对应的角色
<mapper namespace="com.raylee.mapper.UserMapper">
<resultMap id="userMap" type="User">
<id column="uid" property="id"></id>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<collection property="orderList" ofType="order">
<id column="oid" property="id"></id>
<result column="ordertime" property="ordertime"></result>
<result column="total" property="total"></result>
</collection>
</resultMap>
<select id="findAll" resultMap="userMap">
select *, u.id uid, o.id oid from users u, orders o where u.id = o.uid
</select>
<resultMap id="userRoleMap" type="user">
<id column="uid" property="id"></id>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<collection property="roleList" ofType="role">
<id column="rid" property="id"></id>
<result column="roleName" property="roleName"></result>
<result column="roleDesc" property="roleDesc"></result>
</collection>
</resultMap>
<select id="findUserRole" resultMap="userRoleMap">
select *, u.id uid, r.id rid from users u, user_role ur, roles r where u.id = ur.uid and ur.rid = r.id
</select>
</mapper>
准备OrderMapper配置文件
<mapper namespace="com.raylee.mapper.OrderMapper">
<resultMap id="orderMap" type="order">
<id column="oid" property="id"/>
<result column="ordertime" property="ordertime"/>
<result column="total" property="total"/>
<association property="user" javaType="user">
<result column="uid" property="id"/>
<result column="username" property="username"/>
<result column="password" property="password"/>
</association>
</resultMap>
<select id="findAll" resultMap="orderMap">
select *, o.id oid, u.id uid from orders o, users u where o.uid = u.id
</select>
</mapper>
准备SqlMapConfig配置文件
- 使用typeAliases标签给我们的自定义全类名起一个短小精悍的别名
<!-- 自定义别名-->
<typeAliases>
<typeAlias type="com.raylee.domain.User" alias="user"/>
<typeAlias type="com.raylee.domain.Order" alias="order"/>
<typeAlias type="com.raylee.domain.Role" alias="role"/>
</typeAliases>
- 进行连接数据库的配置
<!-- 数据源环境-->
<environments default="developement">
<environment id="developement">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/LCLDataBase"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
- 加载定义xml和接口映射的xml文件
<!-- 加载核心配置文件-->
<mappers>
<mapper resource="UserMapper.xml"/>
<mapper resource="OrderMapper.xml"/>
</mappers>
进行数据查询
一对多查询
每个订单对应一个用户
InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = build.openSession(true);
// 通过sqlSession获得OrderMapper的映射对象
OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
// 然后通过mapper调用findAll方法来查询所有的订单
List<Order> all = mapper.findAll();
for (Order order : all) {
System.out.println(order);
}
多对一查询
每个用户有多个订单
InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = build.openSession(true);
// 通过sqlSession获得UserMapper的映射对象
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
// 然后通过mapper调用findAll方法来查询所有的用户及其所对应的订单
List<User> all = mapper.findAll();
for (User user : all) {
System.out.println(user);
}
多对多查询
每个用户有多个角色, 每个角色可以分配给多个用户
InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = build.openSession(true);
// 通过sqlSession获得UserMapper的映射对象
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
// 然后通过mapper调用findUserRole方法来查询所有的用户及其角色
List<User> all = mapper.findUserRole();
for (User user : all) {
System.out.println(user);
}