Home spring boot jpa trying to model an existing database
Reply: 0

spring boot jpa trying to model an existing database

user14428
1#
user14428 Published in July 21, 2018, 8:52 pm

I have a database that I'm trying to model. There are 3 tables, table cycles with a one to many to table cyclesensors and a one to many to table sensors. I'm trying to get the latest cycle data and this is the query that I found to work:

 Select c.id, cs.cycles, s.* from BizWareUser.Sensor s Inner Join BizWareUser.cyclesensor cs on s.SensorDataCol = cs.id
    Inner Join BizWareUser.cycles c on c.id = cs.cycles
    Where c.id IN (Select max(id) from BizWareUser.cycles cs)

When I use it in hql, I receive this error:

Could not write JSON: HHH000142:

I would like to have the data modeled in such a way for react to parse it properly:

cycles{
   cyclesensors : [
      cyclesensor:{
        sensors : [
          sensor : {
          }
        ]
      }
   ]
}

I'm very new to spring boot but I was hoping to do this without a explicitly writing the query. Below are my models:

Cycles:

  @Entity
    @Data
    @Table(name="cycles", catalog="bizware_asp_test", schema="BizWareUser")
    public class Cycles {
        @Id
        @GeneratedValue(strategy= GenerationType.AUTO)
        @Column(name="id")
        private Long id;
        @Column(name="event_time")
        private Timestamp event_time;
        @Column(name="recipe")
        private Short recipe;
        @Column(name="cycle_duration")
        private Short cycle_duration;
        @Column(name="gondola")
        private Short gondola;
        @Column(name="part_index")
        private Short part_index;
        @Column(name="part_name")
        private String part_name;
        @Column(name="shift_count")
        private Short shift_count;
        @Column(name="pour_time")
        private Short pour_time;
        @Column(name="shift2")
        private String shift2;
        @Column(name="iron_temp")
        private Short iron_temp;
        @Column(name="alarm")
        private Integer alarm;
        @Column(name="pour_weight")
        private Short pour_weight;
        @Column(name="target_weight")
        private Short target_weight;
        @Column(name="pour_operator_id")
        private String pour_operator_id;
        @Column(name="cluster_weight")
        private Short cluster_weight;
        @Column(name="furnace")
        private Character furnace;
        @Column(name="heat")
        private Character heat;
        @Column(name="tap")
        private Character tap;
        @Column(name="recipe_A")
        private Short recipe_A;
        @Column(name="recipe_B")
        private Short recipe_B;
        @Column(name="partname_A")
        private String partname_A;
        @Column(name="partname_B")
        private String partname_B;
        @Column(name="fadetime")
        private Integer fadetime;
        @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "cycles")
        @JsonManagedReference
        private Set<CycleSensor> cycleSensors = new HashSet<>();

