第二章 单表的CURD操作

/04-curd
学生类属性不变
自定义Dao接口实现类
用map的方式了解即可

public interface IStudentDao {//插入void insertStudent(Student student);void insertStudentCatchId(Student student);//删改void deletetudentById(int id);void updateStudent(Student student);//查询所有List<Student> selectAllStudents();Map<String, Object> selectAllStudentsMap();//查询指定Student selectStudentById(int id);//根据姓名查询List<Student> selectStudentsByName(String name);}

修改Dao实现类
增删改查 模糊查询

public class StudentDaoImpl implements IStudentDao {private SqlSession sqlSession;//增@Overridepublic void insertStudent(Student student) {try {sqlSession = MyBatisUtils.getSqlSession();sqlSession.insert(\"insertStudent\", student);sqlSession.commit();} finally {if(sqlSession != null) {sqlSession.close();//sqlSession 关闭了就不用回滚}}}//插入后用新id初始化被插入对象@Overridepublic void insertStudentCatchId(Student student) {try {sqlSession = MyBatisUtils.getSqlSession();sqlSession.insert(\"insertStudentCatchId\", student);sqlSession.commit();} finally {if(sqlSession != null) {sqlSession.close();//sqlSession 关闭了就不用回滚}}}//删@Overridepublic void deletetudentById(int id) {try {sqlSession = MyBatisUtils.getSqlSession();sqlSession.delete(\"deletetudentById\", id);sqlSession.commit();} finally {if(sqlSession != null) {sqlSession.close();//sqlSession 关闭了就不用回滚}}}//改@Overridepublic void updateStudent(Student student) {try {sqlSession = MyBatisUtils.getSqlSession();sqlSession.update(\"updateStudent\", student);sqlSession.commit();} finally {if(sqlSession != null) {sqlSession.close();//sqlSession 关闭了就不用回滚}}}//查询所有@Overridepublic List<Student> selectAllStudents() {List<Student> students = null;try {sqlSession = MyBatisUtils.getSqlSession();students = sqlSession.selectList(\"selectAllStudents\");//查询不用修改，所以不用提交} finally {if(sqlSession != null) {sqlSession.close();//sqlSession 关闭了就不用回滚}}return students;}//了解即可 查询指定可以返回的map对象@Overridepublic Map<String, Object> selectAllStudentsMap() {Map<String, Object> map = new HashMap<>();try {sqlSession = MyBatisUtils.getSqlSession();map = sqlSession.selectMap(\"selectAllStudents\", \"name\");//查询不用修改，所以不用提交} finally {if(sqlSession != null) {sqlSession.close();//sqlSession 关闭了就不用回滚}}return map;}//通过id查询@Overridepublic Student selectStudentById(int id) {Student student = null;try {sqlSession = MyBatisUtils.getSqlSession();student = sqlSession.selectOne(\"selectStudentById\", id);} finally {if(sqlSession != null) {sqlSession.close();//sqlSession 关闭了就不用回滚}}return student;}//通过名字查询 模糊查询@Overridepublic List<Student> selectStudentsByName(String name) {List<Student> students = null;try {sqlSession = MyBatisUtils.getSqlSession();students = sqlSession.selectList(\"selectStudentByName\", name);//查询不用修改，所以不用提交} finally {if(sqlSession != null) {sqlSession.close();//sqlSession 关闭了就不用回滚}}return students;}}

工具类不变，获取主配置文件、sqlSessionFactory、sqlSession的创建

public class MyBatisUtils {private static SqlSessionFactory sqlSessionFactory;public static SqlSession getSqlSession() {try {InputStream is = Resources.getResourceAsStream(\"mybatis.xml\");if (sqlSessionFactory == null) {sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);}return sqlSessionFactory.openSession();} catch (IOException e) {e.printStackTrace();}return null;}}

映射文件中写SQL语句

<mapper namespace=\"test\"><!-- parameterType属性可以省略 --><insert id=\"insertStudent\" parameterType=\"Student\">insert into student(name,age,score) values(#{name}, #{age}, #{score})<!-- 也放在dao包下，表示映射 --></insert><insert id=\"insertStudentCatchId\">insert into student(name,age,score) values(#{name}, #{age}, #{score})<!-- order可以不写  将查询到的id属性赋值给student--><selectKey resultType=\"int\" keyProperty=\"id\" order=\"AFTER\">select @@identity</selectKey></insert><delete id=\"deletetudentById\">delete from student where id=#{xxx}	<!-- 这里的#{}仅仅是个占位符，里面放什么都可以 --></delete><update id=\"updateStudent\">update student set name=#{name}, age=#{age}, score=#{score} where id=#{id}</update><select id=\"selectAllStudents\" resultType=\"Student\">select id,name,age,score from student</select><select id=\"selectStudentById\" resultType=\"Student\">select id,name,age,score from student where id=#{jjj}</select><select id=\"selectStudentByName\" resultType=\"Student\"><!-- select id,name,age,score from student where name like concat(\'%\', #{xxx}, \'%\') -->select id,name,age,score from student where name like \'%\' #{xxx} \'%\'</select></mapper>

主配置文件不变

<configuration><!--注册DB连接四要素属性文件  --><properties resource=\"jdbc.properties\"/><!-- 定义类型别名 --><typeAliases><!-- <typeAlias type=\"com.bjpowernode.beans.Student\" alias=\"Student\"/> --><!-- 将指定包中所有类的简单类名当作其别名 --><package name=\"com.bjpowernode.beans\"/></typeAliases><!-- 配置运行环境 --><environments default=\"mysqlEm\"><environment id=\"mysqlEm\"><transactionManager type=\"JDBC\"/><dataSource type=\"POOLED\"><property name=\"driver\" value=\"${jdbc.driver}\"/><property name=\"url\" value=\"${jdbc.url}\"/><property name=\"username\" value=\"${jdbc.user}\"/><property name=\"password\" value=\"${jdbc.password}\"/></dataSource></environment></environments><!-- 注册映射文件 --><mappers><mapper resource=\"com/bjpowernode/dao/mapper.xml\"/></mappers></configuration>

方法测试

public class MyTest {private IStudentDao dao;@Beforepublic void before() {dao = new StudentDaoImpl();}@Testpublic void test01() {//id都是空的Student student = new Student(\"张三\", 23, 93.5);System.out.println(\"插入前：student = \" + student);dao.insertStudent(student);System.out.println(\"插入后：student = \" + student);}@Testpublic void test02() {//id都是空的Student student = new Student(\"张三\", 23, 93.5);System.out.println(\"插入前：student = \" + student);dao.insertStudentCatchId(student);System.out.println(\"插入后：student = \" + student);}@Testpublic void test03() {dao.deletetudentById(17);}@Testpublic void test04() {Student student = new Student(\"李四\", 23, 93.5);student.setId(18);dao.updateStudent(student);}@Testpublic void test05() {List<Student> students = dao.selectAllStudents();for(Student student : students) {System.out.println(student);}}@Testpublic void test06() {Map<String, Object> map = dao.selectAllStudentsMap();System.out.println(map.get(\"李四\"));}@Testpublic void test07() {Student student = dao.selectStudentById(19);System.out.println(student);}@Testpublic void test08() {List<Student> students = dao.selectStudentsByName(\"张\");for(Student student : students) {System.out.println(student);}}}

2.2 属性名与查询字段名不相同

resultType可以将查询结果直接映射为实体Bean对象的条件是，SQL查询的字段名与实体Bean的属性名一致。因为在将查询结果转换为指定类型对象时，系统自动将查询结果字段名称作为对象的属性名，通过反射机制完成对象的创建。
当SQL查询的字段名与实体Bean的属性名不一致时，将无法创建出需要类型的对象。此时有两种解决方案。
首先修改student表

1）查询字段使用别名
/05-property-column-unsame
修改mapper映射文件

<mapper namespace=\"test\"><!-- parameterType属性可以省略 --><insert id=\"insertStudent\" parameterType=\"Student\">insert into student(tname,tage,score) values(#{name}, #{age}, #{score})<!-- 也放在dao包下，表示映射 --></insert><select id=\"selectAllStudents\" resultType=\"Student\">select tid id,tname name,tage age,score from student</select><select id=\"selectStudentById\" resultType=\"Student\">select tid id,tname name,tage age,score from student where tid=#{jjj}</select></mapper>

2）使用结果映射resultMap
修改mapper即可

<mapper namespace=\"test\"><!-- parameterType属性可以省略 --><insert id=\"insertStudent\" parameterType=\"Student\">insert into student(tname,tage,score) values(#{name}, #{age}, #{score})<!-- 也放在dao包下，表示映射 --></insert><resultMap type=\"Student\" id=\"studentMapper\"><id column=\"tid\" property=\"id\"/><result column=\"tname\" property=\"name\"/><result column=\"tage\" property=\"age\"/></resultMap><select id=\"selectAllStudents\" resultMap=\"studentMapper\">select tid,tname,tage,score from student</select><select id=\"selectStudentById\" resultMap=\"studentMapper\">select tid,tname,tage,score from student where tid=#{jjj}</select></mapper>

2.3 Mapper动态代理

‘在前面自定义Dao接口实现类时发现一个问题：Dao的实现类其实没有实质性的工作，它仅仅是通过SqlSession的相关API定位到映射文件mapper中相应id的SQL语句，真正对DB进行操作的工作其实是由框架通过mapper中的SQL完成的。
所以MyBatis框架就抛开了Dao的实现类，直接定位到映射文件mapper中的相应SQL语句，对DB进行操作。这种实现方式称为Mapper的动态代理。
Mapper的动态代理。无需程序员实现Dao接口。接口是由MyBatis结合映射文件自动生成的动态代理实现的
/07-mapperDynamicProxy
1）修改映射文件的namespace属性值

其他不变

<mapper namespace=\"com.bjpowernode.dao.IStudentDao\">

2）修改日志输出控制文件
mapper的namespace修改了，需要将日志输出控制文件中logger的输出对象进行修改。

log4j.logger.com.bjpowernode.dao.IStudentDao=trace,console

3）Dao接口方法名
Mybatis框架要求，接口中的方法名，与映射文件mapper中相应的SQL标签的id值相同。


4）Dao对象的获取
使用时，需要调用SqlSession的getMapper()方法，即可获取指定接口的实现类对象。
该方法的参数为指定Dao接口类的class值。

测试类修改

public class MyTest {private IStudentDao dao;private SqlSession sqlSession;@Beforepublic void before() {sqlSession = MyBatisUtils.getSqlSession();dao = sqlSession.getMapper(IStudentDao.class);}@Afterpublic void after() {if (sqlSession != null) {sqlSession.close();}}@Testpublic void test01() {//id都是空的Student student = new Student(\"张三\", 23, 93.5);System.out.println(\"插入前：student = \" + student);dao.insertStudent(student);System.out.println(\"插入后：student = \" + student);sqlSession.commit();}@Testpublic void test02() {Student student = new Student(\"张三\", 23, 93.5);System.out.println(\"插入前：student = \" + student);dao.insertStudentCatchId(student);System.out.println(\"插入后：student = \" + student);sqlSession.commit();}@Testpublic void test03() {dao.deletetudentById(17);sqlSession.commit();}@Testpublic void test04() {Student student = new Student(\"李四\", 23, 93.5);student.setId(18);dao.updateStudent(student);sqlSession.commit();}@Testpublic void test05() {List<Student> students = dao.selectAllStudents();for(Student student : students) {System.out.println(student);}sqlSession.commit();}/*不能用了* @Test public void test06() {** Map<String, Object> map = dao.selectAllStudentsMap();* System.out.println(map.get(\"李四\")); sqlSession.commit();** }*/@Testpublic void test07() {Student student = dao.selectStudentById(19);System.out.println(student);sqlSession.commit();}@Testpublic void test08() {List<Student> students = dao.selectStudentsByName(\"张\");for(Student student : students) {System.out.println(student);}sqlSession.commit();}}

2.4 多查询条件无法整体接收问题的解决

/08-selectByMap
1）将这多个参数封装为一个Map
A、修改Dao接口
添加如下方法

List<Student> selectAllStudentsByCondition(Map<String, Object> map);

B、修改测试类

@Testpublic void test01() {Student stu = new Student(\"田七\", 27, 95);Map<String, Object> map = new HashMap<String, Object>();map.put(\"nameCon\", \"张\");map.put(\"ageCon\", 23);map.put(\"stu\", stu);List<Student> students = dao.selectAllStudentsByCondition(map);for(Student student : students) {System.out.println(student);}sqlSession.commit();}

C、修改映射文件

<select id=\"selectAllStudentsByCondition\" resultType=\"Student\">select id,name,age,scorefrom studentwhere name like \'%\' #{nameCon} \'%\'and age > #{ageCon}and score > #{stu.score}</select>

2）多个参数逐个接收
/09-selectByCondition
A、修改Dao接口

List<Student> selectAllStudentsByCondition(String name, int age);

B、修改测试类

List<Student> students = dao.selectAllStudentsByCondition(\"张\", 23);for(Student student : students) {System.out.println(student);}sqlSession.commit();

C、修改映射文件
这里使用索引 分别指向第一个内容和第二个内容

<select id=\"selectAllStudentsByCondition\" resultType=\"Student\">select id,name,age,scorefrom studentwhere name like \'%\' #{0} \'%\'and age > #{1}</select>

#{}中可以放什么内容？
1）参数对象的属性
2）随意内容，此时的#{}是个占位符
3）参数为map时的key
4）参数为map时，若key所对应的value为对象，则可将该对象的属性放入
5）参数的索引号

2.5 动态SQL

动态SQL，主要用于解决查询条件不确定的清空：提交的查询条件不同，执行的SQL语句不同。若将每种可能的情况逐一列出，对所有条件进行排列组合，会出现大量的SQL语句。
/10-dynamicSql
1）if标签
本例实现功能：用户提交的查询条件可以包含一个姓名的模糊查询，同时还可以包含一个年龄的下限。当然，用户在提交表单时可能两个条件均做出设定，或都不做，或只做一个。
引发的问题是，查询条件不确定，查询条件依赖于用户提交的内容。此时，就可使用动态SQL语句，根据用户提交内容对执行的SQL进行拼接。
接口类

public interface IStudentDao {List<Student> selectStudentsByIf(Student student);}

映射文件
通过在where后添加一个“1=1”条件，解决两个条件均未做设定的情况。
有名字，按名字查。没名字有年龄，按年龄查。都没有，显示所有。

<select id=\"selectStudentsByIf\" resultType=\"Student\">select id,name,age,scorefrom studentwhere 1=1<if test=\"name != null and name !=\'\'\">and	name like \'%\' #{name} \'%\'</if><if test=\"age > 0\">and age > #{age}</if></select>

测试类

@Testpublic void test01() {//Student stu = new Student(\"张\", 23, 0);//Student stu = new Student(\"\", 23, 0);Student stu = new Student(\"\", 0, 0);List<Student> students = dao.selectStudentsByIf(stu);for(Student student : students) {System.out.println(student);}sqlSession.commit();}

2）where标签
上述添加1=1，当数据量大时，严重影响查询效率。
Dao接口

List<Student> selectStudentsByWhere(Student student);

映射文件
在有查询条件时，自动添加where子句；没有查询条件时，不会添加。而且系统会自动将多出的and去掉。

<select id=\"selectStudentsByWhere\" resultType=\"Student\">select id,name,age,scorefrom student<where><if test=\"name != null and name != \'\'\">and	name like \'%\' #{name} \'%\'</if><if test=\"age > 0\">and age > #{age}</if></where></select>

测试类

public void test02() {Student stu = new Student(\"张\", 23, 0);//Student stu = new Student(\"\", 23, 0);//Student stu = new Student(\"\", 0, 0);List<Student> students = dao.selectStudentsByWhere(stu);for(Student student : students) {System.out.println(student);}sqlSession.commit();}

3）choose标签
只可以包含when和otherwise，联合使用完成java中的开关语句switch…case功能
本例完成，若姓名不空，按姓名查询；若姓名为空，按年龄查；若没有查询条件，则没有结果。
Dao接口

List<Student> selectStudentsByChoose(Student student);

映射文件

<select id=\"selectStudentsByChoose\" resultType=\"Student\">select id,name,age,scorefrom 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></select>

测试类

//choose用法  when中有一个成立了，后面就不看了@Testpublic void test03() {//Student stu = new Student(\"张\", 23, 0);//Student stu = new Student(\"\", 23, 0);Student stu = new Student(\"\", 0, 0);List<Student> students = dao.selectStudentsByChoose(stu);for(Student student : students) {System.out.println(student);}sqlSession.commit();}

4）foreach标签 遍历数组
Dao接口

List<Student> selectStudentsByForeach(int[] ids);

映射文件

<select id=\"selectStudentsByForeach\" resultType=\"Student\"><!-- 		select id,name,age,score from student where id in (1,3,20)-->select id,name,age,scorefrom student<if test=\"array.length > 0\">where id in<foreach collection=\"array\" item=\"myid\" open=\"(\" close=\")\" separator=\",\">#{myid}</foreach></if></select>

测试

@Testpublic void test04() {int[] ids = {1, 3};List<Student> students = dao.selectStudentsByForeach(ids);for(Student student : students) {System.out.println(student);}sqlSession.commit();}

5）foreach 遍历list基本类型
Dao接口

List<Student> selectStudentsByForeach2(List<Integer> ids);

映射文件

<select id=\"selectStudentsByForeach2\" resultType=\"Student\">select id,name,age,scorefrom student<if test=\"list.size > 0\">where id in<foreach collection=\"list\" item=\"myid\" open=\"(\" close=\")\" separator=\",\">#{myid}</foreach></if></select>

测试

@Testpublic void test05() {List<Integer> ids = new ArrayList<Integer>();ids.add(1);ids.add(3);List<Student> students = dao.selectStudentsByForeach2(ids);for(Student student : students) {System.out.println(student);}sqlSession.commit();}

6）foreach 遍历list自定义数据类型
Dao接口

List<Student> selectStudentsByForeach3(List<Student> students);

映射文件

<select id=\"selectStudentsByForeach3\" resultType=\"Student\">select id,name,age,scorefrom student<if test=\"list.size > 0\">where id in<foreach collection=\"list\" item=\"stu\" open=\"(\" close=\")\" separator=\",\">#{stu.id}</foreach></if></select>

测试

@Testpublic void test06() {Student stu1 = new Student();stu1.setId(1);Student stu2 = new Student();stu2.setId(3);List<Student> stus = new ArrayList<Student>();stus.add(stu1);stus.add(stu2);List<Student> students = dao.selectStudentsByForeach3(stus);for(Student student : students) {System.out.println(student);}sqlSession.commit();}

7）sql标签
可读性差，但对于大量重复语句易修改。
Dao接口

List<Student> selectStudentsBySqlFragment(List<Student> students);

映射

<select id=\"selectStudentsBySqlFragment\" resultType=\"Student\">select <include refid=\"selectColumns\"></include>from student<if test=\"list.size > 0\">where id in<foreach collection=\"list\" item=\"stu\" open=\"(\" close=\")\" separator=\",\">#{stu.id}</foreach></if></select><sql id=\"selectColumns\">id,name,age,score</sql>

测试

@Testpublic void test07() {Student stu1 = new Student();stu1.setId(1);Student stu2 = new Student();stu2.setId(3);List<Student> stus = new ArrayList<Student>();stus.add(stu1);stus.add(stu2);List<Student> students = dao.selectStudentsBySqlFragment(stus);for(Student student : students) {System.out.println(student);}sqlSession.commit();}