目录
一、Mybatis学习。
(1)MyBatis的多表操作。
(1.1)一对一查询。
第1种映射OrderMapper.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="mapper.OrderMapper">
<resultMap id="orderMap" type="domain.Order">
<!--手动指定字段与实体属性的映射关系
column:数据表的字段名称
property:实体的属性名称
-->
<id column="oid" property="id"></id><!--因为数据库多表查询的两个表都有id-->
<result column="ordertime" property="ordertime"></result>
<result column="total" property="total"></result>
<result column="uid" property="user.id"></result>
<result column="username" property="user.username"></result>
<result column="password" property="user.password"></result>
<result column="birthday" property="user.birthday"/>
</resultMap>
<select id="findAll" resultMap="orderMap">
select *,o.id oid from orders o,user u where o.uid = u.id
</select>
</mapper>
第2种映射OrderMapper.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="mapper.OrderMapper">
<resultMap id="orderMap" type="domain.Order">
<!--手动指定字段与实体属性的映射关系
column:数据表的字段名称
property:实体的属性名称
-->
<id column="oid" property="id"></id><!--因为数据库多表查询的两个表都有id-->
<result column="ordertime" property="ordertime"></result>
<result column="total" property="total"></result>
<!--association标签属性:
property:当前实体(order)中的属性名称(private User user)user
javaType:当前实体(order)中的属性的类型(User)
-->
<association property="user" javaType="domain.User">
<id column="uid" property="id"></id>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<result column="birthday" property="birthday"></result>
</association>
</resultMap>
<select id="findAll" resultMap="orderMap">
select *,o.id oid from orders o,user u where o.uid = u.id
</select>
</mapper>
(1.2)一对多查询。
映射UserMapper.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="mapper.UserMapper">
<!--一对多查询的配置-->
<resultMap id="userMap" type="domain.User">
<!--user的信息-->
<id column="uid" property="id"/>
<result column="username" property="username"/>
<result column="password" property="password"/>
<result column="birthday" property="birthday"/>
<!--配置集合信息:
property:集合名称
ofType:当前集合中的数据类型
-->
<collection property="orderList" ofType="domain.Order">
<!--封装order的数据-->
<id column="oid" property="id"/>
<result column="ordertime" property="ordertime"/>
<result column="total" property="total"/>
</collection>
</resultMap>
<select id="findAll" resultMap="userMap">
select *,o.id oid from user u,orders o where u.id = o.uid
</select>
</mapper>
(1.3)多对多查询。
映射UserMapper.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="mapper.UserMapper">
<resultMap id="userRoleMap" type="domain.User">
<!--user的信息-->
<id column="userid" property="id"/>
<result column="username" property="username"/>
<result column="password" property="password"/>
<result column="birthday" property="birthday"/>
<collection property="roleList" ofType="domain.Role">
<!--user内部的roleList信息-->
<id column="roleid" property="id"/>
<result column="roleName" property="roleName"/>
<result column="roleDesc" property="roleDesc"/>
</collection>
</resultMap>
<!--多对多的查询配置-->
<select id="findUserAndRole" resultMap="userRoleMap">
select * from user u,sys_user_role ur,sys_role r where u.id = ur.userid and ur.roleid = r.id
</select>
</mapper>
(1.4)三种查询知识小结。
(2)MyBatis的注解开发。
(2.1)MyBatis常用注解。
1.@One的使用:
@Select("select * from user where id = #{id}")
public User findById(int id);
@Select("select * from orders")
@Results({
@Result(column = "id",property = "id"),
@Result(column = "ordertime",property = "ordertime"),
@Result(column = "total",property = "total"),
@Result(
property = "user",//要封装的属性名称(Order类中的属性名称)
//(即上面查询语句的哪个字段的值为下面查询语句的参数)
column = "uid",//根据哪个字段去查询user表的数据
javaType = User.class,//要封装的属性名称
//select属性,代表查询哪个接口的方法获得数据
one = @One(select = "mapper.UserMapper.findById")
)
})
public List<Order> findAll();
2.@Many的使用:
@Select("select * from orders where uid = #{uid}")
public List<Order> findByUid(int uid);
@Select("select * from user")
@Results({
@Result(id = true,column = "id",property = "id"),//id = true标识该属性是实体类的主键。
@Result(column = "username",property = "username"),
@Result(column = "password",property = "password"),
@Result(
property = "orderList",//要封装的属性名称(User类中的属性名称)
column = "id",//根据哪个字段去查询orders表的数据(即上面查询语句的哪个字段的值为下面查询语句的参数)
javaType = List.class,//要封装的属性名称******************************************************注意
//select属性,代表查询哪个接口的方法获得数据
many = @Many(select = "mapper.OrderMapper.findByUid")
)
})
public List<User> findUserAndOrderAll();
注意:一对一(实体类)与一对多(集合类)的javaType的类型不一样。
(2.2)简单增删改查(封装类中没有引用类属性):单表查询
1.接口类:
package mapper;
public interface UserMapper {
@Insert("insert into user values(#{id},#{username},#{password},#{birthday})")
public void save(User user);
@Update("update user set username = #{username},password = #{password} where id = #{id}")
public void update(User user);
@Delete("delete from user where id = #{id}")
public void delete(int id);
@Select("select * from user where id = #{id}")
public User findById(int id);
@Select("select * from user")
public List<User> findAll();
}
2.测试类:
package test;
public class MyBatisTest {
private UserMapper mapper;
@Before
public void before() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
mapper = sqlSession.getMapper(UserMapper.class);
}
@Test
public void testSave(){
User user = new User();
user.setUsername("tom");
user.setPassword("abc");
mapper.save(user);
}
@Test
public void testUpdate(){
User user = new User();
user.setId(24);
user.setUsername("lucy");
user.setPassword("123");
mapper.update(user);
}
@Test
public void testDelete(){
User user = new User();
mapper.delete(24);
}
@Test
public void testFindById(){
User user = mapper.findById(24);
System.out.println(user);
}
@Test
public void testFindAll(){
List<User> userList = mapper.findAll();
System.out.println(userList);
}
}
3.核心映射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>
<!--加载映射文件-->
<!--<mappers>
<mapper resource="mapper/UserMapper.xml"></mapper>
<mapper resource="mapper/OrderMapper.xml"/>
</mappers>-->
<!--加载映射关系-->
<mappers>
<!--指定接口所在的包-->
<package name="mapper"/>
或者
<!--指定接口-->
<mapper class="mapper.UserMapper"/>
</mappers>
</configuration>
(2.3)一对一查询:多表查询。
1.接口类:
第1种注解配置:
package mapper;
public interface OrderMapper {
@Select("select *,o.id oid from orders o,user u where o.uid = u.id")
@Results({
@Result(column = "oid",property = "id"),
@Result(column = "ordertime",property = "ordertime"),
@Result(column = "total",property = "total"),
@Result(column = "uid",property = "user.id"),
@Result(column = "username",property = "user.username"),
@Result(column = "password",property = "user.password")
})
public List<Order> findAll();
}
第2种注解配置:
@Select("select * from user where id = #{id}")
public User findById(int id);
package mapper;
public interface OrderMapper {
@Select("select * from orders")
@Results({
@Result(column = "id",property = "id"),
@Result(column = "ordertime",property = "ordertime"),
@Result(column = "total",property = "total"),
@Result(
property = "user",//要封装的属性名称(Order类中的属性名称)
//(即上面查询语句的哪个字段的值为下面查询语句的参数)
column = "uid",//根据哪个字段去查询user表的数据
javaType = User.class,//要封装的属性名称
//select属性,代表查询哪个接口的方法获得数据
one = @One(select = "mapper.UserMapper.findById")
)
})
public List<Order> findAll();
}
2.测试类:
package test;
public class MyBatisTest2 {
private OrderMapper mapper;
@Before
public void before() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
mapper = sqlSession.getMapper(OrderMapper.class);
}
@Test
public void testFindAll(){
List<Order> orderList = mapper.findAll();
for (Order order : orderList) {
System.out.println(order);
}
}
}
3.核心映射xml文件:
<!--加载映射关系-->
<mappers>
<!--指定接口所在的包-->
<package name="mapper"/>
<!-- <mapper class="mapper.UserMapper"/>-->
</mappers>
(2.4)一对多查询:多表查询。
1.接口类:
@Select("select * from orders where uid = #{uid}")
public List<Order> findByUid(int uid);
@Select("select * from user")
@Results({
@Result(id = true,column = "id",property = "id"),//id = true标识该属性是实体类的主键。
@Result(column = "username",property = "username"),
@Result(column = "password",property = "password"),
@Result(
property = "orderList",//要封装的属性名称(User类中的属性名称)
column = "id",//根据哪个字段去查询orders表的数据(即上面查询语句的哪个字段的值为下面查询语句的参数)
javaType = List.class,//要封装的属性名称******************************************************注意
//select属性,代表查询哪个接口的方法获得数据
many = @Many(select = "mapper.OrderMapper.findByUid")
)
})
public List<User> findUserAndOrderAll();
2.测试类:
package test;
public class MyBatisTest3 {
private UserMapper mapper;
@Before
public void before() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
mapper = sqlSession.getMapper(UserMapper.class);
}
@Test
public void testFindAll(){
List<User> userList = mapper.findUserAndOrderAll();
for (User user : userList) {
System.out.println(user);
}
}
}
3.核心映射xml文件:
<!--加载映射关系-->
<mappers>
<!--指定接口所在的包-->
<package name="mapper"/>
<!-- <mapper class="mapper.UserMapper"/>-->
</mappers>
(2.5)多对多查询:多表查询。
理解:a表,b表:即a到b表一对多,b到a表也可以一对多。(实质查询结果还是一对多)
a -》b:一对多。
b -》a:一对多。
多对多查询:其中一个表到另外一个表都可以是一对多,则为多对多。
一对多查询:只有一个表到另外一个表是一对多,反过来则是一对一。
区别:数据库实现一对多,需要两张表;实现多对多,需要三张表;(只是导致语句不一样而已)
1.接口类:
package mapper;
public interface RoleMapper {
@Select("select * from sys_user_role ur,sys_role r where ur.userid = #{uid} and ur.roleid = r.id")
public List<Role> findByUid(int uid);
}
package mapper;
public interface UserMapper {
@Select("select * from user")
@Results({
@Result(id = true,column = "id",property = "id"),
@Result(column = "username",property = "username"),
@Result(column = "password",property = "password"),
@Result(
property = "roleList",
column = "id",
javaType = List.class,
many = @Many(select = "mapper.RoleMapper.findByUid")
)
})
public List<User> findUserAndRoleAll();
}
2.测试类:
package test;
public class MyBatisTest4 {
private UserMapper mapper;
@Before
public void before() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
mapper = sqlSession.getMapper(UserMapper.class);
}
@Test
public void testFindAll(){
List<User> userList = mapper.findUserAndRoleAll();
for (User user : userList) {
System.out.println(user);
}
}
}
3.核心映射xml文件:
<!--加载映射关系-->
<mappers>
<!--指定接口所在的包-->
<package name="mapper"/>
<!-- <mapper class="mapper.UserMapper"/>-->
</mappers>
(3)注解配置的一些缺陷(与xml配置的一些区别)。
例如:select *,o.id oid from user u,orders o where u.id = o.uid
如果使用注解配置:则会返回user表的所有数据条(即都满足),然后开始执行里面的子查询,把查到的封装进user类的字段中,如果没有查到则user类中的字段为null。where u.id = o.uid条件可以认为根本就没有使用。
具体区别:注解配置复杂sql语句相当于两条语句(有先后顺序),xml是一条语句。
@Select("select * from orders where uid = #{uid}")
public List<Order> findByUid(int uid);
@Select("select * from user")
@Results({
@Result(id = true,column = "id",property = "id"),//id = true标识该属性是实体类的主键。
@Result(column = "username",property = "username"),
@Result(column = "password",property = "password"),
@Result(
property = "orderList",//要封装的属性名称(User类中的属性名称)
column = "id",//根据哪个字段去查询orders表的数据(即上面查询语句的哪个字段的值为下面查询语句的参数)
javaType = List.class,//要封装的属性名称******************************************************注意
//select属性,代表查询哪个接口的方法获得数据
many = @Many(select = "mapper.OrderMapper.findByUid")
)
})
public List<User> findUserAndOrderAll();