第二章 单表的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;
//增
@Override
public void insertStudent(Student student) {
try {
sqlSession = MyBatisUtils.getSqlSession();
sqlSession.insert("insertStudent", student);
sqlSession.commit();
} finally {
if(sqlSession != null) {
sqlSession.close();//sqlSession 关闭了就不用回滚
}
}
}
//插入后用新id初始化被插入对象
@Override
public void insertStudentCatchId(Student student) {
try {
sqlSession = MyBatisUtils.getSqlSession();
sqlSession.insert("insertStudentCatchId", student);
sqlSession.commit();
} finally {
if(sqlSession != null) {
sqlSession.close();//sqlSession 关闭了就不用回滚
}
}
}
//删
@Override
public void deletetudentById(int id) {
try {
sqlSession = MyBatisUtils.getSqlSession();
sqlSession.delete("deletetudentById", id);
sqlSession.commit();
} finally {
if(sqlSession != null) {
sqlSession.close();//sqlSession 关闭了就不用回滚
}
}
}
//改
@Override
public void updateStudent(Student student) {
try {
sqlSession = MyBatisUtils.getSqlSession();
sqlSession.update("updateStudent", student);
sqlSession.commit();
} finally {
if(sqlSession != null) {
sqlSession.close();//sqlSession 关闭了就不用回滚
}
}
}
//查询所有
@Override
public 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对象
@Override
public 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查询
@Override
public 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;
}
//通过名字查询 模糊查询
@Override
public 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;
@Before
public void before() {
dao = new StudentDaoImpl();
}
@Test
public void test01() {
//id都是空的
Student student = new Student("张三", 23, 93.5);
System.out.println("插入前:student = " + student);
dao.insertStudent(student);
System.out.println("插入后:student = " + student);
}
@Test
public void test02() {
//id都是空的
Student student = new Student("张三", 23, 93.5);
System.out.println("插入前:student = " + student);
dao.insertStudentCatchId(student);
System.out.println("插入后:student = " + student);
}
@Test
public void test03() {
dao.deletetudentById(17);
}
@Test
public void test04() {
Student student = new Student("李四", 23, 93.5);
student.setId(18);
dao.updateStudent(student);
}
@Test
public void test05() {
List<Student> students = dao.selectAllStudents();
for(Student student : students) {
System.out.println(student);
}
}
@Test
public void test06() {
Map<String, Object> map = dao.selectAllStudentsMap();
System.out.println(map.get("李四"));
}
@Test
public void test07() {
Student student = dao.selectStudentById(19);
System.out.println(student);
}
@Test
public 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;
@Before
public void before() {
sqlSession = MyBatisUtils.getSqlSession();
dao = sqlSession.getMapper(IStudentDao.class);
}
@After
public void after() {
if (sqlSession != null) {
sqlSession.close();
}
}
@Test
public 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();
}
@Test
public void test02() {
Student student = new Student("张三", 23, 93.5);
System.out.println("插入前:student = " + student);
dao.insertStudentCatchId(student);
System.out.println("插入后:student = " + student);
sqlSession.commit();
}
@Test
public void test03() {
dao.deletetudentById(17);
sqlSession.commit();
}
@Test
public void test04() {
Student student = new Student("李四", 23, 93.5);
student.setId(18);
dao.updateStudent(student);
sqlSession.commit();
}
@Test
public 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();
*
* }
*/
@Test
public void test07() {
Student student = dao.selectStudentById(19);
System.out.println(student);
sqlSession.commit();
}
@Test
public 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、修改测试类
@Test
public 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,score
from student
where 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,score
from student
where 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,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>
</select>
测试类
@Test
public 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,score
from 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,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>
</select>
测试类
//choose用法 when中有一个成立了,后面就不看了
@Test
public 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,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 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,score
from student
<if test="list.size > 0">
where id in
<foreach collection="list" item="myid" open="(" close=")" separator=",">
#{myid}
</foreach>
</if>
</select>
测试
@Test
public 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,score
from student
<if test="list.size > 0">
where id in
<foreach collection="list" item="stu" open="(" close=")" separator=",">
#{stu.id}
</foreach>
</if>
</select>
测试
@Test
public 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>
测试
@Test
public 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();
}