【Java Web开发学习】Spring配置数据源

2022-10-18 11:53:49

Spring配置数据源

转载：https://www.cnblogs.com/yangchongxing/p/10027495.html

==============================================================

===============================================================

1、使用JNDI数据源

Spring应用程序经常部署在Java EE应用服务器中，如WebSphere、JBoss或甚至像Tomcat这样的Web容器中。这些服务器允许你配置通过JNDI获取数据源。这种配置的好处在于数据源完全可以在应用程序之外进行管理，这样应用程序只需在访问数据库的时候查找数据源就可以了。

参考文档：The Tomcat JDBC Connection Pool https://tomcat.apache.org/tomcat-9.0-doc/jdbc-pool.html

在服务器的server.xml的Context元素下配置Resource

<Resource

 name="jdbc/mysql"

 auth="Container"

 type="javax.sql.DataSource"

 factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"

 testWhileIdle="true"

 testOnBorrow="true"

 testOnReturn="false"

 validationQuery="SELECT 1"

 validationInterval="30000"

 timeBetweenEvictionRunsMillis="30000"

 maxActive="100"

 minIdle="10"

 maxWait="10000"

 initialSize="10"

 removeAbandonedTimeout="60"

 removeAbandoned="true"

 logAbandoned="true"

 minEvictableIdleTimeMillis="30000"

 jmxEnabled="true"

 jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"

 username="root"

 password="123456"

 driverClassName ="com.mysql.jdbc.Driver"

 url="jdbc:mysql://localhost:3306/ycxfw"

 />

Java配置查找数据源

package cn.ycx.config;

import javax.naming.NamingException;

import javax.sql.DataSource;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

import org.springframework.context.annotation.Profile;

import org.springframework.jndi.JndiObjectFactoryBean;

@Profile("PROD")

@Configuration

public class JndiDataSourceConfig {

    @Bean

    public DataSource dataSource() throws IllegalArgumentException, NamingException {

        System.out.println("JndiDataSourceConfig...");

        JndiObjectFactoryBean jndi = new JndiObjectFactoryBean();

        jndi.setJndiName("jdbc/mysql");

        jndi.setResourceRef(true);//自动添加 java:comp/env/ 前缀

        jndi.setProxyInterface(javax.sql.DataSource.class);

        jndi.afterPropertiesSet();

        return (DataSource) jndi.getObject();

    }

}

使用xml配置数据源时需要使用注解@ImportResource(value="classpath:context.xml")导入xml文件

@Profile("PROD")

@Configuration
@ImportResource(value="classpath:context.xml")

public class JndiDataSourceConfig

<jee:jndi-lookup id="dataSource" jndi-name="jdbc/mysql" resource-ref="true"></jee:jndi-lookup>

2、使用嵌入式数据源

package cn.ycx.config;

import javax.sql.DataSource;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

import org.springframework.context.annotation.ImportResource;

import org.springframework.context.annotation.Profile;

import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder;

import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType;

@Profile("DEV")

@Configuration

public class EmbededDataSourceConfig {

    @Bean

    public DataSource dataSource() {

        System.out.println("EmbededDataSourceConfig...");

        return new EmbeddedDatabaseBuilder()

                .setType(EmbeddedDatabaseType.H2)

                .addScript("classpath:schema.sql")

                .addScript("classpath:data.sql")

                .build();

    }

}

使用xml配置数据源时需要使用注解@ImportResource(value="classpath:context.xml")导入xml文件

@Profile("DEV")

@Configuration

@ImportResource(value="classpath:context.xml")

public class EmbededDataSourceConfig

<jdbc:embedded-database id="dataSource" type="H2">

    <jdbc:script location="classpath:schema.sql"/>

    <jdbc:script location="classpath:data.sql"/>

</jdbc:embedded-database>

3、使用C3P0数据源

Java配置

package cn.ycx.web.config;

import java.beans.PropertyVetoException;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

import com.mchange.v2.c3p0.ComboPooledDataSource;

@Configuration

public class DataSourceConfig {

    @Bean(name="dataSource")

    public DataSource dataSource() throws PropertyVetoException {

        ComboPooledDataSource comboPooledDataSource =  new ComboPooledDataSource();

        comboPooledDataSource.setDriverClass("com.mysql.jdbc.Driver");

        comboPooledDataSource.setJdbcUrl("jdbc:mysql:///ycxdb?useUnicode=true&autoReconnect=true&rewriteBatchedStatements=TRUE");

        comboPooledDataSource.setUser("root");

        comboPooledDataSource.setPassword("123456");

        comboPooledDataSource.setInitialPoolSize(10);

        comboPooledDataSource.setMinPoolSize(10);

        comboPooledDataSource.setMaxPoolSize(50);

        comboPooledDataSource.setMaxIdleTime(3600);

        // 解决mysql timeout

        comboPooledDataSource.setTestConnectionOnCheckout(false);

        comboPooledDataSource.setTestConnectionOnCheckin(true);

        comboPooledDataSource.setIdleConnectionTestPeriod(3600);

        return comboPooledDataSource;

    }

}

xml配置

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">

    <property name="driverClass" value="com.mysql.jdbc.Driver" />

    <property name="jdbcUrl" value="jdbc:mysql:///ycxdb?useUnicode=true&autoReconnect=true&rewriteBatchedStatements=TRUE" />

    <property name="user" value="root" />

    <property name="password" value="123456" />

    <property name="initialPoolSize" value="10" />

    <property name="minPoolSize" value="10" />

    <property name="maxPoolSize" value="50" />

    <property name="maxIdleTime" value="3600" />

    <!-- 解决mysql timeouts -->

    <property name="testConnectionOnCheckout" value="${jdbc.testConnectionOnCheckout}" />

    <property name="testConnectionOnCheckin" value="${jdbc.testConnectionOnCheckin}" />

    <property name="idleConnectionTestPeriod" value="${jdbc.idleConnectionTestPeriod}" />

</bean>

使用数据源的Java业务代码

package cn.ycx.service;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.Statement;

import java.time.LocalDateTime;

import java.time.format.DateTimeFormatter;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import java.util.Random;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Service;

@Service

public class IndexServiceImpl implements IndexService {

    @Autowired

    private DataSource dataSource;

    @Override

    public List<Map<String, String>> get() {

        try {

            List<Map<String, String>> list = new ArrayList<Map<String, String>>();

            Map<String, String> item = null;

            Connection con = dataSource.getConnection();

            Statement stm = con.createStatement();

            ResultSet rs = stm.executeQuery("select id,username from user");

            while(rs.next()) {

                item = new HashMap<String, String>();

                item.put("id", rs.getString("id"));

                item.put("username", rs.getString("username"));

                list.add(item);

            }

            rs.close();

            stm.close();

            con.close();

            return list;

        } catch (Exception e) {

            e.printStackTrace();

        }

        return null;

    }

    public Map<String, String> add() {

        try {

            Random r = new Random();

            LocalDateTime dt = LocalDateTime.now();

            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMMddHHmmss");

            Connection con = dataSource.getConnection();

            PreparedStatement pstm = con.prepareStatement("insert into user(id,username)values(?,?)");

            pstm.setString(1, dt.format(formatter));

            pstm.setString(2, String.valueOf(r.nextInt()));

            int count = pstm.executeUpdate();

            pstm.close();

            con.close();

            Map<String, String> data = new HashMap<String, String>();

            data.put("status", String.valueOf(count));

            return data;

        } catch (Exception e) {

        }

        return null;

    }

}

注入数据源

@Autowired
private DataSource dataSource;

使用Connection con = dataSource.getConnection();取得链接

码农公寓

相关文章