Example usage for java.sql ResultSet getBigDecimal

List of usage examples for java.sql ResultSet getBigDecimal

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

Introduction

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

Prototype

BigDecimal getBigDecimal(String columnLabel) throws SQLException;

Source Link

Document

Retrieves the value of the designated column in the current row of this ResultSet object as a java.math.BigDecimal with full precision.

Usage

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

@SuppressWarnings("deprecation")
private void assertNonExistingColumn(final ResultSet rs) throws Exception {
    int nonExistingColIndex = Integer.MAX_VALUE;
    String nonExistingColName = "col" + nonExistingColIndex;

    try {/*  w ww .  ja  v  a  2 s  .  c  o m*/
        rs.getString(nonExistingColIndex);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getString(nonExistingColName);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getBoolean(nonExistingColIndex);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getBoolean(nonExistingColName);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getByte(nonExistingColIndex);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getByte(nonExistingColName);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getShort(nonExistingColIndex);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getShort(nonExistingColName);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getInt(nonExistingColIndex);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getInt(nonExistingColName);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getLong(nonExistingColIndex);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getLong(nonExistingColName);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getFloat(nonExistingColIndex);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getFloat(nonExistingColName);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getDouble(nonExistingColIndex);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getDouble(nonExistingColName);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getBigDecimal(nonExistingColIndex);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getBigDecimal(nonExistingColName);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getBigDecimal(nonExistingColIndex, 1);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getBigDecimal(nonExistingColName, 1);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getBytes(nonExistingColIndex);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getBytes(nonExistingColName);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getDate(nonExistingColIndex);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getDate(nonExistingColName);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getDate(nonExistingColIndex, null);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getDate(nonExistingColName, null);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getTime(nonExistingColIndex);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getTime(nonExistingColName);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getTime(nonExistingColIndex, null);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getTime(nonExistingColName, null);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getTimestamp(nonExistingColIndex);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getTimestamp(nonExistingColName);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getTimestamp(nonExistingColIndex, null);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getTimestamp(nonExistingColName, null);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getAsciiStream(nonExistingColIndex);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getAsciiStream(nonExistingColName);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getUnicodeStream(nonExistingColIndex);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getUnicodeStream(nonExistingColName);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getBinaryStream(nonExistingColIndex);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getBinaryStream(nonExistingColName);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getCharacterStream(nonExistingColIndex);
        fail();
    } catch (SQLException ignore) {
    }

    try {
        rs.getCharacterStream(nonExistingColName);
        fail();
    } catch (SQLException ignore) {
    }

}

From source file:org.wso2.carbon.dataservices.core.description.query.SQLQuery.java

private DataEntry getDataEntryFromRS(ResultSet rs) throws SQLException {
    DataEntry dataEntry = new DataEntry();
    ResultSetMetaData metaData = rs.getMetaData();
    int columnCount = metaData.getColumnCount();
    int columnType;
    String value;//from  w w w.  j av a  2  s  .  c  o  m
    ParamValue paramValue;
    Time sqlTime;
    Date sqlDate;
    Timestamp sqlTimestamp;
    Blob sqlBlob;
    BigDecimal bigDecimal;
    InputStream binInStream;
    boolean useColumnNumbers = this.isUsingColumnNumbers();
    for (int i = 1; i <= columnCount; i++) {
        /* retrieve values according to the column type */
        columnType = metaData.getColumnType(i);
        switch (columnType) {
        /* handle string types */
        case Types.VARCHAR:
            /* fall through */
        case Types.LONGVARCHAR:
            /* fall through */
        case Types.CHAR:
            /* fall through */
        case Types.CLOB:
            /* fall through */
        case Types.NCHAR:
            /* fall through */
        case Types.NCLOB:
            /* fall through */
        case Types.NVARCHAR:
            /* fall through */
        case Types.LONGNVARCHAR:
            value = rs.getString(i);
            paramValue = new ParamValue(value);
            break;
        /* handle numbers */
        case Types.INTEGER:
            /* fall through */
        case Types.TINYINT:
            /* fall through */
        case Types.SMALLINT:
            value = ConverterUtil.convertToString(rs.getInt(i));
            paramValue = new ParamValue(rs.wasNull() ? null : value);
            break;
        case Types.DOUBLE:
            value = ConverterUtil.convertToString(rs.getDouble(i));
            paramValue = new ParamValue(rs.wasNull() ? null : value);
            break;
        case Types.FLOAT:
            value = ConverterUtil.convertToString(rs.getFloat(i));
            paramValue = new ParamValue(rs.wasNull() ? null : value);
            break;
        case Types.BOOLEAN:
            /* fall through */
        case Types.BIT:
            value = ConverterUtil.convertToString(rs.getBoolean(i));
            paramValue = new ParamValue(rs.wasNull() ? null : value);
            break;
        case Types.DECIMAL:
            bigDecimal = rs.getBigDecimal(i);
            if (bigDecimal != null) {
                value = ConverterUtil.convertToString(bigDecimal);
            } else {
                value = null;
            }
            paramValue = new ParamValue(value);
            break;
        /* handle data/time values */
        case Types.TIME:
            /* handle time data type */
            sqlTime = rs.getTime(i);
            if (sqlTime != null) {
                value = this.convertToTimeString(sqlTime);
            } else {
                value = null;
            }
            paramValue = new ParamValue(value);
            break;
        case Types.DATE:
            /* handle date data type */
            sqlDate = rs.getDate(i);
            if (sqlDate != null) {
                value = ConverterUtil.convertToString(sqlDate);
            } else {
                value = null;
            }
            paramValue = new ParamValue(value);
            break;
        case Types.TIMESTAMP:
            sqlTimestamp = rs.getTimestamp(i, calendar);
            if (sqlTimestamp != null) {
                value = this.convertToTimestampString(sqlTimestamp);
            } else {
                value = null;
            }
            paramValue = new ParamValue(value);
            break;
        /* handle binary types */
        case Types.BLOB:
            sqlBlob = rs.getBlob(i);
            if (sqlBlob != null) {
                value = this.getBase64StringFromInputStream(sqlBlob.getBinaryStream());
            } else {
                value = null;
            }
            paramValue = new ParamValue(value);
            break;
        case Types.BINARY:
            /* fall through */
        case Types.LONGVARBINARY:
            /* fall through */
        case Types.VARBINARY:
            binInStream = rs.getBinaryStream(i);
            if (binInStream != null) {
                value = this.getBase64StringFromInputStream(binInStream);
            } else {
                value = null;
            }
            paramValue = new ParamValue(value);
            break;
        /* handling User Defined Types */
        case Types.STRUCT:
            Struct udt = (Struct) rs.getObject(i);
            paramValue = new ParamValue(udt);
            break;
        case Types.ARRAY:
            paramValue = new ParamValue(ParamValue.PARAM_VALUE_ARRAY);
            Array dataArray = (Array) rs.getObject(i);
            if (dataArray == null) {
                break;
            }
            paramValue = this.processSQLArray(dataArray, paramValue);
            break;
        case Types.NUMERIC:
            bigDecimal = rs.getBigDecimal(i);
            if (bigDecimal != null) {
                value = ConverterUtil.convertToString(bigDecimal);
            } else {
                value = null;
            }
            paramValue = new ParamValue(value);
            break;
        case Types.BIGINT:
            value = ConverterUtil.convertToString(rs.getLong(i));
            paramValue = new ParamValue(rs.wasNull() ? null : value);
            break;

        /* handle all other types as strings */
        default:
            value = rs.getString(i);
            paramValue = new ParamValue(value);
            break;
        }
        dataEntry.addValue(useColumnNumbers ? Integer.toString(i) : metaData.getColumnLabel(i), paramValue);
    }
    return dataEntry;
}

From source file:it.greenvulcano.gvesb.datahandling.dbo.utils.ExtendedRowSetBuilder.java

