-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 8 posts ] 
Author Message
 Post subject: Postgre/MySQL discrepancy
PostPosted: Wed May 25, 2016 5:10 pm 
Newbie

Joined: Wed May 25, 2016 4:09 pm
Posts: 4
When I run my application against a MySQL database, everything goes as expected.
When I try the same against a Postgre database it errors out...

The following is the output from the logs.
https://gist.github.com/stelar7/f841497 ... c4baae9d7b

Code:
Hibernate: create table `profiles` (`user_id` bigint not null, `firstname` varchar(255), `profileImage` longblob, `lastname` varchar(255), `nickname` varchar(255), primary key (`user_id`)) ENGINE=InnoDB
Hibernate: create table `users` (`id` bigint not null auto_increment, `email` varchar(200) not null, `password` varchar(200) not null, `resetToken` varchar(36), `tokenExpires` datetime, `username` varchar(200) not null, primary key (`id`)) ENGINE=InnoDB

Hibernate: alter table `users` add constraint UK_6dotkott2kjsp8vw4d0m25fb7 unique (`email`)
Hibernate: alter table `users` add constraint UK_862ken6gv1xtf7oauhcclgxcc unique (`resetToken`)
Hibernate: alter table `users` add constraint UK_r43af9ap4edm43mmtq01oddj6 unique (`username`)

Hibernate: select user0_.`id` as id1_4_, user0_.`email` as email2_4_, user0_.`password` as password3_4_, user0_.`resetToken` as resetTok4_4_, user0_.`tokenExpires` as tokenExp5_4_, user0_.`username` as username6_4_ from `users` user0_ where user0_.`username`=? or user0_.`email`=?
Hibernate: insert into `users` (`email`, `password`, `resetToken`, `tokenExpires`, `username`) values (?, ?, ?, ?, ?)
Hibernate: insert into `profiles` (`firstname`, `profileImage`, `lastname`, `nickname`, `user_id`) values (?, ?, ?, ?, ?)


Code:
Hibernate: create table "profiles" ("user_id" int8 not null, "firstname" varchar(255), "profileImage" oid, "lastname" varchar(255), "nickname" varchar(255), primary key ("user_id"))
Hibernate: create table "users" ("id"  bigserial not null, "email" varchar(200) not null, "password" varchar(200) not null, "resetToken" varchar(36), "tokenExpires" timestamp, "username" varchar(200) not null, primary key ("id"))

Hibernate: alter table "users" add constraint UK_6dotkott2kjsp8vw4d0m25fb7 unique ("email")
Hibernate: alter table "users" add constraint UK_862ken6gv1xtf7oauhcclgxcc unique ("resetToken")
Hibernate: alter table "users" add constraint UK_r43af9ap4edm43mmtq01oddj6 unique ("username")

Hibernate: select user0_."id" as id1_4_, user0_."email" as email2_4_, user0_."password" as password3_4_, user0_."resetToken" as resetTok4_4_, user0_."tokenExpires" as tokenExp5_4_, user0_."username" as username6_4_ from "users" user0_ where user0_."username"=? or user0_."email"=?

Hibernate: insert into `users` (`email`, `password`, `resetToken`, `tokenExpires`, `username`) values (?, ?, ?, ?, ?)

o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 42703
o.h.engine.jdbc.spi.SqlExceptionHelper   : The column name "id" was not found in this ResultSet.

org.hibernate.exception.SQLGrammarException: could not insert: [lolt.models.data.User];
org.postgresql.util.PSQLException: The column name "id" was not found in this ResultSet.


Code:
package lolt.models.data;

import java.util.*;

import javax.persistence.*;
import javax.persistence.Entity;
import javax.persistence.Table;

import org.hibernate.annotations.*;
import org.hibernate.annotations.Parameter;

import com.fasterxml.jackson.annotation.*;

@Entity
@Table(name = "profiles")
public class Profile
{

    @Id
    @JsonIgnore
    @GeneratedValue(generator = "generator")
    @Column(unique = true, nullable = false, name = "user_id")
    @GenericGenerator(name = "generator", strategy = "foreign", parameters = @Parameter(name = "property", value = "user"))
    Long                id;

    @PrimaryKeyJoinColumn
    @OneToOne(fetch = FetchType.LAZY)
    User                user;

    @Column(name = "firstname")
    String              firstName;

    @Column(name = "nickname")
    String              nickname;

    @Column(name = "lastname")
    String              lastName;

    @Lob
    @Column(name = "profileImage")
    byte[]              image;
   
    /* getters and setters */
}


