VastbaseG100

使用连接池

Vastbase G100 jdbc连接可使用连接池，比如阿里巴巴开源连接池Druid。

无框架配置

下载Druid的jar包，除Vastbase G100 JDBC驱动外，引入pom依赖如下：

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.6</version>
</dependency>
<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
<version>1.2.16</version>
</dependency>

在resource目录下配置db_server.properties文件

driverClassName=org.postgresql.Driver
url=jdbc:postgresql://127.0.0.1:5432/test
username=root
password=123456
filters=stat
initialSize=2
maxActive=300
maxWait=60000
timeBetweenEvictionRunsMillis=60000
minEvictableIdleTimeMillis=300000
validationQuery=SELECT 1
testWhileIdle=true
testOnBorrow=false
testOnReturn=false
poolPreparedStatements=false
maxPoolPreparedStatementPerConnectionSize=200;

Java代码

// DBPoolConnection类
import com.alibaba.druid.pool.*;
import org.apache.log4j.*;
import java.io.FileInputStream;
import java.sql.SQLException;
import java.util.Properties;

public class DBPoolConnection {
	static Logger log = LogManager.getLogger(DBPoolConnection.class);
	private static DBPoolConnection dbPoolConnection = null;
	private static DruidDataSource druidDataSource = null;

	static {
		Properties properties = new Properties();
		try {
			properties.load(new FileInputStream(ClassLoader.getSystemResource("db_server.properties").getPath()));
			druidDataSource = (DruidDataSource)DruidDataSourceFactory.createDataSource(properties); //DruidDataSrouce工厂模式
		} catch (Exception e) {
			log.error("获取配置失败");
		}
	}

	/**
	* 数据库连接池单例
	* @return
	*/
	public static synchronized DBPoolConnection getInstance(){
		if (null == dbPoolConnection){
			dbPoolConnection = new DBPoolConnection();
		}
		return dbPoolConnection;
	}

	/**
	* 返回druid数据库连接
	* @return
	* @throws SQLException
	*/
	public DruidPooledConnection getConnection() throws SQLException{
		return druidDataSource.getConnection();
	}
}

在Vastbase G100创建表student：

create table student(id int,name varchar(20),num int,score numeric(4,2));

运行以下代码连接Vastbase G100并写入一条数据到表student

import com.alibaba.druid.pool.DruidPooledConnection;
import java.sql.*;

public class TestDruid{
    public static void main(String[] args) throws SQLException {
        DruidPooledConnection dsConn = DBPoolConnection.getInstance().getConnection();
        Connection conn = dsConn.getConnection();

        //execute SQL
        String sql = "INSERT INTO student VALUES (NULL, ?, ?, ?);";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, "sam");
        pstmt.setInt(2, 35);
        pstmt.setDouble(3, 88.5);
        int i = pstmt.executeUpdate();
        System.out.println("insert rows： " + i);
        
        pstmt.close();
        //release connection and give back to pool
        conn.close();
    }
}

Tomcat配置

Tomcat中植入

Tomcat依赖修改

下载druid的依赖jar，放入到tomcat/lib目录下。

web.xml配置

修改tomcat下的web.xml，里面增加druid配置：增加在web.xml中servlet的default配置后面

<!-- druid view -->
<servlet>
    <servlet-name>DruidStatView</servlet-name>
    <servlet-class>com.alibaba.druid.support.http.StatViewServlet</servlet-class>
    <init-param>
        <!-- 允许清空统计数据 -->
        <param-name>resetEnable</param-name>
        <param-value>true</param-value>
    </init-param>
    <init-param>
        <!-- 用户名 -->
        <param-name>loginUsername</param-name>
        <param-value>admin</param-value>
    </init-param>
    <init-param>
        <!-- 密码 -->
        <param-name>loginPassword</param-name>
        <param-value>admin</param-value>
    </init-param>
</servlet>

