输入参数:parameterType
1、类型为 简单类型(8个基本类型+String)
#{}(防止SQL注入) | ${} 不防止 |
---|---|
#{任意值}会自动给字符串加上' ' | ${value} 其中的标识符必须时value |
${value} 原样输出 适合于动态排序 (动态字段)
动态排序:根据传入的参数进行排序 学号
${value}
<select id="queryStudentOrderByColumn" parameterType="string" resultType="student">
select stuno,stuname,stuage from student order by ${value} desc
</select>
#{任意值,但是不要随便乱写,见名知意}
<select id="queryStudentOrderByColumn" parameterType="string" resultType="student">
select stuno,stuname,stuage from student order by #{value} desc;
</select>
#{value}:mybatis在解析是会将这个解析为 'xxx',${} 原样输出:传什么返回什么
2、对象类型
#{属性名}、${属性名}
模糊查询:根据年龄或者学号查询学生
- 方式一
<select id="queryStudentOrderBystuAgeOrstuName" parameterType="student" resultType="student">
select stuno,stuname,stuage from student
where stuage =#{stuAge} or stuname like #{stuName}
</select>
测试
Student student = new Student();
student.setStuAge(20);
student.setStuName("%w%"); //在这里要进行SQL的%拼接
List<Student> students = studentMapper.queryStudentOrderBystuAgeOrstuName(student);
- 方式二
<select id="queryStudentOrderBystuAgeOrstuName" parameterType="student" resultType="student">
select stuno,stuname,stuage from student
where stuage =#{stuAge} or stuname like '%${stuName}%' 在SQL标签中直接处理
</select>
测试
Student student = new Student();
student.setStuAge(2);
student.setStuName("w");
List<Student> students = studentMapper.queryStudentOrderBystuAgeOrstuName(student);
- 方式三:传入HashMap
<!-- 传入HashMap -->
<select id="queryStudentBystuAgeOrstuNameWithHashMap" parameterType="HashMap" resultType="student">
select stuno,stuname,stuage from
student where stuage =#{stuAge} or stuname like '%${stuName}%'
</select>
输入对象为HashMap:where stuage = #{stuAge}
用map中的key的值 匹配 占位符 #{stuAge},如果匹配成功 就用map的value替换占位符
接口
List<Student> queryStudentOrderBystuAgeOrstuName(Student student);
List<Student> queryStudentBystuAgeOrstuNameWithHashMap(Map<String,Object> map); //String,Object
测试类
// 传入参数 HashMap
public static void queryStudentOrderBystuAgeOrstuNameWithHashMap() throws IOException {
// Connection
// 加载配置文件 conf.xml ->reader
Reader reader = Resources.getResourceAsReader("conf.xml");
// reader -> SqlSession操作MyBatis
// 可以通过build第二参数来指定 数据库环境 建议默认值写死
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
Map<String, Object> studentMap = new HashMap<String, Object>();
studentMap.put("stuAge", 20);
studentMap.put("stuName", "zs");
List<Student> students = studentMapper.queryStudentBystuAgeOrstuNameWithHashMap(studentMap);
System.out.println(students); // 接口方法->sql语句
session.close();
}
3、级联属性
Student类
private int stuNo;
private String stuName;
private int stuAge;
private String graName;
private boolean stuSex;
private Address address; //家庭 学校
private StudentCard card;
//getter setter省
StudentMapper.xml
<!--根据地址查询学生 传入参数类型address-->
<select id="queryStudentByaddress" parameterType="address" resultType="student">
select stuno,stuname,stuage from student
where homeaddress = #{homeAddress} or schooladdress ='${schoolAddress}'
</select>
<!-- 输入参数为级联 此时传入的类型还是学生-->
<select id="queryStudentByaddress" parameterType="student" resultType="student">
select stuno,stuname,stuage from student
where homeaddress = #{address.homeAddress} or schooladdress ='${address.schoolAddress}'
</select>
测试代码
// 查询全部学生 并且根据地址查询学生
public static void queryStudentByaddress() throws IOException {
// Connection
// 加载配置文件 conf.xml ->reader
Reader reader = Resources.getResourceAsReader("conf.xml");
// reader -> SqlSession操作MyBatis
// 可以通过build第二参数来指定 数据库环境 建议默认值写死
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
SqlSession session = sessionFactory.openSession();
// List<Student> students = session.selectList(statement);
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
// Address address= new Address();
// address.setHomeAddress("ah");
// address.setSchoolAddress("js");
// List<Student> students = studentMapper.queryStudentByaddress(address);
Student student = new Student();
Address address = new Address();
address.setHomeAddress("ah");
address.setSchoolAddress("js");
student.setAddress(address);
List<Student> students = studentMapper.queryStudentByaddress(student);
System.out.println(students); // 接口方法->sql语句
session.close();
}
输出参数 resultType
-
简单类型(8个基本类型 +String)
-
输出参数为实体类对象类型
-
输出参数为实体对象类型的集合;虽然输出类型为集合 但是resultType依然写 集合元素类型
-
输出内容为HashMap
<select id="queryAllStudentOutByHashMap" resultType="HashMap">
select stuno "no",stuname "name" from student
</select>
输出参数 resultMap
resultMap: 实体类的属性 数据表的字段:类型、名字不同时
不使用resultMap ,也可以使用resultType+HashMap(select 表的字段名 类的属性名 from … 来指定表和属性的对应关系)
<select id="queryStudentById" parameterType="int" resultMap="queryStudentByIdMap">
select id,name from student where id=#{id}
</select>
<resultMap type="student" id="queryStudentByIdMap">
<!-- 指定类中的属性 和 表中的字段 对应关系 -->
<id property="stuNo" column="id"/>
<result property="stuName" column="name"/>
</resultMap>