Example usage for java.sql PreparedStatement setDate

Introduction

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

Prototype

void setDate(int parameterIndex, java.sql.Date x) throws SQLException;

Source Link

Document

Sets the designated parameter to the given java.sql.Date value using the default time zone of the virtual machine that is running the application.

Usage

From source file:org.wso2.carbon.identity.provider.openid.dao.OpenIDUserRPDAO.java

/**
 * Updates the Relying Party if exists, if not, then creates a new Relying
 * Party//from w ww. j av a2s  .  c  o  m
 * 
 * @param rpdo
 * @throws IdentityException
 */
public void update(OpenIDUserRPDO rpdo) throws IdentityException {

    Connection connection = null;
    PreparedStatement prepStmt = null;

    try {
        connection = JDBCPersistenceManager.getInstance().getDBConnection();

        if (isUserRPExist(connection, rpdo)) {
            // we should update the entry
            prepStmt = connection.prepareStatement(OpenIDSQLQueries.UPDATE_USER_RP);

            prepStmt.setString(1, rpdo.getUserName());
            prepStmt.setInt(2, IdentityUtil.getTenantIdOFUser(rpdo.getUserName()));
            prepStmt.setString(3, rpdo.getRpUrl());
            prepStmt.setString(4, rpdo.isTrustedAlways() ? "TRUE" : "FALSE");
            prepStmt.setDate(5, new java.sql.Date(rpdo.getLastVisit().getTime()));
            prepStmt.setInt(6, rpdo.getVisitCount() + 1);
            prepStmt.setString(7, rpdo.getDefaultProfileName());

            prepStmt.execute();
            connection.commit();
        } else {
            // we should create the entry
            log.debug("Failed to update RP: " + rpdo.getRpUrl() + " for user: " + rpdo.getUserName()
                    + " Entry does not exist in the databse.");
        }
    } catch (SQLException e) {
        log.error("Failed to update RP:  " + rpdo.getRpUrl() + " for user: " + rpdo.getUserName()
                + " Error while accessing the database", e);
    } finally {
        IdentityDatabaseUtil.closeStatement(prepStmt);
        IdentityDatabaseUtil.closeConnection(connection);
    }
}

---

From source file:com.microsoftopentechnologies.azchat.web.dao.UserDAOImpl.java

/**
 * This method generates the prepare statement from userEntity object.
 * /*from  ww  w .j  a v a 2s . co m*/
 * @param preparedStatement
 * @param user
 * @return
 * @throws SQLException
 */
public PreparedStatement generatePreparedStatement(PreparedStatement preparedStatement, UserEntity user)
        throws SQLException {
    preparedStatement.setString(1, user.getNameId());
    preparedStatement.setString(2, user.getIdentityProvider());
    preparedStatement.setString(3, user.getFirstName());
    preparedStatement.setString(4, user.getLastName());
    preparedStatement.setString(5, user.getPhotoBlobUrl());
    preparedStatement.setString(6, user.getEmailAddress());
    preparedStatement.setInt(7, user.getPhoneCountryCode());
    preparedStatement.setLong(8, user.getPhoneNumber());
    preparedStatement.setDate(9, new java.sql.Date(user.getDateCreated().getTime()));
    preparedStatement.setDate(10, new java.sql.Date(user.getCreatedBy().getTime()));
    preparedStatement.setDate(11, new java.sql.Date(user.getDateModified().getTime()));
    preparedStatement.setDate(12, new java.sql.Date(user.getModifiedBy().getTime()));
    return preparedStatement;
}

---

From source file:org.hibernate.search.test.performance.scenario.TestExecutor.java

protected void initDatabase(TestContext ctx) {
    log("starting initialize database");

    ctx.initDatabaseStopWatch.start();//from  w ww  .j ava2  s. co  m

    BatchSupport batchSupport = new BatchSupport(ctx.sessionFactory, ctx.initialOffset);
    batchSupport.execute("insert into author(id, name) values(?, ?)", ctx.initialAuthorCount,
            new BatchCallback() {
                @Override
                public void initStatement(PreparedStatement ps, long id) throws SQLException {
                    ps.setLong(1, id);
                    ps.setString(2, "author" + id);
                }
            });
    batchSupport.execute(
            "insert into book(id, title, summary, rating, totalSold, publicationDate) values(?, ?, ?, ?, ?, ?)",
            ctx.initialBookCount, new BatchCallback() {
                @Override
                public void initStatement(PreparedStatement ps, long id) throws SQLException {
                    ps.setLong(1, id);
                    ps.setString(2, "title" + id);
                    ps.setString(3, reverse(SUMMARIES[(int) (id % SUMMARIES.length)]));
                    ps.setLong(4, -1);
                    ps.setLong(5, -1);
                    ps.setDate(6, new Date(PUBLICATION_DATE_ZERO.getTime()));
                }
            });
    batchSupport.execute("insert into book_author(book_id, authors_id) values(?, ?)", ctx.initialBookCount,
            new BatchCallback() {
                @Override
                public void initStatement(PreparedStatement ps, long id) throws SQLException {
                    ps.setLong(1, id);
                    ps.setLong(2, ctx.initialOffset + (id % ctx.initialAuthorCount));
                }
            });

    ctx.initDatabaseStopWatch.stop();
}

---

From source file:org.apache.phoenix.end2end.index.IndexMaintenanceIT.java

private void helpTestUpdate(String fullDataTableName, String indexName, boolean localIndex) throws Exception {
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {//w  w w.j ava  2  s . c o m
        conn.setAutoCommit(false);
        createDataTable(conn, fullDataTableName, "");
        populateDataTable(conn, fullDataTableName);

        // create an expression index
        String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON "
                + fullDataTableName
                + " ((UPPER(varchar_pk) || '_' || UPPER(char_pk) || '_' || UPPER(varchar_col1) || '_' || UPPER(char_col2)),"
                + " (decimal_pk+int_pk+decimal_col2+int_col1)," + " date_pk+1, date1+1, date2+1 )"
                + " INCLUDE (long_col1, long_col2)";
        PreparedStatement stmt = conn.prepareStatement(ddl);
        stmt.execute();

        // update index pk column and covered column
        String upsert = "UPSERT INTO " + fullDataTableName
                + "(varchar_pk, char_pk, int_pk, long_pk, decimal_pk, date_pk, varchar_col1, long_col1) VALUES(?, ?, ?, ?, ?, ?, ?, ?)";

        stmt = conn.prepareStatement(upsert);
        stmt.setString(1, "varchar1");
        stmt.setString(2, "char1");
        stmt.setInt(3, 1);
        stmt.setLong(4, 1l);
        stmt.setBigDecimal(5, new BigDecimal(0.5));
        stmt.setDate(6, DateUtil.parseDate("2015-01-01 00:00:00"));
        stmt.setString(7, "a.varchar_updated");
        stmt.setLong(8, 101);
        stmt.executeUpdate();
        conn.commit();

        // verify only one row was updated in the data table
        String selectSql = "UPPER(varchar_pk) || '_' || UPPER(char_pk) || '_' || UPPER(varchar_col1) || '_' || UPPER(char_col2), long_col1 from "
                + fullDataTableName;
        ResultSet rs = conn.createStatement().executeQuery("SELECT /*+ NO_INDEX */ " + selectSql);
        assertTrue(rs.next());
        assertEquals("VARCHAR1_CHAR1     _A.VARCHAR_UPDATED_B.CHAR1   ", rs.getString(1));
        assertEquals(101, rs.getLong(2));
        assertTrue(rs.next());
        assertEquals("VARCHAR2_CHAR2     _A.VARCHAR2_B.CHAR2   ", rs.getString(1));
        assertEquals(2, rs.getLong(2));
        assertFalse(rs.next());

        // verify that the rows in the index table are also updated
        rs = conn.createStatement().executeQuery("SELECT " + selectSql);
        assertTrue(rs.next());
        assertEquals("VARCHAR1_CHAR1     _A.VARCHAR_UPDATED_B.CHAR1   ", rs.getString(1));
        assertEquals(101, rs.getLong(2));
        assertTrue(rs.next());
        assertEquals("VARCHAR2_CHAR2     _A.VARCHAR2_B.CHAR2   ", rs.getString(1));
        assertEquals(2, rs.getLong(2));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}

---

From source file:org.psystems.dicom.browser.server.stat.StatClientRequestsChartServlet2.java

/**
 * @param connection/* w  w w. j a  va 2 s.  c om*/
 * @param series
 * @param metrica
 * @param timeBegin
 * @param timeEnd
 * @param dataset
 * @throws SQLException
 */
private void getMetrics(Connection connection, String series, String metrica, Calendar calendarBegin,
        Calendar calendarEnd, DefaultCategoryDataset dataset) throws SQLException {

    PreparedStatement stmt = null;
    try {

        SimpleDateFormat format = new SimpleDateFormat("dd.MM.yyyy");

        Calendar calendar1 = (Calendar) calendarBegin.clone();
        String dateStr = format.format(calendar1.getTime());
        dataset.addValue(0, series, dateStr);
        for (int i = 0; i < 6; i++) {
            calendar1.add(Calendar.DAY_OF_MONTH, 1);
            dateStr = format.format(calendar1.getTime());
            dataset.addValue(0, series, dateStr);
        }

        stmt = connection.prepareStatement("SELECT METRIC_VALUE_LONG, METRIC_DATE"
                + " FROM WEBDICOM.DAYSTAT WHERE METRIC_NAME = ? and " + " METRIC_DATE BETWEEN ? AND ? ");

        stmt.setString(1, metrica);
        stmt.setDate(2, new java.sql.Date(calendarBegin.getTimeInMillis()));
        stmt.setDate(3, new java.sql.Date(calendarEnd.getTimeInMillis()));
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            long value = rs.getLong("METRIC_VALUE_LONG");
            Date date = rs.getDate("METRIC_DATE");
            dateStr = format.format(date.getTime());
            String category = dateStr;
            dataset.addValue(value, series, category);
            //            System.out.println(value + " = " + series + " = "+ category);
        }
        rs.close();

    } finally {
        if (stmt != null)
            stmt.close();
    }
}

---

From source file:org.psystems.dicom.browser.server.stat.StatDailyLoadChartServlet2.java

/**
 * @param connection//  ww  w .j  a  v a2 s .  c  o m
 * @param series
 * @param metrica
 * @param timeBegin
 * @param timeEnd
 * @param dataset
 * @throws SQLException
 */
private void getMetrics(Connection connection, String series, String metrica, Calendar calendarBegin,
        Calendar calendarEnd, DefaultCategoryDataset dataset) throws SQLException {

    PreparedStatement stmt = null;
    try {

        SimpleDateFormat format = new SimpleDateFormat("dd.MM.yyyy");

        Calendar calendar1 = (Calendar) calendarBegin.clone();
        String dateStr = format.format(calendar1.getTime());
        dataset.addValue(0, series, dateStr);
        for (int i = 0; i < 6; i++) {
            calendar1.add(Calendar.DAY_OF_MONTH, 1);
            dateStr = format.format(calendar1.getTime());
            dataset.addValue(0, series, dateStr);
        }

        stmt = connection.prepareStatement("SELECT METRIC_VALUE_LONG, METRIC_DATE"
                + " FROM WEBDICOM.DAYSTAT WHERE METRIC_NAME = ? and " + " METRIC_DATE BETWEEN ? AND ? ");

        stmt.setString(1, metrica);
        stmt.setDate(2, new java.sql.Date(calendarBegin.getTimeInMillis()));
        stmt.setDate(3, new java.sql.Date(calendarEnd.getTimeInMillis()));
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            long value = rs.getLong("METRIC_VALUE_LONG") / 1000;
            Date date = rs.getDate("METRIC_DATE");
            dateStr = format.format(date.getTime());
            String category = dateStr;
            dataset.addValue(value, series, category);
            //            System.out.println(value + " = " + series + " = "+ category);
        }
        rs.close();

    } finally {
        if (stmt != null)
            stmt.close();
    }
}

---

From source file:datawarehouse.CSVLoader.java

/**
 * Parse CSV file using OpenCSV library and load in given database table.
 *
 * @param csvFile Input CSV file/*  w  w w  .ja va2  s  .  c  o m*/
 * @param tableName Database table name to import data
 * @param truncateBeforeLoad Truncate the table before inserting new
 * records.
 * @throws Exception
 */
