I noticed the fastnate code relies on the @mapsid annotation to present when creating onetoone and manytomany relationships. JPA i think does not use this, instead only hibernate uses this.

Is there a work around for this for JPA entities. When my entities have something like this,
@OneToMany(fetch = FetchType.LAZY)
@JoinColumns({
@joincolumn(name = "Field_ID", referencedColumnName = "Field_ID", insertable=false , updatable=false),
@joincolumn(name = "Customer_ID", referencedColumnName = "Customer_ID", insertable=false , updatable=false) })

it fails with a ModelException - misses MapId for a singular property

Hibernate offers support for spatial (geographic) attributes, see
Hibernate ORM 5.0 User Guide - Spatial.

Fastnate should support these attributes, too.

I have a Collection property on my entity with annotation @OneToMany(..., mappedBy = "incomDistrScheme", ...)
Using EntitySqlGenerator (verson 1.4.0) drops exception: org.fastnate.generator.context.ModelException: Unsupported "mapped by" property for public java.util.Collection ...
By debugging i see, that in line 679 of PluralProperty class calling of "entityClass.getProperties().get(this.mappedBy)" returns null
Cause of entityClass.getProperties() collection do not have property "incomDistrScheme", but it have property "incomDtrScheme"
I guess, that error is in line 164 of AccessStyle class:
this.name = Introspector.decapitalize(getter.getName().replaceAll("^get|is", ""));
You don't have to use replaceAll() here. You need to use replaceFirst().

When using Hibernates @Any annotation on a JPA property, Fastnate supports only @AnyMetaDef definitions attached to the field itself:

public class Entity extends BaseEntity {
    @Any
    @AnyMetaDef(idType = "long", metaType = "string", metaValues = {
        @MetaValue(targetEntity = ExampleEntity.class, value = "EE"), 
        @MetaValue(targetEntity = SecondEntity.class, value = "SE")
    })
    private BaseEntity anyEntity;
}

But Hibernate supports named @AnyMetaDefs at arbitrary positions for example:

@AnyMetaDef(name="anyMetaDefExample", idType = "long", metaType = "string", metaValues = {
    @MetaValue(targetEntity = ExampleEntity.class, value = "EE"), 
    @MetaValue(targetEntity = SecondEntity.class, value = "SE")
})
public class Entity extends BaseEntity {
    @Any(metaDef = "anyMetaDefExample")
    private BaseEntity anyEntity;
}

Fastnate should support these globally defined @AnyMetaDefs as well.

Hello liefke.. thanks for your interesting project here. I am just wondering if there is any way/guide to generate the data.sql using gradle instead of maven? I just happened to start a springboot using gradle and would be nice if fastnate provide a way to use it too. Looking forward to hearing from you.

Currently the default names of tables and columns are derived from the JPA specification.

As Hibernate offers the option to change these names by defining another NamingStrategy , it would be nice if FastNate would support the same configuration option.

Please refer the below stack overflow question for more details.

https://stackoverflow.com/questions/59584321/how-to-append-schema-name-in-the-sql-generated-by-fastnate

Writing two entities of two different subclasses in the same class hierarchy with default inheritance type SINGLE_TABLE results in an ArrayIndexOutOfBoundsException:

java.lang.ArrayIndexOutOfBoundsException: 6
    at org.fastnate.generator.statements.ConnectedStatementsWriter$PreparedInsertStatement.setColumnValue(ConnectedStatementsWriter.java:113)
    at org.fastnate.generator.context.PrimitiveProperty.addInsertExpression(PrimitiveProperty.java:235)
    at org.fastnate.generator.EntitySqlGenerator.writeInserts(EntitySqlGenerator.java:268)
    at org.fastnate.generator.EntitySqlGenerator.write(EntitySqlGenerator.java:194)
    at org.fastnate.generator.EntitySqlGenerator.write(EntitySqlGenerator.java:168)
    ...

The current implementation only supports IDs with exactly one value.
IdClass and EmbeddedId are not supported.

