Example usage for java.sql PreparedStatement setObject

List of usage examples for java.sql PreparedStatement setObject

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. setObject

Introduction

In this page you can find the example usage for java.sql PreparedStatement setObject.

Prototype

void setObject(int parameterIndex, Object x) throws SQLException;

Source Link

Document

Sets the value of the designated parameter using the given object.

Usage

From source file:com.enonic.vertical.engine.handlers.MenuHandler.java

private Hashtable<String, Element> appendMenuItemsBySettings(User user, Document doc,
        Hashtable<String, Element> hashtable_menus, MenuGetterSettings getterSettings) {

    Hashtable<String, Element> hashtable_MenuItems = new Hashtable<String, Element>();

    List<Integer> paramValues = new ArrayList<Integer>(2);
    StringBuffer sqlMenuItems = new StringBuffer(MENU_ITEM_SELECT);

    // menuKey/*w ww  .  ja  va  2 s  . c  om*/
    MenuItemCriteria criteria = getterSettings.getMenuItemCriteria();
    if (getterSettings.hasMenuKeys() || criteria.hasMenuKey()) {
        if (getterSettings.hasMenuKeys()) {
            int[] menuKeys = getterSettings.getMenuKeys();
            sqlMenuItems.append(" AND men_lKey IN (");
            for (int i = 0; i < menuKeys.length; i++) {
                if (i > 0) {
                    sqlMenuItems.append(',');
                }
                sqlMenuItems.append(menuKeys[i]);
            }

            sqlMenuItems.append(')');
        } else {
            sqlMenuItems.append(" AND mei_men_lKey = ?");
            paramValues.add(criteria.getMenuKeyAsInteger());
        }
    }
    // only root menuitems
    if (getterSettings.getOnlyRootMenuItems()) {
        sqlMenuItems.append(" AND mei_lParent IS NULL");
    }

    if (user != null) {
        getSecurityHandler().appendMenuItemSQL(user, sqlMenuItems, criteria);
    }

    sqlMenuItems.append(ORDER_BY);

    Connection con = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;

    Element element_AdminReadMenuItems = doc.getDocumentElement();
    try {
        con = getConnection();

        statement = con.prepareStatement(sqlMenuItems.toString());

        int i = 1;
        for (Iterator<Integer> iter = paramValues.iterator(); iter.hasNext(); i++) {
            Object paramValue = iter.next();
            statement.setObject(i, paramValue);
        }

        try {
            // Hender ut menuitems
            resultSet = statement.executeQuery();
            while (resultSet.next()) {

                int curMenuItemKey = resultSet.getInt("mei_lKey");

                Element menuItem = buildMenuItemXML(doc, element_AdminReadMenuItems, resultSet, -1, false,
                        false, true, true, false, 1);
                Element accessRights = XMLTool.createElement(doc, menuItem, "accessrights");
                getSecurityHandler().appendAccessRightsOnMenuItem(user, curMenuItemKey, accessRights, true);

                XMLTool.createElement(doc, menuItem, "menuitems");
                // Lagrer referansen til kategori-elementet for raskt oppslag til senere bruk
                hashtable_MenuItems.put(String.valueOf(curMenuItemKey), menuItem);
            }
        } finally {
            close(resultSet);
            resultSet = null;
            close(statement);
            statement = null;
        }

        // Gr igjennom menuitems og bygger opp trestrukturen
        Element curAdminReadMenuItem = (Element) element_AdminReadMenuItems.getFirstChild();
        while (curAdminReadMenuItem != null) {

            String parentKey = curAdminReadMenuItem.getAttribute("parent");

            Element nextElement = (Element) curAdminReadMenuItem.getNextSibling();

            // Forelder node finnes ikke fra fr
            if ("menuitem".equals(curAdminReadMenuItem.getNodeName())) {
                insertParentMenuItem(user, MENU_ITEM_SELECT_BY_KEY, doc, element_AdminReadMenuItems,
                        hashtable_menus, hashtable_MenuItems, curAdminReadMenuItem, parentKey);
            }

            curAdminReadMenuItem = nextElement;
        }

    } catch (SQLException sqle) {
        String message = "Failed to get menuitems: %t";
        VerticalEngineLogger.error(this.getClass(), 0, message, sqle);
    } finally {
        close(resultSet);
        close(statement);
        close(con);
    }

    return hashtable_MenuItems;
}

From source file:com.iana.boesc.dao.BOESCDaoImpl.java

