在 Spring Boot 中使用 Jdbi3 简介

    
    
        org.springframework.boot
        spring-boot-starter-data-jdbc
    
    
    
        org.jdbi
        jdbi3-core
        3.6.0
    
    
        org.jdbi
        jdbi3-sqlobject
        3.6.0
    
    
        org.jdbi
        jdbi3-postgres
        3.6.0
    

spring:
  datasource:
    # I previously created a "Role" for my postgres database for this tutorial 
    username: jdbi-example-spring-boot
    # When developing locally , I tend not to use passwords for ease of use
    password: ""
    url: jdbc:postgresql://localhost/jdbi-example-spring-boot
    driver-class-name: org.postgresql.Driver

@Configuration
public class JdbiConfiguration {
 
    @Bean
    public Jdbi jdbi(DataSource datasource){
        return Jdbi.create(dataSource)
            .installPlugin(new PostgresPlugin())
            .installPlugin(new SqlObjectPlugin());
    }
}

@Data
public class User {
    private Long id;
    private String firstName;
    private String lastName;
    private String phoneNumber 
}

public interface UserDao {
 
    @Transaction
    @SqlUpdate("CREATE TABLE IF NOT EXISTS users(id BIGINT NOT NULL PRIMARY KEY, first_name VARCHAR(48), last_name VARCHAR(48), phone_number VARCHAR(48))")
    void createUserTable();
 
    @Transaction
    @SqlUpdate("INSERT INTO users(id,first_name,last_name,phone_number) VALUES(:id,:firstName,:lastName,:phoneNumber)")
    void createUser(@BindBean User user);
 
    @SqlQuery("SELECT * FROM users")
    @RegisterBeanMapper(User.class)
    List getUsers();
 
    @SqlQuery("SELECT * FROM users WHERE id = :id")
    @RegisterBeanMapper(User.class)
    User getUser(@Bind("id") Long id);
 
}

@RestController
public class UserController {
 
    private Jdbi jdbi;
 
    public UserController(Jdbi jdbi){
        this.jdbi = jdbi;
        jdbi.useExtension(UserDao.class,UserDao::createUserTable);
    }
 
    @PostMapping("/users")
    public User createUser(@RequestBody User user){
        user.setId(System.currentTimeMillis());
        jdbi.useExtension(UserDao.class, dao -> dao.createUser(user));
        return user;
    }
 
    @GetMapping("/users")
    public List getUsers(){
        return jdbi.withExtension(UserDao.class, UserDao::getUsers);
    }
 
    @GetMapping("/users/{id}")
    public User getUsers(@PathVariable  Long id){
        return jdbi.withExtension(UserDao.class, dao -> dao.getUser(id));
    }
 
}

@SpringBootApplication
public class JdbiExampleApplication {
    public static void main(String[] args) {
        SpringApplication.run(JdbiExampleApplication.class, args);
    }
}

curl -XPOST -H 'Content-Type: application/json' 'http://localhost:8080/users' -d '{"firstName":"Joe","lastName":"Shmo","phoneNumber":"714-832-2211"}'
{
    "id":1589208108922,
    "firstName":"Maria",
    "lastName":"Magee",
    "phoneNumber":"676332415"
}

curl 'http://localhost:8080/users'
[
    {
        "id":1589208108922,
        "firstName":"Maria",
        "lastName":"Magee",
        "phoneNumber":"676332415"
    }
]

curl 'http://localhost:8080/users/1589208108922'
{
    "id":1589208108922,
    "firstName":"Maria",
    "lastName":"Magee",
    "phoneNumber":"676332415"
}