能将里面的方法完善吗？

Question

package org.cs.trade.db;

import java.sql.*;

/**

• 处理数据库连接的类

• */
  public class ConnDB {

  // 常量的设置
  private Connection conn=null;
  private String url="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=tradesystem";
  private String user="sa";
  private String password="sa";

  public Connection getConn() {
  try{
  Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
  conn=DriverManager.getConnection(url,user,password);
  }
  catch(Exception ex){
  ex.printStackTrace();
  }
  return conn;
  }

  public void closeConn(){
  try{
  conn.close();
  }
  catch(Exception ex){
  ex.printStackTrace();
  }
  }

  public static Connection getConnection() {
  // TODO Auto-generated method stub
  return null;
  }

  public static Statement getStatement(Connection conn2) {
  // TODO Auto-generated method stub
  return null;
  }

  public static ResultSet getResultSet(Statement stmt, String sql) {
  // TODO Auto-generated method stub
  return null;
  }

  public static void closeStatement(Statement stmt) {
  // TODO Auto-generated method stub

  }

  public static void closeResultSet(ResultSet rs) {
  // TODO Auto-generated method stub

  }

  public static void closeConnection(Connection conn2) {
  // TODO Auto-generated method stub

  }}

Answer 1

给你一个例子，适合jdbc的
package com.chobits.db.schema;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.chobits.common.PageUtil;
import com.chobits.db.base.DBAction;
import com.chobits.db.base.DBEnitiy;
import com.chobits.db.base.DBInfo;
import com.chobits.db.base.DBProvider;
import com.chobits.db.base.DBSequence;
import com.chobits.db.base.DBTable;
import com.chobits.db.base.DBView;
import com.informix.jdbc.UDTSQLData;

