Spring-jdbc

Spring-jdbc

Spring的JDBC模块负责数据库资源管理和错误处理，大大简化了开发人员对数据库的操作，使得开发人员可以从繁琐的数据库操作收纳柜解脱出来，从而将更多的精力投入到编写业务逻辑当中。

1.jdbcTemple介绍

可以说，jdbcTemple类是Spring-JDBC的核心类，它提供了大量的更新和查询数据库的方法，我们就是使用这一些方法来操作数据库的。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kOUQtBDc-1645286527525)(C:\Users\19448\AppData\Roaming\Typora\typora-user-images\image-20211113184715732.png)]

• jdbcAccessor:为子类提供了一些访问数据库是使用的公共属性

• jdbcOperations：定义了在jdbcTemple类中可以使用的操作集合，包括添加、修改、查询和删除等操作

  2.Spring-jdbc的配置

DriverManagerDataSource：获取与数据库之间的连接

jdbc.username=root
jdbc.password=123456
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/spring?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false

 	<!--开启包扫描、注解开发模式-->
    <context:component-scan base-package="com.yao"/>
    <!--引入配置jdbc的外部资源文件-->
    <context:property-placeholder location="jdbc.properties"/>
    <!--配置数据源，相当于与数据库建立连接-->
    <bean id="DataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <!--构造器注入-->
        <constructor-arg name="username" value="${jdbc.username}"/>
        <constructor-arg name="password" value="${jdbc.password}"/>
        <constructor-arg name="url" value="${jdbc.url}"/>
        <!--setter方法-->
        <property name="driverClassName" value="${jdbc.driver}"/>
    </bean>
    <!--配置jdbc模板-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <!--注入数据源-->
        <constructor-arg ref="DataSource"/>
    </bean>

 <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.1</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>5.2.9.RELEASE</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.2.9.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.2.9.RELEASE</version>
        </dependency>

3.JdbcTemple的使用方法

• execute：执行sql语句
• update：执行插入、更新和删除操作
• query：执行查询操作

package com.yao.hui.DAO.impl;

import com.yao.hui.DAO.AccountDao;
import com.yao.hui.pojo.Account;
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 AccountDaoImpl implements AccountDao {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    @Override
    public Account findAccountById(int id) {
        String sql = "select id,name,balance from account where id = ?";
        return jdbcTemplate.queryForObject(sql,new  Object[]{id}, new BeanPropertyRowMapper<>(Account.class));
    }

    @Override
    public List<Account> findAllAccounts() {
        String sql="select * from account";
        BeanPropertyRowMapper<Account> beanPropertyRowMapper = new BeanPropertyRowMapper<>(Account.class);
        return jdbcTemplate.query(sql,beanPropertyRowMapper);
    }

    @Override
    public int addAccount(Account account) {
        String sql="insert into account(name ,balance) value (?,?)";
        Object[] object = {account.getName(),account.getBalance()};
        return jdbcTemplate.update(sql,object);
}

    @Override
    public int addAccounts(List<Account> accounts) {
        for (Account account : accounts) {
            addAccount(account);
        }
        return accounts.size();
    }


    @Override
    public int deleteAccount(int id) {
        String sql = "delete from account where id = ?";
        return jdbcTemplate.update(sql,id);
    }

    @Override
    public int updateAccount(Account account) {
        String sql = "update account set name = ? ,balance = ? where id = ?";
        return jdbcTemplate.update(sql,account.getName(),account.getBalance(),account.getId());
    }
}

package com.yao.hui.DAO.impl;

import com.yao.hui.DAO.AccountDao;
import com.yao.hui.pojo.Account;
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 AccountDaoImpl implements AccountDao {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    @Override
    public Account findAccountById(int id) {
        String sql = "select id,name,balance from account where id = ?";
        return jdbcTemplate.queryForObject(sql,new  Object[]{id}, new BeanPropertyRowMapper<>(Account.class));
    }

    @Override
    public List<Account> findAllAccounts() {
        String sql="select * from account";
        BeanPropertyRowMapper<Account> beanPropertyRowMapper = new BeanPropertyRowMapper<>(Account.class);
        return jdbcTemplate.query(sql,beanPropertyRowMapper);
    }

    @Override
    public int addAccount(Account account) {
        String sql="insert into account(name ,balance) value (?,?)";
        Object[] object = {account.getName(),account.getBalance()};
        return jdbcTemplate.update(sql,object);
}

    @Override
    public int addAccounts(List<Account> accounts) {
        for (Account account : accounts) {
            addAccount(account);
        }
        return accounts.size();
    }


    @Override
    public int deleteAccount(int id) {
        String sql = "delete from account where id = ?";
        return jdbcTemplate.update(sql,id);
    }

    @Override
    public int updateAccount(Account account) {
        String sql = "update account set name = ? ,balance = ? where id = ?";
        return jdbcTemplate.update(sql,account.getName(),account.getBalance(),account.getId());
    }
}
@Override
    public int updateAccount(Account account) {
        String sql = "update account set name = ? ,balance = ? where id = ?";
        return jdbcTemplate.update(sql,account.getName(),account.getBalance(),account.getId());
    }
}