mybatis注解

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);