Table creation with JPA

Database
Enthusiast

Table creation with JPA

I have JPA code that loads some data in a database.  It works flawlessly on PostGresSQL.  I'm having a lot of trouble getting the database created cleanly.

For some reason, for every table, another table is created with the same columns and the table name is the same except in upper case with a suffix of "_0".  In one case, two additional uppercase tables are created with the same name and suffixes "_0" and "_4".

 

eg. my table name is "lineup".  In addition to creating the "lineup" table, a table "LINEUP_0" is created.  And also a "lineup_seq" table is created for sequences.  PostGreSQL puts all the Sequences in a separate folder.  But that's just a nice to have.

 

My class generatlly have the following annotations

 

@Entity
@Table(name = "lineup")
public class Lineup
{
    @Id
    @SequenceGenerator( name = "lineupSeq", sequenceName = "lineup_seq", allocationSize = 20, initialValue = 100 )
    @GeneratedValue( strategy = GenerationType.SEQUENCE, generator = "lineupSeq" )
    private long pk;
    
    @Column(name = "load_date")
    private Date iLoadDate;
    
    @Column(name = "name")
    private String iName;

    @Column(name = "lineup_id")
    private int iLineUpId;
    
    @Column(name = "has_no_legacy_billcodes")
    private boolean iHasNoLegacyBillCodes;
    
    @ManyToOne( fetch=FetchType.LAZY)
    @JoinColumn(name = "headend_pk", referencedColumnName = "pk")
    private Headend iHeadend;
    
    ...
}

 

The JPA properties file looks like

packagesToScan=com.test.loader
javax.persistence.jdbc.url=jdbc:teradata://<server_name>/database=<dbname>,dbs_port=1025
javax.persistence.jdbc.user=uid
javax.persistence.jdbc.password=**bleep**
javax.persistence.jdbc.driver=com.teradata.jdbc.TeraDriver
hibernate.dialect=org.hibernate.dialect.TeradataDialect
hibernate.show_sql=false 
hibernate.format_sql=false
hibernate.hbm2ddl.auto=create-drop
hibernate.min_size=5
hibernate.max_size=20
hibernate.timeout=500
hibernate.max_statements=50
hibernate.idle_test_period=2000
hibernate.jdbc.batch_size=1000
hibernate.order_inserts=true

Any idea what's going on?  If there is a better forum in Teradata to post this, please point me to it.  There aren't a lot of questions on StackOverflow on Teradata.  But I could try there if I can't get an answer here.

 

Thanks.

 

 


Accepted Solutions
Enthusiast

Re: Table creation with JPA

I fixed the problem with the extraneous tables being created for the foreign keys.  I also fixed the problem of the indexes not being created.

 

I had to create my own Dialect subclass making changes to the ones provided by Hibernate.  Since I'm relatively new to Hibernate and definitely Teradata, I'm not submitting the code into the open source library.  But I'm posting the class here in case someone else attempting to use Hibernate with Teradata finds it useful.

 

To see the reasoning behind why the XXX_n tables are created, check out Dave Wellman's excellent explanation in this post.

 

package com.mod.teradata;

import java.util.Iterator;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.hibernate.boot.Metadata;
import org.hibernate.boot.model.relational.QualifiedNameImpl;
import org.hibernate.dialect.Dialect;
import org.hibernate.dialect.Teradata14Dialect;
import org.hibernate.engine.jdbc.env.spi.JdbcEnvironment;
import org.hibernate.mapping.Column;
import org.hibernate.mapping.Index;
import org.hibernate.tool.schema.internal.StandardIndexExporter;
import org.hibernate.tool.schema.spi.Exporter;

/**
 * This class handles the problems encountered while using the hibernate
 * supplied Teradata Dialect for generation of Tables.
 * 
 *  <p>
 *  The following problems were encountered and are solved by this class.
 *  
 *  <ul>
 *  <li>For every foreign key in a table, another table is created with an
 *  upper case name concatenated with a number.  eg. PERSON_0, PERSON_4.
 *  See {@link #getAddForeignKeyConstraintString(String, String[], String, String[], boolean) getAddForeignKeyConstraintString()} 
 *  for an explaination and the fix.
 *  <li>No indexes created with the <code>@Index</code> or 
 *  <code>@Table indexes</code> attribute are created with the 
 *  <code>org.hibernate.dialect.TeradataDialect</code>
 *  <li>An exception is thrown that <code>hibernate.dialect</code> property 
 *  is not set, even though it is set in the <code>jpa.properties</code> when 
 *  the <code>org.hibernate.dialect.Teradata14Dialect</code> dialect is used.
 *  See {@link ModTeradataDialect} for an explanation on these errors and
 *  the solution.
 *  </ul> 
 *
 */
