Example usage for java.sql ResultSetMetaData getColumnLabel

List of usage examples for java.sql ResultSetMetaData getColumnLabel

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. ResultSetMetaData
  6. getColumnLabel

Introduction

In this page you can find the example usage for java.sql ResultSetMetaData getColumnLabel.

Prototype

String getColumnLabel(int column) throws SQLException;

Source Link

Document

Gets the designated column's suggested title for use in printouts and displays.

Usage

From source file:swp.bibjsf.persistence.Data.java

/**
 * Exports all rows in <code>table</code> to <code>out</code> in CSV format.
 *
 * @param out/*  w  ww.  j  av  a 2 s .  c  o  m*/
 *            the output stream
 * @param table
 *            the name of the table to be exported
 * @throws DataSourceException
 *             thrown in case of problems with the data source
 */
public void export(OutputStream out, final String table) throws DataSourceException {
    final String query = "SELECT * from " + table;
    try {
        Connection connection = dataSource.getConnection();
        try {
            logger.debug("export " + query);
            Statement stmt = connection.createStatement();
            try {
                ResultSet set = stmt.executeQuery(query);
                try {
                    final PrintStream printer = newPrintStream(out);
                    final SimpleDateFormat df = new SimpleDateFormat(DATEFORMAT);
                    final int numberOfColumns = set.getMetaData().getColumnCount();
                    { // print header row
                        ResultSetMetaData metaData = set.getMetaData();
                        for (int column = 1; column <= numberOfColumns; column++) {
                            printer.print(quote(metaData.getColumnLabel(column)));
                            if (column < numberOfColumns) {
                                printer.print(DEFAULT_SEPARATOR);
                            }
                        }
                        printer.println();
                    }
                    // print data rows
                    while (set.next()) {
                        for (int column = 1; column <= numberOfColumns; column++) {
                            Object value = set.getObject(column);
                            if (value != null) {
                                // null should appear as empty string
                                if (value instanceof Date) {
                                    printer.print(quote(df.format((Date) value)));
                                } else {
                                    printer.print(quote(value.toString()));
                                }
                            }
                            if (column < numberOfColumns) {
                                printer.print(DEFAULT_SEPARATOR);
                            }
                        }
                        printer.println();
                    }
                } finally {
                    set.close();
                }
            } finally {
                stmt.close();
            }
        } finally {
            connection.close();
        }
    } catch (SQLException e) {
        throw new DataSourceException(e.getLocalizedMessage());
    }
}

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

