SpringBoot框架(9) -- JDBC

sprinboot操作mysql 数据库，首先添加dependence，springboot自带数据库连接池，这里demo则是另外使用阿里的druid

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.6.3</version>
    <relativePath/> <!-- lookup parent from repository -->
  </parent>
  <groupId>com.mike.study</groupId>
  <artifactId>springboot-jdbc</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <name>springboot-jdbc</name>
  <description>Demo project for Spring Boot</description>
  <properties>
    <java.version>8</java.version>
  </properties>
  <dependencies>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.1.21</version>
    </dependency>

    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.23</version>
    </dependency>

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

    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
    </dependency>
  </dependencies>

  <build>
    <plugins>
      <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
      </plugin>
    </plugins>
  </build>

</project>　　

项目结构如下

 新建实体类Book.java

import java.math.BigDecimal;
import java.util.Date;

public class Book {
    private Integer bookId;
    private String bookName;
    private String bookAuthor;
    private BigDecimal bookPrice;
    private Date bookDate;

    public Integer getBookId() {
        return bookId;
    }

    public void setBookId(Integer bookId) {
        this.bookId = bookId;
    }

    public String getBookName() {
        return bookName;
    }

    public void setBookName(String bookName) {
        this.bookName = bookName;
    }

    public String getBookAuthor() {
        return bookAuthor;
    }

    public void setBookAuthor(String bookAuthor) {
        this.bookAuthor = bookAuthor;
    }

    public BigDecimal getBookPrice() {
        return bookPrice;
    }

    public void setBookPrice(BigDecimal bookPrice) {
        this.bookPrice = bookPrice;
    }

    public Date getBookDate() {
        return bookDate;
    }

    public void setBookDate(Date bookDate) {
        this.bookDate = bookDate;
    }

    @Override
    public String toString() {
        return "Book{" +
                "bookId=" + bookId +
                ", bookName='" + bookName + '\'' +
                ", bookAuthor='" + bookAuthor + '\'' +
                ", bookPrice=" + bookPrice +
                ", bookDate=" + bookDate +
                '}';
    }
}

Book的操作实现类BookDaoImpl.java,（表结构可以根据sql statement补充设计）

#默认数据源是Hikari, 更改为Druid

import com.mike.study.springbootjdbc.dao.BookDao;

import com.mike.study.springbootjdbc.domain.Book;

import com.mike.study.springbootjdbc.map.BookMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @Classname BookImpl
 * @Created by Michael
 * @Date 2023/5/15
 * @Description Book class DAO
 */

@Repository
public class BookDaoImpl implements BookDao {
  @Autowired
  private JdbcTemplate jdbcTemplate;

  public void add() {
    String sql = "INSERT INTO `testdb`.`book`(`book_name`, `book_author`, `book_price`, `book_date`) VALUES ('222', '333', 444, '2018-07-11');\n";
    jdbcTemplate.execute(sql);
  }

  public Book selectOne() {
    String sql = "select * from book where book_id=?";
    Book book = jdbcTemplate.queryForObject(sql, new BookMapper(), 2);

    return book;
  }

  public List<Book> selectList() {
    String sql = "select * from book";
    return jdbcTemplate.query(sql,new BookMapper());
  }

  public void update() {
    String sql = "UPDATE `testdb`.`book` SET `book_name` = '333', `book_author` = '555', `book_price` = 444, `book_date` = '2018-07-11' WHERE `book_id` = 2;\n";
    jdbcTemplate.update(sql);
  }

  public void delete() {
    String sql = "delete from book where book_id=?";
    jdbcTemplate.update(sql,3);
  }
}

由于引入了JDBC的依赖，springboot装载了JdbcTemplate been，所以可以直接通过@Autowired拿到实例对象，同时也是通过该对象操作数据库。当然要操作数据库自然少不了数据库配置

#默认数据源是Hikari, 更改为Druid
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1/testdb?useSSL=true
spring.datasource.username=user
spring.datasource.password=password

简单demo，所以直接在控制台打印查询结果

@SpringBootApplication
public class SpringbootJdbcApplication {

  public static void main(String[] args) {
    ConfigurableApplicationContext run = SpringApplication.run(SpringbootJdbcApplication.class, args);

    BookDaoImpl bean = run.getBean(BookDaoImpl.class);
    Book book = bean.selectOne();
    System.out.println(book);
    run.close();
  }

}

输出结果