Getting the ResultSet from a ManyToMany Relationship with Join Tables in JPA

Question

I have a somewhat complex query that I am trying to figure out how to to store the result in a model. Example:

public interface Po_partRepository extends JpaRepository<Po_part, Long> {

    @Query(value = "SELECT * FROM T_PO_PART u WHERE u.po_id LIKE %?1", 
  nativeQuery = true)
    List<Po_part> findByPoId(Long poId);

    @Query(value = "SELECT " + "po.id po_id," + "po.po_number po_number,"
            + "po.due_date po_due_date," + "po_part.id po_part_id,"
            + "po_part.part_quantity part_quantity," + "part.id part_id,"
            + "part.id part_id," + "part.part_number part_number,"
            + "part.part_description part_description,"
            + "part.plasma_hrs_per_part plasma_hrs,"
            + "part.grind_hrs_per_part grind_hrs,"
            + "part.mill_hrs_per_part mill_hrs,"
            + "part.brakepress_hrs_per_part brakepress_hrs"
            + "FROM hillcresttooldie.t_po po "
            + "join hillcresttooldie.t_po_part po_part "
            + "on po_part.po_id = po.id " + "join hillcresttooldie.t_part part "
            + "on part.id = po_part.part_id;", nativeQuery = true)
    List<Shop_Orders> getShopOrders();

The Shop_Orders class is just a simple model:

public class Shop_Orders {

    private int po_id;
    private int po_number;
    private Date po_due_date;
    private int part_quanity;
    private int part_id;
    private String part_description;
    private int plasma_hrs;
    private int grind_hrs;
    private int mill_hrs;
    private int breakpress_hrs;

    public int getPo_id() {
        return po_id;
    }

    public void setPo_id(int po_id) {
        this.po_id = po_id;
    }

    public int getPo_number() {
        return po_number;
    }

Here is also a snippet of the Po.java class and Part.java class that this query is pulling from.

/**
 * A Po.
 */
@Entity
@Table(name = "T_PO")
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
public class Po implements Serializable {

    /**
     * 
     */
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Column(name = "po_number")
    private String po_number;

part.java

/**
 * A Part.
 */
@Entity
@Table(name = "T_PART")
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
public class Part implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Column(name = "part_number")
    private String part_number;

    @Column(name = "part_description")
    private String part_description;

    @Column(name = "plasma_hrs_per_part", precision=12, scale=4)
    private BigDecimal plasma_hrs_per_part;

    @Column(name = "grind_hrs_per_part", precision=12, scale=4)
    private BigDecimal grind_hrs_per_part;

    @Column(name = "mill_hrs_per_part", precision=12, scale=4)
    private BigDecimal mill_hrs_per_part;

    @Column(name = "brakepress_hrs_per_part", precision=12, scale=4)
    private BigDecimal brakepress_hrs_per_part;

    @Column(name = "lb_per_part", precision=12, scale=4)
    private BigDecimal lb_per_part;

    @Column(name = "inventory_count")
    private Integer inventory_count;

    @ManyToMany
    @Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
    @JoinTable(name = "T_PART_MATERIAL",
               joinColumns = @JoinColumn(name="parts_id", referencedColumnName="ID"),
               inverseJoinColumns = @JoinColumn(name="materials_id", referencedColumnName="ID"))
    private Set<Material> materials = new HashSet<>();

Now when I run this query I get two errors:

Exception in com.htd.web.rest.Po_partResource.getAll() with cause = 
org.hibernate.exception.SQLGrammarException: could not extract ResultSet
org.springframework.dao.InvalidDataAccessResourceUsageException: could not    
extract ResultSet; SQL [n/a]; nested exception is 
org.hibernate.exception.SQLGrammarException: could not extract ResultSet

and

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You 
have an error in your SQL syntax; check the manual that corresponds to your 
MySQL server version for the right syntax to use near 'hillcresttooldie.t_po 
po join hillcresttooldie.t_po_part po_part on po_part.po_i' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)   
~[na:1.8.0_31]

The query works in in mySQL workbench just not in JPA. I know JPA's syntax is a little different but I thought nativeQuery enable pure SQL syntax.

As far and the ResultSet issue I did not sure any mappedBy declarations because I used @JoinTable.

Just so I am clear my two questions are: 1) From my above query, how can I set my model? 2) does nativeQuery indeed allow pure SQL syntax?

This has been asked before http://stackoverflow.com/questions/13012584/jpa-how-to-convert-a-native-query-result-set-to-pojo-class-collection and has a few answers, especially if using JPA 2.1. Yes JPA allows SQL: http://stackoverflow.com/questions/15948795/is-it-possible-to-use-raw-sql-within-a-spring-repository If your first query shown is working, try removing the semi-colon on the getShopOrders query — Chris, May 25 '15 at 18:05

score 0 · Accepted Answer · edited May 23 '17 at 11:50

Since your po_part table is more than just a simple join table (it has a quantity for each part within your order), then you need to map this join table to a separate Entity (called Po_Part, say). This Entity would have associations to both Po and Part, as well as a field for the quantity of the part on that order.

Then your Po Entity would have a collection for the parts within the Order, assuming that Po_Part has a po reference back to the Order:

@OneToMany(mappedBy = "po")
private List<Po_Part> partList;

See my answer to your related question for the query details.

Getting the ResultSet from a ManyToMany Relationship with Join Tables in JPA

1 Answers1

Linked