mySql :
CREATE TABLE `test` (
`t_id` int(11) NOT NULL AUTO_INCREMENT,
`t_name` varchar(200) NOT NULL,
`create_date` datetime NOT NULL,
`update_date` datetime DEFAULT NULL,
PRIMARY KEY (`t_id`)
)
Model:
//@Data 在lombok包下
@Data
public class Test implements Serializable {
private static final long serialVersionUID = 1L;
private Integer tId;
private String tName;
private Date createDate;
@DateTimeFormat(pattern="yyyy-MM-dd") //用户将表单传来的日期进行格式化
private Date updateDate;
}
Mapper:
@Mapper
public interface TestMapper {
@Select("<script>"
+ "select t.t_id,t.t_name from test t where 1=1"
+ "<if test=\"tId != null and tId != '' \"> and t.t_id=#{tId}</if>"
+ "</script>")
@Results({
@Result(column = "t_id", property = "tId"),
@Result(column = "t_name", property = "tName")
})
public List<Test> query(@Param("tId") String tId)
//t_id需要是自增长的
@Insert("<script>INSERT INTO test (t_name, create_date, update_date) VALUES"
+ "(#{test.tName},now(),now())"
+ "</script>")
@SelectKey(before = false, keyProperty = "test.tId", resultType = Integer.class,
statementType = StatementType.STATEMENT, statement = "SELECT LAST_INSERT_ID() AS t_id")
public int insert(@Param("test") Test test);
@Update("<script>UPDATE test set update_date = "
+"<choose> <when test=\"test.updateDate != null \"> #{test.updateDate}</when>"
+"<otherwise> now()</otherwise>"
+ "</choose>"
+ " where t_id=#{test.tId}</script>")
public int update(@Param("test") Test test);
@Delete("delete FROM test where t_id = #{tId}")
public int delete(@Param("tId") String tId);
//批量插入,不支持批量更新,# 与 *的区别,#号是以?占位,*直接替换成相应的内容了
@Insert("<script>" + "insert into test (`t_name`, `create_date`, `update_date`) VALUES "
+ "<foreach collection =\"testList\" item=\"testModel\" index= \"index\" separator =\",\"> "
+ "(<![CDATA[ #{testModel.tName},now(),now()]])>"
+ "</foreach > "
+ "</script>")
@SelectKey(before = false, keyProperty = "tId", resultType = int.class,
statementType = StatementType.STATEMENT, statement = "SELECT LAST_INSERT_ID() AS t_id")
public int insertList(@Param("testList") List<Test> testList);
}
如果通过<script> 这种形式感觉不太清晰,完全可以使用@SelectProvider,@InsertProvider,@UpdateProvider ,@DeleteProvider
provider只是负责返回SQL,但是要注意@Param的用法。