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:biblivre3.acquisition.quotation.QuotationDAO.java

public int getSearchCount(QuotationDTO example) {
    int count = 0;
    Connection con = null;//  ww w.j  av  a  2 s .  com
    try {
        con = getDataSource().getConnection();
        final StringBuilder sql = new StringBuilder(" SELECT count(*) FROM acquisition_quotation ");
        if (example.getSerial() != null && example.getSerial() != 0) {
            sql.append(" WHERE serial_quotation = ? ");
        } else if (example.getQuotationDate() != null) {
            sql.append(" WHERE quotation_date = ? ");
        } else if (example.getSerialSupplier() != null && example.getSerialSupplier() != 0) {
            sql.append(" WHERE serial_supplier = ? ");
        }
        final PreparedStatement pst = con.prepareStatement(sql.toString());
        int i = 1;
        if (example.getSerial() != null && example.getSerial() != 0) {
            pst.setInt(i++, example.getSerial());
        } else if (example.getQuotationDate() != null) {
            pst.setDate(i++, new java.sql.Date(example.getQuotationDate().getTime()));
        } else if (example.getSerialSupplier() != null && example.getSerialSupplier() != 0) {
            pst.setInt(i++, example.getSerialSupplier());
        }
        final ResultSet rs = pst.executeQuery();
        if (rs == null) {
            return count;
        }
        while (rs.next()) {
            return rs.getInt(1);
        }
    } catch (Exception e) {
        log.error(e.getMessage(), e);
        throw new DAOException(e.getMessage());
    } finally {
        closeConnection(con);
    }
    return count;
}

---

From source file:Controllers.AppointmentController.java

/**
 *
 * @param appointment//from   w  w  w  .  j  a  v  a 2s . c  o m
 * @param result
 * @param modelMap
 * @return 
 */
@RequestMapping(method = RequestMethod.POST)
@ResponseStatus(value = HttpStatus.OK)
public String AddApp(@ModelAttribute("appointment") Appointment appointment, BindingResult result,
        ModelMap modelMap) {

    int accountId = appointment.getAccountId();
    int departmentId = appointment.getDepartmentId();
    Date fulldate = appointment.getDate();
    java.sql.Date date = new java.sql.Date(fulldate.getTime());
    String message = appointment.getMessage();

    String content = "";

    if (departmentId == 0 || date == null) {
        content = "Sorry, you didn't fill some of fields. Please, try again.";
        modelMap.addAttribute("content", content);
        return "appointmentConfirmation";
    }
    Date m = null;
    int appointmentcounter = 0;
    Appointment[] appointments = fetchAppointments();
    for (int i = 0; i < appointments.length; i++) {
        if (appointments[i].getDate().compareTo(date) == 0 && appointments[i].getDepartmentId() == departmentId)
            appointmentcounter++;
    }
    if (appointmentcounter >= 2) {
        content = "Sorry, there is no any free spaces for your appointment on " + date + " to visit "
                + findDepartmentName(departmentId) + ". Please, select another day.<br>";
        modelMap.addAttribute("content", content);
        return "appointmentConfirmation";
    }

    try {
        Context ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup("jdbc/medicalCareDataSource");
        connection = ds.getConnection();

        Statement stmt = connection.createStatement();
        PreparedStatement pstmt;

        pstmt = connection.prepareStatement(
                "INSERT INTO appointments (accountId, departmentId, date, message)\n" + "VALUES (?,?,?,?);");
        pstmt.setInt(1, accountId);
        pstmt.setInt(2, departmentId);
        pstmt.setDate(3, date);
        pstmt.setString(4, message);
        pstmt.execute();

        content = "<h4>Appontment have been setted.</h4><br><h5>Please, check information below.</h5><br>"
                + "<table>" + "<tr><td>Seleted department:</td><td>" + findDepartmentName(departmentId)
                + "</td></tr>" + "<tr><td>Seleted date:</td><td>"
                + new SimpleDateFormat("yyyy-MM-dd").format(date) + "</td></tr>"
                + "<tr><td>Attached message</td><td>" + message + "</td></tr>" + "</table>";

        stmt.close();
    } catch (NamingException ex) {
        Logger.getLogger(AppointmentController.class.getName()).log(Level.SEVERE, null, ex);
    } catch (SQLException ex) {
        Logger.getLogger(AppointmentController.class.getName()).log(Level.SEVERE, null, ex);
    }

    modelMap.addAttribute("content", content);

    return "appointmentConfirmation";
}

---

From source file:org.sonar.core.measure.MeasureFilterSql.java

List<MeasureFilterRow> execute(Connection connection) throws SQLException {
    PreparedStatement statement = connection.prepareStatement(sql);
    statement.setFetchSize(FETCH_SIZE);/*from   w w w .  j  a  v a2s .  c  om*/
    ResultSet rs = null;
    try {
        for (int index = 0; index < dateParameters.size(); index++) {
            statement.setDate(index + 1, dateParameters.get(index));
        }
        rs = statement.executeQuery();
        return process(rs);

    } finally {
        DatabaseUtils.closeQuietly(rs);
        DatabaseUtils.closeQuietly(statement);
    }
}

