How to use Spring JDBC Template with Spring Boot for Postgres DataBase

The tutorial shows you how to use Spring JDBC Template to access  & manipulate database (Postgres DB) with Spring Boot.


I. Technology

– Java 1.8
– Maven 3.3.9
– Spring Tool Suite – Version 3.8.1.RELEASE
– Spring Boot: 1.4.0.RELEASE

II. Overview of Spring JDBC Template project

1. Design

jdbc-postgresql

Class mapping with design:
– Client: JdbctemplatePostgresApplication.java
– Bussiness Layer: CustomerService interface, and CustomerServiceImpl implement CustomerService.
– Dao Layer: CustomerDao interface and CustomerDaoImpl implement CustomerDao.
– Database: Postgres.
– Model: Customer class.

2. Step to do

– Create SpringBoot project
– Config DataSource
– Create postgresql-schema
– Create Model: Customer
– Build Dao layer: CustomerDao interface; CustomerImpl
– Build Business Layer: CustomerService interface & CustomerServiceImpl
– Build Client: JdbctemplatePostgresApplication

III. Practice

1. Create SpringBoot project

File->New->Spring Starter Project
jdbc-postgresql-startproj

In SQL, select JDBC & PostgreSQL
jdbc-postgresql-pom-config

Press Finish.

2. Config DataSource

In application.properties file:


spring.datasource.url=jdbc:postgresql://localhost:5432/testdb
spring.datasource.username=postgres
spring.datasource.password=123

3. Create Progresql Schema

Create schema-postgresql.sql file and places in src/main/resources folder with content


DROP TABLE IF EXISTS customer;

CREATE TABLE customer (
    CUST_ID Bigserial PRIMARY KEY NOT NULL,
    NAME varchar(100) NOT NULL,
    AGE smallint NOT NULL
);

In application.properties, put:


spring.datasource.platform=postgresql

4. Model

Create a Class: Customer.java


public class Customer implements Serializable{
    private static final long serialVersionUID = 1L;
    long custId;
    String name;
    int age;
 
    public Customer(){
    }
 
    public Customer(long custId, String name, int age)     {
        this.custId = custId;
        this.name = name;
        this.age = age;
    }
 
    public long getCustId() {
        return custId;
    }
    public void setCustId(long custId) {
        this.custId = custId;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "Customer [age=" + age + ", custId=" + custId + ", name=" + name
        + "]";
    }
}

5. Build Dao Layer

CustomerDao interface


public interface CustomerDao {
	void insert(Customer cus);
	void inserBatch(List customers);
	List loadAllCustomer();
	Customer findCustomerById(long cust_id);
	String findNameById(long cust_id);
	int getTotalNumberCustomer();
}

CustomerDaoImpl.java implements CustomerDao.java interface:
Firstly inject DataSource


@Repository
public class CustomerDaoImpl extends JdbcDaoSupport implements CustomerDao{
 
    @Autowired 
    DataSource dataSource;
 
    @PostConstruct
    private void initialize(){
        setDataSource(dataSource);
    }
    //...
}

Implements Inteface:

Note: CustomerDaoImpl extends JDBCDaoSupport.

5.1. Insert to Postgres Database


@Override
public void insert(Customer cus) {
 String sql = "INSERT INTO customer " +
"(CUST_ID, NAME, AGE) VALUES (?, ?, ?)" ;
 getJdbcTemplate().update(sql, new Object[]{
 cus.getCustId(), cus.getName(), cus.getAge()
});
}

5.2. Insert Batch to Postgres database


@Override
public void inserBatch(List customers) {
	String sql = "INSERT INTO customer " + "(CUST_ID, NAME, AGE) VALUES (?, ?, ?)";
	getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {
		public void setValues(PreparedStatement ps, int i) throws SQLException {
			Customer customer = customers.get(i);
			ps.setLong(1, customer.getCustId());
			ps.setString(2, customer.getName());
			ps.setInt(3, customer.getAge());
		}
		
		public int getBatchSize() {
			return customers.size();
		}
	});

}

5.3. Load All Entities from Postgres


public List loadAllCustomer(){
	String sql = "SELECT * FROM customer";
	List> rows = getJdbcTemplate().queryForList(sql);
	
	List result = new ArrayList();
	for(Map row:rows){
		Customer cus = new Customer();
		cus.setCustId((Long)row.get("cust_id"));
		cus.setName((String)row.get("name"));
		cus.setAge((Integer) row.get("age"));
		result.add(cus);
	}
	
	return result;
}

5.4. Find Entity by Id


@Override
public Customer findCustomerById(long cust_id) {
	String sql = "SELECT * FROM customer WHERE CUST_ID = ?";
	return (Customer)getJdbcTemplate().queryForObject(sql, new Object[]{cust_id}, new RowMapper(){
		@Override
		public Customer mapRow(ResultSet rs, int rwNumber) throws SQLException {
			Customer cust = new Customer();
			cust.setCustId(rs.getLong("cust_id"));
			cust.setName(rs.getString("name"));
			cust.setAge(rs.getInt("age"));
			return cust;
		}
	});
}

