Spring中JdbcTemplate结合连接池（c3p0、dbcp、JdbcDateSupport）及配置properties

最新推荐文章于 2024-06-03 17:24:45 发布

cnflyhz

最新推荐文章于 2024-06-03 17:24:45 发布

阅读量1w

点赞数 1

分类专栏： Spring

本文链接：https://blog.csdn.net/pengyu1801/article/details/60143810

版权

Spring 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

首先创建一个数据库：

CREATE TABLE spring_jdbc(id INT PRIMARY KEY,
             username VARCHAR(8),
             uno VARCHAR(11));

INSERT INTO spring_jdbc(id,username,uno) VALUES(1,'fly','123');
INSERT INTO spring_jdbc(id,username,uno) VALUES(2,'teemo','123');

导入相应的JAR：
这里写图片描述

接下来先在代码中链接一下数据库及使用（简单了解，估计开发中不会用这种方式）

package com.fly.jdbc;

import org.apache.commons.dbcp.BasicDataSource;
import org.springframework.jdbc.core.JdbcTemplate;

public class TestDemoJDBC {

    public static void main(String[] args) {
        // 创建数据源（连接池）dbcp
        BasicDataSource basicDataSource = new BasicDataSource();

        // 基本4项
        basicDataSource.setDriverClassName("com.mysql.jdbc.Driver"); // 加载驱动
        basicDataSource.setUrl("jdbc:mysql://localhost:3306/student");  // 数据库的
        basicDataSource.setUsername("root");
        basicDataSource.setPassword("123");

        // 创建模板
        JdbcTemplate jdbcTemplate = new JdbcTemplate();
        jdbcTemplate.setDataSource(basicDataSource);

        // 使用   插入一条数据
        jdbcTemplate.update("insert into spring_jdbc(id,username,uno) values(?,?,?);", "3","qian","123");
    }
}

第一步：建立一个整个项目公用的JavaBean，因为后面几个都要用到，就建一个公用的就好了。

package com.fly.jdbc;

public class Personbean {
    private int id;
    private String username;
    private String uno;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public String getUno() {
        return uno;
    }
    public void setUno(String uno) {
        this.uno = uno;
    }
    @Override
    public String toString() {
        return "Personbean [id=" + id + ", username=" + username + ", uno="
                + uno + "]";
    }

}

接下来介绍c3p0的使用：

配置文件beans：

<?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"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xsi:schemaLocation="http://www.springframework.org/schema/beans 
                           http://www.springframework.org/schema/beans/spring-beans.xsd
                           http://www.springframework.org/schema/aop 
                           http://www.springframework.org/schema/aop/spring-aop.xsd
                           http://www.springframework.org/schema/context 
                           http://www.springframework.org/schema/context/spring-context.xsd">

    <!-- 创建数据源 c3p0 设置驱动，数据库连接，账号，密码-->
    <bean id="dataSourceId" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="com.mysql.jdbc.Driver"></property>
        <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/student"></property>
        <property name="user" value="root"></property>
        <property name="password" value="123"></property>
    </bean>
    <!-- 创建模板 ,需要注入数据源-->
    <bean id="jdbcTemplateId" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSourceId"></property>
    </bean>

    <!-- 配置dao -->
    <bean id="userDaoId" class="com.fly.jdbc.c3p0.UserDao">
        <property name="jdbcTemplate" ref="jdbcTemplateId"></property>
    </bean>

</beans>

Dao的代码：

package com.fly.jdbc.c3p0;

import java.util.List;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import com.fly.jdbc.Personbean;

public class UserDao {

    //获取模板
    private JdbcTemplate jdbcTemplate;
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate){
        this.jdbcTemplate = jdbcTemplate;
    }

    public void update(Personbean person){
        String sql  = "update spring_jdbc set username=?,uno=? where id=?";
        Object[] args  =  {person.getUsername(),person.getUno(),person.getId()};
        jdbcTemplate.update(sql, args);
    }

    /**
     * 查询所有
     * @return
     */
    public List<Personbean> queryAll() {
        return jdbcTemplate.query("select * from spring_jdbc", ParameterizedBeanPropertyRowMapper.newInstance(Personbean.class));
    }
}

测试代码：

package com.fly.jdbc.c3p0;

import java.util.List;
import org.junit.Test;
import org.springframework.beans.factory.BeanFactory;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.fly.jdbc.Personbean;

public class TestDemo {

