增@Insert

@Insert("INSERT INTO `user`(`user_name`, `password`, `phone`, `email`, `gender`,`icon`, `number`, `post_id`, `post_name`) "
        + "values(#{userName}, #{password}, #{phone}, #{email}, #{gender}, #{icon}, #{number}, #{postId}, #{postName});")
void insert(UserEntity customerUserEntity);

删@Delete

@Delete("delete from `user` where user_name = #{userName}")
void deleteUserByUserName(String userName);

查@Select

普通查询

//查全表
@Select("SELECT * FROM `alarm_records`")
List<AlarmRecordEntity> getAllAlarmRecords();

//查某列（某字段）
@Select("SELECT name FROM `user`")
List<AlarmRecordEntity> getAllAlarmRecords();

//根据某字段查所有符合条件的
@Select("SELECT * from `user` WHERE number = #{number};")
UserEntity queryUserByNumber(@Param("number") String number);

//多条件查询
@Select("SELECT * from `user` WHERE number = #{number} and name = #{name};")
UserEntity queryUserByNumber(@Param("number") String number);

重复的只取一次

遍历某个字段，重复的只取一个（时间戳最近的一个）

把company换成*，即取出整条记录

示例为alarm_records_company表中，company重复的按alarm_time时间最近的取值。

mapper

@Select(" select classtab.company FROM (SELECT company, MAX(alarm_time) alarm_time FROM alarm_records_company GROUP BY company) " +
        "tmp LEFT JOIN alarm_records_company classtab ON " +
        "classtab.company = tmp.company AND classtab.alarm_time = tmp.alarm_time;")
List<String> getAllCompany();

条件查询@SelectProvider

适用不能提前预知哪个条件来查询

前端返回需要查询的参数，AlarmService.class层做逻辑判断，不为空的参数即为要查询的

mapper

@SelectProvider(type = AlarmService.class, method = "selectRecordsByDynamicSql")
List<AlarmRecordEntity> getAlarmRecordsByFeature(HashMap<String,String> features);

AlarmService.class

public String selectRecordsByDynamicSql(HashMap<String,String> features){
    String sql = new SQL(){
        {
            SELECT("*");
            FROM("alarm_records");
            if (!ObjectUtil.isEmptyString(features.get("monitor"))){
                WHERE("monitor = '"+features.get("monitor")+"'");
            }
            if (!ObjectUtil.isEmptyString(features.get("monitorClass"))){
                WHERE("monitor_class = '"+features.get("monitorClass")+"'");
            }
            if (!ObjectUtil.isEmptyString(features.get("monitorValue"))){
                WHERE("monitor_value = '"+features.get("monitorValue")+"'");
            }
            if (!ObjectUtil.isEmptyString(features.get("startTime"))){
                WHERE("alarm_time >= '"+features.get("startTime")+"'");
            }
            if (!ObjectUtil.isEmptyString(features.get("endTime"))){
                WHERE("alarm_time <= '"+features.get("endTime")+"'");
            }
        }
    }.toString();
    return sql;
}

改@Update

```sql
//根据某个字段更新该记录的另一个字段
@Update(“UPDATE user SET icon=#{icon} WHERE user_name=#{userName};”)
void updateIconByUserName(@Param(“userName”) String userName, @Param(“icon”) String icon);

//在整条记录上修改各个字段（id主键不会变）
@Update(“UPDATE alarm_settings “ +
“SET “ +
“monitor_class = #{monitorClass}, “ +
“monitor_value = #{monitorValue}, “ +
“lower_limit = #{lowerLimit}, “ +
“upper_limit = #{upperLimit}, “ +
“message = #{message} “ +
“WHERE id=#{id}”)
public int updateAlarmSetting(AlarmSettingsEntity alarmSettingsEntity);