public int build(Document doc, String id, ResultSet rs, Set<Integer> keyField,
        Map<String, FieldFormatter> fieldNameToFormatter, Map<String, FieldFormatter> fieldIdToFormatter)
        throws Exception {
    if (rs == null) {
        return 0;
    }//from   w ww .j a  v a  2  s.  c o  m
    int rowCounter = 0;
    Element docRoot = doc.getDocumentElement();
    ResultSetMetaData metadata = rs.getMetaData();
    buildFormatterAndNamesArray(metadata, fieldNameToFormatter, fieldIdToFormatter);

    boolean noKey = ((keyField == null) || keyField.isEmpty());
    boolean isKeyCol = false;

    boolean isNull = false;
    Element data = null;
    Element row = null;
    Element col = null;
    Text text = null;
    String textVal = null;
    String precKey = null;
    String colKey = null;
    Map<String, Element> keyCols = new TreeMap<String, Element>();
    while (rs.next()) {
        if (rowCounter % 10 == 0) {
            ThreadUtils.checkInterrupted(getClass().getSimpleName(), name, logger);
        }
        row = parser.createElementNS(doc, AbstractDBO.ROW_NAME, NS);

        parser.setAttribute(row, AbstractDBO.ID_NAME, id);
        for (int j = 1; j <= metadata.getColumnCount(); j++) {
            FieldFormatter fF = fFormatters[j];
            String colName = colNames[j];

            isKeyCol = (!noKey && keyField.contains(new Integer(j)));
            isNull = false;
            col = parser.createElementNS(doc, colName, NS);
            if (isKeyCol) {
                parser.setAttribute(col, AbstractDBO.ID_NAME, String.valueOf(j));
            }
            switch (metadata.getColumnType(j)) {
            case Types.DATE:
            case Types.TIME:
            case Types.TIMESTAMP: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.TIMESTAMP_TYPE);
                Timestamp dateVal = rs.getTimestamp(j);
                isNull = dateVal == null;
                parser.setAttribute(col, AbstractDBO.NULL_NAME, String.valueOf(isNull));
                if (isNull) {
                    parser.setAttribute(col, AbstractDBO.FORMAT_NAME, AbstractDBO.DEFAULT_DATE_FORMAT);
                    textVal = "";
                } else {
                    if (fF != null) {
                        parser.setAttribute(col, AbstractDBO.FORMAT_NAME, fF.getDateFormat());
                        textVal = fF.formatDate(dateVal);
                    } else {
                        parser.setAttribute(col, AbstractDBO.FORMAT_NAME, AbstractDBO.DEFAULT_DATE_FORMAT);
                        textVal = dateFormatter.format(dateVal);
                    }
                }
            }
                break;
            case Types.DOUBLE:
            case Types.FLOAT:
            case Types.REAL: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.FLOAT_TYPE);
                float numVal = rs.getFloat(j);
                parser.setAttribute(col, AbstractDBO.NULL_NAME, "false");
                if (fF != null) {
                    parser.setAttribute(col, AbstractDBO.FORMAT_NAME, fF.getNumberFormat());
                    parser.setAttribute(col, AbstractDBO.GRP_SEPARATOR_NAME, fF.getGroupSeparator());
                    parser.setAttribute(col, AbstractDBO.DEC_SEPARATOR_NAME, fF.getDecSeparator());
                    textVal = fF.formatNumber(numVal);
                } else {
                    parser.setAttribute(col, AbstractDBO.FORMAT_NAME, numberFormat);
                    parser.setAttribute(col, AbstractDBO.GRP_SEPARATOR_NAME, groupSeparator);
                    parser.setAttribute(col, AbstractDBO.DEC_SEPARATOR_NAME, decSeparator);
                    textVal = numberFormatter.format(numVal);
                }
            }
                break;
            case Types.BIGINT:
            case Types.INTEGER:
            case Types.NUMERIC:
            case Types.SMALLINT:
            case Types.TINYINT: {
                BigDecimal bigdecimal = rs.getBigDecimal(j);
                isNull = bigdecimal == null;
                parser.setAttribute(col, AbstractDBO.NULL_NAME, String.valueOf(isNull));
                if (isNull) {
                    if (metadata.getScale(j) > 0) {
                        parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.FLOAT_TYPE);
                    } else {
                        parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.NUMERIC_TYPE);
                    }
                    textVal = "";
                } else {
                    if (fF != null) {
                        parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.FLOAT_TYPE);
                        parser.setAttribute(col, AbstractDBO.FORMAT_NAME, fF.getNumberFormat());
                        parser.setAttribute(col, AbstractDBO.GRP_SEPARATOR_NAME, fF.getGroupSeparator());
                        parser.setAttribute(col, AbstractDBO.DEC_SEPARATOR_NAME, fF.getDecSeparator());
                        textVal = fF.formatNumber(bigdecimal);
                    } else if (metadata.getScale(j) > 0) {
                        parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.FLOAT_TYPE);
                        parser.setAttribute(col, AbstractDBO.FORMAT_NAME, numberFormat);
                        parser.setAttribute(col, AbstractDBO.GRP_SEPARATOR_NAME, groupSeparator);
                        parser.setAttribute(col, AbstractDBO.DEC_SEPARATOR_NAME, decSeparator);
                        textVal = numberFormatter.format(bigdecimal);
                    } else {
                        parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.NUMERIC_TYPE);
                        textVal = bigdecimal.toString();
                    }
                }
            }
                break;
            case Types.NCHAR:
            case Types.NVARCHAR: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.NSTRING_TYPE);
                textVal = rs.getNString(j);
                isNull = textVal == null;
                parser.setAttribute(col, AbstractDBO.NULL_NAME, String.valueOf(isNull));
                if (isNull) {
                    textVal = "";
                }
            }
                break;
            case Types.CHAR:
            case Types.VARCHAR: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.STRING_TYPE);
                textVal = rs.getString(j);
                isNull = textVal == null;
                parser.setAttribute(col, AbstractDBO.NULL_NAME, String.valueOf(isNull));
                if (isNull) {
                    textVal = "";
                }
            }
                break;
            case Types.NCLOB: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.LONG_NSTRING_TYPE);
                NClob clob = rs.getNClob(j);
                isNull = clob == null;
                parser.setAttribute(col, AbstractDBO.NULL_NAME, String.valueOf(isNull));
                if (isNull) {
                    textVal = "";
                } else {
                    Reader is = clob.getCharacterStream();
                    StringWriter str = new StringWriter();

                    IOUtils.copy(is, str);
                    is.close();
                    textVal = str.toString();
                }
            }
                break;
            case Types.CLOB: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.LONG_STRING_TYPE);
                Clob clob = rs.getClob(j);
                isNull = clob == null;
                parser.setAttribute(col, AbstractDBO.NULL_NAME, String.valueOf(isNull));
                if (isNull) {
                    textVal = "";
                } else {
                    Reader is = clob.getCharacterStream();
                    StringWriter str = new StringWriter();

                    IOUtils.copy(is, str);
                    is.close();
                    textVal = str.toString();
                }
            }
                break;
            case Types.BLOB: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.BASE64_TYPE);
                Blob blob = rs.getBlob(j);
                isNull = blob == null;
                parser.setAttribute(col, AbstractDBO.NULL_NAME, String.valueOf(isNull));
                if (isNull) {
                    textVal = "";
                } else {
                    InputStream is = blob.getBinaryStream();
                    ByteArrayOutputStream baos = new ByteArrayOutputStream();
                    IOUtils.copy(is, baos);
                    is.close();
                    try {
                        byte[] buffer = Arrays.copyOf(baos.toByteArray(), (int) blob.length());
                        textVal = Base64.getEncoder().encodeToString(buffer);
                    } catch (SQLFeatureNotSupportedException exc) {
                        textVal = Base64.getEncoder().encodeToString(baos.toByteArray());
                    }
                }
            }
                break;
            default: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.DEFAULT_TYPE);
                textVal = rs.getString(j);
                isNull = textVal == null;
                parser.setAttribute(col, AbstractDBO.NULL_NAME, String.valueOf(isNull));
                if (isNull) {
                    textVal = "";
                }
            }
            }
            if (textVal != null) {
                text = doc.createTextNode(textVal);
                col.appendChild(text);
            }
            if (isKeyCol) {
                if (textVal != null) {
                    if (colKey == null) {
                        colKey = textVal;
                    } else {
                        colKey += "##" + textVal;
                    }
                    keyCols.put(String.valueOf(j), col);
                }
            } else {
                row.appendChild(col);
            }
        }
        if (noKey) {
            if (data == null) {
                data = parser.createElementNS(doc, AbstractDBO.DATA_NAME, NS);
                parser.setAttribute(data, AbstractDBO.ID_NAME, id);
            }
        } else if ((colKey != null) && !colKey.equals(precKey)) {
            if (data != null) {
                docRoot.appendChild(data);
            }
            data = parser.createElementNS(doc, AbstractDBO.DATA_NAME, NS);
            parser.setAttribute(data, AbstractDBO.ID_NAME, id);
            Element key = parser.createElementNS(doc, AbstractDBO.KEY_NAME, NS);
            data.appendChild(key);
            for (Entry<String, Element> keyColsEntry : keyCols.entrySet()) {
                key.appendChild(keyColsEntry.getValue());
            }
            keyCols.clear();
            precKey = colKey;
        }
        colKey = null;
        data.appendChild(row);
        rowCounter++;
    }
    if (data != null) {
        docRoot.appendChild(data);
    }

    return rowCounter;
}

From source file:com.mirth.connect.donkey.server.data.jdbc.JdbcDao.java

private Map<Long, Map<Integer, Map<String, Object>>> getMetaDataMaps(String channelId, List<Long> messageIds) {
    if (messageIds.size() > 1000) {
        throw new DonkeyDaoException("Only up to 1000 message Ids at a time are supported.");
    }/*from ww w  .  j  ava2 s .  co  m*/

    Map<Long, Map<Integer, Map<String, Object>>> metaDataMaps = new HashMap<Long, Map<Integer, Map<String, Object>>>();
    PreparedStatement statement = null;
    ResultSet resultSet = null;

    try {
        Map<String, Object> values = new HashMap<String, Object>();
        values.put("localChannelId", getLocalChannelId(channelId));
        values.put("messageIds", StringUtils.join(messageIds, ","));

        // do not cache this statement since metadata columns may be added/removed
        statement = connection.prepareStatement(querySource.getQuery("getMetaDataMapByMessageId", values));
        resultSet = statement.executeQuery();

        while (resultSet.next()) {
            Long messageId = resultSet.getLong("message_id");
            Integer metaDataId = resultSet.getInt("metadata_id");

            Map<Integer, Map<String, Object>> connectorMetaDataMap = metaDataMaps.get(messageId);
            if (connectorMetaDataMap == null) {
                connectorMetaDataMap = new HashMap<Integer, Map<String, Object>>();
                metaDataMaps.put(messageId, connectorMetaDataMap);
            }

            Map<String, Object> metaDataMap = connectorMetaDataMap.get(metaDataId);
            if (metaDataMap == null) {
                metaDataMap = new HashMap<String, Object>();
                connectorMetaDataMap.put(metaDataId, metaDataMap);
            }

            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            int columnCount = resultSetMetaData.getColumnCount();

            for (int i = 1; i <= columnCount; i++) {
                MetaDataColumnType metaDataColumnType = MetaDataColumnType
                        .fromSqlType(resultSetMetaData.getColumnType(i));
                Object value = null;

                switch (metaDataColumnType) {//@formatter:off
                case STRING:
                    value = resultSet.getString(i);
                    break;
                case NUMBER:
                    value = resultSet.getBigDecimal(i);
                    break;
                case BOOLEAN:
                    value = resultSet.getBoolean(i);
                    break;
                case TIMESTAMP:

                    Timestamp timestamp = resultSet.getTimestamp(i);
                    if (timestamp != null) {
                        value = Calendar.getInstance();
                        ((Calendar) value).setTimeInMillis(timestamp.getTime());
                    }
                    break;

                default:
                    throw new Exception("Unrecognized MetaDataColumnType");
                } //@formatter:on

                metaDataMap.put(resultSetMetaData.getColumnName(i).toUpperCase(), value);
            }
        }

        return metaDataMaps;
    } catch (Exception e) {
        throw new DonkeyDaoException(e);
    } finally {
        close(resultSet);
        close(statement);
    }
}

From source file:org.apache.hive.jdbc.TestJdbcDriver2.java

