Inserting data into tables using Spring, Hibernate, JPA

Question

I'm trying to import data into tables created from Entities. This is the error that is generating.

My schema:

Hibernate: create table pledges (id integer not null auto_increment, amount double precision, date datetime, project_id integer, user_id integer, primary key (id))
Hibernate: create table projects (id integer not null auto_increment, pledge_amount double precision, date datetime, description_project longtext, image varchar(255), project_name varchar(255), required_money double precision, status integer, user_id integer, primary key (id))
Hibernate: create table users (id integer not null auto_increment, credit double precision, email varchar(80), full_name varchar(45), password varchar(255), primary key (id))

Rest of my logs:

Hibernate: alter table pledges add constraint UKhy4wjumf9bhip4v5hamiakkpn unique (user_id, project_id)
Hibernate: alter table users add constraint UK_6dotkott2kjsp8vw4d0m25fb7 unique (email)
Hibernate: alter table pledges add constraint FKrhpj4pdxfa62tffq87i06jkiv foreign key (project_id) references projects (id)
Hibernate: alter table pledges add constraint FKmsyjxxpylu6epd5wmsdi9i9u8 foreign key (user_id) references users (id)
Hibernate: alter table projects add constraint FKhswfwa3ga88vxv1pmboss6jhm foreign key (user_id) references users (id)
2016-11-19 21:15:31.966  INFO 12876 --- [           main] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000476: Executing import script '/import.sql'
2016-11-19 21:15:31.971 ERROR 12876 --- [           main] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000388: Unsuccessful: INSERT INTO projects VALUES (1, 200, CURRENT_TIMESTAMP , 'some description', 'pathtoimage', 'ProjectTransformers', 500, 1, 1)
2016-11-19 21:15:31.971 ERROR 12876 --- [           main] org.hibernate.tool.hbm2ddl.SchemaExport  : Referential integrity constraint violation: "FKHSWFWA3GA88VXV1PMBOSS6JHM: PUBLIC.PROJECTS FOREIGN KEY(USER_ID) REFERENCES PUBLIC.USERS(ID) (1)"; SQL statement:
INSERT INTO projects VALUES (1, 200, CURRENT_TIMESTAMP , 'some description', 'pathtoimage', 'ProjectTransformers', 500, 1, 1) [23506-193]
2016-11-19 21:15:31.972 ERROR 12876 --- [           main] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000388: Unsuccessful: INSERT INTO pledges VALUES (1, 200, CURRENT_TIMESTAMP , 1, 1)
2016-11-19 21:15:31.974 ERROR 12876 --- [           main] org.hibernate.tool.hbm2ddl.SchemaExport  : Referential integrity constraint violation: "FKRHPJ4PDXFA62TFFQ87I06JKIV: PUBLIC.PLEDGES FOREIGN KEY(PROJECT_ID) REFERENCES PUBLIC.PROJECTS(ID) (1)"; SQL statement:
INSERT INTO pledges VALUES (1, 200, CURRENT_TIMESTAMP , 1, 1) [23506-193]
2016-11-19 21:15:31.974  INFO 12876 --- [           main] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000230: Schema export complete
2016-11-19 21:15:32.024  INFO 12876 --- [           main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
2016-11-19 21:15:32.634  WARN 12876 --- [           main] .t.AbstractTemplateResolverConfiguration : Cannot find template location: classpath:/templates/ (please add some templates or check your Thymeleaf configuration)
2016-11-19 21:15:32.765  INFO 12876 --- [           main] o.s.j.e.a.AnnotationMBeanExporter        : Registering beans for JMX exposure on startup
2016-11-19 21:15:32.778  INFO 12876 --- [           main] i.c.c.application.CrowdfundApplication   : Started CrowdfundApplication in 3.817 seconds (JVM running for 4.188)
2016-11-19 21:15:32.779  INFO 12876 --- [       Thread-2] s.c.a.AnnotationConfigApplicationContext : Closing org.springframework.context.annotation.AnnotationConfigApplicationContext@181ce95: startup date [Sat Nov 19 21:15:29 GMT 2016]; root of context hierarchy
2016-11-19 21:15:32.780  INFO 12876 --- [       Thread-2] o.s.j.e.a.AnnotationMBeanExporter        : Unregistering JMX-exposed beans on shutdown
2016-11-19 21:15:32.780  INFO 12876 --- [       Thread-2] j.LocalContainerEntityManagerFactoryBean : Closing JPA EntityManagerFactory for persistence unit 'default'

User.java

package ie.cit.crowdfund.application.entity;

import javax.persistence.*;
import java.util.DoubleSummaryStatistics;
import java.util.List;

@Entity(name = "users")
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    @Column(length = 45)
    private String fullName;

    @Column(length = 80, unique = true)
    private String email;

    @Column
    private String password;

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "user")
    private List<Project> projects;

    @OneToMany(fetch = FetchType.EAGER, mappedBy = "user")
    private List<Pledge> pledges;

    @Column
    private Double credit;

    public User() {
    }

    public User(String fullName, String email, String password) {
        this.fullName = fullName;
        this.email = email;
        this.password = password;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getFullName() {
        return fullName;
    }

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

    public String getEmail() {
        return email;
    }

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

    public List<Project> getProjects() {
        return projects;
    }

    public void setProjects(List<Project> projects) {
        this.projects = projects;
    }

    public List<Pledge> getPledges() {
        return pledges;
    }

    public void setPledges(List<Pledge> Pledges) {
        this.pledges = Pledges;
    }

    public String getPassword() {
        return password;
    }

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

    public Double getCredit() {
        return credit;
    }

    public void setCredit(Double credit) {
        this.credit = credit;
    }

}

