Mybatis接口注释方式执行sql语句,多对一,一对多查询
接口注释方式执行sql语句
-
导入mybatis依赖包
-
编写工具类实现sqlsession
package utils; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import java.io.IOException; import java.io.InputStream; /** * @Author: nsk * @Description: * @Date: create in 2021/3/31 17:29 */ public class MybatisUtil { private static SqlSessionFactory sqlSessionFactory; static{ try { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); } catch (IOException e) { e.printStackTrace(); } } //openSession参数为true,当创建session的时候,执行insert update delete将自动commit //开发尽量不用自动提交 public static SqlSession getSession(){ return sqlSessionFactory.openSession(true); } }
-
编写Mapper接口实现简单的sql注解
package dao; import org.apache.ibatis.annotations.Delete; import org.apache.ibatis.annotations.Insert; import org.apache.ibatis.annotations.Param; import org.apache.ibatis.annotations.Select; import pojo.Student; import pojo.Teacher; import java.util.List; //注意:注解方式使用sql,只能一些简单的sql操作 //使用@Param()注意事项: //1.基本数据类型要加上@Param() 引用数据类型不用添加(若只有一个属性可以不加) //2.对象使用#{属性名}会自动查找引用对象中的属性是否匹配 //3.mapper.xml中的#{}引用就是在此注释的@Param(),注意辨别 public interface StudentMapper { @Select("select * from student") List<Student> getStudents(); @Select("select * from student where id = #{id}") Student getStudentsById(@Param("id") int id); @Insert("insert into student values(#{id},#{name},#{pwd})") int insert(Student student); @Insert("update student set name = #{name},pwd = #{pwd} where id = #{id}") int update(Student student); @Delete("delete from student where id = #{id}") int delete(@Param("id") int id); }
-
编写测试类
package dao; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import pojo.Student; import utils.MybatisUtil; import java.util.List; /** * @Author: nsk * @Description: * @Date: create in 2021/4/1 20:16 */ public class StudentMapperTest { @Test public void getUsers(){ SqlSession session = MybatisUtil.getSession(); StudentMapper mapper = session.getMapper(StudentMapper.class); List<Student> students = mapper.getStudents(); for (Student s: students) System.out.println(s); } @Test public void getUserById() { SqlSession session = MybatisUtil.getSession(); StudentMapper mapper = session.getMapper(StudentMapper.class); Student students = mapper.getStudentsById(1); System.out.println(students); } @Test public void insert() { SqlSession session = MybatisUtil.getSession(); StudentMapper mapper = session.getMapper(StudentMapper.class); Student s = new Student(12,"insertName","insertPWd",null); int a = mapper.insert(s); if(a ==1 )session.commit(); session.close(); System.out.println(a); } @Test public void update() { SqlSession session = MybatisUtil.getSession(); StudentMapper mapper = session.getMapper(StudentMapper.class); Student s = new Student(12,"1111","11111",null); int a = mapper.update(s); session.close(); System.out.println(a); } @Test public void delete(){ SqlSession session = MybatisUtil.getSession(); StudentMapper mapper = session.getMapper(StudentMapper.class); int a = mapper.delete(12); System.out.println(a); } }
-
#{}和${}的区别:
-
#{}执行sql语句的时候mybatis会自动给属性加上""能有效防止sql注入
-
${}执行sql语句的时候mybatis只会拼接属性,可能发生sql注入
-
使用order by 动态参数的时候应该使用${}
多对一查询
-
多个学生对一个老师
-
编写javaBean
-
package pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import org.apache.ibatis.type.Alias; /** * @Author: nsk * @Description: * @Date: create in 2021/4/6 19:27 */ @Alias("teacher") @Data @AllArgsConstructor @NoArgsConstructor public class Teacher { private int id; private String name; private String pwd; }
-
package pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import org.apache.ibatis.type.Alias; /** * @Author: nsk * @Description: * @Date: create in 2021/4/1 20:13 */ @Alias("student") @Data @AllArgsConstructor @NoArgsConstructor public class Student { private int id; private String name; private String pwd; private Teacher teacher; }
-
编写StudentMapper.java接口和StudentMapper.xml使用详情看注释
-
package dao; import pojo.Student; import pojo.Teacher; import java.util.List; public interface StudentMapper { //跟teacher 多对一 List<Student> getStudent(); List<Student> getStudent2(); }
-
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <!--绑定对应的mapper接口,并且使用mybatis-config中的alis别名--> <mapper namespace="dao.StudentMapper"> <!--防止查出来的teacher为null 1.查询所有的学生信息 2.根据查询出来的学生的tid,寻找对应的老师 --> <!--子查询方式--> <resultMap id="studentTeacher" type="student"> <result property="id" column="id"/> <result property="name" column="name"/> <result property="pwd" column="pwd"/> <!--复杂的属性,需要单独处理 对象使用:association 集合:collection --> <association property="teacher" column="tid" javaType="teacher" select="getTeacherById"/> </resultMap> <select id="getStudent" resultMap="studentTeacher"> select * from student </select> <!--子查询中的where id = #{id}由于只有一个属性,可以任意填写#{id}比如#{12}--> <select id="getTeacherById" resultType="teacher"> select * from teacher where id = #{id} </select> <!--按照结果嵌套处理,连表查询方式--> <resultMap id="studentTeacher2" type="student"> <result property="id" column="id"/> <result property="name" column="name"/> <result property="pwd" column="pwd"/> <!--这里的property是要跟javaBean中的Teacher对象的属性名相同--> <association property="teacher" javaType="teacher"> <!--将查询出来的结果--> <result property="id" column="id"/> <result property="name" column="name"/> <result property="pwd" column="pwd"/> </association> </resultMap> <select id="getStudent2" resultMap="studentTeacher2"> select * from student s, teacher t where s.tid = t.id </select> </mapper>
-
测试
-
package dao; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import pojo.Student; import pojo.Teacher; import utils.MybatisUtil; import javax.rmi.CORBA.Util; import java.util.List; /** * @Author: nsk * @Description: * @Date: create in 2021/4/6 19:31 */ public class Many2One { @Test public void getTeacher(){ SqlSession session = MybatisUtil.getSession(); TeacherMapper mapper = session.getMapper(TeacherMapper.class); Teacher t = mapper.getTeacherById(1); System.out.println(t); } @Test public void getStudent(){ SqlSession session = MybatisUtil.getSession(); StudentMapper mapper = session.getMapper(StudentMapper.class); List<Student> students = mapper.getStudent(); for (Student s :students){ System.out.println(s); } } @Test public void getStudent2(){ SqlSession session = MybatisUtil.getSession(); StudentMapper mapper = session.getMapper(StudentMapper.class); List<Student> students = mapper.getStudent2(); for (Student s :students){ System.out.println(s); } } }
-
一对多,一个老师对应多个学生
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--绑定对应的mapper接口,并且使用mybatis-config中的alis别名-->
<mapper namespace="dao.one2many.Teacher2Mapper">
<!--嵌套查询-->
<select id="getAllTeacher" resultType="teacher2">
select * from teacher
</select>
<!--注意这边的表头的别名-->
<select id="getTeacher2" resultMap="TeacherStudent">
select s.id sid,s.name sname ,t.id tid, t.name tname from
student s, teacher t
where t.id = s.tid and t.id = #{tid}
</select>
<resultMap id="TeacherStudent" type="teacher2">
<!--column要对应sql语句查出来的表头,错了就得不到正确的结果-->
<result column="tid" property="id"/>
<result column="tname" property="name"/>
<!--复杂属性 对象使用:association 集合使用:collection
javaType指定属性类型
集合中的泛型信息,使用ofType
-->
<!--取出来student的每个值作为老师的属性,所以collection 是student类型的-->
<collection property="student2List" ofType="student2" >
<!--column要对应sql语句查出来的表头,错了就得不到正确的结果-->
<result column="sid" property="id"/>
<result column="sname" property="name"/>
</collection>
</resultMap>
<!--子查询-->
<!--一个teacher 注意:select的列不要用 * 否则得不到 id ,要指定列的名字-->
<select id="getTeacher3" resultMap="TeacherStudent2">
select * from teacher where id = #{tid}
</select>
<resultMap id="TeacherStudent2" type="teacher2">
<!--子查询出来的是一个集合所以要写生javaType,ofType是ArrayList集合里面的类型是student
因为是子查询,所以加一个select属性,这个select返回student集合,column 是从teacher中取的id
作为子查询的参数
-->
<collection property="student2List" javaType="ArrayList" ofType="student2"
select="getStudentByTeacherId" column="id">
</collection>
</resultMap>
<!--这是上面的子查询,不需要在接口类中写接口,返回类型是student的集合,-->
<select id="getStudentByTeacherId" resultType="student2">
select * from student where tid = #{id}
</select>
</mapper>