Spring_JDBCTemplate操作数据库增查删改-springjdbctemplate操作数据库增查删改.md

焦虑烧麦 148 2022-05-02

新建Spring项目

参考这篇文章使用IDEA创建普通Spring项目

我的环境

  • JDK:1.8
  • IDEA:2020.2
  • MySQL:8.0.21

项目结构

结构.jpg

需要用到的jar包

  • aopalliance-1.0.jar
  • aspectjweaver-1.8.10.jar
  • commons-logging-1.2.jar
  • hamcrest-core-1.1.jar
  • junit-4.12.jar
  • mysql-connector-java-8.0.22.jar
  • spring-aop-4.3.6.RELEASE.jar
  • spring-beans-4.3.6.RELEASE.jar
  • spring-context-4.3.6.RELEASE.jar
  • spring-core-4.3.6.RELEASE.jar
  • spring-expression-4.3.6.RELEASE.jar
  • spring-jdbc-4.3.6.RELEASE.jar
  • spring-tx-4.3.6.RELEASE.jar

jar.jpg

创建数据库

用CMD或者Navicat创建一个Spring数据库

新建XML文件

右键-New-XML Configuration File-Spring Config
applicationContext.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"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context-4.2.xsd
        http://www.springframework.org/schema/aop
        http://www.springframework.org/schema/aop/spring-aop-4.2.xsd">

    <!--  配置数据源  -->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">

        <!--  配置数据库驱动  -->
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>

        <!-- 配置数据库的连接地址 -->
        <property name="url" value="jdbc:mysql://localhost:3306/Spring?serverTimezone=UTC"/>

        <!-- 配置数据库的用户名&密码 -->
        <property name="username" value="root"/>
        <property name="password" value="112800"/>
    </bean>

    <!--  配置JDBC模板  -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <bean id="accountDao" class="com.jaolvv.JDBCdatabase.AccountDaoImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate"/>
    </bean>
</beans>

新建Account类

public class Account {

    private int id; //UserID
    private String username;    //UserName
    private double balance; //UserBalance

    public int getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public double getBalance() {
        return balance;
    }

    public void setBalance(double balance) {
        this.balance = balance;
    }

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", balance=" + balance +
                '}';
    }
}

新建AccountDao类

这个接口主要是写增查删改的抽象方法

import java.util.List;

/**
 * Created with IntelliJ IDEA.
 *
 * @Website : https://www.jaolvv.top
 * @Date 3/25/2021 6:43 PM
 * @ClassName AccountDao
 * @Author Liu
 * 注释/说明:
 *          对数据库进行增删改查的接口
 **/

public interface AccountDao {

    //add Account
    public void addAccount(Account account);//指定Account类,将值封装到了Account类里面去  == (int id, String name, double balance)

    //delete Account
    public void deleteAccount(int account);

    //update Account
    public void updateAccount(Account account);

    //根据id查询账户
    public Account findAccountById(int id);


    //查询所有账户信息
    public List<Account> findAccount();

}

新建AccountDaoImpl类

继承AccountDao类,对里面的抽象方法进行重写

package com.jaolvv.JDBCdatabase;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.util.List;

/**
 * Created with IntelliJ IDEA.
 *
 * @Website : https://www.jaolvv.top
 * @Date 3/25/2021 6:52 PM
 * @ClassName AccountDaoImpl
 * @Author Liu
 * 注释/说明:
 *      重写AccountDao类的方法
 **/

public class AccountDaoImpl implements AccountDao {

    private JdbcTemplate jdbcTemplate;

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    //通过JdbcTemplate实现对数据库的更新 删除 添加
    //jdbcTemplate.update();增删改查都用的update()
    @Override
    public void addAccount(Account account) {
        String sql = "insert into account(username,balance) values(?,?)";
        int result =  jdbcTemplate.update(sql,account.getName(),account.getBalance());
        if (result > 0){
            System.out.println("插入了 "+result+" 行数据");
        }else {
            System.out.println("插入失败!");
        }
    }

    //删除
    @Override
    public void deleteAccount(int id) {
        String sql = "delete from account where id=?";
        int result =  jdbcTemplate.update(sql, id);
        if (result > 0){
            System.out.println("删除了 "+result+" 行数据");
        }else {
            System.out.println("删除失败!");
        }
    }