Project.java

package ie.cit.crowdfund.application.entity;

import ie.cit.crowdfund.application.utils.NewFormat;
import org.hibernate.annotations.Type;

import javax.persistence.*;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

@Entity(name = "projects")
public class Project {


    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    @Column
    private double requiredMoney;

    @Column
    @Type(type = "text")
    private String descriptionProject;

    @Column
    private String image;

    @Column
    private double Pledge_amount;

    @Column
    private Timestamp date;

    @Enumerated
    private Status status;

    @Column(name = "project_name")
    private String name;


    @OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL, mappedBy = "project")
    private List<Pledge> PledgeList = new ArrayList<>();

    @ManyToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @JoinColumn(name = "user_id")
    private User user;


    public Project() {
    }

    public Project(User user, double requiredMoney, String descriptionProject, String image, double Pledge_amount, Timestamp date, Status status, String name, List<Pledge> PledgeList) {
        this.user = user;
        this.requiredMoney = requiredMoney;
        this.descriptionProject = descriptionProject;
        this.image = image;
        this.Pledge_amount = Pledge_amount;
        this.date = date;
        this.status = status;
        this.name = name;
        this.PledgeList = PledgeList;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public User getUserId() {
        return user;
    }

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

    public double getrequiredMoney() {
        return requiredMoney;
    }

    public void setrequiredMoney(double requiredMoney) {
        this.requiredMoney = requiredMoney;
    }

    public String getdescriptionProject() {
        return descriptionProject;
    }

    public void setdescriptionProject(String descriptionProject) {
        this.descriptionProject = descriptionProject;
    }

    public String getImage() {
        return image;
    }

    public void setImage(String image) {
        this.image = image;
    }

    public double getPledge_amount() {
        return PledgeList.stream().mapToDouble(Pledge::getAmount).sum();
    }

    public void setPledge_amount(double Pledge_amount) {
        this.Pledge_amount = Pledge_amount;
    }

    public Timestamp getDate() {
        return date;
    }

    public void setDate(Timestamp date) {
        this.date = date;
    }

    public Status getStatus() {
        return status;
    }

    public void setStatus(Status status) {
        this.status = status;
    }

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public List<Pledge> getPledgeList() {
        return PledgeList;
    }

    public void setPledgeList(List<Pledge> PledgeList) {
        this.PledgeList = PledgeList;
    }

    public double percentPledge() {
        double percent = getPledge_amount() / requiredMoney * 100;
        return NewFormat.roundNumber(percent);
    }

    public boolean finishedProject() {
//        if (percentPledge() >= FINISHED)
//        {
//            return true;
//        }
//        else {
//            return false;
//        }
        return true;
    }

    public String dateFormat() {
        return "" + date.getDate() + "-" + (date.getMonth() + 1) + "-" + (date.getYear() + 1900);
    }
    @Override
    public String toString() {
        return "Project{" +
                "id=" + id +
                ", user=" + user +
                ", requiredMoney=" + requiredMoney +
                ", descriptionProject='" + descriptionProject + '\'' +
                ", image='" + image + '\'' +
                ", Pledge_amount=" + Pledge_amount +
                ", date=" + date +
                ", status='" + status + '\'' +
                ", name='" + name + '\'' +
                ", PledgeList=" + PledgeList +
                '}';
    }
}

