Example usage for java.sql PreparedStatement setBoolean

List of usage examples for java.sql PreparedStatement setBoolean

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

Introduction

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

Prototype

void setBoolean(int parameterIndex, boolean x) throws SQLException;

Source Link

Document

Sets the designated parameter to the given Java boolean value.

Usage

From source file:com.flexive.core.storage.genericSQL.GenericTreeStorage.java

/**
 * Clear a tree nodes dirty flag//from w  ww .java 2s  . c  om
 *
 * @param con    an open and valid connection
 * @param mode   tree mode
 * @param nodeId node id
 * @throws FxUpdateException on errors
 */
protected void clearDirtyFlag(Connection con, FxTreeMode mode, long nodeId) throws FxUpdateException {
    PreparedStatement ps = null;
    try {
        //                                                                 1                                                              2
        ps = con.prepareStatement("UPDATE " + getTable(mode) + " SET DIRTY=?,MODIFIED_AT="
                + StorageManager.getTimestampFunction() + " WHERE ID=?");
        ps.setBoolean(1, false); //live tree is never dirty
        ps.setLong(2, nodeId);
        ps.executeUpdate();
    } catch (SQLException e) {
        throw new FxUpdateException(LOG, e, "ex.tree.update.failed", mode.name(), e.getMessage());
    } finally {
        try {
            if (ps != null)
                ps.close();
        } catch (SQLException e) {
            //ignore
        }
    }
}

From source file:com.flexive.core.storage.genericSQL.GenericTreeStorage.java

/**
 * Flag a tree node dirty/*w  ww  .  j  a  va  2s .co m*/
 *
 * @param con    an open and valid connection
 * @param mode   tree mode
 * @param nodeId node id
 * @throws FxUpdateException on errors
 */
protected void flagDirty(Connection con, FxTreeMode mode, long nodeId) throws FxUpdateException {
    PreparedStatement ps = null;
    try {
        //                                                                 1                                                              2
        ps = con.prepareStatement("UPDATE " + getTable(mode) + " SET DIRTY=?,MODIFIED_AT="
                + StorageManager.getTimestampFunction() + " WHERE ID=?");
        ps.setBoolean(1, mode != FxTreeMode.Live); //live tree is never dirty
        ps.setLong(2, nodeId);
        ps.executeUpdate();
    } catch (SQLException e) {
        throw new FxUpdateException(LOG, e, "ex.tree.update.failed", mode.name(), e.getMessage());
    } finally {
        try {
            if (ps != null)
                ps.close();
        } catch (SQLException e) {
            //ignore
        }
    }
}

From source file:com.flexive.ejb.beans.UserGroupEngineBean.java

/**
 * {@inheritDoc}//  w  w w  . java  2s. co m
 */
@Override
@TransactionAttribute(TransactionAttributeType.REQUIRED)
public void rebuildMandatorGroups() throws FxApplicationException {
    Connection con = null;
    PreparedStatement ps = null;
    Statement stmt = null;
    String sql = "";
    try {
        con = Database.getDbConnection();
        stmt = con.createStatement();
        List<Mandator> missing = new ArrayList<Mandator>(5);
        for (Mandator m : CacheAdmin.getEnvironment().getMandators(true, true)) {
            try {
                UserGroup g = loadMandatorGroup(m.getId());
                sql = "DELETE FROM " + TBL_ASSIGN_GROUPS + " WHERE USERGROUP=" + g.getId();
                stmt.executeUpdate(sql);
                sql = "INSERT INTO " + TBL_ASSIGN_GROUPS + " (ACCOUNT,USERGROUP) (SELECT a.id, " + g.getId()
                        + " FROM " + TBL_ACCOUNTS + " a WHERE a.MANDATOR=" + m.getId() + " AND a.ID!="
                        + Account.NULL_ACCOUNT + ")";
                stmt.executeUpdate(sql);
            } catch (FxNotFoundException e) {
                missing.add(m);
            }
        }
        final long NOW = System.currentTimeMillis();
        for (Mandator m : missing) {
            sql = "INSERT INTO " + TBL_USERGROUPS + " "
                    + "(ID,MANDATOR,AUTOMANDATOR,ISSYSTEM,NAME,COLOR,CREATED_BY,CREATED_AT,MODIFIED_BY,MODIFIED_AT) VALUES ("
                    + "?,?,?,?,?,?,?,?,?,?)";
            if (ps == null)
                ps = con.prepareStatement(sql);
            long gid = seq.getId(FxSystemSequencer.GROUP);
            ps.setLong(1, gid);
            ps.setLong(2, m.getId());
            ps.setLong(3, m.getId());
            ps.setBoolean(4, true); //ISSYSTEM
            ps.setString(5, "Everyone (" + m.getName() + ")");
            ps.setString(6, "#00AA00");
            ps.setLong(7, 0);
            ps.setLong(8, NOW);
            ps.setLong(9, 0);
            ps.setLong(10, NOW);
            ps.executeUpdate();
            sql = "INSERT INTO " + TBL_ASSIGN_GROUPS + " (ACCOUNT,USERGROUP) (SELECT a.ID, " + gid + " FROM "
                    + TBL_ACCOUNTS + " a WHERE a.MANDATOR=" + m.getId() + " AND a.ID!=" + Account.NULL_ACCOUNT
                    + ")";
            stmt.executeUpdate(sql);
        }

        StructureLoader.updateUserGroups(FxContext.get().getDivisionId(), loadAll(-1));
    } catch (SQLException exc) {
        FxLoadException le = new FxLoadException(exc, "ex.usergroup.sqlError", exc.getMessage(), sql);
        LOG.error(le);
        throw le;
    } finally {
        if (ps != null)
            try {
                ps.close();
            } catch (SQLException e) {
                //ignore
            }
        Database.closeObjects(UserGroupEngineBean.class, con, stmt);
    }
}

