Banninger/src/rest/openapi/base/data/db/DBConnect.java

/**
 *
 */
package openapi.base.data.db;

import openapi.base.BaseClass;
import openapi.base.data.Row;
import openapi.base.data.Rows;
import p2.p2server.P2Server;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author SJW
 *
 */
public class DBConnect extends BaseClass {

    String datakey;

    public DBConnect() {

        this.datakey = "P2DATA";

        if (!DBConnectPool.propertiesMap.containsKey(datakey)) {
            String connectstring = P2Server.getP2Server().getDBConnProvider().getProperties().getProperty("p2.db.connectstring");

            String loginid = P2Server.getP2Server().getDBConnProvider().getProperties().getProperty("p2.db.loginid");
            String password = P2Server.getP2Server().getDBConnProvider().getProperties().getProperty("p2.db.password");

            String ipaddress = connectstring.substring(connectstring.indexOf("inetdae7:"), connectstring.indexOf("?")).substring(9);
            String dataname = connectstring.substring(connectstring.indexOf("database="), connectstring.indexOf("&")).substring(9);

            String url = "jdbc:sqlserver://" + ipaddress + ";databasename=" + dataname;
            String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
            new SQLiteJDBC().createP2DataConfig(datakey, driver, loginid, password, url);
        }
    }

    public DBConnect(String datakey) {
        this.datakey = datakey;
    }

    private void close(Connection conn, PreparedStatement statement) {
        if (statement != null) {
            try {
                statement.close();
                statement = null;
            } catch (SQLException e) {
                e.printStackTrace();
                printErrOut("statement关闭失败", e.getMessage(), false);
            }
        }
        if (conn != null) {
            new DBConnectPool().close(datakey, conn);
        }
    }

    public boolean test() {
        return true;
    }

    public Rows runSqlQuery(String SQL) {
        Connection conn = new DBConnectPool().getConnect(datakey);
        if (conn == null) {
            return new Rows();
        }
        Rows rows = new Rows();
        ResultSet resultSet = null;
        PreparedStatement statement = null;
        try {
            statement = conn.prepareStatement(SQL);
            resultSet = statement.executeQuery();
            ResultSetMetaData rsmd = resultSet.getMetaData();
            int colCount = rsmd.getColumnCount();
            List<String> colNameList = new ArrayList<String>(16);
            List<String> colTypeList = new ArrayList<String>();
            for (int i = 0; i < colCount; i++) {
                colNameList.add(rsmd.getColumnName(i + 1));
                colTypeList.add(rsmd.getColumnTypeName(i + 1));
            }
            while (resultSet.next()) {
                Row row = new Row();
                for (int i = 0; i < colCount; i++) {
                    String key = colNameList.get(i);
                    String type = colTypeList.get(i);
                    Object value;
                    switch (type) {
                        case "int":
                            value = resultSet.getInt(colNameList.get(i));
                            break;
                        case "bigint":
                            value = resultSet.getLong(colNameList.get(i));
                            break;
                        case "smallint":
                            //value = resultSet.getShort(colNameList.get(i));
                            value = resultSet.getString(colNameList.get(i));
                            break;
                        case "numeric":
                            value = resultSet.getDouble(colNameList.get(i));
                            break;
                        case "decimal":
                            value = resultSet.getBigDecimal(colNameList.get(i));
                            break;
                        case "datetime":
                            value = resultSet.getString(colNameList.get(i));
                            if (value != null) {
                                value = ((String) value).replace(" 00:00:00.0", "");
                            }
                            break;
                        default:
                            value = resultSet.getString(colNameList.get(i));
                            break;
                    }

                    if ("total".equals(key)) {
                        rows.totalRows = (Integer) value;
                    } else if ("pageTotal".equals(key)) {
                        rows.totalPage = new Double((Double) value).intValue();
                    } else {
                        row.put(key, value);
                    }
                }
                rows.add(row);
            }
            if (rows.totalRows == 0) {
                rows.totalRows = rows.size();
                rows.totalPage = 1;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            printErrOut(e.getMessage(), SQL, false);
            rows.errmsg = e.getMessage();
            try {
                conn.close();
                conn = null;
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                    resultSet = null;
                } catch (SQLException e) {
                    printErrOut(e.getMessage(), SQL, false);
                    e.printStackTrace();
                }
            }
            close(conn, statement);
        }
        return rows;
    }

    public String runSqlUpdate(String SQL) {
        Connection conn = new DBConnectPool().getConnect(datakey);
        if (conn == null) {
            return datakey + "连接失败";
        }
        PreparedStatement statement = null;
        String status = "true";
        try {
            statement = conn.prepareStatement(SQL);
            statement.executeUpdate();
            conn.commit();
        } catch (SQLException e) {
            printErrOut(e.getMessage(), SQL, false);
            status = e.getMessage();
            try {
                conn.rollback();
            } catch (SQLException e1) {
                printErrOut(e.getMessage(), SQL, false);
                e1.printStackTrace();
            }
            try {
                conn.close();
                conn = null;
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
        } finally {
            close(conn, statement);
        }
        return status;
    }

    public String runSqlUpdate(ArrayList<String> SQLlist) {
        Connection conn = new DBConnectPool().getConnect(datakey);
        if (conn == null) {
            return datakey + "连接失败";
        }
        PreparedStatement statement = null;
        String status = "true";
        StringBuffer buffer = new StringBuffer();
        try {
            for (String sql : SQLlist) {
                buffer.append(sql);
                statement = conn.prepareStatement(sql);
                statement.executeUpdate();
            }
            conn.commit();
        } catch (SQLException e) {
            printErrOut("sql执行错误", e.getMessage() + ":" + buffer.toString(), false);
            status = e.getMessage();
            try {
                conn.rollback();
            } catch (SQLException e1) {
                printErrOut(e.getMessage(), buffer.toString(), false);
                e1.printStackTrace();
            }
            try {
                conn.close();
                conn = null;
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
        } finally {
            buffer = null;
            close(conn, statement);
        }
        return status;
    }
}