@Test
public void testDataTypes() throws Exception {
    Statement stmt = con.createStatement();

    ResultSet res = stmt.executeQuery("select * from " + dataTypeTableName + " order by c1");
    ResultSetMetaData meta = res.getMetaData();

    // row 1/*from  w  ww. jav  a2s  . c  om*/
    assertTrue(res.next());
    // skip the last (partitioning) column since it is always non-null
    for (int i = 1; i < meta.getColumnCount(); i++) {
        assertNull("Column " + i + " should be null", res.getObject(i));
    }
    // getXXX returns 0 for numeric types, false for boolean and null for other
    assertEquals(0, res.getInt(1));
    assertEquals(false, res.getBoolean(2));
    assertEquals(0d, res.getDouble(3), floatCompareDelta);
    assertEquals(null, res.getString(4));
    assertEquals(null, res.getString(5));
    assertEquals(null, res.getString(6));
    assertEquals(null, res.getString(7));
    assertEquals(null, res.getString(8));
    assertEquals(0, res.getByte(9));
    assertEquals(0, res.getShort(10));
    assertEquals(0f, res.getFloat(11), floatCompareDelta);
    assertEquals(0L, res.getLong(12));
    assertEquals(null, res.getString(13));
    assertEquals(null, res.getString(14));
    assertEquals(null, res.getString(15));
    assertEquals(null, res.getString(16));
    assertEquals(null, res.getString(17));
    assertEquals(null, res.getString(18));
    assertEquals(null, res.getString(19));
    assertEquals(null, res.getString(20));
    assertEquals(null, res.getDate(20));
    assertEquals(null, res.getString(21));
    assertEquals(null, res.getString(22));

    // row 2
    assertTrue(res.next());
    assertEquals(-1, res.getInt(1));
    assertEquals(false, res.getBoolean(2));
    assertEquals(-1.1d, res.getDouble(3), floatCompareDelta);
    assertEquals("", res.getString(4));
    assertEquals("[]", res.getString(5));
    assertEquals("{}", res.getString(6));
    assertEquals("{}", res.getString(7));
    assertEquals("{\"r\":null,\"s\":null,\"t\":null}", res.getString(8));
    assertEquals(-1, res.getByte(9));
    assertEquals(-1, res.getShort(10));
    assertEquals(-1.0f, res.getFloat(11), floatCompareDelta);
    assertEquals(-1, res.getLong(12));
    assertEquals("[]", res.getString(13));
    assertEquals("{}", res.getString(14));
    assertEquals("{\"r\":null,\"s\":null}", res.getString(15));
    assertEquals("[]", res.getString(16));
    assertEquals(null, res.getString(17));
    assertEquals(null, res.getTimestamp(17));
    assertEquals(null, res.getBigDecimal(18));
    assertEquals(null, res.getString(19));
    assertEquals(null, res.getString(20));
    assertEquals(null, res.getDate(20));
    assertEquals(null, res.getString(21));
    assertEquals(null, res.getString(22));
    assertEquals(null, res.getString(23));

    // row 3
    assertTrue(res.next());
    assertEquals(1, res.getInt(1));
    assertEquals(true, res.getBoolean(2));
    assertEquals(1.1d, res.getDouble(3), floatCompareDelta);
    assertEquals("1", res.getString(4));
    assertEquals("[1,2]", res.getString(5));
    assertEquals("{1:\"x\",2:\"y\"}", res.getString(6));
    assertEquals("{\"k\":\"v\"}", res.getString(7));
    assertEquals("{\"r\":\"a\",\"s\":9,\"t\":2.2}", res.getString(8));
    assertEquals(1, res.getByte(9));
    assertEquals(1, res.getShort(10));
    assertEquals(1.0f, res.getFloat(11), floatCompareDelta);
    assertEquals(1, res.getLong(12));
    assertEquals("[[\"a\",\"b\"],[\"c\",\"d\"]]", res.getString(13));
    assertEquals("{1:{11:12,13:14},2:{21:22}}", res.getString(14));
    assertEquals("{\"r\":1,\"s\":{\"a\":2,\"b\":\"x\"}}", res.getString(15));
    assertEquals("[{\"m\":{},\"n\":1},{\"m\":{\"a\":\"b\",\"c\":\"d\"},\"n\":2}]", res.getString(16));
    assertEquals("2012-04-22 09:00:00.123456789", res.getString(17));
    assertEquals("2012-04-22 09:00:00.123456789", res.getTimestamp(17).toString());
    assertEquals("123456789.0123456", res.getBigDecimal(18).toString());
    assertEquals("abcd", res.getString(19));
    assertEquals("2013-01-01", res.getString(20));
    assertEquals("2013-01-01", res.getDate(20).toString());
    assertEquals("abc123", res.getString(21));
    assertEquals("abc123         ", res.getString(22));

    byte[] bytes = "X'01FF'".getBytes("UTF-8");
    InputStream resultSetInputStream = res.getBinaryStream(23);
    int len = bytes.length;
    byte[] b = new byte[len];
    resultSetInputStream.read(b, 0, len);
    for (int i = 0; i < len; i++) {
        assertEquals(bytes[i], b[i]);
    }

    // test getBoolean rules on non-boolean columns
    assertEquals(true, res.getBoolean(1));
    assertEquals(true, res.getBoolean(4));

    // test case sensitivity
    assertFalse(meta.isCaseSensitive(1));
    assertFalse(meta.isCaseSensitive(2));
    assertFalse(meta.isCaseSensitive(3));
    assertTrue(meta.isCaseSensitive(4));

    // no more rows
    assertFalse(res.next());
}

From source file:it.greenvulcano.gvesb.datahandling.dbo.utils.StandardRowSetBuilder.java

public int build(Document doc, String id, ResultSet rs, Set<Integer> keyField,
        Map<String, FieldFormatter> fieldNameToFormatter, Map<String, FieldFormatter> fieldIdToFormatter)
        throws Exception {
    if (rs == null) {
        return 0;
    }//from w  ww . j  a  v  a 2 s.c o m
    int rowCounter = 0;
    Element docRoot = doc.getDocumentElement();
    ResultSetMetaData metadata = rs.getMetaData();
    FieldFormatter[] fFormatters = buildFormatterArray(metadata, fieldNameToFormatter, fieldIdToFormatter);

    boolean noKey = ((keyField == null) || keyField.isEmpty());

    //boolean isNull = false;
    Element data = null;
    Element row = null;
    Element col = null;
    Text text = null;
    String textVal = null;
    String precKey = null;
    String colKey = null;
    Map<String, String> keyAttr = new HashMap<String, String>();
    while (rs.next()) {
        if (rowCounter % 10 == 0) {
            ThreadUtils.checkInterrupted(getClass().getSimpleName(), name, logger);
        }
        row = parser.createElement(doc, AbstractDBO.ROW_NAME);

        parser.setAttribute(row, AbstractDBO.ID_NAME, id);
        for (int j = 1; j <= metadata.getColumnCount(); j++) {
            FieldFormatter fF = fFormatters[j];

            //isNull = false;
            col = parser.createElement(doc, AbstractDBO.COL_NAME);
            switch (metadata.getColumnType(j)) {
            case Types.DATE: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.DATE_TYPE);
                java.sql.Date dateVal = rs.getDate(j);
                textVal = processDateTime(col, fF, dateVal, AbstractDBO.DEFAULT_DATE_FORMAT);
            }
                break;
            case Types.TIME: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.TIME_TYPE);
                java.sql.Time dateVal = rs.getTime(j);
                textVal = processDateTime(col, fF, dateVal, AbstractDBO.DEFAULT_TIME_FORMAT);
            }
                break;
            case Types.TIMESTAMP: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.TIMESTAMP_TYPE);
                Timestamp dateVal = rs.getTimestamp(j);
                textVal = processDateTime(col, fF, dateVal, AbstractDBO.DEFAULT_DATE_FORMAT);
            }
                break;
            case Types.DOUBLE: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.FLOAT_TYPE);
                double numVal = rs.getDouble(j);
                textVal = processDouble(col, fF, numVal);
            }
                break;
            case Types.FLOAT:
            case Types.REAL: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.FLOAT_TYPE);
                float numVal = rs.getFloat(j);
                textVal = processDouble(col, fF, numVal);
            }
                break;
            case Types.BIGINT: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.BIGINT_TYPE);
                long numVal = rs.getLong(j);
                parser.setAttribute(col, AbstractDBO.NULL_NAME, "false");
                textVal = String.valueOf(numVal);
            }
                break;
            case Types.INTEGER: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.INTEGER_TYPE);
                int numVal = rs.getInt(j);
                parser.setAttribute(col, AbstractDBO.NULL_NAME, "false");
                textVal = String.valueOf(numVal);
            }
                break;
            case Types.SMALLINT:
            case Types.TINYINT: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.SMALLINT_TYPE);
                short numVal = rs.getShort(j);
                parser.setAttribute(col, AbstractDBO.NULL_NAME, "false");
                textVal = String.valueOf(numVal);
            }
                break;
            case Types.NUMERIC:
            case Types.DECIMAL: {
                BigDecimal bigdecimal = rs.getBigDecimal(j);
                boolean isNull = bigdecimal == null;
                parser.setAttribute(col, AbstractDBO.NULL_NAME, String.valueOf(isNull));
                if (isNull) {
                    if (metadata.getScale(j) > 0) {
                        parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.DECIMAL_TYPE);
                    } else {
                        parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.NUMERIC_TYPE);
                    }
                    textVal = "";
                } else {
                    if (fF != null) {
                        parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.DECIMAL_TYPE);
                        parser.setAttribute(col, AbstractDBO.FORMAT_NAME, fF.getNumberFormat());
                        parser.setAttribute(col, AbstractDBO.GRP_SEPARATOR_NAME, fF.getGroupSeparator());
                        parser.setAttribute(col, AbstractDBO.DEC_SEPARATOR_NAME, fF.getDecSeparator());
                        textVal = fF.formatNumber(bigdecimal);
                    } else if (metadata.getScale(j) > 0) {
                        parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.DECIMAL_TYPE);
                        parser.setAttribute(col, AbstractDBO.FORMAT_NAME, numberFormat);
                        parser.setAttribute(col, AbstractDBO.GRP_SEPARATOR_NAME, groupSeparator);
                        parser.setAttribute(col, AbstractDBO.DEC_SEPARATOR_NAME, decSeparator);
                        textVal = numberFormatter.format(bigdecimal);
                    } else {
                        parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.NUMERIC_TYPE);
                        textVal = bigdecimal.toString();
                    }
                }
            }
                break;
            case Types.BOOLEAN: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.BOOLEAN_TYPE);
                boolean bVal = rs.getBoolean(j);
                parser.setAttribute(col, AbstractDBO.NULL_NAME, "false");
                textVal = String.valueOf(bVal);
            }
                break;
            case Types.SQLXML: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.XML_TYPE);
                SQLXML xml = rs.getSQLXML(j);
                boolean isNull = xml == null;
                parser.setAttribute(col, AbstractDBO.NULL_NAME, String.valueOf(isNull));
                if (isNull) {
                    textVal = "";
                } else {
                    textVal = xml.getString();
                }
            }
                break;
            case Types.NCHAR:
            case Types.NVARCHAR: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.NSTRING_TYPE);
                textVal = rs.getNString(j);
                if (textVal == null) {
                    textVal = "";
                }
            }
                break;
            case Types.CHAR:
            case Types.VARCHAR: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.STRING_TYPE);
                textVal = rs.getString(j);
                boolean isNull = textVal == null;
                parser.setAttribute(col, AbstractDBO.NULL_NAME, String.valueOf(isNull));
                if (isNull) {
                    textVal = "";
                }
            }
                break;
            case Types.NCLOB: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.LONG_NSTRING_TYPE);
                NClob clob = rs.getNClob(j);
                if (clob != null) {
                    Reader is = clob.getCharacterStream();
                    StringWriter str = new StringWriter();

                    IOUtils.copy(is, str);
                    is.close();
                    textVal = str.toString();
                } else {
                    textVal = "";
                }
            }
                break;
            case Types.CLOB: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.LONG_STRING_TYPE);
                Clob clob = rs.getClob(j);
                if (clob != null) {
                    Reader is = clob.getCharacterStream();
                    StringWriter str = new StringWriter();

                    IOUtils.copy(is, str);
                    is.close();
                    textVal = str.toString();
                } else {
                    textVal = "";
                }
            }
                break;
            case Types.BLOB: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.BASE64_TYPE);
                Blob blob = rs.getBlob(j);
                boolean isNull = blob == null;
                parser.setAttribute(col, AbstractDBO.NULL_NAME, String.valueOf(isNull));
                if (isNull) {
                    textVal = "";
                } else {
                    InputStream is = blob.getBinaryStream();
                    ByteArrayOutputStream baos = new ByteArrayOutputStream();
                    IOUtils.copy(is, baos);
                    is.close();
                    try {
                        byte[] buffer = Arrays.copyOf(baos.toByteArray(), (int) blob.length());
                        textVal = Base64.getEncoder().encodeToString(buffer);
                    } catch (SQLFeatureNotSupportedException exc) {
                        textVal = Base64.getEncoder().encodeToString(baos.toByteArray());
                    }
                }
            }
                break;
            default: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.DEFAULT_TYPE);
                textVal = rs.getString(j);
                boolean isNull = textVal == null;
                parser.setAttribute(col, AbstractDBO.NULL_NAME, String.valueOf(isNull));
                if (isNull) {
                    textVal = "";
                }
            }
            }
            if (textVal != null) {
                text = doc.createTextNode(textVal);
                col.appendChild(text);
            }
            if (!noKey && keyField.contains(new Integer(j))) {
                if (textVal != null) {
                    if (colKey == null) {
                        colKey = textVal;
                    } else {
                        colKey += "##" + textVal;
                    }
                    keyAttr.put("key_" + j, textVal);
                }
            } else {
                row.appendChild(col);
            }
        }
        if (noKey) {
            if (data == null) {
                data = parser.createElement(doc, AbstractDBO.DATA_NAME);
                parser.setAttribute(data, AbstractDBO.ID_NAME, id);
            }
        } else if ((colKey != null) && !colKey.equals(precKey)) {
            if (data != null) {
                docRoot.appendChild(data);
            }
            data = parser.createElement(doc, AbstractDBO.DATA_NAME);
            parser.setAttribute(data, AbstractDBO.ID_NAME, id);
            for (Entry<String, String> keyAttrEntry : keyAttr.entrySet()) {
                parser.setAttribute(data, keyAttrEntry.getKey(), keyAttrEntry.getValue());
            }
            keyAttr.clear();
            precKey = colKey;
        }
        colKey = null;
        data.appendChild(row);
        rowCounter++;
    }
    if (data != null) {
        docRoot.appendChild(data);
    }

    return rowCounter;
}

