动态sql
数据库结构
/*
SQLyog 企业版 - MySQL GUI v8.14
MySQL - 5.1.49-community : Database - db_mybatis
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`db_mybatis` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `db_mybatis`;
/*Table structure for table `t_address` */
DROP TABLE IF EXISTS `t_address`;
CREATE TABLE `t_address` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sheng` varchar(20) DEFAULT NULL,
`shi` varchar(20) DEFAULT NULL,
`qu` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
/*Data for the table `t_address` */
insert into `t_address`(`id`,`sheng`,`shi`,`qu`) values (1,'江苏省','苏州市','姑苏区'),(2,'江苏省','南京市','鼓楼区');
/*Table structure for table `t_grade` */
DROP TABLE IF EXISTS `t_grade`;
CREATE TABLE `t_grade` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`gradeName` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
/*Data for the table `t_grade` */
insert into `t_grade`(`id`,`gradeName`) values (1,'大学一年级'),(2,'大学二年级');
/*Table structure for table `t_student` */
DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`addressId` int(11) DEFAULT NULL,
`gradeId` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FK_t_student` (`gradeId`),
KEY `FK_t_student2` (`addressId`),
CONSTRAINT `FK_t_student` FOREIGN KEY (`gradeId`) REFERENCES `t_grade` (`id`),
CONSTRAINT `FK_t_student2` FOREIGN KEY (`addressId`) REFERENCES `t_address` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*Data for the table `t_student` */
insert into `t_student`(`id`,`name`,`age`,`addressId`,`gradeId`) values (1,'张三',10,1,1),(2,'李四',11,2,2),(3,'王五',12,2,2);
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
if
@Test
public void testSearchStudents() {
logger.info("添加学生(带条件)");
Map<String,Object> map=new HashMap<String,Object>();
map.put("gradeId", 2);
map.put("name1", "%李%");
map.put("age", 11);
List<Student> studentList=studentMapper.searchStudents(map);
for(Student student:studentList){
System.out.println(student);
}
}
<select id="searchStudents" parameterType="Map" resultMap="StudentResult">
select * from t_student
where gradeId=#{gradeId}
<if test="name1!=null">
and name like #{name}
</if>
<if test="age!=nulll">
and age=#{age}
</if>
</select
2018-06-29 15:53:36,701 [main] INFO [com.java1234.service.StudentTest] - 添加学生(带条件)
2018-06-29 15:53:36,860 [main] DEBUG [com.java1234.mappers.StudentMapper.searchStudents] - ==> Preparing: select * from t_student where gradeId=? and name like ? and age=?
2018-06-29 15:53:36,890 [main] DEBUG [com.java1234.mappers.StudentMapper.searchStudents] - ==> Parameters: 2(Integer), null, 11(Integer)
2018-06-29 15:53:36,904 [main] DEBUG [com.java1234.mappers.StudentMapper.searchStudents] - <== Total: 0
和map的键值对有关系
choose when
@Test
public void testSearchStudents2() {
logger.info("添加学生(带条件)");
Map<String,Object> map=new HashMap<String,Object>();
map.put("searchBy", "gradeId");//是按照gradeId 来查询的
map.put("gradeId", 2);
map.put("name", "%李%");
map.put("age", 11);
List<Student> studentList=studentMapper.searchStudents2(map);
for(Student student:studentList){
System.out.println(student);
}
}
2018-06-29 16:02:43,124 [main] INFO [com.java1234.service.StudentTest] - 添加学生(带条件)
2018-06-29 16:02:43,295 [main] DEBUG [com.java1234.mappers.StudentMapper.searchStudents2] - ==> Preparing: select * from t_student where gradeId=?
2018-06-29 16:02:43,325 [main] DEBUG [com.java1234.mappers.StudentMapper.searchStudents2] - ==> Parameters: 2(Integer)
2018-06-29 16:02:43,340 [main] DEBUG [com.java1234.mappers.StudentMapper.searchStudents2] - <== Total: 2
Student [id=2, name=李四, age=11]
Student [id=3, name=王五, age=12]
@Test
public void testSearchStudents2() {
logger.info("添加学生(带条件)");
Map<String,Object> map=new HashMap<String,Object>();
map.put("searchBy", "name");//按照name 来查询的
map.put("gradeId", 2);
map.put("name", "%李%");
map.put("age", 11);
List<Student> studentList=studentMapper.searchStudents2(map);
for(Student student:studentList){
System.out.println(student);
}
}
2018-06-29 16:08:13,003 [main] INFO [com.java1234.service.StudentTest] - 添加学生(带条件)
2018-06-29 16:08:13,167 [main] DEBUG [com.java1234.mappers.StudentMapper.searchStudents2] - ==> Preparing: select * from t_student where name like ?
2018-06-29 16:08:13,196 [main] DEBUG [com.java1234.mappers.StudentMapper.searchStudents2] - ==> Parameters: %李%(String)
2018-06-29 16:08:13,215 [main] DEBUG [com.java1234.mappers.StudentMapper.searchStudents2] - <== Total: 1
Student [id=2, name=李四, age=11]
where 标签
<select id="searchStudents3" parameterType="Map" resultMap="StudentResult">
select * from t_student
<where>
<if test="gradeId!=null">
gradeId=#{gradeId}
</if>
<if test="name!=null">
and name like #{name}
</if>
<if test="age!=nulll">
and age=#{age}
</if>
</where>
</select>
写的时候,where 会自动把where 后面第一个sql 前面的 and 去掉
trim
<select id="searchStudents4" parameterType="Map" resultMap="StudentResult">
select * from t_student
<trim prefix="where" prefixOverrides="and|or">
<if test="gradeId!=null">
gradeId=#{gradeId}
</if>
<if test="name!=null">
and name like #{name}
</if>
<if test="age!=nulll">
and age=#{age}
</if>
</trim>
</select>
<trim prefix="where" 代表前缀是where prefixOverrides="and|or"> where 后面去掉第一个and 或者是or
foreach
@Test
public void testSearchStudents5() {
logger.info("添加学生(带条件)");
Map<String,Object> map=new HashMap<String,Object>();
List<Integer> gradeIdsList=new ArrayList<Integer>();
// gradeIds.add(1);
gradeIdsList.add(2);
map.put("gradeIds", gradeIdsList);
List<Student> studentList=studentMapper.searchStudents5(map);
for(Student student:studentList){
System.out.println(student);
}
}
<select id="searchStudents5" parameterType="Map" resultMap="StudentResult">
select * from t_student
<if test="gradeIds!=null">
<where>
gradeId in
<foreach item="gradeId" collection="gradeIdsList" open="(" separator="," close=")">
#{gradeId}
</foreach>
</where>
</if>
</select>
2018-06-29 16:40:21,837 [main] INFO [com.java1234.service.StudentTest] - 添加学生(带条件)
2018-06-29 16:40:22,005 [main] DEBUG [com.java1234.mappers.StudentMapper.searchStudents5] - ==> Preparing: select * from t_student WHERE gradeId in ( ? )
2018-06-29 16:40:22,037 [main] DEBUG [com.java1234.mappers.StudentMapper.searchStudents5] - ==> Parameters: 2(Integer)
2018-06-29 16:40:22,052 [main] DEBUG [com.java1234.mappers.StudentMapper.searchStudents5] - <== Total: 2
Student [id=2, name=李四, age=11]
Student [id=3, name=王五, age=12]
set
@Test
public void testUpdateStudent(){
logger.info("更新学生(带条件)");
Student student=new Student();
student.setId(1);
student.setName("张三3");
student.setAge(13);
studentMapper.updateStudent(student);
sqlSession.commit();
}
<update id="updateStudent" parameterType="Student">
update t_student
<set>
<if test="name!=null">
name=#{name},
</if>
<if test="age!=null">
age=#{age},
</if>
</set>
where id=#{id}
</update>
2018-06-29 22:03:13,489 [main] INFO [com.java1234.service.StudentTest] - 更新学生(带条件)
2018-06-29 22:03:13,832 [main] DEBUG [com.java1234.mappers.StudentMapper.updateStudent] - ==> Preparing: update t_student SET name=?, age=? where id=?
2018-06-29 22:03:13,860 [main] DEBUG [com.java1234.mappers.StudentMapper.updateStudent] - ==> Parameters: 张三3(String), 13(Integer), 1(Integer)
2018-06-29 22:03:13,861 [main] DEBUG [com.java1234.mappers.StudentMapper.updateStudent] - <== Updates: 1