It seems that when using @Entity on a class to provide the schema for the h2 database, the order of the columns does not follow the order you’ve specified the class members.

I have a class called article:

package com.backtojavaland.article;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import lombok.Getter;
import lombok.Setter;

@Entity
@Setter @Getter
public class Article {
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private int articleId;
	
	private String title;
	private String summary;

	public Article() {}
	
	public Article(String title, String summary) {
		this.title = title;
		this.summary = summary;
	}
}

I have a data.sql file to insert some data to start off with:

INSERT INTO article values(1, 'Nasa 2020 robot rover to target Jezero ''lake'' crater', 'America''s next robot rover will be sent to a 50km-wide depression that once had water running through it.');

Note: The single quotes and apostrophes have to be escaped.

However, the data wasn’t right. The title and summary were swapped around. To sort this out I had to specify the column names in the INSERT statement. I suppose its much better practice to do this anyway.

INSERT INTO article (article_id, title, summary) values(1, 'Nasa 2020 robot rover to target Jezero ''lake'' crater', 'America''s next robot rover will be sent to a 50km-wide depression that once had water running through it.');

I also had to remove my schema.sql file as this wasn’t loading anything into the database. I’m sure it worked before. Oh well.

The application.properties file for dev is as follows:

spring.datasource.url=jdbc:h2:mem:testdb
spring.h2.console.enabled=true
spring.h2.console.path=/h2

I added a new application-test.properties file to add the MySQL connection details:

spring.datasource.url=jdbc:mysql://localhost/bsctest
spring.datasource.username=test_user
spring.datasource.password=password
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

spring.datasource.test-on-borrow=true
spring.datasource.validation-query=/* ping */

I opened up the MySQL Workbench and created a new schema called bsctest. Then I added the article table. I used the GUI, and it generated the following SQL:

CREATE TABLE `bsctest`.`articles` (
`article_id` INT NOT NULL AUTO_INCREMENT,
`title` VARCHAR(45) NOT NULL,
`summary` VARCHAR(45) NOT NULL,
PRIMARY KEY (`article_id`))
COMMENT = 'Table to hold basic article information for testing.';

This is pretty close to the schema.sql file I have:

CREATE TABLE articles(
article_id int,
title varchar2(50),
summary varchar2(50)
);

so I applied it.

Then I added data.sql with additional MySQL text:

INSERT INTO articles values(1, 'Title 1', 'This is Summary 1 from MySQL.');
INSERT INTO articles values(2, 'Title 2', 'Here is Summary 2 from MySQL.');
INSERT INTO articles values(3, 'Title 3', 'Summary 3 is here from MySQL.');

(Actually, I forgot to add the MySQL bit, realized my application wasn’t refreshing so had no idea if it was using the h2 or MySQL database, so had to do three updates)

The data was successfully inserted into the MySQL database.

Now, I need to use the profiles to pick up the application-test.properties file because it looks like the h2 database is still being used.

Added two run configurations, and they both work fine 😀 Program arguments for test are:

--spring.profiles.active=test

There is nothing for dev, and the default application.properties file is picked up.