From source file:org.apache.hadoop.hive.jdbc.TestJdbcDriver.java

public void testPrepareStatement() {

    String sql = "from (select count(1) from " + tableName
            + " where   'not?param?not?param' <> 'not_param??not_param' and ?=? "
            + " and 1=? and 2=? and 3.0=? and 4.0=? and 'test\\'string\"'=? and 5=? and ?=? "
            + " and date '2012-01-01' = date ?"
            + " ) t  select '2011-03-25' ddate,'China',true bv, 10 num limit 10";

    ///////////////////////////////////////////////
    //////////////////// correct testcase
    //////////////////// executed twice: once with the typed ps setters, once with the generic setObject
    //////////////////////////////////////////////
    try {//from w ww.  jav a2 s  .  c o  m
        PreparedStatement ps = createPreapredStatementUsingSetXXX(sql);
        ResultSet res = ps.executeQuery();
        assertPreparedStatementResultAsExpected(res);
        ps.close();

        ps = createPreapredStatementUsingSetObject(sql);
        res = ps.executeQuery();
        assertPreparedStatementResultAsExpected(res);
        ps.close();

    } catch (Exception e) {
        e.printStackTrace();
        fail(e.toString());
    }

    ///////////////////////////////////////////////
    //////////////////// other failure testcases
    //////////////////////////////////////////////
    // set nothing for prepared sql
    Exception expectedException = null;
    try {
        PreparedStatement ps = con.prepareStatement(sql);
        ps.executeQuery();
    } catch (Exception e) {
        expectedException = e;
    }
    assertNotNull("Execute the un-setted sql statement should throw exception", expectedException);

    // set some of parameters for prepared sql, not all of them.
    expectedException = null;
    try {
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setBoolean(1, true);
        ps.setBoolean(2, true);
        ps.executeQuery();
    } catch (Exception e) {
        expectedException = e;
    }
    assertNotNull("Execute the invalid setted sql statement should throw exception", expectedException);

    // set the wrong type parameters for prepared sql.
    expectedException = null;
    try {
        PreparedStatement ps = con.prepareStatement(sql);

        // wrong type here
        ps.setString(1, "wrong");

        assertTrue(true);
        ResultSet res = ps.executeQuery();
        if (!res.next()) {
            throw new Exception("there must be a empty result set");
        }
    } catch (Exception e) {
        expectedException = e;
    }
    assertNotNull("Execute the invalid setted sql statement should throw exception", expectedException);

    // setObject to the yet unknown type java.util.Date
    expectedException = null;
    try {
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setObject(1, new Date());
        ps.executeQuery();
    } catch (Exception e) {
        expectedException = e;
    }
    assertNotNull("Setting to an unknown type should throw an exception", expectedException);

}

From source file:com.concursive.connect.web.modules.documents.dao.FileItem.java

/**
 * Description of the Method//from w  w w  .  j  a  v a2s . c o  m
 *
 * @param db          Description of the Parameter
 * @param thisVersion Description of the Parameter
 * @return Description of the Return Value
 * @throws SQLException Description of the Exception
 */
