mybatis参数的传递

最新推荐文章于 2024-06-15 22:22:43 发布

swop_

最新推荐文章于 2024-06-15 22:22:43 发布

阅读量462

点赞数

分类专栏： Java mybatis

本文链接：https://blog.youkuaiyun.com/swop_/article/details/95878504

版权

Java 同时被 2 个专栏收录

20 篇文章

订阅专栏

mybatis

3 篇文章

订阅专栏

mybatis的参数传递

#{}和${}

mybatis的参数传递有两种方式,分别是使用#{}和${}这两种方式之间存在一些差异#{}会解析为一个JDBC预编译语句（PreparedStatement）的参数标记符,简单来说就是一个占位符?而传入的参数将会经过的强制类型检查和安全检查等处理，最后作为一个合法的字符串传入.${}这种方式只会做简单的字符串替换，在动态SQL解析阶段将会进行变量替换.所以使用${}时会有SQL注入的风险,

使用#{}传参

xml映射文件如下

<?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 namespace="sz.lz.dao.IUserDao">

	<select id="queryById1" parameterType="int" resultType="sz.lz.vo.User">
		select * from t_user where id=#{id}
	</select>
			
</mapper>

mybatis全局配文件

<?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>
	<!--properties配置一些常用的变量  -->
	<!-- jdbc的数据库连接配置写入到 db.properties -->
	<properties resource="db.properties">

	</properties>
		
	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="${className}"/>
				<property name="url" value="${url}"/>
				<property name="username" value="${username}" />
				<property name="password" value="${password}"  />
			</dataSource>
		</environment>
	</environments>
	
	
	<!-- 注册映射文件 -->
	<mappers>
		<mapper resource="sz/lz/dao/IUserDao.xml" />
	</mappers>
</configuration>

IUserDao接口

package sz.lz.dao;

import sz.lz.vo.User;

public interface IUserDao {
		
	public User queryById1(Integer id);
	
}

测试方法

public class Test {

	@org.junit.Test
	public void test() throws IOException {
		InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
		SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
		SqlSession session = ssf.openSession();
		IUserDao dao = session.getMapper(IUserDao.class);
		User user = dao.queryById1(1);
		System.out.println(user);
		
	}
}

测试结果