@Override
public boolean insertDVIRRecord(DVIR_EDI322Bean eb, DVIRRCDStatisticBean bean, String boescUserId,
        String userType, File file) throws Exception {
    logger.info("----- getPopulatedDataAndInsert ............" + " boescUserId ::" + boescUserId
            + " userType ::" + userType);
    QueryRunner qrun = new QueryRunner(getDataSource());
    Connection conn = getConnection();
    conn.setAutoCommit(false);/*from www.  ja  v  a  2s  .c om*/
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    try {

        StringBuilder sbQuery = new StringBuilder(
                "INSERT INTO DVIR_TRAN_SET (ISA_HEADER, GS_HEADER, SENDER_ID, SENDER_TYPE, ISA_DATETIME, GS_CONTROL, ST_CONTROL,");
        sbQuery.append(
                " INSP_DATE, INSP_TIME, INSP_TIME_ZONE, EQ_PREFIX, EQ_NUMBER, CHASSIS_ID, IEP_SCAC, PORT_QUAL, PORT_ID,  ");
        sbQuery.append(
                " DRV_STATE_ABBR, DRV_LIC_NO, DRV_NAME, MC_SCAC, MC_NAME, RCD_INFO, IEP_DOT, MC_EIN, MC_DOT, IDD_PIN,   ");
        sbQuery.append(
                " Q5_SEG, N7_SEG, R4_SEG, N1_SEG, N1_DR, RCD_00, RCD_01, RCD_02, RCD_03, RCD_04, RCD_05, RCD_06, RCD_07, RCD_08, RCD_09,  ");
        if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_IEP)) {
            sbQuery.append(" IEP_ID, STATUS   ");
        } else if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_MRV)) {
            sbQuery.append(" MRV_ID, STATUS   ");
        } else if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_FO)) {
            sbQuery.append(" FO_ID, STATUS ");
        }

        sbQuery.append(
                " ,CREATED_DATE ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

        //get Additional Details from GIER DB
        GIERInfoDetails gierInfo = null;
        gierInfo = getDVIRAdditionaldetails(eb.getEqpInitial(), eb.getEqpNumber());
        System.out.println("Before UIIA Datasource");
        UIIAInfoDetails uiiaInfo = null;
        uiiaInfo = getUIIAdetailsforDVIR(eb);

        //logger.info("gierInfo ::"+gierInfo);

        pstmt = conn.prepareStatement(sbQuery.toString(), Statement.RETURN_GENERATED_KEYS);
        pstmt.setString(1, eb.getIsaheader());
        pstmt.setString(2, eb.getGsHeader());
        pstmt.setString(3, eb.getSenderId());
        pstmt.setString(4, userType);
        pstmt.setString(5, eb.getIsaDateTime());
        pstmt.setString(6, eb.getGsControl());
        pstmt.setString(7, eb.getStControl());
        pstmt.setString(8, eb.getInspDate());
        pstmt.setString(9, eb.getInspTime());
        pstmt.setString(10, eb.getInspTimeZone());
        pstmt.setString(11, eb.getEqpInitial());
        pstmt.setString(12, eb.getEqpNumber());
        pstmt.setString(13, eb.getChassisId());
        pstmt.setString(14, gierInfo.getCompanySCACCode());
        pstmt.setString(15, eb.getPortQualifier());
        pstmt.setString(16, eb.getPortIdentifier());
        pstmt.setString(17, eb.getDrvState());
        pstmt.setString(18, eb.getDrvLicNo());
        pstmt.setString(19, uiiaInfo.getDrvName());
        pstmt.setString(20, eb.getMcScac());
        pstmt.setString(21, eb.getMcName());
        pstmt.setString(22, eb.getRcdInfo());
        pstmt.setString(23, gierInfo.getUsDotNumber());
        pstmt.setString(24, uiiaInfo.getMcEin());
        pstmt.setString(25, uiiaInfo.getMcDot());
        pstmt.setString(26, uiiaInfo.getIddPin());
        pstmt.setString(27, eb.getQ5Details());
        pstmt.setString(28, eb.getN7Details());
        pstmt.setString(29, eb.getR4Details());
        pstmt.setString(30, eb.getN1Details());
        pstmt.setString(31, eb.getN1DrDetails());
        pstmt.setInt(32, bean.getNoDefectsCount());
        pstmt.setInt(33, bean.getBrakesCount());
        pstmt.setInt(34, bean.getLightsCount());
        pstmt.setInt(35, bean.getWheelCount());
        pstmt.setInt(36, bean.getAirlineCount());
        pstmt.setInt(37, bean.getCouplingCount());
        pstmt.setInt(38, bean.getFrameCount());
        pstmt.setInt(39, bean.getBolsterCount());
        pstmt.setInt(40, bean.getFastenerCount());
        pstmt.setInt(41, bean.getSliderCount());
        pstmt.setString(42, boescUserId);
        pstmt.setString(43, GlobalVariables.STATUS_PENDING);
        pstmt.setObject(44, DateTimeFormater.getSqlSysTimestamp());

        int dbStat = 0;
        int dvirKey = 0;
        dbStat = pstmt.executeUpdate();
        rs = pstmt.getGeneratedKeys();
        if (dbStat != 0) {
            if (rs != null) {
                while (rs.next()) {
                    dvirKey = rs.getInt(1);
                    logger.info("dvirKey: " + dvirKey);
                }
            }
        }
        if (dvirKey != 0) {
            conn.commit();
            //Update BOESC_UNIQUE_NO : using business logic
            String sql = "UPDATE DVIR_TRAN_SET SET DVIR_NO = ? WHERE DVIR_TRAN_ID = ? ";
            qrun.update(sql, new Object[] { Utility.addPadToUniqueNum(dvirKey, "DVIR-"), dvirKey });
            logger.info("Record Inserted successfully for DVIR..." + file.getName());
            return true;
        } else {
            conn.rollback();
            logger.error("Failure Data insertion in DVIR..");
        }
    } finally {

        try {
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException ex1) {
            logger.error(
                    "Caught SQL exception while closing prepared statement /resultset " + ex1.getMessage());
            ex1.printStackTrace();
            throw ex1;
        } catch (Exception e) {
            logger.error("Caught SQL exception in finally block " + e.getMessage());
            e.printStackTrace();
            throw e;
        }
    }
    return false;
}

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

