数据库连接操作java代码

package com.digitalchina.sitech.anhuibss.utils;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.*;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.*;
import java.sql.*;

import com.digitalchina.sitech.anhuibss.utils.JNDINames;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import com.digitalchina.sitech.anhuibss.exception.*;
import sun.jdbc.rowset.CachedRowSet;

/**
* <p>Title: </p>
* <p>Description: </p>
* <p>Copyright:
* 2007.12</p>
* <p>Company:</p>
* @author czk
* @version 1.0
*/

public class DBAction {
/***
* 使用调用：
* 一、
* 1、 private DBAction dbaction;
* 2、 dbaction = new DBAction();
* 3、 dbaction.setDBStmt(queryStr);
* 二、
* dbreturnvector = new Vector();
* dbreturnvector = dbaction.doSelect();
* */
protected static Log log = LogFactory.getLog(DBAction.class);

public String stmtstring = null;

private String msg = "";

private ResultSet rs = null;

private int columncount;

private int status = 0;

private Vector v = new Vector();

private Statement stmt = null;

private Connection dbConnection = null;

private DataSource datasource = null;

private java.sql.PreparedStatement pstmt = null;

public DBAction() throws ApplicationDAOException {


//部署时JNDI开启 （hyx）
/*
try {

InitialContext ic = new InitialContext();
datasource = (DataSource) ic.lookup(JNDINames.WEBBI_DATASOURCE);
}
catch (NamingException ne) {
throw new ApplicationDAOException("Naming Exception while looking "
+ " up DataSource Connection "
+ JNDINames.WEBBI_DATASOURCE + ": \n"
+ ne.getMessage());
}
*/

}

public DBAction(String dataSrc) throws ApplicationDAOException {
try {
InitialContext ic = new InitialContext();
if (dataSrc == null) {
dataSrc = JNDINames.WEBBI_DATASOURCE;
}
datasource = (DataSource) ic.lookup(dataSrc);
} catch (NamingException ne) {
throw new ApplicationDAOException("Naming Exception while looking "
+ "up DataSource Connection " + dataSrc + ": \n"
+ ne.getMessage());
}
}

/**
* Return the dbstmt.
*/
public String getDBStmt() {
return (this.stmtstring);
}

/**
* Set the dbstmt.
*
* @param dbstmt The new DB statement to execute
*/
public void setDBStmt(String stmtstring) {
this.stmtstring = stmtstring;
}

/**
* Return the db message.
*/
public String getDBString() {
return this.msg;
}

public String getSysDate() {
java.text.SimpleDateFormat formatter;
formatter = new java.text.SimpleDateFormat("yyyyMMdd");
String regdate;
regdate = formatter.format(new java.util.Date());
return regdate;
}

/**
* Return the dbreturn int.
*/
public int getDBInt() {
return this.status;
}

/**************************************************************
* 函数过程名：doSelectRs
* 名 称：得到结果集
* 创 建 者:czk
* 创建时间:2007.12-08-13
* 修 改 者：czk * 修改时间：2006.12-08-13
* ver :1.0
* 作用/用途：
* 得到结果集
* 注意事项：
* 在哪里使用这个方法，必须在哪里关闭（hyx）
* 例子：
*
finally {
try {
if (rs != null) {
rs.close();
}
dbaction.closeConnection();//注意事项的地方
}
catch (Exception e) {
log.error(e.getMessage(), e);
}
}

**************************************************************/
public ResultSet doSelectRs() throws ApplicationDAOException {
ResultSet result;
try {
getDBConnection();
stmt = null;
stmt = dbConnection.createStatement();
if (stmt == null) {
throw new ApplicationDAOException(
"DAO doSelectRs(e) stmt==null");
}

result = stmt.executeQuery(stmtstring);
return result;
} catch (Exception se) {

throw new ApplicationDAOException(
"SQLException CachedRowSet doSelectRsSet() "
+ " database table\n" + stmtstring + " :\n"
+ se.toString());
}

}

/**
* Handle database update
*/
public int doUpdate() throws ApplicationDAOException {
this.msg = "";
this.status = 0;

try {
if (dbConnection == null || dbConnection.isClosed()) {
getDBConnection();
}
stmt = null;
stmt = dbConnection.createStatement();
if (stmt == null) {
throw new ApplicationDAOException("DAO doUpdate() stmt==null");
}

status = stmt.executeUpdate(stmtstring);

} catch (SQLException ae) {
this.msg = ae.getMessage();
throw new ApplicationDAOException("SQLException while inserting "
+ "database table \n" + stmtstring + " :\n" + ae);
} finally {
closeConnection();
}
return this.status;
}

public String[][] doSelectRs(String sql) throws ApplicationDAOException {
ResultSet result;
String[][] data;
try {
getDBConnection();
stmt = null;
stmt = dbConnection.createStatement();
if (stmt == null) {
throw new ApplicationDAOException(
"DAO doSelectRs(e) stmt==null");
}

result = stmt.executeQuery(sql);
data = getStringArrayFromRS(result);
return data;
} catch (Exception se) {

throw new ApplicationDAOException(
"SQLException doSelectRs() "
+ " database table\n" + stmtstring + " :\n"
+ se.toString());
} finally {
closeConnection();
}
}

public ResultSet doPrepageSelectSql() throws ApplicationDAOException {
ResultSet result;
try {
getDBConnection();
stmt = null;
stmt = dbConnection.prepareStatement(stmtstring);
if (stmt == null) {
throw new ApplicationDAOException(
"DAO doSelectRs(e) stmt==null");
}

result = stmt.executeQuery(stmtstring);
return result;
} catch (Exception se) {

throw new ApplicationDAOException(
"SQLException CachedRowSet doSelectRsSet() "
+ " database table\n" + stmtstring + " :\n"
+ se.toString());
} finally {
closeConnection();
}

}

public String[][] getStringArrayFromRS(ResultSet rs) {
String[][] data = null;
try {

ResultSetMetaData rsmeta = rs.getMetaData();
int dataCols = rsmeta.getColumnCount();
Vector rows = new Vector();
int dataRows = 0;
while (rs.next()) {
String[] row = new String[dataCols];
for (int i = 0; i < dataCols; i++) {
String s = rs.getString(i + 1) == null ? "" : rs.getString(
i + 1).trim();
row[i] = s;
}
rows.addElement(row);
dataRows++;
}
if (dataRows > 0) {
data = new String[dataRows][dataCols];
for (int i = 0; i < dataRows; i++) {
data[i] = (String[]) rows.elementAt(i);
}
}

} catch (Exception e) {

}
return data;
}

/**
* Method for get connection with database
*/
public Connection getDBConnection() throws ApplicationDAOException {

try {

DriverManager.registerDriver(new com.ibm.db2.jcc.DB2Driver());
String url = "jdbc:db2://130.30.6.34:50000/bssdb";
String userName = "papp";
String password = "papp";
dbConnection = DriverManager.getConnection(url, userName, password);

//dbConnection = datasource.getConnection();
} catch (SQLException se) {
throw new ApplicationDAOException("SQL Exception while getting "
+ "DB connection : \n" + se);
}

return dbConnection;
}

public String getEquiteID(String strEquipType)
throws ApplicationDAOException {
// sbdz-20040813-000001
String retStrID = "";
// strSeq_num默认为1 第一单
String strSeq_num = "1";
int intSeqNum = 0;

ResultSet rs = null;
try {
/*
* 先要判断是否存在该类型的记录，如果不存在增加一条记录． ＢＹ ＪＯＨＮＹ ２００６．０７．１７
*/
String stmtString = "";
String queryStr = "select seq_num from t_common_sequence_key "
+ " where seq_type = '" + strEquipType + "'";
setDBStmt(queryStr);
rs = doSelectRs();
if (!rs.next()) {
queryStr = "insert into t_common_sequence_key(seq_type,seq_name,seq_date,seq_num)"
+ "values ('"
+ strEquipType
+ "','未定义类型','"
+ getSysDate() + "',0)";
setDBStmt(queryStr);
doUpdate();
}
queryStr = "select seq_num from t_common_sequence_key "
+ " where seq_type = '" + strEquipType + "'"
+ " and seq_date='" + getSysDate() + "'";
setDBStmt(queryStr);
rs = doSelectRs();
if (rs.next()) {

// 最大流水号 加1
intSeqNum = (rs.getInt(1) + 1);
stmtString = "update t_common_sequence_key "
+ " set seq_num=seq_num+1 " + " where seq_type = '"
+ strEquipType + "'" + " and seq_date='" + getSysDate()
+ "'";
// 更新操作
setDBStmt(stmtString);
doUpdate();
strSeq_num = String.valueOf(intSeqNum);

} else {

stmtString = "update t_common_sequence_key "
+ " set seq_num=1 ,seq_date='" + getSysDate() + "'"
+ " where seq_type = '" + strEquipType + "'";
setDBStmt(stmtString);
doUpdate();
} // else
// 没有7位数 前补0
while (strSeq_num.length() < 7) {
strSeq_num = "0" + strSeq_num;
}
retStrID = strEquipType + "-" + getSysDate() + "-" + strSeq_num;
} catch (Exception se) {
log.error(se.getMessage(), se);

} finally {
try {
if (rs != null) {
rs.close();
}
closeConnection();
} catch (Exception e) {
log.error(e.getMessage(), e);
}
}

return retStrID;
}

/**
* Close database conection.
*/
public void closeConnection() throws ApplicationDAOException {
try {
if (dbConnection != null && !dbConnection.isClosed()) {
if (stmt != null) {
stmt.close();
}
if (rs != null) {
rs.close();
}
if (pstmt != null) {
pstmt.close();
}
dbConnection.close();
}
} catch (SQLException se) {
throw new ApplicationDAOException("SQL Exception while closing "
+ "DB connection : \n" + se);
}
}

/**************************************************************
* <p>名称:destroy() <br>
* 创建者:
* 创建时间:2006.12 <br>
* 修 改 者：<br>
* 修改时间：2006.12 <br>
* 处理流程: <br>
****************************************************************/

public void destroy() {
//Clean up resources
}

/**
* get dbConnection
* @throws ApplicationDAOException
*/
public Connection getConnection() throws ApplicationDAOException {
try {
if (dbConnection == null || dbConnection.isClosed()) {
getDBConnection();
}
return dbConnection;
} catch (SQLException se) {
throw new ApplicationDAOException(
"SQL Exception while getConnection() : \n " + se);
}

}

public static void main(String args[]) {
try {
DBAction dbaction = new DBAction();
dbaction.stmtstring = "select * from papp.t_sys_web_user";
ResultSet rs = dbaction.doSelectRs();
while (rs.next()) {
String tmp = rs.getString(1);
System.out.println(tmp);
}

dbaction.closeConnection();
} catch (Exception e) {
e.printStackTrace();
}

}

}