0
votes

I have developed a java stand alone application (java+hibernate) , now I am replanting which to java 8+springboot+jpa+hibernate+mysql framework. I used HQL in my old project, but there seems to be some problems with SQL sentences in the new framework , for example, I have to change "From p where id=(:parameter)" to "Select * from p where id=:parameter" by removing the bracket.

BEAN

@Entity
public class Place extends Groupunit_Defined implements Interface_Entity
{
......
@ManyToOne(targetEntity=Place_Definer.class)
@JoinColumn(name="Groupunit_Definer",nullable=false)
private Place_Definer Groupunit_Definer;//
.....
}
@Entity
public class Place_Definer extends Groupunit_Definer
{
......
@Column(nullable=false,length=150)
private String Classification;
…….
}

@SuppressWarnings("unchecked") public ArrayList Simply_get_thing_list ( String Query_String, ArrayList Parameter_List, Class Thing_Class ) { Query q=Entity_Manager.createNativeQuery(Query_String,Thing_Class); ……. return Thing_List; }

SQL SENTENCE

"SELECT * FROM Place p WHERE p.Groupunit_Definer.Classification='Jurisdiction'"

HIBERNATE CONFIG

spring.jpa.database = MYSQL
spring.jpa.show-sql = true
spring.jpa.hibernate.ddl-auto = update
spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect

ERROR MESSAGE

:: Spring Boot ::       (v1.5.21.RELEASE)

2019-09-04 05:28:11.851  INFO 2508 --- [           main] c.s.mirrorworld.mirrorworldApplication   : Starting mirrorworldApplication on LAPTOP-FAS0SHLM with PID 2508 (C:\Users\yueho\eclipse-workspace\mirrorworld\target\classes started by yueho in C:\Users\yueho\eclipse-workspace\mirrorworld)
2019-09-04 05:28:11.859  INFO 2508 --- [           main] c.s.mirrorworld.mirrorworldApplication   : No active profile set, falling back to default profiles: default
2019-09-04 05:28:12.066  INFO 2508 --- [           main] s.c.a.AnnotationConfigApplicationContext : Refreshing org.springframework.context.annotation.AnnotationConfigApplicationContext@2f217633: startup date [Wed Sep 04 05:28:12 CST 2019]; root of context hierarchy
2019-09-04 05:28:15.937  INFO 2508 --- [           main] j.LocalContainerEntityManagerFactoryBean : Building JPA container EntityManagerFactory for persistence unit 'default'
2019-09-04 05:28:15.994  INFO 2508 --- [           main] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [
    name: default
    ...]
2019-09-04 05:28:16.257  INFO 2508 --- [           main] org.hibernate.Version                    : HHH000412: Hibernate Core {5.0.12.Final}
2019-09-04 05:28:16.260  INFO 2508 --- [           main] org.hibernate.cfg.Environment            : HHH000206: hibernate.properties not found
2019-09-04 05:28:16.263  INFO 2508 --- [           main] org.hibernate.cfg.Environment            : HHH000021: Bytecode provider name : javassist
2019-09-04 05:28:16.559  INFO 2508 --- [           main] o.hibernate.annotations.common.Version   : HCANN000001: Hibernate Commons Annotations {5.0.1.Final}
2019-09-04 05:28:17.740  INFO 2508 --- [           main] org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.MySQL5Dialect
2019-09-04 05:28:19.002  WARN 2508 --- [           main] o.h.b.i.SessionFactoryBuilderImpl        : Unrecognized hbm2ddl_auto value : update  .  Supported values include create, create-drop, update, and validate.  Ignoring
2019-09-04 05:28:20.031  INFO 2508 --- [           main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
2019-09-04 05:28:24.577  INFO 2508 --- [           main] o.s.j.e.a.AnnotationMBeanExporter        : Registering beans for JMX exposure on startup
2019-09-04 05:28:24.607  INFO 2508 --- [           main] c.s.mirrorworld.mirrorworldApplication   : Started mirrorworldApplication in 13.439 seconds (JVM running for 24.733)
2019-09-04 05:28:33.326  INFO 2508 --- [WT-EventQueue-0] o.h.h.i.QueryTranslatorFactoryInitiator  : HHH000397: Using ASTQueryTranslatorFactory
2019-09-04 05:28:33.844  WARN 2508 --- [WT-EventQueue-0] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1054, SQLState: 42S22
2019-09-04 05:28:33.845 ERROR 2508 --- [WT-EventQueue-0] o.h.engine.jdbc.spi.SqlExceptionHelper   : Unknown column 'p.Groupunit_Definer.Classification' in 'where clause'
Exception in thread "AWT-EventQueue-0" javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at ……
org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:375)
    at ……
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at …...
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'p.Groupunit_Definer.Classification' in 'where clause'
1
Is that targetEntity=Place_Definer.class really required there? If the GROUPUNIT_DEFINER column is within the PLACE table I just usually does not explicitly give that and it will use the column from the current table (PLACE). And isn't @Table(name="PLACE")...or something like that missing from the class (not sure if it is required or Spring can detect table name from the class name)? - PowR
You're confusing JPQL and SQL. SQL uses table names and column names, and has select *. JPAL uses entity names and their properties, and doesn't have select *. - JB Nizet
Thank you JB Nizet, I changed the sentence to "SELECT p.* FROM Place p WHERE p.Groupunit_Definer.Classification='Jurisdiction'", but no use. - Terry Yue
Regarding createNativeQuery Method of EntityManager, I wonder whether the SQL statement can be any SQL language such as SQL, JPQL or HQL? - Terry Yue

1 Answers

0
votes

The problem has been solved by changing "createNativeQuery" to "createQuery", thank you to