Example usage for java.sql PreparedStatement setLong

List of usage examples for java.sql PreparedStatement setLong

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

Introduction

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

Prototype

void setLong(int parameterIndex, long x) throws SQLException;

Source Link

Document

Sets the designated parameter to the given Java long value.

Usage

From source file:iddb.runtime.db.model.dao.impl.mysql.PenaltyDAOImpl.java

@Override
public List<Penalty> findByPlayerAndType(Long player, Integer type, int offset, int limit, int[] count) {
    String sqlCount = "select count(id) from penalty where playerid = ? and type = ?";
    String sql = "select * from penalty where playerid = ? and type = ? order by created desc limit ?,?";
    Connection conn = null;//from   w w  w.j  a  v a2s  . c  o m
    List<Penalty> list = new ArrayList<Penalty>();
    try {
        conn = ConnectionFactory.getSecondaryConnection();
        PreparedStatement stC = conn.prepareStatement(sqlCount);
        stC.setLong(1, player);
        stC.setInt(2, type.intValue());
        ResultSet rsC = stC.executeQuery(sqlCount);
        if (rsC.next()) {
            count[0] = rsC.getInt(1);
        }
        PreparedStatement st = conn.prepareStatement(sql);
        st.setLong(1, player);
        st.setInt(2, type.intValue());
        st.setInt(3, offset);
        st.setInt(4, limit);
        ResultSet rs = st.executeQuery();
        while (rs.next()) {
            Penalty penalty = new Penalty();
            loadPenalty(penalty, rs);
            list.add(penalty);
        }
    } catch (SQLException e) {
        logger.error("findByPlayerAndType: {}", e);
    } catch (IOException e) {
        logger.error("findByPlayerAndType: {}", e);
    } finally {
        try {
            if (conn != null)
                conn.close();
        } catch (Exception e) {
        }
    }
    return list;
}

From source file:com.buckwa.dao.impl.excise4.Form24DaoImpl.java

@Override
public void create(final Form24 form24) {
    KeyHolder keyHolder = new GeneratedKeyHolder();
    final StringBuilder sql = new StringBuilder();
    sql.append(/*from  w  w  w . j a va 2 s .c  o m*/
            "INSERT INTO `form24`(`form24_id`,`industry_id`,`factory_id`,`create_date`,`create_by`,`update_date`,`update_by`,step,industry_name,tax_no,factory_name) ")
            .append(" VALUES ( NULL,?,?,?,?,?,?,?,?,?,?)");

    logger.info("SQL : " + sql.toString());

    String user = "";
    try {
        user = BuckWaUtils.getUserNameFromContext();
    } catch (BuckWaException e) {
        e.printStackTrace();
    }
    final String userName = user;

    jdbcTemplate.update(new PreparedStatementCreator() {
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            Timestamp currentDate = new Timestamp(System.currentTimeMillis());
            final Industry industry = form24.getIndustry();
            final Factory factory = form24.getFactory();

            PreparedStatement ps = connection.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);
            ps.setLong(1, industry.getIndustryId());
            if (factory.getFactoryId() != null) {
                ps.setLong(2, factory.getFactoryId());
            } else {
                ps.setNull(2, java.sql.Types.BIGINT);
            }
            ps.setTimestamp(3, currentDate);
            ps.setString(4, userName);
            ps.setTimestamp(5, currentDate);
            ps.setString(6, userName);
            ps.setString(7, form24.getStep());
            ps.setString(8, industry.getIndustryName());
            ps.setString(9, industry.getTaxNo());
            ps.setString(10, factory.getFactoryName());
            return ps;
        }

    }, keyHolder);

    final Long returnidform24 = keyHolder.getKey().longValue();
    form24.setForm24Id(returnidform24);
    logger.info("returnidform24 : " + returnidform24);

    //ID PRODUCT
    List<Product> products = form24.getProductList();
    if (products != null) {
        final StringBuilder psql = new StringBuilder();
        psql.append(
                "INSERT INTO `form24_product`(`form24_id`,`seq`,`productName`,`size`,`bandColor`,`backgroudColor`,`licenseNo`,`grossnumber200`,`grossnumber400`,`corkScrap`,`totalScrap`,`create_date`,`create_by`, `update_date`,`update_by`,`product_id`) ")
                .append("VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NULL)");

        logger.info("SQL : " + psql.toString());

        for (final Product p : products) {

            jdbcTemplate.update(new PreparedStatementCreator() {
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    Timestamp currentDate = new Timestamp(System.currentTimeMillis());
                    PreparedStatement ps = connection.prepareStatement(psql.toString(),
                            Statement.RETURN_GENERATED_KEYS);
                    ps.setLong(1, returnidform24);
                    ps.setString(2, p.getSeq());
                    ps.setString(3, p.getProductName());
                    ps.setString(4, p.getSize());
                    ps.setString(5, p.getBandColor());
                    ps.setString(6, p.getBackgroudColor());
                    ps.setString(7, p.getLicenseNo());
                    ps.setBigDecimal(8, p.getGrossnumber200());
                    ps.setBigDecimal(9, p.getGrossnumber400());
                    ps.setBigDecimal(10, p.getCorkScrap());
                    ps.setBigDecimal(11, p.getTotalScrap());

                    ps.setTimestamp(12, currentDate);
                    ps.setString(13, userName);
                    ps.setTimestamp(14, currentDate);
                    ps.setString(15, userName);
                    return ps;
                }

            }, keyHolder);

            long returnidproduct = keyHolder.getKey().longValue();
            p.setProcuctId(returnidproduct);
            logger.info("returnidproduct : " + returnidproduct);
        }
    }
}

From source file:com.l2jfree.gameserver.model.clan.L2ClanMember.java

public void updateSubPledgeType() {
    Connection con = null;/*from  w ww. jav a2s  .  c  om*/

    try {
        con = L2DatabaseFactory.getInstance().getConnection(con);
        PreparedStatement statement = con.prepareStatement("UPDATE characters SET subpledge=? WHERE charId=?");
        statement.setLong(1, _subPledgeType);
        statement.setInt(2, getObjectId());
        statement.execute();
        statement.close();
    } catch (Exception e) {
        _log.warn("could not set char subpledge:", e);
    } finally {
        L2DatabaseFactory.close(con);
    }
}

From source file:com.jagornet.dhcp.db.JdbcLeaseManager.java

/**
 * Insert dhcp lease.// w w  w . jav a 2s  .c o  m
 *
 * @param lease the lease
 */
protected void insertDhcpLease(final DhcpLease lease) {
    getJdbcTemplate().update("insert into dhcplease" + " (ipaddress, duid, iatype, iaid, prefixlen, state,"
            + " starttime, preferredendtime, validendtime," + " ia_options, ipaddr_options)"
            + " values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", new PreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps) throws SQLException {
                    ps.setBytes(1, lease.getIpAddress().getAddress());
                    ps.setBytes(2, lease.getDuid());
                    ps.setByte(3, lease.getIatype());
                    ps.setLong(4, lease.getIaid());
                    ps.setShort(5, lease.getPrefixLength());
                    ps.setByte(6, lease.getState());
                    java.sql.Timestamp sts = new java.sql.Timestamp(lease.getStartTime().getTime());
                    ps.setTimestamp(7, sts, Util.GMT_CALENDAR);
                    java.sql.Timestamp pts = new java.sql.Timestamp(lease.getPreferredEndTime().getTime());
                    ps.setTimestamp(8, pts, Util.GMT_CALENDAR);
                    java.sql.Timestamp vts = new java.sql.Timestamp(lease.getValidEndTime().getTime());
                    ps.setTimestamp(9, vts, Util.GMT_CALENDAR);
                    ps.setBytes(10, encodeOptions(lease.getIaDhcpOptions()));
                    ps.setBytes(11, encodeOptions(lease.getIaAddrDhcpOptions()));
                }
            });
}

From source file:com.surfs.storage.common.datasource.jdbc.JdbcDao.java

@Override
public <T> List<T> queryForList(String poolName, String sql, RowMapper<T> mapper, Object... params)
        throws Exception {
    Connection conn = null;/*from   ww  w. j  a v a  2 s . c o  m*/
    PreparedStatement ps = null;
    ResultSet rs = null;
    List<T> list = new ArrayList<>();
    try {
        conn = getConnection(poolName);
        ps = conn.prepareStatement(sql);
        for (int i = 0; i < params.length; i++) {
            if (params[i] instanceof String)
                ps.setString(i + 1, (String) params[i]);
            else if (params[i] instanceof Integer)
                ps.setInt(i + 1, (Integer) params[i]);
            else if (params[i] instanceof Long)
                ps.setLong(i + 1, (Long) params[i]);
            else if (params[i] instanceof Timestamp)
                ps.setTimestamp(i + 1, (Timestamp) params[i]);
        }
        rs = ps.executeQuery();
        while (rs.next()) {
            T t = mapper.mapRow(rs);
            list.add(t);
        }
        return list;
    } catch (Exception e) {
        throw e;
    } finally {
        JdbcUtils.closeResultset(rs);
        JdbcUtils.closeStatement(ps);
        JdbcUtils.closeConnect(conn);
    }
}

From source file:net.sf.infrared.collector.impl.persistence.ApplicationStatisticsDaoImpl.java

void saveLayerTimes(final ApplicationStatistics stats) {
    final String appName = stats.getApplicationName();
    final String instanceId = stats.getInstanceId();

    final String[] layers = stats.getLayers();

    getJdbcTemplate().batchUpdate(SQL_INSERT_LAYER_TIME, new BatchPreparedStatementSetter() {
        public int getBatchSize() {
            return layers.length;
        }/*from w w  w. j a  v  a  2s.c o m*/

        public void setValues(PreparedStatement ps, int i) throws SQLException {
            ps.setString(1, appName);
            ps.setString(2, instanceId);
            ps.setString(3, layers[i]);
            ps.setLong(4, stats.getTimeInLayer(layers[i]));
            ps.setTimestamp(5, new Timestamp(System.currentTimeMillis()));
        }
    });
    if (log.isDebugEnabled()) {
        log.debug("Saved " + layers.length + " layer times in " + stats + " to DB");
    }
}

From source file:net.mindengine.oculus.frontend.service.project.JdbcProjectDAO.java

@Override
public Long createProject(Project project) throws Exception {

    PreparedStatement ps = getConnection().prepareStatement(
            "insert into projects (name, description, path, parent_id, icon, author_id, date) values (?,?,?,?,?,?,?)",
            Statement.RETURN_GENERATED_KEYS);

    ps.setString(1, project.getName());//from  w  w  w.j ava 2  s.  c o  m
    ps.setString(2, project.getDescription());
    ps.setString(3, project.getPath());
    ps.setLong(4, project.getParentId());
    ps.setString(5, project.getIcon());
    ps.setLong(6, project.getAuthorId());
    ps.setTimestamp(7, new Timestamp(project.getDate().getTime()));

    ps.executeUpdate();
    ResultSet rs = ps.getGeneratedKeys();
    Long projectId = null;
    if (rs.next()) {
        projectId = rs.getLong(1);
    }

    if (project.getParentId() > 0) {
        // Increasing the parents project subprojects_count var
        update("update projects set subprojects_count = subprojects_count+1 where id = :id", "id",
                project.getParentId());
    }

    return projectId;
}

From source file:com.nabla.wapp.server.basic.general.ImportService.java

@Override
public String executeAction(final HttpServletRequest request, final List<FileItem> sessionFiles)
        throws UploadActionException {
    final UserSession userSession = UserSession.load(request);
    if (userSession == null) {
        if (log.isTraceEnabled())
            log.trace("missing user session");
        throw new UploadActionException("permission denied");
    }/*from w w  w.  ja va 2s . c  o  m*/
    Assert.state(sessionFiles.size() == 1);
    try {
        for (FileItem file : sessionFiles) {
            if (file.isFormField())
                continue;
            if (log.isDebugEnabled()) {
                log.debug("field '" + file.getFieldName() + "': uploading " + file.getName());
                log.debug("field: " + file.getFieldName());
                log.debug("filename: " + file.getName());
                log.debug("content_type: " + file.getContentType());
                log.debug("size: " + file.getSize());
            }
            final Connection conn = db.getConnection();
            try {
                final PreparedStatement stmt = conn.prepareStatement(
                        "INSERT INTO import_data (field_name, file_name, content_type, length, content, userSessionId) VALUES(?,?,?,?,?,?);",
                        Statement.RETURN_GENERATED_KEYS);
                try {
                    stmt.setString(1, file.getFieldName());
                    stmt.setString(2, file.getName());
                    stmt.setString(3, file.getContentType());
                    stmt.setLong(4, file.getSize());
                    stmt.setString(6, userSession.getSessionId());
                    final InputStream fs = file.getInputStream();
                    try {
                        stmt.setBinaryStream(5, fs);
                        if (stmt.executeUpdate() != 1) {
                            if (log.isErrorEnabled())
                                log.error("failed to add imported file record");
                            throw new UploadActionException("internal error");
                        }
                        final ResultSet rsKey = stmt.getGeneratedKeys();
                        try {
                            rsKey.next();
                            final Integer id = rsKey.getInt(1);
                            if (log.isDebugEnabled())
                                log.debug(
                                        "uploading " + file.getName() + " successfully completed. id = " + id);
                            return id.toString();
                        } finally {
                            rsKey.close();
                        }
                    } finally {
                        fs.close();
                    }
                } catch (IOException e) {
                    if (log.isErrorEnabled())
                        log.error("error reading file " + file.getName(), e);
                    throw new UploadActionException("internal error");
                } finally {
                    Database.close(stmt);
                }
            } finally {
                // remove any orphan import records i.e. older than 48h (beware of timezone!)
                final Calendar dt = Util.dateToCalendar(new Date());
                dt.add(GregorianCalendar.DATE, -2);
                try {
                    Database.executeUpdate(conn, "DELETE FROM import_data WHERE created < ?;",
                            Util.calendarToSqlDate(dt));
                } catch (final SQLException __) {
                }
                Database.close(conn);
            }
        }
    } catch (SQLException e) {
        if (log.isErrorEnabled())
            log.error("error uploading file", e);
        throw new UploadActionException("internal error");
    } finally {
        super.removeSessionFileItems(request);
    }
    return null;
}

From source file:fr.gael.dhus.database.liquibase.CopyProductImages.java

