传入POJO对象
QueryVo.java
public class QueryVo implements Serializable{
private User user;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
}
SQL映射文件
<!-- 传入POJO对象，查询用户 -->
<select id="findUserByPOJO" parameterType="QueryVo" resultType="User">
select * from user where id = #{user.id}
</select>
测试类
@Test
public void func9() throws IOException {
// 1.加载配置文件
InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
// 2.创建sqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
// 3.获取sqlsession
SqlSession sqlSession = sqlSessionFactory.openSession();
// -------------------------------------------
// 4.SqlSEssion帮我生成一个实现类（给接口的字节码文件）
UserDao mapper = sqlSession.getMapper(UserDao.class);
// 创建一个POJO对象
QueryVo vo = new QueryVo();
User u = new User();
u.setId(2);
vo.setUser(u);
User userByPOJO = mapper.findUserByPOJO(vo);
System.out.println(userByPOJO);
// -------------------------------------------
}
resultMap（手动指定字段）
数据库表
Order实体文件
public class Order {
// 订单id
private int id;
// 用户id
private Integer userId;
// 订单号
private String number;
// 订单创建时间
private Date createtime;
// 备注
private String note;
get/set。。。
}
SQL映射文件
由于实体和SQL中的userid字段不同，所以要使用resultMap
<resultMap id="jzy" type="Order">
<result column="user_id" property="userId" />
</resultMap>
<select id="findOrderById" parameterType="int" resultMap="jzy">
select * from `order` where id = #{id}
-- order是关键字，所以要用``包起来
</select>
OrderDao.java
public interface OrderDao {
Order findOrderById(int i);
}
测试类
public void func() throws IOException {
// 1.加载配置文件
InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
// 2.创建sqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
// 3.获取sqlsession
SqlSession sqlSession = sqlSessionFactory.openSession();
// -------------------------------------------
// 4.执行相应的SQL语句
OrderDao mapper = sqlSession.getMapper(OrderDao.class);
Order orderById = mapper.findOrderById(1);
System.out.println(orderById);
// -------------------------------------------
}
一.动态SQL
1）if标签&&where标签
<!-- 根据性别和名字查询用户 where 可以去掉第一个前ANd -->
<select id="selectUserBySexAndUsername" parameterType="User" resultType="User">
<where>
<if test="sex != null and sex != ‘‘">
and sex = #{sex}
</if>
<if test="username != null and username != ‘‘">
and username = #{username}
</if>
</where>
</select>
2）foreach标签
a.直接传入数组
UserDao.java
public interface UserDao {
List<User> selectUserByIds(int[] ids);
}
配置文件
<!-- 多个ID select * from user where id in (1,2,3)-->
<select id="selectUserByIds" parameterType="int[]" resultType="User">
<include refid="selector"/>
<where>
<!--只要是传入的数组，collection就是"array"-->
<foreach collection="array" item="id" separator="," open="id in (" close=")">
#{id}
</foreach>
</where>
</select>
测试类
@Test
public void func3() throws IOException {
// 1.加载配置文件
InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
// 2.创建sqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
// 3.获取sqlsession
SqlSession sqlSession = sqlSessionFactory.openSession();
// -------------------------------------------
// 方法一：直接传入一个数组
int[] ids = {1,2,3};
UserDao mapper = sqlSession.getMapper(UserDao.class);
List<User> list = mapper.selectUserByIds(ids);
System.out.println(list);
// -------------------------------------------
}
b.直接传入集合
<!-- 多个ID select * from user where id in (1,2,3)-->
<select id="selectUserByIds" parameterType="Integer" resultType="User">
<include refid="selector"/>
<where>
<!--只要是传入的集合，collection就是"list"-->
<foreach collection="list" item="id" separator="," open="id in (" close=")">
#{id}
</foreach>
</where>
</select>
测试类
// 方法二：直接传入一个集合 List<Integer> ids = new ArrayList<>(); ids.add(1); ids.add(2); ids.add(3); UserDao mapper = sqlSession.getMapper(UserDao.class); List<User> list = mapper.selectUserByIds(ids); System.out.println(list);
c.传入一个POJO，里面有集合或者数组
<!-- 多个ID select * from user where id in (1,2,3)-->
<select id="selectUserByIds" parameterType="QueryVo" resultType="User">
<include refid="selector"/>
<where>
<foreach collection="ids" item="id" separator="," open="id in (" close=")">
#{id}
</foreach>
</where>
</select>
测试类
// 方法三：传入一个POJO
//List<Integer> ids = new ArrayList<>();
//
//ids.add(1);
//ids.add(2);
//ids.add(3);
int[] ids = {1,2,3};
QueryVo vo = new QueryVo();
vo.setIds(ids);
UserDao mapper = sqlSession.getMapper(UserDao.class);
List<User> list = mapper.selectUserByIds(vo);
System.out.println(list);
二.关联查询
1、一对一查询
1）在Order中添加一个user字段并提供get，set方法
2）书写接口
List<Order> findOrderList();
3）书写配置文件
<resultMap id="jbb" type="Order"> <id column="id" property="id"/> <result column="user_id" property="userId" /> <result column="number" property="number" /> <!-- 一对一配置 --> <association property="user" javaType="User"> <id column="user_id" property="id" /> <result column="username" property="username"/> </association> </resultMap> <select id="findOrderList" resultMap="jbb"> select o.id,o.user_id,o.number,o.createtime,u.username from `order` o left join user u on o.user_id=u.id </select>
4）测试类
public void func4() throws IOException {
// 1.加载配置文件
InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
// 2.创建sqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
// 3.获取sqlsession
SqlSession sqlSession = sqlSessionFactory.openSession();
// -------------------------------------------
OrderDao mapper = sqlSession.getMapper(OrderDao.class);
List<Order> orderList = mapper.findOrderList();
System.out.println(orderList);
// -------------------------------------------
}
2、一对多查询
1）在User中添加一个集合，保存Order对象
2）书写接口
List<User> findUserAllOrder();
3）SQL映射文件
<resultMap id="jjj" type="User"> <id column="user_id" property="id"/> <result column="username" property="username"/> <!-- 一对多 --> <collection property="orderList" ofType="Order"> <id column="id" property="id"/> <result column="number" property="number"/> <result column="createtime" property="createtime"/> <result column="user_id" property="userId"/> </collection> </resultMap> <select id="findUserAllOrder" resultMap="jjj"> select o.id,o.user_id,o.number,o.createtime,u.username from user u left join `order` o on o.user_id=u.id </select>
测试类
@Test
public void func5() throws IOException {
// 1.加载配置文件
InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
// 2.创建sqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
// 3.获取sqlsession
SqlSession sqlSession = sqlSessionFactory.openSession();
// -------------------------------------------
OrderDao mapper = sqlSession.getMapper(OrderDao.class);
List<User> userAllOrderList = mapper.findUserAllOrder();
System.out.println(userAllOrderList);
// -------------------------------------------
}