2

I know this question has been asked many time on stackoverflow and I have gone through all of them. But nothing worked for me. So I am posting this question again. I have a table in postgres. DDL is below.

CREATE TABLE  APPUSERMASTER (
 USER_ID BIGSERIAL NOT NULL,
 USER_NAME VARCHAR(20) NOT NULL,
 FIRST_NAME VARCHAR(20) NOT NULL,
 SECOND_NAME VARCHAR(20) NOT NULL,
 EMAIL VARCHAR(50) NOT NULL,
 PASSWORD TEXT  NOT NULL,
 PRIMARY KEY (USER_ID) 
) ;

My POJO class is below.

@Entity
@Table(name = "appusermaster", schema = "public")
public class UserMaster implements java.io.Serializable {

private long userId;
private String userName;
private String firstName;
private String secondName;
private String email;
private String password;

public UserMaster() {
}

@Id
@Column(name = "user_id", unique = true, nullable = false)
@GeneratedValue(strategy = GenerationType.IDENTITY)
public long getUserId() {
    return this.userId;
}

public void setUserId(long userId) {
    this.userId = userId;
}

@Column(name = "user_name", nullable = false, length = 20)
public String getUserName() {
    return this.userName;
}

public void setUserName(String userName) {
    this.userName = userName;
}

@Column(name = "first_name", nullable = false, length = 20)
public String getFirstName() {
    return this.firstName;
}

public void setFirstName(String firstName) {
    this.firstName = firstName;
}

@Column(name = "second_name", nullable = false, length = 20)
public String getSecondName() {
    return this.secondName;
}

public void setSecondName(String secondName) {
    this.secondName = secondName;
}

@Column(name = "email", length = 50)
public String getEmail() {
    return this.email;
}

public void setEmail(String email) {
    this.email = email;
}

@Column(name = "password", length = 20)
public String getPassword() {
    return this.password;
}

public void setPassword(String password) {
    this.password = password;
}

}

In my DAO class I have below code.

public UserMaster getUserByUserName(String userName) {
    Criteria criteria = getSession().createCriteria(UserMaster.class); 
    criteria.add(Restrictions.eq("userName", userName));
    UserMaster userMaster = (UserMaster) criteria.uniqueResult();
    return userMaster;
}

But whenever I am executing this code I am getting below exception.

[WARN ] 2017-07-18 18:24:23.105 [http-bio-8181-exec-9] SqlExceptionHelper - SQL Error: 0, SQLState: 42703
[ERROR] 2017-07-18 18:24:23.110 [http-bio-8181-exec-9] SqlExceptionHelper - ERROR: column this_.user_id does not exist
  Position: 8 org.hibernate.exception.SQLGrammarException: could not extract ResultSet
Caused by: org.postgresql.util.PSQLException: ERROR: column this_.user_id does not exist
  Position: 8
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2412)

What am I doing wrong? Hibernate version is 5.1.8.Final. Spring version is 4.3.5.

I turned on show sql property of hibernate and below sql is getting generated.

Hibernate: 
select
    this_.user_id as user_id1_0_0_,
    this_.email as email4_0_0_,
    this_.first_name as first_na5_0_0_,
    this_.password as password6_0_0_,
    this_.second_name as second_n7_0_0_,
    this_.user_name as user_nam9_0_0_ 
from
    public.appusermaster this_ 
where
    this_.user_name=?
[WARN ] 2017-07-18 19:22:40.797 [http-bio-8181-exec-12] SqlExceptionHelper - SQL Error: 0, SQLState: 42703
[ERROR] 2017-07-18 19:22:40.814 [http-bio-8181-exec-12] SqlExceptionHelper -     ERROR: column this_.user_id does not exist
  Position: 8
GD_Java
  • 1,359
  • 6
  • 24
  • 42
  • 1
    Maybe it is a dumb suggestion, but try to replace `return this.userId;` with `return userId;` . – Arnaud Jul 18 '17 at 13:41
  • 1
    @Berger That doesn't sound dumb to me at all. If you poke around for this error, you'll usually see some sort of table name prefixed to the column which can't be found. – Tim Biegeleisen Jul 18 '17 at 13:44
  • @Berger I removed this from getter and it does not work. Thanks – GD_Java Jul 18 '17 at 13:46
  • @GD_Java can you set the debugging to show the SQL statement itself. as per https://stackoverflow.com/questions/30118683/how-to-log-sql-statements-in-spring-boot and update your question here? – Gary - Stand with Ukraine Jul 18 '17 at 13:48
  • @Gary I am not using spring boot it is a simple spring hibernate based web application. I added show sql output in my question. I added below properties in configuration but no change in output . logging.level.org.hibernate.SQL=DEBUG logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE – GD_Java Jul 18 '17 at 13:57
  • Double check that you are hitting the right DB, many people having this error had that problem . – Arnaud Jul 18 '17 at 14:00
  • GD_JAVA Can you check in DB itself if value was generated for user_id? – fg78nc Jul 18 '17 at 14:00
  • @Berger DB name is correct. I found the problem. DB properties file had different host name that is why I was getting this problem. Correcting it fixed the problem. Sorry I overlooked it. – GD_Java Jul 19 '17 at 14:08
  • 1
    @GD_Java : In fact talking about the "right DB", I was meaning the right host+port+db . Nice that the problem is fixed :) – Arnaud Jul 19 '17 at 14:11
  • Upvoted for explaining right DB. :) – GD_Java Jul 19 '17 at 14:12

1 Answers1

0

The generated SQL that the log showed was selecting records from a table called "usermaster" instead of "APPUSERMASTER"

It looks like it isn't picking up the @Table annotation. If you changed the name of the table in your java it might be that your class files are out of date and still referencing the old name.

Gary - Stand with Ukraine
  • 7,081
  • 1
  • 30
  • 39