public class ModTeradataDialect extends Teradata14Dialect
{
    private static Logger cLogger = LogManager.getLogger(ModTeradataDialect.class);
    
    private ModTeradataIndexExporter iModTeradataIndexExporter;
    
    /**
     * This class generates the SQL for the index generation.  
     * 
     * <p>
     * If the <code>org.hibernate.dialect.TeradataDialect</code> dialect is  
     * used, no indexes are created.  This is because in Teradata, to create an 
     * index, the column name has to be specified right after the index name.
     * 
     * <p>
     * The <code>org.hibernate.dialect.Teradata14Dialect</code> dialect was
     * supposed to fix the problem by generating the correct order of the 
     * SQL.  However, there is a bug in the <code>getSqlCreateStrings</code>
     * method where it attempts to load the Dialect from the system properties
     * rather than the JPA properties.  So to use the 
     * <code>org.hibernate.dialect.Teradata14Dialect</code> dialect, we would 
     * also have to pass in a <code>-Dhibernate.dialect</code> option in 
     * addition to setting the value in JPA properties.
     * 
     * <p>
     * This class fixes the problem in the 
     * <code>org.hibernate.dialect.Teradata14Dialect</code> dialect by using 
     * the dialect specified in the JPA properties. 
     *
     */
    private static class ModTeradataIndexExporter extends StandardIndexExporter implements Exporter<Index> 
    {
        private Dialect iDialect;

        public ModTeradataIndexExporter(Dialect dialect) 
        {
            super(dialect);
            iDialect = dialect;
        }

        /**
         * Overridden to use the Dialect set in the JPA properties rather
         * than the one set in the <code>System</code> properties 
         */
        @Override
        public String[] getSqlCreateStrings(Index index, Metadata metadata) 
        {
            final JdbcEnvironment jdbcEnvironment = metadata.getDatabase().getJdbcEnvironment();
            final String tableName = jdbcEnvironment.getQualifiedObjectNameFormatter().format(
                    index.getTable().getQualifiedTableName(),
                    jdbcEnvironment.getDialect()
            );

            final String indexNameForCreation;
            if (iDialect.qualifyIndexName() ) {
                indexNameForCreation = jdbcEnvironment.getQualifiedObjectNameFormatter().format(
                        new QualifiedNameImpl(
                                index.getTable().getQualifiedTableName().getCatalogName(),
                                index.getTable().getQualifiedTableName().getSchemaName(),
                                jdbcEnvironment.getIdentifierHelper().toIdentifier( index.getName() )
                        ),
                        jdbcEnvironment.getDialect()
                );
            }
            else {
                indexNameForCreation = index.getName();
            }

            StringBuilder colBuf = new StringBuilder("");
            boolean first = true;
            Iterator<Column> columnItr = index.getColumnIterator();
            while ( columnItr.hasNext() ) {
                final Column column = columnItr.next();
                if ( first ) {
                    first = false;
                }
                else {
                    colBuf.append( ", " );
                }
                colBuf.append( ( column.getQuotedName( jdbcEnvironment.getDialect() )) );
            }
            colBuf.append( ")" );

            final StringBuilder buf = new StringBuilder()
                    .append( "create index " )
                    .append( indexNameForCreation )
                    .append(  "(" + colBuf  )
                    .append( " on " )
                    .append( tableName );

            return new String[] { buf.toString() };
        }
    }
    
    
    public ModTeradataDialect()
    {
        super();
        iModTeradataIndexExporter =  new ModTeradataIndexExporter(this);
    }
    
    /**
     * Returns the Index exporter from this class.
     */
    @Override
    public Exporter<Index> getIndexExporter() 
    {
        return iModTeradataIndexExporter;
    }
    
