Example usage for java.sql Types TIMESTAMP

List of usage examples for java.sql Types TIMESTAMP

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Types
  6. TIMESTAMP

Introduction

In this page you can find the example usage for java.sql Types TIMESTAMP.

Prototype

int TIMESTAMP

To view the source code for java.sql Types TIMESTAMP.

Click Source Link

Document

The constant in the Java programming language, sometimes referred to as a type code, that identifies the generic SQL type TIMESTAMP.

Usage

From source file:org.apache.ddlutils.io.TestDatabaseIO.java

/**
 * Tests a database model with an unique index with two columns.
 *//*  w ww . j  a  va2 s.  com*/
public void testUniqueIndexWithTwoColumns() throws Exception {
    Database model = readModel("<database xmlns='" + DatabaseIO.DDLUTILS_NAMESPACE + "' name='test'>\n"
            + "  <table name='TableWithIndex'>\n" + "    <column name='id'\n" + "            type='DOUBLE'\n"
            + "            primaryKey='true'\n" + "            required='true'/>\n"
            + "    <column name='when'\n" + "            type='TIMESTAMP'\n" + "            required='true'/>\n"
            + "    <column name='value'\n" + "            type='SMALLINT'\n" + "            default='1'/>\n"
            + "    <unique>\n" + "      <unique-column name='when'/>\n" + "      <unique-column name='id'/>\n"
            + "    </unique>\n" + "  </table>\n" + "</database>");

    assertEquals("test", model.getName());
    assertEquals(1, model.getTableCount());

    Table table = model.getTable(0);

    assertEquals("TableWithIndex", null, 3, 1, 0, 0, 1, table);
    assertEquals("id", Types.DOUBLE, 0, 0, null, null, null, true, true, false, table.getColumn(0));
    assertEquals("when", Types.TIMESTAMP, 0, 0, null, null, null, false, true, false, table.getColumn(1));
    assertEquals("value", Types.SMALLINT, 0, 0, "1", null, null, false, false, false, table.getColumn(2));

    Index index = table.getIndex(0);

    assertEquals(null, true, 2, index);
    assertEquals(table.getColumn(1), null, index.getColumn(0));
    assertEquals(table.getColumn(0), null, index.getColumn(1));

    assertEquals("<?xml version='1.0' encoding='UTF-8'?>\n" + "<database xmlns=\""
            + DatabaseIO.DDLUTILS_NAMESPACE + "\" name=\"test\">\n" + "  <table name=\"TableWithIndex\">\n"
            + "    <column name=\"id\" primaryKey=\"true\" required=\"true\" type=\"DOUBLE\" autoIncrement=\"false\" />\n"
            + "    <column name=\"when\" primaryKey=\"false\" required=\"true\" type=\"TIMESTAMP\" autoIncrement=\"false\" />\n"
            + "    <column name=\"value\" primaryKey=\"false\" required=\"false\" type=\"SMALLINT\" default=\"1\" autoIncrement=\"false\" />\n"
            + "    <unique>\n" + "      <unique-column name=\"when\" />\n"
            + "      <unique-column name=\"id\" />\n" + "    </unique>\n" + "  </table>\n" + "</database>\n",
            model);
}

From source file:nl.ordina.bag.etl.dao.AbstractBAGDAO.java

