115、【JavaEE】【Spring】Spring JDBC

1、概述

• Spring JDBC 是 Spring 提供的一个类似于 Apache Commons DbUtils 的库。是对 JDBC 的封装。

2、举例 — 部分注解

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

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

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

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

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>

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

• 依赖spring-jdbc中包含spring-tx，spring-tx是 Spring 事务管理所需的依赖。

CREATE TABLE xxx(
  id INT NOT NULL,
  name VARCHAR(3) NOT NULL
);

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

@NoArgsConstructor
@AllArgsConstructor
@Data
@ToString
public class Xxx {

    private Integer id;

    private String name;

}

jdbc_driver = com.mysql.cj.jdbc.Driver
jdbc_url = jdbc:mysql://127.0.0.1:3306/test_1?characterEncoding=UTF-8
jdbc_username = root
jdbc_password =

<?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 https://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="com.yscyber.spring.eight"/>

    <context:property-placeholder location="classpath:jdbc.properties"/>

    <!-- 数据源 -->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="${jdbc_driver}"/>
        <property name="url" value="${jdbc_url}"/>
        <property name="username" value="${jdbc_username}"/>
        <property name="password" value="${jdbc_password}"/>
    </bean>

    <!-- 配置 JdbcTemplate  -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <constructor-arg name="dataSource" ref="dataSource"/>
    </bean>

</beans>

import com.yscyber.spring.eight.pojo.Xxx;

import java.util.List;

public interface XxxRepo {

    List<Xxx> listAllXxx();

    int insertXxx(Xxx xxx);

    int updateXxxById(Integer id, Xxx xxx);

    int deleteXxxById(Integer id);

}

import com.yscyber.spring.eight.pojo.Xxx;
import com.yscyber.spring.eight.repo.XxxRepo;

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;

@Repository
public class XxxRepoImpl implements XxxRepo {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<Xxx> listAllXxx() {
        String sql = "SELECT id,name" +
                " FROM xxx";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Xxx.class));
    }

    @Override
    public int insertXxx(Xxx xxx) {
        String sql = "INSERT INTO xxx(id,name)" +
                " VALUES(?,?)";
        return jdbcTemplate.update(sql, xxx.getId(), xxx.getName());
    }

    @Override
    public int updateXxxById(Integer id, Xxx xxx) {
        String sql = "UPDATE xxx" +
                " SET id=?,name=?" +
                " WHERE id=?";
        return jdbcTemplate.update(sql, xxx.getId(), xxx.getName(), id);
    }

    @Override
    public int deleteXxxById(Integer id) {
        String sql =  "DELETE FROM xxx" +
                " WHERE id=?";
        return jdbcTemplate.update(sql, id);
    }

}

package com.yscyber.spring.eight;

import com.yscyber.spring.eight.pojo.Xxx;
import com.yscyber.spring.eight.repo.XxxRepo;

import org.junit.Test;
import org.junit.runner.RunWith;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.util.List;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:applicationContext.xml"})
public class MySpringTest {

    @Autowired
    private XxxRepo xxxRepo;

    @Test
    public void test1() {
        List<Xxx> xxxList = xxxRepo.listAllXxx();
        for (Xxx xxx : xxxList) {
            System.out.println(xxx);
        }
    }

    @Test
    public void test2() {
        xxxRepo.insertXxx(new Xxx(20, "xyz"));
    }

    @Test
    public void test3() {
        xxxRepo.updateXxxById(20, new Xxx(20, "jjj"));
    }

    @Test
    public void test4() {
        xxxRepo.deleteXxxById(20);
    }

}