How can I Fetch Data from MySql DB using Hibernate as I am getting 'Null' value or Entity not Mapped while fetching?

ArchakMukherjee
March 30, 2023
147 views
0 votes
2 Answers

I am new to Hibernate and getting either error or null value when trying to fetch any row from my existing Table in MySQL database.

I have a Table named ‘Customers’, the table structure defined in sql is as below

`CREATE TABLE `customers` (
  `customerNumber` int(11) NOT NULL,
  .....
  .....
  `salesRepEmployeeNumber` int(11) DEFAULT NULL,
  `creditLimit` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`customerNumber`),
  KEY `salesRepEmployeeNumber` (`salesRepEmployeeNumber`),
  CONSTRAINT `customers_ibfk_1` FOREIGN KEY (`salesRepEmployeeNumber`) REFERENCES `employees` (`employeeNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;`

My ‘Customer’ entity is defined as below.



@Entity
@Table(name = "customers")
public class Customers {

    @Id
    @Column(name = "customerNumber")
    private int customerNumber;

    ........
    ........
    @Column(name = "salesRepEmployeeNumber")
    private int salesRepEmployeeNumber;

    @Column(name = "creditLimit")
    private double creditLimit;

   
Getters and setters()

Here is my Reporitory through which I am trying to create the transactional methods. I have two methods, findbyId (to find using the Primary Key) and findAll(to get all rows).



@Repository
@Transactional
public class CustomersRepository {

    @Autowired
    EntityManager em;



    public Customers findbyId(int id){
       return em.find(Customers.class, id);

    }

    public List<Customers> findAll(){
        TypedQuery<Customers> query1= em.createQuery("Select c from Customer c", Customers.class);
        List<Customers> customersList = query1.getResultList();
        return customersList;
    }
}

Here is the main method.

package com.example.demo;


@SpringBootApplication
public class DemoApplication implements CommandLineRunner {

    private Logger logger = LoggerFactory.getLogger(this.getClass());

    @Autowired
    CustomersRepository repository;


    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }

    @Override
    public void run(String... args) throws Exception {

    

        logger.info("customer : {}",repository.findbyId(119));
        //logger.info("List of customer : {}",repository.findAll());

    }
}

when I am calling findbyId, I am getting ‘null’. when I am calling findAll, I am getting Table not mapped error.Here is the Log.

Null for findbyId()
Note : I have checked the data with this given ID is present in the table.

  HHH000400: Using dialect: org.hibernate.dialect.MySQL8Dialect
2023-03-30 11:55:47.892  INFO 10412 --- [           main] o.h.e.t.j.p.i.JtaPlatformInitiator       : HHH000490: Using JtaPlatform implementation: [org.hibernate.engine.transaction.jta.platform.internal.NoJtaPlatform]
2023-03-30 11:55:47.908  INFO 10412 --- [           main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
2023-03-30 11:55:48.119  WARN 10412 --- [           main] JpaBaseConfiguration$JpaWebConfiguration : spring.jpa.open-in-view is enabled by default. Therefore, database queries may be performed during view rendering. Explicitly configure spring.jpa.open-in-view to disable this warning
2023-03-30 11:55:48.751  INFO 10412 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8080 (http) with context path ''
2023-03-30 11:55:48.768  INFO 10412 --- [           main] com.example.demo.DemoApplication         : Started DemoApplication in 8.008 seconds (JVM running for 8.957)
Hibernate: select customers0_.customer_number as customer1_0_0_, customers0_.address_line1 as address_2_0_0_, customers0_.address_line2 as address_3_0_0_, customers0_.city as city4_0_0_, customers0_.contact_first_name as contact_5_0_0_, customers0_.contact_last_name as contact_6_0_0_, customers0_.country as country7_0_0_, customers0_.credit_limit as credit_l8_0_0_, customers0_.customer_name as customer9_0_0_, customers0_.phone as phone10_0_0_, customers0_.postal_code as postal_11_0_0_, customers0_.sales_rep_employee_number as sales_r12_0_0_, customers0_.state as state13_0_0_ from customers customers0_ where customers0_.customer_number=?
2023-03-30 11:55:48.950  INFO 10412 --- [           main] ication$$EnhancerBySpringCGLIB$$296d79dd : customer : null

**
Customer not mapped for findAll()**

Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.
2023-03-30 12:10:01.682 ERROR 6532 --- [           main] o.s.boot.SpringApplication               : Application run failed

java.lang.IllegalStateException: Failed to execute CommandLineRunner
    at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:771) ~[spring-boot-2.7.11-20230328.144451-6.jar:2.7.11-SNAPSHOT]
    at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:752) ~[spring-boot-2.7.11-20230328.144451-6.jar:2.7.11-SNAPSHOT]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:314) ~[spring-boot-2.7.11-20230328.144451-6.jar:2.7.11-SNAPSHOT]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1303) ~[spring-boot-2.7.11-20230328.144451-6.jar:2.7.11-SNAPSHOT]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1292) ~[spring-boot-2.7.11-20230328.144451-6.jar:2.7.11-SNAPSHOT]
    at com.example.demo.DemoApplication.main(DemoApplication.java:26) ~[classes/:na]
Caused by: org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.hql.internal.ast.QuerySyntaxException: Customer is not mapped [Select c from Customer c]; nested exception is java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: Customer is not mapped [Select c from Customer c]
    at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:374) ~[spring-orm-5.3.26.jar:5.3.26]
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:235) ~[spring-orm-5.3.26.jar:5.3.26]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:551) ~[spring-orm-5.3.26.jar:5.3.26]
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61) ~[spring-tx-5.3.26.jar:5.3.26]
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242) ~[spring-tx-5.3.26.jar:5.3.26]
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152) ~[spring-tx-5.3.26.jar:5.3.26]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.26.jar:5.3.26]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763) ~[spring-aop-5.3.26.jar:5.3.26]
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) ~[spring-tx-5.3.26.jar:5.3.26]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388) ~[spring-tx-5.3.26.jar:5.3.26]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) ~[spring-tx-5.3.26.jar:5.3.26]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.26.jar:5.3.26]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763) ~[spring-aop-5.3.26.jar:5.3.26]
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:708) ~[spring-aop-5.3.26.jar:5.3.26]
    at com.example.demo.reporitory.CustomersRepository$$EnhancerBySpringCGLIB$$2a61318c.findAll(<generated>) ~[classes/:na]
    at com.example.demo.DemoApplication.run(DemoApplication.java:36) ~[classes/:na]
    at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:768) ~[spring-boot-2.7.11-20230328.144451-6.jar:2.7.11-SNAPSHOT]
    ... 5 common frames omitted
Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: Customer is not mapped [Select c from Customer c]
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:138) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:757) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:848) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:114) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
    at java.base/java.lang.reflect.Method.invoke(Method.java:566) ~[na:na]
    at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:315) ~[spring-orm-5.3.26.jar:5.3.26]
    at com.sun.proxy.$Proxy89.createQuery(Unknown Source) ~[na:na]
    at com.example.demo.reporitory.CustomersRepository.findAll(CustomersRepository.java:27) ~[classes/:na]
    at com.example.demo.reporitory.CustomersRepository$$FastClassBySpringCGLIB$$95f61396.invoke(<generated>) ~[classes/:na]
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.3.26.jar:5.3.26]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:793) ~[spring-aop-5.3.26.jar:5.3.26]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.3.26.jar:5.3.26]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763) ~[spring-aop-5.3.26.jar:5.3.26]
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137) ~[spring-tx-5.3.26.jar:5.3.26]
    ... 16 common frames omitted
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: Customer is not mapped [Select c from Customer c]
    at org.hibernate.hql.internal.ast.QuerySyntaxException.generateQueryException(QuerySyntaxException.java:79) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
    at org.hibernate.QueryException.wrapWithQueryString(QueryException.java:103) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:220) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:144) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:112) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:73) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
    at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:162) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
    at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:636) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:748) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
    ... 31 common frames omitted
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: Customer is not mapped
    at org.hibernate.hql.internal.ast.util.SessionFactoryHelper.requireClassPersister(SessionFactoryHelper.java:170) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
    at org.hibernate.hql.internal.ast.tree.FromElementFactory.addFromElement(FromElementFactory.java:91) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
    at org.hibernate.hql.internal.ast.tree.FromClause.addFromElement(FromClause.java:77) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
    at org.hibernate.hql.internal.ast.HqlSqlWalker.createFromElement(HqlSqlWalker.java:334) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElement(HqlSqlBaseWalker.java:3782) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElementList(HqlSqlBaseWalker.java:3671) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromClause(HqlSqlBaseWalker.java:746) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:602) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:339) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:287) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:276) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:192) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
    ... 37 common frames omitted

Here is my application.properties file.

spring.jpa.hibernate.ddl-auto=none
spring.datasource.url=jdbc:mysql://localhost:3306/classicmodels
spring.datasource.username=root
spring.datasource.password=admin

spring.h2.console.enabled=true
spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL8Dialect
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

I am not sure why this is happening. I have checked Database is connected, I created another Entity and modified ddl-auto=update to create Table in database from the entity and it got created. I was also able to insert in that Table from my application, but can’t fetch from existing tables. Please help.

Answers

- aspGG
- March 30, 2023 at 9:10 am
- 0 votes
0
You table’s name is ‘Customers’ and you are querying with table ‘Customer’

"Select c from Customer c"

Login or Signup to reply.

your entity name and table name is Customers and in your criteria query you are using Customer , which is causing issue.

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: Customer is not mapped [Select c from Customer c]

@Entity
    @Table(name = "customers")
    public class Customers {

You can also try as follow for TypedQuery, after updating your entity class with Default constructor

@Entity
@Table(name = "customers")
public class Customers {

public Customers() {}
.....


   @Repository
    @Transactional
    public class CustomersRepository {
    
        @Autowired
        EntityManager em;
    
        public Customers findbyId(int id){
           return em.find(Customers.class, id);
    
        }
    
        public List<Customers> findAll(){
          CriteraiBuilder builder=em.getCriteriaBuilder();
          CriteriaQuery<Customers> query= builder.createQuery(Customers.class);
           Root<Customers> siteRoot = query.from(Customers.class);
           query.select(siteRoot);
           TypedQuery<Customers> query1= em.createQuery(query);
            List<Customers> customersList = query1.getResultList();
            return customersList;
        }
    }

Please signup or login to give your own answer.

Click here to cancel reply.