0

I have been working developing on a tool to load/update data into Maria DB using Spring Boot and JPA/Hibernate. I want load data into a table with composite primary keys. Below is what I have tried

The Primary Key Embedded class, it consistes of two primary keys , key and language

@Embeddable
public class MessageResourcePK implements Serializable {
/**
 *
 */
private static final long serialVersionUID = -4226351093015653675L;

private String language;
private String key;

public MessageResourcePK() {}

public MessageResourcePK(String language, String key) {
    super();
    this.language = language;
    this.key = key;
}

/**
 * @return the language
 */
@Column(name = "LANGUAGE")
public String getLanguage() {
    return this.language;
}

/**
 * @param language the language to set
 */
public void setLanguage(String language) {
    this.language = language;
}

/**
 * @return the key
 */
@Column(name = "KEY")
public String getKey() {
    return this.key;
}

/**
 * @param key the key to set
 */
 public void setKey(String key) {
    this.key = key;
 }

 @Override
 public boolean equals(Object obj) {
    boolean resultat = false;

    if (obj == this) {
        resultat = true;
    } else {
        if (!(obj instanceof MessageResourcePK)) {
            resultat = false;
        } else {
            MessageResourcePK autre = (MessageResourcePK) obj;
            if (!this.language.equals(autre.language)) {
                resultat = false;
            } else {
                if (!this.key.equals(autre.key)) {
                    resultat = false;
                } else {
                    resultat = true;
                }
            }
        }
    }
    return resultat;
 }

 @Override
 public int hashCode() {
    return (this.language + this.key).hashCode();
 }

The actual table class

 @Entity
 @Table(name = "MESSAGE_RESOURCE")
 @IdClass(MessageResourcePK.class)
 public class MessageResourceTable implements 
 Comparable<MessageResourceTable> {
 /**
 *
 */
 private static final long serialVersionUID = -4226351093015653675L;

 private String language;
 private String key;
 private String message;

 /**
 * @return the language
 */
 @Id
 @Column(name = "LANGUAGE")
 public String getLanguage() {
    return this.language;
 }

 /**
  * @param language the language to set
 */
 public void setLanguage(String language) {
    this.language = language;
 }

 /**
 * @return the key
 */
 @Id
 @Column(name = "KEY")
 public String getKey() {
    return this.key;
 }

/**
 * @param key the key to set
 */
 public void setKey(String key) {
    this.key = key;
 }

/**
 * @return the message
 */
 @Column(name = "MESSAGE")
 public String getMessage() {
    return this.message;
 }

/**
 * @param message the message to set
 */
 public void setMessage(String message) {
    this.message = message;
 }

 @Override
 public int compareTo(final MessageResourceTable o) {
    return this.language.compareTo(o.getLanguage()) + 
 this.key.compareTo(o.getKey());
 }

The Repository JPA for this

@Repository("MessageResourceService")
@Transactional
public interface MessageResourceService extends 
JpaRepository<MessageResourceTable, MessageResourcePK>{

}

The findAll method works fine, but when I try to use save method, it generates the following query

update
    message_resource 
set
    message=? 
where
    key=? 
    and language=?

But in Maria DB it will not execute, I tried locally with the following query which works fine

update message_resource m set m.MESSAGE = 'jjjj' where m.`KEY`='someKey' and 
m.`LANGUAGE`='someLanguage'

As the query generated is wrong, it throws SQLGrammarException because infront of key there should be something like m.key, is there some solution for it. Please don't mark this as duplicate because, i searched through stackoverflow and nobody got the same case like me.

The application.properties file

## Spring DATASOURCE (DataSourceAutoConfiguration & DataSourceProperties)
spring.datasource.url = jdbc:mysql://localhost:3306/test
spring.datasource.username = root
spring.datasource.password = 


## Hibernate Properties
# The SQL dialect makes Hibernate generate better SQL for the chosen 
database
spring.jpa.properties.hibernate.dialect = 
 org.hibernate.dialect.MariaDBDialect

# Hibernate ddl auto (create, create-drop, validate, update)
spring.jpa.hibernate.ddl-auto = validate

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

spring.jpa.properties.hibernate.current_session_context_class
=org.springframework.orm.hibernate5.SpringSessionContext


spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
venkat
  • 442
  • 5
  • 17
  • Could you post the properties? Like the `application.properties` and the `persistence.xml` – vc73 Apr 10 '19 at 08:59
  • 2
    I think it's because `key` is a reserved word, so you have to quote it. Try this https://stackoverflow.com/a/14943679/3841161. Also having all the names in uppercase could be the source of your next problem. – coladict Apr 10 '19 at 09:02
  • @vc73 - Added the application.properties file – venkat Apr 10 '19 at 09:04
  • @coladict - Worked fine, thanks a lot, can you explain about that property? – venkat Apr 10 '19 at 09:17
  • It forces Hibernate to always use the quotation markers around database, schema, table, and field names when generating queries. It's necessary when you have table/field names that are also reserved words, which is best to avoid, but when there's over 900 reserved words, it's also pretty difficult. – coladict Apr 10 '19 at 09:22
  • @coladict - Thanks a lot, stuck there for half a day – venkat Apr 10 '19 at 10:42

0 Answers0