(1)需求:根据性别和名称来查询用户
(1)User.java
package cn.shu.pojo;
import java.io.Serializable;
import java.util.Date;
public class User implements Serializable{
//实现序列化接口
/**
*
*/
private static final long serialVersionUID = 1L;
private Integer id;
private String username;
private String sex;
private Date birthday;
private String address;
/*setters and getters and toString*/
数据库中user表的数据:
(2)sqlMapperConfig.xml
<mappers>
<!-- sql包和sqlMapConfig.xml处于并列的位置,都在src下
映射器有三种属性,resource,class,url,都可以指定Mapper配置文件的位置
-->
<!--
方法二:使用class属性找到Mapper文件的位置,
此种方法要求mapper接口名称和mapper映射文件名称相同,且放在同一个目录中,此时在Mapper包下
创建一个名为UserMapper.xml(与接口UserMapper.java同名)文件,内容与User.xml内容相同-->
<mapper class="cn.shu.mapper.UserMapper"/>
<!-- 配置文件较多时,使用package元素 标签,此种方法要求mapper接口名称和mapper映射文件名称相同,
且放在同一个目录中,此时在Mapper包下 -->
<!-- <mapper resource="sqlmap/User.xml"/> -->
<!-- <package name="cn.shu.mapper"/> -->
<!-- <mapper class="cn.shu.mapper.OrderMapper"/> -->
</mappers>
工程中Mapper配置文件接口
(3)UserMapper.java
package cn.shu.mapper;
import java.util.List;
import cn.shu.pojo.QueryVo;
import cn.shu.pojo.User;
public interface UserMapper {
//使用Mapper动态代理不用写实现类(userDaoimpl),只用写接口就行
//四个原则
/*
* 1.接口中方法名(selectUserBySexAndUsername)等于User.xml中id名
* 2.返回值类型(List<User>) 与Mapper.xml文件中返回值类型(resultType)一致
* 3.方法中入参类型与Mapper.xml中入参的类型(parameterType)要一致
* 4.命名空间要绑定此接口
* <mapper namespace="cn.shu.mapper.UserMapper"></mapper>
* */
//根据性别和名字查询用户
public List<User> selectUserBySexAndUsername(User user);
}
(4)UserMapper.xml
<mapper namespace="cn.shu.mapper.UserMapper">
<!-- 通过id查询一个用户 -->
<select id="findUserById" parameterType="Integer" resultType="User">
<!-- resultMap为手动映射,resultType为自动映射,自动映射要求数据库中的表字段和User类的字段一致
否则必须进行手动映射.返回类型是全限定名称
如果返回类型写正确了,按住Ctrl可以进入User类中-->
select * from user where id = #{v}
<!-- #{}表示占位符,此时#{}中间可以随便写,parameterType 表示id类型 -->
</select>
<!-- sql标签可以讲重复的sql部分提取出来,使用include引用即可 -->
<sql id="sqlselect">
select * from user
</sql>
<!-- 根据性别和名字查询用户 -->
<select id="selectUserBySexAndUsername" parameterType="User" resultType="User">
<!-- select * from user -->
<include refid="sqlselect"/>
<!-- where标签可以去掉第一个前and,这样会减少查询条件改变时的语法错误
<if test="sex != null and sex != ''">
and sex=#{sex} 前and
</if>
<if test="sex != null and sex != ''">
sex=#{sex} and 后and
</if>
-->
<where>
<!-- 查询时不一定有性别和名字字段,需要进行判断 -->
<if test="sex != null and sex != ''">
sex=#{sex}
</if>
<if test="username != null and username != ''">
and username=#{username}
</if>
</where>
<!-- sex=#{sex} and username=#{username} -->
</select>
</mapper>
(5)测试类书写
//根据性别和名字查询用户
@Test
public void testfindUserBySexAndUsername() throws Exception {
// 加载核心配置文件
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
// 创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
// 创建SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
//sqlSession帮助生成实现类(给接口,接口遵循四大原则)
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setSex("1");
user.setUsername("张小明");
/*如果将性别或者名字的其中一个注释掉再进行查询,操作一下看看结果*/
List<User> users = userMapper.selectUserBySexAndUsername(user);
for (User u : users) {
System.out.println(u);
}
/*
User [id=16, username=张小明, sex=1, birthday=null, address=河南郑州]
注释掉user.setUsername("张小明");结果为:
User [id=10, username=张三, sex=1, birthday=Thu Jul 10 00:00:00 CST 2014, address=北京市]
User [id=16, username=张小明, sex=1, birthday=null, address=河南郑州]
User [id=22, username=陈小明, sex=1, birthday=null, address=河南郑州]
User [id=24, username=张三丰, sex=1, birthday=null, address=河南郑州]
User [id=25, username=陈小明, sex=1, birthday=null, address=河南郑州]
*/
}
(2)需求:
(1)接口UserMapper.java
//根据多个id查询用户(存在以下三种可能)
//情形一:
public List<User> selectUserByIds(Integer[] ids);
//public List<User> selectUserByIds(List<Integer> idsList);
//情形三:
//public List<User> selectUserByIds(QueryVo vo);
(2)QueryVo.java
package cn.shu.pojo;
import java.io.Serializable;
import java.util.List;
//QueryVo为包装类,将User类进行包装
public class QueryVo implements Serializable{
//不实现Serializable接口,就无法实现序列化(对象转化为二进制的过程)
//和反序列化(二进制到对象的过程),在同一台电脑中进行数据调用可以不用此过程
//但不同电脑之间进行数据传输会有序列化和反序列化的过程
/**
*
*/
private static final long serialVersionUID = 1L;
private User user;
List<Integer> idsList;
public List<Integer> getIdsList() {
return idsList;
}
public void setIdsList(List<Integer> idsList) {
this.idsList = idsList;
}
public Integer[] getIds() {
return ids;
}
public void setIds(Integer[] ids) {
this.ids = ids;
}
Integer[] ids;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
}
(3)sqlMapperConfig.xml
其他配置省略
<mappers>
<mapper class="cn.shu.mapper.UserMapper"/>
</mappers>
(4)UserMapper.xml
<!-- 根据多个id进行查询 id in(1,2,3)
1.public List<User> selectUserByIds(QueryVo vo);返回List,类型为User -->
<select id="selectUserByIds" parameterType="QueryVo" resultType="User">
<include refid="sqlselect"/>
<where>
id in
<!-- 情形三:<foreach collection="idsList" item="id" separator="," open="(" close=")"> -->
<!-- 情形一:若传入的参数为数组,此时collection="ids"是错误的 ,不识别IDS-->
<foreach collection="array" item="id" separator="," open="(" close=")">
<!-- 为了打印成(1,2,3)的形态,for循环集合为idsList,其中每个小项叫id
separator为隔点,open为前括号,close为后括号 -->
#{id}
</foreach>
</where>
</select>
(5)情形一测试类书写:
//多个Id查询
//情形一:public List<User> selectUserByIds(Integer[] ids);
@Test
public void testfindUserID() throws Exception {
// 加载核心配置文件
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
// 创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
// 创建SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
//sqlSession帮助生成实现类(给接口,接口遵循四大原则)
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//将要查询的值塞入到数组中
Integer[] ids = new Integer[3];
ids[0]=16;
ids[1]=10;
ids[0]=30;
List<User> users = userMapper.selectUserByIds(ids);
for (User user2 : users) {
System.out.println(user2);
}
}
结果:
DEBUG [main] - Opening JDBC Connection
DEBUG [main] - Created connection 1181869371.
DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@4671e53b]
DEBUG [main] - ==> Preparing: select * from user WHERE id in ( ? , ? , ? )
DEBUG [main] - ==> Parameters: 30(Integer), 10(Integer), null
DEBUG [main] - <== Total: 2
User [id=10, username=张三, sex=1, birthday=Thu Jul 10 00:00:00 CST 2014, address=北京市]
User [id=30, username=stephen hong, sex=男, birthday=Sat Aug 04 00:00:00 CST 2018, address=Hong Kong]
(6)情形三接口类UserMapper.java
//情形三:
public List<User> selectUserByIds(QueryVo vo);
(7)情形三 UserMapper.xml
<!-- 根据多个id进行查询 id in(1,2,3)
1.public List<User> selectUserByIds(QueryVo vo);返回List,类型为User -->
<select id="selectUserByIds" parameterType="QueryVo" resultType="User">
<include refid="sqlselect"/>
<where>
id in
<!-- 情形三:<foreach collection="idsList" item="id" separator="," open="(" close=")"> -->
<foreach collection="idsList" item="id" separator="," open="(" close=")">
<!-- 为了打印成(1,2,3)的形态,for循环集合为idsList,其中每个小项叫id
separator为隔点,open为前括号,close为后括号 -->
#{id}
</foreach>
</where>
</select>
(8)情形三测试类书写:
//多个Id查询
//情形三:public List<User> selectUserByIds(QueryVo vo);
@Test
public void testfindUserIDs() throws Exception {
// 加载核心配置文件
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
// 创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
// 创建SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
//sqlSession帮助生成实现类(给接口,接口遵循四大原则)
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//将要查询的值塞入到list中
List<Integer> ids = new ArrayList<>();
ids.add(16);
ids.add(22);
ids.add(24);
ids.add(26);
QueryVo vo = new QueryVo();
vo.setIdsList(ids);
List<User> user = userMapper.selectUserByIds(vo);
for (User user2 : user) {
System.out.println(user2);
}
}
结果:
DEBUG [main] - Opening JDBC Connection
DEBUG [main] - Created connection 140799417.
DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@8646db9]
DEBUG [main] - ==> Preparing: select * from user WHERE id in ( ? , ? , ? , ? )
DEBUG [main] - ==> Parameters: 16(Integer), 22(Integer), 24(Integer), 26(Integer)
DEBUG [main] - <== Total: 4
User [id=16, username=张小明, sex=1, birthday=null, address=河南郑州]
User [id=22, username=陈小明, sex=1, birthday=null, address=河南郑州]
User [id=24, username=张三丰, sex=1, birthday=null, address=河南郑州]
User [id=26, username=王五, sex=null, birthday=null, address=null]