我们之前拿最简单的查询做了例子演示框架搭建，现在，让我们看一下复杂的查询以及增删改如何去做，一般情况下，我们都是一个SQL映射文件对应一张表和一个实体类，有关该实体类的增删改查都在同一个映射文件中
一、回顾一下最简单的查询
select元素相关属性
id,必须存在的属性，值从概念上讲是唯一标示，不能随便写，应对应接口中方法的名
parameterType，若该方法有参，则必须有该属性，值和接口中参数的类型相对应
resultType，结果集的类型，经过查询，数据库中的信息自动封装，指定类型，就告诉框架你把信息封装成一个什么类型的对象
二、多个参数
需求：根据用户名和编号进行查询
接口中增加方法
User selectUser(String username,int id);
发现parameterType无法指定类型，
①加注解
User selectUser(@Param("username")String username,@Param("id")int id);
<select id="selectUser" resultType="User">
select * from t_user where username = #{username} and id = #{id}
</select>
②参数整合在一起，使用bean对象,
List<User> selectUser(User user);
<select id="selectUser" parameterType="User" resultType="User">
select * from t_user where username = #{username} and id = #{id}
</select>
注意，查询结果有多条，使用list集合作为返回值类型，resultType指定集合中元素的类型
三、删除
需求：根据id进行删除
void deleteUser(int id);
<delete id="deleteUser" parameterType="int">
delete from t_user where id = #{value}
</delete>
去测试类中执行发现并没有生效，没有自动提交，因此需要在try中调用session的commit方法，在catch中调用session的rollback方法
四、修改
void updateUser(User user);
<update id="updateUser" parameterType="User">
update t_user set username = #{username},password = #{password}
where id = #{id}
</update>
五、插入
<!-- keyProperty属性主键列对应的属性的名称
order属性BEFORE表示子查询在主查询前
-->
<insert id="insertUser">
insert into t_user values(#{id},#{username},sysdate,#{password})
<selectKey keyProperty="id" resultType="int" order="BEFORE">
select max(id)+1 from t_user
</selectKey>
</insert>
void insertUser(User user);
User user = new User();
user.setUsername("k");
user.setPassword("k");
userMapper.insertUser(user);
session.commit();
六、特殊结果、参数
1、特殊参数类型使用
根据用户名和密码查询，比如此时用户名和密码不同时写在同一个实体类中，此时要使用一个参数就要用到Map
User selectUserByNAP(Map<String,String> map);
<select id="selectUserByNAP" parameterType="map" resultType="User">
select * from t_user where username = #{m_username} and password = #{m_password}
</select>
Map<String,String> map = new HashMap<String,String>();
map.put("m_username", "l");
map.put("m_password", "l");
User user = userMapper.selectUserByNAP(map);
System.out.println(user.getPassword());
sql语句中参数等于Map的键
2、特殊结果集使用
如果实体类属性与数据库列名不一样，就使用resultMap作为结果集类型
User selectUserByNAP1(Map<String,String> map);
<select id="selectUserByNAP1" parameterType="map" resultMap="userres">
select * from t_user where username = #{m_username} and password = #{m_password}
</select>
<resultMap type="User" id="userres">
<result column="password" property="password"></result>
</resultMap>
Map<String,String> map = new HashMap<String,String>();
map.put("m_username", "l");
map.put("m_password", "l");
User user = userMapper.selectUserByNAP1(map);
System.out.println(user.getPassword());
type:查询结果最终得到的类型
3、建employee和department两张表，其中employee表有eid、eno、ename、deptid等字段，department表有did、dno、dname等字段
-- Create table
create table T_DEPARTMENT
(
did NUMBER(4) not null,
dno VARCHAR2(20),
dname VARCHAR2(20)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table T_DEPARTMENT
add constraint PK_DID_DEPARTMENT primary key (DID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create table
create table T_EMPLOYEE
(
eid NUMBER(4) not null,
eno VARCHAR2(20),
ename VARCHAR2(20),
deptid NUMBER(4)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table T_EMPLOYEE
add constraint PK_EID_EMPLOYEE primary key (EID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
alter table T_EMPLOYEE
add constraint FK_DEPTID_EMPLOYEE foreign key (DEPTID)
references T_DEPARTMENT (DID);
public class Employee {
private int eid;
private String eno;
private String ename;
private Department dept;
}
public class Department {
private int did;
private String dno;
private String dname;
private List<Employee> employees;
}
七、多表查询
1、一对一关联
如果要做查询员工信息同时将部门信息同时查出来
（1）嵌套子查询
public Employee getEmployeeById(int eid);
<select id="getEmployeeById" parameterType="int" resultMap="empres">
select * from t_employee where eid = #{eid}
</select>
<!-- 嵌套子查询
association元素
property属性嵌套查询的结果赋值给Employee中属性的名字
column属性外键字段的名称
javaType属性嵌套子查询返回结果集的类型
select属性嵌套的子查询对相应查询
-->
<resultMap type="Employee" id="empres">
<association property="dept" column="deptid" javaType="Department" select="getDeptById"></association>
</resultMap>
<select id="getDeptById" parameterType="int" resultType="Department">
select * from t_department where did = #{deptid}
</select>
association元素
property属性嵌套查询的结果赋值给Employee中属性的名字
column属性外键字段的名称
javaType属性嵌套子查询返回结果集的类型
select属性嵌套的子查询对相应查询
以上查询的做法实际上非常不好，仅做了一个多表查询就需要多次操作数据库
（2）嵌套结果
public Employee getEmployeeByNo(String eno);
<select id="getEmployeeByNo" parameterType="string" resultMap="empRes">
select *
from t_employee e,t_department d
where e.deptid = d.did
and e.eno = #{eno}
</select>
<resultMap type="employee" id="empRes">
<result column="eid" property="eid"/>
<result column="eno" property="eno"/>
<result column="ename" property="ename"/>
<association property="dept" column="deptid" javaType="Department" resultMap="deptRes"></association>
</resultMap>
<resultMap type="department" id="deptRes">
<result property="did" column="did"></result>
<result property="dno" column="dno"></result>
<result property="dname" column="dname"></result>
</resultMap>
EmployeeDao employeeDao = session.getMapper(EmployeeDao.class);
Employee employee = employeeDao.getEmployeeByNo("1");
System.out.println(employee.getDept().getDid());
2、一对多关联
查询部门信息，并将该部门所有员工查询出来
Department getDepartmentById(int did);
<select id="getDepartmentById" parameterType="int" resultMap="deptRes">
select *
from t_employee e,t_department d
where e.deptid = d.did
and d.did = #{did}
</select>
<resultMap type="department" id="deptRes">
<result property="did" column="did"></result>
<result property="dno" column="dno"></result>
<result property="dname" column="dname"></result>
<!-- 一对多的关联查询
collection元素
property属性针对多个员工信息封装出的集合容器类型的对象，赋值给department类中的对应的属性
ofType属性集合中每个元素的类型 -->
<collection property="employees" ofType="employee">
<result property="eid" column="eid"></result>
<result property="eno" column="eno"></result>
<result property="ename" column="ename"></result>
</collection>
</resultMap>
DepartmentDao departmentDao = session.getMapper(DepartmentDao.class);
Department department = departmentDao.getDepartmentById(1);
List<Employee> list = department.getEmployees();
System.out.println(list.get(0).getEid());
3、向员工表中插入一条数据，同时指定其部门信息
<insert id="insert" parameterType="employee">
insert into t_employee values (#{eid},#{eno},#{ename},#{dept.did})
<selectKey keyProperty="eid" resultType="int" order="BEFORE">
select max(eid)+1 from t_employee
</selectKey>
</insert>
void insert(Employee e);
Employee employee = new Employee();
employee.setEno("2");
employee.setEname("Lily");
Department department = new Department();
department.setDid(1);
employee.setDept(department);
EmployeeDao employeeDao = session.getMapper(EmployeeDao.class);
employeeDao.insert(employee);
session.commit();
4、分页查询
利用pageHelper插件
首先引入jar包
jsqlparser-0.9.jar
pagehelper-3.6.4.jar
在核心配置文件中加入
<!-- plugins元素加在typeAliases下 -->
<plugins>
<plugin interceptor="com.github.pagehelper.PageHelper">
<!-- property元素指明和哪个数据库匹配使用 -->
<property name="dialect" value="oracle"></property>
</plugin>
</plugins>
在查询映射文件中加入
<select id="getAll" resultType="user">
select * from t_user
</select>
List<User> getAll();
//获取第一页数据，每页显示1条
Page page = PageHelper.startPage(1, 1);
userMapper.getAll();
System.out.println("共"+page.getTotal()+"条记录");
List<User> list = page.getResult();
for(User u:list){
System.out.println(u.getPassword());
}
5、组合查询
<!-- 条件查询where元素，补齐where关键字 -->
<select id="getByCondition" parameterType="user" resultType="user">
select * from t_user
<!-- where 1=1 -->
<where>
<if test="id != null and id != 0">
and id = #{id}
</if>
<if test="username != null and username != ''">
and username = #{username}
</if>
</where>
</select>
6、批量删除
<!-- 参数是数组，无需加参数
foreach元素遍历集合容器对象
collection属性指定的是方法传入的类型
item属性每次遍历到的元素
open属性遍历得到的结果的开始字符
separator属性元素之间的分隔符
-->
<delete id="deleteUsers">
delete from t_user where id in
<foreach collection="array" item="item" open="(" close=")" separator=",">
#{item}
</foreach>
</delete>
void deleteUsers(int[] ids);
int[] i = new int[2];
i[0] = 3;
i[1] = 4;
userMapper.deleteUsers(i);
session.commit();
7、缓存
User user = userMapper.getUser(1);
user.setPassword("k");
//修改查询到的user信息
System.out.println(user.getPassword());
user.setPassword("j");
//再次查询理论上应该得到数据库中的内容，但是却是上述修改后的内容，这是因为Mybatis缓存
//缓存是因为提高性能
user = userMapper.getUser(1);
System.out.println(user.getPassword());
此时需要在sql映射文件中加入flushCache="true"
<select id="getUser" parameterType="java.lang.Integer" resultType="User" flushCache="true">
select * from t_user where id = #{value}
</select>
当查询数据库返回结果时，mybatis会生成key、value形式的对象存在于缓存，在接下来在内存中针对查询结果做变更，相当于改变了缓存中的数据，再次查询不访问数据库，而是直接采用缓存中的数据
八、模糊查询
接口中写入
List<User> getUserByName(String name);
注意！！！mybatis接口中方法不要重载
mapper文件中
写法1
<select id="getUserByName" parameterType="String" resultType="User">
select * from t_user where username like '%${value}%'
</select>
写法2
<select id="getUserByName" parameterType="String" resultType="User">
select * from t_user where username like concat('%',#{value},'%')
</select>
写法3
<select id="getUserByName" parameterType="String" resultType="User">
select * from t_user where username like "%"#{value}"%"
</select>
网友评论