SSM
在ss基础上加进行整合Mybatis(applicationContext.xml中添加配置),并添加分页拦截器(添加mybatis分页拦截器),并用generator动态生成到层。
构建基础spring + spring mvc
https://www.cnblogs.com/aeolian/p/11950980.html
SS整合Mybatis
除了mybatis官网下的mybatis.jar
Spring与Mybatis版本关系
spring 与 mybatis对应关系表
我使用的版本(之前用spring3.1整合失败,容器初始化报错升级成4.2.4的)
4.2.4.RELEASE 3.2.8 1.2.2
springconfig.xml配置
在springConfig.xml(即applicationContext.xml)中配置
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="org.logicalcobwebs.proxool.ProxoolDriver"/> <property name="url" value="proxool.hjzzAuthPlatform"/> bean> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource"/> bean> <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="dataSource" ref="dataSource" /> <property name="configLocation" value="classpath:mybatis/SqlMapConfig.xml" /> bean> <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"> <property name="basePackage" value="com.autumn" /> bean>
SqlMapConfig.xml分页配置
位于classpath:mybatis/下面,classpath为src或者resource。
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> <plugins> <plugin interceptor="com.gmtx.system.intercepts.MyPageInterceptor"> <property name="limit" value="10"/> <property name="dbType" value="mysql"/> plugin> plugins> configuration>
MyPageInterceptor分页拦截器
mapper接口的参数一定要以ByPage结尾,并且一定要用@Param注解
List ***ByPage(@Param(“currPage”) Integer currPage,@Param(“pageSize”) Integer pageSize);
package com.autumn.system.intercepts; import java.sql.Connection; import java.util.Map; import java.util.Properties; import org.apache.ibatis.executor.parameter.ParameterHandler; import org.apache.ibatis.executor.statement.StatementHandler; import org.apache.ibatis.mapping.MappedStatement; import org.apache.ibatis.plugin.Interceptor; import org.apache.ibatis.plugin.Intercepts; import org.apache.ibatis.plugin.Invocation; import org.apache.ibatis.plugin.Plugin; import org.apache.ibatis.plugin.Signature; import org.apache.ibatis.reflection.MetaObject; import org.apache.ibatis.reflection.SystemMetaObject; /** * @Intercepts 说明是一个拦截器 * @Signature 拦截器的签名 * type 拦截的类型 四大对象之一( Executor,ResultSetHandler,ParameterHandler,StatementHandler) * method 拦截的方法 * args 参数 */ @Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class})}) public class MyPageInterceptor implements Interceptor { //每页显示的条目数 private int pageSize; //当前现实的页数 private int currPage; //数据库类型 private String dbType; @Override public Object intercept(Invocation invocation) throws Throwable { //获取StatementHandler,默认是RoutingStatementHandler StatementHandler statementHandler = (StatementHandler) invocation.getTarget(); //获取statementHandler包装类 MetaObject MetaObjectHandler = SystemMetaObject.forObject(statementHandler); //分离代理对象链 while (MetaObjectHandler.hasGetter("h")) { Object obj = MetaObjectHandler.getValue("h"); MetaObjectHandler = SystemMetaObject.forObject(obj); } while (MetaObjectHandler.hasGetter("target")) { Object obj = MetaObjectHandler.getValue("target"); MetaObjectHandler = SystemMetaObject.forObject(obj); } //获取连接对象 //Connection connection = (Connection) invocation.getArgs()[0]; //object.getValue("delegate"); 获取StatementHandler的实现类 //获取查询接口映射的相关信息 MappedStatement mappedStatement = (MappedStatement) MetaObjectHandler.getValue("delegate.mappedStatement"); String mapId = mappedStatement.getId(); //statementHandler.getBoundSql().getParameterObject(); //拦截以.ByPage结尾的请求,分页功能的统一实现 if (mapId.matches(".+ByPage$")) { //获取进行数据库操作时管理参数的handler ParameterHandler parameterHandler = (ParameterHandler) MetaObjectHandler.getValue("delegate.parameterHandler"); //获取请求时的参数 MapparaObject = (Map ) parameterHandler.getParameterObject(); //也可以这样获取 //paraObject = (Map ) statementHandler.getBoundSql().getParameterObject(); //参数名称和在service中设置到map中的名称一致 currPage = Integer.parseInt((String) (paraObject.get("currPage")==null?"1":paraObject.get("currPage"))) ; pageSize = Integer.parseInt((String) (paraObject.get("pageSize")==null?"20":paraObject.get("pageSize"))) ; String sql = (String) MetaObjectHandler.getValue("delegate.boundSql.sql"); //也可以通过statementHandler直接获取 //sql = statementHandler.getBoundSql().getSql(); //构建分页功能的sql语句 String limitSql; sql = sql.trim(); limitSql = sql + " limit " + (currPage - 1) * pageSize + "," + pageSize; //将构建完成的分页sql语句赋值个体'delegate.boundSql.sql',偷天换日 MetaObjectHandler.setValue("delegate.boundSql.sql", limitSql); } //调用原对象的方法,进入责任链的下一级 return invocation.proceed(); } //获取代理对象 @Override public Object plugin(Object o) { //生成object对象的动态代理对象 return Plugin.wrap(o, this); } //设置代理对象的参数 @Override public void setProperties(Properties properties) { //如果项目中分页的pageSize是统一的,也可以在这里统一配置和获取,这样就不用每次请求都传递pageSize参数了。参数是在配置拦截器时配置的。 String limit1 = properties.getProperty("limit", "10"); this.pageSize = Integer.valueOf(limit1); this.dbType = properties.getProperty("dbType", "mysql"); } }
测试
成功整合ssm。
之前系统一直是ss + JdbcTemplate封装库,最近一个项目特别适用用mybatis的自动生成代码,做一些简单的增删改查。于是在里面又整合了mybatis,整合成功。
@Controller @RequestMapping("/common") public class CommonController { //Spring JDBCTemplate @Resource(name="commonDao") private CommonDao dao = null;; //Mybatis @Autowired AccountMapper accountMapper = null; //JDBCTemplate测试 @RequestMapping(value="/testJDBCTemplate",method={RequestMethod.GET,RequestMethod.POST}) public ModelAndView testJDBCTemplate(){ ModelAndView mv = new ModelAndView("/jsp/success"); dao.execute("INSERT INTO `account` (`sysid`, `userid`, `aut_userId`, `username`, `userpwd`) VALUES ('aaa', 'aeolian', 32323, 'fff', 'ggg')", new Object[]{}); mv.addObject("msg", "success"); return mv; } //mybatis测试 @RequestMapping(value="/testMybatis",method={RequestMethod.GET,RequestMethod.POST}) @ResponseBody public Object testMybatis(){ AccountKey pam = new AccountKey(); pam.setSysid("1"); pam.setUserid("pingtai"); return accountMapper.selectByPrimaryKey(pam); } //mybatis分页测试 @RequestMapping(value="/testMybatisByPage",method={RequestMethod.GET,RequestMethod.POST}) @ResponseBody public Object testMybatisByPage(Integer currPage,Integer pageSize){ return accountMapper.getAllByPage(currPage,pageSize); } }
遇到的问题
ERROR [localhost-startStop-1] org.springframework.web.context.ContextLoader
spring容器初始化失败。
解决方法:升级spring、mybatis、mybatis-spring到可以相互兼容的版本。
org.apache.ibatis.binding.BindingException
调用dao接口是提示绑定失败。
解决方法:mybatis接口和xml文件不再同一个包内。
Mybatis使用
在mapper包新建接口
public interface FrontUserMapper { Frontuser getFrontUserByAccount(Frontuser frontuser); }
在mapper包中新建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="com.gmtx.front.mapper.FrontUserMapper"> <select id="getFrontUserByAccount" parameterType="com.gmtx.manage.model.Frontuser" resultType="com.gmtx.manage.model.Frontuser"> SELECT * FROM frontuser where userName =#{username} and pwd =#{pwd} select> mapper>
Mybatis (ParameterType) 如何传递多个不同类型的参数
方法一:不需要写parameterType参数
//mapper接口 public ListgetXXXBeanList(String xxId, String xxCode); //xml文件
方法二:基于注解(最简单)
//mapper接口 public ListgetXXXBeanList(@Param("id")String id, @Param("code")String code); //xml
方法三:Map封装
//mapper接口 public ListgetXXXBeanList(HashMap map); //xml
方法四:List封装
//mapper接口 public ListgetXXXBeanList(List list); //xml
Mybatis标签
定义sql语句
select标签
属性介绍:
- id :唯一的标识符.
- parameterType:传给此语句的参数的全路径名或别名 例:com.test.poso.User或user
- resultType :语句返回值类型或别名。注意,如果是集合,那么这里填写的是集合的泛型,而不是集合本身(resultType 与resultMap 不能并用)
<select id="selectByPrimaryKey"parameterType="Object"> select * from student where id=#{id} select>
insert标签
属性介绍:
- id :唯一的标识符
- parameterType:传给此语句的参数的全路径名或别名 例:com.test.poso.User
<insert id="insert" parameterType="Object"> insert into student
<trim prefix="(" suffix=")" suffixOverrides="," > <if test="name != null "> NAME, if> trim>
<trim prefix="values(" suffix=")" suffixOverrides="," > <if test="name != null "> #{name}, if> trim> insert>
delete标签
<delete id="deleteByPrimaryKey" parameterType="Object"> delete from student where id=#{id} delete>
update标签
同insert标签
resultMap标签
基本作用:
- 建立SQL查询结果字段与实体属性的映射关系信息
- 查询的结果集转换为java对象,方便进一步操作。
- 将结果集中的列与java对象中的属性对应起来并将值填充进去
标签说明:
主标签:
- id:该resultMap的标志
- type:返回值的类名,此例中返回Studnet类
子标签:
- id:用于设置主键字段与领域模型属性的映射关系,此处主键为ID,对应id。
- result:用于设置普通字段与领域模型属性的映射关系
<resultMap id="BaseResultMap" type="com.online.charge.platform.model.Student"> <id column="id" property="id" /> <result column="NAME" property="name" /> <result column="HOBBY" property="hobby" /> <result column="MAJOR" property="major" /> <result column="BIRTHDAY" property="birthday" /> <result column="AGE" property="age" /> resultMap> <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="Object"> select id,name,hobby,major,birthday,age from student where id=#{id} select>
动态sql拼接
动态sql拼接标签有if、foreach、choose
if 标签
if标签通常用于WHERE语句、UPDATE语句、INSERT语句中,通过判断参数值来决定是否使用某个查询条件、判断是否更新某一个字段、判断是否插入某个字段的值。
<if test="name != null and name != ''"> and NAME = #{name} if>
foreach 标签
属性介绍:
- collection:collection属性的值有三个分别是list、array、map三种,分别对应的参数类型为:List、数组、map集合。
- item :表示在迭代过程中每一个元素的别名
- index :表示在迭代过程中每次迭代到的位置(下标)
- open :前缀
- close :后缀
- separator :分隔符,表示迭代时每个元素之间以什么分隔
foreach标签主要用于构建in条件,可在sql中对集合进行迭代。也常用到批量删除、添加等操作中。
<select id="selectList" resultMap="BaseResultMap"> select name,hobby from student where id in <foreach item="item" index="index" collection="list" open="(" separator="," close=")"> #{item} foreach> select>
choose标签
有时候我们并不想应用所有的条件,而只是想从多个选项中选择一个。MyBatis提供了choose 元素,按顺序判断when中的条件出否成立,如果有一个成立,则choose结束。当choose中所有when的条件都不满则时,则执行 otherwise中的sql。类似于Java 的switch 语句,choose为switch,when为case,otherwise则为default。
if是与(and)的关系,而choose是或(or)的关系。
<select id="getStudentListChoose" parameterType="Student" resultMap="BaseResultMap"> SELECT * from STUDENT WHERE 1=1 <where> <choose> <when test="Name!=null and student!='' "> AND name LIKE CONCAT(CONCAT('%', #{student}),'%') when> <when test="hobby!= null and hobby!= '' "> AND hobby = #{hobby} when> <otherwise> AND AGE = 15 otherwise> choose> where> select>
格式化输出
where标签
当if标签较多时,这样的组合可能会导致错误。 如下:
<select id="getStudentListWhere" parameterType="Object" resultMap="BaseResultMap"> SELECT * from STUDENT <where> <if test="name!=null and name!='' "> NAME LIKE CONCAT(CONCAT('%', #{name}),'%') if> <if test="hobby!= null and hobby!= '' "> AND hobby = #{hobby} if> where> select>
set标签
<update id="updateStudent" parameterType="Object"> UPDATE STUDENT <set> <if test="name!=null and name!='' "> NAME = #{name}, if> <if test="hobby!=null and hobby!='' "> MAJOR = #{major}, if> <if test="hobby!=null and hobby!='' "> HOBBY = #{hobby} if> set> WHERE ID = #{id}; update>
trim标签
格式化输出,也可以通过trim标签设定或忽略前后缀来实现.
配置关联关系
collection标签
association标签
定义常量及引用
sql标签
<sql id="Base_Column_List"> ID,MAJOR,BIRTHDAY,AGE,NAME,HOBBY sql> <sql id="Example_Where_Clause"> where 1=1 <trim suffixOverrides=","> <if test="id != null and id !=''"> and id = #{id} if> <if test="major != null and major != ''"> and MAJOR = #{major} if> <if test="birthday != null "> and BIRTHDAY = #{birthday} if> <if test="age != null "> and AGE = #{age} if> <if test="name != null and name != ''"> and NAME = #{name} if> <if test="hobby != null and hobby != ''"> and HOBBY = #{hobby} if> <if test="sorting != null"> order by #{sorting} if> <if test="sort!= null and sort != '' "> order by ${sort} ${order} if> trim> sql>
include标签
<select id="selectAll" resultMap="BaseResultMap"> SELECT <include refid="Base_Column_List" /> FROM student <include refid="Example_Where_Clause" /> select> <select id="select" resultMap="BaseResultMap"> select * from ( select tt.*,rownum as rowno from ( SELECT <include refid="Base_Column_List" /> FROM student <include refid="Example_Where_Clause" /> ) tt <where> <if test="pageNum != null and rows != null"> and rownum <=]]>#{page}*#{rows} if> where> ) table_alias where table_alias.rowno>#{pageNum} select>
<delete id="deleteByEntity" parameterType="java.util.Map"> DELETE FROM student <include refid="Example_Where_Clause" /> delete>
xml中sql替换字符
& &
< <
> >
” "
‘ '
<= <=
>= >= = ]]>
<> != ]]>
Mybatis返回Map
一般用于报表,不想新建一个实体类,就用map包装返回值。
mapper.xml
java.util.HashMap代表返回的类型(每一行的数据类型),不是集合。
<select id="getAuthCount" resultType="java.util.HashMap"> select count(*) as authCount,DATE_FORMAT(time, '%Y-%m-%d') as date from log_jump where time > DATE_SUB( CONCAT(DATE_FORMAT(time, '%Y%m'),'01'),INTERVAL 7 day) group by DATE_FORMAT(time, '%Y-%m-%d'); select>
sql查询结果
mapper接口
/*把数据row封装成Map的数据List集合*/ List
Service层
/** * 获取认证次数时间分布 **/ public MapgetAuthCount() { /*返回的result为每一列的集合(echarts报表用)*/ Map resultMap = new HashMap (); /*日期集合*/ List dateList = new ArrayList(); /*次数集合*/ List jumpCountList = new ArrayList(); /*每个map都代表一行数据,Map中的键即使每一行的column名*/ List
前端返回结果
参考:
https://blog.csdn.net/m0_38054145/article/details/81906343