---

From source file:nl.ordina.bag.etl.dao.AbstractBAGMutatiesDAO.java

@Override
public long insertMutatiesFile(final java.util.Date dateFrom, final java.util.Date dateTo, final byte[] content)
        throws DAOException {
    try {/*from  www .  j ava2  s .c  o m*/
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement("insert into bag_mutaties_file (" + " id,"
                        + " date_from," + " date_to," + " content"
                        + ") values ((select nvl(max(id),0) + 1 from bag_mutaties_file),trunc(?),trunc(?),?)",
                        new int[] { 1 });
                ps.setDate(1, new Date(dateFrom.getTime()));
                ps.setDate(2, new Date(dateTo.getTime()));
                ps.setBytes(3, content);
                return ps;
            }
        }, keyHolder);
        return keyHolder.getKey().longValue();
    } catch (DataAccessException e) {
        throw new DAOException(e);
    }
}

---

From source file:biblivre3.acquisition.quotation.QuotationDAO.java

public ArrayList<QuotationDTO> searchQuotation(QuotationDTO example, int offset, int limit) {
    ArrayList<QuotationDTO> requestList = new ArrayList<QuotationDTO>();
    Connection con = null;//from  w w  w.  j av  a 2s . c o  m
    try {
        con = getDataSource().getConnection();
        final StringBuilder sql = new StringBuilder(" SELECT * FROM acquisition_quotation ");

        if (example.getSerial() != null && example.getSerial() != 0) {
            sql.append(" WHERE serial_quotation = ? ");
        } else if (example.getQuotationDate() != null) {
            sql.append(" WHERE quotation_date = ? ");
        } else if (example.getSerialSupplier() != null && example.getSerialSupplier() != 0) {
            sql.append(" WHERE serial_supplier = ? ");
        } else if (!StringUtils.isBlank(example.getSupplierName())) {
            sql.append(
                    " WHERE serial_supplier in (select serial_supplier from acquisition_supplier where company_name ilike ?) ");
        }
        sql.append(" ORDER BY serial_quotation ASC offset ? limit ? ");
        final PreparedStatement pst = con.prepareStatement(sql.toString());

        int i = 1;
        if (example.getSerial() != null && example.getSerial() != 0) {
            pst.setInt(i++, example.getSerial());
        } else if (example.getQuotationDate() != null) {
            pst.setDate(i++, new java.sql.Date(example.getQuotationDate().getTime()));
        } else if (example.getSerialSupplier() != null && example.getSerialSupplier() != 0) {
            pst.setInt(i++, example.getSerialSupplier());
        } else if (!StringUtils.isBlank(example.getSupplierName())) {
            pst.setString(i++, "%" + example.getSupplierName() + "%");
        }

        pst.setInt(i++, offset);
        pst.setInt(i++, limit);
        final ResultSet rs = pst.executeQuery();
        if (rs == null) {
            return requestList;
        }
        while (rs.next()) {
            QuotationDTO dto = this.populateDto(rs);
            requestList.add(dto);
        }
    } catch (Exception e) {
        log.error(e.getMessage(), e);
        throw new DAOException(e.getMessage());
    } finally {
        closeConnection(con);
    }
    return requestList;
}

---

From source file:uk.ac.cam.cl.dtg.segue.dos.PgLocationHistory.java

@Override
public Map<String, LocationHistoryEvent> getLatestByIPAddresses(final Date fromDate, final Date toDate)
        throws SegueDatabaseException {
    try (Connection conn = database.getDatabaseConnection()) {

        PreparedStatement pst;
        pst = conn.prepareStatement("Select * FROM ip_location_history "
                + "WHERE last_lookup BETWEEN ? AND ? AND is_current = TRUE ORDER BY last_lookup DESC");

        pst.setDate(1, new java.sql.Date(fromDate.getTime()));
        pst.setDate(2, new java.sql.Date(toDate.getTime()));

        ResultSet results = pst.executeQuery();
        Map<String, LocationHistoryEvent> resultToReturn = Maps.newHashMap();

        while (results.next()) {
            PgLocationEvent buildPgLocationEntry = buildPgLocationEntry(results);
            resultToReturn.put(buildPgLocationEntry.getIpAddress(), buildPgLocationEntry);
        }/*  ww  w.j a  v a 2 s.c  o m*/

        return resultToReturn;
    } catch (SQLException e) {
        throw new SegueDatabaseException("Postgres exception", e);
    }
}

---

From source file:Controllers.ReportController.java