    /**
     * Do we need to qualify index names with the schema name?
     *
     * @return boolean
     */
    public boolean qualifyIndexName() 
    {
        // Qualifying the index name with the schema name does not work on 
        // Teradata. Attempting to prefix the index name with the schema
        // name fails.
        return false;
    }
    
    
    /**
     * This method creates foreign key constraints using soft Referential 
     * Integrity.
     * 
     * <p>
     * Teradata creates an additional table for every foreign key to manage 
     * hard RI. To prevent these tables from being created, the foreign key
     * alter statement has to specify the <code>NO CHECK OPTION</code> option.
     * 
     * <p>
     * This method gets the alter SQL statement generated by the Dialect class
     * and inserts the NO CHECK option
     * 
     * eg. changes
     * <br><code>
     * alter table person add constraint fk_address foreign key (address_pk) references address
     * </code> 
     * <br>to
     * <br><code>
     * alter table person add constraint fk_address foreign key (address_pk) references WITH NO CHECK OPTION address
     * </code>
     *  
     */
    @Override
    public String getAddForeignKeyConstraintString(
            String theConstraintName,
            String[] theForeignKey, 
            String theReferencedTable,
            String[] thePrimaryKey, 
            boolean theReferencesPrimaryKey)
    {
        String theQuery = super.getAddForeignKeyConstraintString(
                                                    theConstraintName, 
                                                    theForeignKey,
                                                    theReferencedTable, 
                                                    thePrimaryKey, 
                                                    theReferencesPrimaryKey);
        
        String thePattern = "references";
        String theReplacement = thePattern + " WITH NO CHECK OPTION"; 
        String[] theTokens = theQuery.split(thePattern);
        if (theTokens.length == 2) {
            theQuery = theTokens[0] + theReplacement + theTokens[1];
        } else {
            String msg = "There were '" + theTokens.length + " tokens after " +
                         "splitting the Foreign Key Constraint String '" + 
                         theQuery + "' using the pattern '" + thePattern + 
                         "'.  Not modifying the foreign key for soft RI ";
            cLogger.warn(msg);
        }
        
        return theQuery;
    }
}

 

1 ACCEPTED SOLUTION
12 REPLIES
Apprentice

Re: Table creation with JPA

Hi,

 

(This is probably the right forum to use).

 

Normally, tables with suffixes of "_n" are error tables, usually to do with 'referential integrity' constraints. However, I don't recall seeing those built as the result of a CREATE TABLE command (only ALTER TABLE ADD...).

 

Is it possible for you to trace your processing, specifically extracting (and posting here) the SQL that is generated? Doing that will shows us what is being sent to the dbms and therefore why you're ending up with these tables.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Table creation with JPA

Hi Dave,

 

I created a new project to simply the logs with just 2 classes and 2 tables.  Person and Address, with a FK between Address and Person.  I end up with 5 tables created.

  • address
  • address_seq
  • person
  • PERSON_0
  • person_seq

This first time the program runs with an empty database, this is what I see.

create table reconcile.address (pk NUMERIC(18,0) not null, city VARCHAR(255), line1 VARCHAR(255), state VARCHAR(255), primary key (pk))
create multiset table  reconcile.address_seq (next_val NUMERIC(18,0))
insert into reconcile.address_seq values ( 100 )
create table reconcile.person (pk NUMERIC(18,0) not null, name VARCHAR(255), address_pk NUMERIC(18,0), primary key (pk))
create multiset table  reconcile.person_seq (next_val NUMERIC(18,0))
insert into reconcile.person_seq values ( 100 )
alter table reconcile.person add constraint FKq9u2o044vep9c9ya9vw5ko1ux foreign key (address_pk) references reconcile.address

The table PERSON_0 is created for the foreign key. as we can see from the next time I run the program

 

alter table reconcile.person drop constraint FKq9u2o044vep9c9ya9vw5ko1ux
drop table reconcile.address
drop table reconcile.address_seq
drop table reconcile.person
drop table reconcile.person_seq
create table reconcile.address (pk NUMERIC(18,0) not null, city VARCHAR(255), line1 VARCHAR(255), state VARCHAR(255), primary key (pk))
create multiset table  reconcile.address_seq (next_val NUMERIC(18,0))
insert into reconcile.address_seq values ( 100 )
create table reconcile.person (pk NUMERIC(18,0) not null, name VARCHAR(255), address_pk NUMERIC(18,0), primary key (pk))
create multiset table  reconcile.person_seq (next_val NUMERIC(18,0))
insert into reconcile.person_seq values ( 100 )
alter table reconcile.person add constraint FKq9u2o044vep9c9ya9vw5ko1ux foreign key (address_pk) references reconcile.address