@SuppressWarnings("unchecked")
public void run() {
    logger.debug(taskId + "----------------execution-----------------");
    TransformTask task = transformTaskService.getTransformTask(taskId);
    if (task != null) {
        if (task.getStatus() == 9 || task.getRetryTimes() > 3) {
            return;
        }/*w ww  .  ja  v a 2  s. c om*/
        task.setStartTime(new java.util.Date());
        task.setRetryTimes(task.getRetryTimes() + 1);
        task.setStatus(1);
        transformTaskService.save(task);
    }

    List<TableModel> resultList = new java.util.ArrayList<TableModel>();
    Map<String, Object> singleDataMap = new HashMap<String, Object>();
    Connection conn = null;
    PreparedStatement psmt = null;
    ResultSet rs = null;
    ResultSetMetaData rsmd = null;
    boolean success = true;
    long start = System.currentTimeMillis();
    logger.debug("start:" + DateUtils.getDateTime(new java.util.Date()));
    try {
        Database database = getDatabaseService().getDatabaseById(queryDefinition.getDatabaseId());
        if (database != null) {
            conn = DBConnectionFactory.getConnection(database.getName());
        } else {
            conn = DBConnectionFactory.getConnection();
        }

        logger.debug("conn:" + conn.toString());

        String sql = queryDefinition.getSql();
        sql = QueryUtils.replaceSQLVars(sql);
        List<Object> values = null;
        if (paramMap != null) {
            SqlExecutor sqlExecutor = JdbcUtils.rebuildSQL(sql, paramMap);
            sql = sqlExecutor.getSql();
            values = (List<Object>) sqlExecutor.getParameter();
        }

        logger.debug("--------------execute query----------------------");
        logger.debug(queryDefinition.getTitle());

        logger.debug("::sql::" + sql);
        psmt = conn.prepareStatement(sql);

        if (values != null && !values.isEmpty()) {
            JdbcUtils.fillStatement(psmt, values);
            logger.debug("::values::" + values);
        }

        List<ColumnDefinition> columns = new java.util.ArrayList<ColumnDefinition>();

        rs = psmt.executeQuery();
        rsmd = rs.getMetaData();
        int count = rsmd.getColumnCount();
        for (int i = 1; i <= count; i++) {
            int sqlType = rsmd.getColumnType(i);
            ColumnDefinition column = new ColumnDefinition();
            column.setColumnName(rsmd.getColumnName(i));
            column.setColumnLabel(rsmd.getColumnLabel(i));
            column.setJavaType(FieldType.getJavaType(sqlType));
            column.setPrecision(rsmd.getPrecision(i));
            column.setScale(rsmd.getScale(i));
            columns.add(column);
        }

        Set<String> cols = new HashSet<String>();

        while (rs.next()) {
            int index = 0;
            TableModel rowModel = new TableModel();

            ColumnModel cell01 = new ColumnModel();
            cell01.setColumnName("ID");
            cell01.setType("String");
            rowModel.addColumn(cell01);
            rowModel.setIdColumn(cell01);
            cols.add(cell01.getColumnName());

            ColumnModel cell04 = new ColumnModel();
            cell04.setColumnName("AGGREGATIONKEY");
            cell04.setType("String");
            rowModel.addColumn(cell04);
            cols.add(cell04.getColumnName());

            Iterator<ColumnDefinition> iterator = columns.iterator();
            while (iterator.hasNext()) {
                ColumnDefinition column = iterator.next();
                /**
                 * ????
                 */
                if (cols.contains(column.getColumnName())) {
                    continue;
                }
                ColumnModel cell = new ColumnModel();
                String columnName = column.getColumnName();
                String javaType = column.getJavaType();
                cell.setColumnName(columnName);
                cell.setType(javaType);
                index = index + 1;
                if ("String".equals(javaType)) {
                    String value = rs.getString(columnName);
                    cell.setStringValue(value);
                    cell.setValue(value);
                } else if ("Integer".equals(javaType)) {
                    try {
                        Integer value = rs.getInt(columnName);
                        cell.setIntValue(value);
                        cell.setValue(value);
                    } catch (Exception e) {
                        String str = rs.getString(columnName);
                        logger.error("integer:" + str);
                        str = StringTools.replace(str, "$", "");
                        str = StringTools.replace(str, "", "");
                        str = StringTools.replace(str, ",", "");
                        NumberFormat fmt = NumberFormat.getInstance();
                        Number num = fmt.parse(str);
                        cell.setIntValue(num.intValue());
                        cell.setValue(cell.getIntValue());
                        logger.debug("?:" + num.intValue());
                    }
                } else if ("Long".equals(javaType)) {
                    try {
                        Long value = rs.getLong(columnName);
                        cell.setLongValue(value);
                        cell.setValue(value);
                    } catch (Exception e) {
                        String str = rs.getString(columnName);
                        logger.error("long:" + str);
                        str = StringTools.replace(str, "", "");
                        str = StringTools.replace(str, ",", "");
                        NumberFormat fmt = NumberFormat.getInstance();
                        Number num = fmt.parse(str);
                        cell.setLongValue(num.longValue());
                        cell.setValue(cell.getLongValue());
                        logger.debug("?:" + num.longValue());
                    }
                } else if ("Double".equals(javaType)) {
                    try {
                        Double d = rs.getDouble(columnName);
                        cell.setDoubleValue(d);
                        cell.setValue(d);
                    } catch (Exception e) {
                        String str = rs.getString(columnName);
                        logger.error("double:" + str);
                        str = StringTools.replace(str, "", "");
                        str = StringTools.replace(str, ",", "");
                        NumberFormat fmt = NumberFormat.getInstance();
                        Number num = fmt.parse(str);
                        cell.setDoubleValue(num.doubleValue());
                        cell.setValue(cell.getDoubleValue());
                        logger.debug("?:" + num.doubleValue());
                    }
                } else if ("Boolean".equals(javaType)) {
                    Boolean value = rs.getBoolean(columnName);
                    cell.setBooleanValue(value);
                    cell.setValue(value);
                } else if ("Date".equals(javaType)) {
                    Date value = rs.getTimestamp(columnName);
                    cell.setDateValue(value);
                    cell.setValue(value);
                } else {
                    String value = rs.getString(columnName);
                    cell.setStringValue(value);
                    cell.setValue(value);
                }
                rowModel.addColumn(cell);
                if (resultList.isEmpty()) {
                    singleDataMap.put(column.getColumnLabel(), cell.getValue());
                }
            }
            resultList.add(rowModel);
        }

        logger.debug("--------------------resultList size:" + resultList.size());

    } catch (Exception ex) {
        success = false;
        ex.printStackTrace();
        logger.error(ex);
        throw new RuntimeException(ex);
    } finally {
        JdbcUtils.close(rs);
        JdbcUtils.close(psmt);
        JdbcUtils.close(conn);
        if (!success) {
            if (task != null) {
                task.setStatus(2);
                transformTaskService.save(task);
            }
        }
    }

    logger.debug("--------------execute mybatis save----------------------");

    try {

        if (!StringUtils.equalsIgnoreCase(queryDefinition.getRotatingFlag(), "R2C")) {
            TransformTable tbl = new TransformTable();
            tbl.createOrAlterTable(tableDefinition);
        }

        List<ColumnDefinition> columns = DBUtils.getColumnDefinitions(tableDefinition.getTableName());
        if (columns != null && !columns.isEmpty()) {
            tableDefinition.setColumns(columns);
        }

        if (resultList != null && !resultList.isEmpty() && tableDefinition.getTableName() != null
                && tableDefinition.getAggregationKeys() != null) {
            logger.debug("RotatingFlag:" + queryDefinition.getRotatingFlag());
            logger.debug("RotatingColumn:" + queryDefinition.getRotatingColumn());
            /**
             * ????
             */
            if (StringUtils.equalsIgnoreCase(queryDefinition.getRotatingFlag(), "R2C")
                    && StringUtils.isNotEmpty(queryDefinition.getRotatingColumn()) && resultList.size() == 1) {

                logger.debug("?dataMap?:" + singleDataMap);
                logger.debug("AggregationKeys:" + tableDefinition.getAggregationKeys());
                ColumnDefinition idField = columnMap.get(tableDefinition.getAggregationKeys().toLowerCase());
                ColumnDefinition field = columnMap.get(queryDefinition.getRotatingColumn().toLowerCase());
                logger.debug("idField:" + idField);
                logger.debug("field:" + field);
                if (idField != null && field != null) {
                    String javaType = field.getJavaType();
                    List<TableModel> list = new ArrayList<TableModel>();
                    Set<Entry<String, Object>> entrySet = singleDataMap.entrySet();
                    for (Entry<String, Object> entry : entrySet) {
                        String key = entry.getKey();
                        Object value = entry.getValue();
                        if (key == null || value == null) {
                            continue;
                        }
                        TableModel tableModel = new TableModel();
                        tableModel.setTableName(queryDefinition.getTargetTableName());
                        ColumnModel cell = new ColumnModel();
                        cell.setColumnName(queryDefinition.getRotatingColumn());
                        cell.setType(javaType);

                        // logger.debug(cell.getColumnName()+"->"+javaType);

                        if ("String".equals(javaType)) {
                            cell.setStringValue(ParamUtils.getString(singleDataMap, key));
                            cell.setValue(cell.getStringValue());
                        } else if ("Integer".equals(javaType)) {
                            cell.setIntValue(ParamUtils.getInt(singleDataMap, key));
                            cell.setValue(cell.getIntValue());
                        } else if ("Long".equals(javaType)) {
                            cell.setLongValue(ParamUtils.getLong(singleDataMap, key));
                            cell.setValue(cell.getLongValue());
                        } else if ("Double".equals(javaType)) {
                            cell.setDoubleValue(ParamUtils.getDouble(singleDataMap, key));
                            cell.setValue(cell.getDoubleValue());
                        } else if ("Date".equals(javaType)) {
                            cell.setDateValue(ParamUtils.getDate(singleDataMap, key));
                            cell.setValue(cell.getDateValue());
                        } else {
                            cell.setValue(value);
                        }

                        tableModel.addColumn(cell);

                        ColumnModel idColumn = new ColumnModel();
                        idColumn.setColumnName(tableDefinition.getAggregationKeys());
                        idColumn.setJavaType(idField.getJavaType());
                        idColumn.setValue(key);
                        tableModel.setIdColumn(idColumn);
                        list.add(tableModel);
                    }
                    logger.debug("update datalist:" + list);
                    tableDataService.updateTableData(list);
                }
            } else {
                tableDataService.saveAll(tableDefinition, null, resultList);
            }
        }

        resultList.clear();
        resultList = null;

        long time = System.currentTimeMillis() - start;

        if (task != null) {
            task.setEndTime(new java.util.Date());
            task.setStatus(9);
            task.setDuration(time);
        }
        logger.debug("execute time(ms)--------------------------" + time);
    } catch (Exception ex) {
        if (task != null) {
            task.setStatus(2);
        }
        ex.printStackTrace();
        logger.error(ex);
        throw new RuntimeException(ex);
    } finally {
        if (task != null) {
            transformTaskService.save(task);
            if (task.getStatus() != 9) {
                this.run();
            }
        }
    }
}

