February 17th, 2023
SpringBoot JDBC with H2
Adding the dependencies of JDBC and H2 in the pom file.
pom.xml
4.0.0
org.springframework.boot
spring-boot-starter-parent
3.0.2
com.brains.database
database-demo
0.0.1-SNAPSHOT
database-demo
Demo project for Spring Boot
17
org.springframework.boot
spring-boot-starter-jdbc
org.springframework.boot
spring-boot-starter-web
com.h2database
h2
runtime
org.projectlombok
lombok
true
org.springframework.boot
spring-boot-starter-test
test
org.springframework.boot
spring-boot-maven-plugin
org.projectlombok
lombok
application.properties
spring.datasource.url=jdbc:h2:mem:testdb;NON_KEYWORDS=USER
spring.h2.console.enabled=true
spring.data.jpa.repositories.bootstrap-mode=default
spring.jpa.defer-datasource-initialization=true
See the UI for the H2 database by typing this url
http://localhost:8080/h2-console
Add data.sql in the resources folder and it would automatically called at startup. Load some data in the table by adding some records in the same data.sql file.
data.sql
create table person
(
id integer not null,
name varchar(255) not null,
location varchar(255),
birth_date timestamp,
primary key(id)
);
// add few records
insert into person (id, name, location, birth_date)
values(10001,'Ranga','Hyderabad', now() );
insert into person (id, name, location, birth_date)
values(10002,'John','Guntur', now() );
insert into person (id, name, location, birth_date)
values(10003,'Ravi','Ahmedabad', now() );
Create PersonJdbcDao which can interact with the database. Here all the magic happens using Autoconfiguration by SpringBoot. @JdbcTemplate is the class responsible such database interactions.
package com.brains.database.databasedemo.bean;
import java.util.Date;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data @AllArgsConstructor @NoArgsConstructor
public class Person {
private int id;
private String name;
private String location;
private Date birth_date;
}
package com.brains.database.databasedemo.bean;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data @AllArgsConstructor @NoArgsConstructor
public class CustomPerson {
private int id;
private String name;
}
package com.brains.database.databasedemo.repository;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import com.brains.database.databasedemo.bean.Person;
import com.brains.database.databasedemo.bean.CustomPerson;
@Repository
public class PersonJdbcDao {
@Autowired
JdbcTemplate jdbcTemplate;
class PersonRowMapper implements RowMapper{
@Override
public CustomPerson mapRow(ResultSet rs, int rowNum) throws SQLException {
CustomPerson person = new CustomPerson();
person.setId(rs.getInt("id"));
person.setName(rs.getString("name"));
return person;
}
}
public List findAllUsingRowMapper(){
return jdbcTemplate.query("select * from person",
new PersonRowMapper());
}
public List findAll(){
return jdbcTemplate.query("select * from person",
new BeanPropertyRowMapper(Person.class));
}
public Person getPersonById(int id) {
return jdbcTemplate.queryForObject("select * from person where id = ?",
new Object[] {id},
new BeanPropertyRowMapper(Person.class));
}
public int deleteById(int id) {
return jdbcTemplate.update("delete from person where id =?",
new Object[] {id}
);
}
public int insertPerson(Person person) {
return jdbcTemplate.update("insert into person (id, name, location, birth_date)\n"
+ "values(?,?,?,?)",
new Object[] {
person.getId(), person.getName(),
person.getLocation(),
new Timestamp(person.getBirth_date().getTime())});
}
public int updatePerson(Person person) {
return jdbcTemplate.update(
"update person \n"
+ " set name = ?, location = ?, birth_date = ? \n"
+ " where id = ? ",
new Object[] {
person.getName(),
person.getLocation(),
new Timestamp(person.getBirth_date().getTime()),
person.getId()}
);
}
}
Extend the main applicatino with CommandLineRunner so that run() method can be executed while application startup.
package com.brains.database.databasedemo;
import java.util.Date;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import com.brains.database.databasedemo.bean.Person;
import com.brains.database.databasedemo.repository.PersonJdbcDao;
@SpringBootApplication
public class DatabaseDemoApplication implements CommandLineRunner {
public static void main(String[] args) {
SpringApplication.run(DatabaseDemoApplication.class, args);
}
@Autowired
PersonJdbcDao jdbcDao;
private Logger logger = LoggerFactory.getLogger(this.getClass());
@Override
public void run(String... args) throws Exception {
logger.info("All users using RowMapper: -> {}", jdbcDao.findAllUsingRowMapper());
logger.info("All users using BeanPropertyRowMapper: -> {}", jdbcDao.findAll());
logger.info("Peson found: -> {}", jdbcDao.getPersonById(10002));
logger.info("Delete Peson: -> {}", jdbcDao.deleteById(10002));
logger.info("Insert new person -> {}", jdbcDao.insertPerson(new Person(10004, "Suresh", "Ahmedabad", new Date())));
logger.info("Update Person id 10003 -> {}", jdbcDao.updatePerson(new Person(10003, "Aman", "Ahmedabad", new Date())));
logger.info("All users: -> {}", jdbcDao.findAll());
}
}
Running the Application
2023-02-20T20:16:23.124+05:30 INFO 120598 --- [ main] .DatabaseDemoApplication$$SpringCGLIB$$0 : All users using RowMapper: -> [CustomPerson(id=10001, name=Ranga), CustomPerson(id=10002, name=John), CustomPerson(id=10003, name=Ravi)]
2023-02-20T20:16:23.129+05:30 INFO 120598 --- [ main] .DatabaseDemoApplication$$SpringCGLIB$$0 : All users using BeanPropertyRowMapper: -> [Person(id=10001, name=Ranga, location=Hyderabad, birth_date=2023-02-20 20:16:22.657722), Person(id=10002, name=John, location=Guntur, birth_date=2023-02-20 20:16:22.659073), Person(id=10003, name=Ravi, location=Ahmedabad, birth_date=2023-02-20 20:16:22.659391)]
2023-02-20T20:16:23.142+05:30 INFO 120598 --- [ main] .DatabaseDemoApplication$$SpringCGLIB$$0 : Peson found: -> Person(id=10002, name=John, location=Guntur, birth_date=2023-02-20 20:16:22.659073)
2023-02-20T20:16:23.144+05:30 INFO 120598 --- [ main] .DatabaseDemoApplication$$SpringCGLIB$$0 : Delete Peson: -> 1
2023-02-20T20:16:23.145+05:30 INFO 120598 --- [ main] .DatabaseDemoApplication$$SpringCGLIB$$0 : Insert new person -> 1
2023-02-20T20:16:23.148+05:30 INFO 120598 --- [ main] .DatabaseDemoApplication$$SpringCGLIB$$0 : Update Person id 10003 -> 1
2023-02-20T20:16:23.149+05:30 INFO 120598 --- [ main] .DatabaseDemoApplication$$SpringCGLIB$$0 : All users: -> [Person(id=10001, name=Ranga, location=Hyderabad, birth_date=2023-02-20 20:16:22.657722), Person(id=10003, name=Aman, location=Ahmedabad, birth_date=2023-02-20 20:16:23.145), Person(id=10004, name=Suresh, location=Ahmedabad, birth_date=2023-02-20 20:16:23.144)]
Leave a Reply