As the SQL for inserting foreign keys that reference an entity with more than one ID column is totally different to the current implementation, this is somehow a bigger change.

Implementation idea:

INSERT INTO table (primitiveValue, foreignKey1, foreignKey2) SELECT 'abc', id1, id2 FROM table2 WHERE ...

If Fastnate would be able to create a Liquibase file, it would be easier to build a change set by comparing two different versions of that file.

I am currently using Fastnate in tandem with SpringBoot + Flyway, to preload data into certain tables when creating/upgrading new databases. One major hurdle that I needed to clear was to have Fastnate automatically infer the correct dialect when the application properties changed the vendor of the database. (ie. Local dev mode uses H2 whereas production deployment uses Oracle.) The SpringBoot - Flyway bootstrapping mechanism does not provide access to the various beans that I might be able to specify the dialect with, as Flyway's Java migrations only allow the execution of SQL (adhering to the vendor dialect) via a provided DataSource. This is the same issue that Fastnate presents.

A solution could infer the SQL dialect from the DataSource, which is possible, as shown here:
https://github.com/spring-projects/spring-boot/blob/master/spring-boot-autoconfigure/src/main/java/org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration.java#L204

I know that JdbcUtils is a Spring class, so perhaps this isn't as cut and dry. However, slightly modifying that code, I was able to extract the DatabaseDriver type from the DataSource, and then implement a switch-case statement to match up the proper Fastnate SQL GeneratorDialect class to the determined DatabaseDriver.

Currently only GenerationType.SEQUENCE and GenerationType.IDENTITY are supported.

Especially for MySQL GenerationType.TABLE is required as well.

Sample:
@SiZe(max = Constants.SIZE2000)
@column(name = "valueAsString")
private String stringValue;

When a parent in a parent-child relationship has assigned a manual ID (the ID is not generated) and a child references such a parent, the generated SQL may result in a "Referential Integrity Error". This is because the SQL insert statement of the child references the parent before the insert statement for the parent is written.

Version: 1.4.0-SNAPSHOT (the buildNumber is 6)

If we have a collection of embedded elements with a "not null" entity reference, we need to postpone the inserts into the collection table until the referenced entity is written. Otherwise the SQL generation will fail with "Required property entity was not set for ...".

Example:

@Embeddable
public class CollectionsTestElement {

  @NotNull
  @ManyToOne
  private CollectionsTestEntity entity;

}

...
@ElementCollection
private Set<CollectionsTestElement> elements = new HashSet<>();
...

Fastnate does not support the following attribute:

@Column(columnDefinition = "VARCHAR")
@Convert(converter = StringListConverter.class)
private List<String> strings = new ArrayList<>(); 

With StringListConverter like:

@Converter
public class StringListConverter implements AttributeConverter<List<String>, String> {

    public String convertToDatabaseColumn(final List<String> list) {
  	return String.join(",", list);
    }

    public List<String> convertToEntityAttribute(final String joined) {
      	return new ArrayList<>(Arrays.asList(joined.split(",")));
    }

} 

So i found that the AbstractCsvDataProvider is very nice, except that it must load its data from a File, which is validated for its existence and all that. Too bad because I like the ability to load from a CSV, but want to be able to pull this content from a file embedded on a JAR, instead of a standalone file.

This would be nice...
public AbstractCsvStreamDataProvider(final URL resourceURL)

Currently Fastnate can only insert new entities.

Existing entities can be referenced with EntitsSqlGenerator.markExistingEntities. But this works only if either the ID is given, or the entity has a unique property. Referencing any other entity is not possible. And updates of these entities are not possible as well.

Idea

Use an approach like JPQL for finding entities and support updates of the result:

Bean bean = sqlGenerator.find(Bean.class, "SELECT b FROM Bean b WHERE b.text = 'Old text'");
bean.setText("New text");
sqlGenerator.write(bean);

I have following entity

@Entity
public class KeyValue {

    @Id
    private Long id;
      
    public String key;
    public String value;

}

The entity is not creating because key is a reserved keyword. Fastnate on the other hand is generating this line for importing via CSV file:

INSERT INTO UserSettings (id, key, user_id, value) VALUES (2, 'mykey', 1, 'myvalue');

I am not sure whether it is the correct behaviour or not.

We need to run the unit tests with Microsoft SQL Server.

Given a collection of As (of type Embeddable), which also contains an Embeddable B in its definition, it would be desirable for the Fastnate to produce proper insert statements.

Mapping example:

@OrderColumn
@ElementCollection
@AttributeOverrides({ 
    @AttributeOverride(name = "value.valueDE", column = @Column(name = "valueOverridenFromA1", nullable = false)),
    @AttributeOverride(name = "value.valueEN", column = @Column(name = "valueOverridenFromA2")) 
})
private final List<A> values;

@Embeddable
public class A {

    @Embedded
    @AttributeOverrides({
        @AttributeOverride(name = "valueDE", column = @Column(name = "valueOverriden1", nullable = false)),
        @AttributeOverride(name = "valueEN", column = @Column(name = "valueOverriden2")) 
    })
    private B value;

@Embeddable
public class B {

    private String valueDE;

    private String valueEN;

}

Please advise, how to generated Update SQL from Entity. In your github code, there is update statement which can generate update sql. I find way to generate insert sql from entity as:-
StringWriter result = new StringWriter();
Notes notes = new Notes();
notes.setNote("123");
notes.setNoteOid("noteOid");
EntitySqlGenerator sqlGenerator = new EntitySqlGenerator(result);
sqlGenerator.write(notes);

System.out.println( result.toString());

When InheritanceType.JOINED is used in combination with GenerationType.IDENTITY, the IDs in references are not calculated correctly (the reference uses a difference which is way to big).

Hibernate supports to quote all identifiers, when setting the property globally_quoted_identifiers to true. This ensures that even database keywords may be used for property or entity names.

This enhancement should support the property too and use quoted identifiers when set.

PostgreSQL has a command for bulk import:
COPY {columns} FROM {file}.

This is much faster than any plain SQL insert (at least twice as fast), but it needs a special data format and it needs files that are accessible to the server.

Fastnate should support to write that bulk format for a really fast import of huge data samples in PostgreSQL.

When using a ConnectedStatementsWriter one can't see the created SQL statements without a logging connection. Even the Hibernate property show_sql doesn't work, as Fastnate doesn't use the Hibernate resources for writing the statements. It would be nice if one could turn on logging of statements for debugging purposes.

Good Morning

I have three questions related to fastnate.

The first one is if the Fastnate can just be used to populate the database.

The second one is how I can achieve this because I've read all the documentation and even after adding all the maven dependencies and creating the class none data.sql file was generated.

LINK I

LINK II

The third one is if it's possible to generate the whole schema of the database as it's possible with the Spring JPA with the configuration below:

spring.jpa.properties.javax.persistence.schema-generation.create-source=metadata spring.jpa.properties.javax.persistence.schema-generation.scripts.action=create spring.jpa.properties.javax.persistence.schema-generation.scripts.create-target=create.sql

I've seen a person that mentioned fastnate here but reading it wasn't clear if fastnate can do this or not.

LINK III

Thanks for the support

This is currently not possible:

@Entity
@Inheritance(strategy = InheritanceType.JOINED)
public class Superclass {
    @Id
    private Long id;