public boolean updateVersion(Connection db, FileItemVersion thisVersion) throws SQLException {
    // Set the master record
    subject = thisVersion.getSubject();
    clientFilename = thisVersion.getClientFilename();
    filename = thisVersion.getFilename();
    version = thisVersion.getVersion();
    size = thisVersion.getSize();
    enteredBy = thisVersion.getEnteredBy();
    modifiedBy = thisVersion.getModifiedBy();
    comment = thisVersion.getComment();
    modified = thisVersion.getModified();
    // Update the master record
    int i = 0;
    PreparedStatement pst = db.prepareStatement("UPDATE project_files "
            + "SET subject = ?, client_filename = ?, filename = ?, version = ?, "
            + "size = ?, modifiedby = ?, modified = ?, comment = ?, featured_file = ? " + "WHERE item_id = ? ");
    pst.setString(++i, subject);
    pst.setString(++i, clientFilename);
    pst.setString(++i, filename);
    pst.setDouble(++i, version);
    pst.setInt(++i, size);
    pst.setInt(++i, modifiedBy);
    pst.setTimestamp(++i, modified);
    pst.setString(++i, comment);
    pst.setBoolean(++i, featuredFile);
    pst.setInt(++i, this.getId());
    pst.execute();
    pst.close();
    return true;
}

From source file:com.flexive.core.storage.genericSQL.GenericTreeStorage.java

/**
 * {@inheritDoc}/*from   w  ww.  j  av  a  2s  . c  om*/
 */
@Override
public List<FxTreeNode> getNodesWithReference(Connection con, FxTreeMode mode, long referenceId)
        throws FxApplicationException {
    PreparedStatement ps = null;
    List<FxTreeNode> ret = new ArrayList<FxTreeNode>(20);
    long _id = -1;
    try {
        ps = con.prepareStatement(
                prepareSql(mode, mode == FxTreeMode.Live ? TREE_LIVE_GETNODE_REF : TREE_EDIT_GETNODE_REF));
        List<Long> acls = null;
        ps.setBoolean(1, mode == FxTreeMode.Live);
        ps.setLong(2, referenceId);
        ps.setBoolean(3, true);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            _id = rs.getLong(1);
            FxPK _ref = new FxPK(rs.getLong(2), rs.getInt(14));
            int _depth = rs.getInt(3);
            int _totalChilds = rs.getInt(4);
            int _directChilds = rs.getInt(5);
            String _name = rs.getString(6);
            long _parent = rs.getLong(7);
            boolean _dirty = rs.getBoolean(8);
            String _data = rs.getString(9);
            if (rs.wasNull())
                _data = null;
            long _modified = rs.getLong(10);
            int _pos = rs.getInt(11);
            long _acl = rs.getLong(12);
            FxType _type = CacheAdmin.getEnvironment().getType(rs.getLong(13));
            long _stepACL = CacheAdmin.getEnvironment().getStep(rs.getLong(15)).getAclId();
            long _createdBy = rs.getLong(16);
            long _mandator = rs.getLong(17);
            if (acls == null) {
                // fetch ACLs only once, since they are only dependent on the reference instance
                acls = fetchACLs(con, _ref, _acl);
            }
            UserTicket ticket = FxContext.getUserTicket();
            boolean _read;
            boolean _edit;
            boolean _create;
            boolean _delete;
            boolean _export;
            boolean _relate;
            final boolean _system = FxContext.get().getRunAsSystem() || ticket.isGlobalSupervisor();
            FxPermissionUtils.checkPermission(ticket, _createdBy, ACLPermission.READ, _type, _stepACL, acls,
                    true);
            if (_system || ticket.isMandatorSupervisor() && _mandator == ticket.getMandatorId()
                    || !_type.isUsePermissions()
                    || ticket.isInGroup((int) UserGroup.GROUP_OWNER) && _createdBy == ticket.getUserId()) {
                _read = _edit = _create = _delete = _export = _relate = true;
            } else {
                //throw exception if read is forbidden
                _read = FxPermissionUtils.checkPermission(ticket, _createdBy, ACLPermission.READ, _type,
                        _stepACL, acls, false);
                _edit = FxPermissionUtils.checkPermission(ticket, _createdBy, ACLPermission.EDIT, _type,
                        _stepACL, acls, false);
                _relate = FxPermissionUtils.checkPermission(ticket, _createdBy, ACLPermission.RELATE, _type,
                        _stepACL, acls, false);
                _delete = FxPermissionUtils.checkPermission(ticket, _createdBy, ACLPermission.DELETE, _type,
                        _stepACL, acls, false);
                _export = FxPermissionUtils.checkPermission(ticket, _createdBy, ACLPermission.EXPORT, _type,
                        _stepACL, acls, false);
                _create = FxPermissionUtils.checkPermission(ticket, _createdBy, ACLPermission.CREATE, _type,
                        _stepACL, acls, false);
            }
            if (!_read)
                continue;
            final String TRUE = StorageManager.getBooleanTrueExpression();
            FxString label = Database.loadContentDataFxString(con, "FTEXT1024", "ID=" + _ref.getId() + " AND "
                    + (mode == FxTreeMode.Live ? "ISMAX_VER=" + TRUE : "ISLIVE_VER=" + TRUE) + " AND TPROP="
                    + EJBLookup.getConfigurationEngine().get(SystemParameters.TREE_CAPTION_PROPERTY));
            final FxLock lock;
            final long lockUser = rs.getLong(18);
            if (rs.wasNull())
                lock = FxLock.noLockPK();
            else
                lock = new FxLock(FxLockType.getById(rs.getInt(19)), rs.getLong(20), rs.getLong(21), lockUser,
                        _ref);
            FxTreeNode node = new FxTreeNode(mode, lock, _id, _parent, _ref,
                    LifeCycleInfoImpl.load(rs, 16, 22, 23, 24), _type.getId(), acls, _name,
                    getPathById(con, mode, _id), label, _pos, new ArrayList<FxTreeNode>(0),
                    new ArrayList<Long>(0), _depth, _directChilds, _directChilds == 0, _dirty, _modified, _data,
                    _edit, _create, _delete, _relate, _export);
            ret.add(node);
        }
    } catch (SQLException exc) {
        throw new FxLoadException(LOG, "ex.tree.load.failed.node", _id, mode, exc.getMessage());
    } finally {
        try {
            if (ps != null)
                ps.close();
        } catch (Exception exc) {
            /*ignore*/}
    }
    return ret;
}

