初识Spring+dbutils连接数据库例子实战

一、项目介绍

使用idea创建一个spring项目，会自己写一个mysql 数据库工具类结合Spring使用（虽然Spring自带有数据库工具类）

二、概念

spring 的核心，ioc 和di
**ioc:**控制反转，把对象给spring进行管理，不需要再new了，通过xml文件配置beans，或者注解来交给spring管理，核心技术用到反射
**di:**依赖注入，把对象的属性已经交给spring 管理了，可以进行属性值的注入，管理属性，建立在ioc上，依赖注入方式也是可以通过xml,或者注解实现
spring 注解解析
ioc 注解
@Component —把对象交给spring 管理的注解
di 注解
@Value(“”) ----设置普通属性
@Autowired //by type/class 不能有相同的class,不然运行会报错
@Qualifier(“c3po”) //加上一起用就是by name/id
jdk 自带了注解
@Resource(name=“car3”) //by name/id ,如果不写则默认by type/class

三、dbutils 例子

前提：已经在idea 新建了spring 项目

3.1 maven pom.xml依赖如下


    mysql
    mysql-connector-java
    8.0.22



    commons-dbutils
    commons-dbutils
    1.7



    com.mchange
    c3p0
    0.9.5.5

3.2 代码如下

package com.springdemo.dbutils;

import com.mysql.cj.log.LogFactory;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import sun.rmi.runtime.Log;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
public class JdbTemplate {
    //初始化，连接数据库
    private DataSource dataSource;
    private QueryRunner queryRunner;

