package com.mdj.dmdatabase.test;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import com.mdj.dmdatabase.DmDriverManager.DatabaseManager;
import com.mdj.dmdatabase.javabean.Guest;
/**
* @since <a href="http://blog.csdn.net/mak0000">Connectionpool
* EasyconnectionPool</a>
* @author 武汉软件工程职业学院<br>
* 孟德军<br>
* 2009-01-01<br>
* 封装了Dbutils工具<br>
* 的部分细节
* @version 2.0
*
*/
public class Test {
/**
* @see #manager DatabaseManager类的实例.
*/
private DatabaseManager manager;
/**
* @see #con <p bgcolor="#9acd32">java.sql.Connection con Connection实例</p>
*/
private Connection con = null;
public Test(String path) throws IOException, SQLException {
manager = DatabaseManager.getinstance(path);
// con = manager.getConnection();
/**
* 此处书写代码. 查询数据无需用connection接收
*/
/*int a=manager.generalUpdate("insert into guestbook(author,title,ip,updatetime,content) values('王红梅','你过得怎么样','192.168.18.1','2009-01-04','你过得怎么样')");
System.out.println(a);*/
Map map = manager.mapexecuteQuery("select * from guestbook");
for (int i = 0; i < map.size(); i++) {
System.out.println(map.get("author") + " " + map.get("title") + " "
+ map.get("ip") + map.get("updatetime") + " "
+ map.get("content"));
}
List list = manager.listexecuteQuery("select * from guestbook",
Guest.class);
Guest guestbook = null;
for (int i = 0; i < list.size(); i++) {
guestbook = (Guest) list.get(i);
System.out
.println(guestbook.getAuthor() + "\t"
+ guestbook.getTitle() + "\t" + guestbook.getIp()
+ "\t" + guestbook.getUpdatetime() + "\t"
+ guestbook.getContent());
}
manager.releaseConnection(con); manager.closeConnectionPool();
}
public static void main(String[] args) {
String path = System.getProperty("user.dir") + "\\src\\sysconfig.xml";
try {
new Test(path);
} catch (SQLException e) {
} catch (IOException e) {
e.printStackTrace();
}
}
}
配置文件
<Context>
<Resource name="DMdatabase" type="javax.sql.DataSource"
auth="Container" driverClassName="dm.jdbc.driver.DmDriver"
url="jdbc:dm://localhost:12345/guest" username="SYSDBA"
password="SYSDBA" maxActive="10" maxIdle="5" maxWait="6000" delaytime="10" logpath="..//poolserver.log" logsize="1024" />
</Context>
<!--
1.0使用的配置文件,已被以上内容替代.
<?xml version="1.0" encoding="UTF-8"?>
<driver xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<dbmsdriver name="华工达梦数据库系统">
<driverclass>dm.jdbc.driver.DmDriver</driverclass>
<url>jdbc:dm://localhost:12345/guest</url>
<username>SYSDBA</username>
<password>123456</password>
<maxconnection>8</maxconnection>
<minconnection>1</minconnection>
<maxwait>5</maxwait>
<logpath>..//poolserver.log</logpath>
</dbmsdriver>
</driver>
-->
<!--
<Context>
.........................................
</Context>
-->
<!--实例数据库
create database guest datafile 'd:\guest\guest.dbf' size 35;
set current database guest;
create table guestbook(author varchar(16) not null,title varchar(30),ip varchar(16),updatetime date,content text);
insert into guestbook(author,title,ip,updatetime,content)values('孟德军','达梦数据库测试','192.168.18.1','2009-01-05','示例数据库');
select * from guestbook;
-->
name="DMdatabase"
type="javax.sql.DataSource"
auth="Container"
driverClassName="dm.jdbc.driver.DmDriver"
url="jdbc:dm://localhost:12345/guest"
username="SYSDBA"
password="SYSDBA"
maxActive=4
maxIdle=2
maxWait=6000
delaytime=10
logpath="..//poolserver.log"
logsize=1024
接口
java 数据库连接池(接口)2009-05-08 06:17 P.M.
package com.mdj.dmdatabase.develop;
import java.io.PrintWriter;
import java.io.Serializable;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Vector;
import javax.sql.DataSource;
/**
* @see Connectionpool EasyconnectionPool
* @author 武汉软件工程职业学院<br>
* 孟德军<br>
* 2009-01-01<br>
* 封装了Dbutils工具<br>
* 的部分细节
* @version 2.0
*/
public interface DmDataSource extends DataSource, Serializable {
/**
* @see #resultset 存放数据库的表信息.
*/
ResultSet resultset = null;
/**
* @see #con 数据库连接
*/
Connection con = null;
/**
* @see #pool 连接池容器.可自行选择。
*/
Vector<Connection> pool = null;
/**
* @see #driverclass 数据库驱动类
*/
String driverclass = null;
/**
* @see #uername 数据库用户名
*/
String uername = null;
/**
* @see #password 数据库密码
*/
String password = null;
/**
* @see #url 连接数据库url
*/
String url = null;
/**
* @see #filepath 配置文件路径.
*/
String filepath = null;
/**
* @see #logpath 日志文件路径.
*/
String logpath = null;
/**
* @see #maxwaittime 允许等待时间
*/
int maxwaittime = 0;
/**
* @see #delaytime 延迟时间
*/
int delaytime = 0;
/**
* @see #maxconnection 最大连接
*/
int maxconnection = 0;
/**
* @see #minconnection 最小连接
*/
int minconnection = 0;
/**
* @see #poolsize 连接池大小.
*/
int poolsize = 0;
public void closeConnectionPool() throws SQLException;
/**
* @param username 数据库用户名<br>password 数据库密码
* @deprecated 建议使用getconnection()
* @see #getConnection() 参见
*/
public Connection getConnection(String username, String password)
throws SQLException;
/**
* @see #getConnection() 无用户名和密码,以在配置文件中配置。
*/
public Connection getConnection() throws SQLException;
public PrintWriter getLogWriter() throws SQLException;
public int getLoginTimeout() throws SQLException;
public void releaseConnection(Connection con);
public void setLogWriter(PrintWriter out) throws SQLException;
public void setLoginTimeout(int seconds) throws SQLException;
public Map mapexecuteQuery(String sql) throws SQLException;
public List listexecuteQuery(String sql, Class beanclass)
throws SQLException;
public ResultSet getTables(String catalog, String schemapattern,
String tablenamepattern, String[] types) throws SQLException;
public int getJDBCMajorVersion() throws SQLException;
public int generalUpdate(String sql) throws SQLException;
public ResultSet generalQuery(String sql) throws SQLException;
public String toChinese(String str, String oldcharsetName,
String newcharsetName) throws UnsupportedEncodingException;
}
实现
package com.mdj.dmdatabase.DmDriverManager;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Timer;
import java.util.TimerTask;
import java.util.Vector;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;
import com.mdj.dmdatabase.pool.Dmconnection;
/**
* @see Connectionpool EasyconnectionPool
* @author <hr><p bgcolor="#9acd32">武汉软件工程职业学院</p>
* 孟德军<br>
* 2009-01-03<br>
* 封装了Dbutils<br>
* 工具的部分细节
*
* @version 2.0
*/
public class DatabaseManager extends Dmconnection {
/**
*
*/
private static final long serialVersionUID = 1L;
/**
* @see #databasemetaData 数据源.
*/
private DatabaseMetaData databasemetaData;
/**
* @see #resultset 存放数据库的表信息.
*/
private ResultSet resultset = null;
/**
* @see #map 存放mapexecuteQuery查询结果
*/
private Map map = null;
/**
* @see #list 存放listexecuteQuery查询结果
*/
private List list = null;
/**
* @see #con 数据库连接
*/
private Connection con = null;
/**
* @see #pool 连接池容器.可自行选择。
*/
private Vector<Connection> pool = null;
/**
* @see #driverclass 数据库驱动类
*/
private String driverclass = "dm.jdbc.driver.DmDriver";
/**
* @see #uername 数据库用户名
*/
private String username = "SYSDBA";
/**
* @see #password 数据库密码
*/
private String password = "123456";
/**
* @see #url 连接数据库url
*/
private String url = "jdbc:dm://localhost:12345/guest";
/**
* @see #filepath 配置文件路径.
*/
private static String filepath = null;
/**
* @see #logpath 日志文件路径.
*/
private String logpath = "..//server.log";
/**
* @see #maxwaittime 允许等待时间
*/
private int maxwaittime = 10;
/**
* @see #delaytime 延迟时间
*/
private int delaytime = 10;
/**
* @see #maxconnection 最大连接
*/
private int maxconnection = 10;
/**
* @see #minconnection 最小连接
*/
private int minconnection = 5;
/**
* @see #poolsize 连接池大小.
*/
private int poolsize = 10;
/**
* @see #manager 本类实例.
*/
private static DatabaseManager manager = null;
/**
* @see #timer 扫描连接池.
*/
private Timer timer;
private PrintWriter writer;
private boolean loading = false;
/**
* @see #addConnection() 为连接池添加连接
* @throws ClassNotFoundException
*/
private DatabaseManager() {
try {
init();
File file = new File(logpath);
writer = new PrintWriter(new FileWriter(file.getAbsolutePath(),
true), true);
addConnection();
} catch (IOException e) {
e.printStackTrace();
}
}
private void init() {
if (loading == false) {
config(filepath);
} else {
readconfig(filepath);
}
pool = new Vector<Connection>(maxconnection);
timer = new Timer(true);
}
static public synchronized DatabaseManager getinstance(String path) {
// 必须先初始化变量filepath,然后在创建变量.
DatabaseManager.filepath = path;
if (manager == null) {
manager = new DatabaseManager();
}
return manager;
}
private void addConnection() {
serverlog("server start");
for (int i = 0; i < maxconnection; i++) {
try {
Class.forName(driverclass);
DriverManager.setLogWriter(writer);
DriverManager.setLoginTimeout(maxwaittime);
con = DriverManager.getConnection(url, username, password);
pool.add(con);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
serverlog(e.getMessage());
} catch (SQLException e) {
// TODO Auto-generated catch block
serverlog(e.getMessage());
}
}
}
/**
* @see #releaseConnection(Connection) 释放连接,返还连接池.
* @return con 当前连接.
*
*/
public synchronized Connection getConnection() throws SQLException {
if (pool.size() > 0) {
con = pool.get(0);
pool.remove(0);
scaner();
return con;
} else {
return con;
}
}
/**
* @see #releaseConnection(Connection) 释放连接,返还连接池.
* @return con 当前连接.
*
*/
public synchronized Connection getConnection(String username,
String password) {
if (pool.size() > 0) {
con = pool.get(0);
pool.remove(0);
return con;
} else {
return con;
}
}
public synchronized void releaseConnection(Connection con) {
pool.add(con);
}
/**
* @return 无返回值
* @see #closeConnectionPool() 关闭连接,清空连接池.
* @throws SQLException
*/
public void closeConnectionPool() throws SQLException {
for (int i = 0; i < pool.size(); i++) {
pool.get(i).close();
pool.clear();
timer.cancel();
}
}
/**
* @see #scaner()扫描连接池.
*/
private void scaner() {
TimerTask task = new TimerTask() {
@Override
public void run() {
// TODO Auto-generated method stub
if (manager.pool.size() < maxconnection) {
while (manager.pool.size() < maxconnection) {
addConnection();
}
}
}
};
timer.schedule(task, 10000, 10000);
}
/**
* @see #config(String) 读取配置文件.<br>
* 初始化数据库连接数据.
* @since <hr>http://blog.csdn.net/mak0000 <a
* href="http://blog.csdn.net/mak0000">更多信息</a>
* @throws path
* 为配置文件路径,文件路径错误会抛出FileNotFoundException异常
*/
/**
* @see #config(String) 读取配置文件.
*/
private void config(String path) {
loading = true;
DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
try {
DocumentBuilder builder = factory.newDocumentBuilder();
Document document = builder.parse(DatabaseManager.filepath);
NodeList nodelist = document.getElementsByTagName("dbmsdriver");
for (int i = 0; i < nodelist.getLength(); i++) {
Element element = (Element) nodelist.item(i);
driverclass = element.getElementsByTagName("driverclass").item(
0).getFirstChild().getNodeValue();
url = element.getElementsByTagName("url").item(0)
.getFirstChild().getNodeValue();
username = element.getElementsByTagName("username").item(0)
.getFirstChild().getNodeValue();
password = element.getElementsByTagName("password").item(0)
.getFirstChild().getNodeValue();
maxconnection = Integer
.parseInt(element.getElementsByTagName("maxconnection")
.item(0).getFirstChild().getNodeValue());
minconnection = Integer
.parseInt(element.getElementsByTagName("minconnection")
.item(0).getFirstChild().getNodeValue());
logpath = element.getElementsByTagName("logpath").item(0)
.getFirstChild().getNodeValue();
maxwaittime = Integer.parseInt(element.getElementsByTagName(
"maxwaittime").item(0).getFirstChild().getNodeValue());
delaytime = Integer.parseInt(element.getElementsByTagName(
"delaytime").item(0).getFirstChild().getNodeValue());
}
} catch (ParserConfigurationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
serverlog(new Date() + ":\t" + e.getMessage() + "\n");
} catch (SAXException e) {
e.printStackTrace();
serverlog(new Date() + ":\t" + e.getMessage() + "\n");
} catch (IOException e) {
e.printStackTrace();
serverlog(new Date() + ":\t" + e.getMessage() + "\n");
}
}
/**
* @deprecated 该方法为候选方法.
* @param path
* 配置文件路径.
*/
public void readconfig(String path) {
loading = false;
Properties properties = new Properties();
try {
InputStream in = new FileInputStream(DatabaseManager.filepath);
properties.load(in);
driverclass = properties.getProperty("driverclass");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
maxconnection = Integer.parseInt(properties
.getProperty("maxconnection"));
minconnection = Integer.parseInt(properties
.getProperty("minconnection"));
delaytime = Integer.parseInt(properties.getProperty("delaytime"));
logpath = properties.getProperty("logpath");
in.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* @see #serverlog(String) 日志.
* @param msg
* 异常及日常信息.
* @since <a href="http://www.w3.org/xmlschema">建议将变量以配置文件形式存放,<br>
* 以方便使用,例如xml,properties<br>
* 文件.</a>
*/
private void serverlog(String msg) {
writer.println(new Date() + ":" + msg);
writer.close();
}
/**
* @see #setLogWriter(PrintWriter) set delay time
* @throws SQLException
*/
public void setLoginTimeout(int seconds) throws SQLException {
this.maxwaittime = seconds;
}
/**
* @see #getLoginTimeout() get delay time.
* @throws SQLException
*/
public int getLoginTimeout() throws SQLException {
return maxwaittime;
}
/**
* @see #setLogWriter(PrintWriter) set logwriter.
* @throws SQLException
*/
public void setLogWriter(PrintWriter out) throws SQLException {
this.writer = out;
}
/**
* @see #getLogWriter() get logwriter
* @throws SQLException
*/
public PrintWriter getLogWriter() throws SQLException {
return writer;
}
/**
* @see #listexecuteQuery(String, Class) put the var in list,list includes objects bean
* @since Book.class<br>
* Book book=(Book)result.get(i);<br>
* book.getid();
*/
@Override
public List listexecuteQuery(String sql, Class beanclass)
throws SQLException {
con = getConnection();
DbUtils.loadDriver(driverclass);
//build a queryrunner instance.
QueryRunner queryrunner = new QueryRunner();
list = (List) queryrunner.query(con, sql,
new BeanListHandler(beanclass));
releaseConnection(con);
return list;
}
/**
* @see #mapexecuteQuery(String) map形式存放数据.list includes map
*/
@Override
public Map mapexecuteQuery(String sql) throws SQLException {
// TODO Auto-generated method stub
con = getConnection();
DbUtils.loadDriver(driverclass);
QueryRunner queryrunner = new QueryRunner();
list = (List) queryrunner.query(con, sql, new MapListHandler());
releaseConnection(con);
for (int i = 0; i < list.size(); i++) {
map = (Map) list.get(i);
}
return map;
}
/**
* @see #getTables(String, String, String, String[])获取数据库表结构.
* @param catalog
* ,schemaPattern,tableNamePattern,types
*
*/
public ResultSet getTables(String catalog, String schemaPattern,
String tableNamePattern, String[] types) throws SQLException {
con = getConnection();
databasemetaData = con.getMetaData();
resultset = databasemetaData.getTables(catalog, schemaPattern,
tableNamePattern, types);
releaseConnection(con);
return resultset;
}
/**
* @see #getJDBCMajorVersion() jdbc主版本号.
*/
@Override
public int getJDBCMajorVersion() throws SQLException {
// TODO Auto-generated method stub
con = getConnection();
databasemetaData = con.getMetaData();
int version = databasemetaData.getJDBCMajorVersion();
releaseConnection(con);
return version;
}
/**
* @see #getJDBCMinorVersion() jdbc次版本号.
*/
@Override
public int getJDBCMinorVersion() throws SQLException {
// TODO Auto-generated method stub
con = getConnection();
databasemetaData = con.getMetaData();
int version = databasemetaData.getJDBCMinorVersion();
releaseConnection(con);
return version;
}
@Override
/*
* @see #getMaxConnections() 数据库支持的同时建立连接的对象数目.
*/
public int getMaxConnections() throws SQLException {
con = getConnection();
databasemetaData = con.getMetaData();
int maxconnection = databasemetaData.getMaxConnections();
releaseConnection(con);
return maxconnection;
}
/**
* @see #getMaxStatements() 数据库支持同时打开statement的数目.
*/
@Override
public int getMaxStatements() throws SQLException {
// TODO Auto-generated method stub
con = getConnection();
databasemetaData = con.getMetaData();
int minconnection = databasemetaData.getMaxStatements();
releaseConnection(con);
return minconnection;
}
/**
* @see #generalUpdate(String) 更新.
*/
public int generalUpdate(String sql) {
int result = 0;
Statement statement;
try {
con = getConnection();
statement = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
result = statement.executeUpdate(sql);
statement.close();
releaseConnection(con);
} catch (SQLException e) {
// TODO Auto-generated catch block
serverlog(e.getSQLState() + "\t" + e.getMessage());
}
return result;
}
public ResultSet generalQuery(String sql) {
ResultSet result = null;
Statement statement;
try {
con = getConnection();
statement = con.createStatement();
result = statement.executeQuery(sql);
result.close();
statement.close();
releaseConnection(con);
} catch (SQLException e) {
serverlog(e.getSQLState() + "\t" + e.getMessage());
}
return result;
}
public String toChinese(String str, String oldcharsetName,
String newcharsetName) {
String chinesestr = null;
if (str.equals(null) || str == "") {
chinesestr = "";
} else {
try {
chinesestr = new String(str.getBytes(oldcharsetName),
newcharsetName);
} catch (UnsupportedEncodingException e) {
serverlog("\t" + e.getMessage());
}
}
return chinesestr;
}
}
测试