mybatis第二篇
1.${}和#{}的区别
- 1.#在传参的时候,会自动拼接单引号;$不能拼接单引号;
2.$传参时,一般不支持jdbcType指定类型的写法;#则可以;如:
#{name,jdbcType=VARCHAR}
3.$一般用于在sql中拼接表名,结果排序,模糊查询等操作;其他正常参数传递一般使用
4.因为${}使用后不会自动拼接单引号,所以可能还会导致sql攻击
select * from user where username=${username} and password=${username}
当输入值为" ttt' or '22'='22' 时,sql就被替换为
select * from user where username='ttt' or '22'='22' and password='ttt' or '22'='22'
在影射文件中结果类型的处理
<resultMap type="cn.sz.gl.pojo.DeptPlus" id="deptinfo">
<result column="deptno" property="deptno"/>
<result column="dname" property="dname"/>
<result column="loc" property="loc"/>
<!--第一种,通过association 标签实现连表查询
<association column="deptno" property="dept"
select="cn.sz.gl.dao.DeptDao.findById"></association>-->
<!--第二种 连表查询,多对一模式
<collection property="emplist">
<id column="empno" property="empno"/>
<result column="ename" property="ename"/>
<result column="job" property="job"/>
<result column="hiredate" property="hiredate"/>
<result column="sal" property="sal"/>
<result column="comm" property="comm"/>
</collection>
-->
<!-- 方案3 联表查询下,一对多-->
<collection property="emplist" column="deptno"
ofType="cn.sz.gl.pojo.Emp" javaType="java.util.List"
select="cn.sz.gl.pojo.Emp.findByDeptno"></collection>
</resultMap>
<!--根据部门编号查看部门所有员工 1对多-->
<select id="findByIdlist" resultMap="deptinfo" parameterType="java.lang.String">
select deptno, dname, loc from dept where deptno=#{deptno}
</select>
定义接口方法
/**
* 根据部门编号查看部门所有员工
* @param deptno Integer
* @return DeptPlus 部门和员工的组合
*/
public DeptPlus findByIdlist(Integer deptno);
测试
@Test
public void testFindByIdlist() {
SqlSession sqlsession
=MySqlSessionFactory.getMySqlSession();
//通过工厂调用getMapper获取接口实例
DeptDao dao = sqlsession.getMapper(DeptDao.class);
System.out.println(dao.findByIdlist(10));
MySqlSessionFactory.closeSqlSession();
}
2.在插入数据时获取主键
<!-- 增加 -->
<insert id="insert" parameterType="cn.sz.gl.pojo.Users" >
insert into users(id,name,password)
values(users_seq.nextval, #{name,jdbcType=VARCHAR},
#{password,jdbcType=VARCHAR})
</insert>
这里提供两种方案
-
在oracle中,因为自身使用序列自增策略
我们在insert语句中加入selectKey 这样就会把主键,映射到实体类的主键上
<insert id="insert" parameterType="cn.sz.gl.pojo.Users" > <selectKey order="AFTER" keyProperty="empno" resultType=" java.lang.Integer"> select emp_seq.currval from daul </selectKey> insert into users(id,name,password) values(users_seq.nextval, #{name,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR}) </insert>
-
对于mysql和mssql的主键自增策略
先设置启用主键自增策略,将属性useGeneratedKeys="true,指定返回到实体类的属性名,设置对应列名keyColumn="empno",后就会映射到实体类之中
<!-- 增加 --> <insert id="insert" parameterType="cn.sz.gl.pojo.Emp" useGeneratedKeys="true" keyColumn="empno" keyProperty="empno" > insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(emp_seq.nextval, #{ename,jdbcType=VARCHAR}, #{job,jdbcType=VARCHAR}, #{mgr,jdbcType=NUMERIC}, #{hiredate,jdbcType=DATE}, #{sal,jdbcType=NUMERIC}, #{comm,jdbcType=NUMERIC}, #{deptno,jdbcType=NUMERIC}) </insert>
3.ThreadLocal本地线程的使用
现编写工具类MySqlSessionFactory.java
public class MySqlSessionFactory {
private static final String RESOURCE = "mybatis_config.xml";
private static SqlSessionFactoryBuilder builder = null;
private static SqlSessionFactory factory = null;
private static ThreadLocal<SqlSession> threadLocal = new ThreadLocal<SqlSession>();
static{
try {
InputStream is = Resources.getResourceAsStream(RESOURCE);
builder = new SqlSessionFactoryBuilder();
factory = builder.build(is);
} catch (IOException e) {
System.out.println("加载配置文件.....");
}
}
public static SqlSession getMySqlSession() {
SqlSession sqlSession = threadLocal.get();
if(sqlSession==null) {
sqlSession = factory.openSession();
threadLocal.set(sqlSession);
}
return sqlSession;
}
public static void closeSqlSession() {
SqlSession sqlSession = threadLocal.get();
if(sqlSession!=null) {
sqlSession.close();
}
threadLocal.set(null);
}
}
使用在service中
public class UsersServiceImpl implements UsersService {
private static SqlSession sqlsession
=MySqlSessionFactory.getMySqlSession();
private UsersDao dao = null;
/**
* 查询全部
* @return
*/
public List<Users> findAll(){
try {
dao=sqlsession.getMapper(UsersDao.class);
return dao.findAll();
} catch (Exception e) {
System.out.println("findAll'查询列表失败!");
}
return Collections.EMPTY_LIST;
}
}
测试
public class UsersServiceImplTest {
private UsersService service=null;
@Before
public void init() {
service=new UsersServiceImpl();
}
@Test
public void testFindAll() {
service.findAll().forEach(System.out::println);
}
}