ava jdbc-能将里面的方法完善吗？

问题描述

能将里面的方法完善吗？

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;
}

}

时间： 2024-09-08 15:28:58

ava jdbc-能将里面的方法完善吗？的相关文章

java-oa系统怎么使用jdbc链接数据库调用查询方法最后在jsp上分页显示？？？

问题描述 oa系统怎么使用jdbc链接数据库调用查询方法最后在jsp上分页显示??? 表名:users , 用户字段:realname 要求 效果: 解决方案 http://blog.csdn.net/aaabendan/article/details/5442144 解决方案二: http://download.csdn.net/detail/zhai56565/5885775 解决方案三: JdbcTemplate类 package com.cloudwebsoft.framework.db

JSP使用JDBC连接MYSQL数据库的方法_JSP编程

本文实例讲述了JSP使用JDBC连接MYSQL数据库的方法.分享给大家供大家参考,具体如下: 1. 可在 http://www.mysql.com/products/connector-j/index.html下载MySQL JDBC驱动程序mysql-connector-java-*.jar,如我下载的是mysql-connector-java-5.1.18-bin.jar并加入到ClassPath下面,或加入到项目中.   2. 注册JDBC驱动程序 try { Class.forName(

sql2005 jdbc连接mssql数据库实现方法

sql2005 jdbc连接mssql数据库教程实现方法 class.forname("com.microsoft.sqlserver.jdbc.sqlserverdriver"); url = "jdbc:sqlserver://localhost:1433;databasename=tempdb"; 首先下载到sql2005jdbc驱动sqljdbc_2.0.1008.2_enu.exe(版本号1.1之后都支持sql2005,2.0的支持sql2008) 下载的

jsp用jdbc连接db2数据库的方法

js|数据|数据库 数据库版本:db2 UDB v7.2服务器操作系统:aix4.3.3 1.关闭服务器上的web服务:2.停止jdbc侦听进程db2jd,db2jd一般启动的是6789的端口服务,   先用ps -ef | grep db2jd 查看到它的进程号   然后用kill -9 <进程号>  命令停止;3.在数据库服务器中,进入/usr/lpp/db2_07_01/java12目录,执行./usejdbc2以启用jdbc2:4.将/usr/lpp/db2_07_01/java12下

较新版本的spring jdbc template将queryForInt等方法标记为过时，为什么

问题描述 以前都用queryForInt来求count(*),最近换了新版本spring,发现标记为过时了,替代方法虽然有很多,但这个比较直接,为什么过时? 解决方案 现在方法统一 了jdbcTemplate.queryForObject(sql, Integer.class, id)

jdbc数据库连接的几种方法

连接sqlserver 2000或则2005采用,sqljdbc.jar驱动包,连接mysql教程的需要mysql jdbc驱动包   import java.sql.*; public class sqltest {  /**   * @param args   * @throws classnotfoundexception   * @throws illegalaccessexception   * @throws instantiationexception   * @throws sq

详解JDBC连接Access的三种方法

JDBC连接Access第一种方法: 你用下面的代码试试 (强烈建议也是only可以建议的方法) con = DriverManager.getConnection("jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=C:/data/Access/test1.mdb","dba","sql"); 后面的代码一样.这样你就可以访问access数据库了. JDBC连接Access第二种方法:

全面解析JDBC

综述:Java数据库连接体系结构是用于Java应用程序连接数据库的标准方法.JDBC对Java程序员而言是API,对实现与数据库连接的服务提供商而言是接口模型.作为API,JDBC为程序开发提供标准的接口,并为数据库厂商及第三方中间件厂商实现与数据库的连接提供了标准方法.JDBC使用已有的SQL标准并支持与其它数据库连接标准,如ODBC之间的桥接.JDBC实现了所有这些面向标准的目标并且具有简单.严格类型定义且高性能实现的接口. 如何选择合适的JDBC产品? 有关JDBC最新的信息,有兴趣的读者

JSP学习——全面解析JDBC（8）附录

js 附录:JDBC TM技术解析 1. JDBCTM的定义 JDBCTM 是一种用于执行 SQL 语句的 JavaTM API,它由一组用 Java 编程语言编写的类和接口组成.JDBC 为工具/数据库开发人员提供了一个标准的API,使他们能够用纯 Java API 来编写数据库应用程序. 有了 JDBC,向各种关系数据库发送 SQL 语句就是一件很容易的事.换言之,有了 JDBC API,就不必为访问 Sybase 数据库专门写一个程序,为访问 Oracle 数据库又专门写一个程序,为访问