@Override
public void update(final Nummeraanduiding origineel, final Nummeraanduiding mutation) throws DAOException {
    try {/*from w w  w  . java  2  s  . co m*/
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement("update bag_nummeraanduiding set"
                        + " aanduiding_record_inactief = ?," + " aanduiding_record_correctie = ?,"
                        + " huisnummer = ?," + " officieel = ?," + " huisletter = ?,"
                        + " huisnummertoevoeging = ?," + " postcode = ?," + " einddatum_tijdvak_geldigheid = ?,"
                        + " in_onderzoek = ?," + " type_adresseerbaar_object = ?," + " bron_documentdatum = ?,"
                        + " bron_documentnummer = ?," + " nummeraanduiding_status = ?,"
                        + " bag_woonplaats_id = ?," + " bag_openbare_ruimte_id = ?"
                        + " where bag_nummeraanduiding_id = ?" + " and aanduiding_record_correctie = ?"
                        + " and begindatum_tijdvak_geldigheid = ?");
                ps.setInt(1, mutation.getAanduidingRecordInactief().ordinal());
                ps.setLong(2, mutation.getAanduidingRecordCorrectie());
                ps.setInt(3, mutation.getHuisnummer());
                ps.setInt(4, mutation.getOfficieel().ordinal());
                if (mutation.getHuisletter() == null)
                    ps.setNull(5, Types.INTEGER);
                else
                    ps.setString(5, mutation.getHuisletter());
                if (mutation.getHuisnummertoevoeging() == null)
                    ps.setNull(6, Types.VARCHAR);
                else
                    ps.setString(6, mutation.getHuisnummertoevoeging());
                if (mutation.getPostcode() == null)
                    ps.setNull(7, Types.VARCHAR);
                else
                    ps.setString(7, mutation.getPostcode());
                if (mutation.getEinddatumTijdvakGeldigheid() == null)
                    ps.setNull(8, Types.TIMESTAMP);
                else
                    ps.setTimestamp(8, new Timestamp(mutation.getEinddatumTijdvakGeldigheid().getTime()));
                ps.setInt(9, mutation.getInOnderzoek().ordinal());
                ps.setInt(10, mutation.getTypeAdresseerbaarObject().ordinal());
                ps.setDate(11, new Date(mutation.getDocumentdatum().getTime()));
                ps.setString(12, mutation.getDocumentnummer());
                ps.setInt(13, mutation.getNummeraanduidingStatus().ordinal());
                if (mutation.getGerelateerdeWoonplaats() == null)
                    ps.setNull(14, Types.INTEGER);
                else
                    ps.setLong(14, mutation.getGerelateerdeWoonplaats());
                ps.setLong(15, mutation.getGerelateerdeOpenbareRuimte());
                ps.setLong(16, origineel.getIdentificatie());
                ps.setLong(17, origineel.getAanduidingRecordCorrectie());
                ps.setTimestamp(18, new Timestamp(origineel.getBegindatumTijdvakGeldigheid().getTime()));
                return ps;
            }
        });
    } catch (DataAccessException e) {
        throw new DAOException("Error updating nummeraanduiding: " + origineel.getIdentificatie(), e);
    }
}

From source file:com.streamsets.pipeline.stage.origin.jdbc.cdc.oracle.OracleCDCSource.java

private Map<String, Integer> getTableSchema(SchemaAndTable schemaAndTable) throws SQLException {
    Map<String, Integer> columns = new HashMap<>();
    String query = "SELECT * FROM \"" + schemaAndTable.getSchema() + "\".\"" + schemaAndTable.getTable()
            + "\" WHERE 1 = 0";
    try (Statement schemaStatement = connection.createStatement();
            ResultSet rs = schemaStatement.executeQuery(query)) {
        ResultSetMetaData md = rs.getMetaData();
        int colCount = md.getColumnCount();
        for (int i = 1; i <= colCount; i++) {
            int colType = md.getColumnType(i);
            String colName = md.getColumnName(i);
            if (!configBean.baseConfigBean.caseSensitive) {
                colName = colName.toUpperCase();
            }/*  w w  w .ja v a 2  s.c  o  m*/
            if (colType == Types.DATE || colType == Types.TIME || colType == Types.TIMESTAMP) {
                dateTimeColumns.computeIfAbsent(schemaAndTable, k -> new HashMap<>());
                dateTimeColumns.get(schemaAndTable).put(colName, md.getColumnTypeName(i));
            }

            if (colType == Types.DECIMAL || colType == Types.NUMERIC) {
                decimalColumns.computeIfAbsent(schemaAndTable, k -> new HashMap<>());
                decimalColumns.get(schemaAndTable).put(colName,
                        new PrecisionAndScale(md.getPrecision(i), md.getScale(i)));
            }
            columns.put(md.getColumnName(i), md.getColumnType(i));
        }
    }
    return columns;
}

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

/**
 *  Create a new Node from a ResultSet./*  ww  w .j a  v a  2  s .c  o  m*/
 */
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:nl.ordina.bag.etl.dao.AbstractBAGDAO.java

