HANA on SCP Neo: How can I create a HANA schema with JPA/Eclipselink?

[former_member563095]

Hi,

Im working on a Java application with JPA and EclipseLink as provider and from there I would like to create the tables and the schema in which they should be stored. Creating the tables works fine the following way:

@Entity
@Table(name = "customer")
public class Customer implements Serializable {

   private static final long serialVersionUID = 1L;

   @Id
   private Long id;
   private String firstName;
   private String lastName;

    public Customer() {
    }

    public Long getId() {
        return id;
    }

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

    public String getFirstName() {
        return firstName;
    }

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

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }
} 

After that my tables get created as expected in the default schema of my user and I can use them. What also works is when I create a schema by myself and use it in my source code the following way:

@Table(name = "customer", schema = "shop")

The tables will be created in that schema like expected and I can use them. The problem is if I don't create the schema by myself and and want do the following:

@Table(name = "customer", schema = "shop")

Nothing will be created here, not the table and not the schema and I would like to be able to create a schema in the same way I create the tables, is that possible? What I was trying to get it working until now are the following steps:

1) Tried to provide a catalog and a schema, problem remains, not even the tables were created
2) Found this problem/question and played around with different values for the target database, problem remains, not even the tables were created
3) Looked out for examples on the web and github, but in those examples only the table name is provided, not the schema

So I hope someone can tell me if this is possible at all and how it can be done, until then I already wish you a nice weekend.

Kind regards

Maximilian

[Ivan-Mirisola · Accepted solution]

Hi Maximilian,

Have you read the chapter 16 on this John's Hopkins University Course on JEE?

If you look into the persistence configuration, you will find that DDL generation is only relative to the entities - not other database artifacts (such as Schemas). Thus, even though you'll find settings for schema generation (Such as SCHEMA_GENERATION_CREATE_ACTION), they are only meant to create tables and insert data by using the sql script of your own - so it basically replaces the DDL table generation of EclipseLink with your own SQL commands.

The course shows how you could create/drop/popuplate the tables using Maven. This ensures that whatever the application does during startup it will find the database in a consistent manner while you are performing your unit tests.

However, for integrated testing and productive environments, one should have such db artifacts already setup before deploying an application - including the Schema.

There is an API you could try using to create the schema for you in Java - but I would discourage its usage (simply because it would require you to know the dialect to setup security on the schema, etc):

Map properties = new HashMap();
properties.put("javax.persistence.schema-generation.scripts.action", "drop-and-create");
properties.put("javax.persistence.schema-generation.scripts.drop-target", "jpa21-generate-schema-no-connection-drop.jdbc");
properties.put("javax.persistence.schema-generation.scripts.create-target", "jpa21-generate-schema-no-connection-create.jdbc");
    
Persistence.generateSchema("shop", properties);

The above is what one would use in a JUnit test scenario - which will create the database tables, load data, etc - all in the shop schema. This example has been extracted from the EclipseLink wiki page

Best regards,
Ivan

[former_member563095 · Accepted solution]

Hi Ivan,

thanks for sharing the link and your insights in the answer. I had a guess that this does not work but maybe it just was a config thing, I missed and wanted to make sure that's not the case. So now that is cleared! 🙂 So I will create the schema by myself and then use it inside of my JPA-Entites. Thanks again for your time and help here!

Kind regards

Maximilian