From source file:axiom.objectmodel.db.NodeManager.java

/**
 *  Create a new Node from a ResultSet./*from   ww  w.  j  a  v a  2s  . c  om*/
 */
public Node createNode(DbMapping dbm, ResultSet rs, DbColumn[] columns, int offset)
        throws SQLException, IOException, ClassNotFoundException {
    HashMap propBuffer = new HashMap();
    String id = null;
    String name = null;
    String protoName = dbm.getTypeName();
    DbMapping dbmap = dbm;

    Node node = new Node();

    for (int i = 0; i < columns.length; i++) {
        // set prototype?
        if (columns[i].isPrototypeField()) {
            protoName = rs.getString(i + 1 + offset);

            if (protoName != null) {
                dbmap = getDbMapping(protoName);

                if (dbmap == null) {
                    // invalid prototype name!
                    app.logError(ErrorReporter.errorMsg(this.getClass(), "createNode")
                            + "Invalid prototype name: " + protoName + " - using default");
                    dbmap = dbm;
                    protoName = dbmap.getTypeName();
                }
            }
        }

        // set id?
        if (columns[i].isIdField()) {
            id = rs.getString(i + 1 + offset);
            // if id == null, the object doesn't actually exist - return null
            if (id == null) {
                return null;
            }
        }

        // set name?
        if (columns[i].isNameField()) {
            name = rs.getString(i + 1 + offset);
        }

        Property newprop = new Property(node);

        switch (columns[i].getType()) {
        case Types.BIT:
            newprop.setBooleanValue(rs.getBoolean(i + 1 + offset));

            break;

        case Types.TINYINT:
        case Types.BIGINT:
        case Types.SMALLINT:
        case Types.INTEGER:
            newprop.setIntegerValue(rs.getLong(i + 1 + offset));

            break;

        case Types.REAL:
        case Types.FLOAT:
        case Types.DOUBLE:
            newprop.setFloatValue(rs.getDouble(i + 1 + offset));

            break;

        case Types.DECIMAL:
        case Types.NUMERIC:

            BigDecimal num = rs.getBigDecimal(i + 1 + offset);

            if (num == null) {
                break;
            }

            if (num.scale() > 0) {
                newprop.setFloatValue(num.doubleValue());
            } else {
                newprop.setIntegerValue(num.longValue());
            }

            break;

        case Types.VARBINARY:
        case Types.BINARY:
            //                    newprop.setStringValue(rs.getString(i+1+offset));
            newprop.setJavaObjectValue(rs.getBytes(i + 1 + offset));

            break;

        case Types.LONGVARBINARY: {
            InputStream in = rs.getBinaryStream(i + 1 + offset);
            if (in == null) {
                break;
            }
            ByteArrayOutputStream bout = new ByteArrayOutputStream();
            byte[] buffer = new byte[2048];
            int read;
            while ((read = in.read(buffer)) > -1) {
                bout.write(buffer, 0, read);
            }
            newprop.setJavaObjectValue(bout.toByteArray());
        }

            break;
        case Types.LONGVARCHAR:
            try {
                newprop.setStringValue(rs.getString(i + 1 + offset));
            } catch (SQLException x) {
                Reader in = rs.getCharacterStream(i + 1 + offset);
                char[] buffer = new char[2048];
                int read = 0;
                int r;

                while ((r = in.read(buffer, read, buffer.length - read)) > -1) {
                    read += r;

                    if (read == buffer.length) {
                        // grow input buffer
                        char[] newBuffer = new char[buffer.length * 2];

                        System.arraycopy(buffer, 0, newBuffer, 0, buffer.length);
                        buffer = newBuffer;
                    }
                }

                newprop.setStringValue(new String(buffer, 0, read));
            }

            break;

        case Types.CHAR:
        case Types.VARCHAR:
        case Types.OTHER:
            newprop.setStringValue(rs.getString(i + 1 + offset));

            break;

        case Types.DATE:
        case Types.TIME:
        case Types.TIMESTAMP:
            newprop.setDateValue(rs.getTimestamp(i + 1 + offset));

            break;

        case Types.NULL:
            newprop.setStringValue(null);

            break;

        case Types.CLOB:
            Clob cl = rs.getClob(i + 1 + offset);
            if (cl == null) {
                newprop.setStringValue(null);
                break;
            }
            char[] c = new char[(int) cl.length()];
            Reader isr = cl.getCharacterStream();
            isr.read(c);
            newprop.setStringValue(String.copyValueOf(c));
            break;

        default:
            newprop.setStringValue(rs.getString(i + 1 + offset));

            break;
        }

        if (rs.wasNull()) {
            newprop.setStringValue(null);
        }

        propBuffer.put(columns[i].getName(), newprop);

        // mark property as clean, since it's fresh from the db
        newprop.dirty = false;
    }

    if (id == null) {
        return null;
    }

    Hashtable propMap = new Hashtable();
    DbColumn[] columns2 = dbmap.getColumns();
    for (int i = 0; i < columns2.length; i++) {
        Relation rel = columns2[i].getRelation();

        if (rel != null && (rel.reftype == Relation.PRIMITIVE || rel.reftype == Relation.REFERENCE)) {

            Property prop = (Property) propBuffer.get(columns2[i].getName());

            if (prop == null) {
                continue;
            }
            prop.setName(rel.propName);
            // if the property is a pointer to another node, change the property type to NODE
            if ((rel.reftype == Relation.REFERENCE) && rel.usesPrimaryKey()) {
                // FIXME: References to anything other than the primary key are not supported
                prop.convertToNodeReference(rel.otherType, this.app.getCurrentRequestEvaluator().getLayer());
            }
            propMap.put(rel.propName.toLowerCase(), prop);
        }
    }

    node.init(dbmap, id, name, protoName, propMap, safe);

    return node;
}

From source file:com.jd.survey.dao.survey.SurveyDAOImpl.java

/**
 * Reads a single survey page from the survey data table 
 *///from  w  w w.j a va 2  s.c  o  m