CycleSensor:

  @Entity
    @Data
    @Table(name="cyclesensor", catalog="bizware_asp_test", schema="BizWareUser")
    public class CycleSensor {
        @Id
        @GeneratedValue(strategy = GenerationType.AUTO)
        @Column(name="id")
        private Integer Id;
        @ManyToOne(fetch = FetchType.LAZY)
        @JoinColumn(name = "cycles", nullable = false)
        @JsonBackReference
        private Cycles cycles;
        @Column(name="compactor")
        private String compactor;
        @OneToMany
        @JoinColumn(name="Id")
        private Set<Sensor> sensors;

        private CycleSensor(){}

Sensor:

 @Entity
    @Data
    @Table(name="Sensor", catalog="bizware_asp_test", schema="BizWareUser")
    public class Sensor {
        @Id
        @GeneratedValue(strategy= GenerationType.AUTO)
        @Column(name="Id")
        private Integer Id;
        @Column(name="Channel")
        private Integer Channel;
        @Column(name="DateTime")
        private Timestamp DateTime;
        @Column(name="Unit")
        private String Unit;
        @Column(name="Peak")
        private Float Peak;
        @Column(name="Phase")
        private Float Phase;
        @Column(name="Amplitude")
        private Float Amplitude;
        @ManyToOne
        @JoinColumn(name="SensorDataCol")
        private CycleSensor CycleSensor;

CycleRepository:

  @Repository
    public interface CyclesRepository extends CrudRepository<Cycles, Long> {   
@Query("Select s, cs, c from Sensor s Join s.CycleSensor as cs Join cs.cycles as c Where c.id IN (Select max(id) from Cycles)")
        List<Cycles> findTop1ByOrderByIdDesc();      
    }

SensorRepository:

@Repository
public interface SensorRepository extends CrudRepository<Sensor, Long> {

    @Query("Select s, cs, c from Sensor s Join s.CycleSensor as cs Join cs.cycles as c Where c.id IN (Select max(id) from Cycles)")
    List<Sensor> findTop1ByOrderByIdDesc();


}

When I access from the cycles controller, I receive this:

java.lang.ClassCastException: [Ljava.lang.Object; cannot be cast to muellerspring.compactionsystemmonitor.Cycles at muellerspring.compactionsystemmonitor.CyclesController.index(CyclesController.java:16) ~[classes/:na] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_151] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_151] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_151] at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_151] at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205) ~[spring-web-4.3.13.RELEASE.jar:4.3.13.RELEASE] at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133) ~[spring-web-4.3.13.RELEASE.jar:4.3.13.RELEASE] at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97) ~[spring-webmvc-4.3.13.RELEASE.jar:4.3.13.RELEASE] at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827) ~[spring-webmvc-4.3.13.RELEASE.jar:4.3.13.RELEASE] at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738) ~[spring-webmvc-4.3.13.RELEASE.jar:4.3.13.RELEASE] at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85) ~[spring-webmvc-4.3.13.RELEASE.jar:4.3.13.RELEASE] at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967) ~[spring-webmvc-4.3.13.RELEASE.jar:4.3.13.RELEASE] at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901) ~[spring-webmvc-4.3.13.RELEASE.jar:4.3.13.RELEASE] at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970) ~[spring-webmvc-4.3.13.RELEASE.jar:4.3.13.RELEASE] at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861) ~[spring-webmvc-4.3.13.RELEASE.jar:4.3.13.RELEASE] at javax.servlet.http.HttpServlet.service(HttpServlet.java:635) ~[tomcat-embed-core-8.5.23.jar:8.5.23] at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846) ~[spring-webmvc-4.3.13.RELEASE.jar:4.3.13.RELEASE] at javax.servlet.http.HttpServlet.service(HttpServlet.java:742) ~[tomcat-embed-core-8.5.23.jar:8.5.23] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) ~[tomcat-embed-core-8.5.23.jar:8.5.23] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.23.jar:8.5.23] at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) ~[tomcat-embed-websocket-8.5.23.jar:8.5.23] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.23.jar:8.5.23] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.23.jar:8.5.23] at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99) ~[spring-web-4.3.13.RELEASE.jar:4.3.13.RELEASE] at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-4.3.13.RELEASE.jar:4.3.13.RELEASE] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.23.jar:8.5.23] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.23.jar:8.5.23] at org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:108) ~[spring-web-4.3.13.RELEASE.jar:4.3.13.RELEASE] at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-4.3.13.RELEASE.jar:4.3.13.RELEASE] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.23.jar:8.5.23] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.23.jar:8.5.23] at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:81) ~[spring-web-4.3.13.RELEASE.jar:4.3.13.RELEASE] at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-4.3.13.RELEASE.jar:4.3.13.RELEASE] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.23.jar:8.5.23] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.23.jar:8.5.23] at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197) ~[spring-web-4.3.13.RELEASE.jar:4.3.13.RELEASE] at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-4.3.13.RELEASE.jar:4.3.13.RELEASE] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.23.jar:8.5.23] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.23.jar:8.5.23] at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199) ~[tomcat-embed-core-8.5.23.jar:8.5.23] at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) [tomcat-embed-core-8.5.23.jar:8.5.23] at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:478) [tomcat-embed-core-8.5.23.jar:8.5.23] at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140) [tomcat-embed-core-8.5.23.jar:8.5.23] at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81) [tomcat-embed-core-8.5.23.jar:8.5.23] at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87) [tomcat-embed-core-8.5.23.jar:8.5.23] at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342) [tomcat-embed-core-8.5.23.jar:8.5.23] at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:803) [tomcat-embed-core-8.5.23.jar:8.5.23] at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) [tomcat-embed-core-8.5.23.jar:8.5.23] at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868) [tomcat-embed-core-8.5.23.jar:8.5.23] at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1459) [tomcat-embed-core-8.5.23.jar:8.5.23] at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-embed-core-8.5.23.jar:8.5.23] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [na:1.8.0_151] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [na:1.8.0_151] at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-embed-core-8.5.23.jar:8.5.23] at java.lang.Thread.run(Thread.java:748) [na:1.8.0_151]

When I run from the sensors controller, I get this.

Could not write JSON: HHH000142: Javassist Enhancement failed: muellerspring.compactionsystemmonitor.Cycles; nested exception is com.fasterxml.jackson.databind.JsonMappingException: HHH000142: Javassist Enhancement failed: muellerspring.compactionsystemmonitor.Cycles (through reference chain: java.util.ArrayList[0]->muellerspring.compactionsystemmonitor.Sensor["cycleSensor"]->muellerspring.compactionsystemmonitor.CycleSensor["sensors"])

CyclesController

@RestController
public class CyclesController {
    @Autowired
    private CyclesRepository cyclesRepository;

    @RequestMapping(value="/cycles", method=RequestMethod.GET)
    public Cycles index(){
        List<Cycles> cycles = cyclesRepository.findTop1ByOrderByIdDesc();
        return cycles.get(0);
    }

    @RequestMapping(value="/cycles/count", method=RequestMethod.GET)
    public Long Count(){
        return cyclesRepository.count();
    }
}

Sensor Controller:

@RestController
public class SensorController {
    @Autowired
    private SensorRepository sensorRepository;
    @RequestMapping(value="/sensors", method=RequestMethod.GET)
    public List<Sensor> index(){
        List<Sensor> sensors = sensorRepository.findTop1ByOrderByIdDesc();
        return sensors;
    }

}
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.323701 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO