Example usage for java.sql Types BIT

List of usage examples for java.sql Types BIT

Introduction

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

Prototype

int BIT

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

Click Source Link

Document

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

Usage

From source file:com.flexive.core.storage.GenericDivisionImporter.java

/**
 * Import data from a zip archive to a database table
 *
 * @param stmt               statement to use
 * @param zip                zip archive containing the zip entry
 * @param ze                 zip entry within the archive
 * @param xpath              xpath containing the entries to import
 * @param table              name of the table
 * @param executeInsertPhase execute the insert phase?
 * @param executeUpdatePhase execute the update phase?
 * @param updateColumns      columns that should be set to <code>null</code> in a first pass (insert)
 *                           and updated to the provided values in a second pass (update),
 *                           columns that should be used in the where clause have to be prefixed
 *                           with "KEY:", to assign a default value use the expression "columnname:default value",
 *                           if the default value is "@", it will be a negative counter starting at 0, decreasing.
 *                           If the default value starts with "%", it will be set to the column following the "%"
 *                           character in the first pass
 * @throws Exception on errors//w  w  w .j av a  2 s .  c  om
 */
protected void importTable(Statement stmt, final ZipFile zip, final ZipEntry ze, final String xpath,
        final String table, final boolean executeInsertPhase, final boolean executeUpdatePhase,
        final String... updateColumns) throws Exception {
    //analyze the table
    final ResultSet rs = stmt.executeQuery("SELECT * FROM " + table + " WHERE 1=2");
    StringBuilder sbInsert = new StringBuilder(500);
    StringBuilder sbUpdate = updateColumns.length > 0 ? new StringBuilder(500) : null;
    if (rs == null)
        throw new IllegalArgumentException("Can not analyze table [" + table + "]!");
    sbInsert.append("INSERT INTO ").append(table).append(" (");
    final ResultSetMetaData md = rs.getMetaData();
    final Map<String, ColumnInfo> updateClauseColumns = updateColumns.length > 0
            ? new HashMap<String, ColumnInfo>(md.getColumnCount())
            : null;
    final Map<String, ColumnInfo> updateSetColumns = updateColumns.length > 0
            ? new LinkedHashMap<String, ColumnInfo>(md.getColumnCount())
            : null;
    final Map<String, String> presetColumns = updateColumns.length > 0 ? new HashMap<String, String>(10) : null;
    //preset to a referenced column (%column syntax)
    final Map<String, String> presetRefColumns = updateColumns.length > 0 ? new HashMap<String, String>(10)
            : null;
    final Map<String, Integer> counters = updateColumns.length > 0 ? new HashMap<String, Integer>(10) : null;
    final Map<String, ColumnInfo> insertColumns = new HashMap<String, ColumnInfo>(
            md.getColumnCount() + (counters != null ? counters.size() : 0));
    int insertIndex = 1;
    int updateSetIndex = 1;
    int updateClauseIndex = 1;
    boolean first = true;
    for (int i = 0; i < md.getColumnCount(); i++) {
        final String currCol = md.getColumnName(i + 1).toLowerCase();
        if (updateColumns.length > 0) {
            boolean abort = false;
            for (String col : updateColumns) {
                if (col.indexOf(':') > 0 && !col.startsWith("KEY:")) {
                    String value = col.substring(col.indexOf(':') + 1);
                    col = col.substring(0, col.indexOf(':'));
                    if ("@".equals(value)) {
                        if (currCol.equalsIgnoreCase(col)) {
                            counters.put(col, 0);
                            insertColumns.put(col, new ColumnInfo(md.getColumnType(i + 1), insertIndex++));
                            sbInsert.append(',').append(currCol);
                        }
                    } else if (value.startsWith("%")) {
                        if (currCol.equalsIgnoreCase(col)) {
                            presetRefColumns.put(col, value.substring(1));
                            insertColumns.put(col, new ColumnInfo(md.getColumnType(i + 1), insertIndex++));
                            sbInsert.append(',').append(currCol);
                            //                                System.out.println("==> adding presetRefColumn "+col+" with value of "+value.substring(1));
                        }
                    } else if (!presetColumns.containsKey(col))
                        presetColumns.put(col, value);
                }
                if (currCol.equalsIgnoreCase(col)) {
                    abort = true;
                    updateSetColumns.put(currCol, new ColumnInfo(md.getColumnType(i + 1), updateSetIndex++));
                    break;
                }
            }
            if (abort)
                continue;
        }
        if (first) {
            first = false;
        } else
            sbInsert.append(',');
        sbInsert.append(currCol);
        insertColumns.put(currCol, new ColumnInfo(md.getColumnType(i + 1), insertIndex++));
    }
    if (updateColumns.length > 0 && executeUpdatePhase) {
        sbUpdate.append("UPDATE ").append(table).append(" SET ");
        int counter = 0;
        for (String updateColumn : updateSetColumns.keySet()) {
            if (counter++ > 0)
                sbUpdate.append(',');
            sbUpdate.append(updateColumn).append("=?");
        }
        sbUpdate.append(" WHERE ");
        boolean hasKeyColumn = false;
        for (String col : updateColumns) {
            if (!col.startsWith("KEY:"))
                continue;
            hasKeyColumn = true;
            String keyCol = col.substring(4);
            for (int i = 0; i < md.getColumnCount(); i++) {
                if (!md.getColumnName(i + 1).equalsIgnoreCase(keyCol))
                    continue;
                updateClauseColumns.put(keyCol, new ColumnInfo(md.getColumnType(i + 1), updateClauseIndex++));
                sbUpdate.append(keyCol).append("=? AND ");
                break;
            }

        }
        if (!hasKeyColumn)
            throw new IllegalArgumentException("Update columns require a KEY!");
        sbUpdate.delete(sbUpdate.length() - 5, sbUpdate.length()); //remove trailing " AND "
        //"shift" clause indices
        for (String col : updateClauseColumns.keySet()) {
            GenericDivisionImporter.ColumnInfo ci = updateClauseColumns.get(col);
            ci.index += (updateSetIndex - 1);
        }
    }
    if (presetColumns != null) {
        for (String key : presetColumns.keySet())
            sbInsert.append(',').append(key);
    }
    sbInsert.append(")VALUES(");
    for (int i = 0; i < insertColumns.size(); i++) {
        if (i > 0)
            sbInsert.append(',');
        sbInsert.append('?');
    }
    if (presetColumns != null) {
        for (String key : presetColumns.keySet())
            sbInsert.append(',').append(presetColumns.get(key));
    }
    sbInsert.append(')');
    if (DBG) {
        LOG.info("Insert statement:\n" + sbInsert.toString());
        if (updateColumns.length > 0)
            LOG.info("Update statement:\n" + sbUpdate.toString());
    }
    //build a map containing all nodes that require attributes
    //this allows for matching simple xpath queries like "flatstorages/storage[@name='FX_FLAT_STORAGE']/data"
    final Map<String, List<String>> queryAttributes = new HashMap<String, List<String>>(5);
    for (String pElem : xpath.split("/")) {
        if (!(pElem.indexOf('@') > 0 && pElem.indexOf('[') > 0))
            continue;
        List<String> att = new ArrayList<String>(5);
        for (String pAtt : pElem.split("@")) {
            if (!(pAtt.indexOf('=') > 0))
                continue;
            att.add(pAtt.substring(0, pAtt.indexOf('=')));
        }
        queryAttributes.put(pElem.substring(0, pElem.indexOf('[')), att);
    }
    final PreparedStatement psInsert = stmt.getConnection().prepareStatement(sbInsert.toString());
    final PreparedStatement psUpdate = updateColumns.length > 0 && executeUpdatePhase
            ? stmt.getConnection().prepareStatement(sbUpdate.toString())
            : null;
    try {
        final SAXParser parser = SAXParserFactory.newInstance().newSAXParser();
        final DefaultHandler handler = new DefaultHandler() {
            private String currentElement = null;
            private Map<String, String> data = new HashMap<String, String>(10);
            private StringBuilder sbData = new StringBuilder(10000);
            boolean inTag = false;
            boolean inElement = false;
            int counter;
            List<String> path = new ArrayList<String>(10);
            StringBuilder currPath = new StringBuilder(100);
            boolean insertMode = true;

            /**
             * {@inheritDoc}
             */
            @Override
            public void startDocument() throws SAXException {
                counter = 0;
                inTag = false;
                inElement = false;
                path.clear();
                currPath.setLength(0);
                sbData.setLength(0);
                data.clear();
                currentElement = null;
            }

            /**
             * {@inheritDoc}
             */
            @Override
            public void processingInstruction(String target, String data) throws SAXException {
                if (target != null && target.startsWith("fx_")) {
                    if (target.equals("fx_mode"))
                        insertMode = "insert".equals(data);
                } else
                    super.processingInstruction(target, data);
            }

            /**
             * {@inheritDoc}
             */
            @Override
            public void endDocument() throws SAXException {
                if (insertMode)
                    LOG.info("Imported [" + counter + "] entries into [" + table + "] for xpath [" + xpath
                            + "]");
                else
                    LOG.info("Updated [" + counter + "] entries in [" + table + "] for xpath [" + xpath + "]");
            }

            /**
             * {@inheritDoc}
             */
            @Override
            public void startElement(String uri, String localName, String qName, Attributes attributes)
                    throws SAXException {
                pushPath(qName, attributes);
                if (currPath.toString().equals(xpath)) {
                    inTag = true;
                    data.clear();
                    for (int i = 0; i < attributes.getLength(); i++) {
                        String name = attributes.getLocalName(i);
                        if (StringUtils.isEmpty(name))
                            name = attributes.getQName(i);
                        data.put(name, attributes.getValue(i));
                    }
                } else {
                    currentElement = qName;
                }
                inElement = true;
                sbData.setLength(0);
            }

            /**
             * Push a path element from the stack
             *
             * @param qName element name to push
             * @param att attributes
             */
            private void pushPath(String qName, Attributes att) {
                if (att.getLength() > 0 && queryAttributes.containsKey(qName)) {
                    String curr = qName + "[";
                    boolean first = true;
                    final List<String> attList = queryAttributes.get(qName);
                    for (int i = 0; i < att.getLength(); i++) {
                        if (!attList.contains(att.getQName(i)))
                            continue;
                        if (first)
                            first = false;
                        else
                            curr += ',';
                        curr += "@" + att.getQName(i) + "='" + att.getValue(i) + "'";
                    }
                    curr += ']';
                    path.add(curr);
                } else
                    path.add(qName);
                buildPath();
            }

            /**
             * Pop the top path element from the stack
             */
            private void popPath() {
                path.remove(path.size() - 1);
                buildPath();
            }

            /**
             * Rebuild the current path
             */
            private synchronized void buildPath() {
                currPath.setLength(0);
                for (String s : path)
                    currPath.append(s).append('/');
                if (currPath.length() > 1)
                    currPath.delete(currPath.length() - 1, currPath.length());
                //                    System.out.println("currPath: " + currPath);
            }

            /**
             * {@inheritDoc}
             */
            @Override
            public void endElement(String uri, String localName, String qName) throws SAXException {
                if (currPath.toString().equals(xpath)) {
                    if (DBG)
                        LOG.info("Insert [" + xpath + "]: [" + data + "]");
                    inTag = false;
                    try {
                        if (insertMode) {
                            if (executeInsertPhase) {
                                processColumnSet(insertColumns, psInsert);
                                counter += psInsert.executeUpdate();
                            }
                        } else {
                            if (executeUpdatePhase) {
                                if (processColumnSet(updateSetColumns, psUpdate)) {
                                    processColumnSet(updateClauseColumns, psUpdate);
                                    counter += psUpdate.executeUpdate();
                                }
                            }
                        }
                    } catch (SQLException e) {
                        throw new SAXException(e);
                    } catch (ParseException e) {
                        throw new SAXException(e);
                    }
                } else {
                    if (inTag) {
                        data.put(currentElement, sbData.toString());
                    }
                    currentElement = null;
                }
                popPath();
                inElement = false;
                sbData.setLength(0);
            }

            /**
             * Process a column set
             *
             * @param columns the columns to process
             * @param ps prepared statement to use
             * @return if data other than <code>null</code> has been set
             * @throws SQLException on errors
             * @throws ParseException on date/time conversion errors
             */
            private boolean processColumnSet(Map<String, ColumnInfo> columns, PreparedStatement ps)
                    throws SQLException, ParseException {
                boolean dataSet = false;
                for (String col : columns.keySet()) {
                    ColumnInfo ci = columns.get(col);
                    String value = data.get(col);
                    if (insertMode && counters != null && counters.get(col) != null) {
                        final int newVal = counters.get(col) - 1;
                        value = String.valueOf(newVal);
                        counters.put(col, newVal);
                        //                            System.out.println("new value for " + col + ": " + newVal);
                    }
                    if (insertMode && presetRefColumns != null && presetRefColumns.get(col) != null) {
                        value = data.get(presetRefColumns.get(col));
                        //                            System.out.println("Set presetRefColumn for "+col+" to ["+value+"] from column ["+presetRefColumns.get(col)+"]");
                    }

                    if (value == null)
                        ps.setNull(ci.index, ci.columnType);
                    else {
                        dataSet = true;
                        switch (ci.columnType) {
                        case Types.BIGINT:
                        case Types.NUMERIC:
                            if (DBG)
                                LOG.info("BigInt " + ci.index + "->" + new BigDecimal(value));
                            ps.setBigDecimal(ci.index, new BigDecimal(value));
                            break;
                        case java.sql.Types.DOUBLE:
                            if (DBG)
                                LOG.info("Double " + ci.index + "->" + Double.parseDouble(value));
                            ps.setDouble(ci.index, Double.parseDouble(value));
                            break;
                        case java.sql.Types.FLOAT:
                        case java.sql.Types.REAL:
                            if (DBG)
                                LOG.info("Float " + ci.index + "->" + Float.parseFloat(value));
                            ps.setFloat(ci.index, Float.parseFloat(value));
                            break;
                        case java.sql.Types.TIMESTAMP:
                        case java.sql.Types.DATE:
                            if (DBG)
                                LOG.info("Timestamp/Date " + ci.index + "->"
                                        + FxFormatUtils.getDateTimeFormat().parse(value));
                            ps.setTimestamp(ci.index,
                                    new Timestamp(FxFormatUtils.getDateTimeFormat().parse(value).getTime()));
                            break;
                        case Types.TINYINT:
                        case Types.SMALLINT:
                            if (DBG)
                                LOG.info("Integer " + ci.index + "->" + Integer.valueOf(value));
                            ps.setInt(ci.index, Integer.valueOf(value));
                            break;
                        case Types.INTEGER:
                        case Types.DECIMAL:
                            try {
                                if (DBG)
                                    LOG.info("Long " + ci.index + "->" + Long.valueOf(value));
                                ps.setLong(ci.index, Long.valueOf(value));
                            } catch (NumberFormatException e) {
                                //Fallback (temporary) for H2 if the reported long is a big decimal (tree...)
                                ps.setBigDecimal(ci.index, new BigDecimal(value));
                            }
                            break;
                        case Types.BIT:
                        case Types.CHAR:
                        case Types.BOOLEAN:
                            if (DBG)
                                LOG.info("Boolean " + ci.index + "->" + value);
                            if ("1".equals(value) || "true".equals(value))
                                ps.setBoolean(ci.index, true);
                            else
                                ps.setBoolean(ci.index, false);
                            break;
                        case Types.LONGVARBINARY:
                        case Types.VARBINARY:
                        case Types.BLOB:
                        case Types.BINARY:
                            ZipEntry bin = zip.getEntry(value);
                            if (bin == null) {
                                LOG.error("Failed to lookup binary [" + value + "]!");
                                ps.setNull(ci.index, ci.columnType);
                                break;
                            }
                            try {
                                ps.setBinaryStream(ci.index, zip.getInputStream(bin), (int) bin.getSize());
                            } catch (IOException e) {
                                LOG.error("IOException importing binary [" + value + "]: " + e.getMessage(), e);
                            }
                            break;
                        case Types.CLOB:
                        case Types.LONGVARCHAR:
                        case Types.VARCHAR:
                        case SQL_LONGNVARCHAR:
                        case SQL_NCHAR:
                        case SQL_NCLOB:
                        case SQL_NVARCHAR:
                            if (DBG)
                                LOG.info("String " + ci.index + "->" + value);
                            ps.setString(ci.index, value);
                            break;
                        default:
                            LOG.warn("Unhandled type [" + ci.columnType + "] for column [" + col + "]");
                        }
                    }
                }
                return dataSet;
            }

            /**
             * {@inheritDoc}
             */
            @Override
            public void characters(char[] ch, int start, int length) throws SAXException {
                if (inElement)
                    sbData.append(ch, start, length);
            }

        };
        handler.processingInstruction("fx_mode", "insert");
        parser.parse(zip.getInputStream(ze), handler);
        if (updateColumns.length > 0 && executeUpdatePhase) {
            handler.processingInstruction("fx_mode", "update");
            parser.parse(zip.getInputStream(ze), handler);
        }
    } finally {
        Database.closeObjects(GenericDivisionImporter.class, psInsert, psUpdate);
    }
}

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>();
    {/*ww  w .  j a  v  a  2s  .  c  om*/
        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);
    }
}

From source file:com.krawler.portal.tools.ServiceBuilder.java

public void _buildModule(DataObjectOperations dobj, String tablename, String moduleid) {
    Map mapObj = (Map) dobj.getDataObjectMapById("mbFormConfig", "moduleid", moduleid);
    String tableName = null;//w w w  .ja  va2 s  . c o  m
    List<ModuleProperty> fieldData = new ArrayList<ModuleProperty>();
    JSONArray jarray = null;
    if (mapObj != null) {
        try {
            JSONObject jobj = new JSONObject(mapObj.get("fielddata").toString());
            tableName = jobj.getString("tablename");
            jarray = jobj.getJSONArray("data");
        } catch (JSONException ex) {
            logger.warn(ex.getMessage(), ex);
            throw new RuntimeException("Module Configuration not found");
        }
    }
    if (jarray.length() == 0) {
        throw new RuntimeException("Module Configuration is incorrect");
    }

    try {
        for (int i = 0; i < jarray.length(); i++) {
            JSONObject jobjtemp = new JSONObject(jarray.get(i).toString());
            String type = jobjtemp.getString("type");
            int fieldType = 1;
            if (type.equalsIgnoreCase("String")) {
                fieldType = Types.VARCHAR;
            } else if (type.equalsIgnoreCase("double")) {
                fieldType = Types.DOUBLE;
            } else if (type.equals("boolean")) {
                fieldType = Types.BIT;
            } else if (type.equalsIgnoreCase("date")) {
                fieldType = Types.DATE;
            } else if (type.equals("int")) {
                fieldType = Types.INTEGER;
            }
            ModuleProperty moduleField = new ModuleProperty(jobjtemp.get("name").toString(), fieldType);
            moduleField.setForeignKey(jobjtemp.optBoolean("foreignid", false));
            moduleField.setPrimaryKey(jobjtemp.optBoolean("primaryid", false));
            moduleField.setDefaultValue(jobjtemp.optString("defaults", ""));
            moduleField.setRefTable(jobjtemp.optString("table", ""));
            moduleField.setRefField(jobjtemp.optString("reffield", ""));
            fieldData.add(moduleField);
        }
    } catch (JSONException ex) {
        logger.warn(ex.getMessage(), ex);
        throw new RuntimeException("Module Configuration is incorrect");
    } catch (Exception ex) {
        logger.warn(ex.getMessage(), ex);
        throw new RuntimeException("Module Configuration is incorrect");
    }

    dobj.createModuleTable(tableName, fieldData);
}

