LineService.java:

查询分页：使用pagehelper

  1. package com.sgcc.hpc.dc.service;
  3. import com.github.pagehelper.PageHelper;
  4. import com.github.pagehelper.PageInfo;
  5. import com.sgcc.hpc.dc.filter.LineFilter;
  6. import com.sgcc.hpc.dc.mapper.LineMapper;
  7. import com.sgcc.hpc.dc.model.Line;
  8. import com.sgcc.hpc.dc.model.vo.LineVo;
  9. import org.springframework.beans.factory.annotation.Autowired;
  10. import org.springframework.stereotype.Service;
  12. import java.util.List;
  13. import java.util.Objects;
  15. /**
  16.  * @Author: 李孟帅
  17.  * @CreateTime: 2021/4/13 9:15
  18.  * @Description: TODO
  19.  */
  20. @Service
  21. public class LineService {
  23.     @Autowired
  24.     private LineMapper lineMapper;
  26.     /**
  27.      * @Author: 李孟帅
  28.      * @CreateTime: 2021/4/13 13:41
  29.      * @Description: 添加一条链路
  30.      */
  31.     public int addLine(Line line){
  32.         return lineMapper.addLine(line);
  33.     }
  35.     /**
  36.      * @Author: 李孟帅
  37.      * @CreateTime: 2021/4/13 13:44
  38.      * @Description: 删除一条链路
  39.      */
  40.     public int deleteLine(int id){
  41.         return lineMapper.deleteLine(id);
  42.     }
  44.     /**
  45.      * @Author: 李孟帅
  46.      * @CreateTime: 2021/4/13 12:57
  47.      * @Description: 更新链路信息（根据id）
  48.      */
  49.     public int updateLine(Line line){
  50.         return lineMapper.updateLine(line);
  51.     }
  53.     /**
  54.      * @Author: 李孟帅
  55.      * @CreateTime: 2021/4/13 9:16
  56.      * @Description: 根据当前节点名称和采集协议获取所有投运的链路信息（is_operation=1）
  57.      */
  58.     public List<Line> selectLineByNameAndProtocol(String nodeName, String protocolType) {
  59.         return lineMapper.selectLineByNameAndProtocol(nodeName, protocolType);
  60.     }
  62.     /**
  63.      * @Author: 李孟帅
  64.      * @CreateTime: 2021/4/13 12:55
  65.      * @Description: 查询所有的链路信息，根据查询条件筛选
  66.      */
  67.     public PageInfo<Line> selectLine(LineVo lineVo) {
  69.         Integer page = lineVo.getPage();
  70.         Integer size = lineVo.getSize();
  71.         Line line = lineVo.getLine();
  72.         if (Objects.isNull(page)){
  73.             page=1;
  74.         }
  75.         if (Objects.isNull(size)){
  76.             size=10;
  77.         }
  78.         PageHelper.startPage(page, size);
  79.         List<Line> lines = lineMapper.selectLine(line);
  81.         return new PageInfo<>(lines);
  83.     }
  87. }

LineMapper.java

  1. package com.sgcc.hpc.dc.mapper;
  3. import com.sgcc.hpc.dc.model.Line;
  4. import org.apache.ibatis.annotations.Mapper;
  5. import org.apache.ibatis.annotations.Select;
  6. import org.springframework.stereotype.Repository;
  8. import java.util.List;
  10. /**
  11.  * @Author: 李孟帅
  12.  * @CreateTime: 2021/4/13 9:08
  13.  * @Description: TODO
  14.  */
  15. @Repository
  16. @Mapper
  17. public interface LineMapper {
  20.     Integer addLine(Line line);
  22.     Integer deleteLine(int id);
  24.     Integer updateLine(Line line);
  26.     List<Line> selectLineByNameAndProtocol(String nodeName, String protocolType);
  28.     List<Line> selectLine(Line line);
  29. }

LineMapper.xml