public void processSyncSubclassStub(SyncSubclassStub stub) {
    Connection connection = sessionFactory.getCurrentSession().connection();

    boolean stubInsertNeeded = false;
    PreparedStatement ps = null;//from  w  ww.j  av  a  2s. c  o m
    int internalDatabaseId = 0;

    // check if there is a row with a matching person record and no patient record 
    try {
        // becomes something like "select person_id from person where uuid = x" or "select concept_id from concept where uuid = x"
        ps = connection.prepareStatement(
                "SELECT " + stub.getParentTableId() + " FROM " + stub.getParentTable() + " WHERE uuid = ?");
        ps.setString(1, stub.getUuid());
        ps.execute();
        ResultSet rs = ps.getResultSet();
        if (rs.next()) {
            stubInsertNeeded = true;
            internalDatabaseId = rs.getInt(stub.getParentTableId());
        } else {
            stubInsertNeeded = false;
        }

        //this should get no rows
        ps = connection.prepareStatement("SELECT " + stub.getSubclassTableId() + " FROM "
                + stub.getSubclassTable() + " WHERE " + stub.getSubclassTableId() + " = ?");
        ps.setInt(1, internalDatabaseId);
        ps.execute();
        if (ps.getResultSet().next()) {
            stubInsertNeeded = false;
        }
        ps.close();
        ps = null;

    } catch (SQLException e) {
        log.error(
                "Error while trying to see if this person is a patient already (or concept is a concept numeric already)",
                e);
    }
    if (ps != null) {
        try {
            ps.close();
        } catch (SQLException e) {
            log.error("Error generated while closing statement", e);
        }
    }

    if (stubInsertNeeded) {
        try {
            //insert the stub
            String sql = "INSERT INTO " + stub.getSubclassTable() + " (" + stub.getSubclassTableId()
                    + "COLUMNNAMEGOESHERE) VALUES (?COLUMNVALUEGOESHERE)";

            if (CollectionUtils.isNotEmpty(stub.getRequiredColumnNames())
                    && CollectionUtils.isNotEmpty(stub.getRequiredColumnValues())
                    && CollectionUtils.isNotEmpty(stub.getRequiredColumnClasses())) {
                for (int x = 0; x < stub.getRequiredColumnNames().size(); x++) {
                    String column = stub.getRequiredColumnNames().get(x);
                    sql = sql.replace("COLUMNNAMEGOESHERE", ", " + column + "COLUMNNAMEGOESHERE");
                    sql = sql.replace("COLUMNVALUEGOESHERE", ", ?COLUMNVALUEGOESHERE");
                }
            }

            sql = sql.replace("COLUMNNAMEGOESHERE", "");
            sql = sql.replace("COLUMNVALUEGOESHERE", "");

            ps = connection.prepareStatement(sql);

            ps.setInt(1, internalDatabaseId);

            if (CollectionUtils.isNotEmpty(stub.getRequiredColumnNames())
                    && CollectionUtils.isNotEmpty(stub.getRequiredColumnValues())
                    && CollectionUtils.isNotEmpty(stub.getRequiredColumnClasses())) {

                for (int x = 0; x < stub.getRequiredColumnValues().size(); x++) {
                    String value = stub.getRequiredColumnValues().get(x);
                    String className = stub.getRequiredColumnClasses().get(x);
                    Class c;
                    try {
                        c = Context.loadClass(className);
                        ps.setObject(x + 2, SyncUtil.getNormalizer(c).fromString(c, value));
                    } catch (ClassNotFoundException e) {
                        log.error("Unable to convert classname into a Class object " + className);
                    }

                }
            }

            ps.executeUpdate();

            //*and* create sync item for this
            HibernateSyncInterceptor.addSyncItemForSubclassStub(stub);
            log.debug("Sync Inserted " + stub.getParentTable() + " Stub for " + stub.getUuid());
        } catch (SQLException e) {
            log.warn("SQL Exception while trying to create a " + stub.getParentTable() + " stub", e);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    log.error("Error generated while closing statement", e);
                }
            }
        }
    }

    return;
}

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