@Override
public void update(final Pand origineel, final Pand mutation) throws DAOException {
    try {/*from  ww w.  jav  a  2 s.  co m*/
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement("update bag_pand set"
                        + " aanduiding_record_inactief = ?," + " aanduiding_record_correctie = ?,"
                        + " officieel = ?," + " pand_geometrie = ?," + " bouwjaar = ?," + " pand_status = ?,"
                        + " einddatum_tijdvak_geldigheid = ?," + " in_onderzoek = ?,"
                        + " bron_documentdatum = ?," + " bron_documentnummer = ?" + " where bag_pand_id = ?"
                        + " and aanduiding_record_correctie = ?" + " and begindatum_tijdvak_geldigheid = ?");
                ps.setInt(1, mutation.getAanduidingRecordInactief().ordinal());
                ps.setLong(2, mutation.getAanduidingRecordCorrectie());
                ps.setInt(3, mutation.getOfficieel().ordinal());
                ps.setString(4, mutation.getPandGeometrie());
                ps.setInt(5, mutation.getBouwjaar());
                ps.setString(6, mutation.getPandStatus());
                if (mutation.getEinddatumTijdvakGeldigheid() == null)
                    ps.setNull(7, Types.TIMESTAMP);
                else
                    ps.setTimestamp(7, new Timestamp(mutation.getEinddatumTijdvakGeldigheid().getTime()));
                ps.setInt(8, mutation.getInOnderzoek().ordinal());
                ps.setDate(9, new Date(mutation.getDocumentdatum().getTime()));
                ps.setString(10, mutation.getDocumentnummer());
                ps.setLong(11, origineel.getIdentificatie());
                ps.setLong(12, origineel.getAanduidingRecordCorrectie());
                ps.setTimestamp(13, new Timestamp(origineel.getBegindatumTijdvakGeldigheid().getTime()));
                return ps;
            }
        });
    } catch (DataAccessException e) {
        throw new DAOException("Error updating pand: " + origineel.getIdentificatie(), e);
    }
}

From source file:fr.aliacom.obm.common.calendar.CalendarDaoJdbcImpl.java

private PreparedStatement createEventUpdateStatement(Connection con, AccessToken at, Event ev, int sequence)
        throws SQLException {
    PreparedStatement ps;/* w  w  w . j a  v  a  2s.  co  m*/
    String upQ = "UPDATE Event SET event_userupdate=?, " + "event_type=?, event_timezone=?, event_opacity=?, "
            + "event_title=?, event_location=?, " + "event_category1_id=?, event_priority=?, "
            + "event_privacy=?, event_date=?, event_duration=?, " + "event_allday=?, event_repeatkind=?, "
            + "event_repeatfrequence=?, event_repeatdays=?, " + "event_endrepeat=?, event_completed=?, "
            + "event_url=?, event_description=?, event_origin=?, " + "event_sequence=? " + "WHERE event_id=?";

    ps = con.prepareStatement(upQ);
    try {
        ps.setInt(1, at.getObmId());
        ps.setObject(2, obmHelper.getDBCP().getJdbcObject(ObmHelper.VCOMPONENT, ev.getType().toString()));
        ps.setString(3, ev.getTimezoneName() != null ? ev.getTimezoneName() : "Europe/Paris");
        ps.setObject(4, obmHelper.getDBCP().getJdbcObject(ObmHelper.VOPACITY, ev.getOpacity().toString()));
        ps.setString(5, ev.getTitle());
        ps.setString(6, ev.getLocation());
        Integer cat = catIdFromString(con, ev.getCategory(), at.getDomain().getId());
        if (cat != null) {
            ps.setInt(7, cat);
        } else {
            ps.setNull(7, Types.INTEGER);
        }
        ps.setInt(8, ev.getPriority());
        // do not allow making a private event become public from sync
        // ps.setInt(9, old.getPrivacy() != 1 ? ev.getPrivacy() : old
        // .getPrivacy());
        ps.setInt(9, ev.getPrivacy().toInteger());
        ps.setTimestamp(10, new Timestamp(ev.getStartDate().getTime()));
        ps.setInt(11, ev.getDuration());
        ps.setBoolean(12, ev.isAllday());
        EventRecurrence er = ev.getRecurrence();
        ps.setString(13, er.getKind().toString());
        ps.setInt(14, er.getFrequence());
        ps.setString(15, new RecurrenceDaysSerializer().serialize(er.getDays()));
        if (er.getEnd() != null) {
            ps.setTimestamp(16, new Timestamp(er.getEnd().getTime()));
        } else {
            ps.setNull(16, Types.TIMESTAMP);
        }
        ps.setNull(17, Types.TIMESTAMP);
        ps.setNull(18, Types.VARCHAR);
        ps.setString(19, ev.getDescription());
        ps.setString(20, at.getOrigin());
        ps.setInt(21, sequence);
        ps.setInt(22, ev.getObmId().getObmId());
        return ps;
    } catch (SQLException e) {
        ps.close();
        throw e;
    } catch (RuntimeException e) {
        ps.close();
        throw e;
    }
}

