Spring JDBC 的使用

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

针对数据库操作 Spring框架提供了JdbcTemplate 类，该类是Spring框架数据抽象层的基础

    用到的包：

一、Spring JDBC配置

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans-4.3.xsd">
    <!--  配置数据源-->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
       <!-- 数据库驱动 -->
       <property name = "driverClassName" value = "com.mysql.jdbc.Driver" />
       <!-- 连接数据库URL-->
       <property name = "url" value = "jdbc:mysql://localhost/spring" />
       <!-- 连接数据库的用户名-->
       <property name = "username" value = "root" />
       <!-- 连接数据库的密码-->
       <property name = "password" value = "kangxg198811" />
       
    </bean>
    <!-- 配置JDBC 模版-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
       <!-- 默认必须使用数据源 -->
       <property name="dataSource" ref ="dataSource"></property>
    </bean>

</beans>

二 Spring JdbcTemplate 的常用用法

 1. execute()

 1.1. 终端创建 spring 数据库

  

mysql -u root -p

Enter password: 密码

create database spring;

use spring;1.2 创建测试类 dbcTemplateTest

package com.kangxg.jdbc;

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

public class jdbcTemplateTest {

    public static void main(String[] args) {
        
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
        JdbcTemplate jdTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
        jdTemplate.execute("create table account("+"id int primary key auto_increment,"+"username varchar(50),"+"balance double)");
        
    }

}

1.3 debug运行程序

    进入终端查看

或者进入 sequer pro 数据库管理工具查看

1.4 单元测试

   创建测试类

package com.kangxg.jdbc;


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

public class JdbcTemplateJunitTest {
    @Test
    public  void test() {
        // TODO Auto-generated method stub
        //The annotation @Test is disallowed for this Test cannot be resolved to a type
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
        JdbcTemplate jdTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
        jdTemplate.execute("create table account("+"id int primary key auto_increment,"+"username varchar(50),"+"balance double)");
        System.out.println("账户表account 创建成功");
    }

}

 

  出现 错误  Test cannot be resolved to a type 错误后 将鼠标移动到@Test 上  会显示错误提示框，选择 Add Junit4 library to the build path后 ，eclipse 会自动将Junit4的支持包加入到项目中

2. update()

   2.1 在  com.kangxg.jdbc 包中创建 Account类

 

package com.kangxg.jdbc;

public class Account {
  private  Integer id;       //账户ID
  private  String  userName; //用户名
  private  Double  balance;  //账户余额
  
  public Integer getId()
  {
      return this.id;
  }
  
  public String getUserName(){
      return this.userName;
  }
  public void setUsername(String username){
      this.userName = username;
  }
  public Double getBalance()
  {
      return this.balance;
  }
  
  public void setBalance(Double balance){
      this.balance = balance;
  }
  
  public String toString()
  {
      return "Account [id =" + id +"," +"userName =" +userName +", balance =" +balance +"]";
  }

  
}

2.2 在  com.kangxg.jdbc 包中创建 AccountDao 接口文件

 

 package com.kangxg.jdbc;

public interface AccountDao {
  //添加
  public int addAccount(Account account);
  //更新
  public int updateAccount(Account account);
  //删除
  public int deleteAccount(int id);
}

2.3  在  com.kangxg.jdbc 包中创建 AccountDao 接口 实现类 AccountDaoImpl

package com.kangxg.jdbc;

import org.springframework.jdbc.core.JdbcTemplate;

public class AccountDaoImpl implements AccountDao {
    //声明  JdbcTemplate 属性及其setter 方法
    private  JdbcTemplate jdbcTemplate;
    
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate)
    {
        this.jdbcTemplate = jdbcTemplate;
    }
    // 添加账户
    public int addAccount(Account account) {
    
        String sql = "insert into account(username,balance) value(?,?)";
        Object[] obj = new Object[]{
                           account.getUserName(),
                           account.getBalance()
        };
        //执行添加操作，返回的是受SQL语句影响的记录条数
        int num = this.jdbcTemplate.update(sql, obj);
        
        return num;
    }
    //更新账户
    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;
    }

    //删除账户
    public int deleteAccount(int id) {
        String sql = "delete from account where id =?";
        int num = this.jdbcTemplate.update(sql,id);
        return num;
    }

}

2.4 在单元测试类中增加测试方法 

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

 2.5 Debug JUnit run

2.6 查询数据

2.7  在单元测试类中增加 update（）测试方法

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

    debug 运行

信息: Loaded JDBC driver: com.mysql.jdbc.Driver
Sun Jan 21 22:00:35 CST 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
成功修改了1条数据！

    查询数据库

   

    2.8  单元测试类中增加 删除操作

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

    debug 运行

    

信息: Loaded JDBC driver: com.mysql.jdbc.Driver
Sun Jan 21 22:06:27 CST 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
成功删除了1条数据！

   数据库查询操作

3. query()

  3.1 在account 表中增加多个数据

  3.2  在AccountDao 接口中增加方法

 

  // 通过ID 查询
  public Account findAccountById(int id);
  // 查询所以账户
  public List<Account> findAllAccount();

3.3 在AccountDaoImpl类中实现方法

    public Account findAccountById(int id) {

        String sql = "select *  from account where id =?";
        Account act =  (Account) this.jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Account>(Account .class),id);
        return  act;
        
    }
    
    public List<Account> findAllAccount() {
        String sql = "select *  from account ";
    
        return this.jdbcTemplate.query(sql, new BeanPropertyRowMapper<Account>(Account.class));
    }

3.4 在单元测试类中增加测试方法

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

3.5 debug 运行程序