Code:
package lolt.models.data;

import java.time.*;
import java.util.*;

import javax.persistence.*;
import javax.validation.constraints.*;

import org.hibernate.validator.constraints.*;

import com.fasterxml.jackson.annotation.*;

@Entity
@Table(name = "users")
public class User
{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(nullable = false, unique = true, name = "id")
    Long          id;

    @Length(min = 6, max = 200)
    @Column(nullable = false, unique = true, name = "username")
    @Size(min = 6, max = 200, message = "Username must be between 6 and 200 chars")
    String        username;

    @Length(min = 6, max = 200)
    @Column(nullable = false, name = "password")
    @Size(min = 6, max = 200, message = "Password must be between 6 and 200 chars")
    String        password;

    @Length(min = 6, max = 200)
    @Column(nullable = false, unique = true, name = "email")
    @Size(min = 6, max = 200, message = "Email must be between 6 and 200 chars")
    String        email;

    @Column(unique = true, name = "resetToken", length = 36)
    UUID          resetToken;

    @Column(name = "tokenExpires")
    LocalDateTime resetTokenTimeout;

    @JsonIgnore
    @OneToOne(fetch = FetchType.LAZY, mappedBy = "user", cascade = CascadeType.ALL)
    Profile       profile;
   
    public User(final String username, final String password, final String email)
    {
        super();
        this.username = username;
        this.password = password;
        this.email = email;
    }
   
    /* getters and setters here */
}


Code:
package lolt.repos;

import java.util.*;

import org.springframework.data.jpa.repository.*;

import lolt.models.data.*;

public interface UserRepository extends JpaRepository<User, Long>{}


Code:
System.out.println("b");
User start = new User(user.getUsername(), this.bcrypt.encode(user.getPassword()), user.getEmail());
System.out.println("c");
start = this.repo.save(start);
System.out.println("d"); // never reaches this...


Could any of you help me understand whats happening?


Last edited by stelar7 on Thu May 26, 2016 5:00 pm, edited 2 times in total.

Top
 Profile  
 
 Post subject: Re: Postgre/MySQL discrepancy
PostPosted: Thu May 26, 2016 3:05 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
I think you should add the mappings and the logs using the Code section this forum provides. This is much easier for anyone to read your post.


Top
 Profile  
 
 Post subject: Re: Postgre/MySQL discrepancy
PostPosted: Thu May 26, 2016 4:00 am 
Newbie

Joined: Wed May 25, 2016 12:57 pm
Posts: 2
Your application works with MySQL, because Hibernate uses the auto_increment type for id. MySQL itself sets the id column.
Code:
create table `users` (`id` bigint not null auto_increment,...

PostgreSQL doesn't have such type. The id column is inserted by Hibernate
Code:
create table "users" ("id"  bigserial not null,...

So the problem with annotations on the id field: probably, insertable = false
Code:
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(nullable = false, unique = true, updatable = false, insertable = false, name = "id")
Long id;

Try to use more simply variant
Code:
@Id
@GeneratedValue
@Column(name = "id")
Long id;

P.S. Please, never use every annotation you see. Keep annotations as simply as possible.


Top
 Profile  
 
 Post subject: Re: Postgre/MySQL discrepancy
PostPosted: Thu May 26, 2016 4:57 pm 
Newbie

Joined: Wed May 25, 2016 4:09 pm
Posts: 4
I tried doing what you said, and I'm sorry to inform you that it didn't change a thing.


Top
 Profile  
 
 Post subject: Re: Postgre/MySQL discrepancy
PostPosted: Fri May 27, 2016 1:51 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
If you want to use PostgreSQL, it better to use the SEQUENCE generator instead:

Code:
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE)
@Column(nullable = false, unique = true, name = "id")
Long          id;


If you want portability, and don't mind the extra penalty cost of using row-level locks, you can also use a TABLE generator. But if you're just porting the application from MySQL to PostgreSQL, you should know that sequences allow JDBC batch updates, while IDENTITY does not.


Top
 Profile  
 
 Post subject: Re: Postgre/MySQL discrepancy
PostPosted: Sat May 28, 2016 9:45 am 
Newbie

Joined: Wed May 25, 2016 4:09 pm
Posts: 4
Changing from IDENTITY to AUTO seems like it worked. Thanks for your help.


Top
 Profile  
 
 Post subject: Re: Postgre/MySQL discrepancy
PostPosted: Sun May 29, 2016 2:00 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
That's what ladynev suggested you, and you said it didn't change a thing. By default, @GeneratedValue uses AUTO.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 8 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.