From source file:nl.ordina.bag.etl.dao.AbstractBAGDAO.java

@Override
public void update(final Verblijfsobject origineel, final Verblijfsobject mutation) throws DAOException {
    try {/*from ww w .j a v  a  2 s  .  c om*/
        transactionTemplate.execute(new TransactionCallbackWithoutResult() {
            @Override
            protected void doInTransactionWithoutResult(TransactionStatus status) {
                jdbcTemplate.update(new PreparedStatementCreator() {
                    @Override
                    public PreparedStatement createPreparedStatement(Connection connection)
                            throws SQLException {
                        PreparedStatement ps = connection.prepareStatement("update bag_verblijfsobject set"
                                + " aanduiding_record_inactief = ?," + " aanduiding_record_correctie = ?,"
                                + " officieel = ?," + " verblijfsobject_geometrie = ?,"
                                + " oppervlakte_verblijfsobject = ?," + " verblijfsobject_status = ?,"
                                + " einddatum_tijdvak_geldigheid = ?," + " in_onderzoek = ?,"
                                + " bron_documentdatum = ?," + " bron_documentnummer = ?,"
                                + " bag_nummeraanduiding_id = ?" + " where bag_verblijfsobject_id = ?"
                                + " and aanduiding_record_correctie = ?"
                                + " and begindatum_tijdvak_geldigheid = ?");
                        ps.setInt(1, mutation.getAanduidingRecordInactief().ordinal());
                        ps.setLong(2, mutation.getAanduidingRecordCorrectie());
                        ps.setInt(3, mutation.getOfficieel().ordinal());
                        ps.setString(4, mutation.getVerblijfsobjectGeometrie());
                        ps.setInt(5, mutation.getOppervlakteVerblijfsobject());
                        ps.setInt(6, mutation.getVerblijfsobjectStatus().ordinal());
                        if (mutation.getEinddatumTijdvakGeldigheid() == null)
                            ps.setNull(7, Types.TIMESTAMP);
                        else
                            ps.setTimestamp(7,
                                    new Timestamp(mutation.getEinddatumTijdvakGeldigheid().getTime()));
                        ps.setInt(8, mutation.getInOnderzoek().ordinal());
                        ps.setDate(9, new Date(mutation.getDocumentdatum().getTime()));
                        ps.setString(10, mutation.getDocumentnummer());
                        ps.setLong(11, mutation.getHoofdAdres());
                        ps.setLong(12, origineel.getIdentificatie());
                        ps.setLong(13, origineel.getAanduidingRecordCorrectie());
                        ps.setTimestamp(14,
                                new Timestamp(origineel.getBegindatumTijdvakGeldigheid().getTime()));
                        return ps;
                    }
                });
                deleteGebruikersdoelen(origineel);
                insertGebruikersdoelen(mutation);
                deleteNevenadressen(TypeAdresseerbaarObject.VERBLIJFSOBJECT, origineel);
                insertNevenadressen(TypeAdresseerbaarObject.VERBLIJFSOBJECT, mutation);
                deleteGerelateerdePanden(origineel);
                insertGerelateerdePanden(mutation);
            }
        });
    } catch (DataAccessException e) {
        throw new DAOException("Error updating verblijfsobject: " + origineel.getIdentificatie(), e);
    }
}

From source file:nl.ordina.bag.etl.dao.AbstractBAGDAO.java

