Spring Boot与JDBC Template

SpringBoot与JDBC Template的整合

Spring Boot是一个用于简化Java应用程序开发的框架，而JdbcTemplate是Spring框架提供的用于简化JDBC（Java数据库连接）操作的类。通过将Spring Boot与JdbcTemplate整合，可以更方便地进行数据库操作。

下面是Spring Boot与JdbcTemplate的整合步骤：

1. 添加依赖：在你的Spring Boot项目的pom.xml文件中，添加JdbcTemplate的依赖。可以通过在<dependencies>标签内添加以下依赖来实现：

   <dependency>
       <groupId>org.springframework.boot</groupId>
       <artifactId>spring-boot-starter-jdbc</artifactId>
   </dependency>
   

2. 配置数据源：在application.properties（或application.yml）文件中配置数据库连接信息，包括数据库URL、用户名和密码等。

   spring.datasource.url=jdbc:mysql://localhost:3306/mydatabase
   spring.datasource.username=username
   spring.datasource.password=password
   spring.datasource.driver-class-name=com.mysql.jdbc.Driver
   

3. 创建JdbcTemplate Bean：在Spring Boot的配置类中创建一个JdbcTemplate的Bean，用于执行数据库操作。在配置类上使用@Configuration注解，并通过@Bean注解创建JdbcTemplate Bean。

   @Configuration
   public class DatabaseConfig {
   
       @Autowired
       private DataSource dataSource;
   
       @Bean
       public JdbcTemplate jdbcTemplate() {
           return new JdbcTemplate(dataSource);
       }
   }
   

4. 使用JdbcTemplate：在需要进行数据库操作的地方注入JdbcTemplate，并使用其提供的方法执行SQL语句。

   @Autowired
   private JdbcTemplate jdbcTemplate;
   
   public void fetchUsers() {
       List<Map<String, Object>> userList = jdbcTemplate.queryForList("SELECT * FROM users");
       for (Map<String, Object> user : userList) {
           System.out.println("User: " + user.get("username"));
       }
   }
   

Demo

项目结构

项目源码

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class B01SpringBootJdbcTemplateApplication {

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

}

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class WebsiteController {
    @Autowired
    WebsiteService websiteService;
    @GetMapping("/add-website")
    public void addWebsite(){
        Website website=new Website();
        website.setId(1);
        website.setName("supremepole");
        website.setUrl("https://cs.supremepole.com");
        websiteService.addWebsite(website);
        Website website2=new Website();
        website2.setId(2);
        website2.setName("supremepole interview");
        website2.setUrl("https://interview.supremepole.com");
        websiteService.addWebsite(website2);
    }
    @GetMapping("/get-website")
    public Website getWebsite(){
        return websiteService.getWebsiteById(1);
    }
    @GetMapping("/update-website")
    public void updateWebsite(){
        Website website=new Website();
        website.setId(1);
        website.setName("supremepole algorithm");
        website.setUrl("https://algorithm.supremepole.com");
        websiteService.updateWebsite(website);
    }
    @GetMapping("/delete-website")
    public void deleteWebsite(){
        websiteService.deleteWebsiteById(2);
    }
}

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class WebsiteService {
    @Autowired
    WebsiteDao websiteDao;
    public int addWebsite(Website website) {
        return websiteDao.addWebsite(website);
    }
    public int updateWebsite(Website book) {
        return websiteDao.updateWebsite(book);
    }
    public int deleteWebsiteById(Integer id) {
        return websiteDao.deleteWebsiteById(id);
    }
    public Website getWebsiteById(Integer id) {
        return websiteDao.getWebsiteById(id);
    }
    public List<Website> getAllWebsites() {
        return websiteDao.getAllWebsites();
    }
}

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @author CodeCoderCoding
 */
@Repository
public class WebsiteDao {
    @Autowired
    JdbcTemplate jdbcTemplate;
    public int addWebsite(Website website) {
        return jdbcTemplate.update("INSERT INTO website(id,name,url) VALUES (?,?,?)",
                website.getId(), website.getName(), website.getUrl());
    }
    public int updateWebsite(Website website) {
        return jdbcTemplate.update("UPDATE website SET name=?,url=? WHERE id=?",
                website.getName(), website.getUrl(), website.getId());
    }
    public int deleteWebsiteById(Integer id) {
        return jdbcTemplate.update("DELETE FROM website WHERE id=?", id);
    }
    public Website getWebsiteById(Integer id) {
        return jdbcTemplate.queryForObject("select * from website where id=?",
                new BeanPropertyRowMapper<>(Website.class), id);
    }
    public List<Website> getAllWebsites() {
        return jdbcTemplate.query("select * from website",
                new BeanPropertyRowMapper<>(Website.class));
    }
}

/**
 * @author CodeCoderCoding
 */
public class Website {
    private int id;
    private String url;
    private String name;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public String toString(){
        return "id="+id+" ,name"+name+" ,url"+url;
    }
}

server.port=8081

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/spring-demo?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false
spring.datasource.username=root
spring.datasource.password=123456