@Override
public EventChanges getSync(AccessToken token, ObmUser calendarUser, Date lastSync, SyncRange syncRange,
        EventType typeFilter, boolean onEventDate) {

    PreparedStatement evps = null;
    ResultSet evrs = null;/*from   www .j av a2s .c  o  m*/
    Connection con = null;
    Calendar cal = getGMTCalendar();
    StringBuilder fetchIds = new StringBuilder();
    fetchIds.append("SELECT e.event_id, att.eventlink_state, e.event_ext_id, ex.eventexception_parent_id ");
    fetchIds.append(" FROM Event e ");
    fetchIds.append("INNER JOIN EventLink att ON att.eventlink_event_id=e.event_id ");
    fetchIds.append("INNER JOIN UserEntity ue ON att.eventlink_entity_id=ue.userentity_entity_id ");
    fetchIds.append("INNER JOIN EventLink attupd ON attupd.eventlink_event_id=e.event_id ");
    fetchIds.append("LEFT JOIN EventException ex ON ex.eventexception_child_id = e.event_id ");
    fetchIds.append("WHERE e.event_type=? AND ue.userentity_user_id=? ");

    // dirty hack to disable need-action to opush & tbird
    if (token.getOrigin().contains("push")) {
        fetchIds.append(" AND att.eventlink_state != 'NEEDS-ACTION' ");
    }

    if (lastSync != null) {
        fetchIds.append(
                " AND (e.event_timecreate >= ? OR e.event_timeupdate >= ? OR attupd.eventlink_timeupdate >= ?");
        if (onEventDate) {
            fetchIds.append(" OR e.event_date >= ? OR e.event_repeatkind != 'none'");
        }
        fetchIds.append(")");
    }

    if (syncRange != null) {
        fetchIds.append("AND (");
        fetchIds.append(
                "(e.event_repeatkind != 'none' AND (e.event_endrepeat IS NULL OR e.event_endrepeat >= ?)) OR ");

        fetchIds.append("(e.event_date >= ? ");
        if (syncRange.getBefore() != null) {
            fetchIds.append("AND e.event_date <= ? ");
        }
        fetchIds.append(") )");
        logger.info(token.getUserLogin() + " will use the sync range [ " + syncRange.getAfter() + " - "
                + syncRange.getBefore() + " ]");
    }

    fetchIds.append(" GROUP BY e.event_id, att.eventlink_state, e.event_ext_id, ex.eventexception_parent_id");

    List<DeletedEvent> declined = new LinkedList<DeletedEvent>();
    Set<Event> parentOfDeclinedRecurrentEvent = Sets.newHashSet();

    StringBuilder fetched = new StringBuilder();
    fetched.append("(0");
    boolean fetchedData = false;
    try {
        con = obmHelper.getConnection();
        evps = con.prepareStatement(fetchIds.toString());
        int idx = 1;
        evps.setObject(idx++, obmHelper.getDBCP().getJdbcObject(ObmHelper.VCOMPONENT, typeFilter.toString()));
        evps.setObject(idx++, calendarUser.getUid());
        if (lastSync != null) {
            evps.setTimestamp(idx++, new Timestamp(lastSync.getTime()));
            evps.setTimestamp(idx++, new Timestamp(lastSync.getTime()));
            evps.setTimestamp(idx++, new Timestamp(lastSync.getTime()));
            if (onEventDate) {
                evps.setTimestamp(idx++, new Timestamp(lastSync.getTime()));
            }
        }
        if (syncRange != null) {
            // Recurrent events
            evps.setTimestamp(idx++, new Timestamp(syncRange.getAfter().getTime()));
            // Non-recurrent events
            evps.setTimestamp(idx++, new Timestamp(syncRange.getAfter().getTime()));
            if (syncRange.getBefore() != null) {
                evps.setTimestamp(idx++, new Timestamp(syncRange.getBefore().getTime()));
            }
        }

        evrs = evps.executeQuery();
        while (evrs.next()) {
            int recurentParentId = evrs.getInt(4);
            State state = State.getValueOf(evrs.getString(2));
            Integer eventId = evrs.getInt(1);
            if (state == State.DECLINED) {
                if (recurentParentId == 0) {
                    declined.add(
                            DeletedEvent.builder().eventObmId(eventId).eventExtId(evrs.getString(3)).build());
                } else {
                    Event e = findEventById(token, new EventObmId(recurentParentId));
                    parentOfDeclinedRecurrentEvent.add(e);
                }
            } else {
                fetchedData = true;
                if (recurentParentId > 0) {
                    fetched.append(",");
                    fetched.append(recurentParentId);
                }
                fetched.append(",");
                fetched.append(eventId);
            }
        }
    } catch (Throwable t) {
        logger.error(t.getMessage(), t);
    } finally {
        obmHelper.cleanup(con, evps, evrs);
    }
    fetched.append(")");

    List<Event> changedEvent = new LinkedList<Event>();
    Map<EventObmId, Event> eventById = new HashMap<EventObmId, Event>();

    evps = null;
    evrs = null;
    con = null;

    Date lastSyncToBuild = null;
    if (fetchedData) {
        String ev = "SELECT " + EVENT_SELECT_FIELDS + ", eventexception_date as recurrence_id "
                + " FROM Event e " + "INNER JOIN EventEntity ON e.event_id=evententity_event_id "
                + "INNER JOIN UserObm o ON e.event_owner=o.userobm_id "
                + "INNER JOIN UserObm c ON e.event_usercreate=c.userobm_id "
                + "INNER JOIN Domain ON e.event_domain_id=domain_id "
                + "LEFT JOIN EventCategory1 ON e.event_category1_id=eventcategory1_id "
                + "LEFT JOIN EventException ON e.event_id = eventexception_child_id " + "WHERE e.event_id IN "
                + fetched.toString();

        try {
            con = obmHelper.getConnection();
            evps = con.prepareStatement(ev);
            evrs = evps.executeQuery();
            boolean lastSyncSet = false;
            while (evrs.next()) {
                if (!lastSyncSet) {
                    lastSyncToBuild = JDBCUtils.getDate(evrs, "last_sync");
                    lastSyncSet = true;
                }

                Event e = eventFromCursor(cal, evrs);
                eventById.put(e.getObmId(), e);
                changedEvent.add(e);
            }
        } catch (SQLException e) {
            logger.error("error getting events", e);
        } finally {
            obmHelper.cleanup(con, evps, evrs);
        }
    }

    if (lastSyncToBuild == null) {
        if (lastSync != null) {
            Connection conDate = null;
            try {
                conDate = obmHelper.getConnection();
                Date newLastSync = obmHelper.selectNow(conDate);
                if (newLastSync != null) {
                    lastSyncToBuild = newLastSync;
                } else {
                    lastSyncToBuild = lastSync;
                }
            } catch (SQLException e) {
                logger.error("error updating lastsync field", e);
            } finally {
                obmHelper.cleanup(conDate, null, null);
            }

        } else {
            Calendar ls = Calendar.getInstance();
            ls.set(Calendar.YEAR, 1970);
            lastSyncToBuild = ls.getTime();
        }
    }

    Connection conComp = null;
    Date touchDateForFakeExDates;
    try {
        conComp = obmHelper.getConnection();
        touchDateForFakeExDates = obmHelper.selectNow(conComp);
        if (!changedEvent.isEmpty()) {
            IntegerIndexedSQLCollectionHelper changedIds = new IntegerIndexedSQLCollectionHelper(changedEvent);
            loadAttendees(conComp, eventById, calendarUser.getDomain().getName());
            loadAlerts(conComp, token, eventById, changedIds);
            loadExceptions(conComp, cal, eventById, changedIds);
            loadEventExceptions(conComp, token, eventById, changedIds);
        }

        touchParentOfDeclinedRecurrentEvents(parentOfDeclinedRecurrentEvent, changedEvent,
                touchDateForFakeExDates);

        if (!changedEvent.isEmpty()) {
            replaceDeclinedEventExceptionByException(calendarUser, changedEvent);
        }
    } catch (SQLException e) {
        logger.error("error loading attendees, alerts, exceptions, eventException", e);
    } finally {
        obmHelper.cleanup(conComp, null, null);
    }

    Iterable<DeletedEvent> deletedEvents = Iterables.concat(
            findDeletedEvents(calendarUser, lastSync, typeFilter, declined),
            findDeletedEventsLinks(calendarUser, lastSync));

    EventChanges syncEventChanges = EventChanges.builder().lastSync(lastSyncToBuild).updates(changedEvent)
            .deletes(Sets.newHashSet(deletedEvents)).build();

    return syncEventChanges;
}

