mybatis框架补充

一，#{} 和${}区别

1.传数值

#{}  占位符，是经过预编译的，编译好SQL语句再取值，#方式能够防止sql注入

eg：#{}：delete from admin where id = #{id}

        结果： dalete from admin where id = ？

${}会将值以字符串形式拼接到sql语句，${}方式无法防止SQL注入

eg：sql注入演示

        ${}：delete from admin where id = '${id or 1}'

        结果：这样使整张表的数据被删除了,所以相比于#{},#{}在对单独数据处理时更加安全

2.传列名

select * from admin order by ${column} desc

二，特殊处理定义resultMap

1.定义resultMap

<resultMap id="adminResultMap" type="Admin">
<id column="id" property="id"/>
<result property="acc" column="account" />
<result property="pwd" column="password" />
</resultMap>

（1）.resultMap的id属性时resultMap的唯一标识，本例中定义为"adminResultMap"

（2）.resultMap的type属性是映射的POJO类型，(指模型类中属性类型,首字母大写)

（3）id标签映射主键，result标签映射非主键

（4）property设置对象属性名称，column映射查询结果的列名称

2.使用resultMap

<select id="findAdminInfoResultMap" resultMap="adminResultMap">
SELECT id ,account,password FROM admin
</select>

（1）本例输出映射使用的是resultMap，而非resultType

（2）resultMap引用了adminResultMap

三，多表关联处理结果集

resultMap元素中association，collection元素

association适用于一对一关系

collection适用于一对多关系

eg1：

查找一个专业及其所包含学生人数（一对多使用Collection）

使用resultMap组装查询结果

<?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="com.ffyc.mybatisdemo.dao.MajorDao"><resultMap id="majormap" type="Major"><id column="id" property="id"/><result column="name" property="name"/><collection property="students" javaType="arraylist" ofType="Student"><id column="sid" property="id"/><result column="sname" property="name"/><result column="snum" property="num"/></collection>
</resultMap><select id="findMajor" parameterType="int" resultMap="majormap">selectm.id,m.name,s.id sid,s.name sname,s.num snumfrom major m inner join student s on m.id = s.majoridwhere m.id = #{id}</select></mapper>

package com.ffyc.mybatisdemo.dao;import com.ffyc.mybatisdemo.model.Major;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;public interface MajorDao {Major findMajor(int id);/*简单的sql，可以在抽象方法上使用注解标签写sql，复杂的还是推荐写道Mapper文件中*/@Insert("insert into major(name)values (#{name})")int insertMajor(Major major);}

package com.ffyc.mybatisdemo.test;import com.ffyc.mybatisdemo.dao.MajorDao;
import com.ffyc.mybatisdemo.dao.StudentDao;
import com.ffyc.mybatisdemo.model.Major;
import com.ffyc.mybatisdemo.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;import java.io.IOException;public class TestMajor {public static void main(String[] args) throws IOException {SqlSession sqlSession = MybatisUtil.getSqlSession();MajorDao majorDao = sqlSession.getMapper(MajorDao.class);Major major = majorDao.findMajor(1);System.out.println(major);//        Major major = new Major();
//        major.setName("数学");
//
//        System.out.println(majorDao.insertMajor(major));
//sqlSession.close();}}

eg2：通过学号查找对应学生的信息（一对一  使用associate）

使用resultMap组装查询结果

<?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="com.ffyc.mybatisdemo.dao.StudentDao"><resultMap id="studentmap" type="Student"><id property="id" column="id"/><result property="num" column="num"/><result property="gender" column="gender"/><result property="name" column="name"/><result property="phone" column="phone"/><!--关联表数据映射   会自动创建关联对象--><association property="major" javaType="Major"><id column="mid" property="id"/><result column="mname" property="name"/></association></resultMap><select id="findStudentById" parameterType="int" resultMap="studentmap">selects.id,s.num,s.gender,s.phone,m.id mid,m.name mnamefrom student s inner join major m on s.majorid=m.idwhere s.id = #{id}</select><select id="findStdents" resultMap="studentmap">selects.id,s.num,s.gender,s.phone,m.id mid,m.name mnamefrom student s inner join major m on s.majorid=m.id</select>
</mapper>

package com.ffyc.mybatisdemo.dao;import com.ffyc.mybatisdemo.model.Student;import java.util.ArrayList;public interface StudentDao {Student findStudentById(int id);ArrayList<Student> findStdents ();}

package com.ffyc.mybatisdemo.test;import com.ffyc.mybatisdemo.dao.StudentDao;
import com.ffyc.mybatisdemo.model.Student;
import com.ffyc.mybatisdemo.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;import java.io.IOException;
import java.util.ArrayList;public class TestStudent {public static void main(String[] args) throws IOException {SqlSession sqlSession = MybatisUtil.getSqlSession();StudentDao studentDao = sqlSession.getMapper(StudentDao.class);Student student = studentDao.findStudentById(3);System.out.println(student.getMajor().getName());ArrayList<Student> students = studentDao.findStdents();System.out.println(students);System.out.println(student);sqlSession.close();}}

四.使用注解的方式实现mybatis交互数据库

1.常用注解方式

（1）@Insert ： 插入sql , 和xml insert sql语法完全一样

（2）@Select ： 查询sql, 和xml select sql语法完全一样

（3）@Update ： 更新sql, 和xml update sql语法完全一样

（4）@Delete ： 删除sql, 和xml delete sql语法完全一样

（5）@Results ： 设置结果集合   

（6）@Result ： 设置结果

2.使用案例

查询所有信息：

@Select("select * from t_emp")
@Results(id = "empMap",value = {
@Result(column = "emp_id",property = "empId",id = true),
@Result(column = "emp_name",property = "empName"),
@Result(column = "emp_tel",property = "empTel"),
@Result(column = "emp_education",property = "empEducation"),
@Result(column = "emp_birthday",property = "empBirthday")
})
List<Employee> getAll();

查询单个信息：

@Select("select * from t_emp where emp_id=#{empId}")
Employee getById(@Param("empId") Integer empId);

插入信息：

@Insert("insert into t_emp (emp_id, emp_name, emp_tel, " +
" emp_education, emp_birthday, fk_dept_id" +" )" values (#{empId}, #{empName}, #
{empTel}, " +" #{empEducation}, #{empBirthday}, #{fkDeptId}" +" )")
int insert(Employee record);

删除信息：
 

@Delete("delete from t_emp where id = #{empId}")
void delete(@Param("empId") Integer empId);