From source file:com.github.woonsan.jdbc.jcr.impl.JcrJdbcResultSetTest.java

@Test
public void testGetMetaData() throws Exception {
    Statement statement = getConnection().createStatement();
    ResultSet rs = statement.executeQuery(SQL_EMPS);
    ResultSetMetaData metaData = rs.getMetaData();

    // When 'sql' query used, jcr adds 'jcr:path' and 'jcr:score' columns automatically.
    assertEquals(5 + Constants.META_COLUMNS.size(), metaData.getColumnCount());

    assertEquals("empno", metaData.getColumnName(1));
    assertEquals("ename", metaData.getColumnName(2));
    assertEquals("salary", metaData.getColumnName(3));
    assertEquals("hiredate", metaData.getColumnName(4));

    assertEquals("empno", metaData.getColumnLabel(1));
    assertEquals("ename", metaData.getColumnLabel(2));
    assertEquals("salary", metaData.getColumnLabel(3));
    assertEquals("hiredate", metaData.getColumnLabel(4));

    for (int i = 1; i <= metaData.getColumnCount(); i++) {
        assertFalse(metaData.isAutoIncrement(i));
        assertTrue(metaData.isCaseSensitive(i));
        assertTrue(metaData.isSearchable(i));
        assertTrue(metaData.isReadOnly(i));
        assertFalse(metaData.isWritable(i));
        assertFalse(metaData.isDefinitelyWritable(i));
    }// w  ww . j  a v  a  2 s .co  m

    rs.close();
    statement.close();
}

From source file:swp.bibjsf.persistence.Data.java

/**
 * Retrieves the column information from <code>table</code>.
 *
 * @param table/*from  w  w w . j  av a2 s . co m*/
 *            the name of the table whose columns need to be known
 * @return descriptors for each table column
 * @throws DataSourceException
 *             thrown in case of problems with the data source
 */
private ColumnDescriptor[] getColumns(String table) throws DataSourceException {
    final String query = "SELECT * from " + table;
    try {
        Connection connection = dataSource.getConnection();
        try {
            logger.debug("getColumns " + query);
            Statement stmt = connection.createStatement();
            try {
                ResultSet set = stmt.executeQuery(query);
                try {
                    final int numberOfColumns = set.getMetaData().getColumnCount();
                    ColumnDescriptor[] result = new ColumnDescriptor[numberOfColumns];
                    { // get columns
                        ResultSetMetaData metaData = set.getMetaData();
                        for (int column = 1; column <= numberOfColumns; column++) {
                            result[column - 1] = new ColumnDescriptor();
                            result[column - 1].type = metaData.getColumnType(column);
                            result[column - 1].label = metaData.getColumnLabel(column);
                        }
                    }
                    return result;
                } finally {
                    set.close();
                }
            } finally {
                stmt.close();
            }
        } finally {
            connection.close();
        }
    } catch (SQLException e) {
        throw new DataSourceException(e.getLocalizedMessage());
    }
}