From source file:org.apache.openjpa.jdbc.sql.DBDictionary.java

/**
 * Set the given value as a parameter to the statement. The column
 * type will come from {@link Types}.//from ww w  .j a v a2 s .c  o  m
 */
public void setObject(PreparedStatement stmnt, int idx, Object val, int colType, Column col)
        throws SQLException {
    if (colType == -1 || colType == Types.OTHER)
        stmnt.setObject(idx, val);
    else
        stmnt.setObject(idx, val, colType);
}

From source file:com.draagon.meta.manager.db.driver.GenericSQLDriver.java

/**
 * Sets a specific value on a prepared statement
 *//*  w  w w.ja  v  a  2  s .  c om*/
protected void setStatementValue(PreparedStatement s, MetaField f, int index, Object value)
        throws SQLException {
    int j = index;

    switch (f.getType()) {
    case MetaField.BOOLEAN: {
        if (value == null) {
            s.setNull(j, Types.BIT);
        } else if (value instanceof Boolean) {
            s.setBoolean(j, ((Boolean) value).booleanValue());
        } else {
            s.setBoolean(j, Boolean.valueOf(value.toString()).booleanValue());
        }
    }
        break;

    case MetaField.BYTE: {
        if (value == null) {
            s.setNull(j, Types.TINYINT);
        } else if (value instanceof Byte) {
            s.setByte(j, ((Byte) value).byteValue());
        } else {
            s.setByte(j, Byte.valueOf(value.toString()).byteValue());
        }
    }
        break;

    case MetaField.SHORT: {
        if (value == null) {
            s.setNull(j, Types.SMALLINT);
        } else if (value instanceof Short) {
            s.setShort(j, ((Short) value).shortValue());
        } else {
            s.setShort(j, Short.valueOf(value.toString()).shortValue());
        }
    }
        break;

    case MetaField.INT: {
        if (value == null) {
            s.setNull(j, Types.INTEGER);
        } else if (value instanceof Integer) {
            s.setInt(j, ((Integer) value).intValue());
        } else {
            s.setInt(j, Integer.valueOf(value.toString()).intValue());
        }
    }
        break;

    case MetaField.DATE: // NOTE DATE IS TREATED AS LONG!
    {
        if (value == null) {
            s.setNull(j, Types.TIMESTAMP);
        } else if (value instanceof java.util.Date) {
            s.setTimestamp(j, new Timestamp(((java.util.Date) value).getTime()));
        } else {
            s.setTimestamp(j, new Timestamp(Long.valueOf(value.toString()).longValue()));
        }
    }
        break;

    case MetaField.LONG: {
        if (value == null) {
            s.setNull(j, Types.BIGINT);
        } else if (value instanceof Long) {
            s.setLong(j, ((Long) value).longValue());
        } else {
            s.setLong(j, Long.valueOf(value.toString()).longValue());
        }
    }
        break;

    // WARNING:  This should not be a valid key
    case MetaField.FLOAT: {
        if (value == null) {
            s.setNull(j, Types.FLOAT);
        } else if (value instanceof Float) {
            s.setFloat(j, ((Float) value).floatValue());
        } else {
            s.setFloat(j, Float.valueOf(value.toString()).floatValue());
        }
    }
        break;

    // WARNING:  This should not be a valid key
    case MetaField.DOUBLE: {
        if (value == null) {
            s.setNull(j, Types.DOUBLE);
        } else if (value instanceof Double) {
            s.setDouble(j, ((Double) value).doubleValue());
        } else {
            s.setDouble(j, Double.valueOf(value.toString()).doubleValue());
        }
    }
        break;

    case MetaField.STRING:
        if (value == null) {
            s.setNull(j, Types.VARCHAR);
        } else {
            s.setString(j, value.toString());
        }
        break;

    case MetaField.OBJECT:
        //if ( value == null )
        //  s.setNull( j, Types.BLOB );
        //else
        s.setObject(j, value);
        break;
    }
}

From source file:com.oltpbenchmark.benchmarks.seats.SEATSLoader.java

/**
 * // ww w  . j  a v a  2 s . co m
 * @param catalog_tbl
 */