From source file:com.flexive.ejb.beans.structure.TypeEngineBean.java

/**
 * Create a new type/*w w w. j  a  va 2 s  .com*/
 *
 * @param type the type to create
 * @return id of the new type
 * @throws FxApplicationException on errors
 */
private long create(FxTypeEdit type) throws FxApplicationException {
    final UserTicket ticket = FxContext.getUserTicket();
    FxPermissionUtils.checkRole(ticket, Role.StructureManagement);
    final FxEnvironment environment = CacheAdmin.getEnvironment();
    if (StringUtils.isEmpty(type.getName()))
        throw new FxInvalidParameterException("NAME", "ex.structure.create.nameMissing");
    if (!type.getStorageMode().isSupported())
        throw new FxInvalidParameterException("STORAGEMODE", "ex.structure.typeStorageMode.notSupported",
                type.getStorageMode().getLabel().getBestTranslation(ticket));
    if (type.getACL().getCategory() != ACLCategory.STRUCTURE)
        throw new FxInvalidParameterException("aclId", "ex.acl.category.invalid",
                type.getACL().getCategory().name(), ACLCategory.STRUCTURE.name());
    if (type.hasDefaultInstanceACL() && type.getDefaultInstanceACL().getCategory() != ACLCategory.INSTANCE)
        throw new FxInvalidParameterException("DEFACL", "ex.acl.category.invalid",
                type.getDefaultInstanceACL().getCategory(), ACLCategory.INSTANCE);
    Connection con = null;
    PreparedStatement ps = null;
    long newId = seq.getId(FxSystemSequencer.TYPEDEF);
    final long NOW = System.currentTimeMillis();
    try {
        con = Database.getDbConnection();
        ps = con.prepareStatement(TYPE_CREATE);
        ps.setLong(1, newId);
        ps.setString(2, type.getName());
        if (type.getParent() != null)
            ps.setLong(3, type.getParent().getId());
        else
            ps.setNull(3, java.sql.Types.INTEGER);
        ps.setInt(4, type.getStorageMode().getId());
        ps.setInt(5, type.getCategory().getId());
        ps.setInt(6, type.getMode().getId());
        ps.setInt(7, type.getLanguage().getId());
        ps.setInt(8, type.getState().getId());
        ps.setByte(9, type.getBitCodedPermissions());
        ps.setBoolean(10, type.isTrackHistory());
        ps.setLong(11, type.getHistoryAge());
        ps.setLong(12, type.getMaxVersions());
        ps.setInt(13, type.getMaxRelSource());
        ps.setInt(14, type.getMaxRelDestination());
        ps.setLong(15, ticket.getUserId());
        ps.setLong(16, NOW);
        ps.setLong(17, ticket.getUserId());
        ps.setLong(18, NOW);
        ps.setLong(19, type.getACL().getId());
        ps.setLong(20, type.getWorkflow().getId());
        if (type.getIcon().isEmpty())
            ps.setNull(21, java.sql.Types.INTEGER);
        else
            ps.setLong(21, type.getIcon().getDefaultTranslation().getId());
        ps.setBoolean(22, type.isUseInstancePermissions() && type.isMultipleContentACLs());
        ps.setBoolean(23, type.isIncludedInSupertypeQueries());
        if (type.hasDefaultInstanceACL())
            ps.setLong(24, type.getDefaultInstanceACL().getId());
        else
            ps.setNull(24, java.sql.Types.INTEGER);
        ps.setBoolean(25, type.isAutoVersion());
        ps.executeUpdate();
        Database.storeFxString(type.getLabel(), con, TBL_STRUCT_TYPES, "DESCRIPTION", "ID", newId);

        StructureLoader.reload(con);
        FxType thisType = CacheAdmin.getEnvironment().getType(newId);
        htracker.track(thisType, "history.type.create", type.getName(), newId);

        //store relations
        ps.close();
        if (type.getAddedRelations().size() > 0) {
            ps = con.prepareStatement("INSERT INTO " + TBL_STRUCT_TYPERELATIONS
                    + " (TYPEDEF,TYPESRC,TYPEDST,MAXSRC,MAXDST)VALUES(?,?,?,?,?)");
            ps.setLong(1, thisType.getId());
            for (FxTypeRelation rel : type.getAddedRelations()) {
                if (rel.getSource().isRelation())
                    throw new FxInvalidParameterException("ex.structure.type.relation.wrongTarget",
                            type.getName(), rel.getSource().getName());
                if (rel.getDestination().isRelation())
                    throw new FxInvalidParameterException("ex.structure.type.relation.wrongTarget",
                            type.getName(), rel.getDestination().getName());
                ps.setLong(2, rel.getSource().getId());
                ps.setLong(3, rel.getDestination().getId());
                ps.setLong(4, rel.getMaxSource());
                ps.setLong(5, rel.getMaxDestination());
                ps.addBatch();
                htracker.track(thisType, "history.type.create.relation.add", type.getName(),
                        rel.getSource().getName(), rel.getMaxSource(), rel.getDestination().getName(),
                        rel.getMaxDestination());
            }
            ps.executeBatch();
        }

        if (type.getParent() == null) {
            for (FxPropertyAssignment spa : environment.getSystemInternalRootPropertyAssignments()) {
                final FxPropertyAssignmentEdit derived = FxPropertyAssignmentEdit.createNew(spa, thisType,
                        spa.getAlias(), "/");
                assignmentEngine.save(
                        updateAclAssignmentMultiplicity(type, derived).setEnabled(true)._setSystemInternal(),
                        false);
            }
        } else {
            //create parent assignments
            List<FxAssignment> parentAssignments = type.getParent().getConnectedAssignments("/");
            for (FxAssignment as : parentAssignments) {
                if (as instanceof FxPropertyAssignment) {
                    FxPropertyAssignmentEdit pae = FxPropertyAssignmentEdit.createNew((FxPropertyAssignment) as,
                            thisType, as.getAlias(), "/");
                    pae.setEnabled(type.isEnableParentAssignments());
                    assignmentEngine.save(updateAclAssignmentMultiplicity(type, pae), false);
                } else if (as instanceof FxGroupAssignment) {
                    FxGroupAssignmentEdit pge = FxGroupAssignmentEdit.createNew((FxGroupAssignment) as,
                            thisType, as.getAlias(), "/");
                    pge.setEnabled(type.isEnableParentAssignments());
                    assignmentEngine.save(pge, true);
                }
            }
        }

        // store structure options
        storeTypeOptions(con, TBL_STRUCT_TYPES_OPTIONS, "ID", newId, type.getOptions(), false);

        StructureLoader.reload(con);
    } catch (SQLException e) {
        if (StorageManager.isUniqueConstraintViolation(e)) {
            EJBUtils.rollback(ctx);
            throw new FxCreateException("ex.structure.type.exists", type.getName());
        }
        EJBUtils.rollback(ctx);
        throw new FxCreateException(LOG, e, "ex.db.sqlError", e.getMessage());
    } catch (FxCacheException e) {
        EJBUtils.rollback(ctx);
        throw new FxCreateException(LOG, e, "ex.cache", e.getMessage());
    } catch (FxLoadException e) {
        EJBUtils.rollback(ctx);
        throw new FxCreateException(e);
    } catch (FxNotFoundException e) {
        EJBUtils.rollback(ctx);
        throw new FxCreateException(e);
    } catch (FxEntryExistsException e) {
        EJBUtils.rollback(ctx);
        throw new FxCreateException(e);
    } catch (FxUpdateException e) {
        EJBUtils.rollback(ctx);
        throw new FxCreateException(e);
    } finally {
        Database.closeObjects(TypeEngineBean.class, con, ps);
    }
    return newId;
}