    @Test
    public void demo(){
        String xmlPath = "com/fly/jdbc/c3p0/beans.xml";
        BeanFactory context = new ClassPathXmlApplicationContext(xmlPath);
        UserDao userDao = (UserDao) context.getBean("userDaoId");
        List<Personbean> queryAll = userDao.queryAll();
        for (Personbean personbean : queryAll) {
            System.out.println(personbean);
        }
    }
}

dbcp的使用案例

配置文件bean:

<?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"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xsi:schemaLocation="http://www.springframework.org/schema/beans 
                           http://www.springframework.org/schema/beans/spring-beans.xsd
                           http://www.springframework.org/schema/aop 
                           http://www.springframework.org/schema/aop/spring-aop.xsd
                           http://www.springframework.org/schema/context 
                           http://www.springframework.org/schema/context/spring-context.xsd">

    <!-- 创建数据源 -->
    <bean id="dataSourceId" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
        <property name="url" value="jdbc:mysql://localhost:3306/student"></property>
        <property name="username" value="root"></property>
        <property name="password" value="123"></property>
    </bean>
    <!-- 创建模板 ,需要注入数据源-->
    <bean id="jdbcTemplateId" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSourceId"></property>
    </bean>

    <!-- 配置dao -->
    <bean id="personDaoId" class="com.fly.jdbc.dbcp.PersonDao">
        <property name="jdbcTemplate" ref="jdbcTemplateId"></property>
    </bean>

</beans>

Dao的代码：

package com.fly.jdbc.dbcp;

import org.springframework.jdbc.core.JdbcTemplate;
import com.fly.jdbc.Personbean;

public class PersonDao {

    // 获取模板
    private  JdbcTemplate jdbcTemplate;

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

    public void update(Personbean person){
        String sql = "update spring_jdbc set username=?,uno=? where id=?";
        Object[] arg = {person.getUsername(),person.getUno(),person.getId()};
        jdbcTemplate.update(sql,arg);
    }
}

测试代码：

package com.fly.jdbc.dbcp;

import org.junit.Test;
import org.springframework.beans.factory.BeanFactory;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.fly.jdbc.Personbean;

public class TestDemo1 {

    @Test
    public void demo(){
        Personbean mPersonbean = new Personbean();
        mPersonbean.setId(1);
        mPersonbean.setUsername("hash");
        mPersonbean.setUno("123456");
        String xmlPath = "com/fly/jdbc/dbcp/beans.xml";
        BeanFactory context = new ClassPathXmlApplicationContext(xmlPath);
        PersonDao bean = (PersonDao) context.getBean("personDaoId");
        bean.update(mPersonbean);
    }
}

JdbcDateSupport案例介绍：

配置文件bean:

<?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"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xsi:schemaLocation="http://www.springframework.org/schema/beans 
                           http://www.springframework.org/schema/beans/spring-beans.xsd
                           http://www.springframework.org/schema/aop 
                           http://www.springframework.org/schema/aop/spring-aop.xsd
                           http://www.springframework.org/schema/context 
                           http://www.springframework.org/schema/context/spring-context.xsd">

    <!-- 创建数据源 c3p0-->
    <bean id="dataSourceId" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="com.mysql.jdbc.Driver"></property>
        <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/student"></property>
        <property name="user" value="root"></property>
        <property name="password" value="123"></property>
    </bean>

    <!-- 配置dao 
        * dao 继承 JdbcDaoSupport，之后只需要注入数据源，底层将自动创建模板
    -->
    <bean id="userDaoId" class="com.fly.jdbc.jdbcdaosupport.UserDao">
        <property name="dataSource" ref="dataSourceId"></property>
    </bean>

</beans>

dao代码：

package com.fly.jdbc.jdbcdaosupport;

import java.util.List;

import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.fly.jdbc.Personbean;

/**
 * 和上一个c3p0相比，就是在Dao类中继承JdbcDaoSupport，在XML文件中不配置JdbcTemplate
 * 就这一个区别
 * @author Administrator
 *
 */
public class UserDao extends JdbcDaoSupport{

    public void update(Personbean person){
        String sql  = "update spring_jdbc set username=?,uno=? where id=?";
        Object[] args  =  {person.getUsername(),person.getUno(),person.getId()};
        this.getJdbcTemplate().update(sql, args);
    }

