I'm having this confusing problem trying to query @OneToMany relationship using JPA CriteriaQuery where there's this columns named . as col_2_0_ (notice the dot), there's nothing but a dot in that column. And obviously, an expcetion is being thrown:
java.sql.SQLSyntaxErrorException: 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 '. as col_2_0_, stockdetai1_.id as id1_6_, stockdetai1_.active as active2_6_, sto' at line 1
Here are the mappings
Product.class
public class Product {
@Id
@GeneratedValue(generator = ProductIdGenerator.NAME)
@GenericGenerator(name = ProductIdGenerator.NAME, strategy = ProductIdGenerator.PATH)
@Column(updatable = false, length = IDENTIFIER_LENGTH, columnDefinition = "VARCHAR(11)")
private String id;
// other columns
//-----------problem here-----------//
@JsonIgnore
@OneToMany(mappedBy = "product")
private List<StockDetail> stockDetails;
//----------------------------------//
// getters and setters
}
StockDetail.class
public class StockDetail {
@Id
@GeneratedValue(generator = StockDetailIdGenerator.NAME)
@GenericGenerator(name = StockDetailIdGenerator.NAME, strategy = StockDetailIdGenerator.PATH)
@Column(columnDefinition = "VARCHAR(20)", updatable = false)
private String id;
//---------here's the foreign key---------//
@ManyToOne(optional = false, fetch = FetchType.LAZY)
private Product product;
//---------here's the foreign key---------//
// other columns
// getters and setters
}
Here's the query
Session session = getCurrentSession();
CriteriaBuilder builder = session.getCriteriaBuilder();
CriteriaQuery<Tuple> query = builder.createQuery(Tuple.class);
Root<E> root = query.from(type);
query = selectColumns(query, root, columns).where(builder.and(isActive(builder, root),
builder.equal(root.get(HibernateHelper.getIdentifierPropertyName(type)), id)));
return session.createQuery(query).getResultStream().findFirst().orElse(null);
Here's Hibernate query logging
Hibernate:
select
product0_.active as col_0_0_,
product0_.name as col_1_0_,
. as col_2_0_, // unknown column
stockdetai1_.id as id1_6_,
stockdetai1_.active as active2_6_,
stockdetai1_.color as color3_6_,
stockdetai1_.description as descript4_6_,
stockdetai1_.material as material5_6_,
stockdetai1_.numeric_size as numeric_6_6_,
stockdetai1_.product_id as product10_6_,
stockdetai1_.provider_id as provide11_6_,
stockdetai1_.size as size7_6_,
stockdetai1_.sold_by as sold_by12_6_,
stockdetai1_.status as status8_6_,
stockdetai1_.stocked_by as stocked13_6_,
stockdetai1_.stocked_date as stocked_9_6_
from
products product0_
inner join
stock_details stockdetai1_
on product0_.id=stockdetai1_.product_id
where
product0_.active=1
and product0_.id=?
DB schema
I've checked the requested colunms to see if there was any unwanted/unknown/unusual columns but they were all clean. The exception was only thrown from the DBMS, not the ORM.
I really don't want to separately fetch the StockDetail using another query. Thank you for any help guys.