    //更新
    @Override
    public void updateAccount(Account account) {
        String sql = "update account set username = ?,balance = ? where id = ?";
        int result =  jdbcTemplate.update(sql,account.getName(),account.getBalance(),account.getId());
        if (result > 0){
            System.out.println("修改了  "+result+" 行数据");
        }else {
            System.out.println("更新失败!");
        }
    }

    //根据id查询账户
    @Override
    public Account findAccountById(int id) {
        String sql="select * from account where id=?";
        //RowMapper 行的 映射:指两个元素的集之间元素相互“对应”的关系
        RowMapper<Account> rowMapper=new BeanPropertyRowMapper<Account>(Account.class);
        Account account=jdbcTemplate.queryForObject(sql, rowMapper,id);
        return account;
    }


    //查询所有账户信息
    @Override
    public List<Account> findAccount() {
        String sql="select * from account";
        RowMapper<Account> rowMapper=new BeanPropertyRowMapper<Account>(Account.class);
        List<Account> list=jdbcTemplate.query(sql, rowMapper);
        return list;
    }


}


创建测试类

对JdbcTemplate进行测试
使用Junit创建一个Test方法
用jdbcTemplate.execute创建一个名为account的Mysql数据库

@Test   //创建Mysql数据库;“Spring”下的account表
    public  void main() {
        ApplicationContext context = new ClassPathXmlApplicationContext("com/jaolvv/JDBCdatabase/applicationContext.xml");
        JdbcTemplate jdbcTemplate =(JdbcTemplate)context.getBean("jdbcTemplate");
        jdbcTemplate.execute("create table account("+
                "id int primary key auto_increment,"+
                "username varchar(100),"+
                "balance double)");
        System.out.println("账户表创建成功");
    }

运行效果

如果成功运行则会显示 "账户表创建成功",同时数据库Spring中会多出Account表

添加功能

@Test   //添加功能
    public void addAccount(){
        ApplicationContext context = new ClassPathXmlApplicationContext("com/jaolvv/JDBCdatabase/applicationContext.xml");
        AccountDao accountDao =(AccountDao) context.getBean("accountDao");
        Account account = new Account();
        account.setName("李四");
        account.setBalance(8000.00);
        accountDao.addAccount(account);
    }

测试结果

20210328_231023.jpg

删除功能

@Test   //删除功能
    public void deleteAccount(){
        ApplicationContext context = new ClassPathXmlApplicationContext("com/jaolvv/JDBCdatabase/applicationContext.xml");
        AccountDao accountDao =(AccountDao) context.getBean("accountDao");
        accountDao.deleteAccount(4);
    }

测试结果

20210328_231147.jpg

更新功能

@Test   //更新功能
    public void updateAccount(){
        ApplicationContext context = new ClassPathXmlApplicationContext("com/jaolvv/JDBCdatabase/applicationContext.xml");
        AccountDao accountDao =(AccountDao) context.getBean("accountDao");
        Account account = new Account();
        account.setName("zhangsan3");
        account.setId(3);
        account.setBalance(7000.00);
        accountDao.updateAccount(account);
    }

测试结果

update.jpg

通过ID查找

 //根据id查询账户
    @Override
    public Account findAccountById(int id) {
        String sql="select * from account where id=?";
        //RowMapper 行的 映射:指两个元素的集之间元素相互“对应”的关系
        RowMapper<Account> rowMapper=new BeanPropertyRowMapper<Account>(Account.class);
        Account account=jdbcTemplate.queryForObject(sql, rowMapper,id);
        return account;
    }

测试结果

id.jpg

查询所有账户信息

//查询所有账户信息
    @Override
    public List<Account> findAccount() {
        String sql="select * from account";
        RowMapper<Account> rowMapper=new BeanPropertyRowMapper<Account>(Account.class);
        List<Account> list=jdbcTemplate.query(sql, rowMapper);
        return list;
    }

测试结果

all.jpg

完整测试类

package com.jaolvv.JDBCdatabase;

import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;

/**
 * Created with IntelliJ IDEA.
 *
 * @Website : https://www.jaolvv.top
 * @Date 3/19/2021 8:09 PM
 * @ClassName JdbcTemplateTest
 * @Author Liu
 * 注释/说明:
 **/

