映射器是javaBean和数据库表沟通的桥梁, 必须掌握它的使用
它的内容如下表
select
这是最简单的用法了, 根据id来返回一个实体。
<select id="getStudent" parameterType="integer" resultType="student">
select * from student where stuId = #{stuId}
</select>
参数解释
id 和接口中的方法定义一致
parameterType 要传给sql的参数类型, 可以是全路径或别名
resultType 结果类型,可以是全路径或别名
统计姓王的学生人数, 步骤如下
1 . 在接口中定义方法
public Student countFirstName(String name);
2 . 配置select标签
这里需要注意的是like的 %写法
<select id="countFirstName" parameterType="string" resultType="integer">
select count(*) FROM student where stuName like concat(#{firstName} ,'%')
</select>
3 . 测试
@Test
public void test() throws IOException {
SqlSession sqlSession = null;
try {
sqlSession = SqlSessionFactoryUtil.openSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
System.out.println(studentMapper.countFirstName("王"));
} catch (Exception e){
e.printStackTrace();
}finally {
if (sqlSession != null){
sqlSession.close();
}
}
}
select的自动映射
假如有一个这样的字段,在数据库中是stu_id, 用下划线分割了两个词, 但是javabean中的命名为stuId, 两种不能互相对应。
解决办法是 ,用as将放回列表重命名成javabean的属性
<select id="getStudent" parameterType="integer" resultType="student">
SELECT stu_id AS stuId , stu_name AS stuName,stu_age AS stuAge,stu_major AS stuMajor,birthday FROM student WHERE stu_Id = #{stuId}
</select>
多个值传递
上面传递一个值还是挺简单的, 当有多个值的时候 用map将列名对应的参数传入.
比如查找姓氏为王, 年龄18的学生
接口方法
public List<Student> findByNameAndAge(Map<String, String> params);
xml
<select id="findByNameAndAge" parameterType="map" resultType="student">
SELECT stu_id AS stuId , stu_name AS stuName,stu_age AS stuAge,stu_major AS stuMajor,birthday
FROM student WHERE stu_name LIKE concat(#{name},'%') AND stu_age = #{age};
</select>
测试
@Test
public void test() throws IOException {
SqlSession sqlSession = null;
try {
sqlSession = SqlSessionFactoryUtil.openSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
Map<String, String> paramsMap = new HashMap<String, String>();
paramsMap.put("name","王");
paramsMap.put("age","18");
System.out.println(studentMapper.findByNameAndAge(paramsMap));
} catch (Exception e){
e.printStackTrace();
}finally {
if (sqlSession != null){
sqlSession.close();
}
}
}
多值传递 –注解方式
在接口方法参数上加上@Param(“name”) 也可以将值传入
public List<Student> findByNameAndAge(@Param("name") String name, @Param("age") Integer age);
xml, 不需要指定parameterType
<select id="findByNameAndAge" resultType="student">
SELECT stu_id AS stuId , stu_name AS stuName,stu_age AS stuAge,stu_major AS stuMajor,birthday
FROM student WHERE stu_name LIKE concat(#{name},'%') AND stu_age = #{age};
</select>
测试代码
studentMapper.findByNameAndAge("王",18)
使用javabean 方式传入
需要针对查询的参数来重新创建一个类,类的属性就是查询的参数,如下
public class StuParam {
private String name;
private Integer age;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
}
接口创建以这个参数类为参数的方法
public List<Student> findByNameAndAge(StuParam stuParam);
xml, 指定输入类型为StuParam类
<select id="findByNameAndAge" parameterType="com.cyh.pojo.StuParam" resultType="student">
SELECT stu_id AS stuId , stu_name AS stuName,stu_age AS stuAge,stu_major AS stuMajor,birthday
FROM student WHERE stu_name LIKE concat(#{name},'%') AND stu_age = #{age};
</select>
测试:
studentMapper.findByNameAndAge(new StuParam("王",18));
3种多值传递方法感觉还是注解的方便一些。
insert
插入的规则要比查询简单很多, 只要注意普通的查询是不返回主键的,如下代码
@Test
public void test() throws IOException {
SqlSession sqlSession = null;
try {
sqlSession = SqlSessionFactoryUtil.openSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
Student stu = new Student();
stu.setStuName("刘si");
stu.setStuAge(12);
stu.setStuMajor("软件");
stu.setBirthday(new Date());
studentMapper.insertStudent(stu);
sqlSession.commit();
System.out.println(stu.getStuId());
} catch (Exception e){
e.printStackTrace();
}finally {
if (sqlSession != null){
sqlSession.close();
}
}
}
输出的id是null, 主键不回填, 后面若是需要对stu这个对象操作的话就麻烦了, 在xml中有解决的办法,如下 . 设置useGeneratedKeys为true , keyProperty为pojo的主键属性
<insert id="insertStudent" parameterType="student"
useGeneratedKeys="true" keyProperty="stuId">
insert into student(stu_name, stu_age, stu_major, birthday) values(#{stuName},#{stuAge},#{stuMajor},#{birthday})
</insert>
update
参数传递的方式和select 一样
接口如下
public Integer updateStudent(Student stu);
xml 如下
<update id="updateStudent" parameterType="student" >
UPDATE student SET stu_name=#{stuName} WHERE stu_id = #{stuId}
</update>
测试
@Test
public void test() throws IOException {
SqlSession sqlSession = null;
try {
sqlSession = SqlSessionFactoryUtil.openSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
Student stu = new Student();
stu.setStuId(6);
stu.setStuName("新名字哎");
studentMapper.updateStudent(stu);
sqlSession.commit();
} catch (Exception e){
e.printStackTrace();
}finally {
if (sqlSession != null){
sqlSession.close();
}
}
}
注意一点是, 这里只按id修改了学生的姓名,没有传入stuMajor参数, 但是数据库不会将stu_major字段设置为null, 而是保留上一次的内容
delete
<delete id="deleteStudent" parameterType="integer">
delete FROM student WHERE stu_id = #{stuid}
</delete>