spring与dbutilsTemplete数据库整合

最新推荐文章于 2024-05-03 10:06:59 发布

weixin_39919087

最新推荐文章于 2024-05-03 10:06:59 发布

阅读量103

点赞数

分类专栏： spring 文章标签： db spring

spring 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

依赖架包

		<dependency>
			<groupId>commons-dbutils</groupId>
			<artifactId>commons-dbutils</artifactId>
			<version>1.7</version>
		</dependency>

新建DbUtilsTemplete.java模板

package com.testfan.spring.dbutils;


import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory; 

//@Service
public class DbUtilsTemplate { 

	//@Autowired
    private DataSource dataSource; 
	
    private QueryRunner queryRunner; 
    private static final Log LOG = LogFactory.getLog(DbUtilsTemplate.class); 

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


    /** 
     * 执行sql语句 
     * @param sql sql语句 
     * @param params 参数数组 
     * @return 受影响的行数 
     */ 
    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 e) { 
            LOG.error("Error occured while attempting to update data", e); 
        } 
        return affectedRows; 
    } 
      
    /** 
     * 执行批量sql语句 
     * @param sql sql语句 
     * @param params 二维参数数组 
     * @return 受影响的行数的数组 
     */ 
    public int[] batchUpdate(String sql, Object[][] params) { 
        queryRunner = new QueryRunner(dataSource); 
        int[] affectedRows = new int[0]; 
        try { 
            affectedRows = queryRunner.batch(sql, params); 
        } catch (SQLException e) { 
            LOG.error("Error occured while attempting to batch update data", e); 
        } 
        return affectedRows; 
    }     

      
    /** 
     * 执行查询，将每行的结果保存到一个Map对象中，然后将所有Map对象保存到List中 
     * @param sql sql语句 
     * @param params 参数数组 
     * @return 查询结果 
     */ 
    @SuppressWarnings("unchecked") 
    public List<Map<String, Object>> findListMap(String sql, Object... params) { 
        queryRunner = new QueryRunner(dataSource); 
        List<Map<String, Object>> list = new ArrayList<Map<String,Object>>(); 
        try { 
            if (params == null) { 
                list = (List<Map<String, Object>>) queryRunner.query(sql, new MapListHandler()); 
            } else { 
                list = (List<Map<String, Object>>) queryRunner.query(sql, new MapListHandler(), params); 
            } 
        } catch (SQLException e) { 
            LOG.error("Error occured while attempting to query data", e); 
        } 
        return list; 
    } 
      
    /** 
     * 执行查询，将每行的结果保存到Bean中，然后将所有Bean保存到List中 
     * @param entityClass 类名 
     * @param sql sql语句 
     * @param params 参数数组 
     * @return 查询结果 
     */ 
    @SuppressWarnings("unchecked") 
    public <T> List<T> findList(String sql,Class<T> entityClass, Object... params) { 
        queryRunner = new QueryRunner(dataSource); 
        List<T> list = new ArrayList<T>(); 
        try { 
            if (params == null) { 
                list = (List<T>) queryRunner.query(sql, new BeanListHandler(entityClass)); 
            } else { 
                list = (List<T>) queryRunner.query(sql, new BeanListHandler(entityClass), params); 
            } 
        } catch (SQLException e) { 
            LOG.error("Error occured while attempting to query data", e); 
        } 
        return list; 
    } 
      

      
    /** 
     * 查询出结果集中的第一条记录，并封装成对象 
     * @param entityClass 类名 
     * @param sql sql语句 
     * @param params 参数数组 
     * @return 对象 
     */ 
    @SuppressWarnings("unchecked") 
    public <T> T findOne(String sql, Class<T> 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 e) { 
            LOG.error("Error occured while attempting to query data", e); 
        } 
        return (T) object; 
    } 
      
    /** 
     * 查询出结果集中的第一条记录，并封装成Map对象 
     * @param sql sql语句 
     * @return 封装为Map的对象 
     */ 
    public Map<String, Object> findFirst(String sql) { 
        return findFirst(sql, null); 
    } 
      
    /** 
     * 查询出结果集中的第一条记录，并封装成Map对象 
     * @param sql sql语句 
     * @param param 参数 
     * @return 封装为Map的对象 
     */ 
    public Map<String, Object> findFirst(String sql, Object param) { 
        return findFirst(sql, new Object[] { param }); 
    } 
      
    /** 
     * 查询出结果集中的第一条记录，并封装成Map对象 
     * @param sql sql语句 
     * @param params 参数数组 
     * @return 封装为Map的对象 
     */ 
    @SuppressWarnings("unchecked") 
    public Map<String, Object> findFirst(String sql, Object[] params) { 
        queryRunner = new QueryRunner(dataSource); 
        Map<String, Object> map = null; 
        try { 
            if (params == null) { 
                map = (Map<String, Object>) queryRunner.query(sql, new MapHandler()); 
            } else { 
                map = (Map<String, Object>) queryRunner.query(sql, new MapHandler(), params); 
            } 
        } catch (SQLException e) { 
            LOG.error("Error occured while attempting to query data", e); 
        } 
        return map; 
    } 
      
    /** 
     * 查询某一条记录，并将指定列的数据转换为Object 
     * @param sql sql语句 
     * @param columnName 列名 
     * @return 结果对象 
     */ 
    public Object findBy(String sql, String columnName) { 
        return findBy(sql, columnName, null); 
    } 
      
    /** 
     * 查询某一条记录，并将指定列的数据转换为Object 
     * @param sql sql语句 
     * @param columnName 列名 
     * @param param 参数 
     * @return 结果对象 
     */ 
    public Object findBy(String sql, String columnName, Object param) { 
        return findBy(sql, columnName, new Object[] { param }); 
    } 
      
    /** 
     * 查询某一条记录，并将指定列的数据转换为Object 
     * @param sql sql语句 
     * @param columnName 列名 
     * @param params 参数数组 
     * @return 结果对象 
     */ 
    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 e) { 
            LOG.error("Error occured while attempting to query data", e); 
        } 
        return object; 
    } 
      
    /** 
     * 查询某一条记录，并将指定列的数据转换为Object 
     * @param sql sql语句 
     * @param columnIndex 列索引 
     * @return 结果对象 
     */ 
    public Object findBy(String sql, int columnIndex) { 
        return findBy(sql, columnIndex, null); 
    } 
      
    /** 
     * 查询某一条记录，并将指定列的数据转换为Object 
     * @param sql sql语句 
     * @param columnIndex 列索引 
     * @param param 参数 
     * @return 结果对象 
     */ 
    public Object findBy(String sql, int columnIndex, Object param) { 
        return findBy(sql, columnIndex, new Object[] { param }); 
    } 
      
    /** 
     * 查询某一条记录，并将指定列的数据转换为Object 
     * @param sql sql语句 
     * @param columnIndex 列索引 
     * @param params 参数数组 
     * @return 结果对象 
     */ 
    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 e) { 
            LOG.error("Error occured while attempting to query data", e); 
        } 
        return object; 
    } 
}