From source file:HSqlManager.java

public static void uniqueDB(Connection connection, int bps) throws ClassNotFoundException, SQLException,
        InstantiationException, IllegalAccessException, IOException {
    DpalLoad.main(new String[1]);
    HSqlPrimerDesign.Dpal_Inst = DpalLoad.INSTANCE_WIN64;
    String base = new File("").getAbsolutePath();
    if (!written) {
        CSV.makeDirectory(new File(base + "/PhageData"));
        INSTANCE.readFileAll(INSTANCE.path).stream().forEach(x -> {
            try {
                CSV.writeDataCSV(x[1], Fasta.process(x[1], bps), bps);
            } catch (IOException e) {
                e.printStackTrace();/*ww w  .  jav a 2s.c  o  m*/
            }
        });
    }
    Connection db = connection;
    db.setAutoCommit(false);
    Statement stat = db.createStatement();
    PrintWriter log = new PrintWriter(new File("javalog.log"));
    stat.execute("SET FILES LOG FALSE;\n");
    PreparedStatement st = db
            .prepareStatement("UPDATE Primerdb.Primers" + " SET UniqueP = true, Tm = ?, GC =?, Hairpin =?"
                    + "WHERE Cluster = ? and Strain = ? and " + "Sequence = ? and Bp = ?");
    ResultSet call = stat.executeQuery("Select * From Primerdb.Phages;");
    List<String[]> phages = new ArrayList<>();
    while (call.next()) {
        String[] r = new String[3];
        r[0] = call.getString("Strain");
        r[1] = call.getString("Cluster");
        r[2] = call.getString("Name");
        phages.add(r);
    }
    phages.stream().map(x -> x[0]).collect(Collectors.toSet()).stream().forEach(x -> {
        phages.stream().filter(y -> y[0].equals(x)).map(y -> y[1]).collect(Collectors.toSet()).parallelStream()
                .forEach(z -> {
                    try {
                        Set<String> nonclustphages = phages.stream()
                                .filter(a -> a[0].equals(x) && !a[1].equals(z)).map(a -> a[2])
                                .collect(Collectors.toSet());
                        ResultSet resultSet = stat.executeQuery("Select Sequence from primerdb.primers"
                                + " where Strain ='" + x + "' and Cluster ='" + z + "' and CommonP = true"
                                + " and Bp = " + Integer.valueOf(bps) + " ");
                        Set<CharSequence> primers = Collections.synchronizedSet(new HashSet<>());
                        while (resultSet.next()) {
                            primers.add(resultSet.getString("Sequence"));
                        }
                        for (String phage : nonclustphages) {
                            CSV.readCSV(base + "/PhageData/" + Integer.toString(bps) + phage + ".csv")
                                    .parallelStream().filter(primer -> primers.contains(primer))
                                    .forEach(primers::remove);

                        }
                        int i = 0;
                        for (CharSequence a : primers) {
                            try {
                                st.setDouble(1, HSqlPrimerDesign.primerTm(a, 0, 800, 1.5, 0.2));
                                st.setDouble(2, HSqlPrimerDesign.gcContent(a));
                                st.setBoolean(3, HSqlPrimerDesign.calcHairpin((String) a, 4));
                                st.setString(4, z);
                                st.setString(5, x);
                                st.setString(6, a.toString());
                                st.setInt(7, bps);
                                st.addBatch();
                            } catch (SQLException e) {
                                e.printStackTrace();
                                System.out.println("Error occurred at " + x + " " + z);
                            }
                            i++;
                            if (i == 1000) {
                                i = 0;
                                st.executeBatch();
                                db.commit();
                            }
                        }
                        if (i > 0) {
                            st.executeBatch();
                            db.commit();
                        }
                    } catch (SQLException e) {
                        e.printStackTrace();
                        System.out.println("Error occurred at " + x + " " + z);
                    }
                    log.println(z);
                    log.flush();
                    System.gc();
                });
    });
    stat.execute("SET FILES LOG TRUE\n");
    st.close();
    stat.close();
    System.out.println("Unique Updated");
}

