I am working on a Spring Web Application. I am using mysql database, but for my unit tests, I want to run them in H2 database.
Test specific application properties:
#Specific spring boot configuration for tests
spring.main.banner-mode=off
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.url=jdbc:h2:mem:skel;MODE=MYSQL
spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect
spring.datasource.user=sa
spring.datasource.password=
spring.jpa.hibernate.ddl-auto=create
spring.jpa.defer-datasource-initialization=true
endpoints.enabled=false
# enable test-profile
spring.profiles.active=test
As you can see, my database is in MODE=MYSQL, since my data.sql is in MySQL dialect.
But during initialization of of data.sql I get this error:
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement
"INSERT IGNORE INTO [*]user (username, password, first_name, last_name, enabled,
news_cycle, created_at, updated_at) VALUES('[email protected]',
'$2a$10$BFNo8gUTorQMtikcFbYVEeAPyX5iCn5BpKglp.eJ2DrFs.bNeXgEu', 'Admin', 'Adminoso',
'TRUE', 'NEVER', '2016-01-01 00:00:00', '2016-01-01 00:00:00')"; expected
"identifier"; SQL statement:
INSERT IGNORE INTO user (username, password, first_name, last_name, enabled,
news_cycle, created_at, updated_at) VALUES('[email protected]',
'$2a$10$BFNo8gUTorQMtikcFbYVEeAPyX5iCn5BpKglp.eJ2DrFs.bNeXgEu', 'Admin', 'Adminoso',
'TRUE', 'NEVER', '2016-01-01 00:00:00', '2016-01-01 00:00:00') [42001-212]
I suppose from this error you can see the SQL statement that is causing the problem, other statements in data.sql do not seem to cause issues. for example:
INSERT IGNORE INTO department (department_id, department_name, created_at,
updated_at) VALUES (1, 'Marketing', '2016-01-01 00:00:00', '2016-01-01 00:00:00');
My user entity:
import at.qe.skeleton.model.facility.Department;
import at.qe.skeleton.model.facility.Room;
import lombok.Getter;
import lombok.Setter;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;
import javax.persistence.*;
import java.time.LocalDateTime;
import java.util.Objects;
import java.util.Set;
/**
* Entity representing users.
*
* This class is part of the skeleton project provided for students of the
* courses "Software Architecture" and "Software Engineering" offered by the
* University of Innsbruck.
*/
@Getter
@Setter
@Entity
@EntityListeners(AuditingEntityListener.class)
public class User implements Comparable<User>{
@Id
@Column(name = "username", length = 255)
private String username;
@Column(name = "password",nullable = false,length = 255)
private String password;
@Column(name = "first_Name",nullable = false,length = 255)
private String firstName;
@Column(name = "last_Name",nullable = false,length = 255)
private String lastName;
@Column(name = "enabled", nullable = false)
private boolean enabled;
@Column(name = "news_cycle", nullable = false)
@Enumerated(EnumType.STRING)
private NewsletterCycle newsCycle;
@ElementCollection(targetClass = UserRole.class, fetch = FetchType.EAGER)
@CollectionTable(name = "user_roles")
@Enumerated(EnumType.STRING)
private Set<UserRole> roles;
@ManyToOne
@JoinColumn(name = "department_id")
private Department department;
@OneToOne(cascade = CascadeType.ALL)
@JoinColumn(name = "manages_department_id", referencedColumnName = "department_id")
private Department managingDepartment;
@ManyToOne
@JoinColumn(name = "assigned_room_id")
private Room assignedRoom;
@OneToMany(mappedBy = "user", cascade = CascadeType.REMOVE, fetch = FetchType.EAGER)
private Set<Absence> absences;
@CreatedDate
@Column(name = "created_at", nullable = false)
private LocalDateTime created;
@LastModifiedDate
@Column(name = "updated_at", nullable = false)
private LocalDateTime updated;
@Override
public int compareTo(User o) {
return this.username.compareTo(o.getUsername());
}
@Override
public int hashCode() {
int hash = 7;
hash = 59 * hash Objects.hashCode(this.getUsername());
return hash;
}
@Override
public boolean equals(Object obj) {
if (obj == null) {
return false;
}
if (!(obj instanceof User)) {
return false;
}
final User other = (User) obj;
return Objects.equals(this.username, other.getUsername());
}
@Override
public String toString() {
return "username: " getUsername() " name: " getFirstName() " " getLastName();
}
}
What does this "identifier expected" SQL syntax error mean in this case, I can't figure it out.
I tried making all fields except for username in user nullable, and then tried the same statement only inserting with the username, suspecting that maybe some timestamp datatype was the problem, but that did not change a thing.
I hope somebody can help thank you!
CodePudding user response：
If I understand your question correctly, you are trying to populate data.sql (mysql format) to an H2 database.
spring.datasource.url=jdbc:h2:mem:skel;MODE=MYSQL
spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect
I just doubt that H2 can have enough support on this. My suggestion is to provide a data-h2.sql for your test. And it will be easy to maintain.
CodePudding user response：
You named your table user which is a reserved keyword in H2. It's also a reserved keyword in the ANSI SQL-99 standard and often in other SQL implementations (sometimes it is a non-reserved keyword, for example in MySQL).
You can use reserved keywords as table names in SQL if you delimit them. H2 supports standard identifier delimiters, which are double-quotes.
I don't know if there's an easy way to make Spring delimit the identifiers in SQL statements. I recall it's pretty wonky. You have to define the entity with built-in double-quotes around its name, like this:
@Entity
@Table(name = "\"user\"")
See https://www.chrouki.com/posts/escape-sql-reserved-keywords-jpa-hibernate/
It's easier if you can just avoid using reserved words for your table names (or other identifiers, including columns, procedures, views, indexes, partitions, etc.).