From source file:org.dbist.dml.impl.DmlJdbc.java

@SuppressWarnings("unchecked")
private <T> T newInstance(ResultSet rs, Class<T> clazz, Table table) throws SQLException {
    if (ValueUtils.isPrimitive(clazz))
        return (T) toRequiredType(rs, 1, clazz);

    ResultSetMetaData metadata = rs.getMetaData();
    Map<String, Field> fieldCache;
    Map<String, Field> subFieldCache;
    if (classFieldCache.containsKey(clazz)) {
        fieldCache = classFieldCache.get(clazz);
        subFieldCache = classSubFieldCache.get(clazz);
    } else {//from www .  j  a  va  2 s . c  o m
        fieldCache = new ConcurrentHashMap<String, Field>();
        classFieldCache.put(clazz, fieldCache);
        subFieldCache = null;
    }

    T data;
    try {
        data = newInstance(clazz);
    } catch (InstantiationException e) {
        throw new DbistRuntimeException(e);
    } catch (IllegalAccessException e) {
        throw new DbistRuntimeException(e);
    }
    if (data instanceof Map) {
        Map<String, Object> map = (Map<String, Object>) data;
        for (int i = 0; i < metadata.getColumnCount();) {
            i++;
            String name = metadata.getColumnLabel(i);
            switch (columnAliasRule) {
            case 0: {
                break;
            }
            case 1: {
                name = name.toUpperCase();
                break;
            }
            case 2: {
                name = name.toLowerCase();
                break;
            }
            case 3: {
                name = ValueUtils.toCamelCase(name, '_');
                break;
            }
            }
            map.put(name, toRequiredType(rs, i, null));
        }
    } else {
        for (int i = 0; i < metadata.getColumnCount();) {
            i++;
            String name = metadata.getColumnLabel(i);
            Field field = null;
            Field subField = null;
            if (fieldCache.containsKey(name)) {
                field = fieldCache.get(name);
                subField = subFieldCache == null ? null : subFieldCache.get(name);
            } else {
                field = getField(clazz, table, name);

                if (field == null && name.contains("__")) {
                    int index = name.indexOf("__");
                    String fieldName = name.substring(0, index);
                    field = getField(clazz, table, fieldName);
                    if (field != null) {
                        String subFieldName = name.substring(index + 2);
                        Class<?> subClass = field.getType();
                        Table subTable = getTable(subClass);
                        subField = getField(subClass, subTable, subFieldName);
                        if (subField == null) {
                            field = null;
                        } else {
                            if (subFieldCache == null) {
                                subFieldCache = new ConcurrentHashMap<String, Field>();
                                classSubFieldCache.put(clazz, subFieldCache);
                            }
                            subFieldCache.put(name, subField);
                        }
                    }
                }

                fieldCache.put(name, field == null ? ReflectionUtils.NULL_FIELD : field);
            }
            if (field == null || ReflectionUtils.NULL_FIELD.equals(field))
                continue;
            setFieldValue(rs, i, data, field, subField);
        }
    }
    return data;
}

From source file:org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.ResultSetTableModelFactory.java