From source file:gobblin.source.extractor.extract.jdbc.JdbcExtractor.java

private String parseColumnAsString(final ResultSet resultset, final ResultSetMetaData resultsetMetadata, int i)
        throws SQLException {

    if (isBlob(resultsetMetadata.getColumnType(i))) {
        return readBlobAsString(resultset.getBlob(i));
    }//from  ww w.  j ava2s .  co  m
    if ((resultsetMetadata.getColumnType(i) == Types.BIT || resultsetMetadata.getColumnType(i) == Types.BOOLEAN)
            && convertBitToBoolean()) {
        return Boolean.toString(resultset.getBoolean(i));
    }
    return resultset.getString(i);
}

From source file:org.seasar.dbflute.logic.replaceschema.loaddata.impl.DfAbsractDataWriter.java

/**
 * Get the bind type to find a value type.
 * @param tableName The name of table corresponding to column. (NotNull)
 * @param columnMeta The meta info of column. (NotNull)
 * @return The type of column. (NullAllowed: However Basically NotNull)
 *///from  w w  w.j a v a2s.c  o m
protected Class<?> getBindType(String tableName, DfColumnMeta columnMeta) {
    Map<String, Class<?>> cacheMap = _bindTypeCacheMap.get(tableName);
    if (cacheMap == null) {
        cacheMap = StringKeyMap.createAsFlexibleOrdered();
        _bindTypeCacheMap.put(tableName, cacheMap);
    }
    final String columnName = columnMeta.getColumnName();
    Class<?> bindType = cacheMap.get(columnName);
    if (bindType != null) { // cache hit
        return bindType;
    }

    // use mapped JDBC defined value if found (basically found)
    // because it has already been resolved about JDBC specification per DBMS
    final String jdbcType = _columnHandler.getColumnJdbcType(columnMeta);
    Integer jdbcDefValue = TypeMap.getJdbcDefValueByJdbcType(jdbcType);
    if (jdbcDefValue == null) { // basically no way
        jdbcDefValue = columnMeta.getJdbcDefValue(); // as plain
    }

    // ReplaceSchema uses an own original mapping way
    // (not uses Generate mapping)
    // it's simple mapping (for string processor)
    if (jdbcDefValue == Types.CHAR || jdbcDefValue == Types.VARCHAR || jdbcDefValue == Types.LONGVARCHAR
            || jdbcDefValue == Types.CLOB) {
        bindType = String.class;
    } else if (jdbcDefValue == Types.TINYINT || jdbcDefValue == Types.SMALLINT
            || jdbcDefValue == Types.INTEGER) {
        bindType = Integer.class;
    } else if (jdbcDefValue == Types.BIGINT) {
        bindType = Long.class;
    } else if (jdbcDefValue == Types.DECIMAL || jdbcDefValue == Types.NUMERIC) {
        bindType = BigDecimal.class;
    } else if (jdbcDefValue == Types.TIMESTAMP) {
        bindType = Timestamp.class;
    } else if (jdbcDefValue == Types.TIME) {
        bindType = Time.class;
    } else if (jdbcDefValue == Types.DATE) {
        // it depends on value type settings
        // that which is bound java.sql.Date or java.sql.Timestamp
        bindType = java.util.Date.class;
    } else if (jdbcDefValue == Types.BIT || jdbcDefValue == Types.BOOLEAN) {
        bindType = Boolean.class;
    } else if (jdbcDefValue == Types.BINARY || jdbcDefValue == Types.VARBINARY
            || jdbcDefValue == Types.LONGVARBINARY || jdbcDefValue == Types.BLOB) {
        bindType = byte[].class;
    } else if (jdbcDefValue == Types.OTHER && TypeMap.UUID.equalsIgnoreCase(jdbcType)) {
        // [UUID Headache]: The reason why UUID type has not been supported yet on JDBC.
        bindType = UUID.class;
    } else {
        bindType = Object.class;
    }
    cacheMap.put(columnName, bindType);
    return bindType;
}