From source file:com.flexive.core.storage.genericSQL.GenericTreeStorageSimple.java

/**
 * {@inheritDoc}//from w  w w .  ja  v  a2  s .  c  om
 */
@Override
public long createNode(Connection con, SequencerEngine seq, ContentEngine ce, FxTreeMode mode, long nodeId,
        long parentNodeId, String name, FxString label, int position, FxPK reference, String data,
        boolean activateContent) throws FxApplicationException {
    checkDataValue(data);
    NodeCreateInfo nci = getNodeCreateInfo(mode, seq, ce, nodeId, name, label, reference, true);
    FxTreeNodeInfo parentNode = getTreeNodeInfo(con, mode, parentNodeId);
    long left, right;

    if (position < 0)
        position = 0;
    else if (position > parentNode.getDirectChildCount())
        position = parentNode.getDirectChildCount();

    // Create the node
    PreparedStatement ps = null;
    try {
        if (position == 0) {
            //first entry
            left = parentNode.getLeft().longValue() + 1;
        } else if (parentNode.getDirectChildCount() >= position) {
            //last entry
            left = parentNode.getRight().longValue();
        } else {
            //inbetween - calculate needed left/right slot
            ps = con.prepareStatement("SELECT RGT FROM " + getTable(mode)
                    + " WHERE PARENT=? ORDER BY LFT LIMIT 1 OFFSET " + position);
            ps.setLong(1, parentNodeId);
            ResultSet rs = ps.executeQuery();
            if (rs == null || !rs.next()) {
                throw new FxTreeException("ex.tree.create.failed.positioning", parentNodeId, position);
            }
            left = rs.getLong(1);
            ps.close();
        }
        right = left + 1;
        //"move" nodes to the right to make space
        ps = con.prepareStatement("UPDATE " + getTable(mode) + " SET RGT=RGT+2 WHERE RGT>=?");
        ps.setLong(1, left);
        ps.executeUpdate();
        ps.close();
        ps = con.prepareStatement("UPDATE " + getTable(mode) + " SET LFT=LFT+2 WHERE LFT>?");
        ps.setLong(1, right);
        ps.executeUpdate();
        ps.close();
        //insert the new node
        ps = con.prepareStatement("INSERT INTO " + getTable(mode) + " (ID,PARENT,DEPTH,DIRTY,REF,LFT,RGT,"
                + "CHILDCOUNT,NAME,MODIFIED_AT,TEMPLATE) VALUES " + "(" + nci.id + "," + parentNodeId + ","
                + (parentNode.getDepth() + 1) + ",?," + nci.reference.getId() + ",?,?,0,0,?,"
                + StorageManager.getTimestampFunction() + ",?)");
        ps.setBoolean(1, mode != FxTreeMode.Live);
        ps.setLong(2, left);
        ps.setLong(3, right);
        ps.setString(4, nci.name);
        if (StringUtils.isEmpty(data)) {
            ps.setNull(5, java.sql.Types.VARCHAR);
        } else {
            ps.setString(6, data);
        }
        ps.executeUpdate();
        ps.close();

        //update the parents childcount
        ps = con.prepareStatement(
                "UPDATE " + getTable(mode) + " SET CHILDCOUNT=CHILDCOUNT+1 WHERE ID=" + parentNodeId);
        ps.executeUpdate();
    } catch (SQLException e) {
        throw new FxTreeException(e, "ex.db.sqlError", e.getMessage());
    } finally {
        try {
            if (ps != null)
                ps.close();
        } catch (Throwable t) {
            /*ignore*/
        }
    }
    return nci.id;
}