public class JdbcTemplateTest {
    @Test   //创建Mysql数据库;“Spring”下的account表
    public  void main() {
        ApplicationContext context = new ClassPathXmlApplicationContext("com/jaolvv/JDBCdatabase/applicationContext.xml");
        JdbcTemplate jdbcTemplate =(JdbcTemplate)context.getBean("jdbcTemplate");
        jdbcTemplate.execute("create table account("+
                "id int primary key auto_increment,"+
                "username varchar(100),"+
                "balance double)");
        System.out.println("账户表创建成功");
    }

    @Test   //添加功能
    public void addAccount(){
        ApplicationContext context = new ClassPathXmlApplicationContext("com/jaolvv/JDBCdatabase/applicationContext.xml");
        AccountDao accountDao =(AccountDao) context.getBean("accountDao");
        Account account = new Account();
        account.setUsername("李四");
        account.setBalance(8000.00);
        accountDao.addAccount(account);
    }

    @Test   //删除功能
    public void deleteAccount(){
        ApplicationContext context = new ClassPathXmlApplicationContext("com/jaolvv/JDBCdatabase/applicationContext.xml");
        AccountDao accountDao =(AccountDao) context.getBean("accountDao");
        accountDao.deleteAccount(8);
    }

    @Test   //更新功能
    public void updateAccount(){
        ApplicationContext context = new ClassPathXmlApplicationContext("com/jaolvv/JDBCdatabase/applicationContext.xml");
        AccountDao accountDao =(AccountDao) context.getBean("accountDao");
        Account account = new Account();
        account.setUsername("李四");
        account.setId(3);
        account.setBalance(5000.00);
        accountDao.updateAccount(account);
    }

    @Test   //查找功能ById
    public void findAccountById(){
        ApplicationContext context = new ClassPathXmlApplicationContext("com/jaolvv/JDBCdatabase/applicationContext.xml");
        AccountDao accountDao =(AccountDao) context.getBean("accountDao");
        Account account = accountDao.findAccountById(3);
        System.out.println(account);
    }

    @Test   //查询所有账户信息
    public void findAccount(){
        ApplicationContext context = new ClassPathXmlApplicationContext("com/jaolvv/JDBCdatabase/applicationContext.xml");
        AccountDao accountDao =(AccountDao) context.getBean("accountDao");
        List<Account> list = accountDao.findAccount();
        for (Account account : list) {
            System.out.println(account);
        }
    }
}

源码地址:Github

事务测试

在实际开发中,操作数据库时都会涉及到事务管理问题,为此Spring提供了专门用于事务处理的API。Spring的事务管理简化了传统的事务管理流程,并且在一定程度上减少了开发者的工作量。

在XML里加更两条

<!-- 配置事物管理器,指定数据源 -->
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

    <context:component-scan base-package="com.jaolvv.JDBCdatabase"></context:component-scan>
    <!-- 打开事物管理器(注解)驱动 -->
    <tx:annotation-driven transaction-manager="transactionManager"></tx:annotation-driven>

在之前的AccountDao接口里加一个抽象方法

    //模拟转账方法
    public void transfer(String outAccount,String inAccount,Double money);

在AccountDaoImpl里编写具体代码

@Override
    @Transactional(propagation = Propagation.REQUIRED,isolation = Isolation.DEFAULT,readOnly = false)
    public void transfer(String outAccount, String inAccount, Double money) {
        //收款,收款账号的金额=收款账号的金额+money
        String sql1 = "update account set balance=balance+? where username=?";
        jdbcTemplate.update(sql1, money, inAccount);

        //汇款,汇款账号的金额=汇款账号的金额-money
        String sql2 = "update account set balance=balance-? where username=?";
        jdbcTemplate.update(sql2,money,outAccount);
    }

在测试类里实现

@Test
    public void transfer(){
        ApplicationContext context = new ClassPathXmlApplicationContext("com/jaolvv/JDBCdatabase/applicationContext.xml");
        AccountDao accountDao =(AccountDao) context.getBean("accountDao");
        accountDao.transfer("Jaolvv","Liu",300.0);
        System.out.println("转账成功");
    }
    @Test
    public void transferanotaion() {
        ApplicationContext context = new ClassPathXmlApplicationContext("com/jaolvv/JDBCdatabase/applicationContext.xml");
        AccountDao accountDao =(AccountDao) context.getBean("accountDao");
        accountDao.transfer("Liu", "Jaolvv", 1200.0);
        System.out.println("转账成功!");
    }

执行结果

钱钱钱钱钱.jpg