    /**
     * 查询所有
     * @return
     */
    public List<Personbean> queryAll() {
        return this.getJdbcTemplate().query("select * from spring_jdbc", ParameterizedBeanPropertyRowMapper.newInstance(Personbean.class));
    }
}

测试：

package com.fly.jdbc.jdbcdaosupport;

import java.util.List;
import org.junit.Test;
import org.springframework.beans.factory.BeanFactory;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.fly.jdbc.Personbean;

public class TestDemo {

    @Test
    public void demo(){
        String xmlPath = "com/fly/jdbc/jdbcdaosupport/beans.xml";
        BeanFactory context = new ClassPathXmlApplicationContext(xmlPath);
        UserDao userDao = (UserDao) context.getBean("userDaoId");
        List<Personbean> queryAll = userDao.queryAll();
        for (Personbean personbean : queryAll) {
            System.out.println(personbean);
        }
    }
}

配置properties后的连接使用（很方便，扩展性强）

配置properties文件：jabcInfo.properties

jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.jdbcUrl=jdbc:mysql://localhost:3306/student
jdbc.user=root
jdbc.password=123

配置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"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xsi:schemaLocation="http://www.springframework.org/schema/beans 
                           http://www.springframework.org/schema/beans/spring-beans.xsd
                           http://www.springframework.org/schema/aop 
                           http://www.springframework.org/schema/aop/spring-aop.xsd
                           http://www.springframework.org/schema/context 
                           http://www.springframework.org/schema/context/spring-context.xsd">

    <!-- 加载配置文件 
        "classpath:"前缀表示 src下
        在配置文件之后通过  ${key} 获得内容
    -->
    <context:property-placeholder location="com/fly/jdbc/properties/jdbcInfo.properties"/>

    <!-- 创建数据源 c3p0-->
    <bean id="dataSourceId" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="${jdbc.driverClass}"></property>
        <property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>
        <property name="user" value="${jdbc.user}"></property>
        <property name="password" value="${jdbc.password}"></property>
    </bean>

    <!-- 配置dao 
        * dao 继承 JdbcDaoSupport，之后只需要注入数据源，底层将自动创建模板
    -->
    <bean id="userDaoFinalId" class="com.fly.jdbc.properties.UserDaoFinal">
        <property name="dataSource" ref="dataSourceId"></property>
    </bean>

</beans>

Dao类

package com.fly.jdbc.properties;

import java.util.List;

import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import com.fly.jdbc.Personbean;

/**
 * 和上一个c3p0相比，就是在Dao类中继承JdbcDaoSupport，在XML文件中不配置JdbcTemplate
 * 就这一个区别
 * @author Administrator
 *
 */
public class UserDaoFinal extends JdbcDaoSupport{

    public void update(Personbean person){
        String sql  = "update spring_jdbc set username=?,uno=? where id=?";
        Object[] args  =  {person.getUsername(),person.getUno(),person.getId()};
        this.getJdbcTemplate().update(sql, args);
    }

    /**
     * 查询所有
     * @return
     */
    public List<Personbean> queryAll() {
        return this.getJdbcTemplate().query("select * from spring_jdbc", ParameterizedBeanPropertyRowMapper.newInstance(Personbean.class));
    }

    /**
     * 
     * @param id
     * @return
     */
    public Personbean queryId(int id){
        return this.getJdbcTemplate().queryForObject("select * from spring_jdbc where id =?", ParameterizedBeanPropertyRowMapper.newInstance(Personbean.class),id);
    }
}

测试类：

package com.fly.jdbc.properties;

import org.junit.Test;
import org.springframework.beans.factory.BeanFactory;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.fly.jdbc.Personbean;

public class TestDemo {

    @Test
    public void demo(){
        String xmlPath = "com/fly/jdbc/properties/beans.xml";
        BeanFactory context = new ClassPathXmlApplicationContext(xmlPath);
        UserDaoFinal bean = (UserDaoFinal) context.getBean("userDaoFinalId");
        Personbean personbean = bean.queryId(1);
        System.out.println(personbean);
    }
}

cnflyhz

关注

1
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
Spring中JdbcTemplate结合连接池（c3p0、dbcp、JdbcDateSupport）及配置properties

首先创建一个数据库：CREATE TABLE spring_jdbc(id INT PRIMARY KEY, username VARCHAR(8), uno VARCHAR(11));INSERT INTO spring_jdbc(id,username,uno) VALUES(1,'fly','123');INSERT INTO spring
复制链接

扫一扫

专栏目录