通过原生的java代码封装的类,这样每次数据库操作都要创建这个数据库操作的类,效率较低。
demo:
DBO类
package test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.lang.StringUtils;
import org.hyena.frame.util.DateTimeUtil;
public class DBO {
protected Connection conn = null; //数据库连接对象
protected Statement stmt = null; //Statement
protected boolean isTrans = true; //默认为自动提交,即不进行事务处理
protected String DBName = ""; //要连接的数据库
public boolean isConnected = false; //数据库连接是否成功
public Connection getConn(){
return this.conn;
}
/**
* 设置手动提交事务
*/
public void setAutoTrans(){
this.isTrans = false;
}
/**
* Default constructor,no parameters
* 取数据库连接并生产Statement。
* @throws SQLException
*/
public void init(String db){
try{
this.DBName = db;
this.conn = KQDAO.getConnection(this.DBName);
this.isConnected = false;
while(!this.isConnected){
try {
if(this.conn.isClosed()) this.conn = KQDAO.getConnection(this.DBName);
Statement stmt = this.conn.createStatement();
stmt.executeQuery("select 0");
stmt.close();
this.isConnected = true;
}
catch (SQLException e) {
this.isConnected = false;
this.conn = KQDAO.getConnection(this.DBName);
}
}
}catch(Exception e){
this.conn = null;
this.isConnected = false;
System.err.print(DateTimeUtil.formatDateTime(java.util.Calendar.getInstance().getTime(),"yyyy-MM-dd HH:mm:ss"));
System.err.println(" YDX Err: DBO.init("+ db + "): "+ e.getMessage());
}
}
/**
* Default constructor, parameter bl control if the connection auto commit trans
* 取数据库连接并生产Statement。
* @throws SQLException
*/
public void init(String db,boolean bl){
try{
this.init(db);
this.isTrans = bl;
this.conn.setAutoCommit(this.isTrans);
this.isConnected = true;
}catch(Exception e){
this.conn = null ;
this.isConnected = false;
System.err.print(DateTimeUtil.formatDateTime(java.util.Calendar.getInstance().getTime(),"yyyy-MM-dd HH:mm:ss"));
System.err.println(" YDX Err: DBO.init(): "+ db + " ------------ " + e.getMessage());
}
}
/**
* 执行查询。
* 该方法调用Statement的executeQuery(sql)方法并返回ResultSet结果集。
* @param sql SQL语句
* @return ResultSet结果集
*/
public ResultSet executeQuery(String sql) throws SQLException {
ResultSet rs = null;
try{
//Statement stmt = this.conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,java.sql.ResultSet.CONCUR_READ_ONLY);
PreparedStatement pstmt=this.conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
if(pstmt != null){
//rs = stmt.executeQuery(sql);
pstmt.executeQuery();
rs = pstmt.getResultSet();
}
}catch(SQLException e){
System.out.println(DateTimeUtil.formatDateTime(java.util.Calendar.getInstance().getTime(),"yyyy-MM-dd HH:mm:ss") +" YDX Err: DBO.executeQuery(): "+ e.getMessage());
System.out.println("YDX Err SQL: "+ DBName + " sql:" + sql);
}
return rs;
}
/**
* 执行更新。
* @param sql SQL语句
* @return 修改的行数
*/
public int executeUpdate(String sql) throws SQLException {
int iRtn=-1;
try{
Statement stmt = this.conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,java.sql.ResultSet.CONCUR_READ_ONLY);
if(stmt != null){
//System.out.println(sql);
iRtn = stmt.executeUpdate(sql);
stmt.close();
}
}catch(Exception e){
System.out.println(DateTimeUtil.formatDateTime(java.util.Calendar.getInstance().getTime(),"yyyy-MM-dd HH:mm:ss") +" YDX Err: DBO.executeUpdate(): "+ e.getMessage());
System.out.println("YDX Err SQL: "+ DBName + " sql:" + sql);
}
return iRtn;
}
/**
* 写操作记录。
* @return 修改的行数
* 语法 executeLog(EmpNo,ModuleName,Operation,Result,Remark,IP)
*/
public int executeLog(String EmpNo,String ModuleName,String Operation,int Result,String Remark,String IP) throws SQLException {
int iRtn=-1;
String sql = "" ;//写记录的逻辑
try{
Statement stmt = this.conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,java.sql.ResultSet.CONCUR_READ_ONLY);
if(stmt != null){
iRtn = stmt.executeUpdate(sql);
stmt.close();
}
}catch(Exception e){
System.out.println(DateTimeUtil.formatDateTime(java.util.Calendar.getInstance().getTime(),"yyyy-MM-dd HH:mm:ss") +" YDX Err: DBO.executeLog(): "+ e.getMessage());
System.out.println("YDX Err SQL: "+ DBName + " sql:" + sql);
}
return iRtn;
}
//提交事务
public void commit(){
try{
if (! isTrans) {
this.conn.commit(); //提交事务
isTrans = true; //还原为默认值
}
}catch(Exception e){
System.out.println(DateTimeUtil.formatDateTime(java.util.Calendar.getInstance().getTime(),"yyyy-MM-dd HH:mm:ss") +" YDX Err: DBO.commit(): "+ DBName + " ---- " + e.getMessage());
}
}
//回滚事务
public void rollback(){
try{
if (! isTrans) {
this.conn.rollback();
}
}catch(Exception e){
System.out.println(DateTimeUtil.formatDateTime(java.util.Calendar.getInstance().getTime(),"yyyy-MM-dd HH:mm:ss") +" YDX Err: DBO.rollback(): "+ e.getMessage());
}
}
/**
* 关闭数据库操作,释放数据库连接。
*/
public void close(){
try{
if(this.conn != null) this.conn.close();
this.conn = null;
}catch(SQLException e){
System.out.println(DateTimeUtil.formatDateTime(java.util.Calendar.getInstance().getTime(),"yyyy-MM-dd HH:mm:ss") +" YDX Err: DBO.close().finally{}: "+ e.getMessage());
}
}
private void close(Statement stmt, ResultSet rs) {
try {
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
//关闭资源
private void closeConnection(Connection conn) {
if (conn != null) {
try {
conn.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
DAO类:
package test;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import org.hyena.frame.util.*;
public class DAO {
public static Connection getConnection(String DBName) throws SQLException {
DataSource ds = null;
try{
Context ctx = new InitialContext();
ds = (DataSource)ctx.lookup("java:comp/env/jdbc/" + DBName);
}
catch(Exception e) {
System.out.print(DateTimeUtil.formatDateTime(java.util.Calendar.getInstance().getTime(),"yyyy-MM-dd HH:mm:ss") +" Err: DAO.getConnection(): Can not find KQ JNDI source!!! " + KQDBName);
}
return ds.getConnection();
}
}
使用例子如下:
String DBNAME = "myDbName";
DBO kQDBO = new DBO();
kQDBO .init(DBNAME,false);//不自动提交事务
if (kQDBO.isConnected) {
try{
String sql = "update table set name = 'new' where id =1";
kQDBO.executeUpdate(sql);//执行语句
kQDBO.executeLog();//记录操作记录
kQDBO.commit();//提交事务
}catch (Exception e) {
KQDBO.rollback();//事物回滚
}finally{
kQDBO.close();
}
}