![[外链图片转存失败(img-LCMuKf6x-1563081922006)(D:\Typora\截图\mybatis#{}测试.png)](https://img-blog.csdnimg.cn/20190714132610739.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N3b3Bf,size_16,color_FFFFFF,t_70)

由上面可以看出来#{}在sql语句中是以占位符的形式存在的,另外这里因为要查看sql语句使用了log4j下面是log4j的具体配置如下

maven坐标

<dependency>
  		<groupId>org.slf4j</groupId>
  		<artifactId>slf4j-log4j12</artifactId>
  		<version>1.7.25</version>
 </dependency>

resources文件夹下的log4j.properties文夹中的配置,分别在控制台输出日志和吧日志写入E:\\Logs\\mybatis-param\\mybatis.log这个文件中

$[外链图片转存失败(img-EZtew2rx-1563081922008)(D:\Typora\截图\log4j配置文件.png)]$

log4j.rootCategory=ALL, stdout , R
 
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=[QC] %p [%t] %C.%M(%L) | %m%n
 
log4j.appender.R=org.apache.log4j.DailyRollingFileAppender
log4j.appender.R.File=E:\\Logs\\mybatis-param\\mybatis.log
log4j.appender.R.layout=org.apache.log4j.PatternLayout
log4j.appender.R.layout.ConversionPattern=%d-[TS] %p %t %c - %m%n
log4j.appender.R.Encoding=UTF-8

使用${}传参

xml映射文件

<?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 namespace="sz.lz.dao.IUserDao">

	<select id="queryById1" parameterType="int" resultType="sz.lz.vo.User">
		select * from t_user where id=#{id}
	</select>
	<select id="queryById2" parameterType="int" resultType="sz.lz.vo.User">
		select * from t_user where id=${id}
	</select>
		
</mapper>

IUserDao接口

package sz.lz.dao;

import org.apache.ibatis.annotations.Param;

import sz.lz.vo.User;

public interface IUserDao {
	
	//#{}传参时如果只有一个参数那么你在#{}中任意写什么都可以
	public User queryById1(Integer id);
	//使用${}传参时需要使用@Param()注解给参数命名否则会报错
	public User queryById2(@Param("id")Integer id);
	
}

测试方法

public class Test {

	@org.junit.Test
	public void test() throws IOException {
		InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
		SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
		SqlSession session = ssf.openSession();
		IUserDao dao = session.getMapper(IUserDao.class);
		User user = dao.queryById2(1);
		System.out.println(user);
		
	}
}

$[外链图片转存失败(img-n1XmZiHN-1563081922009)(D:\Typora\截图${}传参.png)]$

这里可以看出${}传参是直接替换了sql语句中的${id}所以说使用${}会有sql注入的风险,但是有些场景有只能用${}比如你传递的是表名或是列名时

xml映射文件

<?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 namespace="sz.lz.dao.IUserDao">

	<select id="queryById1" parameterType="int" resultType="sz.lz.vo.User">
		select * from t_user where id=#{id}
	</select>
	<select id="queryById2" parameterType="int" resultType="sz.lz.vo.User">
		select * from t_user where id=${id}
	</select>
	
	<select id="queryAll1" parameterType="String" resultType="sz.lz.vo.User">
		select * from ${tableName}
	</select>
	
	<select id="queryAll2" parameterType="String" resultType="sz.lz.vo.User" >
		select * from #{tableName}
	</select>
	
</mapper>

IUserDao接口

package sz.lz.dao;

import java.util.List;

import org.apache.ibatis.annotations.Param;

import sz.lz.vo.User;

public interface IUserDao {
	
	
	public User queryById1(Integer id);
	
	public User queryById2(@Param("id")Integer id);
	
	public List<User> queryAll1(@Param("tableName")String tableName);
	
	public List<User> queryAll2(String tableName);
}

测试#{}传参

@org.junit.Test
	public void test6() throws IOException {
		InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
		SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
		SqlSession session = ssf.openSession();		
		IUserDao dao = session.getMapper(IUserDao.class);
		List<User> list = dao.queryAll2("t_user");
		for (User user : list) {
			System.out.println(user);
		}
		
		
	}

$[外链图片转存失败(img-OBDlMcCe-1563081922010)(D:\Typora\截图#{}传表名.png)]$

如图所示使用#{}传递表名会报错因为传递进去的表名变成了't_user'所以会报错而用${}则不会报错

测试${}传参

@org.junit.Test
	public void test5() throws IOException {
		InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
		SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
		SqlSession session = ssf.openSession();		
		IUserDao dao = session.getMapper(IUserDao.class);
		List<User> list = dao.queryAll1("t_user");
		for (User user : list) {
			System.out.println(user);
		}
				
	}

$[外链图片转存失败(img-4ie4M805-1563081922012)(D:\Typora\截图${}传表名.png)]$

由上面的结果可以看出${}是没有问题的

多个参数的传递

推荐使用#{}

Mapper接口中有多个参数，无论参数名是什么，在Mapper.xml文件中，参数都是arg0、arg1…或者param1、param2…

xml映射文件

<?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 namespace="sz.lz.dao.IUserDao">

	<select id="queryById1" parameterType="int" resultType="sz.lz.vo.User">
		select * from t_user where id=#{id}
	</select>
	<select id="queryById2" parameterType="int" resultType="sz.lz.vo.User">
		select * from t_user where id=${id}
	</select>
	<!-- 使用User对象传递参数可以直接使用#{}大括号中加属性名称来取得参数-->
	<insert id="addUser1" parameterType="sz.lz.vo.User">
		insert into t_user(name,age) value(#{name},#{age})
	</insert>
	<!-- 传递多个参数可以直接使用使用@Param()注解给参数命名-->
	<insert id="addUser2" >
		insert into t_user(name,age) value(#{name},#{age})
	</insert>
	<!-- 传递多个参数可以直接使用arg0、arg1…或者param1、param2…来取参数-->
	<insert id="addUser3" >
		insert into t_user(name,age) value(#{arg0},#{arg1})
	</insert>
	
	<select id="queryAll1" parameterType="String" resultType="sz.lz.vo.User">
		select * from ${tableName}
	</select>
	
	<select id="queryAll2" parameterType="String" resultType="sz.lz.vo.User">
		select * from #{tableName}
	</select>
	
</mapper>

IUserDao接口

package sz.lz.dao;

import java.util.List;

import org.apache.ibatis.annotations.Param;

import sz.lz.vo.User;

public interface IUserDao {
	
	
	public User queryById1(Integer id);
	
	public User queryById2(@Param("id")Integer id);
	
	public int addUser1(User user);
	
	public int addUser2(@Param("name")String name,@Param("age")Integer age);
	
	public int addUser3(String name,Integer age);
	
	public List<User> queryAll1(@Param("tableName")String tableName);
	
	public List<User> queryAll2(String tableName);
}

addUser1测试方法

	@org.junit.Test
	public void test2() throws IOException {
		InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
		SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
		SqlSession session = ssf.openSession();
		User user = new User("赵六", 33);
		IUserDao dao = session.getMapper(IUserDao.class);
		int count = dao.addUser1(user);
		session.commit();
		System.out.println(count);
				
	}

测试结果

$[外链图片转存失败(img-UFUtWUGj-1563081922013)(D:\Typora\截图\addUser1.png)]$

addUser2测试方法

	@org.junit.Test
	public void test3() throws IOException {
		InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
		SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
		SqlSession session = ssf.openSession();		
		IUserDao dao = session.getMapper(IUserDao.class);
		int count = dao.addUser2("孙七",34);
		session.commit();
		System.out.println(count);		
	}

$[外链图片转存失败(img-sJQRnwqz-1563081922014)(D:\Typora\截图\adduser2测试.png)]$

addUser3测试方法

@org.junit.Test
	public void test4() throws IOException {
		InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
		SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
		SqlSession session = ssf.openSession();
		
		IUserDao dao = session.getMapper(IUserDao.class);
		int count = dao.addUser3("孙八",34);
		session.commit();
		System.out.println(count);
			
	}

$[外链图片转存失败(img-VdSLq4T5-1563081922015)(D:\Typora\截图\adduser3.png)]$

取得返回结果

1.可以用ResultType来规定返回的是什么类型的数据:对于简单数据类型，例如查询总记录数、查询某一个用户名这一类返回值是一个基本数据类型的，直接写Java中的基本数据类型即可,如果返回的是一个对象或者集合，并且对象中的属性和查询的字段名是一一对应的，那么resultType也可以直接写一个对象。

2.如果属性名和字段名不一致可以用ResultMap来解决属性名和字段名不一致问题

ResultMap示例

User对象中的一个字段和t_user表中的字段不一致,在数据库中字段名为name但是在java对象中名为realName

数据库字段名

$[外链图片转存失败(img-C2ZPd6u4-1563081922016)(D:\Typora\截图\user表数据库字段名.png)]$

User java 对象

package sz.lz.vo;

public class User {

	private Integer id;
	private String realName;
	private Integer age;

	public User(Integer id, String realName, Integer age) {
		super();
		this.id = id;
		this.realName = realName;
		this.age = age;
	}

	public User() {
		super();
	}

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getRealName() {
		return realName;
	}

	public void setRealName(String realName) {
		this.realName = realName;
	}

	public Integer getAge() {
		return age;
	}

	public void setAge(Integer age) {
		this.age = age;
	}

	@Override
	public String toString() {
		return "User [id=" + id + ", realName=" + realName + ", age=" + age + "]";
	}

}

xml映射文件

<?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 namespace="sz.lz.dao.IUserDao">
	
	<!-- 定义数据库中的字段和java对象中的字段映射 -->
	<resultMap type="sz.lz.vo.User" id="userMap">
		<id column="id" property="id"/>
		<result column="name" property="realName" />
		<result column="age" property="age" />	
	</resultMap>
												<!-- 引用上面定义的映射 -->
	<select id="queryById" parameterType="int" resultMap="userMap">
		select id,name,age from t_user where id=#{id}
	</select>
		
</mapper>

IUserDao接口中定义的方法

package sz.lz.dao;

import sz.lz.vo.User;

public interface IUserDao {

	public User queryById(Integer id) ;

}

测试方法

@org.junit.Test
	public void test() throws IOException {
		InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
		SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
		SqlSession session = ssf.openSession();
		IUserDao dao = session.getMapper(IUserDao.class);
		User user = dao.queryById(1);
		System.out.println(user);
		
	}

测试结果

$[外链图片转存失败(img-vWUzdvNi-1563081922017)(D:\Typora\截图\resultMap.png)]$

或者还可以直接在查询时给相应的字段取个别名,如下

<?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 namespace="sz.lz.dao.IUserDao">
													
	<select id="queryById" parameterType="int" resultType="sz.lz.vo.User">
		select id,name realName,age from t_user where id=#{id}
	</select>
		
</mapper>