Springboot使用注解法相关对数据库的操作

选择逆序排列的第一个

column代表在数据中的名称，property代表在类中的名称。

@Select("select * from ${stationName} order by gpstime desc limit 1")
    @Results({
            @Result(column = "gpstime", property = "gpsTime"),
            @Result(column = "hour", property = "hour"),
            @Result(column = "minute", property = "minute"),
            @Result(column = "second", property = "second"),
            @Result(column = "x", property = "x"),
            @Result(column = "y", property = "y"),
            @Result(column = "z", property = "z"),
            @Result(column = "flag", property = "flag")
    })
    StationModel getStationMsgNew(@Param("stationName") String stationName);

在数据库表中得到名字相同的表数量

sqlite_master：表示数据库表的名称，station%：类似于station1，station2……

@Select("select count(*) from sqlite_master where type='table' and name like 'station%'")
    int getStationNum();

从数据库中随机获取一条数据

limit1：表示取随即后的一条数据。

    @Select("SELECT * FROM ${stationName} where flag = 1 ORDER BY RANDOM() limit 1")
    @Results({
            @Result(column = "gpstime", property = "gpsTime"),
            @Result(column = "hour", property = "hour"),
            @Result(column = "minute", property = "minute"),
            @Result(column = "second", property = "second"),
            @Result(column = "x", property = "x"),
            @Result(column = "y", property = "y"),
            @Result(column = "z", property = "z"),
            @Result(column = "flag", property = "flag")
    })
    StationModel getDataRand(@Param("stationName") String stationName);

获取数据库中第二新的数据

即倒序排列后的第二条数据。

    @Select("select * from ${stationName} where flag='1' order by gpstime desc limit 1,1")
    @Results({
            @Result(column = "gpstime", property = "gpsTime"),
            @Result(column = "hour", property = "hour"),
            @Result(column = "minute", property = "minute"),
            @Result(column = "second", property = "second"),
            @Result(column = "x", property = "x"),
            @Result(column = "y", property = "y"),
            @Result(column = "z", property = "z"),
            @Result(column = "flag", property = "flag")
    })
    StationModel getStationSecondByFlag(@Param("stationName") String stationName);