Mybatis需要注意的细节

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'

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>

这里提供两种方案

1. 在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>    

2. 对于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);
    }
}