@Override
public void update(final Ligplaats origineel, final Ligplaats mutation) throws DAOException {
    try {/*from  w w w .j a  v  a  2 s. c o m*/
        transactionTemplate.execute(new TransactionCallbackWithoutResult() {
            @Override
            protected void doInTransactionWithoutResult(TransactionStatus status) {
                jdbcTemplate.update(new PreparedStatementCreator() {
                    @Override
                    public PreparedStatement createPreparedStatement(Connection connection)
                            throws SQLException {
                        PreparedStatement ps = connection.prepareStatement("update bag_ligplaats set"
                                + " aanduiding_record_inactief = ?," + " aanduiding_record_correctie = ?,"
                                + " officieel = ?," + " ligplaats_status = ?," + " ligplaats_geometrie = ?,"
                                + " einddatum_tijdvak_geldigheid = ?," + " in_onderzoek = ?,"
                                + " bron_documentdatum = ?," + " bron_documentnummer = ?,"
                                + " bag_nummeraanduiding_id = ?" + " where bag_ligplaats_id = ?"
                                + " and aanduiding_record_correctie = ?"
                                + " and begindatum_tijdvak_geldigheid = ?");
                        ps.setInt(1, mutation.getAanduidingRecordInactief().ordinal());
                        ps.setLong(2, mutation.getAanduidingRecordCorrectie());
                        ps.setInt(3, mutation.getOfficieel().ordinal());
                        ps.setInt(4, mutation.getLigplaatsStatus().ordinal());
                        ps.setString(5, mutation.getLigplaatsGeometrie());
                        if (mutation.getEinddatumTijdvakGeldigheid() == null)
                            ps.setNull(6, Types.TIMESTAMP);
                        else
                            ps.setTimestamp(6,
                                    new Timestamp(mutation.getEinddatumTijdvakGeldigheid().getTime()));
                        ps.setInt(7, mutation.getInOnderzoek().ordinal());
                        ps.setDate(8, new Date(mutation.getDocumentdatum().getTime()));
                        ps.setString(9, mutation.getDocumentnummer());
                        ps.setLong(10, mutation.getHoofdAdres());
                        ps.setLong(11, origineel.getIdentificatie());
                        ps.setLong(12, origineel.getAanduidingRecordCorrectie());
                        ps.setTimestamp(13,
                                new Timestamp(origineel.getBegindatumTijdvakGeldigheid().getTime()));
                        return ps;
                    }
                });
                deleteNevenadressen(TypeAdresseerbaarObject.LIGPLAATS, origineel);
                insertNevenadressen(TypeAdresseerbaarObject.LIGPLAATS, mutation);
            }
        });
    } catch (DataAccessException e) {
        throw new DAOException("Error updating ligplaats: " + origineel.getIdentificatie(), e);
    }
}

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

private void setStatementValues(PreparedStatement stmt, int stmtNumber, Property p, int columnType)
        throws SQLException {
    if (p.getValue() == null) {
        stmt.setNull(stmtNumber, columnType);
    } else {/*  w  w  w.  ja  v  a  2  s .co m*/
        switch (columnType) {
        case Types.BIT:
        case Types.TINYINT:
        case Types.BIGINT:
        case Types.SMALLINT:
        case Types.INTEGER:
            stmt.setLong(stmtNumber, p.getIntegerValue());

            break;

        case Types.REAL:
        case Types.FLOAT:
        case Types.DOUBLE:
        case Types.NUMERIC:
        case Types.DECIMAL:
            stmt.setDouble(stmtNumber, p.getFloatValue());

            break;

        case Types.VARBINARY:
        case Types.BINARY:
        case Types.BLOB:
            stmt.setString(stmtNumber, p.getStringValue());

            break;

        case Types.LONGVARBINARY:
        case Types.LONGVARCHAR:
            try {
                stmt.setString(stmtNumber, p.getStringValue());
            } catch (SQLException x) {
                String str = p.getStringValue();
                Reader r = new StringReader(str);

                stmt.setCharacterStream(stmtNumber, r, str.length());
            }

            break;

        case Types.CLOB:
            String val = p.getStringValue();
            Reader isr = new StringReader(val);
            stmt.setCharacterStream(stmtNumber, isr, val.length());

            break;

        case Types.CHAR:
        case Types.VARCHAR:
        case Types.OTHER:
            stmt.setString(stmtNumber, p.getStringValue());

            break;

        case Types.DATE:
        case Types.TIME:
        case Types.TIMESTAMP:
            stmt.setTimestamp(stmtNumber, p.getTimestampValue());

            break;

        case Types.NULL:
            stmt.setNull(stmtNumber, 0);

            break;

        default:
            stmt.setString(stmtNumber, p.getStringValue());

            break;
        }
    }
}

From source file:org.apache.ddlutils.io.TestDatabaseIO.java

/**
 * Tests a complex database model with multiple tables, foreign keys, indices and uniques.
 *//*ww w .j  a  v a2  s.  c o  m*/
