ibatis

ibatis基本操作

import java.util.List;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.annotations.Param;

@Mapper
public interface FileMappigMapper {
  String selectSql = "SELECT URL, DOWNLOAD_SIZE,"
      + "  DATE_FORMAT(EXPIRES_TIME, '%Y-%m-%d %H:%i:%s') AS EXPIRES_TIME,"
      + "  FROM FILE_MAPPING ";

  @Select(selectSql + " WHERE EXPIRES_TIME <= #{expiresTime}")
  List<FileInfo> listFileMappingBefore(@Param("expiresTime") String expiresTime);

  @Insert("INSERT INTO FILE_MAPPING(URL, DOWNLOAD_SIZE, DOWNLOAD_TIME) VALUES (\n"
      + "    #{url}, #{downloadSize}, #{downloadTime})")
  int addFileMapping(FileInfo file);

  @Update("UPDATE FILE_MAPPING SET EXPIRES_TIME=#{expiresTime}, UPDATED_TIME=CURRENT_TIMESTAMP WHERE URL=#{url}")
  int updateFileExpireTime(@Param("url") String url, @Param("expiresTime") String expiresTime);

  @Delete("DELETE FROM FILE_MAPPING WHERE URL=#{url}")
  int deleteFileMapping(String url);
}

JSON字段提取函数json_extract 及 sql集合操作示例

 @Select("SELECT SUM(json_extract(CONTENT_JSON, '$.bitrate')) from SESSION "
      + "  WHERE CATEGORY='abc' and SERVICE_ID in (${serviceIdList})")
  public Integer getTotalRequestBitrate(@Param("serviceIdList") String serviceIdList);

将Java非基本类型映射到数据库的某个字符串字段中存储

将复杂Java类转换为JSON字符串,用于存储到数据库中

import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface SessionMapper {  
   @Update("UPDATE SESSION SET "
      + "  STATUS_DETAIL=#{detail,typeHandler=io.example.scheduler.mapper.JsonTypeHandler},"
      + "  UPDATED_TIME = CURRENT_TIMESTAMP WHERE ID= #{id}")
  public int updateSession(@Param("id") String id, @Param("detail") StatusDetail detail);
  
  @Select("SELECT * FROM SESSION")
  @ResultMap("jobResult")
  public List<Job> listJob();
}

相应的在读取数据库时，需要将对应字符串转换为该复杂类，因此需要定义映射文件

<?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.example.scheduler.mapper.SessionMapper">
  <resultMap id="jobResult" type="com.example.scheduler.Job" autoMapping="true">
    <result property="statusDetail" column="STATUS_DETAIL"
      javaType=""com.example.scheduler.StatusDetail"
      typeHandler=""com.example.scheduler.mapper.JsonTypeHandler"/>
  </resultMap>
</mapper>

注意“将复杂Java类转换为JSON字符串”时，需要定义TypeHandler的子类，用于将数据转换为字符串

package com.example.scheduler.mapper;

import com.fasterxml.jackson.core.JsonProcessingException;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandler;

public class JsonTypeHandler<T> implements TypeHandler<T>  {
  private Class<T> dataClass;

  public JsonTypeHandler(Class<T> dataClass) {
    if (dataClass == null)
      throw new IllegalArgumentException("should provide typeClass");
    this.dataClass = dataClass;
  }

  @Override
  public void setParameter(PreparedStatement ps, int i, T parameter, JdbcType jdbcType) throws SQLException {
    try {
      ps.setString(i, parameter== null ? null : Utils.writerSnake.writeValueAsString(parameter));
    } catch (JsonProcessingException e) {
      ps.setString(i, null);
    }
  }

  @Override
  public T getResult(ResultSet rs, String columnName) throws SQLException {
    return parseString(rs.getString(columnName));
  }

  @Override
  public T getResult(ResultSet rs, int columnIndex) throws SQLException {
    return parseString(rs.getString(columnIndex));
  }

  @Override
  public T getResult(CallableStatement cs, int columnIndex) throws SQLException {
    return parseString(cs.getString(columnIndex));
  }

