CODEWITHSUNDEEP
javahibernate
newday
newday

Reputation: 3878

jdbc.exceptions.jdbc4.MySQLSyntaxErrorException

I have two DAO Hibernate mapping classes. Relationship is one-to-many bidirectional relationship. When I try to add a new setting I get, the following error.

jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key, updated_by, updated_time, appType_id) values ('Mxxd', '2015-12-17 11:09:' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:400)
    at com.mysql.jdbc.Util.getInstance(Util.java:383)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:980)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3847)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3783)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2447)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1901)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2113)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2049)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2034)
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:147)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:208)
    at org.hibernate.id.IdentityGenerator$GetGeneratedKeysDelegate.executeAndExtract(IdentityGenerator.java:96)
    at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:58)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3032)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3558)
    at org.hibernate.action.internal.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:98)
    at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:492)
    at org.hibernate.engine.spi.ActionQueue.addResolvedEntityInsertAction(ActionQueue.java:197)
    at org.hibernate.engine.spi.ActionQueue.addInsertAction(ActionQueue.java:181)
    at org.hibernate.engine.spi.ActionQueue.addAction(ActionQueue.java:216)
    at org.hibernate.event.internal.AbstractSaveEventListener.addInsertAction(AbstractSaveEventListener.java:334)
    at org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:289)
    at org.hibernate.event.internal.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:195)
    at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:126)
    at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:209)
    at org.hibernate.event.internal.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:55)
    at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:194)
    at org.hibernate.event.internal.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:49)
    at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:90)
    at org.hibernate.internal.SessionImpl.fireSave(SessionImpl.java:715)
    at org.hibernate.internal.SessionImpl.save(SessionImpl.java:707)
    at org.hibernate.internal.SessionImpl.save(SessionImpl.java:702)
    at com.ips.tvadmindao.services.TvAppTypeService.addAppType(TvAppTypeService.java:53)
    at com.xxx.tvadmindao.services.Application.main(Application.java:50)
2015-12-17 11:09:37 WARN  SqlExceptionHelper:144 - SQL Error: 1064, SQLState: 42000
2015-12-17 11:09:37 ERROR SqlExceptionHelper:146 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key, updated_by, updated_time, appType_id) values ('Kalla', '2015-12-17 11:09:' at line 1
Exception in thread "main" 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.id.IdentityGenerator$GetGeneratedKeysDelegate.executeAndExtract(IdentityGenerator.java:96)
    at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:58)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3032)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3558)
    at org.hibernate.action.internal.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:98)
    at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:492)
    at org.hibernate.engine.spi.ActionQueue.addResolvedEntityInsertAction(ActionQueue.java:197)
    at org.hibernate.engine.spi.ActionQueue.addInsertAction(ActionQueue.java:181)
    at org.hibernate.engine.spi.ActionQueue.addAction(ActionQueue.java:216)
    at org.hibernate.event.internal.AbstractSaveEventListener.addInsertAction(AbstractSaveEventListener.java:334)
    at org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:289)
    at org.hibernate.event.internal.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:195)
    at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:126)
    at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:209)
    at org.hibernate.event.internal.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:55)
    at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:194)
    at org.hibernate.event.internal.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:49)
    at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:90)
    at org.hibernate.internal.SessionImpl.fireSave(SessionImpl.java:715)
    at org.hibernate.internal.SessionImpl.save(SessionImpl.java:707)
    at org.hibernate.internal.SessionImpl.save(SessionImpl.java:702)
    at com.xxx.tvadmindao.services.TvAppTypeService.addAppType(TvAppTypeService.java:53)
    at com.xxx.tvadmindao.services.Application.main(Application.java:50)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key, updated_by, updated_time, appType_id) values ('MenukaD', '2015-12-17 11:09:' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:400)
    at com.mysql.jdbc.Util.getInstance(Util.java:383)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:980)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3847)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3783)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2447)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1901)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2113)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2049)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2034)
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:147)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:208)
    ... 23 more

Here with I have added the method that use to add settingstypes.

public boolean addAppType() {
        //boolean success = false;
        // add app and setting together
        String appType_name = "AAAA";
        String created_by = "Mxxxd";

        TvAppsType_Settings setting = new TvAppsType_Settings();
        setting.setCreated_by(created_by);
        setting.setCreated_time(new LocalDateTime());
        setting.setKey("Host2");
        setting.setUpdated_by(created_by);
        setting.setUpdated_time(new LocalDateTime());
        TvAppsType apptype2 = (TvAppsType)session.get(TvAppsType.class, 4);

        setting.setApptypes(apptype2);
        session.save(setting);
        if (!HibernateUtil.INSTANCE.commitTransaction())
            return false;
        logger.info("Exited : " + "addAppType(String appType_name, String created_by)");
        return true;
    }

There are DAO classes too.

@Entity
@Table(name = "tvapps_type_settings")
public class TvAppsType_Settings {
    @Id  //primary key
    @GeneratedValue(strategy = GenerationType.IDENTITY)//strategy for generating auto generated number
    @Column(name = "settingType_id", nullable = false)
    private int  settingType_id;

