#{}:是以预编译的映射,将参数设置到sql语句中,和jdbc的preraredStatement一样,使用占位符,防止sql注入。
${}:取出的值会直接拼装在sql中,会有安全问题。
大多数情况下的参数取值,我们都要用#{}的方式取值。
但是原生jdbc不支持占位符的地方,例如:分表,排序等等。。。我们可以使用${}
分表:比如按照年份的分表查询员工绩效等等如下:
select * from ${year}_table a where 1=1 order by a.age ${desc}
package com.yunqing.mybatis.dao;
import com.yunqing.mybatis.bean.User;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.List;
import java.util.Map;
public interface UserMapper {
//主要体现在此处,利用${tableName}获取了jdbc无法使用占位符?替代的数据库,其次用${orderName}获取了jdbc无法使用占位符?代替的排序desc倒序
@Select("select * from ${tableName} order by id ${orderName}")
List<User> getAllUser(Map<String,Object> map);
User getUserByIdAndName(@Param("id")Integer id, @Param("name")String name);
User getUserByMap(Map<String,Object> map);
//User getUserByPoJo(User user);
void insertUser(User user);
void updateUser(User user);
void deleteUserById(Integer id);
}
@Test
public void getAllUser() throws IOException {
//从xml中获取sqlSessionFactory
String resource = "conf/mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//获取sqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
//现在的mybatis接口式编程写法
//相当于接口的实现类
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
Map<String,Object> map = new HashMap<>();
//此处传入数据库名和排序的方式。
map.put("tableName","t_user");
map.put("orderName","desc");
List<User> list = userMapper.getAllUser(map);
System.out.println(list);
sqlSession.close();
}
注意:当插入一个null值的时候,#{email}在mysql中可以插入成功。
在oracle中会插入失败,报错。原因是jdbcType会在插入null值时转化成OTHER类型,oracle不支持OTHER类型。
两种解决办法:
1.#{email,jdbcType=NULL}
2.在全局设置中
<settings>
<setting name="jdbcTypeForNull" value="NULL"/>
</settings>
返回map形式的结果:
dao层接口:
Map<String,Object> getUserByIdReturnMap(Integer id);
//告诉mybatis封装的时候哪个属性作为map的key
@MapKey("name")
Map<String,User> getAllUserReturnMap();
sql xml
<select id="getUserByIdReturnMap" resultType="map">
SELECT * FROM t_user WHERE id = #{id}
</select>
<select id="getAllUserReturnMap" resultType="com.yunqing.mybatis.bean.User">
SELECT * FROM t_user
</select>
test测试类
/**
* 结果返回map
* @throws IOException
*/
@Test
public void getUserByIdReturnMap() throws IOException {
String r = "mybatis-config.xml";
InputStream is = Resources.getResourceAsStream(r);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
Map<String,Object> map = userMapper.getUserByIdReturnMap(1);
System.out.println(map);
}
/**
* 返回此种形式的map ---> Map<String,User> ---> pdd=User{id=9, name='pdd', age=33}
* @throws IOException
*/
@Test
public void getAllUserReturnMap() throws IOException {
String r = "mybatis-config.xml";
InputStream is = Resources.getResourceAsStream(r);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
Map<String,User> map = userMapper.getAllUserReturnMap();
System.out.println(map);
}
测试结果:
接口1:{name=yx, id=1, age=12}
接口2:{pdd=User{id=9, name='pdd', age=33}, uzi=User{id=7, name='uzi', age=20}, yang=User{id=2, name='yang', age=23}, yangxu=User{id=3, name='yangxu', age=34}, kang=User{id=5, name='kang', age=18}, yx=User{id=1, name='yx', age=12}}