package com.drug.db;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
/**
* 自己写的一个数据库工具类,希望可以封装所有的数据库操作
* 直接对封装好的数据库对象javaBean进行操作
*
* 数据库中表中的字段名必须首字母大写
* 封装好的数据对象类javaBean中的变量必须和数据库表中的字段相对应,(首字母不用大写)
*
* 这样以后修改数据库,只需要对应的修改一下数据对象类javaBean就行了,呵呵
*
* 本人java还在自学中,望大家多多指正
*/
public class DBUtil {
/**
* 方 法 名: getTableRowCount
* 功能描述: 获取表的行数
* 输入参数: tableName:表名
* 返 回 值: int
* 编 码 人: zmj
* 编码时间: 2010-2-8 上午11:10:26
*/
public int getTableRowCount(String tableName) {
Connection con = null;
PreparedStatement pst = null;
ResultSet rs = null;
int rowCount = -1;
try {
String sql = "select * from " + tableName;
con = getCon();
pst = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
rs = pst.executeQuery();
rs.last();
rowCount = rs.getRow();
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
close(con, pst, rs);
}
return rowCount;
}
/**
* 方 法 名:hasElem
* 功能描述:判断表中是否含有该元素
* 输入参数: tableName:表名,keyName:查询的键值,key用来查询的键
* 返 回 值: boolean
* 编 码 人: zmj
* 编码时间: 2010-2-20 上午10:26:33
*/
public boolean hasElem(String tableName, String keyName, Object key) {
Connection con = null;
PreparedStatement pst = null;
ResultSet rs = null;
String sql = "select * from " + tableName + " where "
+ firstUpper(keyName) + "=?";
try {
con = getCon();
pst = con.prepareStatement(sql);
setObject(pst, 1, key);
rs = pst.executeQuery();
if (rs.next()) {
return true;
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(con, pst, rs);
}
return false;
}
/**
* 方 法 名:getTableElem
* 功能描述: 获取数据库中一个表中的全部数据
* 输入参数:tableName:表名,beanName:用来封闭的javaBean
* 返 回 值: ArrayList<Object>
* 编 码 人: zmj
* 编码时间: 2010-2-20 上午08:16:52
*/
public ArrayList<Object> getTableElem(String tableName, String beanName) {
return getElemList(tableName, beanName, 1, -1);
}
/***
* 方 法 名:getTable
* 功能描述:获取数据库中一个表中的数据
* 输入参数:tableName:表名,beanName:用来封装的javaBean,offset:偏移量,count:取出的记录条数,为-1时表示全部
* 返 回值:ArrayList<Object>
* 编 码 人: zmj
* 编码时间: 2010-2-8
* **/
public ArrayList<Object> getElemList(String tableName, String beanName,
int offset, int count) {
Connection con = null;
PreparedStatement pst = null;
ResultSet rs = null;
ArrayList<Object> rs_list = new ArrayList<Object>();
try {
String sql = "select * from " + tableName;
con = getCon();
pst = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
rs = pst.executeQuery();
String[] paramName = tableColName(rs);
Class<?> beanClass = Class.forName(beanName);
if (offset > 1)
rs.absolute(offset - 1);
else if (offset < 0 && offset != -1)
return rs_list;
while (rs.next() && (count == -1 || count-- > 0)) {
Object bean = beanClass.newInstance();
saveBean(bean, rs, paramName);
rs_list.add(bean);
}
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} finally {
close(con, pst, rs);
}
return rs_list;
}
/**
* 方 法 名:fuzzySearch
* 功能描述: 进行模糊查询
* 输入参数: tableName:表名,beanName:用来封装的javaBean,
keyName:查询的键值,key用来查询的键,offset:偏移量,count:返回的最大个数,-1表示全部
* 返 回 值: ArrayList<Object>
* 编 码 人: zmj
* 编码时间: 2010-2-8 下午01:44:23
*/
public ArrayList<Object> fuzzySearch(String tableName, String beanName,
String fuzzyKeyName, String fuzzyKey, Object[][] key, int offset,
int count) {
Connection con = null;
PreparedStatement pst = null;
ResultSet rs = null;
ArrayList<Object> rs_list = new ArrayList<Object>();
try {
String sql = "select * from " + tableName + " where "
+ firstUpper(fuzzyKeyName) + " like ?";
for (int i = 0; i < key.length; i++) {
if (key[i].length == 2) {
sql += " and " + firstUpper((String) key[i][0]) + "=?";
}
}
con = getCon();
pst = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
pst.setObject(1, "%" + fuzzyKey + "%");
for (int i = 0; i < key.length; i++) {
if (key[i].length == 2) {
this.setObject(pst, i + 2, key[i][1]);
}
}
rs = pst.executeQuery();
String[] paramName = tableColName(rs);
Class<?> beanClass = Class.forName(beanName);
if (offset > 1)
rs.absolute(offset - 1);
else if (offset < 0 && offset != -1)
return rs_list;
while (rs.next() && (count == -1 || count-- > 0)) {
Object bean = beanClass.newInstance();
saveBean(bean, rs, paramName);
rs_list.add(bean);
}
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} finally {
close(con, pst, rs);
}
return rs_list;
}
/***
* 方 法 名:getElem
* 功能描述:获取数据库中一个表中的一条记录
* 输入参数:tableName:表名,beanName:javaBean的名字
* 返 回 值:Object
* 编 码 人: zmj
* 编码时间: 2010-2-8
* @throws SQLException
* **/
public Object getElem(String tableName, String keyName, Object key,
String beanName) {
Connection con = null;
PreparedStatement pst = null;
ResultSet rs = null;
String sql = "select * from " + tableName + " where "
+ firstUpper(keyName) + "=?";
String[] paramName = null;
try {
con = getCon();
pst = con.prepareStatement(sql);
setObject(pst, 1, key);
rs = pst.executeQuery();
paramName = tableColName(rs);
Class<?> beanClass;
beanClass = Class.forName(beanName);
Object bean = null;
bean = beanClass.newInstance();
if (rs.next()) {
saveBean(bean, rs, paramName);
}
return bean;
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} finally {
close(con, pst, rs);
}
return null;
}
/***
* 方 法 名:insert
* 功能描述:将JavaBean添加到数据库表table中
* 输入参数: tableName:表名,bean:javaBean
* 返 回 值:int
* 编 码 人: zmj
* 编码时间: 2010-2-8
* **/
public int insert(String tableName, Object bean) {
Connection con = null;
PreparedStatement pst = null;
StringBuilder sql = new StringBuilder("insert into " + tableName + "(");
try {
Object[][] beanObj = bean2Array(bean);
int n = beanObj.length;
if (n < 1) {
return 0;
}
for (int i = 0; i < n; i++) {
if (i != 0)
sql.append(",");
sql.append(beanObj[i][0]);
}
sql.append(") values(");
for (int i = 0; i < n; i++) {
if (i != 0)
sql.append(",");
sql.append("?");
}
sql.append(")");
con = getCon();
pst = con.prepareStatement(sql.toString());
for (int i = 0; i < n; i++) {
setObject(pst, i + 1, beanObj[i][1]);
}
return pst.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} finally {
close(con, pst);
}
return 0;
}
/**
* 方 法 名:updateElem
* 功能描述: 更新表中的一个元素
* 输入参数: tableName,keyName,key,bean
* 返 回 值: int
* 编 码 人: zmj
* 编码时间: 2010-2-8 上午11:30:18
*/
public int updateElem(String tableName, String keyName, Object key,
Object bean) {
Connection con = null;
PreparedStatement pst = null;
StringBuilder sql = new StringBuilder("update " + tableName + " set ");
try {
Object[][] beanObj = bean2Array(bean);
int n = beanObj.length;
if (n < 1) {
return 0;
}
for (int i = 0; i < n; i++) {
if (i != 0)
sql.append(",");
sql.append(beanObj[i][0] + "=?");
}
sql.append(" where " + firstUpper(keyName) + "=?");
con = getCon();
pst = con.prepareStatement(sql.toString());
int i = 0;
for (; i < n; i++) {
setObject(pst, i + 1, beanObj[i][1]);
}
setObject(pst, i + 1, key);
return pst.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} finally {
close(con, pst);
}
return 0;
}
/**
* 方 法 名: deleteElem
* 功能描述: 删除表中的一条记录
* 输入参数: tableName:表名, keyName:键名 key:键值
* 返 回 值: int
* 编 码 人: zmj
* 编码时间: 2010-2-8 下午01:47:08
*/
public int deleteElem(String tableName, String keyName, Object key) {
Connection con = null;
PreparedStatement pst = null;
StringBuilder sql = new StringBuilder("delete from " + tableName);
try {
sql.append(" where " + firstUpper(keyName) + "=?");
con = getCon();
pst = con.prepareStatement(sql.toString());
setObject(pst, 1, key);
int count = pst.executeUpdate();
return count;
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} finally {
close(con, pst);
}
return 0;
}
/**
* 方 法 名:setObject
* 功能描述:私有方法,设置preparedStatement的setXXX项
* 输入参数: pst:PreparedStatement对象,i:列的位置,obj:要插入的对象
* 返 回 值: void
* 编 码 人: zmj
* 编码时间: 2010-2-8 上午11:13:02
*/
private void setObject(PreparedStatement pst, int i, Object obj)
throws SQLException {
if (obj == null) {
pst.setString(i, null);
} else if (obj.getClass() == java.util.Date.class) {
long l = ((java.util.Date) obj).getTime();
java.sql.Timestamp t = new java.sql.Timestamp(l);
pst.setTimestamp(i, t);
} else {
pst.setObject(i, obj);
}
}
// 私有方法,把javabean存放到Object二维数组中
private Object[][] bean2Array(Object bean) {
Class<? extends Object> beanClass = bean.getClass();
Field[] field = beanClass.getDeclaredFields();
Method[] method = beanClass.getDeclaredMethods();
ArrayList<Object> list = new ArrayList<Object>();
for (int i = 0; i < field.length; i++) {
for (int j = 0; j < method.length; j++) {
String methodName = method[j].getName();
if (isGetMethod(methodName)
&& methodName.contains(firstUpper(field[i].getName()))) {
Object o = null;
try {
o = method[j].invoke(bean);
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
if (o != null) {
list.add(field[i].getName());
list.add(o);
}
}
}
}
int m = list.size();
if (m < 1 || m % 2 != 0) {
return new Object[0][0];
}
Object[][] bean_obj = new Object[m / 2][2];
Iterator<Object> iter = list.iterator();
int i = 0;
while (iter.hasNext()) {
bean_obj[i][0] = iter.next();
bean_obj[i][1] = iter.next();
i++;
}
return bean_obj;
}
// 私有方法,返回表中列名
private String[] tableColName(ResultSet rs) throws SQLException {
ResultSetMetaData rsmd = rs.getMetaData();
int n = rsmd.getColumnCount();
String[] name = new String[n];
for (int i = 0; i < n; i++) {
name[i] = rsmd.getColumnName(i + 1);
}
return name;
}
// 私有方法,保存ResultSet一行记录到一个javaBean中
private void saveBean(Object bean, ResultSet rs, String[] paramName)
throws SQLException {
Method[] method = bean.getClass().getDeclaredMethods();
for (int i = 0; i < paramName.length; i++) {
for (int j = 0; j < method.length; j++) {
String methodName = method[j].getName();
if (methodName.endsWith(paramName[i])
&& isSetMethod(methodName)) {
Object param = rs.getObject(paramName[i]);
if (param != null) {
// 如果为Timestamp类,则转换为Date类
if (param.getClass() == java.sql.Timestamp.class) {
long l = ((java.sql.Timestamp) param).getTime();
param = new java.util.Date(l);
}
try {
method[j].invoke(bean, param);
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
}
}
}
}
// 私有方法,判断是否为getter方法
private boolean isGetMethod(String methodName) {
if (methodName == null)
return false;
return methodName.startsWith("is") || methodName.startsWith("get");
}
// 私有方法,判断是否为setter方法
private boolean isSetMethod(String methodName) {
if (methodName == null)
return false;
return methodName.startsWith("set");
}
// 私有方法,将首字母大写
private String firstUpper(String s) {
return s.substring(0, 1).toUpperCase() + s.substring(1);
}
// 私有方法,获取数据库连接
private Connection getCon() throws ClassNotFoundException, SQLException {
DB.getInstance();
return DB.getConnection();
}
// 私有方法,关闭数据库操作1,用于查询数据库操作
private void close(Connection con, PreparedStatement pst, ResultSet rs) {
try {
if (rs != null)
rs.close();
if (pst != null)
pst.close();
if (con != null)
DB.release(con);
} catch (SQLException e) {
e.printStackTrace();
}
}
// 私有方法,关闭数据库操作2,用于更新数据库操作
//我用的数据库为access,不知道为什么,数据库Connection不关闭,数据库不更新,求解!!
private void close(Connection con, PreparedStatement pst) {
try {
if (pst != null)
pst.close();
if (con != null) {
con.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/*
* 自己写的一个很简单的数据库连接池
*/
package com.drug.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Stack;
public class DB {
private static DB DB_INSTANCE = null;
private static int MAX = 40;
private static Stack<Connection> CON_POOL = new Stack<Connection>();
// 数据库配置
private static String className = "sun.jdbc.odbc.JdbcOdbcDriver";
private static String url = "jdbc:odbc:test";
// 同步对象
private final static byte[] lock = new byte[0];
private DB() throws ClassNotFoundException {
Class.forName(className);
}
// 返回单一实例
public static DB getInstance() throws ClassNotFoundException {
if (DB_INSTANCE == null) {
DB_INSTANCE = new DB();
}
return DB_INSTANCE;
}
// 获取连接
public static Connection getConnection() throws SQLException {
synchronized (lock) {
if (CON_POOL.isEmpty()) {
return DriverManager.getConnection(url);
}
return CON_POOL.pop();
}
}
// 释放连接
public static void release(Connection con) throws SQLException {
synchronized (lock) {
if (con == null || con.isClosed()) {
return;
} else if (CON_POOL.size() < MAX + 1) {
CON_POOL.push(con);
} else {
con.close();
}
}
}
}