▬[WARN ] ExceptionHandlerLoggedImpl.java:27 org.hibernate.tool.schema.internal.ExceptionHandlerLoggedImpl - GenerationTarget encountered exception accepting command : Unable to execute command [alter table reconcile.person add constraint FKq9u2o044vep9c9ya9vw5ko1ux foreign key (address_pk) references reconcile.address]
org.hibernate.tool.schema.spi.CommandAcceptanceException: Unable to execute command [alter table reconcile.person add constraint FKq9u2o044vep9c9ya9vw5ko1ux foreign key (address_pk) references reconcile.address]
...
Caused by: java.sql.SQLException: [Teradata Database] [TeraJDBC 16.00.00.28] [Error 5303] [SQLState HY000] Error table 'reconcile.person_0' already exists.
...

I'm not sure why the PERSON_0 table is created in the first place.  I don't know if I can change the annotation for the "@ManyToOne" annotation can be changed to avoid that.

 

Here are the two classes.

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.SequenceGenerator;

@Entity(name="person")
public class Person
{
    @Id
    @SequenceGenerator( name = "personSeq", sequenceName = "person_seq", allocationSize = 1, initialValue = 100 )
    @GeneratedValue( strategy = GenerationType.SEQUENCE, generator = "personSeq" )
    private long pk;
    
    @Column(name = "name")
    private String iName;

    @ManyToOne( fetch=FetchType.LAZY)
    @JoinColumn(name = "address_pk", referencedColumnName = "pk")
    private Address iAddress;

    /**
     * Default constructor Required for JPA
     */
    Person()
    {
    }
    
    public Person(String theName)
    {
        iName = theName;
    }
    
    public void addAddress(Address theAddress)
    {
        iAddress = theAddress;
    }
    
    public long getPrimaryKey()
    {
        return pk;
    }

    @Override
    public String toString()
    {
        return iName;
    }
}
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.SequenceGenerator;

@Entity (name="address")
public class Address
{
    @Id
    @SequenceGenerator( name = "addressSeq", sequenceName = "address_seq", allocationSize = 1, initialValue = 100 )
    @GeneratedValue( strategy = GenerationType.SEQUENCE, generator = "addressSeq" )
    private long pk;
    
    @Column(name = "line1")
    private String iLine1;

    @Column(name = "city")
    private String iCity;
    
    @Column(name = "state")
    private String iState;
    
    /**
     * Default constructor Required for JPA
     */
    Address()
    {
    }
    
    public Address(String theLine1, String theCity, String theState)
    {
        iLine1 = theLine1;
        iCity = theCity;
        iState = theState;
    }

    public long getPrimaryKey()
    {
        return pk;
    }
    
    @Override
    public String toString()
    {
        return iLine1 + ", " + iCity + ", " + iState;
    }
}

Any help wouldbe appreciated.

Supporter

Re: Table creation with JPA

Hi Milind,

 

Drop person_o table first. Then rewrite your alter statement as shown below:

alter table reconcile.person add constraint FKq9u2o044vep9c9ya9vw5ko1ux foreign key (address_pk) references WITH NO CHECK OPTION reconcile.address;

I have added "WITH NO CHECK OPTION". Without it alter creates a error table.

Hope it helps.

 

Thanks,
Rohan Sawant

Enthusiast

Re: Table creation with JPA

Thanks Rohan. But I'm not writing the SQL code. It's getting generated by Teradata's jpa dialect from the annotation.

@ManyToOne( fetch=FetchType.LAZY) @JoinColumn(name = "address_pk", referencedColumnName = "pk")
private Address iAddress;

I'll Google and see if there is something I can add in the annotation or better yet some global setting that will create all foreign key references with the NO CHECK option. Thanks for the pointer.
Apprentice

Re: Table creation with JPA

Hi,

 