public static AttributeMap<Object> collectData(final ResultSetMetaData rsmd, final int column,
        final String name) throws SQLException {
    AttributeMap<Object> metaData = new AttributeMap<Object>();
    metaData.setAttribute(MetaAttributeNames.Core.NAMESPACE, MetaAttributeNames.Core.TYPE,
            TypeMapper.mapForColumn(rsmd, column));
    metaData.setAttribute(MetaAttributeNames.Core.NAMESPACE, MetaAttributeNames.Core.NAME, name);
    try {//from   w  w w .  j av  a2 s.c  om
        if (rsmd.isCurrency(column + 1)) {
            metaData.setAttribute(MetaAttributeNames.Numeric.NAMESPACE, MetaAttributeNames.Numeric.CURRENCY,
                    Boolean.TRUE);
        } else {
            metaData.setAttribute(MetaAttributeNames.Numeric.NAMESPACE, MetaAttributeNames.Numeric.CURRENCY,
                    Boolean.FALSE);
        }
    } catch (SQLException e) {
        logger.debug(
                "Error on ResultSetMetaData#isCurrency. Driver does not implement the JDBC specs correctly. ",
                e);
    }
    try {

        if (rsmd.isSigned(column + 1)) {
            metaData.setAttribute(MetaAttributeNames.Numeric.NAMESPACE, MetaAttributeNames.Numeric.SIGNED,
                    Boolean.TRUE);
        } else {
            metaData.setAttribute(MetaAttributeNames.Numeric.NAMESPACE, MetaAttributeNames.Numeric.SIGNED,
                    Boolean.FALSE);
        }
    } catch (SQLException e) {
        logger.debug(
                "Error on ResultSetMetaData#isSigned. Driver does not implement the JDBC specs correctly. ", e);
    }

    try {
        final String tableName = rsmd.getTableName(column + 1);
        if (tableName != null) {
            metaData.setAttribute(MetaAttributeNames.Database.NAMESPACE, MetaAttributeNames.Database.TABLE,
                    tableName);
        }
    } catch (SQLException e) {
        logger.debug(
                "Error on ResultSetMetaData#getTableName. Driver does not implement the JDBC specs correctly. ",
                e);
    }

    try {
        final String schemaName = rsmd.getSchemaName(column + 1);
        if (schemaName != null) {
            metaData.setAttribute(MetaAttributeNames.Database.NAMESPACE, MetaAttributeNames.Database.SCHEMA,
                    schemaName);
        }
    } catch (SQLException e) {
        logger.debug(
                "Error on ResultSetMetaData#getSchemaName. Driver does not implement the JDBC specs correctly. ",
                e);
    }

    try {
        final String catalogName = rsmd.getCatalogName(column + 1);
        if (catalogName != null) {
            metaData.setAttribute(MetaAttributeNames.Database.NAMESPACE, MetaAttributeNames.Database.CATALOG,
                    catalogName);
        }
    } catch (SQLException e) {
        logger.debug(
                "Error on ResultSetMetaData#getTableName. Driver does not implement the JDBC specs correctly. ",
                e);
    }

    try {
        final String label = rsmd.getColumnLabel(column + 1);
        if (label != null) {
            metaData.setAttribute(MetaAttributeNames.Formatting.NAMESPACE, MetaAttributeNames.Formatting.LABEL,
                    label);
        }
    } catch (SQLException e) {
        logger.debug(
                "Error on ResultSetMetaData#getTableName. Driver does not implement the JDBC specs correctly. ",
                e);
    }

    try {
        final int displaySize = rsmd.getColumnDisplaySize(column + 1);
        metaData.setAttribute(MetaAttributeNames.Formatting.NAMESPACE,
                MetaAttributeNames.Formatting.DISPLAY_SIZE, IntegerCache.getInteger(displaySize));
    } catch (SQLException e) {
        logger.debug(
                "Error on ResultSetMetaData#getTableName. Driver does not implement the JDBC specs correctly. ",
                e);
    }

    try {
        final int precision = rsmd.getPrecision(column + 1);
        metaData.setAttribute(MetaAttributeNames.Numeric.NAMESPACE, MetaAttributeNames.Numeric.PRECISION,
                IntegerCache.getInteger(precision));
    } catch (SQLException e) {
        logger.debug(
                "Error on ResultSetMetaData#getTableName. Driver does not implement the JDBC specs correctly. ",
                e);
    }

    try {
        final int scale = rsmd.getScale(column + 1);
        metaData.setAttribute(MetaAttributeNames.Numeric.NAMESPACE, MetaAttributeNames.Numeric.SCALE,
                IntegerCache.getInteger(scale));
    } catch (SQLException e) {
        logger.debug(
                "Error on ResultSetMetaData#getTableName. Driver does not implement the JDBC specs correctly. ",
                e);
    }
    return metaData;
}

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

/**
 * @param conn//from   ww w  .j  a va  2  s .c  o  m
 *            ?
 * @param sqlExecutor
 *            ?
 * @param start
 *            0
 * @param pageSize
 *            ?
 * @return
 */