5.5.  Find Entity Name by Id


@Override
public String findNameById(long cust_id) {
 String sql = "SELECT name FROM customer WHERE cust_id = ?";
 return getJdbcTemplate().queryForObject(sql, new Object[]{cust_id}, String.class);
}

5.6.  Get Total Entity


@Override
public int getTotalNumberCustomer() {
 String sql = "SELECT Count(*) FROM customer";
 int total = getJdbcTemplate().queryForObject(sql, Integer.class);
 return total;
}

6. Build Service Layer

6.1. Service Interface


public interface CustomerService {
	void insert(Customer cus);
	void insertBatch(List customers);
	void loadAllCustomer();
	void getCustomerById(long cust_id);
	void getCustomerNameById(long cust_id);
	void getTotalNumerCustomer();
}

6.2. Implement Service Layer


@Service
public class CustomerServiceImpl implements CustomerService{

	@Autowired CustomerDao customerDao;
	
	@Override
	public void insert(Customer cus) {
		customerDao.insert(cus);
	}
	
	@Override
	public void insertBatch(List customers) {
		customerDao.inserBatch(customers);
	}	
	
	public void loadAllCustomer(){
		List listCust = customerDao.loadAllCustomer();
		for(Customer cus: listCust){
			System.out.println(cus.toString());
		}
	}

	@Override
	public void getCustomerById(long cust_id) {
		Customer cust = customerDao.findCustomerById(cust_id);
		System.out.println(cust);
	}

	@Override
	public void getCustomerNameById(long cust_id) {
		String name = customerDao.findNameById(cust_id);
		System.out.println("Customer's name = " + name);
	}

	@Override
	public void getTotalNumerCustomer() {
		int totalNumberCustomer = customerDao.getTotalNumberCustomer();
		System.out.println("Total Number Customer is: " + totalNumberCustomer);
	}

}

7. Implement Client

First enable: ComponentScan: service & dao


@ComponentScan("com.javasampleapproach.jdbcpostgresql.service.impl, com.javasampleapproach.jdbcpostgresql.dao.impl")

Get Customer Service from Context:


CustomerService cusService = context.getBean(CustomerService.class);

Prepare Data:


Random r = new Random();

// Customer 1
Customer cus_1 = new Customer();
Long cus_1_id = r.nextLong();
cus_1.setCustId(cus_1_id);
cus_1.setName("demoCustomer_1");
cus_1.setAge(30);

// Customer 2
Customer cus_2 = new Customer();
Long cus_2_id = r.nextLong();
cus_2.setCustId(cus_2_id);
cus_2.setName("demoCustomer_2");
cus_2.setAge(30);

// Customer 3
Customer cus_3 = new Customer();
Long cus_3_id = r.nextLong();
cus_3.setCustId(cus_3_id);
cus_3.setName("demoCustomer_2");
cus_3.setAge(30);

Insert a customer to DB


cusService.insert(cus_1);

Insert a List of Customer to DB


List customers = new ArrayList();
customers.add(cus_2);
customers.add(cus_3);
cusService.insertBatch(customers); 

Load All Customer and display


cusService.loadAllCustomer();

Get Customer By Id


cusService.getCustomerById(Long.valueOf(cus_1_id));

Get Customer’s name by Id


cusService.getCustomerNameById(cus_2_id);

Get Total Customers in DB


cusService.getTotalNumerCustomer();

IV. Source Code

SpringJdbcTemplatePostgresql



By grokonez | September 23, 2016.

Last updated on May 6, 2021.



Related Posts


3 thoughts on “How to use Spring JDBC Template with Spring Boot for Postgres DataBase”

    1. Hello,

      For exposing RestAPIs, you create a RestController.
      SpringBoot supports all for you, follow below steps to do it:
      – add web dependency:

      
      	org.springframework.boot
      	spring-boot-starter-web
      
      

      – create a WebController with @RestController, then use annotations {@GetMapping, @PostMapping, @PutMapping, @DeleteMapping}
      to build RestApis.

      – Then you just use CustomerService to interact with database:

      @Autowired
      CustomerService cusService;
      

      Follow the tutorial to do it:

      Spring Framework 4.3 New Feature RequestMapping: @GetMapping, @PostMapping, @PutMapping, @DeleteMapping

      About the form submition, You can follow the guide:
      Spring Web MVC – Spring Form Submission | Spring Boot

      If you choose JQuery-Ajax or Angular for front-end, you can follow the tutorials:
      – JQuery: How to integrate JQuery Ajax POST/GET & Spring MVC | Spring Boot
      – AngularJs: How to integrate Http Angularjs with Spring MVC | Spring Boot
      – Angular4: How to use Angular HttpClient to POST, PUT, DELETE data on SpringBoot Rest APIs – Angular 4

      Regards,
      JSA

Got Something To Say:

Your email address will not be published. Required fields are marked *

*