    public void setDataSource(DataSource dataSource){
        this.dataSource = dataSource;
    }

    
    public int update(String sql,Object... params){
        queryRunner = new QueryRunner(dataSource);
        int affectedRows = 0;
        try {
            if (params == null){

                affectedRows = queryRunner.update(sql);

            }else {
                affectedRows = queryRunner.update(sql, params);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return affectedRows;
    }

    
    public int[] batchUpdate(String sql,Object[][]params){
        queryRunner = new QueryRunner(dataSource);
        int[] affectedRows = new int[0];
        try {
            affectedRows = queryRunner.batch(sql,params);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return affectedRows;
    }

    
    public List> findListMap(String sql,Object... params){
        queryRunner = new QueryRunner(dataSource);
        List> list = new ArrayList>();
        try {
        if (params == null){
            list = queryRunner.query(sql, new MapListHandler());
        }else {
            list = queryRunner.query(sql, new MapListHandler(), params);
        }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }

    
    @SuppressWarnings("unchecked")
    public  List findList(String sql,Class entityClass, Object... params) {
        queryRunner = new QueryRunner(dataSource);
        List list = new ArrayList();
        try {
            if (params == null) {
                list = (List) queryRunner.query(sql, new BeanListHandler(entityClass));
            } else {
                list = (List) queryRunner.query(sql, new BeanListHandler(entityClass), params);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }



    
    @SuppressWarnings("unchecked")
    public  T findOne(String sql, Class entityClass, Object... params) {
        queryRunner = new QueryRunner(dataSource);
        Object object = null;
        try {
            if (params == null) {
                object = queryRunner.query(sql, new BeanHandler(entityClass));
            } else {
                object = queryRunner.query(sql, new BeanHandler(entityClass), params);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return (T) object;
    }

    
    public Map findFirst(String sql) {
        return findFirst(sql, null);
    }

    
    public Map findFirst(String sql, Object param) {
        return findFirst(sql, new Object[] { param });
    }

    
    @SuppressWarnings("unchecked")
    public Map findFirst(String sql, Object[] params) {
        queryRunner = new QueryRunner(dataSource);
        Map map = null;
        try {
            if (params == null) {
                map = (Map) queryRunner.query(sql, new MapHandler());
            } else {
                map = (Map) queryRunner.query(sql, new MapHandler(), params);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return map;
    }

    
    public Object findBy(String sql, String columnName) {
        return findBy(sql, columnName, null);
    }

    
    public Object findBy(String sql, String columnName, Object param) {
        return findBy(sql, columnName, new Object[] { param });
    }

    
    public Object findBy(String sql, String columnName, Object[] params) {
        queryRunner = new QueryRunner(dataSource);
        Object object = null;
        try {
            if (params == null) {
                object = queryRunner.query(sql, new ScalarHandler(columnName));
            } else {
                object = queryRunner.query(sql, new ScalarHandler(columnName), params);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return object;
    }

    
    public Object findBy(String sql, int columnIndex) {
        return findBy(sql, columnIndex, null);
    }

    
    public Object findBy(String sql, int columnIndex, Object param) {
        return findBy(sql, columnIndex, new Object[] { param });
    }

    
    public Object findBy(String sql, int columnIndex, Object[] params) {
        queryRunner = new QueryRunner(dataSource);
        Object object = null;
        try {
            if (params == null) {
                object = queryRunner.query(sql, new ScalarHandler(columnIndex));
            } else {
                object = queryRunner.query(sql, new ScalarHandler(columnIndex), params);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return object;
    }
}

四、spring XML +dbutils

把上面写的数据库类交给spring管理，用xml 方式
因为datasource ,要赋值连接数据库，所以会引用第三方类

4.1 白盒测试

spring+ junit
依赖junit 包

    
        junit
        junit
        4.12
        test

写测试类

import com.springdemo.dbutils.JdbTemplate;
import org.junit.Assert;
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.Map;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:jdbcDataSource.xml"})
public class DbUtilsIjunitTest {
      @Autowired
      private JdbTemplate jdbTemplate;

      @Test
      public void dbTest(){
        String sql = "SELECT* FROM sys_log WHERe id =? ";
          Map first = jdbTemplate.findFirst(sql, "1");
          int size = first.size();
          boolean actual = size >0;
          System.out.println("===="+first);
          Assert.assertTrue("find result is not null ",actual);
      }

}

4.2 spring 注解方式+ testng 白盒测试 4 .2.1 spring 要用注解方式，首先要在xml 配一个扫描，xml 格式如下

因为C3p0是第三方库，所以我还是保留写在xml上，代码注解方面，基于前面的数据库连接的类，主要是把把这个class 给spring ioc ，然后再注入值

把对象交给spring 的注解
//@Component
public class JdbTemplate {
    //初始化，连接数据库
    private DataSource dataSource;
    private QueryRunner queryRunner;

//    @Autowired //by type/class
//    @Qualifier("c3po")  //加上一起用就是by name/id
    public void setDataSource(DataSource dataSource){
        this.dataSource = dataSource;
    }

4.2.2 testng 白盒测试

首先也是引入包

    
        org.testng
        testng
        7.3.0

测试代码：

package com.springTest;

import com.springdemo.dbutils.JdbTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.testng.AbstractTestNGSpringContextTests;
import org.testng.Assert;
import org.testng.annotations.Test;

@ContextConfiguration(locations = { "classpath:ioc.xml" })
public class DbUtilstestngTest extends AbstractTestNGSpringContextTests {
    @Autowired
    private JdbTemplate jdbTemplate;

    @Test
    public void tDBTest(){
        String sql = "SELECt* FROM sys_log WHERe id =? ";
        Object jdbTemplateBy = jdbTemplate.findBy(sql, 2, "1");
        System.out.println("----"+jdbTemplateBy);
        Assert.assertNotNull(jdbTemplateBy);

    }

}

testng.xml

4.2.3遇到问题报错：

TestNG by default disables loading DTD from unsecured Urls. If you need to explicitly load the DTD from a http url, please do so by using the JVM argument
解决方法参考：https://zhuanlan.zhihu.com/p/348235776

testng 优势----多参数的覆盖，还有可以并行执行

五、拓展 5.1 第三方包的使用

5.2 scope

5.3 生命周期

5.4 properties 文件的读取

初识Spring+dbutils连接数据库例子实战

Java相关栏目本月热门文章