新建的模板整合到spring_db.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"
	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">

	<!-- 导入资源文件 -->
	<context:property-placeholder location="classpath*:jdbc.properties" />
	
	<!-- 配置 c3p0 数据源 -->
	<bean class="com.mchange.v2.c3p0.ComboPooledDataSource" id="dataSource">
		<property name="user" value="${jdbc.username}"></property>	
		<property name="password" value="${jdbc.password}"></property>	
		<property name="jdbcUrl" value="${jdbc.url}"></property>	
		<property name="driverClass" value="${jdbc.driverClass}"></property>	
		<property name="initialPoolSize" value="${jdbc.initPoolSize}"></property>	
		<property name="maxPoolSize" value="${jdbc.maxPoolSize}"></property>	
	</bean>
	
	<!-- 配置 spring 的 JdbcTemplate -->
	<bean class="org.springframework.jdbc.core.JdbcTemplate" id="jdbcTemplate" >
		<property name="dataSource" ref="dataSource"></property>
	</bean>
	
	<!-- 配置 spring 的 JdbcUtils -->
	<bean id="dbUtilsTemplate" class="com.testfan.spring.dbutils.DbUtilsTemplate"  >
		<property name="dataSource" ref="dataSource"></property>
	</bean>

</beans>

使用DbUtilsTemplate对象，通过注解自动注入

package com.testfan.dbutils;

import java.util.List;
import java.util.Map;

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 com.testfan.spring.dbutils.DbUtilsTemplate;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations= {"classpath:spring_db.xml"})//加载核心配置文件
public class dbutilsTest {

	@Autowired
	DbUtilsTemplate dbUtilsTemplate;
	
	@Test
	public void testUpdate() {
		String sql = "UPDATE t_user SET loginname = ? WHERE uid = ?";
		dbUtilsTemplate.update(sql, "test1","1");
	}
	
	@Test
	public void testQueryOne() {
		String sql = "select * from t_user where uid =?";
		//RowMapper<TestUser> rowMapper = new BeanPropertyRowMapper<>(TestUser.class);
		TestUser user = dbUtilsTemplate.findOne(sql, TestUser.class,"09b2c272-88f8-4f27-b9aa-a1f16ea7f6d4");
		System.out.println(user);
	}
	
	@Test
	public void testQueryForList() {
		String sql = "select * from t_user";
		//RowMapper<TestUser> rowMapper = new BeanPropertyRowMapper<>(TestUser.class);
		List<TestUser> user = dbUtilsTemplate.findList(sql, TestUser.class);
		System.out.println(user.size());
	}
	
	@Test
	public  void testQueryMap() {
		String sql = "select * from t_user";
		List<Map<String, Object>> user = dbUtilsTemplate.findListMap(sql);
		System.out.println(user);
	}
	
}

weixin_39919087

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
spring与dbutilsTemplete数据库整合

依赖架包 <dependency> <groupId>commons-dbutils</groupId> <artifactId>commons-dbutils</artifactId> <version>1.7</version> </dependency>新建DbUti...
复制链接

扫一扫