Example usage for java.sql ResultSet getObject

List of usage examples for java.sql ResultSet getObject

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. ResultSet
  6. getObject

Introduction

In this page you can find the example usage for java.sql ResultSet getObject.

Prototype

Object getObject(String columnLabel) throws SQLException;

Source Link

Document

Gets the value of the designated column in the current row of this ResultSet object as an Object in the Java programming language.

Usage

From source file:at.alladin.rmbt.statisticServer.OpenTestSearchResource.java

/**
 * Gets a JSON-String containing all open-data-values of all rows
 * that matched the given criteria//from   w w w.j a v a  2  s .  c  om
 * @param whereClause the where-clause to use
 * @param searchValues the values for the columns which the user wants to filter
 * @param offset a offset-value for paging (given as "next-cursor" in the response), -1 if none is set
 * @return 
 */
private String getSearchResult(String whereClause, Queue<Map.Entry<String, FieldType>> searchValues,
        String orderClause, long offset, long maxrows) {
    long startTime = System.currentTimeMillis();
    String offsetString = (offset > 0) ? " AND t.uid<" + offset : ""; //if no sorting is used
    String offsetString2 = (offset > 0) ? " OFFSET " + offset : ""; //if sorting is used => may have concurrency issues in the results
    boolean defaultOrder = true;
    if (orderClause == null || orderClause.isEmpty()) {
        orderClause = " ORDER BY t.uid DESC ";
        offsetString2 = "";
    } else {
        defaultOrder = false;
        offsetString = "";
    }

    if (maxrows > MAXROWS)
        maxrows = MAXROWS;
    if (maxrows <= 0)
        maxrows = DEFAULTROWS;

    //There are many LEFT JOINs in the sql statement that are usual not needed.
    //This has no significant impact on the performance since our DBMS (postgres)
    //is intelligent enough to ignore these during query optimization if they are
    //not needed
    final String sql = "SELECT" + " t.uid as cursor, " + //only for pagination
            " ('P' || t.open_uuid) open_uuid," + " ('O' || t.open_test_uuid) open_test_uuid,"
            + " to_char(t.time AT TIME ZONE 'UTC', 'YYYY-MM-DD HH24:MI:SS') \"time\"," +
            //" nt.group_name cat_technology," +
            //" nt.name network_type," +
            " t.geo_lat lat," + " t.geo_long long," +
            //" t.geo_provider loc_src," +
            //" t.zip_code," +
            " t.speed_download download_kbit," + " t.speed_upload upload_kbit,"
            + " (t.ping_median::float / 1000000) ping_ms," + " t.signal_strength," + " t.lte_rsrp," +
            //" ts.name server_name," +
            //" duration test_duration," +
            //" num_threads," +
            " (CASE WHEN publish_public_data THEN CONCAT(plattform,' ',network_group_name) ELSE network_group_name END) as platform, "
            + " (CASE WHEN publish_public_data THEN COALESCE(adm.fullname, t.model) ELSE '' END) model,"
            + " COALESCE(prov.shortname, mprov.shortname, msim.shortname,msim.name,"
            + "    prov.name, mprov.name, network_operator_name, t.public_ip_as_name, network_sim_operator) provider_name "
            +
            //" client_software_version client_version," +
            //" network_operator network_mcc_mnc," +
            //" network_operator_name network_name," +
            //" network_sim_operator sim_mcc_mnc," +
            //" nat_type \"connection\"," +
            //" public_ip_asn asn," +
            //" client_public_ip_anonymized ip_anonym," +
            //" (ndt.s2cspd*1000)::int ndt_download_kbit," +
            //" (ndt.c2sspd*1000)::int ndt_upload_kbit" +
            " FROM test t" + " LEFT JOIN network_type nt ON nt.uid=t.network_type"
            + " LEFT JOIN device_map adm ON adm.codename=t.model"
            + " LEFT JOIN test_server ts ON ts.uid=t.server_id"
            + " LEFT JOIN provider prov ON provider_id = prov.uid "
            + " LEFT JOIN provider mprov ON mobile_provider_id = mprov.uid"
            + " LEFT JOIN mccmnc2name msim ON mobile_sim_id = msim.uid" + //TODO: finalize migration to msim/mnwk 
            " WHERE " + " (t.deleted = false)" + ((this.excludeImplausible) ? " AND implausible = false" : "")
            + " AND status = 'FINISHED' " + whereClause + offsetString + orderClause + " LIMIT " + maxrows
            + offsetString2;

    final String[] columns;
    PreparedStatement ps = null;
    ResultSet rs = null;
    final JSONObject response = new JSONObject();
    final JSONArray resultList = new JSONArray();
    try {
        ps = conn.prepareStatement(sql);

        //fill in values for WHERE
        ps = fillInWhereClause(ps, searchValues, 1);

        //Logger.getLogger(OpenTestResource.class.getName()).log(Level.INFO, "prepstmt" + ps);

        if (!ps.execute())
            return null;
        rs = ps.getResultSet();

        long lastUID = 0; //remember last uid for pagination since rs can only be traversed in one direction
        while (rs.next()) {
            final JSONObject jsonItem = new JSONObject();

            for (int i = 0; i < openDataFieldsSummary.length; i++) {
                final Object obj = rs.getObject(openDataFieldsSummary[i]);
                if (obj == null) {
                    jsonItem.put(openDataFieldsSummary[i], JSONObject.NULL);
                } else if (openDataNumberFields.contains(openDataFieldsSummary[i])) {
                    final String tmp = obj.toString().trim();
                    if (tmp.isEmpty())
                        jsonItem.put(openDataFieldsSummary[i], JSONObject.NULL);
                    else
                        jsonItem.put(openDataFieldsSummary[i], JSONObject.stringToValue(tmp));
                } else {
                    jsonItem.put(openDataFieldsSummary[i], obj.toString());
                }

            }
            lastUID = rs.getLong("cursor");
            resultList.put(jsonItem);
        }
        //if there are more results than we send, use pagination
        if (resultList.length() == maxrows) {
            //if it is the standard sort order
            if (defaultOrder) {
                response.put("next_cursor", lastUID);
            } else {
                offset = (offset < 0) ? 0 : offset;
                response.put("next_cursor", offset + maxrows);
            }
        } else {
            response.put("next_cursor", JSONObject.NULL);
        }

        response.put("results", resultList);

        //also put in the result, how long the query took to execute
        long elapsedTime = System.currentTimeMillis() - startTime;
        response.put("duration_ms", elapsedTime);
    } catch (final JSONException e) {
        Logger.getLogger(OpenTestResource.class.getName()).log(Level.SEVERE, null, e);
    } catch (SQLException ex) {
        try {
            setStatus(Status.CLIENT_ERROR_NOT_FOUND);
            response.put("error", "invalid parameters");
        } catch (JSONException ex1) {
            Logger.getLogger(OpenTestResource.class.getName()).log(Level.SEVERE, null, ex1);
        }
        Logger.getLogger(OpenTestResource.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        try {
            if (rs != null)
                rs.close();
            if (ps != null)
                ps.close();
        } catch (final SQLException e) {
            Logger.getLogger(OpenTestResource.class.getName()).log(Level.SEVERE, null, e);
        }
    }

    return response.toString();
}

From source file:edu.ku.brc.specify.toycode.mexconabio.CopyFromGBIF.java

/**
 * //  w  ww .  j a va 2s.  co  m
 */
public void processMissingGenusSpecies() {
    String pSQL = "UPDATE raw SET genus=?, species=?, subspecies=? WHERE id = ?";

    String where = " WHERE genus IS NULL AND species IS NULL AND scientific_name IS NOT NULL";
    String gbifSQLBase = "SELECT id, scientific_name FROM raw" + where;

    long totalRecs = BasicSQLUtils.getCount(srcDBConn, "SELECT COUNT(*) FROM raw " + where);
    long procRecs = 0;
    long startTime = System.currentTimeMillis();
    int secsThreshold = 0;

    PrintWriter pw = null;

    final double HRS = 1000.0 * 60.0 * 60.0;

    Statement gStmt = null;
    PreparedStatement pStmt = null;

    try {
        pw = new PrintWriter("gbif.log");

        pStmt = dbConn.prepareStatement(pSQL);

        System.out.println("Total Records: " + totalRecs);
        pw.println("Total Records: " + totalRecs);

        gStmt = srcDBConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        gStmt.setFetchSize(Integer.MIN_VALUE);

        System.out.println(gbifSQLBase);

        ResultSet gRS = gStmt.executeQuery(gbifSQLBase);
        while (gRS.next()) {
            int id = gRS.getInt(1);
            pStmt.setObject(4, id);

            String[] gs = StringUtils.split(gRS.getString(2), ' ');
            switch (gs.length) {
            case 1:
                pStmt.setString(1, gs[0]);
                pStmt.setString(2, null);
                pStmt.setString(3, null);
                break;

            case 2:
                pStmt.setString(1, gs[0]);
                pStmt.setString(2, gs[1]);
                pStmt.setString(3, null);
                break;

            case 3:
                pStmt.setString(1, gs[0]);
                pStmt.setString(2, gs[1]);
                pStmt.setString(3, gs[2]);
                break;

            default:
                continue;
            }
            try {
                pStmt.executeUpdate();

            } catch (Exception ex) {
                System.err.println("For ID[" + gRS.getObject(1) + "][" + gRS.getObject(2) + "]");
                ex.printStackTrace();
                pw.print("For ID[" + gRS.getObject(1) + "] " + ex.getMessage());
                pw.flush();
            }

            procRecs++;
            if (procRecs % 10000 == 0) {
                long endTime = System.currentTimeMillis();
                long elapsedTime = endTime - startTime;

                double avergeTime = (double) elapsedTime / (double) procRecs;

                double hrsLeft = (((double) elapsedTime / (double) procRecs) * (double) totalRecs - procRecs)
                        / HRS;

                int seconds = (int) (elapsedTime / 60000.0);
                if (secsThreshold != seconds) {
                    secsThreshold = seconds;

                    String msg = String.format(
                            "Elapsed %8.2f hr.mn   Ave Time: %5.2f    Percent: %6.3f  Hours Left: %8.2f ",
                            ((double) (elapsedTime)) / HRS, avergeTime,
                            100.0 * ((double) procRecs / (double) totalRecs), hrsLeft);
                    System.out.println(msg);
                    pw.println(msg);
                    pw.flush();
                }
            }
        }

    } catch (Exception ex) {
        ex.printStackTrace();

    } finally {
        try {
            if (gStmt != null) {
                gStmt.close();
            }
            if (pStmt != null) {
                pStmt.close();
            }
            pw.close();

        } catch (Exception ex) {

        }
    }
    System.out.println("Done transferring.");
    pw.println("Done transferring.");
}

From source file:com.glaf.dts.transform.MxTransformManager.java

protected List<Map<String, Object>> prepare(QueryDefinition query) {
    logger.debug("-------------------------1 start------------------------");
    List<Map<String, Object>> resultList = new java.util.ArrayList<Map<String, Object>>();
    Connection conn = null;//from w ww  .ja  v a2  s  . c o m
    PreparedStatement psmt = null;
    ResultSet rs = null;
    ResultSetMetaData rsmd = null;
    try {
        Database database = databaseService.getDatabaseById(query.getDatabaseId());
        if (database != null) {
            conn = DBConnectionFactory.getConnection(database.getName());
        } else {
            conn = DBConnectionFactory.getConnection();
        }
        logger.debug("-------------------------1 connection------------------------");

        String sql = QueryUtils.replaceSQLVars(query.getSql());
        logger.debug(">sql=" + query.getSql());
        psmt = conn.prepareStatement(sql);

        rs = psmt.executeQuery();
        rsmd = rs.getMetaData();
        logger.debug("-------------------------1 executeQuery------------------------");
        int count = rsmd.getColumnCount();
        while (rs.next()) {
            Map<String, Object> rowMap = new java.util.HashMap<String, Object>();
            for (int i = 1; i <= count; i++) {
                String columnName = rsmd.getColumnLabel(i);
                if (null == columnName || 0 == columnName.length()) {
                    columnName = rsmd.getColumnName(i);
                }
                try {
                    rowMap.put(columnName, rs.getObject(i));
                } catch (SQLException ex) {
                    rowMap.put(columnName, rs.getString(i));
                }
            }
            resultList.add(rowMap);
        }

        query.setResultList(resultList);

        // logger.debug(">resultList=" + resultList);
        return resultList;
    } catch (Exception ex) {
        logger.error(ex);
        ex.printStackTrace();
        throw new RuntimeException(ex);
    } finally {
        JdbcUtils.close(rs);
        JdbcUtils.close(psmt);
        JdbcUtils.close(conn);
        logger.debug("-------------------------1 start------------------------");
    }
}

From source file:edu.ku.brc.specify.toycode.RegPivot.java

/**
 * @param newTblName//from   w w w .java 2s  . c  o m
 * @param stmt
 * @param pStmt
 * @param fillSQL
 * @param secInx
 * @param dbFieldTypes
 * @param dbFieldNames
 * @param inxToName
 * @return
 * @throws SQLException
 */
private int fillTrackTable(final String newTblName, final Statement stmt, final PreparedStatement pStmt,
        final String fillSQL, final int secInx, final Vector<Integer> dbFieldTypes,
        final Vector<String> dbFieldNames, final HashMap<Integer, String> inxToName) throws SQLException {
    System.out.println("Filling Track Table.");

    int instCnt = 0;

    System.out.println(fillSQL);

    ResultSet rs = stmt.executeQuery(fillSQL);
    ResultSetMetaData rsmd = rs.getMetaData();

    HashMap<String, Integer> nameToIndex = new HashMap<String, Integer>();
    for (int c = 1; c <= rsmd.getColumnCount(); c++) {
        nameToIndex.put(rsmd.getColumnName(c), c);
        System.out.println(c + " - " + rsmd.getColumnName(c));
    }

    boolean debug = false;

    String prevRegId = null;

    HashMap<String, HashMap<String, Object>> colHash = new HashMap<String, HashMap<String, Object>>();

    HashMap<String, Object> nameToVals = new HashMap<String, Object>();

    while (rs.next()) {
        String regId = rs.getString(1);
        if (prevRegId == null)
            prevRegId = regId;

        for (int i = 1; i < secInx; i++) {
            if (debug)
                System.out.println("Put: " + dbFieldNames.get(i - 1) + "  " + dbFieldTypes.get(i - 1) + "  = "
                        + rs.getObject(i));

            if (dbFieldTypes.get(i - 1) == java.sql.Types.TIMESTAMP) {
                try {
                    String ts = rs.getString(i);
                    if (StringUtils.isNotEmpty(ts) && ts.equals("0000-00-00 00:00:00")) {
                        continue;
                    }
                } catch (Exception ex) {
                    continue;
                }
            }
            nameToVals.put(dbFieldNames.get(i - 1), rs.getObject(i));
        }
        String name = rs.getString(secInx);
        name = StringUtils.replace(name, "(", "_");
        name = StringUtils.replace(name, ")", "_");

        if (name.equals("reg_type")) {
            String strVal = (String) rs.getObject(secInx + 2);
            name = strVal + "_number";

            nameToVals.put(name, regId);
            if (debug)
                System.out.println("Put: " + name + " = " + regId);
        } else {
            Integer intVal = (Integer) rs.getObject(secInx + 1);
            String strVal = (String) rs.getObject(secInx + 2);
            nameToVals.put(name, strVal != null ? strVal : intVal);
            if (debug)
                System.out.println("Put: " + name + " = " + intVal + " / " + strVal);
        }

        if (debug)
            System.out.println("-------------------------------------------");

        if (!prevRegId.equals(regId)) {
            String colNum = (String) nameToVals.get("Collection_number");

            if (StringUtils.isNotEmpty(colNum)) {
                copyHash(colNum, colHash, nameToVals);
            }
            prevRegId = regId;
            nameToVals.clear();
        }
    }

    writeHash(colHash, null, pStmt, dbFieldTypes, dbFieldNames, inxToName);

    String alterSQL = "ALTER TABLE " + newTblName + " ADD Lookup VARCHAR(64) AFTER IP";
    BasicSQLUtils.update(connection, alterSQL);

    alterSQL = "ALTER TABLE " + newTblName + " ADD Country VARCHAR(64) AFTER Lookup";
    BasicSQLUtils.update(connection, alterSQL);

    alterSQL = "ALTER TABLE " + newTblName + " ADD City VARCHAR(64) AFTER Country";
    BasicSQLUtils.update(connection, alterSQL);

    return instCnt;
}

From source file:org.metis.sql.SqlStmnt.java

/**
 * This method is a call-back method for the Spring JdbcTemplate's query
 * call. It is responsible for mapping a row in the result set to a map. The
 * returned map is placed into a list or array that is eventually
 * transformed into a json array or object.
 *//*  w w  w .  j ava  2 s  .  c o m*/
public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {

    Map<String, Object> map = new HashMap<String, Object>();
    ResultSetMetaData rsmd = rs.getMetaData();
    int columnCount = rsmd.getColumnCount();
    for (int index = 1; index <= columnCount; index++) {
        String column = JdbcUtils.lookupColumnName(rsmd, index);
        Object value = rs.getObject(column);
        map.put(column, value);
    }
    return map;
}

From source file:com.p5solutions.core.jpa.orm.rowbinder.EntityRowBinder.java

/**
 * Gets the basic result object./*from  w w  w.  jav a2s.  com*/
 * 
 * @param rs
 *          the rs
 * @return the basic result object
 * @throws SQLException
 *           the sQL exception
 */
private Object getBasicResultObject(ResultSet rs) throws SQLException {
    final boolean isPlainClass = Object.class.equals(entityClass);
    final boolean isBasicClass = ReflectionUtility.isBasicClass(entityClass);

    ResultSetMetaData metaData = rs.getMetaData();

    // if the entity is a of sub-type object.class or is some sort of
    // primitive class such as BigDecimal, Integer, Double, Short, etc.
    if (isPlainClass || isBasicClass) {

        // / obviously if we have more than one column, we cannot
        // possibly map it
        // / to a plain old java object of type Object.class, since
        // there are no
        // / members to map the columns to!
        if (metaData.getColumnCount() > 1) {
            String error = "Cannot return multi-column resultset into "
                    + "a plain object of type Object.class. If you need to map a multi-column "
                    + "resultset, please use an object marked with @" + Entity.class + " annotation.";
            logger.error(error);

            throw new RuntimeException(error);
        }

        // // THIS SHOULD NEVER HAPPEN, QUERY EXCEPTION SHOULD
        // // BE THROWN IF THERE IS A SYNTAX ERROR IN THE QUERY.
        // if (metaData.getColumnCount() == 0) { }

        // Otherwise if there is only 1 column, and its within the scope
        // of plain object.class
        return (T) rs.getObject(1);
    }

    return null;
}

From source file:de.innovationgate.webgate.api.jdbc.custom.JDBCSource.java

/**
 * @param resultSet// w  w w.  j a v a  2s  . co  m
 * @return
 */
private Map extractRowKey(ResultSet resultSet, String tableHint) throws SQLException {
    ResultSetMetaData rsMeta = resultSet.getMetaData();
    TableName tableName = new TableName(rsMeta.getCatalogName(1), rsMeta.getSchemaName(1),
            rsMeta.getTableName(1));
    String completeTableName = tableName.getCompleteName();
    if (completeTableName == null || completeTableName.trim().equals("")) {
        completeTableName = tableHint;
    }

    Map keys = new KeyMap();
    List keyColumns = (List) _tables.get(String.valueOf(completeTableName).toLowerCase());

    // If key columns are not retrievable, just return the empty map as key
    if (keyColumns == null) {
        return keys;
    }

    Iterator keyColumnsIt = keyColumns.iterator();
    while (keyColumnsIt.hasNext()) {
        String keyColumn = (String) keyColumnsIt.next();
        Object keyValue = resultSet.getObject(keyColumn);

        // Since the key columns from KeyMap originate from the _tables list, we should use the column names unmodified
        // keys.put(keyColumn.toLowerCase(), keyValue);
        keys.put(keyColumn, keyValue);
    }

    return keys;

}

From source file:com.panet.imeta.trans.steps.scriptvalues_mod.ScriptValuesAddedFunctions.java

public static Object fireToDB(Context actualContext, Scriptable actualObject, Object[] ArgList,
        Function FunctionContext) {

    Object oRC = new Object();
    if (ArgList.length == 2) {
        try {/* w w  w.  jav a2 s.c o m*/
            Object scmO = actualObject.get("_step_", actualObject);
            ScriptValuesMod scm = (ScriptValuesMod) Context.jsToJava(scmO, ScriptValuesMod.class);
            String strDBName = Context.toString(ArgList[0]);
            String strSQL = Context.toString(ArgList[1]);
            DatabaseMeta ci = DatabaseMeta.findDatabase(scm.getTransMeta().getDatabases(), strDBName);
            ci.shareVariablesWith(scm);

            Database db = new Database(ci);
            db.setQueryLimit(0);
            try {
                db.connect();
                ResultSet rs = db.openQuery(strSQL);
                ResultSetMetaData resultSetMetaData = rs.getMetaData();
                int columnCount = resultSetMetaData.getColumnCount();
                if (rs != null) {
                    List<Object[]> list = new ArrayList<Object[]>();
                    while (rs.next()) {
                        Object[] objRow = new Object[columnCount];
                        for (int i = 0; i < columnCount; i++) {
                            objRow[i] = rs.getObject(i + 1);
                        }
                        list.add(objRow);
                    }
                    Object[][] resultArr = new Object[list.size()][];
                    list.toArray(resultArr);
                    db.disconnect();
                    return resultArr;
                }
            } catch (Exception er) {
                throw Context.reportRuntimeError(er.toString());
            }
        } catch (Exception e) {
        }
    } else {
        throw Context.reportRuntimeError("The function call fireToDB requires 2 arguments.");
    }
    return oRC;
}

From source file:com.akretion.kettle.steps.terminatooor.ScriptValuesAddedFunctions.java

public static Object fireToDB(ScriptEngine actualContext, Bindings actualObject, Object[] ArgList,
        Object FunctionContext) {

    Object oRC = new Object();
    if (ArgList.length == 2) {
        try {//from ww w  .j  a v  a 2  s.c  o m
            Object scmO = actualObject.get("_step_");
            ScriptValuesMod scm = (ScriptValuesMod) scmO;
            String strDBName = (String) ArgList[0];
            String strSQL = (String) ArgList[1];
            DatabaseMeta ci = DatabaseMeta.findDatabase(scm.getTransMeta().getDatabases(), strDBName);
            if (ci == null)
                throw new RuntimeException("Database connection not found: " + strDBName);
            ci.shareVariablesWith(scm);

            Database db = new Database(scm, ci);
            db.setQueryLimit(0);
            try {
                if (scm.getTransMeta().isUsingUniqueConnections()) {
                    synchronized (scm.getTrans()) {
                        db.connect(scm.getTrans().getThreadName(), scm.getPartitionID());
                    }
                } else {
                    db.connect(scm.getPartitionID());
                }

                ResultSet rs = db.openQuery(strSQL);
                ResultSetMetaData resultSetMetaData = rs.getMetaData();
                int columnCount = resultSetMetaData.getColumnCount();
                if (rs != null) {
                    List<Object[]> list = new ArrayList<Object[]>();
                    while (rs.next()) {
                        Object[] objRow = new Object[columnCount];
                        for (int i = 0; i < columnCount; i++) {
                            objRow[i] = rs.getObject(i + 1);
                        }
                        list.add(objRow);
                    }
                    Object[][] resultArr = new Object[list.size()][];
                    list.toArray(resultArr);
                    db.disconnect();
                    return resultArr;
                }
            } catch (Exception er) {
                throw new RuntimeException(er.toString());
            }
        } catch (Exception e) {
            throw new RuntimeException(e.toString());
        }
    } else {
        throw new RuntimeException("The function call fireToDB requires 2 arguments.");
    }
    return oRC;
}

From source file:com.glaf.core.jdbc.QueryHelper.java

@SuppressWarnings("unchecked")
public int getTotal(Connection conn, String sql, Map<String, Object> paramMap) {
    if (!DBUtils.isLegalQuerySql(sql)) {
        throw new RuntimeException(" SQL statement illegal ");
    }//  w w  w .j  a va 2s .c  o m
    int total = -1;
    PreparedStatement psmt = null;
    ResultSet rs = null;
    try {
        List<Object> values = null;
        if (paramMap != null) {
            SqlExecutor sqlExecutor = DBUtils.replaceSQL(sql, paramMap);
            sql = sqlExecutor.getSql();
            values = (List<Object>) sqlExecutor.getParameter();
        }

        sql = DBUtils.removeOrders(sql);

        logger.debug("sql:\n" + sql);
        logger.debug("values:" + values);

        psmt = conn.prepareStatement(sql);

        if (values != null && !values.isEmpty()) {
            JdbcUtils.fillStatement(psmt, values);
        }

        rs = psmt.executeQuery();
        if (rs.next()) {
            Object object = rs.getObject(1);
            if (object != null) {
                if (object instanceof Integer) {
                    Integer iCount = (Integer) object;
                    total = iCount.intValue();
                } else if (object instanceof Long) {
                    Long iCount = (Long) object;
                    total = iCount.intValue();
                } else if (object instanceof BigDecimal) {
                    BigDecimal bg = (BigDecimal) object;
                    total = bg.intValue();
                } else if (object instanceof BigInteger) {
                    BigInteger bi = (BigInteger) object;
                    total = bi.intValue();
                } else {
                    String x = object.toString();
                    if (StringUtils.isNotEmpty(x)) {
                        total = Integer.parseInt(x);
                    }
                }
            }
        }
    } catch (Exception ex) {
        logger.error(ex);
        ex.printStackTrace();
        throw new RuntimeException(ex);
    } finally {
        JdbcUtils.close(psmt);
        JdbcUtils.close(rs);
    }

    return total;
}