java.sql.SQLException Invalid object name

Question

I get the error below when I try to read from the database using createQuery but I can read from the database if I use createNativeQuery on my entityManager. So I guess my entityManager works but something is wrong with my Hibernate configuration?

application.properties:

security.user.password=<password>
logging.level.org.springframework.security=DEBUG
spring.datasource.url=jdbc:jtds:sqlserver://localhost:60830;databaseName=<db>
spring.datasource.username=<user>
spring.datasource.password=<password>
spring.datasource.driver-class-name=net.sourceforge.jtds.jdbc.Driver
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=none
spring.jpa.properties.hibernate.id.new_generator_mappings=false
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.SQLServerDialect
spring.jpa.database-platform=org.hibernate.dialect.SQLServerDialect

DAO:

package demo.dao;

import demo.entities.StampCard;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

@Transactional
@Repository
public class StampCardDAO implements IStampCardDAO {

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public StampCard getStampCard(int id) {
        //These rows works fine
        //Query query = entityManager.createNativeQuery("select userId from StampCard where StampCardID = '1'");
        //String userId = (String) query.getSingleResult();
        Query query = entityManager.createQuery("SELECT s FROM StampCard s WHERE s.stampCardID = '1'");
        StampCard stampCard = (StampCard) query.getSingleResult();
        return stampCard;
    }
}

Interface for DAO:

package demo.dao;

import demo.entities.StampCard;

public interface IStampCardDAO {

    StampCard getStampCard(int id);
}

Entity:

package demo.entities;

import lombok.Getter;
import lombok.Setter;
import org.apache.commons.lang3.builder.EqualsBuilder;
import org.apache.commons.lang3.builder.HashCodeBuilder;

import javax.persistence.Entity;
import javax.persistence.Id;
import java.io.Serializable;

@Entity
public class StampCard implements Serializable {

    private static final long serialVersionUID = 6602888822739626415L;

    private int stampCardID;
    @Getter @Setter private String createdDate;
    @Getter @Setter private String userID;
    @Getter @Setter private int numberOfStamps;

    @Id
    public int getStampCardID() {
        return stampCardID;
    }

    public void setStampCardID(int stampCardID) {
        this.stampCardID = stampCardID;
    }

    @Override 
    public boolean equals(Object obj) {
        if (obj == null) {
            return false;
        }
        if (obj == this) {
            return true;
        }
        if (obj.getClass() != getClass()) {
            return false;
        }
        StampCard rhs = (StampCard) obj;
        return new EqualsBuilder().append(this.stampCardID, rhs.stampCardID).append(this.createdDate, rhs.createdDate).append(this.userID, rhs.userID)
                .append(this.numberOfStamps, rhs.numberOfStamps).isEquals();
    }

    @Override 
    public int hashCode() {
        return new HashCodeBuilder().append(stampCardID).append(createdDate).append(userID).append(numberOfStamps).toHashCode();
    }
}

Database table:

CREATE TABLE [dbo].[StampCard](
    [StampCardID] [int] NOT NULL,
    [CreatedDate] [varchar](8) NOT NULL,
    [UserID] [varchar](255) NOT NULL,
    [NumberOfStamps] [int] NOT NULL,
 CONSTRAINT [PK_CardID] PRIMARY KEY CLUSTERED 
(
    [StampCardID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Database table content:

StampCardID CreatedDate UserID  NumberOfStamps
1           20171208    USer    1

Error message:

2017-12-10 18:04:09.837 WARN 37080 --- [ restartedMain] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 208, SQLState: S0002 2017-12-10 18:04:09.840 ERROR 37080 --- [ restartedMain] o.h.engine.jdbc.spi.SqlExceptionHelper : Invalid object name 'stamp_card'.

Caused by: java.sql.SQLException: Invalid object name 'stamp_card'.

I am thankful for all the help I can get.

Answer 1

For cases where you see that column does'nt exist. Might be the reason that in the DB your Column name is like JiraNumber so within the @Table annotation as well as within the @Column annotation write the string value for the parameter name in UPPER CASE.

Answer 2

I had a similar issue, but in my case the root cause was existing schema within the Database I was connected to.

Adding the following line to the configuration (application.properties file) enabled total drop of the existing schema and creation of the one defined through my application

spring.jpa.hibernate.ddl-auto = create-drop

Here is an answer by Naros, which helped me understand the "create-drop" property better: https://stackoverflow.com/a/42147995/6110987

Hope it helps!

Answer 3

The accepted answer works. An alternative solution is to change naming strategy. Springs default naming strategy, org.springframework.boot.orm.jpa.SpringNamingStrategy, splits camel case names with underscore. Change naming strategy by adding spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl in application.properties. Then the @Table annotation is not needed since the database table and entity class has the same name.

Answer 4

@Entity with StampCard class name, hibernate searchs a table with name Stamp_Card

So if your table name is just StampCard use Table annotation like below to point your table name.

@Entity
@Table(name = "STAMPCARD")