mybatis中使用注解实现与数据库交互：
1.查询操作：
@Select("SELECT * FROM t_instance_operation WHERE instance_id = #{instanceId}")
@Results(id = "instOpResultMap", { //指定映射配置的id,可以被其他的方法引用
@Result(property = "instanceId", column = "instance_id"),
@Result(property = "operationCode", column = "operation_code"),
@Result(property = "status", column = "status"),
@Result(property = "planningTime", column = "planning_time"),
@Result(property = "taskId", column = "task_id"),
@Result(property = "operationResult", column = "operation_result"),
@Result(property = "resultInfo", column = "result_info"),
@Result(property = "extInfo", column = "ext_info"),
@Result(property = "userName", column = "user_name")
})
List<InstanceOperation> getInstOpByInstId(@Param("instanceId")int instanceId);
1)@Param
参数映射
2)@Select
编写查询语句
3)#{instanceId}
获取方法的参数，与@Param("instanceId")中的instanceId对应
4)@Results
配置查询结果与返回值对象的映射关系
5)@ResultMap(value = {"instOpResultMap"})
引用已经存在的映射配置，这里表示引用已经存在的id为instOpResultMap的映射配置，如：
@Select(" Select * from t_instance_operation where instance_id =#{instanceId} and status <4")
@ResultMap(value = {"instOpResultMap"})
List<InstanceOperation> getOperatingInstOpt(@Param("instanceId") int instanceId);
6)@selectProvider
两个属性：
type：指定获取sql语句的类的.class
method:指定获取sql语句的方法
如：
@SelectProvider(type = InstanceOperationProvider.class, method = "queryLastStatus")
@Results(id = "instOpResultMap", value = {
@Result(property = "instanceId", column = "instance_id"),
@Result(property = "operationCode", column = "operation_code"),
@Result(property = "status", column = "status"),
@Result(property = "planningTime", column = "planning_time"),
@Result(property = "taskId", column = "task_id"),
@Result(property = "operationResult", column = "operation_result"),
@Result(property = "resultInfo", column = "result_info"),
@Result(property = "extInfo", column = "ext_info"),
@Result(property = "userName", column = "user_name")
})
InstanceOperation getLastInstOp(@Param("instanceId") int instanceId, @Param("operationCode") String operationCode);
public class InstanceOperationProvider { //获取sql语句的类
public String queryLastStatus(@Param("instanceId") int instanceId, @Param("operationCode") String operationCode) { //获取sql语句的方法，返回String
boolean withOpCode = operationCode != null && !"".equals(operationCode.trim());
return getSql4LastOp(withOpCode)
+ " AND t1.instance_id = #{instanceId}"
+ (withOpCode ? " AND t1.operation_code = #{operationCode}" : ""
+ " ORDER BY t1.id DESC LIMIT 1");
}
5)可以在@select中写动态sql:
@Select("<script> select id, project_code, area_id, area_name, levels, project_instance_id, project_path, user_account, "
+ "status, create_time, remark ,operation_code, capacity, params "
+ "from t_plan_task where "
+ "<if test='taskIds != null and !taskIds.isEmpty()'>"
+ " id in "
+ " <foreach collection='taskIds' index='' item='taskId' open='(' separator=',' close=')'>"
+ " #{taskId}"
+ " </foreach>"
+ "</if>"
+ "<if test='taskIds == null or taskIds.isEmpty()'>"
+ " 1!=1"
+ "</if>"
+ "</script>")
@Results({
@Result(property="id", column="id"),
@Result(property="projectCode", column="project_code"),
@Result(property="areaId", column="area_id"),
@Result(property="areaName", column="area_name"),
@Result(property="levels", column="levels", typeHandler=NetLevelHandler.class),
@Result(property="projectInstanceId", column="project_instance_id"),
@Result(property="projectPath", column="project_path"),
@Result(property="userAccount", column="user_account"),
@Result(property="status", column="status", typeHandler=EnumTaskStatusHandler.class),
@Result(property="createTime", column="create_time"),
@Result(property="remark", column="remark"),
@Result(property="operationCode", column="operation_code"),
@Result(property="capacity", column="capacity"),
@Result(property="params", column="params")
})
@ResultType(List.class)
public List<Task> queryPlanTaskByIds(@Param("taskIds") List<Integer> taskIds);
2.插入操作：
@Insert("insert into t_plan_task (project_code, area_id, area_name,levels, project_instance_id, project_path, "
+ "user_account,user_name, status, "
+ "create_time, remark,operation_code, capacity, params) values ("
+ "#{projectCode},"
+ "#{areaId},"
+ "#{areaName},"
+ "#{levels, typeHandler=smartodn.platform.planschedule.service.constant.NetLevelHandler},"
+ "#{projectInstanceId},"
+ "#{projectPath},"
+ "#{userAccount},"
+ "#{userName},"
+ "#{status, typeHandler=smartodn.platform.planschedule.service.constant.EnumTaskStatusHandler},"
+ "#{createTime},"
+ "#{remark},"
+ "#{operationCode},"
+ "#{capacity},"
+ "#{params}"
+ ")")
@Options(useGeneratedKeys=true, keyProperty="id", keyColumn="id") //应该是实现主键返回
public int insertPlanTask(Task task);