@Override
public SurveyPage getPage(final Survey survey, final SurveyDefinitionPage surveyDefinitionPage,
        final String dateFormat) {
    try {
        boolean hasDatabaseQuestions = false;
        StringBuilder stringBuilder = new StringBuilder();
        String pageVisibilityColumn = "p" + surveyDefinitionPage.getOrder() + "v";
        stringBuilder.append("select survey_id, " + pageVisibilityColumn + ", ");
        for (Question question : surveyDefinitionPage.getQuestions()) {
            int optionsCount = question.getOptions().size();
            int rowCount = question.getRowLabels().size();
            int columnCount = question.getColumnLabels().size();
            switch (question.getType()) {
            case YES_NO_DROPDOWN: //Yes No DropDown
                stringBuilder.append(" p" + surveyDefinitionPage.getOrder() + "q" + question.getOrder() + ",");
                hasDatabaseQuestions = true;
                break;
            case SHORT_TEXT_INPUT: //Short Text Input
                stringBuilder.append(" p" + surveyDefinitionPage.getOrder() + "q" + question.getOrder() + ",");
                hasDatabaseQuestions = true;
                break;
            case LONG_TEXT_INPUT: //Long Text Input
                stringBuilder.append(" p" + surveyDefinitionPage.getOrder() + "q" + question.getOrder() + ",");
                hasDatabaseQuestions = true;
                break;
            case HUGE_TEXT_INPUT: //Huge Text Input
                stringBuilder.append(" p" + surveyDefinitionPage.getOrder() + "q" + question.getOrder() + ",");
                hasDatabaseQuestions = true;
                break;
            case INTEGER_INPUT: //Integer Input
                stringBuilder.append(" p" + surveyDefinitionPage.getOrder() + "q" + question.getOrder() + ",");
                hasDatabaseQuestions = true;
                break;
            case CURRENCY_INPUT: //Currency Input
                stringBuilder.append(" p" + surveyDefinitionPage.getOrder() + "q" + question.getOrder() + ",");
                hasDatabaseQuestions = true;
                break;
            case DECIMAL_INPUT: //Decimal Input
                stringBuilder.append(" p" + surveyDefinitionPage.getOrder() + "q" + question.getOrder() + ",");
                hasDatabaseQuestions = true;
                break;
            case DATE_INPUT: //Date Input 
                stringBuilder.append(" p" + surveyDefinitionPage.getOrder() + "q" + question.getOrder() + ",");
                hasDatabaseQuestions = true;
                break;
            case SINGLE_CHOICE_DROP_DOWN: //Single choice Drop Down
                stringBuilder.append(" p" + surveyDefinitionPage.getOrder() + "q" + question.getOrder() + ",");
                hasDatabaseQuestions = true;
                break;
            case MULTIPLE_CHOICE_CHECKBOXES: //Multiple Choice Checkboxes
                hasDatabaseQuestions = true;
                for (int o = 1; o <= optionsCount; o++) {
                    stringBuilder.append(
                            " p" + surveyDefinitionPage.getOrder() + "q" + question.getOrder() + "o" + o + ",");
                }
                //other support
                stringBuilder.append(
                        " p" + surveyDefinitionPage.getOrder() + "q" + question.getOrder() + "text" + ",");
                break;
            case DATASET_DROP_DOWN: //DataSet Drop Down
                hasDatabaseQuestions = true;
                stringBuilder.append(" p" + surveyDefinitionPage.getOrder() + "q" + question.getOrder() + ",");
                break;
            case SINGLE_CHOICE_RADIO_BUTTONS: //Single Choice Radio Buttons
                hasDatabaseQuestions = true;
                stringBuilder.append(" p" + surveyDefinitionPage.getOrder() + "q" + question.getOrder() + ",");
                //other support
                stringBuilder.append(
                        " p" + surveyDefinitionPage.getOrder() + "q" + question.getOrder() + "text" + ",");
                break;
            case YES_NO_DROPDOWN_MATRIX://Yes No DropDown Matrix
                hasDatabaseQuestions = true;
                for (int r = 1; r <= rowCount; r++) {
                    for (int c = 1; c <= columnCount; c++) {
                        stringBuilder.append(" p" + surveyDefinitionPage.getOrder() + "q" + question.getOrder()
                                + "r" + r + "c" + c + ",");
                    }
                }
                break;
            case SHORT_TEXT_INPUT_MATRIX://Short Text Input Matrix
                hasDatabaseQuestions = true;
                for (int r = 1; r <= rowCount; r++) {
                    for (int c = 1; c <= columnCount; c++) {
                        stringBuilder.append(" p" + surveyDefinitionPage.getOrder() + "q" + question.getOrder()
                                + "r" + r + "c" + c + ",");
                    }
                }
                break;
            case INTEGER_INPUT_MATRIX://Integer Input Matrix
                hasDatabaseQuestions = true;
                for (int r = 1; r <= rowCount; r++) {
                    for (int c = 1; c <= columnCount; c++) {
                        stringBuilder.append(" p" + surveyDefinitionPage.getOrder() + "q" + question.getOrder()
                                + "r" + r + "c" + c + ",");
                    }
                }
                break;
            case CURRENCY_INPUT_MATRIX://Currency Input Matrix
                hasDatabaseQuestions = true;
                for (int r = 1; r <= rowCount; r++) {
                    for (int c = 1; c <= columnCount; c++) {
                        stringBuilder.append(" p" + surveyDefinitionPage.getOrder() + "q" + question.getOrder()
                                + "r" + r + "c" + c + ",");
                    }
                }
                break;
            case DECIMAL_INPUT_MATRIX://Decimal Input Matrix
                hasDatabaseQuestions = true;
                for (int r = 1; r <= rowCount; r++) {
                    for (int c = 1; c <= columnCount; c++) {
                        stringBuilder.append(" p" + surveyDefinitionPage.getOrder() + "q" + question.getOrder()
                                + "r" + r + "c" + c + ",");
                    }
                }
                break;
            case DATE_INPUT_MATRIX://Date Input Matrix
                hasDatabaseQuestions = true;
                for (int r = 1; r <= rowCount; r++) {
                    for (int c = 1; c <= columnCount; c++) {
                        stringBuilder.append(" p" + surveyDefinitionPage.getOrder() + "q" + question.getOrder()
                                + "r" + r + "c" + c + ",");
                    }
                }
                break;

            case STAR_RATING: //Integer Input
                hasDatabaseQuestions = true;
                stringBuilder.append(" p" + surveyDefinitionPage.getOrder() + "q" + question.getOrder() + ",");
                break;
            case SMILEY_FACES_RATING: //Integer Input
                hasDatabaseQuestions = true;
                stringBuilder.append(" p" + surveyDefinitionPage.getOrder() + "q" + question.getOrder() + ",");
                break;
            }
        }
        stringBuilder.setLength(stringBuilder.length() - 1);
        if (!hasDatabaseQuestions) {
            stringBuilder.setLength(stringBuilder.length() - 1);
        }

        stringBuilder.append(" from survey_data_" + surveyDefinitionPage.getSurveyDefinition().getId());
        stringBuilder.append(" where survey_id = ?");
        SurveyPage surveyPage = this.jdbcTemplate.queryForObject(stringBuilder.toString(),
                new Object[] { survey.getId() }, new RowMapper<SurveyPage>() {
                    public SurveyPage mapRow(ResultSet rs, int rowNum) throws SQLException {
                        int optionsCount;
                        int rowCount;
                        int columnCount;

                        Integer[] integerAnswerValuesArray;

                        Long[][] longAnswerValuesMatrix;
                        String[][] stringAnswerValuesMatrix;
                        BigDecimal[][] bigDecimalAnswerValuesMatrix;
                        Boolean[][] booleanAnswerValuesMatrix;
                        Date[][] dateAnswerValuesMatrix;

                        SurveyPage page = new SurveyPage(survey, surveyDefinitionPage);
                        page.setVisible((rs.getBoolean("p" + surveyDefinitionPage.getOrder() + "v")));
                        QuestionAnswer questionAnswer;
                        List<QuestionAnswer> questionAnswers = new ArrayList<QuestionAnswer>();
                        for (Question question : surveyDefinitionPage.getQuestions()) {
                            questionAnswer = new QuestionAnswer(question);
                            optionsCount = question.getOptions().size();
                            rowCount = question.getRowLabels().size();
                            columnCount = question.getColumnLabels().size();

                            switch (question.getType()) {
                            case YES_NO_DROPDOWN: //Yes No DropDown
                                questionAnswer.setBooleanAnswerValue((rs.getBoolean(
                                        "p" + surveyDefinitionPage.getOrder() + "q" + question.getOrder())));
                                break;
                            case SHORT_TEXT_INPUT: //Short Text Input
                                questionAnswer.setStringAnswerValue((rs.getString(
                                        "p" + surveyDefinitionPage.getOrder() + "q" + question.getOrder())));
                                break;
                            case LONG_TEXT_INPUT: //Long Text Input
                                questionAnswer.setStringAnswerValue((rs.getString(
                                        "p" + surveyDefinitionPage.getOrder() + "q" + question.getOrder())));
                                break;
                            case HUGE_TEXT_INPUT: //Huge Text Input
                                questionAnswer.setStringAnswerValue((rs.getString(
                                        "p" + surveyDefinitionPage.getOrder() + "q" + question.getOrder())));
                                break;
                            case INTEGER_INPUT: //Integer Input
                                questionAnswer.setLongAnswerValue((rs.getLong(
                                        "p" + surveyDefinitionPage.getOrder() + "q" + question.getOrder())));
                                if (rs.wasNull())
                                    questionAnswer.setLongAnswerValue(null);
                                questionAnswer
                                        .setStringAnswerValue(questionAnswer.getLongAnswerValue() == null ? ""
                                                : questionAnswer.getLongAnswerValue().toString());
                                break;
                            case CURRENCY_INPUT: //Currency Input
                                questionAnswer.setBigDecimalAnswerValue((rs.getBigDecimal(
                                        "p" + surveyDefinitionPage.getOrder() + "q" + question.getOrder())));
                                questionAnswer.setStringAnswerValue(
                                        questionAnswer.getBigDecimalAnswerValue() == null ? ""
                                                : CurrencyValidator.getInstance().format(
                                                        questionAnswer.getBigDecimalAnswerValue(),
                                                        LocaleContextHolder.getLocale()));
                                break;
                            case DECIMAL_INPUT: //Decimal Input
                                questionAnswer.setBigDecimalAnswerValue((rs.getBigDecimal(
                                        "p" + surveyDefinitionPage.getOrder() + "q" + question.getOrder())));
                                questionAnswer.setStringAnswerValue(
                                        questionAnswer.getBigDecimalAnswerValue() == null ? ""
                                                : BigDecimalValidator.getInstance().format(
                                                        questionAnswer.getBigDecimalAnswerValue(),
                                                        LocaleContextHolder.getLocale()));
                                break;
                            case DATE_INPUT: //Date Input 
                                questionAnswer.setDateAnswerValue((rs.getDate(
                                        "p" + surveyDefinitionPage.getOrder() + "q" + question.getOrder())));
                                questionAnswer
                                        .setStringAnswerValue(questionAnswer.getDateAnswerValue() == null ? ""
                                                : DateValidator.getInstance().format(
                                                        questionAnswer.getDateAnswerValue(), dateFormat));
                                break;
                            case SINGLE_CHOICE_DROP_DOWN: //Single choice Drop Down
                                questionAnswer.setStringAnswerValue((rs.getString(
                                        "p" + surveyDefinitionPage.getOrder() + "q" + question.getOrder())));
                                break;
                            case MULTIPLE_CHOICE_CHECKBOXES: //Multiple Choice Checkboxes
                                integerAnswerValuesArray = new Integer[optionsCount];
                                int index = 0;
                                for (int o = 1; o <= optionsCount; o++) {
                                    if (rs.getBoolean("p" + surveyDefinitionPage.getOrder() + "q"
                                            + question.getOrder() + "o" + o)) {
                                        integerAnswerValuesArray[index] = o;
                                        index++;
                                    }
                                }
                                questionAnswer.setIntegerAnswerValuesArray(integerAnswerValuesArray);
                                //other text data
                                questionAnswer.setOtherStringAnswerValue(
                                        (rs.getString("p" + surveyDefinitionPage.getOrder() + "q"
                                                + question.getOrder() + "text")));
                                break;
                            case DATASET_DROP_DOWN: //DataSet Drop Down
                                questionAnswer.setStringAnswerValue((rs.getString(
                                        "p" + surveyDefinitionPage.getOrder() + "q" + question.getOrder())));
                                break;
                            case SINGLE_CHOICE_RADIO_BUTTONS: //Single Choice Radio Buttons
                                questionAnswer.setStringAnswerValue((rs.getString(
                                        "p" + surveyDefinitionPage.getOrder() + "q" + question.getOrder())));
                                //other text data
                                questionAnswer.setOtherStringAnswerValue(
                                        (rs.getString("p" + surveyDefinitionPage.getOrder() + "q"
                                                + question.getOrder() + "text")));
                                break;
                            case YES_NO_DROPDOWN_MATRIX://Yes No DropDown Matrix
                                booleanAnswerValuesMatrix = new Boolean[rowCount][columnCount];
                                for (int r = 1; r <= rowCount; r++) {
                                    for (int c = 1; c <= columnCount; c++) {
                                        booleanAnswerValuesMatrix[r - 1][c - 1] = rs
                                                .getBoolean("p" + surveyDefinitionPage.getOrder() + "q"
                                                        + question.getOrder() + "r" + r + "c" + c);
                                    }
                                }
                                questionAnswer.setBooleanAnswerValuesMatrix(booleanAnswerValuesMatrix);
                                break;
                            case SHORT_TEXT_INPUT_MATRIX://Short Text Input Matrix
                                stringAnswerValuesMatrix = new String[rowCount][columnCount];
                                for (int r = 1; r <= rowCount; r++) {
                                    for (int c = 1; c <= columnCount; c++) {
                                        stringAnswerValuesMatrix[r - 1][c - 1] = rs
                                                .getString("p" + surveyDefinitionPage.getOrder() + "q"
                                                        + question.getOrder() + "r" + r + "c" + c);
                                    }
                                }
                                questionAnswer.setStringAnswerValuesMatrix(stringAnswerValuesMatrix);
                                break;
                            case INTEGER_INPUT_MATRIX://Integer Input Matrix
                                stringAnswerValuesMatrix = new String[rowCount][columnCount];
                                longAnswerValuesMatrix = new Long[rowCount][columnCount];
                                for (int r = 1; r <= rowCount; r++) {
                                    for (int c = 1; c <= columnCount; c++) {
                                        longAnswerValuesMatrix[r - 1][c - 1] = rs
                                                .getLong("p" + surveyDefinitionPage.getOrder() + "q"
                                                        + question.getOrder() + "r" + r + "c" + c);
                                        if (rs.wasNull())
                                            longAnswerValuesMatrix[r - 1][c - 1] = null;
                                        stringAnswerValuesMatrix[r - 1][c
                                                - 1] = longAnswerValuesMatrix[r - 1][c - 1] == null ? ""
                                                        : longAnswerValuesMatrix[r - 1][c - 1].toString();
                                    }
                                }
                                questionAnswer.setStringAnswerValuesMatrix(stringAnswerValuesMatrix);
                                questionAnswer.setLongAnswerValuesMatrix(longAnswerValuesMatrix);
                                break;
                            case CURRENCY_INPUT_MATRIX://Currency Input Matrix
                                stringAnswerValuesMatrix = new String[rowCount][columnCount];
                                bigDecimalAnswerValuesMatrix = new BigDecimal[rowCount][columnCount];
                                for (int r = 1; r <= rowCount; r++) {
                                    for (int c = 1; c <= columnCount; c++) {
                                        bigDecimalAnswerValuesMatrix[r - 1][c - 1] = rs
                                                .getBigDecimal("p" + surveyDefinitionPage.getOrder() + "q"
                                                        + question.getOrder() + "r" + r + "c" + c);
                                        stringAnswerValuesMatrix[r - 1][c
                                                - 1] = bigDecimalAnswerValuesMatrix[r - 1][c - 1] == null
                                                        ? ""
                                                        : CurrencyValidator.getInstance().format(
                                                                bigDecimalAnswerValuesMatrix[r - 1][c - 1],
                                                                LocaleContextHolder.getLocale());
                                    }
                                }
                                questionAnswer.setStringAnswerValuesMatrix(stringAnswerValuesMatrix);
                                questionAnswer.setBigDecimalAnswerValuesMatrix(bigDecimalAnswerValuesMatrix);
                                break;
                            case DECIMAL_INPUT_MATRIX://Decimal Input Matrix
                                stringAnswerValuesMatrix = new String[rowCount][columnCount];
                                bigDecimalAnswerValuesMatrix = new BigDecimal[rowCount][columnCount];
                                for (int r = 1; r <= rowCount; r++) {
                                    for (int c = 1; c <= columnCount; c++) {
                                        bigDecimalAnswerValuesMatrix[r - 1][c - 1] = rs
                                                .getBigDecimal("p" + surveyDefinitionPage.getOrder() + "q"
                                                        + question.getOrder() + "r" + r + "c" + c);
                                        stringAnswerValuesMatrix[r - 1][c
                                                - 1] = bigDecimalAnswerValuesMatrix[r - 1][c - 1] == null
                                                        ? ""
                                                        : BigDecimalValidator.getInstance().format(
                                                                bigDecimalAnswerValuesMatrix[r - 1][c - 1],
                                                                LocaleContextHolder.getLocale());
                                    }
                                }
                                questionAnswer.setStringAnswerValuesMatrix(stringAnswerValuesMatrix);
                                questionAnswer.setBigDecimalAnswerValuesMatrix(bigDecimalAnswerValuesMatrix);
                                break;
                            case DATE_INPUT_MATRIX://Date Input Matrix
                                stringAnswerValuesMatrix = new String[rowCount][columnCount];
                                dateAnswerValuesMatrix = new Date[rowCount][columnCount];
                                for (int r = 1; r <= rowCount; r++) {
                                    for (int c = 1; c <= columnCount; c++) {
                                        dateAnswerValuesMatrix[r - 1][c - 1] = rs
                                                .getDate("p" + surveyDefinitionPage.getOrder() + "q"
                                                        + question.getOrder() + "r" + r + "c" + c);
                                        stringAnswerValuesMatrix[r - 1][c
                                                - 1] = dateAnswerValuesMatrix[r - 1][c - 1] == null
                                                        ? ""
                                                        : DateValidator.getInstance().format(
                                                                dateAnswerValuesMatrix[r - 1][c - 1],
                                                                dateFormat);
                                    }
                                }
                                questionAnswer.setStringAnswerValuesMatrix(stringAnswerValuesMatrix);
                                questionAnswer.setDateAnswerValuesMatrix(dateAnswerValuesMatrix);
                                break;
                            case STAR_RATING: //Integer Input
                                questionAnswer.setLongAnswerValue((rs.getLong(
                                        "p" + surveyDefinitionPage.getOrder() + "q" + question.getOrder())));
                                if (rs.wasNull())
                                    questionAnswer.setLongAnswerValue(null);
                                questionAnswer
                                        .setStringAnswerValue(questionAnswer.getLongAnswerValue() == null ? ""
                                                : questionAnswer.getLongAnswerValue().toString());
                                break;
                            case SMILEY_FACES_RATING: //Integer Input
                                questionAnswer.setLongAnswerValue((rs.getLong(
                                        "p" + surveyDefinitionPage.getOrder() + "q" + question.getOrder())));
                                if (rs.wasNull())
                                    questionAnswer.setLongAnswerValue(null);
                                questionAnswer
                                        .setStringAnswerValue(questionAnswer.getLongAnswerValue() == null ? ""
                                                : questionAnswer.getLongAnswerValue().toString());
                                break;

                            }
                            questionAnswers.add(questionAnswer);
                        }
                        page.setQuestionAnswers(questionAnswers);
                        return page;
                    }
                });
        return surveyPage;
    } catch (Exception e) {
        log.error(e.getMessage(), e);
        throw (new RuntimeException(e));
    }
}