    /*@Column(name = "appType_id",  insertable=false , updatable=false)
    private int appType_id;*/

    @ManyToOne(cascade= CascadeType.ALL)
    @JoinColumn(name= "appType_id",insertable=false, updatable=false)
    private TvAppsType apptypes;

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

    @Type(type = "org.jadira.usertype.dateandtime.joda.PersistentLocalDateTime")
    @Column(name = "created_time", nullable = false)
    private LocalDateTime created_time;

    @Column(name = "created_by", nullable = false)
    private String created_by;

    @Type(type = "org.jadira.usertype.dateandtime.joda.PersistentLocalDateTime")
    @Column(name = "updated_time", nullable = false)
    private LocalDateTime updated_time;

    @Column(name = "updated_by", nullable = false)
    private String updated_by;

    public TvAppsType getApptypes() {
        return apptypes;
    }

    public void setApptypes(TvAppsType apptypes) {
        this.apptypes = apptypes;
    }

    public int getSettingType_id() {
        return settingType_id;
    }

    public void setSettingType_id(int settingType_id) {
        this.settingType_id = settingType_id;
    }

    public String getKey() {
        return key;
    }

    public void setKey(String key) {
        this.key = key;
    }

    public LocalDateTime getCreated_time() {
        return created_time;
    }

    public void setCreated_time(LocalDateTime created_time) {
        this.created_time = created_time;
    }

    public String getCreated_by() {
        return created_by;
    }

    public void setCreated_by(String created_by) {
        this.created_by = created_by;
    }

    public LocalDateTime getUpdated_time() {
        return updated_time;
    }

    public void setUpdated_time(LocalDateTime updated_time) {
        this.updated_time = updated_time;
    }

    public String getUpdated_by() {
        return updated_by;
    }

    public void setUpdated_by(String updated_by) {
        this.updated_by = updated_by;
    }

}

TvAppsType DAO class.

@Entity
@Table(name = "tvapps_type")
public class TvAppsType {
    @Id  //primary key
    @GeneratedValue(strategy = GenerationType.IDENTITY)//strategy for generating auto generated number
    @Column(name = "appType_id", nullable = false)
    private int appType_id;

    @Column(name = "appType_name", nullable = false)
    private String appType_name;

    @OneToMany(cascade = CascadeType.ALL)
    @JoinColumn(name = "appType_id",nullable = false)
    private List<TvAppsType_Settings> tvsettings = new ArrayList<TvAppsType_Settings>();

    @Type(type = "org.jadira.usertype.dateandtime.joda.PersistentLocalDateTime")
    @Column(name = "created_time", nullable = false)
    private LocalDateTime created_time;

    @Column(name = "created_by", nullable = false)
    private String created_by;

    @Type(type = "org.jadira.usertype.dateandtime.joda.PersistentLocalDateTime")
    @Column(name = "updated_time", nullable = false)
    private LocalDateTime updated_time;

    @Column(name = "updated_by", nullable = false)
    private String updated_by;

    public List<TvAppsType_Settings> getTvsettings() {
        return tvsettings;
    }

    public void setTvsettings(List<TvAppsType_Settings> tvsettings) {
        this.tvsettings = tvsettings;
    }

    public int getAppType_id() {
        return appType_id;
    }

    public void setAppType_id(int appType_id) {
        this.appType_id = appType_id;
    }

    public String getAppType_name() {
        return appType_name;
    }

    public void setAppType_name(String appType_name) {
        this.appType_name = appType_name;
    }

    public LocalDateTime getCreated_time() {
        return created_time;
    }

    public void setCreated_time(LocalDateTime created_time) {
        this.created_time = created_time;
    }

    public String getCreated_by() {
        return created_by;
    }

    public void setCreated_by(String created_by) {
        this.created_by = created_by;
    }

    public LocalDateTime getUpdated_time() {
        return updated_time;
    }

    public void setUpdated_time(LocalDateTime updated_time) {
        this.updated_time = updated_time;
    }

    public String getUpdated_by() {
        return updated_by;
    }

    public void setUpdated_by(String updated_by) {
        this.updated_by = updated_by;
    }

}

Can you guys help me to fix this error?

Upvotes: 0

Views: 791

Answers (2)

Ishamael
Ishamael

Reputation: 12795

key is a reserved keyword in MySQL. Hibernate doesn't account for it, and pastes your column names as is into the query, hence the error.

There are two ways to fix it. A more sane is to rename your column to be something else, like setting_key or something similar.

If you are not OK with renaming it, there's a hacky solution:

@Column(name = "`key`")
private String key;

Note that the name of the column is now in backticks, and since hibernate just pastes it into the query, it will be interpreted by MySQL as key

Upvotes: 1

Ctx
Ctx

Reputation: 18410

Your MySQL-Server doesn't like the fieldname key, and your persistence provider doesn't handle that correctly. One option would be to rename the field key to something else, if that is possible for you.

Alternatively,

@Column(name="`key`")

should work.

Upvotes: 1

Related Questions