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
}}
給你一個例子，適合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;
}
}