Using the 'no check option' may be a solution for you, but you need to be aware of the consequences. More on that later.

 

To specifically answer one of your questions "why is the person_0 table created?"

     This is created as a result of the ALTER TABLE statement that was executed, adding 'standard' referential integrity. When doing that the error table (table "person_0") is always created. If you drop that referential integrity constraint this error table is not dropped. You have to do that in using code.

 

Implications of "no check option".

If you use this option then (as Rowan says) the error table will not be created. However, the DBMS will not check your data when you insert, update or delete rows; meaning that you can have 'bad' data inserted into the tables. In your example you could get a row added to the 'person' table with a non-null value for column 'address_pk' which does not exist in table 'address'.

   This form of RI is known as 'soft ri', which has it uses (I've used it many times) but does not provide the data protection that 'standard ri' does.

 

If you simply want to avoid the error table you could also use "WITH CHECK OPTION" which is 'batch ri'. This will not create an error table, it will check the data and again it has it's uses.

 

I understand that you're going through the JPA dialect, but firstly: "What are you trying to achieve?" Once that is know then we can say what Teradata option will be best suited to you and from there we can see what JPA options/annotations are required to achieve it.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Table creation with JPA

Dave,

 

Thanks for the explanation. What I'm doing in my app is performing an audit. It loads data in these tables and then processes them to identify discrepancies across some systems. I'm using JPA to create the tables before the data gets written and read. This allows me to annotate my business entity classes and ensures that schema changes I make in terms of field additions or renames in the class get reflected in the database without having to write SQL. I'll also be using Liquibase to take care of changes like dropping columns or removing old renamed columns.

 

I'm not sure if that's what you were looking for I  terms of explanation.  If not, I can elaborate.

 

PostGreSql also creates sequence tables, but they are not displayed in the Tables folder. I assume it's also keeping some referential integrity tables, which again are not visible.

 

Now that I know what these tables are, I suppose I can just leave them be, especially if I can figure out a way to filter them out. They are noise that prevent me from viewing the operational schema.

 

But while I normally prefer a strong RI when I'm writing JDBC code to ensure that the database maintains data integrity and prevents any bugs that could have slipped in my code from violating it, I'm not sure if I need it with JPA.  Of course, stronger RI also ensures that someone doesn't just execute SQL code to violate it either.  So on the whole I suppose it may be better to have it.

Apprentice

Re: Table creation with JPA

Hi,

 

Thanks for the explanation.

 

In my experience, most Teradata customer sites do not use either standard or batch ri (the two types of ri that are enforced). A lot of customers use 'soft ri'. This is for a number of reasons, mainly around the fact that most Teradata systems are not classed as operational systems and the data is processed via ETL code which cleans, validates transforms etc. Added to that is a robust security capability which means that most users cannot change data - and hence 'break' referential integrity.

 

If it is only 'your' application/processing which is loading and reading these tables then I wonder if you really need ri at all. Will 'others' have the ability and time to manipulate the data between you loading them and using them to check and audit the data?

 

Even if the data is there long enough, can you use security to control who can manipulate that data? No problem if others can read it, just don't let them Insert/Update/Delete.

 

Does that help?

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Table creation with JPA

We are actually using Teradata as an operational database.  The application will be reading and writing the data so yes, we could go with a soft RI.  There is no intervening process or user making changes to the data.  In production, for sure, there is only read access.  And any changes made to the data in development would be intentional and not a problem.

 

The current version of the solution is using Stored Procedures and DDL to generate the tables.  I don't see any _0 tables there.  I'm getting the DDL from the SQL developers to see the FK statement.  But they told me that they haven't used any NO CHECK option in their statements.  Once I see the DDL, I'll get a better idea.

 

So, given that we would want soft RI, is there a JPA property that we can use to create that? 

Apprentice

Re: Table creation with JPA

Hi,

 

Sorry but I've no idea about the JPA properties. If you can point me to a list of them then I might be able to translate from JPA to Teradata. (I did a quick search via Google but no joy)

 

Your current solution may not be using the 'NO CHECK' option and still not producing any "_0" tables. These are only produced when adding ri using an ALTER TABLE statement. If the ri is added as part of the CREATE TABLE statement then the "_0" table will not be created.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com