How persist Map<String, Embeddable type> using hibernate in mySQL

Question

I want to use Hibernate(4.3.6 Final) to persist a Map with basic type as key and non entity embeddable compound type value as value.

I didn't have any problem with basic type as key and value I done everything in the same way and it was ok. I have problem when I change value of map to embeddable compound type as I show it below.

But i getting error:

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'hash' in 'field list'

Could You help what I doing wrong or what I miss?

Table prepared to persist my map

image of table user_address

My Map to persist in entity USER (fragment)

@ElementCollection
@CollectionTable(name="USER_ADDRESS", joinColumns=@JoinColumn(name="USER_ID"))
@MapKeyColumn(name="ADDRESS_TYPE")
@AttributeOverrides({@AttributeOverride(name="addressLine1", column=@Column(name="USER_ADDRESS_LINE_1")),
        @AttributeOverride(name="addressLine2", column=@Column(name="USER_ADDRESS_LINE_2"))})
private Map<String,Address> address = new HashMap<String, Address>();

Embeddable compound type ADDRESS (fragment)

@Embeddable
public class Address {

@Column(name="ADDRESS_LINE_1")
private String addressLine1;

@Column(name="ADDRESS_LINE_2")
private String addressLine2;

@Column(name="CITY")
private String city;

@Column(name="STATE")
private String state;

@Column(name="ZIP_CODE")
private String zipCode;

public Address() {
}

Main method public class Application {

public static void main(String[] args) {
    Session session = HibernateUtil.getSessionFactory().openSession();

    try {
        Transaction transaction = session.beginTransaction();


        User user = new User();

        Address address = new Address();
        Address address2 = new Address();
        setAddressFields(address);
        setAddressFields2(address2);
        user.getAddress().put("ONE",address);
        user.getAddress().put("TWO",address2);
        setUserFields(user);

        session.save(user);

        transaction.commit();

    } catch (Exception e) {
        e.printStackTrace();
    }finally{
        session.close();
        HibernateUtil.getSessionFactory().close();
    }


}

private static void setUserFields(User user) {
    user.setAge(22);
    user.setBirthDate(new Date());
    user.setCreatedBy("kmb");
    user.setCreatedDate(new Date());
    user.setEmailAddress("kmb385");
    user.setFirstName("Kevin");
    user.setLastName("bowersox");
    user.setLastUpdatedBy("kevin");
    user.setLastUpdatedDate(new Date());
}

private static void setAddressFields(Address address) {
    address.setAddressLine1("Line 1");
    address.setAddressLine2("Line 2");
    address.setCity("New York");
    address.setState("NY");
    address.setZipCode("12345");
}

private static void setAddressFields2(Address address) {
    address.setAddressLine1("Line 3");
    address.setAddressLine2("Line 4");
    address.setCity("Corning");
    address.setState("NY");
    address.setZipCode("12345");
}

}

Full text of error:

DEBUG - insert into USER_ADDRESS (USER_ID, hash, value, ADDRESS_LINE_1, ADDRESS_LINE_2, CITY, STATE, ZIP_CODE) values (?, ?, ?, ?, ?, ?, ?, ?) Hibernate: insert into USER_ADDRESS (USER_ID, hash, value, ADDRESS_LINE_1, ADDRESS_LINE_2, CITY, STATE, ZIP_CODE) values (?, ?, ?, ?, ?, ?, ?, ?) DEBUG - could not execute statement [n/a] com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'hash' in 'field list' at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:939) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009) at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5094) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:208) at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:62) at org.hibernate.persister.collection.AbstractCollectionPersister.recreate(AbstractCollectionPersister.java:1311) at org.hibernate.action.internal.CollectionRecreateAction.execute(CollectionRecreateAction.java:67) at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:463) at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:349) at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:350) at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:56) at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1222) at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:425) at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.beforeTransactionCommit(JdbcTransaction.java:101) at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.commit(AbstractTransactionImpl.java:177) at com.infiniteskills.data.Application.main(Application.java:33) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at com.intellij.rt.execution.application.AppMain.main(AppMain.java:144) WARN - SQL Error: 1054, SQLState: 42S22 ERROR - Unknown column 'hash' in 'field list' INFO - HHH000010: On release of batch it still contained JDBC statements DEBUG - HHH000420: Closing un-released batch DEBUG - Releasing JDBC connection DEBUG - Released JDBC connection DEBUG - HHH000031: Closing DEBUG - Implicitly destroying ServiceRegistry on de-registration of all child ServiceRegistries org.hibernate.exception.SQLGrammarException: could not execute statement at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:80) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:211) at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:62) at org.hibernate.persister.collection.AbstractCollectionPersister.recreate(AbstractCollectionPersister.java:1311) at org.hibernate.action.internal.CollectionRecreateAction.execute(CollectionRecreateAction.java:67) at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:463) at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:349) at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:350) at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:56) at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1222) at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:425) at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.beforeTransactionCommit(JdbcTransaction.java:101) at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.commit(AbstractTransactionImpl.java:177) at com.infiniteskills.data.Application.main(Application.java:33) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at com.intellij.rt.execution.application.AppMain.main(AppMain.java:144) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'hash' in 'field list' at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:939) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009) at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5094) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:208) ... 17 more INFO - HHH000030: Cleaning up connection pool [jdbc:mysql://localhost:3306/ifinances] DEBUG - Implicitly destroying Boot-strap registry on de-registration of all child ServiceRegistries

The most interesting IMO fragment of error listening

Hibernate try to use sql insert with hash and value but of course I don't have these columns in my table.

DEBUG - insert into USER_ADDRESS (USER_ID, hash, value, ADDRESS_LINE_1, ADDRESS_LINE_2, CITY, STATE, ZIP_CODE) values (?, ?, ?, ?, ?, ?, ?, ?) Hibernate: insert into USER_ADDRESS (USER_ID, hash, value, ADDRESS_LINE_1, ADDRESS_LINE_2, CITY, STATE, ZIP_CODE) values (?, ?, ?, ?, ?, ?, ?, ?)

Thank you in advance for advices

QuestionMaker QuestionMaker · Accepted Answer · 2016-07-26T10:09:41

As i think i partialy found solution.

There is a problem with @AttributeOverride, I don't know why because I've used exactly the same notation to persist a Collection of Addresses(Embeddable compound type)

I will be very grateful for help why there is a problem when I try to override column names in case when I using a Map, and everything is ok when I try to persist a Collection of the same embeddable compound type(Address)

When I changed class Address to avoid override columns name, my Map was successfully persisted. In other words I have changed column names of Address type to correspond exactyly to the column names in USER_ADDRESS.

Changed compound embeddable type

Now column names correspond exactly to the column names in table to persist map

@Embeddable
public class Address {

    @Column(name="USER_ADDRESS_LINE_1")
    private String addressLine1;

    @Column(name="USER_ADDRESS_LINE_2")
    private String addressLine2;

    @Column(name="CITY")
    private String city;

        @Column(name="STATE")
    private String state;

    @Column(name="ZIP_CODE")
    private String zipCode;

    public Address() {
    }
...

Map to persist without Override

@ElementCollection
@CollectionTable(name="USER_ADDRESS", joinColumns=@JoinColumn(name="USER_ID"))
@MapKeyColumn(name="ADDRESS_TYPE")
@Columns(columns = {
        @Column(name="USER_ADDRESS_LINE_1"),
        @Column(name="USER_ADDRESS_LINE_2"),
        @Column(name="CITY"),
        @Column(name="STATE"),
        @Column(name="ZIP_CODE")
})
private Map<String,Address> address = new HashMap<String, Address>();

Result

select from table USER_ADDRESS after map has been saved

Hibernate: insert into USER_ADDRESS (USER_ID, ADDRESS_TYPE, USER_ADDRESS_LINE_1, USER_ADDRESS_LINE_2, CITY, STATE, ZIP_CODE) values (?, ?, ?, ?, ?, ?, ?) DEBUG - insert into USER_ADDRESS (USER_ID, ADDRESS_TYPE, USER_ADDRESS_LINE_1, USER_ADDRESS_LINE_2, CITY, STATE, ZIP_CODE) values (?, ?, ?, ?, ?, ?, ?) Hibernate: insert into USER_ADDRESS (USER_ID, ADDRESS_TYPE, USER_ADDRESS_LINE_1, USER_ADDRESS_LINE_2, CITY, STATE, ZIP_CODE) values (?, ?, ?, ?, ?, ?, ?) DEBUG - Done inserting collection: 2 rows inserted DEBUG - committed JDBC Connection DEBUG - HHH000420: Closing un-released batch DEBUG - Releasing JDBC connection DEBUG - Released JDBC connection DEBUG - HHH000031: Closing DEBUG - Implicitly destroying ServiceRegistry on de-registration of all child ServiceRegistries INFO - HHH000030: Cleaning up connection pool [jdbc:mysql://localhost:3306/ifinances] DEBUG - Implicitly destroying Boot-strap registry on de-registration of all child ServiceRegistries

How persist Map<String, Embeddable type> using hibernate in mySQL

1 Answers