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; / 数据库类型private String dbmsDriver; / 数据库驱动private String host; / 主机private String port; / 端口private String db; / 数据库名private String user; / 用户名private String password; / 密码private Class beanClass = null;private Connection conn = null;private final Log logger = LogFactory.getLog(DbHelper.class);/* 根据数据库类型设置驱动程序* param dbms 数据库类型，如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;/* 根据数据库类型设置端口* param dbms 数据库类型*/private void fillDbmsPort(String dbms) if (dbms.equalsIgnoreCase(postgresql) port = 5432; else if (dbms.equalsIgnoreCase(mysql) port = 3306;/* 设置数据连接时需要的参数* param dbms 数据库类型* param host 主机* param db 数据库名* 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;/* 设置数据连接时需要的参数* param dbms 数据库类型* param host 主机* param port 端口* param db 数据库名* 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;/* 加载驱动*/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(); /* 关闭Connection*/public void close() try DbUtils.close(conn); conn = null; catch (SQLException e) logger.error(Database close failed!); logger.error(e.getMessage(); /* 根据无参sql语句进行查询，并返回一个对象，用于单条记录的查询* 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;/* 根据有参sql语句进行查询，并返回一个对象，用于单条记录的查询* 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;/* 根据无参sql语句进行查询，并返回一个数据集，用于多条记录的查询* 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;/* 根据有参sql语句进行查询，并返回一个数据集，用于多条记录的查询* 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;/* 根据无参sql语句进行数据更新，并返回更新后影响的记录数* 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;/* 根据有参sql语句进行数据更新，并返回更新后影响的记录数* 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;/* 设置存放查询结果的Bean类，每个Bean类对象对应一条查询的结果记录* param beanClass，如User.class*/public void setBeanClass(Class beanClass) this.beanClass = beanClass;/* 设置使用外部的Connection对象，如来自数据库连接池的连接* 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是从数据库查询出来的记录对象 User u = (User) dh.query(SELECT id,name,age,address FROM t_user WHERE id=9); /u2是从数据库查询出来的记录对象 User u2 = (User) dh.query(SELECT id,name,age,address FROM t_user WHERE id=?, n); / users和user2存放着从数据库查询