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.运行看结果