Spring的JDBC开发-spring学习06

Spring的数据库开发技术

​ 学习的目标：

了解Spring中JDBC模块的作用
熟悉Spring JDBC的配置
掌握 JDBC Template类中几个常用方法的使用

1.SpringJDBC

​ Spring的JDBC模块负责数据库资源和错误处理，大大简化了开发人员对数据库的操作。

​ 1.1Spring jdbcTemplate解析

​ jdbcTemplate类：是Spring框架数据抽象层的基础，其他更高层次的抽象类却是构建于 jdbcTemplate类之上的。 jdbcTemplate是Spring JDBC的核心类。

​ 1.2 继承关系：

​ 继承自抽象类jdbcAccessor,实现了接口jdbcOperations

​ 1.3jdbcTemplate类提供访问数据库时候的公共属性：

​ DataSource:主要功能获取数据库的连接。具体实现的时候还可以引入对数据库连接的缓冲池和分布式事务的支持。

​ SQLExceptionTranslator

2.Spring JDBC的配置

​ 2.1Spring JDBC模块由4个包组成：

• core: 包含JDBC的核心功能

• dataSource:访问数据源的使用工具类

• object:以面向对象的方式访问数据库

• support：包含了core和object包的支持类

  ​ 2.2dataSource的4个属性：

  1. driverClassName:所使用的驱动名字
  2. url:数据源所在地址
  3. username：访问数据库的用户名
  4. passward:访问数据库的密码

  3.Spring jdbcTemplate的常用方法：

  1.execute():

  ​ execute(String sql)执行SQL语句

  2.update():

  ​ 插入，插入，删除数据操作

  3.Query():

  ​ 处理各种对数据库查询操作

  ​ 步骤：

  1.mySQL里面创建一个spring数据库。

  creare database spring;
  use spring;
  show tables;
  

  2.在src里面建配置文件

  <?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
         http://www.springframework.org/schema/context/spring-context.xsd">
  
      <!--配置数据源-->
      <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
          <!--数据库驱动-->
          <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
          <!--连接数据库的url-->
          <property name="url" value="jdbc:mysql://localhost:3306/spring"/>
          <!--连接数据库的用户名-->
          <property name="username" value="root"/>
          <!--连接数据库的密码-->
          <property name="password" value="123456"/>
      </bean>
  
      <!--配置JDBC模板-->
      <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
          <!--默认必须使用数据源-->
          <property name="dataSource" ref="dataSource"/>
      </bean>
  
      <!--开启注解扫描-->
      <!--<context:component-scan base-package="com.lz.jiaotong"/>-->
      <!--定义id为accountDao的Bean-->
      <bean id="accountDao" class="com.lz.jiaotong.dao.impl.AccountDaoImpl">
          <!--将jdbcTempla注入到accountDao实例中-->
          <property name="jdbcTemplate" ref="jdbcTemplate"/>
      </bean>
  </beans>
  

  ​ 3.创建实体类account

  package com.lz.jiaotong.doamin;
  
  public class Account {
      private Integer id;
      private String username;
      private Double balance;
  
      public Integer getId() {
          return id;
      }
  
      public void setId(Integer 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;
      }
  
      public String toString() {
          return "Account [id=" + id + ", "
                  + "username=" + username + ", "
                  + "balance=" + balance + "]";
      }
  }
  

  ​ 4.创建实体接口和实现类

  package com.lz.jiaotong.dao;
  
  import com.lz.jiaotong.doamin.Account;
  
  import java.util.List;
  
  public interface AccountDao {
      public int addAccount(Account account);
      public int updateAccount(Account account);
      public int deleteAccount(int id);
  
      public  Account findAccountById(int id);
      public List<Account> findAllAccount();
  }
  

package com.lz.jiaotong.dao.impl;

import com.lz.jiaotong.dao.AccountDao;
import com.lz.jiaotong.doamin.Account;
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()
@Repository("accountDao")
public class AccountDaoImpl implements AccountDao {
    private JdbcTemplate jdbcTemplate;

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

    @Override
    public int addAccount(Account account) {
        String sql = "insert into account(username, balance) values (?,?)";
        Object[] obj = new Object[] {
                account.getUsername(),
                account.getBalance()
        };
        int num = this.jdbcTemplate.update(sql, obj);
        return num;
    }

    @Override
    public int updateAccount(Account account) {
        String sql="update account set username=?, balance=? where id=?";
        Object[] params = new Object[] {
                account.getUsername(),
                account.getBalance(),
                account.getId()
        };
        int num = this.jdbcTemplate.update(sql, params);
        return num;
    }

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

    @Override
    public Account findAccountById(int id) {
        String sql = "select * from account where id = ?";
        RowMapper<Account> rowMapper = new BeanPropertyRowMapper<Account>(Account.class);
        return this.jdbcTemplate.queryForObject(sql, rowMapper, id);
    }

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

5.编写测试类

import com.lz.jiaotong.dao.AccountDao;
import com.lz.jiaotong.doamin.Account;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.util.List;

public class MyJdbcTemplateTest {
    @Test
    public void excuteTableTest(){
        ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
        JdbcTemplate jdbcTemplate = (JdbcTemplate) context.getBean("jdbcTemplate");
        jdbcTemplate.execute("create table account(id int primary key auto_increment," +
                "username varchar(64)," +
                "balance double);");
        System.out.println("账户表account创建成功!");
    }

    @Test
    public void addAccountTest() {
        ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
        AccountDao accountDao = (AccountDao) context.getBean("accountDao");
        Account account = new Account();
        account.setUsername("Tom");
        account.setBalance(5000.0);
        int num = accountDao.addAccount(account);
        if (num > 0) {
            System.out.println("成功插入了" + num + "条数据！");
        } else {
            System.out.println("插入操作执行失败！");
        }
    }

    @Test
    public void updateAccountTest() {
        ApplicationContext context= new ClassPathXmlApplicationContext("bean.xml");
        AccountDao accountDao = (AccountDao) context.getBean("accountDao");
        Account account = new Account();
        account.setId(4);
        account.setUsername("Joy");
        account.setBalance(5050.00);
        int num = accountDao.updateAccount(account);
        if (num > 0) {
            System.out.println("成功修改了" + num + "条数据！");
        }else {
            System.out.println("修改操作执行失败！");
        }
    }

    @Test
    public void deleteAccountTest() {
        ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
        AccountDao accountDao = (AccountDao) context.getBean("accountDao");
        int num = accountDao.deleteAccount(2);
        if (num > 0) {
            System.out.println("成功删除了" + num + "条数据！");
        } else {
            System.out.println("删除操作执行失败！");
        }
    }

    @Test
    public void findAccountByTest() {
        ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
        AccountDao accountDao = (AccountDao) context.getBean("accountDao");
        Account account = accountDao.findAccountById(3);
        System.out.println(account);
    }

    @Test
    public void findAllAccountTest() {
        ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
        AccountDao accountDao = (AccountDao) context.getBean("accountDao");
        List<Account> account = accountDao.findAllAccount();
        for (Account act : account) {
            System.out.println(act);
        }
    }

    public static void main(String[] args) {
        ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
    }
}