Java中MySql的DBHelper類.doc

package hzvtc.util;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.util.ArrayList;import mons.dbutils.DbUtils;import mons.dbutils.QueryRunner;import mons.dbutils.ResultSetHandler;import mons.dbutils.handlers.BeanHandler;import mons.dbutils.handlers.BeanListHandler;import mons.logging.Log;import mons.logging.LogFactory;public class DbHelper private String dbms; / 數(shù)據(jù)庫類型private String dbmsDriver; / 數(shù)據(jù)庫驅(qū)動(dòng)private String host; / 主機(jī)private String port; / 端口private String db; / 數(shù)據(jù)庫名private String user; / 用戶名private String password; / 密碼private Class beanClass = null;private Connection conn = null;private final Log logger = LogFactory.getLog(DbHelper.class);/* 根據(jù)數(shù)據(jù)庫類型設(shè)置驅(qū)動(dòng)程序* param dbms 數(shù)據(jù)庫類型，如mysql、postgresql等*/private void fillDbmsDriver(String dbms) if (dbms.equalsIgnoreCase(postgresql) dbmsDriver = org.postgresql.Driver; else if (dbms.equalsIgnoreCase(mysql) dbmsDriver = org.gjt.mm.mysql.Driver;/* 根據(jù)數(shù)據(jù)庫類型設(shè)置端口* param dbms 數(shù)據(jù)庫類型*/private void fillDbmsPort(String dbms) if (dbms.equalsIgnoreCase(postgresql) port = 5432; else if (dbms.equalsIgnoreCase(mysql) port = 3306;/* 設(shè)置數(shù)據(jù)連接時(shí)需要的參數(shù)* param dbms 數(shù)據(jù)庫類型* param host 主機(jī)* param db 數(shù)據(jù)庫名* param user 用戶名* param password 密碼*/public DbHelper(String dbms, String host, String db, String user, String password) this.dbms = dbms; fillDbmsDriver(dbms); this.host = host; fillDbmsPort(dbms); this.db = db; this.user = user; this.password = password;/* 設(shè)置數(shù)據(jù)連接時(shí)需要的參數(shù)* param dbms 數(shù)據(jù)庫類型* param host 主機(jī)* param port 端口* param db 數(shù)據(jù)庫名* param user 用戶* param password 密碼*/public DbHelper(String dbms, String host, String port, String db, String user, String password) this.dbms = dbms; fillDbmsDriver(dbms); this.host = host; this.port = port; this.db = db; this.user = user; this.password = password;/* 加載驅(qū)動(dòng)*/public void connect() if (conn != null) return; String strConn = String.format(jdbc:%s:/%s:%s/%s, dbms, host, port, db); DbUtils.loadDriver(dbmsDriver); try conn = DriverManager.getConnection(strConn, user, password); catch (SQLException e) logger.error(Database connection failed!); logger.error(e.getMessage(); /* 關(guān)閉Connection*/public void close() try DbUtils.close(conn); conn = null; catch (SQLException e) logger.error(Database close failed!); logger.error(e.getMessage(); /* 根據(jù)無參sql語句進(jìn)行查詢，并返回一個(gè)對象，用于單條記錄的查詢* param sql * return Object*/public Object query(String sql) if (conn = null | beanClass = null) return null; QueryRunner run = new QueryRunner(); ResultSetHandler h = new BeanHandler(beanClass); Object result = null; try result = run.query(conn, sql, h); catch (SQLException e) (SQLException in + sql + ); logger.error(e.getMessage(); return result;/* 根據(jù)有參sql語句進(jìn)行查詢，并返回一個(gè)對象，用于單條記錄的查詢* param sql * param args * return Object*/public Object query(String sql, Object. args) if (conn = null | beanClass = null) return null; QueryRunner run = new QueryRunner(); ResultSetHandler h = new BeanHandler(beanClass); Object result = null; try result = run.query(conn, sql, args, h); catch (SQLException e) (SQLException in + sql + ); logger.error(e.getMessage(); return result;/* 根據(jù)無參sql語句進(jìn)行查詢，并返回一個(gè)數(shù)據(jù)集，用于多條記錄的查詢* param sql* return ArrayList*/public ArrayList queryRecords(String sql) if (conn = null | beanClass = null) return null; QueryRunner run = new QueryRunner(); ResultSetHandler h = new BeanListHandler(beanClass); ArrayList result = null; try result = (ArrayList) run.query(conn, sql, h); / obtain the quaried records catch (SQLException e) (SQLException in + sql + ); logger.error(e.getMessage(); return result;/* 根據(jù)有參sql語句進(jìn)行查詢，并返回一個(gè)數(shù)據(jù)集，用于多條記錄的查詢* param sql* param args* return*/public ArrayList queryRecords(String sql, Object. args) if (conn = null | beanClass = null) return null; QueryRunner run = new QueryRunner(); ResultSetHandler h = new BeanListHandler(beanClass); ArrayList result = null; try result = (ArrayList) run.query(conn, sql, args, h); System.out.println(result.size(); / obtain the quaried records catch (SQLException e) (SQLException in + sql + ); logger.error(e.getMessage(); return result;/* 根據(jù)無參sql語句進(jìn)行數(shù)據(jù)更新，并返回更新后影響的記錄數(shù)* param sql* return int*/public int update(String sql) if (conn = null) return 0; QueryRunner run = new QueryRunner(); int result = 0; try result = run.update(conn, sql); catch (SQLException e) (SQLException in + sql + ); logger.error(e.getMessage(); return result;/* 根據(jù)有參sql語句進(jìn)行數(shù)據(jù)更新，并返回更新后影響的記錄數(shù)* param sql* param args* return int*/public int update(String sql, Object. args) if (conn = null) return -1; QueryRunner run = new QueryRunner(); int result = 0; try result = run.update(conn, sql, args); catch (SQLException e) (SQLException in + sql + ); logger.error(e.getMessage(); return result;/* 設(shè)置存放查詢結(jié)果的Bean類，每個(gè)Bean類對象對應(yīng)一條查詢的結(jié)果記錄* param beanClass，如User.class*/public void setBeanClass(Class beanClass) this.beanClass = beanClass;/* 設(shè)置使用外部的Connection對象，如來自數(shù)據(jù)庫連接池的連接* param conn Connection對象*/public void setConn(Connection conn) this.conn = conn;Overrideprotected void finalize() throws Throwable / TODO Auto-generated method stub close(); super.finalize();DbTestpackage hzvtc.util.test;import hzvtc.util.DbHelper;import java.util.List;public class DbTest /* param args*/public static void main(String args) / TODO Auto-generated method stub int n = 3; DbHelper dh = new DbHelper(mysql, localhost, test, root, 123456); dh.connect(); dh.setBeanClass(User.class); /u是從數(shù)據(jù)庫查詢出來的記錄對象 User u = (User) dh.query(SELECT id,name,age,address FROM t_user WHERE id=9); /u2是從數(shù)據(jù)庫查詢出來的記錄對象 User u2 = (User) dh.query(SELECT id,name,age,address FROM t_user WHERE id=?, n); / users和user2存放著從數(shù)據(jù)庫查詢