From source file:com.jd.survey.dao.survey.SurveyDAOImpl.java

/**
 * Reads a single survey page from the survey data table 
 *//* w ww  . j  a  va  2 s .c  o m*/
@Override
public List<SurveyPage> getPages(final Survey survey, final SurveyDefinition surveyDefinition,
        final String dateFormat) {
    try {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("select * ");
        stringBuilder.setLength(stringBuilder.length() - 1);
        stringBuilder.append(" from survey_data_" + surveyDefinition.getId());
        stringBuilder.append(" where survey_id = ?");
        List<SurveyPage> surveyPages = this.jdbcTemplate.queryForObject(stringBuilder.toString(),
                new Object[] { survey.getId() }, new RowMapper<List<SurveyPage>>() {
                    public List<SurveyPage> mapRow(ResultSet rs, int rowNum) throws SQLException {
                        int optionsCount;
                        int rowCount;
                        int columnCount;
                        Integer[] integerAnswerValuesArray;
                        Long[][] longAnswerValuesMatrix;
                        String[][] stringAnswerValuesMatrix;
                        BigDecimal[][] bigDecimalAnswerValuesMatrix;
                        Boolean[][] booleanAnswerValuesMatrix;
                        Date[][] dateAnswerValuesMatrix;

                        List<SurveyPage> pages = new ArrayList<SurveyPage>();
                        for (SurveyDefinitionPage surveyDefinitionPage : surveyDefinition.getPages()) {
                            SurveyPage page = new SurveyPage(survey, surveyDefinitionPage);
                            page.setVisible((rs.getBoolean("p" + surveyDefinitionPage.getOrder() + "v")));
                            QuestionAnswer questionAnswer;
                            List<QuestionAnswer> questionAnswers = new ArrayList<QuestionAnswer>();
                            for (Question question : surveyDefinitionPage.getQuestions()) {
                                questionAnswer = new QuestionAnswer(question);
                                optionsCount = question.getOptions().size();
                                rowCount = question.getRowLabels().size();
                                columnCount = question.getColumnLabels().size();
                                switch (question.getType()) {
                                case YES_NO_DROPDOWN: //Yes No DropDown
                                    questionAnswer.setBooleanAnswerValue((rs.getBoolean("p"
                                            + surveyDefinitionPage.getOrder() + "q" + question.getOrder())));
                                    break;
                                case SHORT_TEXT_INPUT: //Short Text Input
                                    questionAnswer.setStringAnswerValue((rs.getString("p"
                                            + surveyDefinitionPage.getOrder() + "q" + question.getOrder())));
                                    break;
                                case LONG_TEXT_INPUT: //Long Text Input
                                    questionAnswer.setStringAnswerValue((rs.getString("p"
                                            + surveyDefinitionPage.getOrder() + "q" + question.getOrder())));
                                    break;
                                case HUGE_TEXT_INPUT: //Huge Text Input
                                    questionAnswer.setStringAnswerValue((rs.getString("p"
                                            + surveyDefinitionPage.getOrder() + "q" + question.getOrder())));
                                    break;
                                case INTEGER_INPUT: //Integer Input
                                    questionAnswer.setLongAnswerValue((rs.getLong("p"
                                            + surveyDefinitionPage.getOrder() + "q" + question.getOrder())));
                                    questionAnswer.setStringAnswerValue(
                                            questionAnswer.getLongAnswerValue() == null ? ""
                                                    : questionAnswer.getLongAnswerValue().toString());
                                    break;
                                case CURRENCY_INPUT: //Currency Input
                                    questionAnswer.setBigDecimalAnswerValue((rs.getBigDecimal("p"
                                            + surveyDefinitionPage.getOrder() + "q" + question.getOrder())));
                                    questionAnswer.setStringAnswerValue(
                                            questionAnswer.getBigDecimalAnswerValue() == null ? ""
                                                    : CurrencyValidator.getInstance().format(
                                                            questionAnswer.getBigDecimalAnswerValue(),
                                                            LocaleContextHolder.getLocale()));
                                    break;
                                case DECIMAL_INPUT: //Decimal Input
                                    questionAnswer.setBigDecimalAnswerValue((rs.getBigDecimal("p"
                                            + surveyDefinitionPage.getOrder() + "q" + question.getOrder())));
                                    questionAnswer.setStringAnswerValue(
                                            questionAnswer.getBigDecimalAnswerValue() == null ? ""
                                                    : BigDecimalValidator.getInstance().format(
                                                            questionAnswer.getBigDecimalAnswerValue(),
                                                            LocaleContextHolder.getLocale()));
                                    break;
                                case DATE_INPUT: //Date Input 
                                    questionAnswer.setDateAnswerValue((rs.getDate("p"
                                            + surveyDefinitionPage.getOrder() + "q" + question.getOrder())));
                                    questionAnswer
                                            .setStringAnswerValue(
                                                    questionAnswer.getDateAnswerValue() == null ? ""
                                                            : DateValidator.getInstance().format(
                                                                    questionAnswer.getDateAnswerValue(),
                                                                    dateFormat));
                                    break;
                                case SINGLE_CHOICE_DROP_DOWN: //Single choice Drop Down
                                    questionAnswer.setStringAnswerValue((rs.getString("p"
                                            + surveyDefinitionPage.getOrder() + "q" + question.getOrder())));
                                    break;
                                case MULTIPLE_CHOICE_CHECKBOXES: //Multiple Choice Checkboxes
                                    integerAnswerValuesArray = new Integer[optionsCount];
                                    int index = 0;
                                    for (int o = 1; o <= optionsCount; o++) {
                                        if (rs.getBoolean("p" + surveyDefinitionPage.getOrder() + "q"
                                                + question.getOrder() + "o" + o)) {
                                            integerAnswerValuesArray[index] = o;
                                            index++;
                                        }
                                    }
                                    questionAnswer.setIntegerAnswerValuesArray(integerAnswerValuesArray);
                                    //other text data
                                    questionAnswer.setOtherStringAnswerValue(
                                            (rs.getString("p" + surveyDefinitionPage.getOrder() + "q"
                                                    + question.getOrder() + "text")));
                                    break;
                                case DATASET_DROP_DOWN: //DataSet Drop Down
                                    questionAnswer.setStringAnswerValue((rs.getString("p"
                                            + surveyDefinitionPage.getOrder() + "q" + question.getOrder())));
                                    break;
                                case SINGLE_CHOICE_RADIO_BUTTONS: //Single Choice Radio Buttons
                                    questionAnswer.setStringAnswerValue((rs.getString("p"
                                            + surveyDefinitionPage.getOrder() + "q" + question.getOrder())));
                                    //other text data
                                    questionAnswer.setOtherStringAnswerValue(
                                            (rs.getString("p" + surveyDefinitionPage.getOrder() + "q"
                                                    + question.getOrder() + "text")));
                                    break;
                                case YES_NO_DROPDOWN_MATRIX://Yes No DropDown Matrix
                                    booleanAnswerValuesMatrix = new Boolean[rowCount][columnCount];
                                    for (int r = 1; r <= rowCount; r++) {
                                        for (int c = 1; c <= columnCount; c++) {
                                            booleanAnswerValuesMatrix[r - 1][c - 1] = rs
                                                    .getBoolean("p" + surveyDefinitionPage.getOrder() + "q"
                                                            + question.getOrder() + "r" + r + "c" + c);
                                        }
                                    }
                                    questionAnswer.setBooleanAnswerValuesMatrix(booleanAnswerValuesMatrix);
                                    break;
                                case SHORT_TEXT_INPUT_MATRIX://Short Text Input Matrix
                                    stringAnswerValuesMatrix = new String[rowCount][columnCount];
                                    for (int r = 1; r <= rowCount; r++) {
                                        for (int c = 1; c <= columnCount; c++) {
                                            stringAnswerValuesMatrix[r - 1][c - 1] = rs
                                                    .getString("p" + surveyDefinitionPage.getOrder() + "q"
                                                            + question.getOrder() + "r" + r + "c" + c);
                                        }
                                    }
                                    questionAnswer.setStringAnswerValuesMatrix(stringAnswerValuesMatrix);
                                    break;
                                case INTEGER_INPUT_MATRIX://Integer Input Matrix
                                    stringAnswerValuesMatrix = new String[rowCount][columnCount];
                                    longAnswerValuesMatrix = new Long[rowCount][columnCount];
                                    for (int r = 1; r <= rowCount; r++) {
                                        for (int c = 1; c <= columnCount; c++) {
                                            longAnswerValuesMatrix[r - 1][c - 1] = rs
                                                    .getLong("p" + surveyDefinitionPage.getOrder() + "q"
                                                            + question.getOrder() + "r" + r + "c" + c);
                                            stringAnswerValuesMatrix[r - 1][c
                                                    - 1] = longAnswerValuesMatrix[r - 1][c - 1] == null ? ""
                                                            : longAnswerValuesMatrix[r - 1][c - 1].toString();
                                        }
                                    }
                                    questionAnswer.setStringAnswerValuesMatrix(stringAnswerValuesMatrix);
                                    questionAnswer.setLongAnswerValuesMatrix(longAnswerValuesMatrix);
                                    break;
                                case CURRENCY_INPUT_MATRIX://Currency Input Matrix
                                    stringAnswerValuesMatrix = new String[rowCount][columnCount];
                                    bigDecimalAnswerValuesMatrix = new BigDecimal[rowCount][columnCount];
                                    for (int r = 1; r <= rowCount; r++) {
                                        for (int c = 1; c <= columnCount; c++) {
                                            bigDecimalAnswerValuesMatrix[r - 1][c - 1] = rs
                                                    .getBigDecimal("p" + surveyDefinitionPage.getOrder() + "q"
                                                            + question.getOrder() + "r" + r + "c" + c);
                                            stringAnswerValuesMatrix[r - 1][c
                                                    - 1] = bigDecimalAnswerValuesMatrix[r - 1][c - 1] == null
                                                            ? ""
                                                            : CurrencyValidator.getInstance().format(
                                                                    bigDecimalAnswerValuesMatrix[r - 1][c - 1],
                                                                    LocaleContextHolder.getLocale());
                                        }
                                    }
                                    questionAnswer.setStringAnswerValuesMatrix(stringAnswerValuesMatrix);
                                    questionAnswer
                                            .setBigDecimalAnswerValuesMatrix(bigDecimalAnswerValuesMatrix);
                                    break;
                                case DECIMAL_INPUT_MATRIX://Decimal Input Matrix
                                    stringAnswerValuesMatrix = new String[rowCount][columnCount];
                                    bigDecimalAnswerValuesMatrix = new BigDecimal[rowCount][columnCount];
                                    for (int r = 1; r <= rowCount; r++) {
                                        for (int c = 1; c <= columnCount; c++) {
                                            bigDecimalAnswerValuesMatrix[r - 1][c - 1] = rs
                                                    .getBigDecimal("p" + surveyDefinitionPage.getOrder() + "q"
                                                            + question.getOrder() + "r" + r + "c" + c);
                                            stringAnswerValuesMatrix[r - 1][c
                                                    - 1] = bigDecimalAnswerValuesMatrix[r - 1][c - 1] == null
                                                            ? ""
                                                            : BigDecimalValidator.getInstance().format(
                                                                    bigDecimalAnswerValuesMatrix[r - 1][c - 1],
                                                                    LocaleContextHolder.getLocale());
                                        }
                                    }
                                    questionAnswer.setStringAnswerValuesMatrix(stringAnswerValuesMatrix);
                                    questionAnswer
                                            .setBigDecimalAnswerValuesMatrix(bigDecimalAnswerValuesMatrix);
                                    break;
                                case DATE_INPUT_MATRIX://Date Input Matrix
                                    stringAnswerValuesMatrix = new String[rowCount][columnCount];
                                    dateAnswerValuesMatrix = new Date[rowCount][columnCount];
                                    for (int r = 1; r <= rowCount; r++) {
                                        for (int c = 1; c <= columnCount; c++) {
                                            dateAnswerValuesMatrix[r - 1][c - 1] = rs
                                                    .getDate("p" + surveyDefinitionPage.getOrder() + "q"
                                                            + question.getOrder() + "r" + r + "c" + c);
                                            stringAnswerValuesMatrix[r - 1][c
                                                    - 1] = dateAnswerValuesMatrix[r - 1][c - 1] == null
                                                            ? ""
                                                            : DateValidator.getInstance().format(
                                                                    dateAnswerValuesMatrix[r - 1][c - 1],
                                                                    dateFormat);
                                        }
                                    }
                                    questionAnswer.setStringAnswerValuesMatrix(stringAnswerValuesMatrix);
                                    questionAnswer.setDateAnswerValuesMatrix(dateAnswerValuesMatrix);
                                    break;
                                case STAR_RATING: //Integer Input
                                    questionAnswer.setLongAnswerValue((rs.getLong("p"
                                            + surveyDefinitionPage.getOrder() + "q" + question.getOrder())));
                                    if (rs.wasNull())
                                        questionAnswer.setLongAnswerValue(null);
                                    questionAnswer.setStringAnswerValue(
                                            questionAnswer.getLongAnswerValue() == null ? ""
                                                    : questionAnswer.getLongAnswerValue().toString());
                                    break;
                                case SMILEY_FACES_RATING: //Integer Input
                                    questionAnswer.setLongAnswerValue((rs.getLong("p"
                                            + surveyDefinitionPage.getOrder() + "q" + question.getOrder())));
                                    if (rs.wasNull())
                                        questionAnswer.setLongAnswerValue(null);
                                    questionAnswer.setStringAnswerValue(
                                            questionAnswer.getLongAnswerValue() == null ? ""
                                                    : questionAnswer.getLongAnswerValue().toString());
                                    break;
                                }
                                questionAnswers.add(questionAnswer);
                            }
                            page.setQuestionAnswers(questionAnswers);
                            pages.add(page);
                        }
                        return pages;
                    }
                });
        return surveyPages;
    } catch (Exception e) {
        log.error(e.getMessage(), e);
        throw (new RuntimeException(e));
    }
}

