SpringJDBC—資料庫的另一種連接配接方式

我們的目标是星辰大海，而非人間煙塵

我之前暑假在家裡學的是mybatis連接配接資料庫技術，裡面有mapper.xml實作sql語句的書寫，還有mapper接口，來實作資料庫底層的調用，我覺得比較麻煩，上課的時候老師講了用SpringJDBC來連接配接資料庫，我個人覺得這種技術比較簡單，相對于mybatis的各種配置檔案，是以記錄一下一種新的技術。

文章目錄

• • SpringJDBC---資料庫的另一種連接配接方式
  • • 1.導入依賴
    • 2.配置spring的配置檔案_`spring-config.xml`
    • 3.Person實體類的書寫
    • 4.SQL語句的書寫
    • 5.編寫測試檔案
    • 6.運作看結果

1.導入依賴

<dependencies>
    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.20</version>
    </dependency>

    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-core</artifactId>
      <version>5.3.8</version>
    </dependency>

    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-beans</artifactId>
      <version>5.3.8</version>
    </dependency>

    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>5.3.8</version>
    </dependency>

    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context-support</artifactId>
      <version>5.3.8</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-expression</artifactId>
      <version>5.3.8</version>
    </dependency>

    <dependency>
      <groupId>commons-logging</groupId>
      <artifactId>commons-logging</artifactId>
      <version>1.2</version>
    </dependency>

    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.22</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>5.3.8</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-tx</artifactId>
      <version>5.3.8</version>
    </dependency>
  </dependencies>
           

2.配置spring的配置檔案_

spring-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans.xsd
       http://www.springframework.org/schema/context
       https://www.springframework.org/schema/context/spring-context.xsd">

    <context:component-scan base-package="org.example"/>
    <!-- 1.配置資料源  -->
    <bean class="org.springframework.jdbc.datasource.DriverManagerDataSource" id="dataSource">
        <!-- 1.1.資料庫驅動 -->
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
        <!-- 1.2.連接配接資料庫的url -->
        <property name="url" value="jdbc:mysql://localhost:3306/spring?characterEncoding=utf8&amp;serverTimezone=UTC"/>
        <!-- 1.3.連接配接資料庫的使用者名 -->
        <property name="username" value="root"></property>
        <!-- 1.4.連接配接資料庫的密碼 -->
        <property name="password" value="111111"></property>
    </bean>
    <!-- 2配置JDBC模闆 -->
    <bean id="jdbcTemplate"
          class="org.springframework.jdbc.core.JdbcTemplate">
        <!-- 預設必須使用資料源 -->
        <property name="dataSource" ref="dataSource"></property>
    </bean>

</beans>
           

3.Person實體類的書寫

package org.example;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Person {
    private int id;
    private int age;
    private String name;
}
           

4.SQL語句的書寫

1. @Repository：完成 PersonDao的自動注入
2. @Autowired：完成 JdbcTemplate 自動注入
3. 通過調用jdbcTemplate.xxxx()方法來完成sql語句的書寫

package org.example;

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

import java.util.List;

@Repository
public class PersonDao {

    @Autowired
    JdbcTemplate jdbcTemplate;

    public void addUser(String name,int age){
        jdbcTemplate.update("insert into person values(null,?,?)",age,name);
    }

    public void updateUser(String name,int age,int id){
        jdbcTemplate.update("update person set name = ?,age=? where id = ?",name,age,id);
    }

    public void deleteUser(int id){
        jdbcTemplate.update("delete from person where id = ? ",id);
    }

    public List<Person> findAllUser(){
        RowMapper<Person> rowMapper = new BeanPropertyRowMapper<>(Person.class);
        return jdbcTemplate.query("select * from person",rowMapper);
    }
}

           

5.編寫測試檔案

package org.example;

import static org.junit.Assert.assertTrue;

import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import java.util.List;

/**
 * Unit test for simple App.
 */
public class AppTest 
{
    /**
     * Rigorous Test :-)
     */

    @Test
    public void test2(){
        ApplicationContext context = new ClassPathXmlApplicationContext("spring-config.xml");
        PersonDao personDao = context.getBean("personDao", PersonDao.class);
        List<Person> allUser = personDao.findAllUser();
        for (Person person : allUser) {
            System.out.println(person);
        }
    }

}

           

6.運作看結果