Overview:
I need an easy database so I am going through an H2 Embedded DB in springboot.
In this part, I am creating the initial tables and hopefully entering some data through both a sql script and programmatically.
Steps with references:
http://www.springboottutorial.com/spring-boot-and-spring-jdbc-with-h2
schema.sql:
it looks like spring boot will automatically search for and execute an sql script (probably with the specific name schema.sql) on startup.
CREATE TABLE log
(
id VARCHAR(255) NOT NULL,
ip VARCHAR(255) NOT NULL,
request VARCHAR(255) NOT NULL,
status VARCHAR(255) NOT NULL,
userAgent VARCHAR(255) NOT NULL
);
data.sql:
INSERT INTO log
VALUES('2020-01-01 00:00:00.001', '192.168.234.82', 'GET', '200', 'someInfo');
Note the error during the testing phase of maven when I left off the ending semicolon:
[ERROR] Tests run: 1, Failures: 0, Errors: 1, Skipped: 0, Time elapsed: 2.639 s <<< FAILURE! - in com.wallethub.exam.parser.ParserApplicationTests
[ERROR] contextLoads(com.wallethub.exam.parser.ParserApplicationTests) Time elapsed: 0.006 s <<< ERROR!
java.lang.IllegalStateException: Failed to load ApplicationContext
Caused by: org.springframework.beans.factory.BeanCreationException:
Error creating bean with name 'dataSource' defined in class path resource [org/springframework/boot/autoconfigure/jdbc/DataSourceConfiguration$Hikari.class]: Initialization of bean failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'org.springframework.boot.autoconfigure.jdbc.DataSourceInitializerInvoker': Invocation of init method failed; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of URL [file:/home/dfastje/Development/Wallethub/parserUtil/parser/target/classes/data.sql]: INSERT INTO log; nested exception is org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "INSERT INTO LOG [*]"; expected "., (, DIRECT, SORTED, DEFAULT, VALUES, SET, (, WITH, SELECT, FROM"; SQL statement:
INSERT INTO log [42001-197]
Caused by: org.springframework.beans.factory.BeanCreationException:
Error creating bean with name 'org.springframework.boot.autoconfigure.jdbc.DataSourceInitializerInvoker': Invocation of init method failed; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of URL [file:/home/dfastje/Development/Wallethub/parserUtil/parser/target/classes/data.sql]: INSERT INTO log; nested exception is org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "INSERT INTO LOG [*]"; expected "., (, DIRECT, SORTED, DEFAULT, VALUES, SET, (, WITH, SELECT, FROM"; SQL statement:
INSERT INTO log [42001-197]
Caused by: org.springframework.jdbc.datasource.init.ScriptStatementFailedException:
Failed to execute SQL script statement #1 of URL [file:/home/dfastje/Development/Wallethub/parserUtil/parser/target/classes/data.sql]: INSERT INTO log; nested exception is org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "INSERT INTO LOG [*]"; expected "., (, DIRECT, SORTED, DEFAULT, VALUES, SET, (, WITH, SELECT, FROM"; SQL statement:
INSERT INTO log [42001-197]
Caused by: org.h2.jdbc.JdbcSQLException:
Syntax error in SQL statement "INSERT INTO LOG [*]"; expected "., (, DIRECT, SORTED, DEFAULT, VALUES, SET, (, WITH, SELECT, FROM"; SQL statement:
INSERT INTO log [42001-197]
Log.java model class:
Lombok is amazing (https://projectlombok.org/features/Data)
package com.wallethub.exam.parser.model;
import lombok.Data;
@Data
public class Log {
String id;
String ip;
String request;
String status;
String userAgent;
}
Queries.java class:
package com.wallethub.exam.parser.data;
import org.springframework.stereotype.Component;
@Component
public class Queries {
public String selectAllFromLogWhereColumn(LogColumns column){
String columnName = column.name();
String query = "SELECT * FROM log WHERE " + columnName + "=?";
return query;
}
public enum LogColumns{
timeId,
ip,
request,
status,
userAgent
}
}
LogJdbcRepository.java class:
package com.wallethub.exam.parser.data;
import com.wallethub.exam.parser.model.Log;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
@Repository
public class LogJdbcRepository {
@Autowired
JdbcTemplate jdbcTemplate;
@Autowired
Queries queries;
public Log findByTimeId(String timeId){
return jdbcTemplate.queryForObject(
queries.selectAllFromLogWhereColumn(Queries.LogColumns.timeId),
new Object[] {timeId},
new BeanPropertyRowMapper<Log>(Log.class)
);
}
}
Commandline Runner:
I usually verify the methods in debug mode, but using the commandlinerunner interface was a pretty cool solution from the tutorial I am following! I will remove this for the final version.
package com.wallethub.exam.parser;
import com.wallethub.exam.parser.data.LogJdbcRepository;
import lombok.extern.log4j.Log4j2;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@Log4j2
@SpringBootApplication
public class ParserApplication implements CommandLineRunner {
@Autowired
LogJdbcRepository repository;
public static void main(String[] args) {
SpringApplication.run(ParserApplication.class, args);
}
@Override
public void run(String... args) throws Exception {
log.info( "Log timeId 2020-01-01 00:00:00.001 -> {}",
repository.findByTimeId("2020-01-01 00:00:00.001") );
}
}
Output after the Application started line in the standard output:
2018-09-12 15:04:47.689 INFO 25479 --- [ main] c.w.exam.parser.ParserApplication : Log timeId 2020-01-01 00:00:00.001 -> Log(timeId=2020-01-01 00:00:00.001, ip=192.168.234.82, request=GET, status=200, userAgent=someInfo)
Build and Run:
mvn clean package
mvn spring-boot:run
Access H2 Console: http://localhost:8080/h2-console
Saved Settings: Generic H2 (Embedded)
Setting Name: Generic H2 (Embedded)
Driver Class: org.h2.Driver
JDBC URL: jdbc:h2:mem:testdb
User Name: sa
Password:
Conclusion:
I was able to successfully create the table, add the initial data from a sql script, and access that data programmatically!
The next step is to remove the default spring startup info (maybe) and to write the import code!