@SuppressWarnings("unchecked")
public List<Map<String, Object>> getResultList(Connection conn, SqlExecutor sqlExecutor, int start,
        int pageSize) {
    if (!DBUtils.isLegalQuerySql(sqlExecutor.getSql())) {
        throw new RuntimeException(" SQL statement illegal ");
    }
    List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
    String sql = sqlExecutor.getSql();
    PreparedStatement psmt = null;
    ResultSet rs = null;
    ResultSetMetaData rsmd = null;
    boolean supportsPhysicalPage = false;
    try {
        Dialect dialect = DBConfiguration.getDatabaseDialect(conn);
        if (dialect != null && dialect.supportsPhysicalPage()) {
            supportsPhysicalPage = true;
            sql = dialect.getLimitString(sql, start, pageSize);
            logger.debug("sql=" + sqlExecutor.getSql());
            logger.debug(">>sql=" + sql);
        }

        psmt = conn.prepareStatement(sql);
        if (sqlExecutor.getParameter() != null) {
            List<Object> values = (List<Object>) sqlExecutor.getParameter();
            JdbcUtils.fillStatement(psmt, values);
            logger.debug(">>values=" + values);
        }

        rs = psmt.executeQuery();

        if (conf.getBoolean("useMyBatisResultHandler", false)) {

            resultList = this.getResults(rs);

        } else {
            rsmd = rs.getMetaData();

            int count = rsmd.getColumnCount();
            List<ColumnDefinition> columns = new ArrayList<ColumnDefinition>();

            for (int i = 1; i <= count; i++) {
                int sqlType = rsmd.getColumnType(i);
                ColumnDefinition column = new ColumnDefinition();
                column.setIndex(i);
                column.setColumnName(rsmd.getColumnName(i));
                column.setColumnLabel(rsmd.getColumnLabel(i));
                column.setJavaType(FieldType.getJavaType(sqlType));
                column.setPrecision(rsmd.getPrecision(i));
                column.setScale(rsmd.getScale(i));
                if (column.getScale() == 0 && sqlType == Types.NUMERIC) {
                    column.setJavaType("Long");
                }
                column.setName(StringTools.camelStyle(column.getColumnLabel().toLowerCase()));
                columns.add(column);
            }

            if (!supportsPhysicalPage) {
                logger.debug("---------------------skipRows:" + start);
                this.skipRows(rs, start, pageSize);
            }

            logger.debug("---------------------columns:" + columns.size());
            logger.debug("---------------------start:" + start);
            logger.debug("---------------------pageSize:" + pageSize);
            // int index = 0;
            while (rs.next()) {
                // index++;
                // logger.debug("---------------------row index:" + index);

                Map<String, Object> rowMap = new HashMap<String, Object>();
                Iterator<ColumnDefinition> iterator = columns.iterator();
                while (iterator.hasNext()) {
                    ColumnDefinition column = iterator.next();
                    String columnLabel = column.getColumnLabel();
                    String columnName = column.getColumnName();
                    if (StringUtils.isEmpty(columnName)) {
                        columnName = column.getColumnLabel();
                    }
                    columnName = columnName.toLowerCase();
                    String javaType = column.getJavaType();

                    if ("String".equals(javaType)) {
                        String value = rs.getString(column.getIndex());
                        if (value != null) {
                            value = value.trim();
                            rowMap.put(columnName, value);
                            rowMap.put(columnLabel, rowMap.get(columnName));
                        }
                    } else if ("Integer".equals(javaType)) {
                        try {
                            Integer value = rs.getInt(column.getIndex());
                            rowMap.put(columnName, value);
                            rowMap.put(columnLabel, rowMap.get(columnName));
                        } catch (Exception e) {
                            String str = rs.getString(column.getIndex());
                            logger.error("integer:" + str);
                            str = StringTools.replace(str, "$", "");
                            str = StringTools.replace(str, "", "");
                            str = StringTools.replace(str, ",", "");
                            NumberFormat fmt = NumberFormat.getInstance();
                            Number num = fmt.parse(str);
                            rowMap.put(columnName, num.intValue());
                            rowMap.put(columnLabel, rowMap.get(columnName));
                            logger.debug("?:" + num.intValue());
                        }
                    } else if ("Long".equals(javaType)) {
                        try {
                            Long value = rs.getLong(column.getIndex());
                            rowMap.put(columnName, value);
                            rowMap.put(columnLabel, rowMap.get(columnName));
                        } catch (Exception e) {
                            String str = rs.getString(column.getIndex());
                            logger.error("long:" + str);
                            str = StringTools.replace(str, "$", "");
                            str = StringTools.replace(str, "", "");
                            str = StringTools.replace(str, ",", "");
                            NumberFormat fmt = NumberFormat.getInstance();
                            Number num = fmt.parse(str);
                            rowMap.put(columnName, num.longValue());
                            rowMap.put(columnLabel, rowMap.get(columnName));
                            logger.debug("?:" + num.longValue());
                        }
                    } else if ("Double".equals(javaType)) {
                        try {
                            Double d = rs.getDouble(column.getIndex());
                            rowMap.put(columnName, d);
                            rowMap.put(columnLabel, rowMap.get(columnName));
                        } catch (Exception e) {
                            String str = rs.getString(column.getIndex());
                            logger.error("double:" + str);
                            str = StringTools.replace(str, "$", "");
                            str = StringTools.replace(str, "", "");
                            str = StringTools.replace(str, ",", "");
                            NumberFormat fmt = NumberFormat.getInstance();
                            Number num = fmt.parse(str);
                            rowMap.put(columnName, num.doubleValue());
                            rowMap.put(columnLabel, rowMap.get(columnName));
                            logger.debug("?:" + num.doubleValue());
                        }
                    } else if ("Boolean".equals(javaType)) {
                        rowMap.put(columnName, rs.getBoolean(column.getIndex()));
                        rowMap.put(columnLabel, rowMap.get(columnName));
                    } else if ("Date".equals(javaType)) {
                        rowMap.put(columnName, rs.getTimestamp(column.getIndex()));
                        rowMap.put(columnLabel, rowMap.get(columnName));
                    } else if ("Blob".equals(javaType)) {

                    } else {
                        Object value = rs.getObject(column.getIndex());
                        if (value != null) {
                            if (value instanceof String) {
                                value = (String) value.toString().trim();
                            }
                            rowMap.put(columnName, value);
                            rowMap.put(columnLabel, rowMap.get(columnName));
                        }
                    }
                }
                resultList.add(rowMap);
            }
        }

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

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

/**
 * @param conn/*from  ww w  . j a  va2s .c o  m*/
 *            ?
 * @param start
 *            0
 * @param pageSize
 *            ?
 * @param sql
 *            ?
 * @param paramMap
 *            ?
 * @return
 */
@SuppressWarnings("unchecked")
public ResultModel getResultList(Connection conn, String sql, Map<String, Object> paramMap, int start,
        int pageSize) {
    if (!DBUtils.isLegalQuerySql(sql)) {
        throw new RuntimeException(" SQL statement illegal ");
    }
    ResultModel resultModel = new ResultModel();
    boolean supportsPhysicalPage = false;
    PreparedStatement psmt = null;
    ResultSetMetaData rsmd = null;
    ResultSet rs = null;
    Dialect dialect = null;
    try {
        dialect = DBConfiguration.getDatabaseDialect(conn);
        if (dialect != null && dialect.supportsPhysicalPage()) {
            logger.debug("sql=" + sql);
            supportsPhysicalPage = dialect.supportsPhysicalPage();
            sql = dialect.getLimitString(sql, start, pageSize);
            logger.debug(">>sql=" + sql);
        }

        List<Object> values = null;
        if (paramMap != null) {
            SqlExecutor sqlExecutor = DBUtils.replaceSQL(sql, paramMap);
            sql = sqlExecutor.getSql();
            values = (List<Object>) sqlExecutor.getParameter();
        }

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

        psmt = conn.prepareStatement(sql);

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

        List<ColumnDefinition> columns = new ArrayList<ColumnDefinition>();
        rs = psmt.executeQuery();
        rsmd = rs.getMetaData();
        int count = rsmd.getColumnCount();
        for (int i = 1; i <= count; i++) {
            int sqlType = rsmd.getColumnType(i);
            ColumnDefinition column = new ColumnDefinition();
            column.setIndex(i);
            column.setColumnName(rsmd.getColumnName(i));
            column.setColumnLabel(rsmd.getColumnLabel(i));
            column.setJavaType(FieldType.getJavaType(sqlType));
            column.setPrecision(rsmd.getPrecision(i));
            column.setScale(rsmd.getScale(i));
            if (column.getScale() == 0 && sqlType == Types.NUMERIC) {
                column.setJavaType("Long");
            }
            column.setName(StringTools.lower(StringTools.camelStyle(column.getColumnLabel())));
            columns.add(column);
        }

        resultModel.setHeaders(columns);

        if (!supportsPhysicalPage) {
            this.skipRows(rs, start);
        }

        int k = 0;
        while (rs.next() && k++ < pageSize) {
            int index = 0;
            RowModel rowModel = new RowModel();
            Iterator<ColumnDefinition> iterator = columns.iterator();
            while (iterator.hasNext()) {
                ColumnDefinition column = iterator.next();
                ColumnDefinition c = new ColumnDefinition();
                c.setColumnName(column.getColumnName());
                c.setColumnLabel(column.getColumnLabel());
                c.setName(column.getName());
                c.setJavaType(column.getJavaType());
                c.setPrecision(column.getPrecision());
                c.setScale(column.getScale());
                String javaType = column.getJavaType();
                index = index + 1;
                if ("String".equals(javaType)) {
                    String value = rs.getString(column.getIndex());
                    c.setValue(value);
                } else if ("Integer".equals(javaType)) {
                    try {
                        Integer value = rs.getInt(column.getIndex());
                        c.setValue(value);
                    } catch (Exception e) {
                        String str = rs.getString(column.getIndex());
                        str = StringTools.replace(str, "$", "");
                        str = StringTools.replace(str, "", "");
                        str = StringTools.replace(str, ",", "");
                        NumberFormat fmt = NumberFormat.getInstance();
                        Number num = fmt.parse(str);
                        c.setValue(num.intValue());
                    }
                } else if ("Long".equals(javaType)) {
                    try {
                        Long value = rs.getLong(column.getIndex());
                        c.setValue(value);
                    } catch (Exception e) {
                        String str = rs.getString(column.getIndex());
                        str = StringTools.replace(str, "$", "");
                        str = StringTools.replace(str, "", "");
                        str = StringTools.replace(str, ",", "");
                        NumberFormat fmt = NumberFormat.getInstance();
                        Number num = fmt.parse(str);
                        c.setValue(num.longValue());
                    }
                } else if ("Double".equals(javaType)) {
                    try {
                        Double value = rs.getDouble(column.getIndex());
                        c.setValue(value);
                    } catch (Exception e) {
                        String str = rs.getString(column.getIndex());
                        str = StringTools.replace(str, "$", "");
                        str = StringTools.replace(str, "", "");
                        str = StringTools.replace(str, ",", "");
                        NumberFormat fmt = NumberFormat.getInstance();
                        Number num = fmt.parse(str);
                        c.setValue(num.doubleValue());
                    }
                } else if ("Boolean".equals(javaType)) {
                    Boolean value = rs.getBoolean(column.getIndex());
                    c.setValue(value);
                } else if ("Date".equals(javaType)) {
                    Timestamp value = rs.getTimestamp(column.getIndex());
                    c.setValue(value);
                } else {
                    c.setValue(rs.getObject(column.getIndex()));
                }
                rowModel.addColumn(c);
            }
            resultModel.addRow(rowModel);
        }
    } catch (Exception ex) {
        logger.error(ex);
        ex.printStackTrace();
        throw new RuntimeException(ex);
    } finally {
        JdbcUtils.close(psmt);
        JdbcUtils.close(rs);
    }
    return resultModel;
}

From source file:org.sakaiproject.util.foorm.Foorm.java

/**
 * /*from ww  w  . jav a  2 s  .c om*/
 * @param table
 * @param formDefinition
 * @param vendor
 * @param md
 * @return
 */
public String[] formAdjustTable(String table, String[] formDefinition, String vendor, ResultSetMetaData md) {
    ArrayList<String> rv = new ArrayList<String>();

    for (String formField : formDefinition) {
        Properties info = parseFormString(formField);
        String field = info.getProperty("field", null);
        String type = info.getProperty("type", null);
        if ("header".equals(type))
            continue;
        String maxs = adjustMax(info.getProperty("maxlength", null));
        int maxlength = 0;
        if (maxs != null)
            maxlength = (new Integer(maxs)).intValue();
        if (maxlength < 1)
            maxlength = 80;

        String sqlType = null;
        boolean autoIncrement = false;
        int sqlLength = -1;
        boolean isNullable = false;
        try {
            for (int i = 1; i <= md.getColumnCount(); i++) {
                if (field.equalsIgnoreCase(md.getColumnLabel(i))) {
                    sqlLength = md.getColumnDisplaySize(i);
                    autoIncrement = md.isAutoIncrement(i);
                    sqlType = getSuperType(md.getColumnClassName(i));
                    isNullable = (md.isNullable(i) == ResultSetMetaData.columnNullable);
                    break;
                }
            }
        } catch (Exception e) {
            // ignore
        }

        logger.fine(field + " (" + maxlength + ") type=" + type);
        logger.fine(field + " (" + sqlLength + ") auto=" + autoIncrement + " type=" + sqlType + " null="
                + isNullable);

        //  If the field is not there...
        if (sqlType == null) {
            if ("oracle".equals(vendor)) {
                rv.add("ALTER TABLE " + table + " ADD ( " + formSql(formField, vendor) + " )");
            } else if ("mysql".equals(vendor)) {
                rv.add("ALTER TABLE " + table + " ADD " + formSql(formField, vendor));
            } else {
                rv.add("ALTER TABLE " + table + " ADD COLUMN " + formSql(formField, vendor));
            }
            continue;
        }

        String ff = formSql(formField, vendor);

        // BLTI-220, BLTI-238 - Required will be enforced in software - not the DB
        boolean shouldAlter = false;
        if ("key".equals(type)) {
            if (!NUMBER_TYPE.equals(sqlType))
                logger.severe(field + " must be Integer and Auto Increment");
        } else if ("autodate".equals(type)) {
        } else if ("url".equals(type) || "text".equals(type) || "textarea".equals(type)) {
            if ("oracle.sql.CLOB".equals(sqlType) || "oracle.jdbc.OracleClob".equals(sqlType))
                continue; // CLOBS large enough :)
            if (!STRING_TYPE.equals(sqlType)) {
                logger.severe(field + " must be String field");
                continue;
            }
            if (sqlLength < maxlength)
                shouldAlter = true;
            if (!isNullable)
                shouldAlter = true; // BLTI-220, BLTI-238
        } else if ("radio".equals(type) || "checkbox".equals(type) || "integer".equals(type)) {
            if (NUMBER_TYPE.equals(sqlType))
                continue;
            logger.severe(field + " must be Integer field");
        }

        if (shouldAlter) {
            if ("oracle".equals(vendor)) {
                rv.add("ALTER TABLE " + table + " MODIFY ( " + ff + " )");
            } else if ("mysql".equals(vendor)) {
                rv.add("ALTER TABLE " + table + " MODIFY " + ff);
            } else {
                rv.add("ALTER TABLE " + table + " ALTER COLUMN " + ff);
            }
        }
    }

    return rv.toArray(new String[rv.size()]);
}

From source file:it.fub.jardin.server.DbUtils.java

public void notifyChanges(MailUtility mailUtility, final Integer resultsetId,
        final List<BaseModelData> newItemList, String operazione, String username)
        throws SQLException, HiddenException {
    Integer id_table = 0;/*from   w ww.  ja v a 2 s .  co  m*/
    String mitt = mailUtility.getMailSmtpSender();

    Connection connection = this.dbConnectionHandler.getConn();

    String query = "SELECT address_statement, data_statement, link_id, name FROM " + T_NOTIFY
            + " WHERE id_resultset = '" + resultsetId + "'";

    JardinLogger.debug(username, "query: " + query);

    ResultSet result = doQuery(connection, query);
    while (result.next()) {
        String testo = "";
        String address_statement = result.getString(1);
        String data_statement = result.getString(2);
        String bmdid = result.getString(3);
        String oggetto = result.getString(4);
        // JardinLogger.debug(username, "bmdid " + bmdid);

        for (BaseModelData record : newItemList) {
            // System.out.println("idrecord:"+record.get(bmdid)+":idrecord");
            if (record.get(bmdid) != null && record.get(bmdid) != "" && record.get(bmdid) != " "
                    && record.get(bmdid) != "\"\"") {
                id_table = Integer.valueOf(record.get(bmdid).toString());
                PreparedStatement psData = (PreparedStatement) connection.prepareStatement(data_statement);
                psData.setInt(1, id_table);
                ResultSet resultData = psData.executeQuery();
                while (resultData.next()) {
                    ResultSetMetaData md = resultData.getMetaData();
                    int count = md.getColumnCount();
                    for (int i = 1; i <= count; i++) {
                        testo += md.getColumnLabel(i) + ": " + resultData.getString(i) + "\n";
                    }
                    //            JardinLogger.debug(username, "\nmessaggio:\n" + testo);
                    testo += "\n";
                }

            } else {

                // gestire notifica per inserimento righe nel db
                testo += "nuovo record\n";
                Iterator itr = record.getPropertyNames().iterator();
                while (itr.hasNext()) {
                    String key = itr.next().toString();
                    testo += key + ": " + record.get(key) + "\n";
                    // System.out.print(key + ": " + record.get(key) + "\n");
                }
                testo += "\n\n";
                //          System.out.println(testo);
                JardinLogger.error(username, "Notifica non inviata perch  un inserimento!");
            }
        }

        PreparedStatement ps = (PreparedStatement) connection.prepareStatement(address_statement);
        // ps.setInt(1, id_table);
        ResultSet resultAddress = ps.executeQuery();
        while (resultAddress.next()) {
            JardinLogger.info(username, "Sending notification mail to: " + resultAddress.getString(1));
            if (!(resultAddress.getString(1) == null)) {
                try {
                    mailUtility.sendMail(resultAddress.getString(1), mitt, oggetto + " - " + operazione, testo);

                } catch (MessagingException e) {
                    e.printStackTrace();
                    JardinLogger.error(username, "Invio non riuscito!");
                    JardinLogger.error(username, "MessagingException: " + e.toString());
                    // Log.info(e.toString());
                }
                JardinLogger.info(username, "Invio riuscito!");
            } else {
                JardinLogger.error(username, "Errore invio mail: Mail non valida!");
            }
        }

    }
}