    @OneToOne
    private Subclass entity;
}

@Entity
public class Subclass extends Superclass {
}

generator.write(new Subclass());

Stacktrace:

org.fastnate.generator.context.ModelException: Superclass.entity does not reference an ID column in class Subclass
    at org.fastnate.generator.context.EntityClass.getIdColumn(EntityClass.java:716)
    ...

Currently the @DefaultValue annotation on temporal attributes is written 'as is' with a simple string quoting to the database. It is not possible to use a database independent format. And it is not possible to reference CURRENT_TIMESTAMP or CURRENT_DATE with the ConnectedStatementsWriter.

Fastnate should use a database independent format for temporal attributes.

Fastnate creates the wrong column names for the following relationship:

public class FirstEntity {
  @ManyToMany
  private Set<SecondEntity> secondEntities;
}

public class SecondEntity {
  @ManyToMany(mappedBy="secondEntities")
  private Set<FirstEntity> firstEntities;
}

Current workaround: Use a @JoinTable annotation for secondEntities and define names for the (inverse) columns.

In the constructor the autocommit of a given connection is forcibly set according to GeneratorDialect#isFastInTransaction() configuration. However, if the supplied connection was already used within a transaction any action prio fastnate is commited.

I would expect, that fastnate respects the transaction.

Could you please also check whether this code is a duplicate of EntityImporter#importData(connection)

If the allocationSize of SequenceGenerator and TableGenerator is bigger than one, Fastnate should use all allocated IDs - not only the last allocated value.

While most of the SQL generated by Fastnate does not depend on the JPA library you use, there are some specialties we offer for Hibernate:

1. hibernate_sequence is used as sequence name, if no sequence name is given for an id with GenerationType.Sequence
2. The strategy for GenerationType.AUTO is taken from Hibernate
3. Support for Formula, Any and ManyToAny annotations

Because of the last point we require Hibernate to be on the classpath during compilation.

The focus of this enhancement is to extract the Hibernate specific part into a standalone module, which you need to link, if you want to use these features. In a second step we could than focus on the creation of such modules for other JPA implementations.

If you want to specify additional files that are relevant for SQL generation, you need to build your own DataChangeDetector.

It should be easy to support in addition a pattern based configuration option:

<configuration>
    <relevantFiles>
        <relevantFile>**/*.properties</relevantFile>
    </relevantFiles>
</configuration>

In the generator the method AccessStyle.*.getDeclaredAttributes(Class) should not grab synthetic fields/getters.

Fastnate does not allow the following sequence definition:

@MappedSuperclass
public abstract class AbstractEntity {
    @Id
    @SequenceGenerator(name = "entitySequence", sequenceName = "EntitySeq")
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "entitySequence")
    private Long id;
}

@Entity
public class FirstEntity extends AbstractEntity {
  //...
}

@Entity
public class SecondEntity extends AbstractEntity {
  //...
}

Fastnate will use a separate sequence for FirstEntity and for SecondEntity, instead of using the same sequence for both entities.

Hello,

I recently tryed to use the Fastnate API to populate the contents of a DB (H2, created at app start with Flyway), and ran into a bug where it appears that the Fastnate API cannot generate values for generically defined IDs.

My project's ORM leverages classes from the Spring API, which extend (after several levels) AbstractPersistable.java (source) as their @MappedSuperclass.

I found that since the ID is defined as the following,

@MappedSuperclass
public abstract class AbstractPersistable<PK extends Serializable> implements Persistable<PK> {
	@Id @GeneratedValue private PK id;

... the Fastnate IdGenerator choked when trying to determine the class type to be assigned. This was my resulting error message:

org.fastnate.generator.context.ModelException: Can't handle number class for generated value: interface java.io.Serializable
	at org.fastnate.generator.context.IdGenerator.createNextValue(IdGenerator.java:66)
	at org.fastnate.generator.context.GeneratedIdProperty.addInsertExpression(GeneratedIdProperty.java:69)
	at org.fastnate.generator.EntitySqlGenerator.writeInserts(EntitySqlGenerator.java:203)
	at org.fastnate.generator.EntitySqlGenerator.write(EntitySqlGenerator.java:144)
	at org.fastnate.generator.EntitySqlGenerator.write(EntitySqlGenerator.java:118)
	at org.fastnate.generator.EntitySqlGenerator.write(EntitySqlGenerator.java:160)
	at org.fastnate.data.csv.AbstractCsvDataProvider.writeEntities(AbstractCsvDataProvider.java:333)

I believe that the underlying problem is here. It appears that while this function successfully identifies the class from which the @Id is declared, the process does not sustain the generics that truly define the ID's type for a concrete class that would extend it, causing the code to think that the ID is of type Serializable, when it should be Long (as in my case).

Up to now all SQL statements were written to an SQL file.

This has the advantage, that no database connection is necessary at build time and that we don't need to attach the initial data sources (CSV files or other databases) to the shipped artifact.

But it has the disadvantage, that we need to write an SQL file for every supported database dialect. If we have different SQL files for different target environments, we will even need to write a file for each of them.
And we can't change how the SQL files are executed (as long as we use the initial import of the JPA provider).

It would be nice if both ways are supported. And everyone can choose, which is the best for him.

EntitySqlGenerator e = new EntitySqlGenerator(this.context);
e.write(courseEntity);
When creating an object of EntitySqlGenerator this error comes

"Cannot instantiate the type EntitySqlGenerator"

How can I solve it?

Hello again!

Catched the InheritanceType.JOINED bug (v.1.4.1).
My class structure consists of 3 classes:
@entity class (1)
extends @MappedSuperclass class (2)
extends @entity class (3) with @Inheritance(strategy = InheritanceType.JOINED)
So, I'm trying to generate inserts by class (1) objects and catching NPE on null discriminaror column.

The problem, I see, is in EntityClass.findHierarchyRoot method. At line 623 the condition "if (parentDescription.getInheritanceType() == InheritanceType.JOINED)" returns true, so the parent class (3) and its context defines correctly.
But.
In this "if" condition body I don't see the row like "this.inheritanceType = parentDescription.inheritanceType;", so "inheritanceType" value of my target class (1) keeps its default value, equals to "SINGLE_TABLE".

And then the EntitySqlGenerator.writeInserts method (lines 259-260) has not null "discriminator" value, but null "classDescription.getDiscriminatorColumn()" result

Fastnate ignores the @JoinColumn declaration in the following override:

@AssociationOverride(name = "simpleEntities", joinTable = @JoinTable(name = "MyTable", 
        joinColumns = @JoinColumn(name = "join_id"), 
        inverseJoinColumns = @JoinColumn(name = "inverseJoin_id"))) })

Only a @JoinColumn defined like this ist found:

@AssociationOverride(joinColumns = @JoinColumn(name = ...)) 

results in unique key constraint exception when running a combined script

If an entity contains a nullable @Version field, that field should be initialized with 0 in INSERT.

Example:

@Entity
public Example {
    @Id
    private Long id;

    @Version
    private Long version;
}

Workaround: Initialize the version field in application code.

<configuration>
   <sqlFile>${project.build.outputDirectory}/import/module-init.sql</sqlFile>
   ...
</configuration>

fails with java.io.FileNotFoundException when the folder named import does not exist

While the additional @Inject annotation is already supported with its own DataProviderFactory (see #21), the always available @Resource annotation is ignored - which could be used in the same way.

We could support @Resource in the DefaultDataProviderFactory and in the InjectDataProviderFactory. In addition the DefaultDataProviderFactory could support @PostConstruct, too.

Up to now Fastnate reads mapping information from annotations only. As annotations are usually the preferred option, this should be enough for most projects. Nevertheless some projects might still use mapping files for historic reason or when legacy POJOs are used.

I cannot use LocalTime nor LocalDate with fastnate.

Hi,

is there any plan of supporting @GenericGenerator annotation?

Thanks for the answer

There is already support for writing now into the SQL files (as reference to GeneratorDialect.NOW).
But there is no option to write today or now + 5 days.

With the wrapper class "RelativeDate", we could archieve that:

// Set "now"
persistedBean.setDate(RelativeDate.NOW);
// Set "today"
persistedBean.setDate(RelativeDate.today);
// Set yesterday
persistedBean.setDate(new RelativeDate(RelativeDate.today, - DateUtils.MILLIS_PER_DAY));

To inject data of another DataProvider into a new DataProvider we currently only support to offer the other one in the constructor of the new one:

public class PersonData extends AbstractDataProvider {

	private final OrganisationData organisations;

	public PersonData(OrganisationData organisations) {
		this.organisations = organisations;
	}
	...

It would be nice, if we could support @Inject on fields as well.