传递参数6种方式:(以select为例)
1.传递单个简单类型的参数(掌握)
2.传递多个简单类型的参数(掌握)
3.传递对象类型的参数(掌握)
4.传递位置代表参数
5.传递Map类型的参数
6.使用${ }代表参数
7.分页实例
=============================================================
以下介绍都使用表student
CREATE TABLE `student` (
`id` int(11) NOT NULL ,
`name` varchar(255) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
实体类:Student.java
工具类:MybatisUtil.java
public class MybatisUtil{
private static SqlSessionFactory factory;
static{
try{
InputStream in = Resources.getResourcesAsStreamL("mapper.xml文件路径");
SqlSessionFactoryBuilder builder=new SqlSessionFactoryBuilder();
factory = builder.build(in);
}catch(IOExecption e){
e.printStackTrace();
}
}
public static SqlSession getSqlSession(){
SqlSession session =null;
if(factory!=null){
session = factory.openSession(true);
}
return session;
}
}
1.传递单个简单类型的参数(掌握)
StudentDao.java
public Interface StudentDao{
//传递简单参数,简单参数指的是基本数据类型和String
List<Student> selectStudentById(int id);
}
StudentDao.xml
<mapper namespace="dao接口全限定名" >
<select id="selectStudentById" resultType="Student类的全限定名" >
select id,name,email,age from student where id=#{stuid}
</select>
</mapper>
因为dao接口中的方法传入的参数是int(简单类型参数),所以在对应的xml文件接收的变量可以任意写
2.传递多个简单类型的参数(掌握)
StudentDao.java
public Interface StudentDao{
//传递多个简单类型的参数
List<Student> selectByIdAndName(@Param("stuid") int id,@Param("stuname") String name);
}
StudentDao.xml
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="StudentDao.xml的全限定名" resultType="student类的全限定名">
<select id="selectByIdAndName" resultType="Student.java的全限定名">
select id,name,email,age from student where id=#{stuid} and name=#{stuname}
</select>
</mapper>
1)@Param("stuid")等价于@Param(value="stuid")。
2)在dao层传参使用@Param(value)这种形式传值,在对应的mapper.xml文件中接收值也采取括号中的变量接收。说白点这里的value是声明StudentDao.xml中查询语句的接收值,这里传入id,XML文件用stuid 接收
3.传递对象类型的参数(掌握)
QueryParam.java
public class QueryParam{
private int myage;
private String myname;
public QueryParam(){}
public QueryParam(int age,String name){
this.myage=age;
this.myname=name;
}
...set/get方法...
...toString方法...
}
StudentDao.java
public Interface StudentDao{
//传递对象参数
List<Student> selectByParam(QueryParam param);
}
StudentDao.xml
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="StudentDao.java全限定名">
<select id="selectByParam" resultType="Student.java的全限定名">
select id,name,email,age from student where age=#{myage} and name=#{myname}
</select>
</mapper>
TestStudent.java
public class TestSutdent{
@Test
public void testSelectByParam(){
SqlSession sessioin = MybatisUtil.getSqlSession();
StudentDao dao = session.getMapper(StudentDao.class);
QueryParam param = new QueryParam(18,"李四");
List<Student> students = dao.select(param);
students.forEach(stu->System.out.println(stu));
}
}
传递对象类型的参数,Student.java因属性值与对应数据库表的字段值相同,传入的值接收是采用的是类对应的属性值。当出现上面案例情况,传入一个类对象作为参数,在xml文件接收时也需要采用对应类(QueryParam.java)的属性值接收
4.传递位置代表参数
StudentDao.java
public Interface StudentDao{
//使用位置传递参数
List<Student> selectMultiByNameAndAge(String name,Integer age);
}
StudentDao.xml
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="StudentDao全限定名">
<!--
使用位置传入参数, 位置从0开始
语法格式 #{arg0} , #{arg1}
-->
<select id="selectMultiByNameAndAge" resultType="Student全限定名">
select id,name,email,age from student where name=#{arg0} or age=#{arg1}
</select>
</mapper>
接收参数使用arg来代替,arg0代表第一个参数,arg1代表第二个参数,以此类推
5.传递Map类型的参数
StudentDao.java
public Interface StudentDao{
/**
* 使用Map传入参数
*/
List<Student> selectMultiByMap(Map<String,Object> map);
}
StudentDao.xml
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="StudentDao全限定名">
<!--
使用Map传入参数,mapper文件中使用map的方式是:#{key}
-->
<select id="selectMultiByMap" resultType="com.bjpowernode.domain.Student">
select id,name,email,age from student where name=#{stuname} or age=#{stuage}
</select>
</mapper>
TestStudent.java
public class TestStudent{
//测试向mapper文件传入map
@Test
public void testSelectByMap(){
SqlSession sqlSession = MyBatisUtil.getSqlSession();
StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
Map<String,Object> map = new HashMap<>();
map.put("stuname", "张三");
map.put("stuage", 19);
List<Student> students = studentDao.selectMultiByMap(map);
students.forEach(stu -> System.out.println(stu));
}
}
使用Map作为参数传递,在mapper.xml文件中接收值则是map的key
6.使用${ }代表参数
StudentDao.java
public Interface StudentDao{
/*
使用 ${}
*/
Student findByDiffField(@Param("col") String columName,@Param("fval") Object fieldValue);
}
StudentDao.xml
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="StudentDao全限定名">
<select id="findByDiffField" resultType="Student全限定名">
select id,name,email,age from student where ${col} = #{fval}
</select>
</mapper>
TestStudent.java
public class TestStudent{
//测试向mapper文件,使用${}
@Test
public void testFindDiffField(){
SqlSession sqlSession = MyBatisUtil.getSqlSession();
StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
Student student = null;
//Student student = studentDao.findByDiffField("id", 1002);
//System.out.println("findById:"+student);
studentDao.findByDiffField("email", "null; drop table student");
System.out.println("findByEmail:"+student);
}
}
1) 使用${}, 是进行字符串的替换, mybatis使用Statement执行sql语句, 有sql注入的风险。
2) ${}一般是用来替换表名,列名使用。
7.分页实例
PageInfo.java
public class PageInfo {
private Integer age;
private Integer pageNo;
private Integer pageSize;
...set/get方法...
...toString方法...
}
StudentDao.java
public Interface StudentDao{
//分页显示 age > 18的记录,每页两条数据
List<Student> findStudentPage(PageInfo pageInfo);
}
StudentDao.xml
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="StudentDao全限定名">
<select id="findStudentPage" resultType="Student全限定名">
select id,name,email,age from student where age> #{age} order by id limit #{pageNo},#{pageSize}
</select>
</mapper>
mybatis.xml
需要在mybatis.xml文件中注册分页插件。在这之前同时需要在pom.xml文件中添加pageHelper依赖
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--配置mybatis日志-->
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<!--注册分页插件-->
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor" />
</plugins>
<environments default="mysql">
<environment id="mysql">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<!--使用属性配置文件的语法 ${key}-->
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/springdb"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<!--指定mapper文件的位置-->
<mappers>
<mapper resource="com/dao/StudentDao.xml"/>
</mappers>
</configuration>
TestStudent.java
public class TestStudent{
@Test
public void testPage(){
SqlSession sqlSession = MyBatisUtil.getSqlSession();
StudentDao dao = sqlSession.getMapper(StudentDao.class);
PageInfo pageInfo = new PageInfo();
pageInfo.setAge(18);
pageInfo.setPageNo(2);
pageInfo.setPageSize(2);
List<Student> students = dao.findStudentPage(pageInfo);
students.forEach(stu -> System.out.println(stu));
}
}
以上xml文件使用时都需要在mybatis.xml文件中注册才可以使用。
使用的依赖(pom.xml)
<!--PageHelper-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.1.10</version>
</dependency>
<!--junit依赖-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
<!--mybatis依赖-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.1</version>
</dependency>
<!--mysql驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.9</version>
</dependency>