字段名与属性名不一致:
方法一:只在查询中会出现此类问题,使用别名即可。
<select id="selectStudentByName" resultType="Student">
<!-- select id,name,age,score from student where tname like '%${value}%';-->
select tid id,tname name,tage age,score from student where name like '%'#{name}'%';
</select>
tid:字段名
id:属性名
方法二:增加<resultMap>字段,设置映射
<resultMap type="Student" id="studentMapper">
<id column="tname" property="name"/>
<id column="tage" property="age"/>
</resultMap>
<select id="selectStudentById" resultMap="Student">
<!-- 查询出的对象都要以Student的形式封装起来 -->>
select id,name,age,score from student where id = #{id};
</select>
<select id="selectStudentByName" resultMap="Student">
<!-- select id,name,age,score from student where name like '%${value}%';-->
select id,name,age,score from student where name like '%'#{name}'%';
</select>
Mapper动态代理:
干掉dao实现类,因为接口实现类的名字跟mapper.xml中的sql语句对应的id相同。然后mapper的namespace名改为相应接口的全名。
删除掉 dao实现类,改用 dao = sqlSession.getMapper(com.bjpowernode.dao.IStudentDao.class);
示例:
public class MyTest {
private IStudentDao dao;
private SqlSession sqlSession;
@Before
public void before(){
sqlSession = MyBatisUnils.getSqlSession();
dao = sqlSession.getMapper(com.bjpowernode.dao.IStudentDao.class);
}
@After
public void After(){
if(sqlSession!=null)
sqlSession.close();
}
@Test
public void test01(){
Student student = new Student("柳柳",25,99);
System.out.println("插入前:Student = "+student);
dao.insertStudent(student);
System.out.println("插入后:Student = "+student);
sqlSession.commit();
}
新插入的对象自动返回id问题
<insert id="insertStudent">
INSERT INTO student(name,age,score) VALUES(#{name},#{age},#{score})
<selectKey resultType="int" keyProperty="id" order="AFTER">
select @@identity
</selectKey>
</insert>
多查询条件无法整体接收问题:
表单给出的查询条件是无法将其封装成一个对象的,也就是说查询方法只能携带多个参数,而不能将参数进行封装成一个对象。有两种解决方案:
①:将这多个参数封装成一个Map
接口:
public interface IStudentDao {
List<Student> selectStudentByCondition(Map<String,Object> map);
}
test类:
public void test01(){
Student stu = new Student("田七",27,95);
Map<String,Object> map = new HashMap<String, Object>();
map.put("nameCon", "张");
map.put("ageCon", 20);
map.put("stu",stu);
List<Student> students = dao.selectStudentByCondition(map);
for (Student student : students) {
System.out.println(student);
}
}
<select id="selectStudentByCondition" resultType="Student">
select id,name,age,score
from student
where name like '%'#{nameCon}'%'
and age > #{ageCon}
and score > #{stu.score}
</select>
②:多个参数
接口:
public interface IStudentDao {
List<Student> selectStudentByCondition(String name,int age);
}
测试类:
@Test
public void test01(){
List<Student> students = dao.selectStudentByCondition("张",20);
for (Student student : students) {
System.out.println(student);
}
}
mapper.xml(索引的方式)
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.bjpowernode.dao.IStudentDao">
<select id="selectStudentByName" resultType="Student">
<!-- select id,name,age,score from student where name like '%${value}%';-->
select id,name,age,score
from student
where name like '%'#{0}'%'
and age > #{1}
</select>
</mapper>
#{}可以放什么:
1)参数对象的属性
2)随意内容,此时#{}是个占位符
3)参数为map时的key
4)参数为map时,当key对应的value为对象时,key的属性
5)参数的索引号
动态SQL:
几个标签的使用。
(1) if
属性:test = 判断条件
select id,name,age,score
from student
where 1 = 1
<if test="name!=null and name!=''">
and name like '%'#{name}'%'
</if>
<if test="age>0">
and age > #{age};
</if>
(2) where
无属性
select id,name,age,score
from student
<where>
<if test="name!=null and name!=''">
and name like '%'#{name}'%'
</if>
<if test="age>0">
and age > #{age};
</if>
</where>
where 标签替代了where关键字的使用,可以自行根据需要省略and,所以可以在确保第一个条件不成立的情况下,保证第二个条件能正确判断。
(3)choose标签
select id,name,age,score
from student
<where>
<choose>
<when test="name!=null and name!=''">
and name like '%'#{name}'%'
</when>
<when test="age>0">
and age>#{age}
</when>
<otherwise>
1 = 2
</otherwise>
</choose>
</where>
choose-when-otherwise 相当于 switch-case-default,当前边条件成立时,后边程序不执行。
(4)<foreach> 的使用
<select id="selectStudentByForeach" resultType="Student">
<!-- select id,name,age,score from student where name like '%${value}%';-->
select id,name,age,score
from student
<if test="array.length>0">
where id in
<foreach collection="array" item="myId" open="(" close=")" separator=",">
#{myId}
</foreach>
</if>
</select>
@Test
public void test02(){
int[] ids = {1,3};
List<Student> students = dao.selectStudentByForeach(ids);
for (Student student : students) {
System.out.println(student);
}
}
@Test
public void test03(){
List<Integer> ids = new ArrayList<>();
ids.add(1);
ids.add(3);
List<Student> students = dao.selectStudentByForeach2(ids);
for (Student student : students) {
System.out.println(student);
}
}
foreach:针对要查询的条件在一个数组或者List中。
属性:collection-集合类型(array 、list)
open-开始的标记 (
close-结束的标记 ) sql拼接
separator-条件集合的分割标记
item-每一个条件值
foreach 用于遍历条件为对象的集合
<select id="selectStudentByForeach3" resultType="Student">
<!-- select id,name,age,score from student where name like '%${value}%';-->
select id,name,age,score
from student
<if test="list.size>0">
where id in
<foreach collection="list" item="myStu" open="(" close=")" separator=",">
#{myStu.id}
</foreach>
</if>
</select>
相当于:
select id,name,age,score
from student
where id in (1,3)
@Test
public void test04(){
Student stu1 = new Student();
stu1.setId(1);
Student stu2 = new Student();
stu1.setId(2);
List<Student> stus = new ArrayList<>();
stus.add(stu1);
stus.add(stu2);
List<Student> students = dao.selectStudentByForeach3(stus);
for (Student student : students) {
System.out.println(student);
}
}
唯一与之前不同的是:遍历时,条件集合为对象的属性的集合。
<select id="selectStudentBySqlFragment" resultType="Student">
<!-- select id,name,age,score from student where name like '%${value}%';-->
select <include refid="selectColumns"></include>
from student
<if test="list.size>0">
where id in
<foreach collection="list" item="myStu" open="(" close=")" separator=",">
#{myStu.id}
</foreach>
</if>
</select>
<sql id="selectColumns">
id,name,age,score
</sql>
(5)<sql>的使用,sql段的使用,其作用完全是代替作用。