@Override
public void execute(Database database) throws CustomChangeException {
    PreparedStatement products = null;
    ResultSet products_res = null;
    JdbcConnection db_connection = (JdbcConnection) database.getConnection();
    try {//  ww w . j av  a 2 s  .  com
        products = db_connection.prepareStatement("SELECT ID,QUICKLOOK,THUMBNAIL FROM PRODUCTS");
        products_res = products.executeQuery();
        while (products_res.next()) {
            PreparedStatement copy_blob_stmt = null;
            ResultSet generated_key_res = null;
            try {
                Blob ql = (Blob) products_res.getObject("QUICKLOOK");
                Blob th = (Blob) products_res.getObject("THUMBNAIL");
                Long pid = products_res.getLong("ID");

                // No images: add false flags
                if ((ql == null) && (th == null)) {
                    PreparedStatement product_flags_stmt = null;
                    // Add related flags
                    try {
                        product_flags_stmt = db_connection.prepareStatement(
                                "UPDATE PRODUCTS SET THUMBNAIL_FLAG=?,QUICKLOOK_FLAG=? " + "WHERE ID=?");
                        product_flags_stmt.setBoolean(1, false);
                        product_flags_stmt.setBoolean(2, false);
                        product_flags_stmt.setLong(3, pid);
                        product_flags_stmt.execute();
                    } finally {
                        if (product_flags_stmt != null)
                            try {
                                product_flags_stmt.close();
                            } catch (Exception e) {
                                logger.warn("Cannot close Statement !");
                            }
                    }
                    continue;
                }

                copy_blob_stmt = db_connection.prepareStatement(
                        "INSERT INTO PRODUCT_IMAGES (QUICKLOOK,THUMBNAIL) " + "VALUES (?,?)",
                        Statement.RETURN_GENERATED_KEYS);

                copy_blob_stmt.setBlob(1, ql);
                copy_blob_stmt.setBlob(2, th);
                copy_blob_stmt.execute();

                generated_key_res = copy_blob_stmt.getGeneratedKeys();
                if (generated_key_res.next()) {
                    PreparedStatement set_product_image_id_stmt = null;
                    Long iid = generated_key_res.getLong(1);

                    // Add ProductImages "IMAGES" entry in product
                    try {
                        set_product_image_id_stmt = db_connection
                                .prepareStatement("UPDATE PRODUCTS SET IMAGES_ID=?, THUMBNAIL_FLAG=?, "
                                        + "QUICKLOOK_FLAG=?  WHERE ID=?");
                        set_product_image_id_stmt.setLong(1, iid);
                        set_product_image_id_stmt.setBoolean(2, th != null);
                        set_product_image_id_stmt.setBoolean(3, ql != null);
                        set_product_image_id_stmt.setLong(4, pid);
                        set_product_image_id_stmt.execute();
                    } finally {
                        if (set_product_image_id_stmt != null)
                            try {
                                set_product_image_id_stmt.close();
                            } catch (Exception e) {
                                logger.warn("Cannot close Statement !");
                            }
                    }
                } else {
                    logger.error("Cannot retrieve Image primary key for " + "product ID #"
                            + products_res.getLong("ID"));
                }
            } finally {
                if (generated_key_res != null)
                    try {
                        generated_key_res.close();
                    } catch (Exception e) {
                        logger.warn("Cannot close ResultSet !");
                    }
                if (copy_blob_stmt != null)
                    try {
                        copy_blob_stmt.close();
                    } catch (Exception e) {
                        logger.warn("Cannot close Statement !");
                    }
            }
        }
    } catch (Exception e) {
        throw new CustomChangeException("Cannot move Blobs from product", e);
    } finally {
        if (products_res != null) {
            try {
                products_res.close();
            } catch (Exception e) {
                logger.warn("Cannot close ResultSet !");
            }
        }
        if (products != null) {
            try {
                products.close();
            } catch (Exception e) {
                logger.warn("Cannot close Statement !");
            }
        }
        //if (db_connection!=null) try { db_connection.close (); }
        // catch (Exception e) {}
    }
}

From source file:org.dcache.chimera.H2FsSqlDriver.java

@Override
Stat createInode(String id, int type, int uid, int gid, int mode, int nlink, long size) {
    /* H2 uses weird names for the column with the auto-generated key, so we cannot use the code
     * in the base class.//from   ww  w .  j  a  va 2  s.  c o m
     */
    Timestamp now = new Timestamp(System.currentTimeMillis());
    KeyHolder keyHolder = new GeneratedKeyHolder();
    _jdbc.update(con -> {
        PreparedStatement ps = con.prepareStatement(
                "INSERT INTO t_inodes (ipnfsid,itype,imode,inlink,iuid,igid,isize,iio,"
                        + "ictime,iatime,imtime,icrtime,igeneration) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)",
                Statement.RETURN_GENERATED_KEYS);
        ps.setString(1, id);
        ps.setInt(2, type);
        ps.setInt(3, mode & UnixPermission.S_PERMS);
        ps.setInt(4, nlink);
        ps.setInt(5, uid);
        ps.setInt(6, gid);
        ps.setLong(7, size);
        ps.setInt(8, _ioMode);
        ps.setTimestamp(9, now);
        ps.setTimestamp(10, now);
        ps.setTimestamp(11, now);
        ps.setTimestamp(12, now);
        ps.setLong(13, 0);
        return ps;
    }, keyHolder);

    Stat stat = new Stat();
    stat.setIno((Long) keyHolder.getKey());
    stat.setId(id);
    stat.setCrTime(now.getTime());
    stat.setGeneration(0);
    stat.setSize(size);
    stat.setATime(now.getTime());
    stat.setCTime(now.getTime());
    stat.setMTime(now.getTime());
    stat.setUid(uid);
    stat.setGid(gid);
    stat.setMode(mode & UnixPermission.S_PERMS | type);
    stat.setNlink(nlink);
    stat.setDev(17);
    stat.setRdev(13);

    return stat;
}