  private T parseString(String value) {
    if (value == null || value.isBlank())
      return null;
    try {

ObjectMapper mapperSnake = new ObjectMapper().setSerializationInclusion(Include.NON_NULL)

.setPropertyNamingStrategy(PropertyNamingStrategies.SNAKE_CASE)

.configure(DeserializationFeature.FAIL_ON_UNKNOWN_PROPERTIES, false)

return mapperSnake.readValue(value, dataClass);
    } catch (JsonProcessingException e) {
      throw new RuntimeException(e);
    }
  }
}

将Java非基本类型class展开存储为数据表的多个字段

当属性为非Java基本类型class，但想展开存储在数据库时，比如如下类

@JsonNaming(PropertyNamingStrategies.SnakeCaseStrategy.class)
public class Schedule {
  public Recurrence recurrence;
  public ExtraInfo extraInfo = new ExtraInfo();
}

public class Recurrence {
  public Integer type；  
  @JsonInclude(value = JsonInclude.Include.CUSTOM, valueFilter = JsonIgnoreNumberFilter.class)
  public int dailyInterval = 0;
  @JsonProperty("week_days")
  public List<String> weekDays; 
  public int monthlyType = 0;
}

想展开存储为如下数据库字段

CREATE TABLE SCHEDULE (
    RECURRENCE_TYPE  int(11)               DEFAULT NULL,
    DAILY_INTERVAL   int(11)               DEFAULT NULL,
    WEEK_DAYS        varchar(128)          DEFAULT NULL,
    MONTHLY_TYPE     int(11)               DEFAULT NULL,
) DEFAULT CHARSET = utf8;

那么当读取数据库时，需要将相关字段合并为原来的属性类，需要用到resultMap和association

<?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.example.scheduler.mapper.ScheduleMapper">
  <resultMap id="scheduleResult" type="com.example.scheduler.model.Schedule" autoMapping="true">
    <result property="extraInfo" column="EXTRA_INFO_JSON"
      javaType="com.example.scheduler.ExtraInfo"
      typeHandler="com.example.scheduler.mapper.JsonTypeHandler"/>
    <association property="recurrence" notNullColumn="RECURRENCE_TYPE"
      javaType="com.example.scheduler.model.Recurrence">
      <result property="type" column="RECURRENCE_TYPE"/>
      <result property="dailyInterval" column="DAILY_INTERVAL"/>
      <result property="monthlyType" column="MONTHLY_TYPE"/>
      <result property="weekDays" column="WEEK_DAYS"
         typeHandler="com.example.scheduler.mapper.StringListTypeHandler"/>
    </association>
  </resultMap>
</mapper>

注意“result” 和“association”的区别：

result定义数据库中的某个字段与对应属性的类的定义

association将数据库中的多个字段合并为某个类的一部分属性

Annotation中包含<script> 和 <where>等

import java.util.List;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.ResultMap;
import org.apache.ibatis.annotations.Select;

@Mapper
public interface ScheduleMapper {
  @Select({"<script>",
      "SELECT * from SCHEDULE",
      "  <where> ",
      "    <if test='startDate != null'> START_DATE >= #{startDate} </if>",
      "    <if test='endDate   != null'> AND  #{endDate} >= END_DATE </if>",
      "  </where> ",
      "</script>"})
  @ResultMap("scheduleResult")
  public List<Schedule> listSchedules(@Param("startDate") Date startDate, @Param("endDate") Date endDate);

  @Select({"<script>", selectSql, " WHERE ID= #{id}", "</script>"})
  @ResultMap("scheduleResult")
  public Schedule getSchedule(String id);

  /**
   * reference to https://mybatis.org/mybatis-3/dynamic-sql.html
   */
  @Insert({"<script> INSERT INTO SCHEDULE(ID, RECURRENCE_TYPE,WEEK_DAYS) VALUES (#{id}, "
    + "  <if test='recurrence != null'>"
    + "   #{recurrence.type}, "
    + "   #{recurrence.weekDays,typeHandler=com.example.scheduler.mapper.StringListTypeHandler}"
    + "  </if>"
    + "  <if test='recurrence == null'> null, null</if>"
    + " )",
    "</script>"})
  public int addSchedule(Schedule sch);
}

posted @ 2025-02-19 12:21 cyj2024 阅读(14) 评论(0) 收藏举报

刷新页面返回顶部

cyj1

ibatis

ibatis基本操作

将Java非基本类型映射到数据库的某个字符串字段中存储

将Java非基本类型class展开存储为数据表的多个字段

Annotation中包含<script> 和 <where>等

公告