包含增删改查

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  3. <mapper namespace="com.sgcc.hpc.dc.mapper.LineMapper">
  4.     <resultMap id="BaseResultMap" type="com.sgcc.hpc.dc.model.Line">
  5.         <result column="id" jdbcType="INTEGER" property="id"/>
  6.         <result column="protocol_type" jdbcType="VARCHAR" property="protocolType"/>
  7.         <result column="comm_add" jdbcType="VARCHAR" property="commAdd"/>
  8.         <result column="is_operation" jdbcType="VARCHAR" property="isOperation"/>
  9.         <result column="operation_time" jdbcType="TIMESTAMP" property="operationTime"/>
  10.         <result column="node_name" jdbcType="VARCHAR" property="nodeName"/>
  11.         <result column="change_time" jdbcType="TIMESTAMP" property="changeTime"/>
  12.         <result column="fes_time" jdbcType="TIMESTAMP" property="fesTime"/>
  13.         <result column="fes_type" jdbcType="INTEGER" property="fesType"/>
  14.         <result column="station_ip_1" jdbcType="VARCHAR" property="stationIp1"/>
  15.         <result column="port_1" jdbcType="VARCHAR" property="port1"/>
  16.         <result column="station_ip_2" jdbcType="VARCHAR" property="stationIp2"/>
  17.         <result column="port_2" jdbcType="VARCHAR" property="port2"/>
  18.         <result column="station_code" jdbcType="VARCHAR" property="stationCode"/>
  19.         <result column="fes_period" jdbcType="INTEGER" property="fesPeriod"/>
  20.         <result column="fes_nodeId" jdbcType="VARCHAR" property="fesNodeId"/>
  21.         <result column="need_cer" jdbcType="INTEGER" property="needCer"/>
  22.         <result column="cdkey1" jdbcType="VARCHAR" property="cdkey1"/>
  23.         <result column="cdkey2" jdbcType="VARCHAR" property="cdkey2"/>
  24.     </resultMap>
  26.     <insert id="addLine" parameterType="com.sgcc.hpc.dc.model.Line">
  27.         INSERT INTO hpc_manage_line(protocol_type,comm_add,is_operation,operation_time,node_name,change_time,fes_time,fes_type,
  28.         station_ip_1,port_1,station_ip_2,port_2,station_code,fes_period,fes_nodeId,need_cer,cdkey1,cdkey2)
  29.         VALUES(#{protocolType},#{commAdd},#{isOperation},#{operationTime},#{nodeName},#{changeTime},#{fesTime},#{fesType},
  30.         #{stationIp1},#{port1},#{stationIp2},#{port2},#{stationCode},#{fesPeriod},#{fesNodeId},#{needCer},#{cdkey1},#{cdkey2})
  31.     </insert>
  33.     <insert id="deleteLine">
  34.         DELETE FROM hpc_manage_line WHERE id=#{id}
  35.     </insert>
  37.     <update id="updateLine" parameterType="com.sgcc.hpc.dc.model.Line">
  38.         UPDATE hpc_manage_line
  39.         <set>
  40.             <if test="protocolType != null">protocol_type=#{protocolType},</if>
  41.             <if test="commAdd != null">comm_add=#{commAdd},</if>
  42.             <if test="isOperation != null">is_operation=#{isOperation},</if>
  43.             <if test="operationTime != null">operation_time=#{operationTime},</if>
  44.             <if test="isOperation != null">is_operation=#{isOperation},</if>
  45.             <if test="nodeName != null">node_name=#{nodeName},</if>
  46.             <if test="changeTime != null">change_time=#{changeTime},</if>
  47.             <if test="fesTime != null">fes_time=#{fesTime},</if>
  48.             <if test="fesType != null">fes_type=#{fesType},</if>
  49.             <if test="stationIp1 != null">station_ip_1=#{stationIp1},</if>
  50.             <if test="port1 != null">port_1=#{port1},</if>
  51.             <if test="stationIp2 != null">station_ip_2=#{stationIp2},</if>
  52.             <if test="port2 != null">port_2=#{port2},</if>
  53.             <if test="stationCode != null">station_code=#{stationCode},</if>
  54.             <if test="fesPeriod != null">fes_period=#{fesPeriod},</if>
  55.             <if test="fesNodeId != null">fes_nodeId=#{fesNodeId},</if>
  56.             <if test="needCer != null">need_cer=#{needCer},</if>
  57.             <if test="cdkey1 != null">cdkey1=#{cdkey1},</if>
  58.             <if test="cdkey2 != null">cdkey2=#{cdkey2},</if>
  59.         </set>
  60.         WHERE id=#{id}
  61.     </update>
  63.     <select id="selectLineByNameAndProtocol" resultMap="BaseResultMap">
  64.         SELECT * FROM hpc_manage_line WHERE is_operation = 1 AND node_name = #{nodeName} AND protocol_type = #{protocolType}
  65.     </select>
  67.     <select id="selectLine" parameterType="com.sgcc.hpc.dc.model.Line" resultMap="BaseResultMap">
  68.         SELECT
  69.         id,protocol_type,comm_add,is_operation,operation_time,node_name,change_time,fes_time,fes_type,station_ip_1,port_1,station_ip_2,port_2,station_code,
  70.         fes_period,fes_nodeId,need_cer,cdkey1,cdkey2
  71.         FROM hpc_manage_line
  72.         <where>
  73.             <if test="protocolType !=null and protocolType != ''">
  74.                 and protocol_type LIKE CONCAT('%',#{protocolType},'%' )
  75.             </if>
  77.             <if test="isOperation != null and isOperation != ''">
  78.                 and is_operation = #{isOperation}
  79.             </if>
  80.             <if test="nodeName != null">
  81.                 and node_name LIKE CONCAT('%',#{nodeName},'%' )
  82.             </if>
  83.             <if test="operationTime != null ">
  84.                 and operation_time &lt;= #{operationTime}
  85.             </if>
  87.             <if test="stationCode != null and stationCode !=''">
  88.                 and station_code LIKE CONCAT('%',#{stationCode},'%' )
  89.             </if>
  91.           <if test="stationList !=null">
  92.                 AND station_code IN
  93.                 <foreach collection="stationList" item="item" index="index" open="(" separator="," close=")">
  94.                     #{item}
  95.                 </foreach>
  96.             </if>
  97.         </where>
  98.         ORDER BY operation_time DESC
  99.     </select>
  104. </mapper>

application.yml

  1. mybatis:
  2.   mapper-locations: classpath:mapper/*.xml
  3.   type-aliases-package: com.sgcc.hpc.dc.mapper
  4.   config-location: classpath:/mybatis-config.xml
  5. pagehelper:
  6.   helperDialect: mysql
  7.   reasonable: true
  8.   supportMethodsArguments: true
  9.   params: count=countSql

mybatis-config.xml

  1. <?xml version="1.0" encoding="UTF-8"?>  
  2. <!DOCTYPE configuration  
  3.      PUBLIC "-//mybatis.org//DTD Config 3.0//EN"  
  4.      "http://mybatis.org/dtd/mybatis-3-config.dtd">
  5. <configuration>
  6.     <settings>
  7.         <!-- 使全局的映射器启用或禁用缓存。 -->
  8.         <setting name="cacheEnabled" value="true" />
  9.         <!-- 全局启用或禁用延迟加载。当禁用时，所有关联对象都会即时加载。 -->
  10.         <setting name="lazyLoadingEnabled" value="true" />
  11.         <!-- 当启用时，有延迟加载属性的对象在被调用时将会完全加载任意属性。否则，每种属性将会按需要加载。 -->        
  12.         <setting name="aggressiveLazyLoading" value="true"/>
  13.         <!-- 是否允许单条sql 返回多个数据集  (取决于驱动的兼容性) default:true -->
  14.         <setting name="multipleResultSetsEnabled" value="true" />
  15.         <!-- 是否可以使用列的别名 (取决于驱动的兼容性) default:true -->
  16.         <setting name="useColumnLabel" value="true" />
  17.         <!-- 允许JDBC 生成主键。需要驱动器支持。如果设为了true，这个设置将强制使用被生成的主键，有一些驱动器不兼容不过仍然可以执行。  default:false  -->
  18.         <setting name="useGeneratedKeys" value="false" />
  19.         <!-- 指定 MyBatis 如何自动映射 数据基表的列 NONE：不隐射　PARTIAL:部分  FULL:全部  -->
  20.         <setting name="autoMappingBehavior" value="PARTIAL" />
  21.         <!-- 这是默认的执行类型  （SIMPLE: 简单； REUSE: 执行器可能重复使用prepared statements语句；BATCH: 执行器可以重复执行语句和批量更新）  -->
  22.         <setting name="defaultExecutorType" value="SIMPLE" />
  24.         <setting name="defaultStatementTimeout" value="25" />
  26.         <setting name="defaultFetchSize" value="100" />
  28.         <setting name="safeRowBoundsEnabled" value="false" />
  29.         <!-- 使用驼峰命名法转换字段。 -->
  30.         <setting name="mapUnderscoreToCamelCase" value="true" />
  31.         <!-- 设置本地缓存范围 session:就会有数据的共享  statement:语句范围 (这样就不会有数据的共享 ) defalut:session -->
  32.         <setting name="localCacheScope" value="SESSION" />
  33.         <!-- 默认为OTHER,为了解决oracle插入null报错的问题要设置为NULL -->
  34.         <setting name="jdbcTypeForNull" value="NULL" />
  35.         <setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString" />
  36.         <!-- 打印查询语句 -->
  37.         <setting name="logImpl" value="STDOUT_LOGGING" />
  38.     </settings>
  40. </configuration>