多年一直从事delphi和c#开发,这几年退出二线,这阵子由于团队需要,学习了java,并梳理了java的数据访问类,发出来给大家拍拍砖。
实现的功能是数据库的CRUD功能,另外有结合了C3P0的连接池管理。
废话不多说,直接贴代码 :
数据库访问接口类 IDbDriver
/**this interface define the database operation function
* fuzhou huilin corp.ltd @author lms 2016.07.05
* last update by lms 2016.07.07
*/
package hs.libra.core.db;
import java.util.List;
public interface IDbDriver {
public void init(String configFile); //to load database config file
public boolean openConnection(); //establish connection
public void closeConnection(); //close connection
public int executeSql(String sql); //run sql for effect rows
public int executeSql(String sql, Object[] params); //run sql with parameters
public int getAutoIncId(String table, String field); //to get new id of auto-increment field
public int getTableRows(String table); //to query indicate table total rows
public boolean tableExists(String table); //to query if table exists
public <T>T queryForBean(String sql, Class<T> cls); //query data for bean
public <T>T queryForBean(String sql, Class<T> cls, Object[] params); //query data for bean with parameters
public <T>List<T> queryForBeans(String sql, Class<T> cls); //query data for beans
public <T>List<T> queryForBeans(String sql, Class<T> cls, Object[] params); //query data for beans with parameters
}
2、接口实现类,实现对mysql访问,类里头有涉及自己再封装的一些通用api,如果想编译通过的,可以硬编码。基本上猜也能猜得出来是干嘛用的。这些类命名规则是CXXX,表示框架Core核心类。不是C++命名规则。
/**DBUtils Encapsulation for common operation and pool management
* the module encapsulate DBUtnils
* by fuzhou huilin corp.ltd @author lms 2016.07.07
* last update by @author lms 2016.07.08
*/
package hs.libra.core.db;
import hs.libra.core.object.CObject;
import hs.libra.core.tool.CJsonUtils;
import hs.libra.core.tool.CPropUtils;
import hs.libra.core.tool.CStrUtils;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class CDbUtilsDrv extends CObject implements IDbDriver{
//database parameters
private static String url = "jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf-8";
private static String driver = "com.mysql.jdbc.Driver";
private static String account = "root";
private static String password = "123456";
//pool manage parameters
private static ComboPooledDataSource poolDataSource;
private static boolean loaded = false;
private static boolean inited = false; //global parameter,only initialize one time
private static int initPoolSize = 10;
private static int minPoolSize = 5;
private static int maxPoolSize = 10;
private static int maxStatement = 100;
private static int maxIdleTime = 60;
//current instance manage
private Connection conn = null; //current connection object
private boolean connected = false; //current connection if established
/**to initialize pool data source
* ComboPooledDataSource only will be initialize one time even if you call it for many times
*/
private static void initParam(){
if (inited) return;
try{
poolDataSource = new ComboPooledDataSource();
poolDataSource.setUser(account);
poolDataSource.setPassword(password);
poolDataSource.setJdbcUrl(url);
poolDataSource.setDriverClass(driver);
poolDataSource.setInitialPoolSize(initPoolSize);
poolDataSource.setMinPoolSize(minPoolSize);
poolDataSource.setMaxPoolSize(maxPoolSize);
poolDataSource.setMaxStatements(maxStatement);
poolDataSource.setMaxIdleTime(maxIdleTime);
}catch(Exception e){
e.printStackTrace();
}
inited = true;
}
/**to load database configuration file and get database parameter
* this function only can be called one time
* @param configFile file of database configuration
*/
private static void loadParam(String configFile) {
if (loaded) return;
if (CStrUtils.isBlankStr(configFile))
configFile = "/config/db.properties";
CPropUtils cfg = new CPropUtils(configFile);
//load database parameters
url = cfg.getValue("url");
driver = cfg.getValue("driver");
account = cfg.getValue("account");
password = cfg.getValue("password");
//load pool parameters
initPoolSize = CStrUtils.strToIntDef(cfg.getValue("initPoolSize"),10);
minPoolSize = CStrUtils.strToIntDef(cfg.getValue("minPoolSize"),5);
maxPoolSize = CStrUtils.strToIntDef(cfg.getValue("maxPoolSize"),10);
maxStatement = CStrUtils.strToIntDef(cfg.getValue("maxStatement"),100);
maxIdleTime = CStrUtils.strToIntDef(cfg.getValue("maxIdleTime"),60);
loaded = true;
cfg = null;
}
public CDbUtilsDrv(String configFile){
loadParam(configFile);
initParam();
openConnection();
}
@Override
public void init(String configFile) {
loadParam(configFile);
initParam();
}
/**to override CObject.dispose function
*/
@Override
public void dispose(){
closeConnection();
}
/**to establish connection to database
* @return true if connected,false due to failure connection
*/
@Override
public boolean openConnection() {
if (connected) return true;
initParam();
try{
this.conn = poolDataSource.getConnection();
this.connected = true;
}catch(SQLException e){
e.printStackTrace();
}
return this.connected;
}
/**to close database connection,caller need to close database after using
*/
@Override
public void closeConnection() {
try{
if ((conn!=null) && (!conn.isClosed()))
DbUtils.closeQuietly(conn);
}
catch(SQLException e){
e.printStackTrace();
}
conn = null;
}
/**execute sql script for effect rows
* @param strSql the sql text to be executed
* @return =-1 means system error or >=0 means rows effected
*/
@Override
public int executeSql(String sql) {
int ret = -1;
QueryRunner qr = null;
try{
qr = new QueryRunner();
ret = qr.update(conn,sql);
}
catch(SQLException e){
setErrMsg(e.getMessage());
e.printStackTrace();
}
qr = null;
return ret;
}
/**execute sql with parameter
* @param strSql sql to be executed
* @param params parameters of sql
* @return =-1 means system error or >=0 means rows effected
*/
@Override
public int executeSql(String sql, Object[] params) {
int ret = -1;
QueryRunner qr = null;
try{
qr = new QueryRunner();
ret = qr.update(conn,sql,params);
}
catch(SQLException e){
setErrMsg(e.getMessage());
e.printStackTrace();
}
qr = null;
return ret;
}
/**to get auto increment field value after insert
* @param tbName table to query
* @param fieldName the table's field name
* @return -1 means system error or return the field max value
*/
@Override
public int getAutoIncId(String table, String field) {
int ret = -1;
QueryRunner qr = null;
try{
String strSql = String.format("select max(%s) from %s",field,table);
qr = new QueryRunner();
Long t = (Long)qr.query(conn,strSql,new ScalarHandler<Long>(1)); //to get column 1 value
ret = t.intValue();
}
catch(SQLException e){
setErrMsg(e.getMessage());
e.printStackTrace();
}
qr = null;
return ret;
}
/**to query table record count
* @param tbName table to query
* @return -1 means system error or return the table record count
*/
@Override
public int getTableRows(String table) {
int ret = -1;
QueryRunner qr = null;
try{
String strSql = String.format("select count(*) from %s",table);
qr = new QueryRunner();
Long t = (Long)qr.query(conn,strSql,new ScalarHandler<Long>(1)); //to get column 1 value
ret = t.intValue();
}
catch(SQLException e){
this.setErrMsg(e.getMessage());
e.printStackTrace();
}
qr = null;
return ret;
}
/**to judge table whether exists
* @param tableName name of the table to judge
* @return true if exists or return false
*/
@Override
public boolean tableExists(String table) {
boolean ret = false;
QueryRunner qr = null;
try{
String strSql = String.format("select count(*) from %s where 1<0",table);
qr = new QueryRunner();
Long t = (Long)qr.query(conn,strSql,new ScalarHandler<Long>(1)); //to get column 1 value
ret = t.intValue()>=0;
}
catch(SQLException e){
this.setErrMsg(e.getMessage());
e.printStackTrace();
}
qr = null;
return ret;
}
/**execute sql for first row data and convert to bean
* @param sql query sql script
* @param bh, such as User.class
* eg: User u=queryForBean("select * from user where id=10",User.Class);
* @return bean or null
*/
public <T>T queryForBean(String sql, Class<T> cls){
QueryRunner qr = null;
T ret = null;
try{
qr = new QueryRunner();
ret = qr.query(conn,sql,new BeanHandler<T>(cls));
}
catch(SQLException e){
this.setErrMsg(e.getMessage());
e.printStackTrace();
}
qr = null;
return ret;
}
/**execute sql for first row data and convert to bean with sql parameters
* @param sql query sql script
* @param bhd, such as User.class
* @param params parameters of sql
* eg: User u=queryForBean("select * from user where id=? and name=?",User.class,new Object[1,"kitty"]);
* @return bean or null
*/
public <T>T queryForBean(String sql, Class<T> cls, Object[] params){
QueryRunner qr = null;
T ret = null;
try{
qr = new QueryRunner();
ret = qr.query(conn,sql,new BeanHandler<T>(cls),params);
}
catch(SQLException e){
this.setErrMsg(e.getMessage());
e.printStackTrace();
}
qr = null;
return ret;
}
/**execute sql for java bean
* @param the sql for data
* @param cls bean class
* eg: List<User> us=queryForBeans("select * from user where id<10",User.class);
* @return bean object list if success, otherwise return null
*/
@Override
public <T>List<T> queryForBeans(String sql, Class<T> cls){
QueryRunner qr = null;
List<T> ret = null;
try{
qr = new QueryRunner();
ret = qr.query(conn,sql,new BeanListHandler<T>(cls));
}
catch(SQLException e){
this.setErrMsg(e.getMessage());
e.printStackTrace();
}
qr = null;
return ret;
}
/**execute sql for java bean with sql parameters
* @param sql the sql text
* @param cls bean class,such as new User.class
* @param params sql parameters
* @return bean object list if success, otherwise return null
* eg: List<User> u=queryForBeans("select * from user where id=? and name=?",User.class,new Object[]{1,"test"});
*/
public <T>List<T> queryForBeans(String sql, Class<T> cls, Object[] params){
QueryRunner qr = null;
List<T> ret = null;
try{
qr = new QueryRunner();
ret = qr.query(conn, sql, new BeanListHandler<T>(cls), params);
}
catch(SQLException e){
this.setErrMsg(e.getMessage());
e.printStackTrace();
}
qr = null;
return ret;
}
// for test case //
public static void main(String[] args){
for (int i=0;i<20;i++){
CDbUtilsDrv drv = new CDbUtilsDrv("/config/db.properties");
TestBean b = drv.queryForBean("select * from domain_doc_record where id=?", TestBean.class,new Object[]{10});
System.out.println("query for bean:"+CJsonUtils.objToJson(b));
List<TestBean> bs = drv.queryForBeans("select * from domain_doc_record where id<10", TestBean.class);
System.out.println("query for list bean:"+CJsonUtils.objsToJson(bs));
drv.dispose();
drv = null;
}
System.out.println("finished");
}
}