java

java开发自建jdbc链接数据库执行存储过程
package com.peraglobal.rdms.project.utils;

import com.peraglobal.pdp.common.utils.StringUtil;
import com.peraglobal.pdp.core.utils.AppConfigUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.InitializingBean;

import java.sql.*;
public class DatasourceManagerUtils implements InitializingBean {

    Logger logger = LoggerFactory.getLogger(this.getClass());

    private String url = "";
    private String drivername = "";
    private String userName = "";
    private String password = "";
    private Connection con = null;
    private Statement stm = null;
    private CallableStatement proc = null;
    private static DatasourceManagerUtils datasourceManagerUtils = null ;
    public static DatasourceManagerUtils getInstance() throws Exception{
        if(datasourceManagerUtils==null){
            String db_url = AppConfigUtils.get("db.url.master.default");
            String db_uname = AppConfigUtils.get("jdbc.masterName");
            String db_pwd = AppConfigUtils.get("jdbc.masterPasswd");
            datasourceManagerUtils = new DatasourceManagerUtils(db_url, db_uname, db_pwd);
        }
        return datasourceManagerUtils;
    }

    @Override
    public void afterPropertiesSet() throws Exception {
        getInstance();
    }

    private DatasourceManagerUtils(String db_url, String db_uname, String db_pwd) throws Exception {
        try {
            this.url = db_url;
            this.drivername = "oracle.jdbc.OracleDriver";
            this.userName = db_uname;
            this.password = db_pwd;
            Class.forName(this.drivername).newInstance();
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("数据库连接异常");
        }
    }

    public Connection getDBCon() throws Exception {
        if (StringUtil.isNotEmpty(this.url)) {
            try {
                System.out.println(this.url);
                DriverManager.setLoginTimeout(30);
                this.con = DriverManager.getConnection(this.url, this.userName, this.password);
            } catch (Exception e) {
                e.printStackTrace();
                throw new Exception("ERP数据库连接异常");
            }
        } else {
            System.out.println("未设置数据库参数");
            throw new Exception("未设置数据库参数");
        }
        return this.con;
    }

    public void createCon() throws Exception {
        this.con = DriverManager.getConnection(this.url, this.userName, this.password);
    }

    public void getStm() throws Exception {
        createCon();
        this.stm = this.con.createStatement();
    }

    public int executeUpdate(String sql) throws Exception {
        int mark = 0;
        getStm();
        int iCount = this.stm.executeUpdate(sql);
        if (iCount > 0)
            mark = 1;
        else
            mark = 0;
        return mark;
    }

    public ResultSet executeQuery(String sql) throws Exception {
        ResultSet rs = null;
        getStm();
        try {
            rs = this.stm.executeQuery(sql);
        } catch (Exception e) {
            e.printStackTrace();
            logger.error("查询数据库失败！");
        }
        return rs;
    }

    public boolean execute(String sql) throws Exception {
        boolean flag = false;
        getStm();
        try {
            flag = this.stm.execute(sql);
        } catch (Exception e) {
            e.printStackTrace();
            logger.error("sql执行失败！");
        }
        return flag;
    }

    public int executeProlifecycletoasstaskitem(String lifeCycleRootId,String assTaskRootId,String versionId,String projectId) throws Exception {
        int k = 0;
        try {
            createCon();
            this.proc = this.con.prepareCall("{CALL tips_package.pro_lifecycletoasstaskitem(?,?,?,?,?)}");
            this.proc.setString(1, lifeCycleRootId);
            this.proc.setString(2, assTaskRootId);
            this.proc.setString(3, assTaskRootId);
            this.proc.setString(4, versionId);
            this.proc.setString(5, projectId);
            k = this.proc.executeUpdate();
            this.con.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            this.closed();
        }
        return k;
    }

    public void closed() {
        if (this.stm != null)
            try {
                this.stm.close();
            } catch (SQLException e) {
                e.printStackTrace();
                logger.error("关闭stm对象失败！");
            }
        if (this.proc != null)
            try {
                this.proc.close();
            } catch (SQLException e) {
                e.printStackTrace();
                logger.error("关闭proc对象失败！");
            }
        if (this.con != null)
            try {
                this.con.close();
            } catch (SQLException e) {
                e.printStackTrace();
                logger.error("关闭con对象失败！");
            }
    }
}