From source file:org.apache.sqoop.mapreduce.hcat.SqoopHCatUtilities.java

/**
* Resolve a database-specific type to HCat data type. Largely follows Sqoop's
* hive translation./*from  ww  w.  j av a 2  s  .c  o  m*/
* @param sqlType
*          sql type
* @return hcat type
*/
public static String toHCatType(int sqlType) {
    switch (sqlType) {

    // Ideally TINYINT and SMALLINT should be mapped to their
    // HCat equivalents tinyint and smallint respectively
    // But the Sqoop Java type conversion has them mapped to Integer
    // Even though the referenced Java doc clearly recommends otherwise.
    // Changing this now can cause many of the sequence file usages to
    // break as value class implementations will change. So, we
    // just use the same behavior here.
    case Types.SMALLINT:
    case Types.TINYINT:
    case Types.INTEGER:
        return "int";

    case Types.VARCHAR:
        return "varchar";
    case Types.CHAR:
        return "char";
    case Types.LONGVARCHAR:
    case Types.NVARCHAR:
    case Types.NCHAR:
    case Types.LONGNVARCHAR:
    case Types.DATE:
    case Types.TIME:
    case Types.TIMESTAMP:
    case Types.CLOB:
        return "string";

    case Types.FLOAT:
    case Types.REAL:
        return "float";

    case Types.NUMERIC:
    case Types.DECIMAL:
        return "decimal";

    case Types.DOUBLE:
        return "double";

    case Types.BIT:
    case Types.BOOLEAN:
        return "boolean";

    case Types.BIGINT:
        return "bigint";

    case Types.BINARY:
    case Types.VARBINARY:
    case Types.BLOB:
    case Types.LONGVARBINARY:
        return "binary";

    default:
        throw new IllegalArgumentException("Cannot convert SQL type to HCatalog type " + sqlType);
    }
}

