H2+Druid+dbUtil 简单使用

  此例子只是起到一个抛砖引玉的作用， 更多的关于Dbutil的API还需要读者自己去查看，不多说了， 直接上代码

<dependency>
		<groupId>com.google.guava</groupId>
			<artifactId>guava</artifactId>
			<version>12.0</version>
		</dependency>
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>0.2.25</version>
		</dependency>
		<dependency>
			<groupId>com.h2database</groupId>
			<artifactId>h2</artifactId>
			<version>1.4.181</version>
		</dependency>
		<dependency>
			<groupId>commons-dbutils</groupId>
			<artifactId>commons-dbutils</artifactId>
			<version>1.6</version>
		</dependency>

1.数据库连接配置文件:

driverClassName=org.h2.Driver
url=jdbc:h2:~/testAuto1
username=sa
password=sa
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

2.建表语句：

CREATE TABLE investmentData ( 
     id int(7)  AUTO_INCREMENT, 
     name varchar(120) DEFAULT NULL, 
     PRIMARY KEY (id) 
);
CREATE INDEX investNameIndex ON investmentData (name);

3.获得数据源，数据连接等

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

import javax.sql.DataSource;

import com.alibaba.druid.pool.DruidDataSourceFactory;


/**
 * The Class DataSourceUtil.
 */
public class DataSourceUtil {

	
	private static String confile = Util.getClassPath() + "config"
			+ File.separator + "conf.properties";
	// public static String confile = "druid.properties";
	public static Properties p = null;

	static {
		p = new Properties();
		InputStream inputStream = null;
		try {
			// java应用
			System.out.println(confile);
			File file = new File(confile);
			inputStream = new BufferedInputStream(new FileInputStream(file));
			p.load(inputStream);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (inputStream != null) {
					inputStream.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	
	public static final DataSource getDataSource()
			throws Exception {
		DataSource dataSource =
		 DruidDataSourceFactory.createDataSource(p);
		return dataSource;
	}
}

4.建表，查询，批量插入等。

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.ArrayUtils;



public class DBOperator {
	
	private void tableCreate() throws Exception {
		QueryRunner queryRunner = new QueryRunner(
				DataSourceUtil.getDataSource());
		byte[] bs = new byte[1024];
		String confile = Util.getClassPath() + "config" + File.separator + "db.sql";
		InputStream inputStream = new FileInputStream(new File(confile));
		IOUtils.read(inputStream, bs);
		String sql = new String(bs);
		Object[] arraylist  = queryRunner.insert(sql, new ArrayHandler());
		System.out.println("+===" + arraylist.length);
	}

	private void addData() throws Exception {
		String sql ="insert into investmentData(name) values(?);";
		QueryRunner queryRunner = new QueryRunner(DataSourceUtil.getDataSource());
		String confile = Util.getClassPath() + "investmentData" + File.separator + "ILLEGAL_DATA.txt";
		InputStream inputStream = new FileInputStream(new File(confile));
		List<String>  listArr = IOUtils.readLines(inputStream);
		String stringArr[][] = new String[listArr.size()][];
		for (int i = 0; i < stringArr.length; i++) {
			stringArr[i]= ArrayUtils.toArray( listArr.get(i));
		}
		int[] arraylist = queryRunner.batch(sql,stringArr );
		System.out.println("==>>>"+ arraylist);
	}
	
	public String queryInvestmentName(String investmentName) throws Exception {
		String tableName ="INVESTMENTDATA";
		QueryRunner queryRunner = new QueryRunner(
		DataSourceUtil.getDataSource());
		String checkTableIfExists="SELECT TABLE_NAME  FROM INFORMATION_SCHEMA.TABLES  where TABLE_NAME   =?";
		Object[] tableArrayList  = queryRunner.query(checkTableIfExists, new ArrayHandler(),new Object[]{tableName});
		if(!((tableArrayList.length >=1) && tableArrayList[0].equals("INVESTMENTDATA"))){
			tableCreate();
			addData();
		}
		String querySql ="select name from investmentData where name =?";
		Object[] arraylist  = queryRunner.query(querySql, new ArrayHandler(), new Object[]{investmentName});
		return arraylist.length >=1 ?arraylist[0].toString():null;
	}
	
	public static void main(String[] args) throws Exception {
		DBOperator dbOperator = new DBOperator();
		String investmentName = "ccc";
		String name  = dbOperator.queryInvestmentName(investmentName);
		System.out.println(name);
	}
}