From source file:org.openmrs.module.sync.api.db.hibernate.HibernateSyncDAO.java

public void exportChildDB(String uuidForChild, OutputStream os) throws DAOException {
    PrintStream out = new PrintStream(os);
    Set<String> tablesToSkip = new HashSet<String>();
    {//w w w. ja v a  2 s.  c o  m
        tablesToSkip.add("hl7_in_archive");
        tablesToSkip.add("hl7_in_queue");
        tablesToSkip.add("hl7_in_error");
        tablesToSkip.add("formentry_archive");
        tablesToSkip.add("formentry_queue");
        tablesToSkip.add("formentry_error");
        tablesToSkip.add("sync_class");
        tablesToSkip.add("sync_import");
        tablesToSkip.add("sync_record");
        tablesToSkip.add("sync_server");
        tablesToSkip.add("sync_server_class");
        tablesToSkip.add("sync_server_record");
        // TODO: figure out which other tables to skip
        // tablesToSkip.add("obs");
        // tablesToSkip.add("concept");
        // tablesToSkip.add("patient");
    }
    List<String> tablesToDump = new ArrayList<String>();
    Session session = sessionFactory.getCurrentSession();
    String schema = (String) session.createSQLQuery("SELECT schema()").uniqueResult();
    log.warn("schema: " + schema);
    // Get all tables that we'll need to dump
    {
        Query query = session.createSQLQuery(
                "SELECT tabs.table_name FROM INFORMATION_SCHEMA.TABLES tabs WHERE tabs.table_schema = '"
                        + schema + "'");
        for (Object tn : query.list()) {
            String tableName = (String) tn;
            if (!tablesToSkip.contains(tableName.toLowerCase()))
                tablesToDump.add(tableName);
        }
    }
    log.warn("tables to dump: " + tablesToDump);

    String thisServerGuid = getGlobalProperty(SyncConstants.PROPERTY_SERVER_UUID);

    // Write the DDL Header as mysqldump does
    {
        out.println("-- ------------------------------------------------------");
        out.println("-- Database dump to create an openmrs child server");
        out.println("-- Schema: " + schema);
        out.println("-- Parent GUID: " + thisServerGuid);
        out.println("-- Parent version: " + OpenmrsConstants.OPENMRS_VERSION);
        out.println("-- ------------------------------------------------------");
        out.println("");
        out.println("/*!40101 SET CHARACTER_SET_CLIENT=utf8 */;");
        out.println("/*!40101 SET NAMES utf8 */;");
        out.println("/*!40103 SET TIME_ZONE='+00:00' */;");
        out.println("/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;");
        out.println("/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;");
        out.println("/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;");
        out.println("/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;");
        out.println("/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;");
        out.println("/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;");
        out.println("/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;");
        out.println("/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;");
        out.println("");
    }
    try {
        // JDBC way of doing this
        // Connection conn =
        // DriverManager.getConnection("jdbc:mysql://localhost/" + schema,
        // "test", "test");
        Connection conn = sessionFactory.getCurrentSession().connection();
        try {
            Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

            // Get the create database statement
            ResultSet rs = st.executeQuery("SHOW CREATE DATABASE " + schema);
            for (String tableName : tablesToDump) {
                out.println();
                out.println("--");
                out.println("-- Table structure for table `" + tableName + "`");
                out.println("--");
                out.println("DROP TABLE IF EXISTS `" + tableName + "`;");
                out.println("SET @saved_cs_client     = @@character_set_client;");
                out.println("SET character_set_client = utf8;");
                rs = st.executeQuery("SHOW CREATE TABLE " + tableName);
                while (rs.next()) {
                    out.println(rs.getString("Create Table") + ";");
                }
                out.println("SET character_set_client = @saved_cs_client;");
                out.println();

                {
                    out.println("-- Dumping data for table `" + tableName + "`");
                    out.println("LOCK TABLES `" + tableName + "` WRITE;");
                    out.println("/*!40000 ALTER TABLE `" + tableName + "` DISABLE KEYS */;");
                    boolean first = true;

                    rs = st.executeQuery("select * from " + tableName);
                    ResultSetMetaData md = rs.getMetaData();
                    int numColumns = md.getColumnCount();
                    int rowNum = 0;
                    boolean insert = false;

                    while (rs.next()) {
                        if (rowNum == 0) {
                            insert = true;
                            out.print("INSERT INTO `" + tableName + "` VALUES ");
                        }
                        ++rowNum;
                        if (first) {
                            first = false;
                        } else {
                            out.print(", ");
                        }
                        if (rowNum % 20 == 0) {
                            out.println();
                        }
                        out.print("(");
                        for (int i = 1; i <= numColumns; ++i) {
                            if (i != 1) {
                                out.print(",");
                            }
                            if (rs.getObject(i) == null) {
                                out.print("NULL");
                            } else {
                                switch (md.getColumnType(i)) {
                                case Types.VARCHAR:
                                case Types.CHAR:
                                case Types.LONGVARCHAR:
                                    out.print("'");
                                    out.print(
                                            rs.getString(i).replaceAll("\n", "\\\\n").replaceAll("'", "\\\\'"));
                                    out.print("'");
                                    break;
                                case Types.BIGINT:
                                case Types.DECIMAL:
                                case Types.NUMERIC:
                                    out.print(rs.getBigDecimal(i));
                                    break;
                                case Types.BIT:
                                    out.print(rs.getBoolean(i));
                                    break;
                                case Types.INTEGER:
                                case Types.SMALLINT:
                                case Types.TINYINT:
                                    out.print(rs.getInt(i));
                                    break;
                                case Types.REAL:
                                case Types.FLOAT:
                                case Types.DOUBLE:
                                    out.print(rs.getDouble(i));
                                    break;
                                case Types.BLOB:
                                case Types.VARBINARY:
                                case Types.LONGVARBINARY:
                                    Blob blob = rs.getBlob(i);
                                    out.print("'");
                                    InputStream in = blob.getBinaryStream();
                                    while (true) {
                                        int b = in.read();
                                        if (b < 0) {
                                            break;
                                        }
                                        char c = (char) b;
                                        if (c == '\'') {
                                            out.print("\'");
                                        } else {
                                            out.print(c);
                                        }
                                    }
                                    out.print("'");
                                    break;
                                case Types.CLOB:
                                    out.print("'");
                                    out.print(
                                            rs.getString(i).replaceAll("\n", "\\\\n").replaceAll("'", "\\\\'"));
                                    out.print("'");
                                    break;
                                case Types.DATE:
                                    out.print("'" + rs.getDate(i) + "'");
                                    break;
                                case Types.TIMESTAMP:
                                    out.print("'" + rs.getTimestamp(i) + "'");
                                    break;
                                default:
                                    throw new RuntimeException("TODO: handle type code " + md.getColumnType(i)
                                            + " (name " + md.getColumnTypeName(i) + ")");
                                }
                            }
                        }
                        out.print(")");
                    }
                    if (insert) {
                        out.println(";");
                        insert = false;
                    }

                    out.println("/*!40000 ALTER TABLE `" + tableName + "` ENABLE KEYS */;");
                    out.println("UNLOCK TABLES;");
                    out.println();
                }
            }
        } finally {
            conn.close();
        }

        // Now we mark this as a child
        out.println("-- Now mark this as a child database");
        if (uuidForChild == null)
            uuidForChild = SyncUtil.generateUuid();
        out.println("update global_property set property_value = '" + uuidForChild + "' where property = '"
                + SyncConstants.PROPERTY_SERVER_UUID + "';");

        // Write the footer of the DDL script
        {
            out.println("/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;");
            out.println("/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;");
            out.println("/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;");
            out.println("/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;");
            out.println("/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;");
            out.println("/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;");
            out.println("/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;");
            out.println("/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;");
        }
        out.flush();
        out.close();
    } catch (IOException ex) {
        log.error("IOException", ex);

    } catch (SQLException ex) {
        log.error("SQLException", ex);
    }
}