Learn How To Use The PostgreSQL Database with Spring Boot Now! 🧑‍🎓

				
					/*
 * Precondition:
 *
 * - Java version "22.0.1" 2024-04-16
 * - Groovy version 4.0.17
 */
package com.thospfuller.examples.postgres.database.spring.boot

@GrabConfig(systemClassLoader=true)

@Grab(group='org.springframework.boot', module='spring-boot', version='3.3.0')
@Grab(group='org.springframework.boot', module='spring-boot-autoconfigure', version='3.3.0')
import org.springframework.boot.autoconfigure.SpringBootApplication
import org.springframework.boot.builder.SpringApplicationBuilder
import org.springframework.context.annotation.Configuration
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.stereotype.Repository
import org.springframework.stereotype.Component
import org.springframework.boot.jdbc.DataSourceBuilder

@Grab(group='org.springframework', module='spring-jdbc', version='6.1.8')
import org.springframework.jdbc.core.JdbcTemplate
import org.springframework.jdbc.core.ParameterizedPreparedStatementSetter
import org.springframework.transaction.annotation.Transactional
import org.springframework.context.annotation.Bean
import org.springframework.boot.CommandLineRunner
import org.springframework.boot.WebApplicationType
import org.springframework.stereotype.Service

@Grab(group='com.impossibl.pgjdbc-ng', module='pgjdbc-ng', version='0.8.9')
import com.impossibl.postgres.jdbc.PGDataSource

import javax.sql.DataSource
import java.sql.PreparedStatement

@Grab(group='javax.annotation', module='javax.annotation-api', version='1.3.2')
import javax.annotation.PostConstruct
import javax.annotation.PreDestroy

@Grab(group='org.slf4j', module='slf4j-simple', version='2.0.9')
import org.slf4j.LoggerFactory

def log = LoggerFactory.getLogger(this.class)

log.info "Postgres Database with Spring Boot example begins; args: $args"

@Configuration
class BeanConfiguration {

  @Bean
  DataSource getDataSource () {

    PGDataSource dataSource = new PGDataSource()

    dataSource.setHost("0.0.0.0")
    dataSource.setPort(5432)
    dataSource.setDatabaseName("postgres")
    dataSource.setUser("postgres")
    dataSource.setPassword("password")

    return dataSource
  }

  @Bean  
  JdbcTemplate getJdbcTemplate (DataSource dataSource) {
    return new JdbcTemplate (dataSource)
  }
}

@Repository
@Transactional
class ExampleRepository {

  private static final def log = LoggerFactory.getLogger(ExampleRepository)

  static final def TABLE_NAME = "NAMES"

  @Autowired
  private JdbcTemplate jdbcTemplate

  void createExampleTable () {

    log.info "createExampleTable: method begins."

    jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS ${TABLE_NAME} (id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR(255));")

    log.info "createExampleTable: method ends."
  }

  def deleteExampleTable () {

    log.info "deleteExampleTable: method begins."

    jdbcTemplate.execute("DROP TABLE IF EXISTS ${TABLE_NAME};")

    log.info "deleteExampleTable: method ends."
  }

  def addNames (String... names) {
    return addNames (names as List<String>)
  }

  def addNames (List<String> nameList) {

    return jdbcTemplate.batchUpdate(
      "INSERT INTO ${TABLE_NAME} (name) VALUES (?);",
      nameList,
      nameList.size (),
      { PreparedStatement preparedStatement, String name ->
        preparedStatement.setString(1, name)
      } as ParameterizedPreparedStatementSetter<String>
    )
  }

  def updateName (int id, String newName) {

    return jdbcTemplate.update(
      "UPDATE ${TABLE_NAME} SET NAME = ? WHERE ID = ?;",
      newName,
      id
    )
  }

  def deleteName (int id) {

    return jdbcTemplate.update(
      "DELETE FROM ${TABLE_NAME} WHERE ID = ?;",
      id
    )
  }

  def readNames () {
    return jdbcTemplate.queryForList ("select name from ${TABLE_NAME}")
  }
}

@Service
class ExampleService {

  private static final def log = LoggerFactory.getLogger(ExampleService)

  @Autowired
  def exampleRepository

  @PostConstruct
  def start () {

    log.info "start: method begins."

    exampleRepository.createExampleTable ()

    log.info "start: method ends."
  }

  @PreDestroy
  def stop () {

    log.info "stop: method begins."

    exampleRepository.deleteExampleTable ()

    log.info "stop: method ends."
  }

  def addNames (String... nameList) {
    return exampleRepository.addNames (nameList)
  }

  def updateName (int id, String newName) {
    return exampleRepository.updateName (id, newName)
  }

  def deleteName (int id) {
    return exampleRepository.deleteName (id)
  }

  def readNames () {
    return exampleRepository.readNames ()
  }
}

@Component
class ExampleCommandLineRunner implements CommandLineRunner {

  private static final def log = LoggerFactory.getLogger(PostgreSQLSpringBootExampleApplication)

  @Autowired
  private def exampleService

  @Override
  public void run (String... args) {

    log.info "run: method begins; args: $args"

    def namesAdded = exampleService.addNames ("aaa", "bbb", "ccc", "ddd")

    log.info "namesAdded: $namesAdded"

    def updateResult = exampleService.updateName (2, "ZZZ")

    def names = exampleService.readNames ()

    log.info "updateResult: $updateResult, names after update: $names"

    def deletedNames = exampleService.deleteName (2)

    names = exampleService.readNames ()

    log.info "deletedNames: $deletedNames, names after deletion: $names"

    log.info "run: method ends."
  }
}

@SpringBootApplication(scanBasePackages = ["com.thospfuller.examples.postgres.database.spring.boot"])
class PostgreSQLSpringBootExampleApplication {}

def springApplicationBuilder = new SpringApplicationBuilder(PostgreSQLSpringBootExampleApplication)

def context = springApplicationBuilder
  .profiles("default")
  .web(WebApplicationType.NONE)
  .parent (
    BeanConfiguration,
    ExampleRepository,
    ExampleService,
    ExampleCommandLineRunner
  )
  .run(args)

context.close ()

log.info "...done!"

return