public void loadTable(Table catalog_tbl, Iterable<Object[]> iterable, int batch_size) {
    // Special Case: Airport Locations
    final boolean is_airport = catalog_tbl.getName().equals(SEATSConstants.TABLENAME_AIRPORT);

    if (LOG.isDebugEnabled())
        LOG.debug(String.format("Generating new records for table %s [batchSize=%d]", catalog_tbl.getName(),
                batch_size));
    final List<Column> columns = catalog_tbl.getColumns();

    // Check whether we have any special mappings that we need to maintain
    Map<Integer, Integer> code_2_id = new HashMap<Integer, Integer>();
    Map<Integer, Map<String, Long>> mapping_columns = new HashMap<Integer, Map<String, Long>>();
    for (int col_code_idx = 0, cnt = columns.size(); col_code_idx < cnt; col_code_idx++) {
        Column catalog_col = columns.get(col_code_idx);
        String col_name = catalog_col.getName();

        // Code Column -> Id Column Mapping
        // Check to see whether this table has columns that we need to map their
        // code values to tuple ids
        String col_id_name = this.profile.code_columns.get(col_name);
        if (col_id_name != null) {
            Column catalog_id_col = catalog_tbl.getColumnByName(col_id_name);
            assert (catalog_id_col != null) : "The id column " + catalog_tbl.getName() + "." + col_id_name
                    + " is missing";
            int col_id_idx = catalog_tbl.getColumnIndex(catalog_id_col);
            code_2_id.put(col_code_idx, col_id_idx);
        }

        // Foreign Key Column to Code->Id Mapping
        // If this columns references a foreign key that is used in the Code->Id mapping
        // that we generating above, then we need to know when we should change the 
        // column value from a code to the id stored in our lookup table
        if (this.profile.fkey_value_xref.containsKey(col_name)) {
            String col_fkey_name = this.profile.fkey_value_xref.get(col_name);
            mapping_columns.put(col_code_idx, this.profile.code_id_xref.get(col_fkey_name));
        }
    } // FOR

    int row_idx = 0;
    int row_batch = 0;

    try {
        String insert_sql = SQLUtil.getInsertSQL(catalog_tbl);
        PreparedStatement insert_stmt = this.conn.prepareStatement(insert_sql);
        int sqlTypes[] = catalog_tbl.getColumnTypes();

        for (Object tuple[] : iterable) {
            assert (tuple[0] != null) : "The primary key for " + catalog_tbl.getName() + " is null";

            // AIRPORT 
            if (is_airport) {
                // Skip any airport that does not have flights
                int col_code_idx = catalog_tbl.getColumnByName("AP_CODE").getIndex();
                if (profile.hasFlights((String) tuple[col_code_idx]) == false) {
                    if (LOG.isTraceEnabled())
                        LOG.trace(String.format("Skipping AIRPORT '%s' because it does not have any flights",
                                tuple[col_code_idx]));
                    continue;
                }

                // Update the row # so that it matches what we're actually loading
                int col_id_idx = catalog_tbl.getColumnByName("AP_ID").getIndex();
                tuple[col_id_idx] = (long) (row_idx + 1);

                // Store Locations
                int col_lat_idx = catalog_tbl.getColumnByName("AP_LATITUDE").getIndex();
                int col_lon_idx = catalog_tbl.getColumnByName("AP_LONGITUDE").getIndex();
                Pair<Double, Double> coords = Pair.of((Double) tuple[col_lat_idx], (Double) tuple[col_lon_idx]);
                if (coords.first == null || coords.second == null) {
                    LOG.error(Arrays.toString(tuple));
                }
                assert (coords.first != null) : String.format("Unexpected null latitude for airport '%s' [%d]",
                        tuple[col_code_idx], col_lat_idx);
                assert (coords.second != null) : String.format(
                        "Unexpected null longitude for airport '%s' [%d]", tuple[col_code_idx], col_lon_idx);
                this.airport_locations.put(tuple[col_code_idx].toString(), coords);
                if (LOG.isTraceEnabled())
                    LOG.trace(String.format("Storing location for '%s': %s", tuple[col_code_idx], coords));
            }

            // Code Column -> Id Column
            for (int col_code_idx : code_2_id.keySet()) {
                assert (tuple[col_code_idx] != null) : String.format(
                        "The value of the code column at '%d' is null for %s\n%s", col_code_idx,
                        catalog_tbl.getName(), Arrays.toString(tuple));
                String code = tuple[col_code_idx].toString().trim();
                if (code.length() > 0) {
                    Column from_column = columns.get(col_code_idx);
                    assert (from_column != null);
                    Column to_column = columns.get(code_2_id.get(col_code_idx));
                    assert (to_column != null) : String.format("Invalid column %s.%s", catalog_tbl.getName(),
                            code_2_id.get(col_code_idx));
                    long id = (Long) tuple[code_2_id.get(col_code_idx)];
                    if (LOG.isTraceEnabled())
                        LOG.trace(String.format("Mapping %s '%s' -> %s '%d'", from_column.fullName(), code,
                                to_column.fullName(), id));
                    this.profile.code_id_xref.get(to_column.getName()).put(code, id);
                }
            } // FOR

            // Foreign Key Code -> Foreign Key Id
            for (int col_code_idx : mapping_columns.keySet()) {
                Column catalog_col = columns.get(col_code_idx);
                assert (tuple[col_code_idx] != null || catalog_col.isNullable()) : String.format(
                        "The code %s column at '%d' is null for %s id=%s\n%s", catalog_col.fullName(),
                        col_code_idx, catalog_tbl.getName(), tuple[0], Arrays.toString(tuple));
                if (tuple[col_code_idx] != null) {
                    String code = tuple[col_code_idx].toString();
                    tuple[col_code_idx] = mapping_columns.get(col_code_idx).get(code);
                    if (LOG.isTraceEnabled())
                        LOG.trace(String.format("Mapped %s '%s' -> %s '%s'", catalog_col.fullName(), code,
                                catalog_col.getForeignKey().fullName(), tuple[col_code_idx]));
                }
            } // FOR

            for (int i = 0; i < tuple.length; i++) {
                try {
                    if (tuple[i] != null) {
                        insert_stmt.setObject(i + 1, tuple[i]);
                    } else {
                        insert_stmt.setNull(i + 1, sqlTypes[i]);
                    }
                } catch (SQLDataException ex) {
                    LOG.error("INVALID " + catalog_tbl.getName() + " TUPLE: " + Arrays.toString(tuple));
                    throw new RuntimeException("Failed to set value for " + catalog_tbl.getColumn(i).fullName(),
                            ex);
                }
            } // FOR
            insert_stmt.addBatch();
            row_idx++;

            if (++row_batch >= batch_size) {
                LOG.debug(String.format("Loading %s batch [total=%d]", catalog_tbl.getName(), row_idx));
                insert_stmt.executeBatch();
                conn.commit();
                insert_stmt.clearBatch();
                row_batch = 0;
            }

        } // FOR

        if (row_batch > 0) {
            insert_stmt.executeBatch();
            conn.commit();
        }
        insert_stmt.close();
    } catch (Exception ex) {
        throw new RuntimeException("Failed to load table " + catalog_tbl.getName(), ex);
    }

    if (is_airport)
        assert (this.profile.getAirportCount() == row_idx) : String.format("%d != %d",
                profile.getAirportCount(), row_idx);

    // Record the number of tuples that we loaded for this table in the profile
    if (catalog_tbl.getName().equals(SEATSConstants.TABLENAME_RESERVATION)) {
        this.profile.num_reservations = row_idx + 1;
    }

    LOG.info(String.format("Finished loading all %d tuples for %s [%d / %d]", row_idx, catalog_tbl.getName(),
            this.finished.incrementAndGet(), this.getCatalog().getTableCount()));
    return;
}