public void testComplex() throws Exception {
    // A = id:INTEGER, parentId:INTEGER, name:VARCHAR(32); fk 'parent' -> A (parentId -> id), unique(name)
    // B = id:TIMESTAMP, aid:INTEGER, cid:CHAR(32) fk -> A (aid -> id), fk -> C (cid -> id), index(aid,cid)
    // C = id:CHAR(32), text:LONGVARCHAR; index 'byText' (text)

    Database model = readModel("<database xmlns='" + DatabaseIO.DDLUTILS_NAMESPACE + "' name='test'>\n"
            + "  <table name='A'\n" + "         description='Table A'>\n" + "    <column name='id'\n"
            + "            type='INTEGER'\n" + "            autoIncrement='true'\n"
            + "            primaryKey='true'\n" + "            required='true'\n"
            + "            description='The primary key of table A'/>\n" + "    <column name='parentId'\n"
            + "            type='INTEGER'\n"
            + "            description='The field for the foreign key parent'/>\n" + "    <column name='name'\n"
            + "            type='VARCHAR'\n" + "            size='32'\n" + "            required='true'\n"
            + "            description='The name'/>\n" + "    <foreign-key name='parent' foreignTable='A'>\n"
            + "       <reference local='parentId' foreign='id'/>\n" + "    </foreign-key>\n" + "    <unique>\n"
            + "      <unique-column name='name'/>\n" + "    </unique>\n" + "  </table>\n"
            + "  <table name='B'\n" + "         description='Table B'>\n" + "    <column name='id'\n"
            + "            type='TIMESTAMP'\n" + "            primaryKey='true'\n"
            + "            required='true'\n" + "            description='The primary key of table B'/>\n"
            + "    <column name='aid'\n" + "            type='INTEGER'\n"
            + "            description='The field for the foreign key towards A'/>\n"
            + "    <column name='cid'\n" + "            type='CHAR'\n" + "            size='32'\n"
            + "            description='The field for the foreign key towards C'/>\n"
            + "    <foreign-key foreignTable='A'>\n" + "       <reference local='aid' foreign='id'/>\n"
            + "    </foreign-key>\n" + "    <foreign-key foreignTable='C'>\n"
            + "       <reference local='cid' foreign='id'/>\n" + "    </foreign-key>\n" + "    <index>\n"
            + "      <index-column name='aid'/>\n" + "      <index-column name='cid'/>\n" + "    </index>\n"
            + "  </table>\n" + "  <table name='C'\n" + "         description='Table C'>\n"
            + "    <column name='id'\n" + "            type='CHAR'\n" + "            size='32'\n"
            + "            primaryKey='true'\n" + "            required='true'\n"
            + "            description='The primary key of table C'/>\n" + "    <column name='text'\n"
            + "            type='LONGVARCHAR'\n" + "            description='The text'/>\n"
            + "    <index name='byText'>\n" + "      <index-column name='text'/>\n" + "    </index>\n"
            + "  </table>\n" + "</database>");

    assertEquals("test", model.getName());
    assertEquals(3, model.getTableCount());

    // table A

    Table table = model.getTable(0);

    assertEquals("A", "Table A", 3, 1, 1, 1, 1, table);
    assertEquals("id", Types.INTEGER, 0, 0, null, "The primary key of table A", null, true, true, true,
            table.getColumn(0));
    assertEquals("parentId", Types.INTEGER, 0, 0, null, "The field for the foreign key parent", null, false,
            false, false, table.getColumn(1));
    assertEquals("name", Types.VARCHAR, 32, 0, null, "The name", null, false, true, false, table.getColumn(2));
    assertEquals(table.getColumn(0), table.getAutoIncrementColumns()[0]);

    ForeignKey fk = table.getForeignKey(0);

    assertEquals("parent", CascadeActionEnum.NONE, CascadeActionEnum.NONE, table, 1, fk);
    assertEquals(table.getColumn(1), table.getColumn(0), fk.getFirstReference());

    Index index = table.getIndex(0);

    assertEquals(null, true, 1, index);
    assertEquals(table.getColumn(2), null, index.getColumn(0));

    // table B

    table = model.getTable(1);

    assertEquals("B", "Table B", 3, 1, 0, 2, 1, table);
    assertEquals("id", Types.TIMESTAMP, 0, 0, null, "The primary key of table B", null, true, true, false,
            table.getColumn(0));
    assertEquals("aid", Types.INTEGER, 0, 0, null, "The field for the foreign key towards A", null, false,
            false, false, table.getColumn(1));
    assertEquals("cid", Types.CHAR, 32, 0, null, "The field for the foreign key towards C", null, false, false,
            false, table.getColumn(2));

    fk = table.getForeignKey(0);

    assertEquals(null, CascadeActionEnum.NONE, CascadeActionEnum.NONE, model.getTable(0), 1, fk);
    assertEquals(table.getColumn(1), model.getTable(0).getColumn(0), fk.getFirstReference());

    fk = table.getForeignKey(1);

    assertEquals(null, CascadeActionEnum.NONE, CascadeActionEnum.NONE, model.getTable(2), 1, fk);
    assertEquals(table.getColumn(2), model.getTable(2).getColumn(0), fk.getFirstReference());

    index = table.getIndex(0);

    assertEquals(null, false, 2, index);
    assertEquals(table.getColumn(1), null, index.getColumn(0));
    assertEquals(table.getColumn(2), null, index.getColumn(1));

    // table C

    table = model.getTable(2);

    assertEquals("C", "Table C", 2, 1, 0, 0, 1, table);
    assertEquals("id", Types.CHAR, 32, 0, null, "The primary key of table C", null, true, true, false,
            table.getColumn(0));
    assertEquals("text", Types.LONGVARCHAR, 0, 0, null, "The text", null, false, false, false,
            table.getColumn(1));

    index = table.getIndex(0);

    assertEquals("byText", false, 1, index);
    assertEquals(table.getColumn(1), null, index.getColumn(0));

    assertEquals("<?xml version='1.0' encoding='UTF-8'?>\n" + "<database xmlns=\""
            + DatabaseIO.DDLUTILS_NAMESPACE + "\" name=\"test\">\n"
            + "  <table name=\"A\" description=\"Table A\">\n"
            + "    <column name=\"id\" primaryKey=\"true\" required=\"true\" type=\"INTEGER\" autoIncrement=\"true\" description=\"The primary key of table A\" />\n"
            + "    <column name=\"parentId\" primaryKey=\"false\" required=\"false\" type=\"INTEGER\" autoIncrement=\"false\" description=\"The field for the foreign key parent\" />\n"
            + "    <column name=\"name\" primaryKey=\"false\" required=\"true\" type=\"VARCHAR\" size=\"32\" autoIncrement=\"false\" description=\"The name\" />\n"
            + "    <foreign-key foreignTable=\"A\" name=\"parent\">\n"
            + "      <reference local=\"parentId\" foreign=\"id\" />\n" + "    </foreign-key>\n"
            + "    <unique>\n" + "      <unique-column name=\"name\" />\n" + "    </unique>\n" + "  </table>\n"
            + "  <table name=\"B\" description=\"Table B\">\n"
            + "    <column name=\"id\" primaryKey=\"true\" required=\"true\" type=\"TIMESTAMP\" autoIncrement=\"false\" description=\"The primary key of table B\" />\n"
            + "    <column name=\"aid\" primaryKey=\"false\" required=\"false\" type=\"INTEGER\" autoIncrement=\"false\" description=\"The field for the foreign key towards A\" />\n"
            + "    <column name=\"cid\" primaryKey=\"false\" required=\"false\" type=\"CHAR\" size=\"32\" autoIncrement=\"false\" description=\"The field for the foreign key towards C\" />\n"
            + "    <foreign-key foreignTable=\"A\">\n" + "      <reference local=\"aid\" foreign=\"id\" />\n"
            + "    </foreign-key>\n" + "    <foreign-key foreignTable=\"C\">\n"
            + "      <reference local=\"cid\" foreign=\"id\" />\n" + "    </foreign-key>\n" + "    <index>\n"
            + "      <index-column name=\"aid\" />\n" + "      <index-column name=\"cid\" />\n"
            + "    </index>\n" + "  </table>\n" + "  <table name=\"C\" description=\"Table C\">\n"
            + "    <column name=\"id\" primaryKey=\"true\" required=\"true\" type=\"CHAR\" size=\"32\" autoIncrement=\"false\" description=\"The primary key of table C\" />\n"
            + "    <column name=\"text\" primaryKey=\"false\" required=\"false\" type=\"LONGVARCHAR\" autoIncrement=\"false\" description=\"The text\" />\n"
            + "    <index name=\"byText\">\n" + "      <index-column name=\"text\" />\n" + "    </index>\n"
            + "  </table>\n" + "</database>\n", model);
}