jpa mysql视图查询

你的名字 2022-01-27 05:34 280阅读 0赞

> 通过注解我们都自动生成表，再新建repository，可以查询表数据；这里我们介绍mysql的视图查询，关联多个表

*  先新建视图（如果先新建视图，可能注解会帮你生成表，而不是我们想要的视图）
 *  视图这里简单展示下，不做细讲,视图语句如下：
    
        select `event`.`id` AS `EVENTID`,`event`.`code_year` AS `CODE_YEAR`,`event`.`code_num` AS `CODE_NUM`,`event`.`event_resource` AS `EVENT_RESOURCE`,`event`.`event_location` AS `LOCATION`,`event`.`islock` AS `ISCLOCK`,`event`.`clock_man` AS `CLOCKMAN_ID`,`clockm`.`user_name` AS `CLOCKMAN`,`evt_flow`.`limit_time` AS `TACHE_LIMIT_TIME`,`evt_flow`.`recieve_role` AS `FLOW_RECEIVE_ROLE_ID`,`evt_flow`.`receive_dept` AS `FLOW_RECELVE_DEPT_ID` from ((`evt_t_event` `event` join `evt_t_accept_flow` `evt_flow` on((`event`.`id` = `evt_flow`.`event_id`))) join `pmi_t_user` `clockm` on((`event`.`clock_man` = `clockm`.`id`))) where (`event`.`db_status` > 0)
 *  创建java实体类，对应视图
    
        @Entity
          @Table(name = "v_todo_event_list")
          public class ToDoEventList implements Serializable { 
          
              @Id //这个是必须的，不然启动报错
          
             private long EVENTID;
             private String CODE_YEAR;
             private int  CODE_NUM;
             private int EVENT_RESOURCE;
             private String LOCATION;
             private int ISCLOCK;
             private long CLOCKMAN_ID;
             private String CLOCKMAN;
             private int TACHE_LIMIT_TIME;
             private long FLOW_RECEIVE_ROLE_ID;
             private long FLOW_RECELVE_DEPT_ID;
            
            。。。。。。set get
        
               @Override
          	    public String toString() { 
          	        return "ToDoEventList{" +
          	                "EVENTID=" + EVENTID +
          	                ", CODE_YEAR='" + CODE_YEAR + '\'' +
          	                ", CODE_NUM=" + CODE_NUM +
          	                ", EVENT_RESOURCE=" + EVENT_RESOURCE +
          	                ", LOCATION='" + LOCATION + '\'' +
          	                ", ISCLOCK=" + ISCLOCK +
          	                ", CLOCKMAN_ID=" + CLOCKMAN_ID +
          	                ", CLOCKMAN='" + CLOCKMAN + '\'' +
          	                ", TACHE_LIMIT_TIME=" + TACHE_LIMIT_TIME +
          	                ", FLOW_RECEIVE_ROLE_ID=" + FLOW_RECEIVE_ROLE_ID +
          	                ", FLOW_RECELVE_DEPT_ID=" + FLOW_RECELVE_DEPT_ID +
          	                '}';
          	    }
           }
 *  创建Repository类
    
        public interface IToDoEventRepository extends JpaRepository<ToDoEventList,Long> , JpaSpecificationExecutor<ToDoEventList> { 
            ToDoEventList findById(long id);
          }
 *  方法调用：
    
        @Autowired
          IToDoEventRepository toDoEventRepository;
    
        ToDoEventList eventList = toDoEventRepository.findById(1);
          LOGGER.info(eventList);
 *  打印结果：  
    ![在这里插入图片描述][2019052515183481.png]  
    ![在这里插入图片描述][20190525151948961.png]

[2019052515183481.png]: /images/20220127/3e303c85e9df419ba67d04d754f83425.png
[20190525151948961.png]: /images/20220127/91b5ebd9e9c44632b0e83eb986dce7de.png