From source file:org.apache.marmotta.kiwi.persistence.KiWiConnection.java

/**
 * Store a new node in the database. The method will retrieve a new database id for the node and update the
 * passed object. Afterwards, the node data will be inserted into the database using appropriate INSERT
 * statements. The caller must make sure the connection is committed and closed properly.
 * <p/>/* w w w  .  j a va2  s  .c o m*/
 * If the node already has an ID, the method will do nothing (assuming that it is already persistent)
 *
 *
 * @param node
 * @throws SQLException
 */
public synchronized void storeNode(KiWiNode node) throws SQLException {

    // ensure the data type of a literal is persisted first
    if (node instanceof KiWiLiteral) {
        KiWiLiteral literal = (KiWiLiteral) node;
        if (literal.getType() != null && literal.getType().getId() < 0) {
            storeNode(literal.getType());
        }
    }

    requireJDBCConnection();

    // retrieve a new node id and set it in the node object
    if (node.getId() < 0) {
        node.setId(getNextSequence());
    }

    // distinguish the different node types and run the appropriate updates
    if (node instanceof KiWiUriResource) {
        KiWiUriResource uriResource = (KiWiUriResource) node;

        PreparedStatement insertNode = getPreparedStatement("store.uri");
        insertNode.setLong(1, node.getId());
        insertNode.setString(2, uriResource.stringValue());
        insertNode.setTimestamp(3, new Timestamp(uriResource.getCreated().getTime()), calendarUTC);

        insertNode.executeUpdate();

    } else if (node instanceof KiWiAnonResource) {
        KiWiAnonResource anonResource = (KiWiAnonResource) node;

        PreparedStatement insertNode = getPreparedStatement("store.bnode");
        insertNode.setLong(1, node.getId());
        insertNode.setString(2, anonResource.stringValue());
        insertNode.setTimestamp(3, new Timestamp(anonResource.getCreated().getTime()), calendarUTC);

        insertNode.executeUpdate();
    } else if (node instanceof KiWiDateLiteral) {
        KiWiDateLiteral dateLiteral = (KiWiDateLiteral) node;

        PreparedStatement insertNode = getPreparedStatement("store.tliteral");
        insertNode.setLong(1, node.getId());
        insertNode.setString(2, dateLiteral.stringValue());
        insertNode.setTimestamp(3, new Timestamp(dateLiteral.getDateContent().getMillis()), calendarUTC);
        insertNode.setInt(4,
                dateLiteral.getDateContent().getZone().getOffset(dateLiteral.getDateContent()) / 1000);
        if (dateLiteral.getType() != null)
            insertNode.setLong(5, dateLiteral.getType().getId());
        else
            throw new IllegalStateException("a date literal must have a datatype");
        insertNode.setTimestamp(6, new Timestamp(dateLiteral.getCreated().getTime()), calendarUTC);

        insertNode.executeUpdate();
    } else if (node instanceof KiWiIntLiteral) {
        KiWiIntLiteral intLiteral = (KiWiIntLiteral) node;

        PreparedStatement insertNode = getPreparedStatement("store.iliteral");
        insertNode.setLong(1, node.getId());
        insertNode.setString(2, intLiteral.getContent());
        insertNode.setDouble(3, intLiteral.getDoubleContent());
        insertNode.setLong(4, intLiteral.getIntContent());
        if (intLiteral.getType() != null)
            insertNode.setLong(5, intLiteral.getType().getId());
        else
            throw new IllegalStateException("an integer literal must have a datatype");
        insertNode.setTimestamp(6, new Timestamp(intLiteral.getCreated().getTime()), calendarUTC);

        insertNode.executeUpdate();
    } else if (node instanceof KiWiDoubleLiteral) {
        KiWiDoubleLiteral doubleLiteral = (KiWiDoubleLiteral) node;

        PreparedStatement insertNode = getPreparedStatement("store.dliteral");
        insertNode.setLong(1, node.getId());
        insertNode.setString(2, doubleLiteral.getContent());
        insertNode.setDouble(3, doubleLiteral.getDoubleContent());
        if (doubleLiteral.getType() != null)
            insertNode.setLong(4, doubleLiteral.getType().getId());
        else
            throw new IllegalStateException("a double literal must have a datatype");
        insertNode.setTimestamp(5, new Timestamp(doubleLiteral.getCreated().getTime()), calendarUTC);

        insertNode.executeUpdate();
    } else if (node instanceof KiWiBooleanLiteral) {
        KiWiBooleanLiteral booleanLiteral = (KiWiBooleanLiteral) node;

        PreparedStatement insertNode = getPreparedStatement("store.bliteral");
        insertNode.setLong(1, node.getId());
        insertNode.setString(2, booleanLiteral.getContent());
        insertNode.setBoolean(3, booleanLiteral.booleanValue());
        if (booleanLiteral.getType() != null)
            insertNode.setLong(4, booleanLiteral.getType().getId());
        else
            throw new IllegalStateException("a boolean literal must have a datatype");
        insertNode.setTimestamp(5, new Timestamp(booleanLiteral.getCreated().getTime()), calendarUTC);

        insertNode.executeUpdate();
    } else if (node instanceof KiWiStringLiteral) {
        KiWiStringLiteral stringLiteral = (KiWiStringLiteral) node;

        Double dbl_value = null;
        Long lng_value = null;
        if (stringLiteral.getContent().length() < 64 && NumberUtils.isNumber(stringLiteral.getContent()))
            try {
                dbl_value = Double.parseDouble(stringLiteral.getContent());
                lng_value = Long.parseLong(stringLiteral.getContent());
            } catch (NumberFormatException ex) {
                // ignore, keep NaN
            }

        PreparedStatement insertNode = getPreparedStatement("store.sliteral");
        insertNode.setLong(1, node.getId());
        insertNode.setString(2, stringLiteral.getContent());
        if (dbl_value != null) {
            insertNode.setDouble(3, dbl_value);
        } else {
            insertNode.setObject(3, null);
        }
        if (lng_value != null) {
            insertNode.setLong(4, lng_value);
        } else {
            insertNode.setObject(4, null);
        }

        if (stringLiteral.getLocale() != null) {
            insertNode.setString(5, stringLiteral.getLocale().getLanguage().toLowerCase());
        } else {
            insertNode.setObject(5, null);
        }
        if (stringLiteral.getType() != null) {
            insertNode.setLong(6, stringLiteral.getType().getId());
        } else {
            insertNode.setObject(6, null);
        }
        insertNode.setTimestamp(7, new Timestamp(stringLiteral.getCreated().getTime()), calendarUTC);

        insertNode.executeUpdate();
    } else {
        log.warn("unrecognized node type: {}", node.getClass().getCanonicalName());
    }

    cacheNode(node);
}