From source file:gemlite.core.internal.db.AsyncEventHelper.java

/**
 * Set column value at given index in a prepared statement. The implementation
 * tries using the matching underlying type to minimize any data type
 * conversions, and avoid creating wrapper Java objects (e.g. {@link Integer}
 * for primitive int)./* w ww  .  ja  v a 2 s . com*/
 * 
 * @param type
 *          the SQL type of the column as specified by JDBC {@link Types}
 *          class
 * @param ps
 *          the prepared statement where the column value has to be set
 * @param row
 *          the source row as a {@link ResultSet} from where the value has to
 *          be extracted
 * @param rowPosition
 *          the 1-based position of the column in the provided
 *          <code>row</code>
 * @param paramIndex
 *          the 1-based position of the column in the target prepared
 *          statement (provided <code>ps</code> argument)
 * @param sync
 *          the {@link DBSynchronizer} object, if any; it is used to store
 *          whether the current driver is JDBC4 compliant to enable performing
 *          BLOB/CLOB operations {@link PreparedStatement#setBinaryStream},
 *          {@link PreparedStatement#setCharacterStream}
 * 
 * @throws SQLException
 *           in case of an exception in setting parameters
 */
public final void setColumnInPrepStatement(String type, Object val, PreparedStatement ps,
        final DBSynchronizer sync, int paramIndex) throws SQLException {
    switch (type) {
    case JavaTypes.STRING:
        if (val == null || StringUtils.isEmpty(val.toString()))
            ps.setNull(paramIndex, Types.VARCHAR);
        else {
            final String realVal = (String) val;
            ps.setString(paramIndex, realVal);
        }
        break;
    case JavaTypes.INT1:
    case JavaTypes.INT2:
    case JavaTypes.INT3:
        if (val == null || StringUtils.isEmpty(val.toString()))
            ps.setNull(paramIndex, Types.INTEGER);
        else {
            final int realVal = (int) val;
            ps.setInt(paramIndex, realVal);
        }
        break;
    case JavaTypes.DOUBLE1:
    case JavaTypes.DOUBLE2:
        if (val == null || StringUtils.isEmpty(val.toString()))
            ps.setNull(paramIndex, Types.DOUBLE);
        else {
            final double realVal = (double) val;
            ps.setDouble(paramIndex, realVal);
        }
        break;
    case JavaTypes.FLOAT1:
    case JavaTypes.FLOAT2:
        if (val == null || StringUtils.isEmpty(val.toString()))
            ps.setNull(paramIndex, Types.FLOAT);
        else {
            final float realVal = (float) val;
            ps.setDouble(paramIndex, realVal);
        }
        break;
    case JavaTypes.BOOLEAN1:
    case JavaTypes.BOOLEAN2:
        if (val == null || StringUtils.isEmpty(val.toString()))
            ps.setNull(paramIndex, Types.BOOLEAN);
        else {
            final boolean realVal = (boolean) val;
            ps.setBoolean(paramIndex, realVal);
        }
        break;
    case JavaTypes.DATE_SQL:
        if (val == null || StringUtils.isEmpty(val.toString()))
            ps.setNull(paramIndex, Types.DATE);
        else {
            final Date realVal = (Date) val;
            ps.setDate(paramIndex, realVal);
        }
        break;
    case JavaTypes.DATE_UTIL:
        if (val == null || StringUtils.isEmpty(val.toString()))
            ps.setNull(paramIndex, Types.DATE);
        else {
            final java.util.Date realVal = (java.util.Date) val;
            ps.setDate(paramIndex, new Date(realVal.getTime()));
        }
        break;
    case JavaTypes.BIGDECIMAL:
        if (val == null || StringUtils.isEmpty(val.toString()))
            ps.setNull(paramIndex, Types.DECIMAL);
        else {
            final BigDecimal realVal = (BigDecimal) val;
            ps.setBigDecimal(paramIndex, realVal);
        }
        break;
    case JavaTypes.TIME:
        if (val == null || StringUtils.isEmpty(val.toString()))
            ps.setNull(paramIndex, Types.TIME);
        else {
            final Time realVal = (Time) val;
            ps.setTime(paramIndex, realVal);
        }
        break;
    case JavaTypes.TIMESTAMP:
        if (val == null || StringUtils.isEmpty(val.toString()))
            ps.setNull(paramIndex, Types.TIMESTAMP);
        else {
            final Timestamp realVal = (Timestamp) val;
            ps.setTimestamp(paramIndex, realVal);
        }
        break;
    case JavaTypes.OBJECT:
        if (val == null || StringUtils.isEmpty(val.toString()))
            ps.setNull(paramIndex, Types.JAVA_OBJECT);
        else {
            final Object realVal = (Object) val;
            ps.setObject(paramIndex, realVal);
        }
        break;
    default:
        throw new UnsupportedOperationException("java.sql.Type = " + type + " not supported");
    }
}