阿里开源数据库连接池druid配置使用

• 阿里开源数据库连接池druid配置使用

1、下载jar包

  druid-1.1.9.jar

  http://central.maven.org/maven2/com/alibaba/druid/

2、添加jar包至build path

3、配置applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>

<bean id="dbServiceId" class="service.impl.DbServiceImpl">
	<!--<property name="druidDataSource" ref="dataSource"></property>-->
	<property name="dbDao" ref="dbDaoId"></property>
</bean>

<bean id="dbDaoId" class="dao.impl.DbDaoImpl">
	<!--<property name="jdbcTemplate" ref="jdbcTemplate"></property>-->
	<property name="namedParameterJdbcTemplate" ref="namedParameterJdbcTemplate"></property>
</bean>
<!--
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
	<property name="dataSource" ref="dataSource"></property>	
</bean>
-->
<bean id="namedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
	<constructor-arg ref="dataSource"></constructor-arg>
</bean>

<bean id="dbConfigId" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
	<property name="location">
		<value>classpath:dbconfig.properties</value>
	</property>
</bean>

<bean id="log-filter" class="com.alibaba.druid.filter.logging.Log4jFilter">
	<property name="dataSourceLogEnabled" value="false" />
</bean>

 <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close"> 
 <property name="url" value="${jdbc_url}" />
 <property name="username" value="${jdbc_user}" />
 <property name="password" value="${jdbc_password}" />
 
 <property name="filters" value="log4j" />

 <property name="maxActive" value="20" />
 <property name="initialSize" value="1" />
 <property name="maxWait" value="60000" />
 <property name="minIdle" value="1" />

 <property name="timeBetweenEvictionRunsMillis" value="60000" />
 <property name="minEvictableIdleTimeMillis" value="300000" />
 
 <property name="validationQuery" value="SELECT 1" />
 <property name="testWhileIdle" value="true" />
 <property name="testOnBorrow" value="false" />
 <property name="testOnReturn" value="false" />

 <property name="poolPreparedStatements" value="true" />
 <property name="maxOpenPreparedStatements" value="20" />

 <property name="asyncInit" value="true" />
 <property name="proxyFilters">
    <list>
        <ref bean="log-filter"/>
    </list>
 </property>
 </bean> 

释义：

dbServiceId中注入druidDataSource使用
namedParameterJdbcTemplate和jdbcTemplate为spring对数据库操作的封装，注入dataSource使用
dbConfigId配置dataSource的参数
log-filter配置dataSource日志记录
dataSource使用阿里巴巴开源连接池druid

4、新建dbconfig.properties和log4j.properties
dbconfig.properties配置：
jdbc_url: jdbc:mysql://localhost:3306/XXX
jdbc_user: XXX
jdbc_password:XXX

log4j.properties配置：
log4j.rootLogger=error, A
log4j.appender.A=org.apache.log4j.ConsoleAppender
log4j.appender.A.layout=org.apache.log4j.PatternLayout
log4j.appender.A.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} [%c]-[%p] %m%n

5、3种使用dataSource数据源的方式：
引入的jar包：

(1）传统获取connection方式
package service.impl;
import service.DbService;
import javax.sql.DataSource;
import java.sql.Connection;
import dao.DbDao;
import alarm.AlarmBean;
import com.fasterxml.jackson.databind.ObjectMapper;

public class DbServiceImpl implements DbService{

/*直接获取连接池connection方式*/
  private Connection conn=null;
   private DataSource druidDataSource=null;

private DbDao dbDao;
 
//setter方法注入druidDataSource对象
public void setDruidDataSource(DataSource druidDataSource) {
	this.druidDataSource=druidDataSource;
}
public DataSource getDruidDataSource() {
	return druidDataSource;
}

public void setDbDao(DbDao dbDao) {
	this.dbDao=dbDao;
}
public DbDao getDbDao() {
	return dbDao;
}
public void testDbDao() {
	this.dbDao.selectTest();
}
   /*直接获取连接池connection方式*/
public Connection getConnection() {
	try {
		conn=this.druidDataSource.getConnection();
		return conn;
	} catch(Exception e) {
		e.printStackTrace();
	}
	return conn;
}

}

DbService dbService = (DbService) context.getBean("dbServiceId");
Connection conn=dbService.getConnection();
try {
		Statement st=conn.createStatement();
		ResultSet rs=st.executeQuery("select * from alarm_on");
		while(rs.next()) {
			System.out.println(rs.getInt("id")+" "+rs.getString("status")+" "+rs.getString("level"));
		}
		rs.close();
		st.close();
		conn.close();
	} catch (Exception e) {
		e.printStackTrace();
	}

(2)/jdbcTemplate方式/

private JdbcTemplate jdbcTemplate;
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate=jdbcTemplate;
	}
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}
	public void selectTest() {
		String sql="select count(*) from alarm_on";
		int count=this.jdbcTemplate.queryForObject(sql, Integer.class);
		System.out.println("总数:"+count);
	}

(3)NamedParameterJdbcTemplate方式

private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
	public void setNamedParameterJdbcTemplate(NamedParameterJdbcTemplate namedParameterJdbcTemplate) {
		this.namedParameterJdbcTemplate=namedParameterJdbcTemplate;
	}
	public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
		return namedParameterJdbcTemplate;
	}
	public int insertAlarmMsg(AlarmBean alarm) {
		String sql="sql";
		SqlParameterSource sqlParameterSource = new BeanPropertySqlParameterSource(alarm);
		int result=this.namedParameterJdbcTemplate.update(sql, sqlParameterSource);
		return result;
	}

此方法需要对应一个实体类AlarmBean
larmBean {

public AlarmBean() {
	super();
	// TODO Auto-generated constructor stub
}
public AlarmBean(String phoneNumber, String cuPublic, String sendMonitor, String status, String happenTime,
		String level, String hostname, String hostip, String messageInfo, String value, String remarks) {
	super();
	this.phoneNumber = phoneNumber;
	this.cuPublic = cuPublic;
	this.sendMonitor = sendMonitor;
	this.status = status;
	this.happenTime = happenTime;
	this.level = level;
	this.hostname = hostname;
	this.hostip = hostip;
	this.messageInfo = messageInfo;
	this.value = value;
	this.remarks = remarks;
}

private String phoneNumber;
private String cuPublic;
private String sendMonitor;

private String status;
private String happenTime;
private String level;
private String hostname;
private String hostip;
private String messageInfo;
private String value;
//private String recoverTime;
//private String recoverValue;
private String remarks;

public String getPhoneNumber() {
	return phoneNumber;
}
public void setPhoneNumber(String phoneNumber) {
	this.phoneNumber = phoneNumber;
}
...
@Override
public String toString() {
	return "AlarmBean [phoneNumber=" + phoneNumber + ", cuPublic=" + cuPublic + ", sendMonitor="
			+ sendMonitor + ", status=" + status + ", happenTime=" + happenTime + ", level=" + level + ", hostname="
			+ hostname + ", hostip=" + hostip + ", messageInfo=" + messageInfo + ", value=" + value + ", remarks="
			+ remarks + "]";
}