From source file:org.jumpmind.symmetric.db.AbstractTriggerTemplate.java

protected String buildKeyVariablesDeclare(Column[] columns, String prefix) {
    String text = "";
    for (int i = 0; i < columns.length; i++) {
        text += "declare @" + prefix + "pk" + i + " ";
        switch (columns[i].getMappedTypeCode()) {
        case Types.TINYINT:
        case Types.SMALLINT:
        case Types.INTEGER:
        case Types.BIGINT:
            text += "bigint\n";
            break;
        case Types.NUMERIC:
        case Types.DECIMAL:
            text += "decimal\n";
            break;
        case Types.FLOAT:
        case Types.REAL:
        case Types.DOUBLE:
            text += "float\n";
            break;
        case Types.CHAR:
        case Types.VARCHAR:
        case ColumnTypes.NVARCHAR:
        case ColumnTypes.LONGNVARCHAR:
        case Types.LONGVARCHAR:
            text += "varchar(1000)\n";
            break;
        case Types.DATE:
            text += "date\n";
            break;
        case Types.TIME:
            text += "time\n";
            break;
        case Types.TIMESTAMP:
            text += "datetime\n";
            break;
        case Types.BOOLEAN:
        case Types.BIT:
            text += "bit\n";
            break;
        case Types.CLOB:
            text += "varchar(max)\n";
            break;
        case Types.BLOB:
        case Types.BINARY:
        case Types.VARBINARY:
        case Types.LONGVARBINARY:
        case -10: // SQL-Server ntext binary type
            text += "varbinary(max)\n";
            break;
        case Types.OTHER:
            text += "varbinary(max)\n";
            break;
        default://  w w  w.j  av  a  2  s  .com
            if (columns[i].getJdbcTypeName() != null
                    && columns[i].getJdbcTypeName().equalsIgnoreCase("interval")) {
                text += "interval";
                break;
            }
            throw new NotImplementedException(columns[i] + " is of type " + columns[i].getMappedType());
        }
    }

    return text;
}