Pledge.java

package ie.cit.crowdfund.application.entity;

import javax.persistence.*;
import java.sql.Timestamp;

@Entity(name = "pledges")
@Table(name = "pledges", uniqueConstraints = @UniqueConstraint(columnNames = {"user_id", "project_id"}))
public class Pledge {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;

@ManyToOne
@JoinColumn(name = "user_id")
private User user;

@Column
private double amount;

@ManyToOne
@JoinColumn(name = "project_id")
private Project project;

@Column
private Timestamp date;


public Pledge() {
}

public Pledge(User user, double amount, Project project, boolean approved, Timestamp date) {
    this.user = user;
    this.amount = amount;
    this.project = project;
    this.date = date;
}

public Project getProject() {
    return project;
}

public void setProject(Project project) {
    this.project = project;
}

public int getId() {
    return id;
}

public void setId(int id) {
    this.id = id;
}

public User getUser() {
    return user;
}

public void setUser(User user) {
    this.user = user;
}

public double getAmount() {
    return amount;
}

public void setAmount(double amount) {
    this.amount = amount;
}

public Project getProjectId() {
    return project;
}

public void setProjectId(Project project) {
    this.project = project;
}

public Timestamp getDate() {
    return date;
}

public void setDate(Timestamp date) {
    this.date = date;
}

public String setDateFormat() {
    return "" + date.getDate() + "-" + (date.getMonth() + 1) + "-" + (date.getYear() + 1900);
}

}

This is the import.sql that I'm running.

INSERT INTO projects VALUES (1, 200, CURRENT_TIMESTAMP , 'some description', 'pathtoimage', 'ProjectTransformers', 500, 1, 1)
INSERT INTO users VALUES (1, 1000, '[email protected]', 'Jenna Smith', '1234')
INSERT INTO pledges VALUES (1, 200, CURRENT_TIMESTAMP , 1, 1)

I think there is something wrong with the import.sql but I don't know what. Am I missing something ? Is something wrong ?

The tables are described in the error the first 3 rows. It is automatically generated by hibernate. — Pavel Vasilev
– Pavel Vasilev, Commented Nov 19, 2016 at 22:32
Are you using hibernate ddl in order to create your tables automatically? — Moshe Arad
– Moshe Arad, Commented Nov 19, 2016 at 22:39
Yes. If the table doesn't exist it creates it automatically based on the entities . — Pavel Vasilev
– Pavel Vasilev, Commented Nov 19, 2016 at 22:42
If you look at the first 3 rows at the error message I'm getting. Hibernate creates the table I want them to be. The main error is: Referential integrity constraint violation: "FKHSWFWA3GA88VXV1PMBOSS6JHM: PUBLIC.PROJECTS FOREIGN KEY(USER_ID) REFERENCES PUBLIC.USERS(ID) (1)"; SQL statement: — Pavel Vasilev
– Pavel Vasilev, Commented Nov 19, 2016 at 22:51

Matteo Baldi · Accepted Answer · 2016-11-19 22:41:29Z

1

In your import.sql, insert first the father table, than the related children:

INSERT INTO users VALUES (1, 1000, '[email protected]', 'Jenna Smith', '1234');
INSERT INTO projects VALUES (1, 200, CURRENT_TIMESTAMP , 'some description', 'pathtoimage', 'ProjectTransformers', 500, 1, 1);
INSERT INTO pledges VALUES (1, 200, CURRENT_TIMESTAMP , 1, 1);

answered Nov 19, 2016 at 22:41

Matteo Baldi

5,83811 gold badges44 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Pavel Vasilev Over a year ago

Oh god ... , I knew it was something simple. Didn't even notice. Thanks that fixed the problem.

Vishrant Over a year ago

in order to make this work, make sure hibernate.hbm2ddl.auto is set to create or create-drop if it is set to update import.sql will not work

Collectives™ on Stack Overflow

Inserting data into tables using Spring, Hibernate, JPA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related