<servlet-mapping>
    <servlet-name>DruidStatView</servlet-name>
    <url-pattern>/druid/*</url-pattern>
</servlet-mapping>
<!-- druid view -->

context.xml配置

将source中的factory修改为：com.alibaba.druid.pool.DruidDataSourceFactory

<Resource name="prpDataSource"
          auth="Container"
          type="javax.sql.DataSource"
          factory="com.alibaba.druid.pool.DruidDataSourceFactory"
          defaultTransactionIsolation="READ_COMMITTED"
          username="用户名"
          password="密码"
          filters="stat,wall"
          driverClassName="org.postgresql.Driver"
          url="jdbc:postgresql://127.0.0.1:5432/dbname"
          maxActive="96"
          minIdle="8"
          removeabandoned="true"
          removeabandonedtimeout="60"
          logabandoned="true">
</Resource>

catalina.properties文件

如果druid没有加载到，再修改catalina.properties文件

项目中配置

引入pom依赖

<dependency>
    <groupId>com.alibaba.druid</groupId>
    <artifactId>druid-wrapper</artifactId>
    <version>0.2.9</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.9</version>
</dependency>
<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.8</version>
</dependency>

context.xml与web.xml配置

参考bulkload中的相关配置

filter相关配置

如果项目中有使用Authorization权限拦截的，需要在白名单中配置/druid/路径

<filter>
    <filter-name>Authorization</filter-name>
    <filter-class>
        com.sinosoft.common.AuthorizationFilter
    </filter-class>
    <init-param>
        <param-name>loginPage</param-name>
        <param-value>/index.html</param-value>
    </init-param>
    <init-param>
        <param-name>valid</param-name>
        <param-value>true</param-value>
    </init-param>
    <init-param>
        <param-name>ignore</param-name>
        <param-value>
            /login.do;
            /claim/swfFlowBeforeQuery.do;
            /interFaces/;
            /prpall/;
            /www/;
            /druid/;
        </param-value>
    </init-param>
</filter>

Spring配置

引入相关依赖

<!-- SPRINGBOOT WEB -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!-- SPRINGBOOT DRUID -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>
        
        <!-- SPRINGBOOT JDBC -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <!-- Vastbase G100 -->
        <dependency>
            <groupId>cn.com.vastdata</groupId>
            <artifactId>vastbase</artifactId>
            <version>${vastbase-g100-jdbc.version}</version>
        </dependency>
        
        <!-- LOG4J -->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.16</version>
            <scope>compile</scope>
        </dependency>

xml配置

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:aop="http://www.springframework.org/schema/aop" 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.xsd
        http://www.springframework.org/schema/aop
        http://www.springframework.org/schema/aop/spring-aop.xsd">
    <!-- 配置_Druid和Spring关联监控配置 -->
    <bean id="druid-stat-interceptor"
        class="com.alibaba.druid.support.spring.stat.DruidStatInterceptor"></bean>
    <!-- 方法名正则匹配拦截配置 -->
    <bean id="druid-stat-pointcut" class="org.springframework.aop.support.JdkRegexpMethodPointcut"
        scope="prototype">
        <property name="patterns">
            <list>
            	<!--此处根据实际项目的包名更改-->
                <value>com.beini.*</value>
            </list>
        </property>
    </bean>
    <aop:config proxy-target-class="true">
        <aop:advisor advice-ref="druid-stat-interceptor"
	pointcut-ref="druid-stat-pointcut" />
    </aop:config>
</beans>

结合mybatis的xml配置

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:context="http://www.springframework.org/schema/context" xmlns:p="http://www.springframework.org/schema/p"
xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:util="http://www.springframework.org/schema/util"
	    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
	http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd
	http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.0.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd
	http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.0.xsd">
<!-- 配置dao层扫描 -->
	<context:property-placeholder location="classpath:conf/database.properties" /> 
     <!-- 通过JDBC模版获取数据库连接 -->
    <bean scope="singleton" id="jdbcTemplate"
        class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
	<!-- 数据库连接池 -->
	<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
		destroy-method="close">
		<property name="url" value="${jdbc.url}" />
		<property name="username" value="${jdbc.username}" />
		<property name="password" value="${jdbc.password}" />
		<property name="driverClassName" value="${jdbc.driverClassName}" />
	</bean>
	
	<!-- 让spring管理sqlsessionfactory 使用mybatis和spring整合包中的 -->
	<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
		<!-- 数据库连接池 -->
		<property name="dataSource" ref="dataSource" />
		<!-- 加载mybatis的全局配置文件 -->
		<property name="mapperLocations" value="classpath:com/mapper/*.xml" />
	</bean>
	
	<!-- 配置mapper扫描包 -->
	<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
		<property name="basePackage" value="com.mapper" />
	</bean>
</beans>

yml配置

spring:
  # 数据源配置
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: org.postgresql.Driver
    url: jdbc:postgresql://127.0.0.1:5432/test
    username: root
    password: 123456
    # 连接池配置
    druid:
      # 初始化大小，最小，最大
      initial-size: 5
      min-idle: 5
      max-active: 20
      # 配置获取连接等待超时的时间
      max-wait: 60000
      # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位毫秒
      time-between-eviction-runs-millis: 60000
      # 配置一个连接在池中最小生存时间
      min-evictable-idle-time-millis: 300000
      validation-query: SELECT 1 FROM sys_user
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      # 打开 PSCache，并且指定每个连接上 PSCache 的大小
      pool-prepared-statements: true
      max-pool-prepared-statement-per-connection-size: 20
      # 配置监控统计拦截的 Filter，去掉后监控界面 SQL 无法统计，wall 用于防火墙
      filters: stat,wall,log4j
      # 通过 connection-properties 属性打开 mergeSql 功能；慢 SQL 记录
      connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
      # 配置 DruidStatFilter
      web-stat-filter:
        enabled: true
        url-pattern: /*
        exclusions: .js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*
      # 配置 DruidStatViewServlet
      stat-view-servlet:
        url-pattern: /druid/*
        # IP 白名单，没有配置或者为空，则允许所有访问
        allow: 127.0.0.1
        # IP 黑名单，若白名单也存在，则优先使用
        deny: 192.168.31.253
        # 禁用 HTML 中 Reset All 按钮
        reset-enable: false
        # 登录用户名/密码
        login-username: root
        login-password: 123