MyBatis基础(三)
输入参数 parameterType
1、类型为 简单类型(8个基本类型+String)
2、类型为 对象类型
3、Map<Sting, Object>
占位符:#{}
-
类型为 简单类型(8个基本类型+String)
1、#{任意值} 2、#{}自动给String类型加上'' (自动类型转换) 3、#{}可以防止SQL注入
-
类型为 对象类型
1、#{属性名} 属性名严格区分大小写 2、获取对象的值(嵌套类型对象)
占位符:${}
-
类型为 简单类型(8个基本类型+String)
1、${value} ,其中的标识符只能是value 2、${} 原样输出,但是适合于 动态排序(动态字段) 3、${}不能防止SQL注入问题
-
类型为 对象类型
1、${属性名} 属性名严格区分大小写 2、获取对象的值(嵌套类型对象)
动态排序
-
接口方法
//根据指定的列进行排序 List<Student> queryStudentOrderByColumn(String column);
-
Mapper.xml
<select id="queryStudentOrderByColumn" parameterType="string" resultType="student" > select stuno,stuname,stuage from student order by ${value} asc </select> <!-- ${value} 为原样输出,适合动态传值 -->
-
StudentTest
public static void queryStudentOrderByColumn() throws IOException { //Connection - SqlSession操作MyBatis //conf.xml - > reader Reader reader = Resources.getResourceAsReader("conf.xml") ; //reader ->SqlSession //可以通过build的第二参数 指定数据库环境 SqlSessionFactory sessionFacotry = new SqlSessionFactoryBuilder().build(reader,"development") ; SqlSession session = sessionFacotry.openSession() ; StudentMapper studentMapper = session.getMapper( StudentMapper.class) ; List<Student> students = studentMapper.queryStudentOrderByColumn("stuno") ; //接口的方法->SQL //查询全部学生,并且根据姓名排序 System.out.println(students); session.close(); }
模糊查询
-
接口方法
//根据年龄或姓名进行模糊查询 List<Student> queryStudentBystuageOrstuName(Student student);
-
Mapper.xml
<select id="queryStudentBystuageOrstuName" parameterType="student" resultType="student" > select stuno,stuname,stuage from student where stuage= #{stuAge} or stuname like '%${stuName}%' </select>
-
StudentTest
//根据姓名或年龄查询学生 public static void queryStudentBystuageOrstuName() throws IOException { //Connection - SqlSession操作MyBatis //conf.xml - > reader Reader reader = Resources.getResourceAsReader("conf.xml") ; //reader ->SqlSession //可以通过build的第二参数 指定数据库环境 SqlSessionFactory sessionFacotry = new SqlSessionFactoryBuilder().build(reader,"development") ; SqlSession session = sessionFacotry.openSession() ; StudentMapper studentMapper = session.getMapper( StudentMapper.class) ; Student student = new Student(); student.setStuAge(24); student.setStuName("w"); List<Student> students = studentMapper.queryStudentBystuageOrstuName(student) ; //接口的方法->SQL System.out.println(students); session.close(); }
级联属性
-
实体类
public class Student { private int stuNo ; private String stuName ; private int stuAge ; private String graName ; private boolean stuSex ; private Address address;//家庭、学校 public Address getAddress() { return address; } public void setAddress(Address address) { this.address = address; } } public class Address { private String homeAddress; private String schoolAddress; public String getHomeAddress() { return homeAddress; } public void setHomeAddress(String homeAddress) { this.homeAddress = homeAddress; } public String getSchoolAddress() { return schoolAddress; } public void setSchoolAddress(String schoolAddress) { this.schoolAddress = schoolAddress; } }
-
接口方法
//查询所有学生信息,并根据学生对象中的地址进行排序 List<Student> queryStudentByaddress(Student address);
-
Mapper.xml
<!-- 输入参数为 级联属性 --> <select id="queryStudentByaddress" parameterType="student" resultType="student" > select stuno,stuname,stuage from student where homeaddress = #{address.homeAddress} or schooladdress = '${address.schoolAddress}' </select>
-
StudentTest
StudentMapper studentMapper = session.getMapper( StudentMapper.class) ; Student student = new Student(); Address address = new Address(); address.setHomeAddress("xa"); address.setSchoolAddress("x"); student.setAddress(address); List<Student> students = studentMapper.queryStudentByaddress(student) ; System.out.println(students);
输入参数为Map
-
接口方法
List<Student> queryStudentBystuageOrstuNameWithHashMap(Map<String,Object> map);//String,Object
-
Mapper.xml
<select id="queryStudentBystuageOrstuNameWithHashMap" parameterType="HashMap" resultType="student" > select stuno,stuname,stuage from student where stuage= #{stuAge} or stuname like '%${stuName}%' </select>
-
StudentTest
StudentMapper studentMapper = session.getMapper( StudentMapper.class) ; Map<String,Object> studentMap = new HashMap<>(); studentMap.put("stuAge", 24); studentMap.put("stuName", "zs"); List<Student> students = studentMapper.queryStudentBystuageOrstuNameWithHashMap (studentMap) ;//接口的方法->SQL System.out.println(students);
-
小结
输入对象为HashMap: SQL语句如:where stuage= #{stuAge} 用map中的key的值 匹配 占位符#{stuAge},如果匹配成功 就用map的value的值替换占位符
MyBatis调用存储过程
-
接口方法
//根据存储过程查询某个年级的学生总数 void queryCountByGradeWithProcedure(Map<String,Object> params); //通过存储过程,根据学号实现删除 void deleteStuBynoWithProcedure(Map<String,Object> params);
-
Mapper.xml
<!-- 通过调用[存储过程] 实现查询 ,statementType="CALLABLE" 存储过程的输入参数,在mybatis用Map来传递(HashMap) --> <select id="queryCountByGradeWithProcedure" statementType="CALLABLE" parameterType="HashMap" > { CALL queryCountByGradeWithProcedure( #{gName,jdbcType=VARCHAR,mode=IN}, #{scount,jdbcType=INTEGER,mode=OUT} ) } </select> <!-- 其中 通过statementType="CALLABLE"设置SQL的执行方式是存储过程。 存储过程的输入参数gName需要通过HashMap来指定 在使用时,通过hashmap的put方法传入输入参数的值;通过hashmap的Get方法 获取输出参数的值。 要注意Jar问题:ojdbc6.jar不能在 调存储过程时 打回车、tab,但是ojdbc7.jar可以。 --> <!-- 通过存储过程实现删除 --> <delete id="deleteStuBynoWithProcedure" statementType="CALLABLE" parameterType="HashMap"> { CALL deleteStuBynoWithProcedure( #{sno,jdbcType=INTEGER,mode=IN} ) } </delete>
-
StudentTest
//根据存储过程查询某个年级的学生总数 public static void queryCountByGradeWithProcedure() throws IOException { Reader reader = Resources.getResourceAsReader("conf.xml"); SqlSessionFactory sessionFacotry = new SqlSessionFactoryBuilder().build(reader,"development"); SqlSession session = sessionFacotry.openSession(); StudentMapper studentMapper = session.getMapper( StudentMapper.class); //通过map给 存储过程指定输入参数 Map<String,Object> params = new HashMap<>(); params.put("gName", "g1") ;//指定存储过程的输入参数gName的值是g1 studentMapper.queryCountByGradeWithProcedure(params);//调用存储过程,并传入输入参数 //获取存储过程的输出参数 Object count = params.get("scount") ; System.out.println(count); session.close(); } //根据学号 删除学生(存储过程) public static void deleteStudentByStunoWithProcedure() throws IOException { Reader reader = Resources.getResourceAsReader("conf.xml"); SqlSessionFactory sessionFacotry = new SqlSessionFactoryBuilder().build(reader,"development"); SqlSession session = sessionFacotry.openSession(); StudentMapper studentMapper = session.getMapper( StudentMapper.class); Map<String,Object> map = new HashMap<>(); map.put("sno", 3) ; studentMapper.deleteStuBynoWithProcedure(map); session.commit(); session.close(); }
-
存储过程注意事项
- 存储过程,无论输入参数是什么值,语法上都需要 用map来传递该值;
- 只要是
<transactionManager type="JDBC" />
,则增删改都需要手工commit
; - 如果报错:
No enum constant org.apache.ibatis.type.JdbcType.xx
,则说明mybatis不支持xx
类型,需要查表。
输出参数
-
8个简单类型 + String(
resultType
)一般用这个指定输出参数
-
对象类型(
resultMap
)resultMap: 实体类的属性、数据表的字段: 类型、名字不同时(stuno,id),一般用于解决表字段和类属性之间的映射关系。
<resultMap type="student" id="queryStudentByIdMap"> <!-- 指定类中的属性 和 表中的字段 对应关系 --> <id property="stuNo" column="id" /> <result property="stuName" column="name" /> </resultMap>
-
resultType
+HashMap
<!-- select 表的字段名 "类的属性名" from... 来指定字段名 和属性名的对应关系 --> <select id="queryStudentByIdWithHashMap" parameterType="int" resultType="student" > select id "stuNo",name "stuName" from student where id = #{id} </select>
-
注意:
如果查询多个字段,但发现 某一个字段结果始终为默认值(0,0.0,null),则可能是 表的字段 和 类的属性名字写错。
-