Spring Boot で、JDBC sqlite3を使う。

2. Customer.java を作る。
spring_sql/src/main/java/com/example/spring_sql/Customer.java
package com.example.spring_sql; public class Customer { private long id; private String firstName, lastName; public Customer(long id, String firstName, String lastName) { this.id = id; this.firstName = firstName; this.lastName = lastName; } @Override public String toString() { return String.format( "Customer[id=%d, firstName='%s', lastName='%s']", id, firstName, lastName); } // getters & setters omitted for brevity }

3. SpringSqlApplication.java を作る
spring_sql/src/main/java/com/example/spring_sql/SpringSqlApplication.java
package com.example.spring_sql; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.ApplicationArguments; //import org.springframework.boot.CommandLineRunner; import org.springframework.boot.ApplicationRunner; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; import org.springframework.jdbc.core.JdbcTemplate; import java.util.Arrays; import java.util.List; import java.util.Map; import java.util.stream.Collectors; @SpringBootApplication public class SpringSqlApplication implements ApplicationRunner { //private static final Logger log = LoggerFactory.getLogger(SpringSqlApplication.class); public static void main(String[] args) { SpringApplication.run(SpringSqlApplication.class, args); //SpringApplication.run(RelationalDataAccessApplication.class, args); } @Autowired JdbcTemplate jdbcTemplate; @Override //public void run(String... strings) throws Exception { public void run(ApplicationArguments args) throws Exception { System.out.println(">> start run"); //log.info("start run"); //jdbcTemplate.execute("DROP TABLE customers IF EXISTS"); //jdbcTemplate.execute("CREATE TABLE customers(" + // "id SERIAL, first_name VARCHAR(255), last_name VARCHAR(255))"); // Split up the array of whole names into an array of first/last names //List<Object[]> splitUpNames = Arrays.asList("John Woo", "Jeff Dean", "Josh Bloch", "Josh Long").stream() // .map(name -> name.split(" ")) // .collect(Collectors.toList()); // Use a Java 8 stream to print out each tuple of the list //splitUpNames.forEach(name -> log.info(String.format("Inserting customer record for %s %s", name[0], name[1]))); // Uses JdbcTemplate's batchUpdate operation to bulk load data //jdbcTemplate.batchUpdate("INSERT INTO customers(first_name, last_name) VALUES (?,?)", splitUpNames); //log.info("Querying for customer records where first_name = 'Josh':"); //jdbcTemplate.query( // "SELECT id, first_name, last_name FROM customers WHERE first_name = ?", // (rs, rowNum) -> new Customer(rs.getLong("id"), rs.getString("first_name"), rs.getString("last_name")), "Josh") //.forEach(customer -> log.info(customer.toString()) List<Map<String, Object>> all_users = findAllUsers(); for (Map<String, Object> name : all_users) { for (var key: name.keySet()) { var value = name.get(key); System.out.println(key+":"+value); } } } public List<Map<String, Object>> findAllUsers() { String sql = "SELECT * FROM customers;"; return jdbcTemplate.queryForList(sql); } }

4. pom.xml を修正する。
spring_sql/pom.xml
.... <dependencies> <!-- Spring Boot JDBC --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <!-- SQLite JDBC Driver add by nishi --> <dependency> <groupId>org.xerial</groupId> <artifactId>sqlite-jdbc</artifactId> <version>3.51.0.0</version> </dependency> ....

5. application.properties を修正する。
spring_sql/src/main/resources/application.properties
spring.application.name=spring_sql # SQLiteデータベースファイルのパスを指定 # 相対パスの場合 (例: プロジェクトルートの直下) spring.datasource.url=jdbc:sqlite:my_database.db # 絶対パスの場合 (例: /opt/db/my_database.db) # spring.datasource.url=jdbc:sqlite:/opt/db/my_database.db # SQLiteではドライバークラス名を明示的に指定する必要がある場合が多い spring.datasource.driver-class-name=org.sqlite.JDBC # SQLiteはユーザー名とパスワードを必要としないため、設定は不要 # spring.datasource.username= # spring.datasource.password=

6. build and run
$ ./mcnw package
run
$ java -jar target/spring_sql-0.0.1-SNAPSHOT.jar

注) そのまえに、my_database.db が必要じゃ。
spring_sql/my_database.db を用意する。
$ sqlite3 my_database.db
sqlite> CREATE TABLE customers(id SERIAL, first_name VARCHAR(255), last_name VARCHAR(255));
sqlite> insert into customers values(1,'おんちゃん','となり');
sqlite> insert into customers values(2,'ねこ','のらねこ');
sqlite> insert into customers values(3,'いぬ','近所');
sqlite> .exit