public class DBSchema implements DBAction{

private DBInfo info = null;
private Connection connection = null;
private PreparedStatement statement = null;
private ResultSet resultset = null;
private String schemaName = null;
private DBProvider dbProvider = null;
private Logger logger = Logger.getLogger(DBSchema.class);

public DBSchema() {
}

private void close(){
    this.schemaName = null;
    try{
        if(resultset!=null){
            resultset.close();
            resultset = null;
        }
    }catch(Exception e){
        resultset = null;
    }
    try{
        if(statement!=null){
            statement.close();
            statement = null;
        }
    }catch(Exception e){
        statement = null;
    }
    try{
        if(connection!=null){
            connection.close();
            connection = null;
        }
    }catch(Exception e){
        connection = null;
    }   
    logger.debug("释放数据库连接");
}

@Override
public void openTransaction() throws Exception{
    try{
        dbProvider = DBProvider.findProvider(this, info.getDriver());
        logger.debug("驱动driver: "+dbProvider.getAvalitedDriverName());
        Class.forName(dbProvider.getAvalitedDriverName());
        logger.debug("访问url: "+info.getUrl());
        logger.debug("访问user: "+info.getUser());
        logger.debug("访问password: "+info.getPassword());
        this.connection = DriverManager.getConnection(info.getUrl(), info.getUser(), info.getPassword());
        logger.debug("连接成功");
        this.connection.setAutoCommit(false);
        logger.debug("发送SQLTransaction事务信令true");
        this.schemaName = connection.getCatalog();
        logger.debug("数据库名字: "+this.schemaName);
    }catch(Exception e){
        logger.error("连接数据库失败", e);
        throw e;
    }       
}

@Override
public void commitTransaction() throws Exception{
    try {
        if(this.connection==null){
            throw new Exception("数据库连接不存在，或SQL事务没有打开");
        }
        this.connection.commit();
        logger.debug("提交SQLTransaction成功");
        this.close();
    } catch (Exception e) {
        throw e;
    }
}

@Override
public void rollbackTransaction() throws Exception{
    try {
        if(this.connection==null){
            throw new Exception("数据库连接不存在，或SQL事务没有打开");
        }
        this.connection.rollback();
        logger.debug("回滚SQLTransaction成功");
        this.close();
    } catch (Exception e) {
        throw e;
    }
}

@Override
public List<Map<String, String>> queryList(String sql, String[] params) throws Exception {
    List<Map<String, String>> list = new ArrayList<Map<String, String>>();
    String params_txt = "";
    try{
        if(this.connection==null){
            throw new Exception("数据库连接不存在，或SQL事务没有打开");
        }
        if(params!=null && params.length>0){
            for(int i=0;i<params.length;i++){
                params_txt += "["+(i+1)+"] "+params[i]+", ";
            }
        }
        logger.debug("SQL->"+sql+"\nparams->"+params_txt);
        statement = connection.prepareStatement(sql);
        if(params!=null && params.length>0){
            for(int i=0;i<params.length;i++){
                statement.setObject(i+1, params[i]);
            }
        }
        resultset = statement.executeQuery();
        ResultSetMetaData rsmd = statement.getMetaData();
        int columnCount = rsmd.getColumnCount();
        while(resultset.next()){
            Map<String, String> map = new HashMap<String, String>();
            for(int i=1;i<=columnCount;i++){
                String name = rsmd.getColumnName(i);
                String value = this.trackValue(resultset.getObject(name)).trim();
                map.put(name.toUpperCase(), value);
            }
            list.add(map);
        }   
        logger.debug("总计条数ListSize=->"+list.size());
        if(resultset != null){
            resultset.close();
        }
        if(statement != null){
            statement.close();
        }
    }catch(Exception e){
        String error = "SQL错误:"+e.getMessage()+"\n";
        error += "SQL->"+sql+"\n";
        error += "参数->"+params_txt; 
        throw new Exception(error);
    }       
    return list;
}

@Override
public List<Map<String, String>> queryList(String sql) throws Exception {
    return this.queryList(sql, null);
}

@Override
public Map<String, String> queryMap(String sql, String[] params) throws Exception {
    List<Map<String, String>> list = this.queryList(sql, params);
    Map<String, String> result = null;
    if(list.size()>0){
        result = list.get(0);
    }
    return result;
}

@Override
public Map<String, String> queryMap(String sql) throws Exception {
    return this.queryMap(sql, null);
}

@Override
public int excuteUpdate(String sql, String[] params) throws Exception {
    int count = 0;
    String params_txt = "";
    try{
        if(this.connection==null){
            throw new Exception("数据库连接不存在，或SQL事务没有打开");
        }           
        if(params!=null && params.length>0){
            for(int i=0;i<params.length;i++){
                params_txt += "["+(i+1)+"] "+params[i]+", ";
            }
        }
        logger.debug("SQL->"+sql+"\nparams->"+params_txt);
        statement = connection.prepareStatement(sql);
        if(params!=null && params.length>0){
            for(int i=0;i<params.length;i++){
                statement.setObject(i+1, params[i]);
            }
        }
        count = statement.executeUpdate();
        if(statement != null){
            statement.close();
        }           
    }catch(Exception e){
        String error = "SQL错误:"+e.getMessage()+"\n";
        error += "SQL->"+sql+"\n";
        error += "参数->"+params_txt; 
        throw new Exception(error);
    }
    return count;
}

@Override
public int excuteUpdate(String sql) throws Exception {
    return this.excuteUpdate(sql, null);
}

@Override
public int getIntegerBySQL(String sql, String tag, String[] params) throws Exception {
    Map<String,String> result = this.queryMap(sql, params);
    int value = 0;
    if(result!=null){
        value = Integer.parseInt(result.get(tag.toUpperCase())+"");
    }
    return value;
}

@Override
public int getIntegerBySQL(String sql, String tag) throws Exception {
    return this.getIntegerBySQL(sql, tag, null);
}

@Override
public long getSequenceNextValue(String sequence) throws Exception {
    long value = dbProvider.getSequenceNextValue(sequence);
    return value;
}

@Override
public int getCount(String table, String index, String solution, String[] params) throws Exception {
    if(index==null || index.isEmpty()){
        index = "*";
    }
    String sql = "select count("+index+") as TTT_NO from "+table+" where 1=1 ";
    if(solution!=null && !solution.isEmpty()){
        sql += " and "+solution;
    }
    Map<String, String> map = this.queryMap(sql, params);
    int value = Integer.parseInt(map.get("TTT_NO"));
    return value;
}

@Override
public int getCount(String table, String index, String solution) throws Exception {
    return getCount(table, index, solution, null);
}

@Override
public int getSum(String table, String index, String solution, String[] params) throws Exception {
    String sql = "select sum("+index+") as TTT_NO from "+table+" where 1=1 ";
    if(solution!=null && !solution.isEmpty()){
        sql += " and "+solution;
    }
    Map<String, String> map = this.queryMap(sql, params);
    int value = Integer.parseInt(map.get("TTT_NO"));
    return value;
}

@Override
public int getSum(String table, String index, String solution) throws Exception {
    return getSum(table, index, solution, null);
}

@Override
public int getAVG(String table, String index, String solution, String[] params) throws Exception {
    String sql = "select sum("+index+") as TTT_NO from "+table+" where 1=1 ";
    if(solution!=null && !solution.isEmpty()){
        sql += " and "+solution;
    }
    Map<String, String> map = this.queryMap(sql, params);
    int value = Integer.parseInt(map.get("TTT_NO"));
    return value;
}

@Override
public int getAVG(String table, String index, String solution) throws Exception {
    return getAVG(table, index, solution, null);
}

@Override
public List<Map<String, String>> queryListByTable(String table, String solution, String[] params) throws Exception{
    String sql = "select * from "+table+" where 1=1 ";
    if(solution!=null && !solution.isEmpty()){
        sql += " and "+solution;
    }
    return this.queryList(sql, params);
}

@Override
public List<Map<String, String>> queryListByTable(String table, String solution) throws Exception{
    return queryListByTable(table, solution, null);
}

@Override
public Map<String, String> queryMapByTable(String table, String solution, String[] params) throws Exception{
    String sql = "select * from "+table+" where 1=1 ";
    if(solution!=null && !solution.isEmpty()){
        sql += " and "+solution;
    }
    return queryMap(sql, params);
}

@Override
public Map<String, String> queryMapByTable(String table, String solution) throws Exception{
    return queryMapByTable(table, solution, null);
}

@Override
public int insertToTable(String table, String[] fields, String[] values, String[] params) throws Exception{
    if(fields==null || fields.length==0){
        throw new Exception("待插入的字段名称不能为空");
    }
    if(values==null || values.length==0){
        throw new Exception("待插入的字段值不能为空");
    }
    if(fields.length!=values.length){
        throw new Exception("待插入的字段个数"+fields.length+"与值个数"+values.length+"不一致");
    }
    String fieldStr = "";
    for(int i=0;i<fields.length;i++){
        if(i==fields.length-1){
            fieldStr += fields[i];
        }else{
            fieldStr += fields[i]+", ";
        }
    }       
    String valueStr = "";
    for(int i=0;i<values.length;i++){
        if(i==values.length-1){
            valueStr += values[i];
        }else{
            valueStr += values[i]+", ";
        }
    }       
    String sql = "insert into " + table+ "("+fieldStr+") values ("+valueStr+")";
    return this.excuteUpdate(sql, params);
}

@Override
public int insertToTable(String table, String[] fields, String[] values) throws Exception{
    return this.insertToTable(table, fields, values, null);
}

@Override
public int updateAtTable(String table, String[] setFields, String[] setValues, String[] whereFields, String[] checkTypes, String[] whereValues, String[] params) throws Exception{
    if(setFields==null || setFields.length==0){
        throw new Exception("待更新的字段名称不能为空");
    }
    if(setValues==null || setValues.length==0){
        throw new Exception("待更新的字段值不能为空");
    }
    if(setFields.length!=setValues.length){
        throw new Exception("待更新的字段个数"+setFields.length+"与值个数"+setValues.length+"不一致");
    }
    if(whereFields!=null){
        if(whereFields.length==0){
            throw new Exception("条件判断字段名称不能为空");
        }
        if(checkTypes.length==0){
            throw new Exception("条件判断字操作符不能为空");
        }
        if(whereValues.length==0){
            throw new Exception("条件值不能为空");
        }
        if(whereFields.length!=checkTypes.length){
            throw new Exception("条件判断数量不一致");
        }
        if(whereFields.length!=whereValues.length){
            throw new Exception("条件判断数量不一致");
        }
    }
    String setStr = "";
    for(int i=0;i<setFields.length;i++){
        String expression = setFields[i]+"="+setValues[i];
        if(i==setFields.length-1){
            setStr += expression+", ";
        }else{
            setStr += expression+" ";
        }
    }
    String whereStr = null;
    if(whereFields!=null){
        whereStr = "";
        for(int i=0;i<whereFields.length;i++){
            String expression = whereFields[i]+""+checkTypes[i]+""+whereValues[i];
            if(i==whereFields.length-1){
                whereStr += expression;
            }else{
                whereStr += " and "+expression;
            }
        }
    }   
    String sql = "";
    if(whereStr==null){
        sql = "update " + table+ "set "+setStr;
    }else{
        sql = "update " + table+ "set "+setStr+" where "+whereStr;
    }
    return this.excuteUpdate(sql, params);
}

@Override
public int updateAtTable(String table, String[] setFields, String[] setValues, String[] whereFields, String[] checkTypes, String[] whereValues) throws Exception{
    return this.updateAtTable(table, setFields, setValues, whereFields, checkTypes, whereValues, null);
}

@Override
public int deleteAtTable(String table, String[] whereFields, String[] checkTypes, String[] whereValues, String[] params) throws Exception{
    if(whereFields.length==0){
        throw new Exception("条件判断字段名称不能为空");
    }
    if(checkTypes.length==0){
        throw new Exception("条件判断字操作符不能为空");
    }
    if(whereValues.length==0){
        throw new Exception("条件值不能为空");
    }
    if(whereFields.length!=checkTypes.length){
        throw new Exception("条件判断数量不一致");
    }
    if(whereFields.length!=whereValues.length){
        throw new Exception("条件判断数量不一致");
    }
    String whereStr = null;
    for(int i=0;i<whereFields.length;i++){
        String expression = whereFields[i]+""+checkTypes[i]+""+whereValues[i];
        if(i==whereFields.length-1){
            whereStr += expression;
        }else{
            whereStr += " and "+expression;
        }
    }

    String sql = "";
    if(whereStr.isEmpty()){
        sql = "delete from " + table;
    }else{
        sql = "delete from " + table+ " where "+whereStr;
    }
    return this.excuteUpdate(sql, params);
}

@Override
public int deleteAtTable(String table, String[] whereFields, String[] checkTypes, String[] whereValues) throws Exception{
    return this.deleteAtTable(table, whereFields, checkTypes, whereValues, null);
}

@Override
public int deleteByTable(String table, String solution, String[] params) throws Exception{
    String sql = "delete from "+table+" where 1=1 ";
    if(solution!=null && !solution.isEmpty()){
        sql += " and "+solution;
    }
    return excuteUpdate(sql, params);
}

@Override
public int deleteByTable(String table, String solution) throws Exception{
    return deleteByTable(table, solution, null);
}

@Override
public List<String> queryOneColumnListByTable(String table, String in_columns, String out_column, String solution, String[] params) throws Exception {
    String sql = "select "+in_columns+" from "+table+" where 1=1 ";
    if(solution!=null && !solution.isEmpty()){
        sql += " and "+solution;
    }
    List<Map<String, String>> list = this.queryList(sql, params);
    List<String> result = new ArrayList<String>();
    for(int i=0;i<list.size();i++){
        Map<String, String> map = list.get(i);
        result.add(map.get(out_column.toUpperCase()));
    }
    return result;
}

@Override
public List<String> queryOneColumnListByTable(String table, String in_columns, String out_column, String solution) throws Exception {
    return this.queryOneColumnListByTable(table, in_columns, out_column, solution, null);
}

@Override
public PageUtil queryPageList(int requestPageNo, String sql) throws Exception{
    return queryPageList(requestPageNo, sql, null);
}

@Override
public PageUtil queryPageList(int requestPageNo, String sql, String[] params) throws Exception{
    return dbProvider.queryPageList(requestPageNo, sql, params);
}

@Override
public Map<String, String> queryKVMap(String sql, String[] params, String dataField, String labelField) throws Exception{
    List<Map<String, String>> list = this.queryList(sql, params);
    Map<String, String> result = new HashMap<String, String>();
    for(int i=0;i<list.size();i++){
        Map<String, String> map = list.get(i);
        String key = map.containsKey(dataField.toUpperCase()) ? map.get(dataField.toUpperCase()) : "";
        String value = map.containsKey(labelField.toUpperCase()) ? map.get(labelField.toUpperCase()) : "";
        if(!key.isEmpty()){
            result.put(key, value); 
        }
    }
    return result;
}

@Override
public Map<String, String> queryKVMap(String sql, String dataField, String labelField) throws Exception{
    return queryKVMap(sql, null, dataField, labelField);
}

public DBEnitiy getDBEnitiy() throws Exception{
    return dbProvider.getDBEnitiy();
}

@Override
public DBTable getDBTable(String name) throws Exception {
    return dbProvider.getDBTable(name);
}

@Override
public DBView getDBView(String name) throws Exception {
    return dbProvider.getDBView(name);
}

@Override
public DBSequence getDBSequence(String name) throws Exception {
    return dbProvider.getDBSequence(name);
}

@Override
public List<String> getDBTableNames() throws Exception {
    return dbProvider.getDBTableNames();
}

@Override
public List<String> getDBViewNames() throws Exception {
    return dbProvider.getDBViewNames();
}

private String trackValue(Object object){
    if(object == null || object.toString().trim().length()==0 || object.toString().trim().toLowerCase().equals("null")){
        return "";
    }else{
        return String.valueOf(object);
    }
}

@Override
public void setInfo(DBInfo info) {
    this.info = info;
}

@Override
public DBInfo getInfo() {
    return this.info;
}

public static void main(String[] args){
    DBAction dbAction = null;
    try {
        DBInfo info = new DBInfo();
        info.setDriver("com.informix.jdbc.IfxDriver");
        info.setUrl("jdbc:informix-sqli://192.168.2.231:7777/hanyong:informixserver=minicc2;CLIENT_LOCALE=zh_CN.gb;DB_LOCALE=zh_CN.gb");
        info.setUser("informix");
        info.setPassword("abc123");

        dbAction = info.createDBAction();
        dbAction.openTransaction();
        DBTable dbTable = dbAction.getDBTable("user_info");
        System.out.println(dbTable.getFieldString());

// long userRi = dbAction.getSequenceNextValue("seq_user_info");
// long roleRi = dbAction.getSequenceNextValue("seq_role_info");
//

// String sqlAddUser = "insert into user_info (ri, name, role_ri, address) values ("+userRi+", '少女', 0, '地址')";
// dbAction.excuteUpdate(sqlAddUser, null);

        dbAction.commitTransaction();
    } catch (Exception e) {
        if(dbAction!=null){
            try {
                dbAction.rollbackTransaction();
            } catch (Exception e1) {
                e1.printStackTrace();
            }
        }
        e.printStackTrace();
    }
}

public String getSchemaName() {
    return schemaName;
}

}