public Patient[] fetchAppointments(int departmentId, java.sql.Date date) {

    Patient[] patients = null;//w w  w .  j  a  v  a  2 s.c om

    try {
        Context ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup("jdbc/medicalCareDataSource");
        connection = ds.getConnection();

        PreparedStatement pstmt = connection.prepareStatement("SELECT appointments.message, \n"
                + "accounts.firstName, accounts.lastName FROM appointments INNER JOIN accounts ON appointments.accountId = accounts.accountId \n"
                + "WHERE appointments.departmentId = ? AND appointments.date = ?;");

        pstmt.setInt(1, departmentId);
        pstmt.setDate(2, date);

        ResultSet resultSet = pstmt.executeQuery();

        List<Patient> appointmentsList = new ArrayList<Patient>();
        while (resultSet.next()) {
            Patient patient = new Patient();
            patient.setPatientFirstName(resultSet.getString("firstName"));
            patient.setPatientSecondName(resultSet.getString("lastName"));
            patient.setPatientClaim(resultSet.getString("message"));

            appointmentsList.add(patient);
        }

        patients = new Patient[appointmentsList.size()];
        patients = appointmentsList.toArray(patients);

        pstmt.close();

    } catch (NamingException ex) {
        Logger.getLogger(AppointmentController.class.getName()).log(Level.SEVERE, null, ex);
    } catch (SQLException ex) {
        Logger.getLogger(AppointmentController.class.getName()).log(Level.SEVERE, null, ex);
    }

    return patients;
}

---

From source file:com.acme.spring.jdbc.repository.impl.JdbcStockRepository.java

/**
 * {@inheritDoc}/*  w w  w  .  ja  v a  2 s .co m*/
 */
@Override
public long save(final Stock stock) {

    // validates the input
    validateNoNull(stock, "stock");
    validateNotEmpty(stock.getSymbol(), "symbol");

    KeyHolder keyHolder = new GeneratedKeyHolder();
    // performs the insert in the database
    jdbcTemplate.update(new PreparedStatementCreator() {
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            PreparedStatement ps = connection.prepareStatement(
                    "insert into Stock(name, symbol, value, date) values (?, ?, ?, ?)", new String[] { "id" });

            int index = 1;
            ps.setString(index++, stock.getName());
            ps.setString(index++, stock.getSymbol());
            ps.setBigDecimal(index++, stock.getValue());
            ps.setDate(index++, new java.sql.Date(stock.getDate().getTime()));
            return ps;
        }
    }, keyHolder);

    // retrieves the value primary key for the inserted key
    stock.setId((Long) keyHolder.getKey());

    return stock.getId();
}

---

From source file:org.castor.cpa.test.test89.TestLazyEmployeeExpiration.java

/**
 * Update a person outside of JDO using a JDBC connection.
 * //from   w w  w.j  ava2s  .co  m
 * @param firstName
 *            first part of primary key of object to be updated
 * @param lastName
 *            first part of primary key of object to be updated
 * @param newBirthdate
 *            new value of persons birthdate
 * @throws Exception
 */
private void updatePersonUsingJDBC(final String firstName, final String lastName, final Date newBirthdate)
        throws Exception {

    LOG.info("updatePersonUsingJDBC: updating " + firstName + " " + lastName);
    _db.begin();
    PreparedStatement updatePersonStatement = _db.getJdbcConnection()
            .prepareStatement("update test89_pks_person set bday=? where fname=? and lname=?");
    updatePersonStatement.setDate(1, newBirthdate);
    updatePersonStatement.setString(2, firstName);
    updatePersonStatement.setString(3, lastName);
    int rc = updatePersonStatement.executeUpdate();
    if (rc <= 0) {
        LOG.error(//
                "updatePersonUsingJDBC: error updating person, return = " + rc);
        return;
    }
    _db.commit();

}

---

From source file:org.castor.cpa.test.test89.TestLazyEmployeeExpiration.java

/**
 * Update a person outside of JDO using a JDBC connection.
 * //  w  w  w .  j  a v a 2 s. co m
 * @param firstName
 *            first part of primary key of object to be updated
 * @param lastName
 *            first part of primary key of object to be updated
 * @param newStartDate
 *            new value of persons birthdate
 * @throws Exception
 */
private void updateEmplUsingJDBC(final String firstName, final String lastName, final Date newStartDate)
        throws Exception {

    LOG.info("updateEmployeeUsingJDBC: updating " + firstName + " " + lastName);
    _db.begin();
    PreparedStatement updateEmployeeStatement = _db.getJdbcConnection()
            .prepareStatement("update test89_pks_employee set start_date=? where fname=? and lname=?");
    updateEmployeeStatement.setDate(1, newStartDate);
    updateEmployeeStatement.setString(2, firstName);
    updateEmployeeStatement.setString(3, lastName);
    int rc = updateEmployeeStatement.executeUpdate();
    if (rc <= 0) {
        LOG.error("updateEmplUsingJDBC: error updating employee, return = " + rc);
        return;
    }
    _db.commit();
}

---

• «
• 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
• »