From source file:com.mirth.connect.donkey.test.util.TestUtils.java

private static String typeToString(int sqlType) {
    switch (sqlType) {
    case Types.ARRAY:
        return "ARRAY";
    case Types.BIGINT:
        return "BIGINT";
    case Types.BINARY:
        return "BINARY";
    case Types.BIT:
        return "BIT";
    case Types.BLOB:
        return "BLOB";
    case Types.BOOLEAN:
        return "BOOLEAN";
    case Types.CHAR:
        return "CHAR";
    case Types.CLOB:
        return "CLOB";
    case Types.DATALINK:
        return "DATALINK";
    case Types.DATE:
        return "DATE";
    case Types.DECIMAL:
        return "DECIMAL";
    case Types.DISTINCT:
        return "DISTINCT";
    case Types.DOUBLE:
        return "DOUBLE";
    case Types.FLOAT:
        return "FLOAT";
    case Types.INTEGER:
        return "INTEGER";
    case Types.JAVA_OBJECT:
        return "JAVA_OBJECT";
    case Types.LONGNVARCHAR:
        return "LONGNVARCHAR";
    case Types.LONGVARBINARY:
        return "LONGVARBINARY";
    case Types.LONGVARCHAR:
        return "LONGVARCHAR";
    case Types.NCHAR:
        return "NCHAR";
    case Types.NCLOB:
        return "NCLOB";
    case Types.NULL:
        return "NULL";
    case Types.NUMERIC:
        return "NUMERIC";
    case Types.NVARCHAR:
        return "NVARCHAR";
    case Types.OTHER:
        return "OTHER";
    case Types.REAL:
        return "REAL";
    case Types.REF:
        return "REF";
    case Types.ROWID:
        return "ROWID";
    case Types.SMALLINT:
        return "SMALLINT";
    case Types.SQLXML:
        return "SQLXML";
    case Types.STRUCT:
        return "STRUCT";
    case Types.TIME:
        return "TIME";
    case Types.TIMESTAMP:
        return "TIMESTAMP";
    case Types.TINYINT:
        return "TINYINT";
    case Types.VARBINARY:
        return "VARBINARY";
    case Types.VARCHAR:
        return "VARCHAR";
    default:/*from  w ww. j av a2  s. co  m*/
        return "UNKNOWN";
    }
}

From source file:org.apache.gobblin.source.jdbc.JdbcExtractor.java

private String parseColumnAsString(final ResultSet resultset, final ResultSetMetaData resultsetMetadata, int i)
        throws SQLException {

    if (isBlob(resultsetMetadata.getColumnType(i))) {
        return readBlobAsString(resultset.getBlob(i));
    }//from w ww.j  av a 2  s  .c o m
    if (isClob(resultsetMetadata.getColumnType(i))) {
        return readClobAsString(resultset.getClob(i));
    }
    if ((resultsetMetadata.getColumnType(i) == Types.BIT || resultsetMetadata.getColumnType(i) == Types.BOOLEAN)
            && convertBitToBoolean()) {
        return Boolean.toString(resultset.getBoolean(i));
    }
    return resultset.getString(i);
}

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 ww w  .jav  a2  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;
}