public void loadCSV(String csvFile, String tableName, boolean truncateBeforeLoad) throws Exception {

    CSVReader csvReader = null;
    if (null == this.connection) {
        throw new Exception("Not a valid connection.");
    }
    try {

        csvReader = new CSVReader(new FileReader(csvFile), this.seprator);

    } catch (Exception e) {
        e.printStackTrace();
        throw new Exception("Error occured while executing file. " + e.getMessage());
    }

    String[] headerRow = csvReader.readNext();

    if (null == headerRow) {
        throw new FileNotFoundException(
                "No columns defined in given CSV file." + "Please check the CSV file format.");
    }

    String questionmarks = StringUtils.repeat("?,", headerRow.length);
    questionmarks = (String) questionmarks.subSequence(0, questionmarks.length() - 1);

    String query = SQL_INSERT.replaceFirst(TABLE_REGEX, tableName);
    query = query.replaceFirst(KEYS_REGEX, StringUtils.join(headerRow, ","));
    query = query.replaceFirst(VALUES_REGEX, questionmarks);

    System.out.println("Query: " + query);

    String[] nextLine;
    Connection con = null;
    PreparedStatement ps = null;
    try {
        con = this.connection;
        con.setAutoCommit(false);
        ps = con.prepareStatement(query);

        if (truncateBeforeLoad) {
            //delete data from table before loading csv
            con.createStatement().execute("DELETE FROM " + tableName);
        }

        final int batchSize = 1000;
        int count = 0;
        while ((nextLine = csvReader.readNext()) != null) {
            if (null != nextLine) {
                int index = 1;
                for (String string : nextLine) {
                    //System.out.print(string + ": ");
                    try {
                        DateFormat format = new SimpleDateFormat("dd.mm.yyyy");
                        Date date = format.parse(string);
                        ps.setDate(index++, new java.sql.Date(date.getTime()));
                        //System.out.println("date");
                    } catch (ParseException | SQLException e) {
                        try {
                            Double income = parseDouble(string.replace(",", "."));
                            ps.setDouble(index++, income);
                            //System.out.println("double");
                        } catch (NumberFormatException | SQLException err) {
                            ps.setString(index++, string);
                            //System.out.println("string");
                        }
                    }
                }
                ps.addBatch();
            }
            if (++count % batchSize == 0) {
                ps.executeBatch();
            }
        }
        ps.executeBatch(); // insert remaining records
        con.commit();
    } catch (Exception e) {
        con.rollback();
        e.printStackTrace();
        throw new Exception("Error occured while loading data from file to database." + e.getMessage());
    } finally {
        if (null != ps) {
            ps.close();
        }
        if (null != con) {
            con.close();
        }

        csvReader.close();
    }
}

---

From source file:com.esofthead.mycollab.module.project.service.ibatis.GanttAssignmentServiceImpl.java

private void massUpdateBugGanttItems(final List<TaskGanttItem> taskGanttItems, Integer sAccountId) {
    if (CollectionUtils.isNotEmpty(taskGanttItems)) {
        Lock lock = DistributionLockUtil.getLock("gantt-bug-service" + sAccountId);
        try {//w  w w . j a  v  a2  s.com
            final long now = new GregorianCalendar().getTimeInMillis();
            if (lock.tryLock(30, TimeUnit.SECONDS)) {
                try (Connection connection = dataSource.getConnection()) {
                    connection.setAutoCommit(false);
                    PreparedStatement batchTasksStatement = connection.prepareStatement(
                            "UPDATE `m_tracker_bug` SET " + "summary = ?, `startdate` = ?, `enddate` = ?, "
                                    + "`lastUpdatedTime`=?, `percentagecomplete`=?, `assignuser`=?, `ganttindex`=?, "
                                    + "`milestoneId`=? WHERE `id` = ?");
                    for (int i = 0; i < taskGanttItems.size(); i++) {
                        TaskGanttItem ganttItem = taskGanttItems.get(i);
                        if (ProjectTypeConstants.BUG.equals(ganttItem.getType())) {
                            batchTasksStatement.setString(1, ganttItem.getName());
                            batchTasksStatement.setDate(2, getDateWithNullValue(ganttItem.getStartDate()));
                            batchTasksStatement.setDate(3, getDateWithNullValue(ganttItem.getEndDate()));
                            batchTasksStatement.setDate(4, new Date(now));
                            batchTasksStatement.setDouble(5,
                                    MoreObjects.firstNonNull(ganttItem.getProgress(), 0d));
                            batchTasksStatement.setString(6, ganttItem.getAssignUser());
                            batchTasksStatement.setInt(7, ganttItem.getGanttIndex());
                            batchTasksStatement.setObject(8, ganttItem.getMilestoneId());
                            batchTasksStatement.setInt(9, ganttItem.getId());
                            batchTasksStatement.addBatch();
                        }

                    }
                    batchTasksStatement.executeBatch();
                    connection.commit();
                }
            }
        } catch (Exception e) {
            throw new MyCollabException(e);
        } finally {
            DistributionLockUtil.removeLock("gantt-bug-service" + sAccountId);
            lock.unlock();
        }
    }
}

---

From source file:org.kawanfw.test.api.client.InsertAndUpdatePrepStatementTest.java

/**
 * Do a 100 row insert inside a loop/*  www. j a v  a2  s  . com*/
 * 
 * @param connection
 *            the AceQL Connection
 * 
 * @throws Exception
 *             it any Exception occurs
 */
public void insertLoopPrepStatement(Connection connection, int numberToInsert) throws Exception {
    // We can now use our Remote JDBC Connection as a regular Connection!
    connection.setAutoCommit(false);

    // We will do all our remote insert in a SQL Transaction
    try {
        String sql = "insert into orderlog values ( ?, ?, ?, ?, ?, ?, ?, ?, ? )";

        // Create a new Prepared Statement
        PreparedStatement prepStatement = null;

        MessageDisplayer.display("");
        MessageDisplayer.display("Inserting " + numberToInsert + " orderlog...");

        SqlUtil sqlUtil = new SqlUtil(connection);

        for (int customerId = 1; customerId < numberToInsert + 1; customerId++) {
            int i = 1;
            long theTime = new java.util.Date().getTime();

            prepStatement = connection.prepareStatement(sql);

            prepStatement.setInt(i++, customerId);
            prepStatement.setInt(i++, customerId);
            prepStatement.setString(i++, "Item Description No " + customerId);
            prepStatement.setBigDecimal(i++, new BigDecimal(customerId));
            prepStatement.setDate(i++, new java.sql.Date(theTime));
            prepStatement.setTimestamp(i++, new Timestamp(theTime));

            prepStatement.setBytes(i++, null); // No Blob in this example.

            if (sqlUtil.isIngres() || sqlUtil.isPostgreSQL()) {
                prepStatement.setInt(i++, 0);
            } else {
                prepStatement.setBoolean(i++, false);
            }

            prepStatement.setInt(i++, customerId);

            prepStatement.executeUpdate();
            prepStatement.close();
        }

        // We do either everything in a single transaction or nothing
        connection.commit(); // Commit is propagated on Server
        MessageDisplayer.display("Remote Commit Done on AceQL Server!");
    } catch (Exception e) {
        connection.rollback();
        throw e;
    } finally {
        connection.setAutoCommit(true);
    }

}

---

From source file:com.salesmanager.core.service.catalog.impl.db.dao.CategoryDao.java

public void save(final Category instance) {
    getHibernateTemplate().execute(new HibernateCallback() {
        public Object doInHibernate(Session session) throws HibernateException, SQLException {
            Connection con = session.connection();
            PreparedStatement ps = con
                    .prepareStatement("insert into categories(categories_id,categories_image,parent_id,"
                            + "sort_order,date_added,last_modified,categories_status,visible,RefCategoryID,"
                            + "RefCategoryLevel,RefCategoryName,RefCategoryParentID,RefExpired,merchantid,depth,"
                            + "lineage) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
            ps.setLong(1, instance.getCategoryId());
            ps.setString(2, instance.getCategoryImage());
            ps.setLong(3, instance.getParentId());
            ps.setInt(4, (instance.getSortOrder() == null ? 0 : instance.getSortOrder()));
            ps.setDate(5, new java.sql.Date(instance.getDateAdded().getTime()));
            ps.setDate(6, new java.sql.Date(instance.getLastModified().getTime()));
            ps.setBoolean(7, instance.isCategoryStatus());
            ps.setBoolean(8, instance.isVisible());
            ps.setLong(9, instance.getRefCategoryId());
            ps.setInt(10, instance.getRefCategoryLevel());
            ps.setString(11, instance.getRefCategoryName());
            ps.setString(12, instance.getRefCategoryParentId());
            ps.setString(13, instance.getRefExpired());
            ps.setLong(14, instance.getMerchantId());
            ps.setInt(15, instance.getDepth());
            ps.setString(16, instance.getLineage());
            return ps.executeUpdate();
        }/*w w w . j  a  v  a 2 s  .  co  m*/

    });
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• »