From source file:com.pari.nm.utils.db.InventoryDBHelper.java

public static void insertConfig(Integer nodeId, int confType, String config) {
    PreparedStatement ps = null;
    Connection c = null;//from  ww  w.  j a v a  2s .  co m
    try {
        c = DBHelper.getConnection();
        ps = c.prepareStatement("insert into node_current_config (id, conf_type, config) VALUES (?,?,?)");
        ps.setInt(1, nodeId);
        ps.setInt(2, confType);
        byte b[] = CompressionUtils
                .compressInputStream(new ByteArrayInputStream(config.getBytes(Charset.forName("UTF-8"))));
        ps.setObject(3, b);
        ps.executeUpdate();
    } catch (Exception ex) {
        logger.error("Exception while inserting " + confType + " configuration for node: " + nodeId
                + " can not update.", ex);
    } finally {
        try {
            if (ps != null) {
                ps.close();
            }
        } catch (Exception ex) {
        } finally {
            if (c != null) {
                DBHelper.releaseConnection(c);
            }
        }
    }

}

From source file:com.pari.nm.utils.db.InventoryDBHelper.java

public static void saveShowCommand(int nodeId, String cli, String output) {
    try {/*from w ww .j a  va2 s  . c  om*/
        String query = "delete from show_commands_new where device_id=" + nodeId + " and cli='" + cli + "'";
        DBHelper.executeUpdate(query);
    } catch (Exception ex) {
        logger.error("Exception while deleting show command : " + nodeId + " and cli='" + cli + "'", ex);
    }
    PreparedStatement ps = null;
    Connection c = null;
    if (output == null || output.trim().isEmpty()) {
        return;
    }

    output = output.trim();

    try {
        c = DBHelper.getConnection();
        ps = c.prepareStatement("INSERT INTO show_commands_new (device_id, cli, out_put) VALUES (?,?,?)");
        if (output.startsWith(cli)) {
            output = output.substring(cli.length());
        }
        ps.setInt(1, nodeId);
        ps.setString(2, cli);
        ps.setObject(3, CompressionUtils.getCompressedString(output));

        ps.executeUpdate();
    } catch (Exception ee) {
        logger.warn("Error while saving show cli for the node = " + nodeId + " and cli = " + cli, ee);
    } finally {
        try {
            if (ps != null) {
                ps.close();
            }
        } catch (Exception e) {
        }

        try {
            DBHelper.releaseConnection(c);
        } catch (Exception ee) {
        }
    }
}