<selectid="getEmployeeByCond"resultType="com.lnhoo.dao.Employee"> select * from employees where <iftest="id!=null"> id=#{id} </if> <iftest="lastName!=null and lastName!=''"> and last_name like #{lastName} </if> </select>
<selectid="getEmployeeByCond"resultType="com.lnhoo.dao.Employee"> select * from employees where 1=1 <iftest="id!=null"> and id=#{id} </if> <iftest="lastName!=null and lastName!=''"> and last_name like #{lastName} </if> </select>
也可以使用where标签将所有查询条件包括在内，自动将多出来的and和or去掉，
1 2 3 4 5 6 7 8 9 10 11
<selectid="getEmployeeByCond"resultType="com.lnhoo.dao.Employee"> select * from employees <where> <iftest="id!=null"> id=#{id} </if> <iftest="lastName!=null and lastName!=''"> and last_name like #{lastName} </if> </where> </select>
使用where标签的时候，and应该放在条件语句之前，比如这里“and”就放在“last_name like #{lastName}”之前，如果放在后面拼接出的sql就不符合语法规则
3 trim自定义字符串前后缀
可以使用trim标签自定义字符串的前后缀，
1 2 3 4 5 6 7 8 9 10 11
<selectid="getEmployeeByCondTrim"resultType="com.lnhoo.dao.Employee"> select * from employees <trimprefix="where"suffixOverrides="and"> <iftest="id!=null"> id=#{id} and </if> <iftest="lastName!=null and lastName!=''"> last_name like #{lastName} and </if> </trim> </select>
<selectid="getEmployeeForEach"resultType="com.lnhoo.dao.Employee"> select * from employees where id in <foreachcollection="ids"item="eid"open="("close=")"separator=","> #{eid} </foreach> </select>
<selectid="getEmployeeByName"resultType="com.lnhoo.dao.Employee"> <bindname="_lastName"value="'%' + lastName + '%'"/> select * from employees where last_name like #{_lastName} </select>
模糊查询无法通过“#”操作符取参数值，并且如果使用“$”操作符取参数值会有sql注入的风险
bind标签可以将OGNL表达式的值绑定到1个变量中，后续可以用”#”操作符引用
9 抽取可重用的sql片段
抽取可重用的sql片段，方便后续引用，
1 2 3 4 5 6 7 8 9 10
<selectid="getEmployeeByName"resultType="com.lnhoo.dao.Employee"> <bindname="_lastName"value="'%' + lastName + '%'"/> select <includerefid="empCols"></include> from employees where last_name like #{_lastName} </select>
<mappernamespace="com.lnhoo.dao.EmployeeMapper"> <selectid="getEmployeeById"resultType="com.lnhoo.Employee"> select * from employees where id = #{id} </select>
<updateid="updateEmployee"parameterType="com.lnhoo.Employee"> update employees set last_name=#{lastName}, gender=#{gender}, email=#{email} where id=#{id} </update>
<deleteid="deleteEmployeeById"parameterType="int"> delete from employees where id=#{id} </delete> </mapper>
<selectid="getEmployeeByMap"resultType="com.lnhoo.Employee"> select * from employees where id=#{id} and last_name=#{lastName, jdbcType=NULL}; </select>
<selectid="getEmployeeById"resultMap="Employee"> select e.id id, last_name, gender, email, d.id did, dept_name from employees e, departments d where e.dept_id=d.id and e.id=#{id} </select>
publicinterfaceDepartmentMapper{ Department getDepartmentById(Integer id); }
此时与接口绑定的查询sql配置为，
1 2 3 4 5
<selectid="getDepartmentById"resultMap="Department"> select d.id id, dept_name, e.id eid, last_name, gender, email, dept_id from departments d left join employees e on d.id=e.dept_id where d.id=#{id} </select>
<mappernamespace="com.lnhoo.dao.EmployeeMapper"> <selectid="getEmployeeById"resultType="emp"> select * from employees where id = #{id} </select> </mapper>
<mappernamespace="com.lnhoo.dao.EmployeeMapper"> <selectid="getEmployeeById"resultType="emp"> select * from employees where id = #{id} </select> <selectid="getEmployeeById"resultType="emp"databaseId="mysql"> select * from employees where id = #{id} </select> <selectid="getEmployeeById"resultType="emp"databaseId="oracle"> select id, last_name, email from tbl_employees where id = #{id} </select> </mapper>
/** * Returns an instance of a proxy class for the specified interfaces * that dispatches method invocations to the specified invocation * handler. * @param loader the class loader to define the proxy class * @param interfaces the list of interfaces for the proxy class * to implement * @param h the invocation handler to dispatch method invocations to * @return a proxy instance with the specified invocation handler of a * proxy class that is defined by the specified class loader * and that implements the specified interfaces */ staticObject newProxyInstance(ClassLoader loader, Class<?>[] interfaces,InvocationHandlerinvocationHandler );
2.1.1.2 创建方式
假设有一接口，
1 2 3
publicinterfaceUserDao{ publicintadd(int a, int b); }
这个接口有一实现类，
1 2 3 4 5 6
publicclassUserDaoImplimplementsUserDao{ @Override publicintadd(int a, int b){ return a + b; } }
