Example usage for org.springframework.jdbc.core JdbcTemplate query

List of usage examples for org.springframework.jdbc.core JdbcTemplate query

  1. HOME
  2. Java
  3. org.springframework
  4. org.springframework.jdbc.core.*
  5. JdbcTemplate
  6. query

Introduction

In this page you can find the example usage for org.springframework.jdbc.core JdbcTemplate query.

Prototype

@Override
    public <T> List<T> query(String sql, RowMapper<T> rowMapper, @Nullable Object... args)
            throws DataAccessException

Source Link

Usage

From source file:com.starit.diamond.server.utils.PaginationHelper.java

/**
 * ?/*from www  .j  a va2s .c o  m*/
 * 
 * @param jt
 *            jdbcTemplate
 * @param sqlCountRows
 *            SQL
 * @param sqlFetchRows
 *            ?sql
 * @param args
 *            ?
 * @param pageNo
 *            
 * @param pageSize
 *            ??
 * @param rowMapper
 * @return
 */
public Page<E> fetchPage(final JdbcTemplate jt, final String sqlCountRows, final String sqlFetchRows,
        final Object args[], final int pageNo, final int pageSize, final ParameterizedRowMapper<E> rowMapper) {
    if (pageSize == 0) {
        return null;
    }

    // ?
    final int rowCount = jt.queryForInt(sqlCountRows, args);

    // 
    int pageCount = rowCount / pageSize;
    if (rowCount > pageSize * pageCount) {
        pageCount++;
    }

    // Page
    final Page<E> page = new Page<E>();
    page.setPageNumber(pageNo);
    page.setPagesAvailable(pageCount);
    page.setTotalCount(rowCount);

    if (pageNo > pageCount)
        return null;
    // ????
    final int startRow = (pageNo - 1) * pageSize;
    // TODO ?? limit

    String selectSQL = "";
    if ("mysql".equals(ProfileUtil.getDbType()))
        selectSQL = sqlFetchRows + " limit " + startRow + "," + pageSize;
    else if ("oracle".equals(ProfileUtil.getDbType()))
        selectSQL = "SELECT * FROM (SELECT A.*, ROWNUM RN FROM (" + (sqlFetchRows) + ") A WHERE ROWNUM <= "
                + (pageSize + startRow) + ") WHERE RN >= " + (startRow + 1);
    jt.query(selectSQL, args, new ResultSetExtractor() {
        public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
            final List<E> pageItems = page.getPageItems();
            int currentRow = 0;
            while (rs.next()) {
                pageItems.add(rowMapper.mapRow(rs, currentRow++));
            }
            return page;
        }
    });
    return page;
}

From source file:com.sfs.whichdoctor.search.SearchDAOImpl.java

/**
 * Search.//  ww  w.j ava 2  s .c  o  m
 *
 * @param search the search
 * @param loadDetails the load details
 *
 * @return the search results bean
 *
 * @throws WhichDoctorSearchDaoException the which doctor search dao
 *             exception
 */
@SuppressWarnings("unchecked")
public final SearchResultsBean search(final SearchBean search, final BuilderBean loadDetails)
        throws WhichDoctorSearchDaoException {

    if (this.sqlHandlers == null) {
        throw new WhichDoctorSearchDaoException("The search is not configured");
    }

    if (search.getType() == null) {
        /* Incorrect type set in SearchBean */
        dataLogger.error("No search type has been set");
        throw new WhichDoctorSearchDaoException("No search type has been set");
    }

    SqlHandler sqlHandler = null;
    if (this.sqlHandlers.containsKey(search.getType())) {
        sqlHandler = this.sqlHandlers.get(search.getType());
    }

    if (sqlHandler == null) {
        throw new WhichDoctorSearchDaoException("No SQL handler found for this search");
    }

    /* Build SQL WHERE statement */
    if (search.getSearchArray() != null) {
        search.setSQLWhereComponents(search.getSQLArrayStatement(sqlHandler.getIdentifierColumn()),
                search.getAction(), search.getSearchArray(), search.getSearchArrayDescription());
    } else {
        if (search.getSearchCriteria() != null && search.getSearchConstraints() != null) {
            Map<String[], Collection<Object>> fields = sqlHandler.construct(search.getSearchCriteria(),
                    search.getSearchConstraints());

            for (String[] key : fields.keySet()) {
                search.setSQLWhereComponents(key[0], search.getAction(), fields.get(key), key[1]);

            }
        }
    }

    final String sqlWHERE = search.getSearchSQL();

    // System.out.println("- SQL where: " + sqlWHERE);

    final Collection<Object> parameters = search.getSearchParameters();

    /* Set ordering system of returned results */
    if (search.getOrderColumn() == null) {
        search.setOrderColumn(sqlHandler.getDefaultOrder());
    }
    String sqlORDER = buildOrder(search);

    /* Set range of results */
    if (search.getRequestedPage() == 0) {
        search.setRequestedPage(1);
    }
    String sqlLIMIT = buildLimit(search);

    /* Initalize SearchResultsBean to hold result */
    SearchResultsBean searchResults = new SearchResultsBean();

    // Fill with values from SearchBean
    searchResults.setOrderAscending(search.getOrderAscending());
    searchResults.setOrderColumn(search.getOrderColumn());
    searchResults.setRequestedPage(search.getRequestedPage());
    searchResults.setType(search.getType());
    searchResults.setLimit(search.getLimit());
    searchResults.setSearchCriteria(search.getSearchCriteria());
    searchResults.setSearchVectors(search.getSearchVectors());

    if (loadDetails.getBoolean("RESULT_COUNT")) {
        /* Load the result count for this search */

        StringBuffer countSQL = new StringBuffer();
        countSQL.append(sqlHandler.getCountSql());
        countSQL.append(sqlWHERE);

        dataLogger.info("SQL Query: " + countSQL.toString());

        /* System.out.println("- SQL count: " + countSQL.toString()); */

        try {
            JdbcTemplate jdbcTemplate = this.getJdbcTemplateReader();
            if (!sqlHandler.getDefaultConnection()) {
                jdbcTemplate = this.getIsbJdbcTemplate();
            }
            final int totalResults = jdbcTemplate.queryForInt(countSQL.toString(), parameters.toArray());

            searchResults.setTotalResults(totalResults);
        } catch (DataAccessException de) {
            dataLogger.error("Error getting search result count: " + de.getMessage());
        }
    }

    // BUILD SQL Statement
    StringBuffer searchSQL = new StringBuffer();
    searchSQL.append(sqlHandler.getSelectSql());
    searchSQL.append(sqlWHERE);
    searchSQL.append(sqlHandler.getGroupBy());
    searchSQL.append(sqlORDER);
    searchSQL.append(sqlLIMIT);

    dataLogger.info("SQL Query: " + searchSQL.toString());

    /* System.out.println("- SQL search: " + searchSQL.toString()); */

    Collection<Integer> objects = null;

    try {
        JdbcTemplate jdbcTemplate = this.getJdbcTemplateReader();
        if (!sqlHandler.getDefaultConnection()) {
            jdbcTemplate = this.getIsbJdbcTemplate();
        }
        objects = jdbcTemplate.query(searchSQL.toString(), parameters.toArray(), new RowMapper() {
            public Object mapRow(final ResultSet rs, final int rowNum) throws SQLException {
                return rs.getInt(1);
            }
        });

    } catch (IncorrectResultSizeDataAccessException ie) {
        dataLogger.debug("No results found for this search: " + ie.getMessage());
    }

    if (objects != null) {
        // Pass the results to the handler and load the referenced objects
        searchResults.setSearchResults(sqlHandler.load(objects, loadDetails));
    }
    return searchResults;
}

From source file:com.rplt.studioMusik.dataPersewaan.PersewaanStudioMusikDAO.java

@Override
public boolean cekKetersediaanJadwal(PersewaanStudioMusik pPersewaanStudioMusik) {
    List<PersewaanStudioMusik> pegawaiList = new ArrayList<PersewaanStudioMusik>();

    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

    String sql = "SELECT to_char((to_date(?, 'HH24:MI') + ? / 24), 'HH24:MI') FROM dual";

    //        sql = "SELECT date_format(date_add((STR_TO_DATE(?, '%d-%b-%Y %k:%S')), INTERVAL ? HOUR), '%d-%b-%Y %k:%S')";
    ////ww w . j  av a 2 s  .  com
    //        String selesaiSewa = jdbcTemplate.queryForObject(sql, 
    //                new Object[]{
    //                    pPersewaanStudioMusik.getmMulaiSewa(), 
    //                    pPersewaanStudioMusik.getmDurasi()}, 
    //                String.class);
    //
    //        System.out.println(selesaiSewa);
    //        
    //        pPersewaanStudioMusik.setmSelesaiSewa(selesaiSewa);
    sql = "SELECT * FROM PERSEWAAN_STUDIO_MUSIK WHERE KODE_STUDIO = ? "
            + "AND to_char(MULAI_SEWA, 'dd-MON-yyyy') = ? "
            + "AND to_char(mulai_sewa,'HH24:MI') BETWEEN ? AND ? " + "AND "
            + "(to_char(SELESAI_SEWA,'HH24:MI') > ? AND " + "to_char(MULAI_SEWA,'HH24:MI') < ?)";

    sql = "SELECT * FROM `studiomusik`.`persewaan_studio_musik` " + "WHERE KODE_STUDIO = ? "
            + "AND STR_TO_DATE(?, '%d-%b-%Y %k:%S') < `SELESAI_SEWA` "
            + "AND STR_TO_DATE(?, '%d-%b-%Y %k:%S') > `MULAI_SEWA`";

    //        System.out.println(pPersewaanStudioMusik.getmMulaiSewa() + " " + jamMulai + "sampai" + selesaiSewa);
    pegawaiList = jdbcTemplate
            .query(sql,
                    new Object[] { pPersewaanStudioMusik.getmKodeStudio(),
                            pPersewaanStudioMusik.getmMulaiSewa(), pPersewaanStudioMusik.getmSelesaiSewa() },
                    new PersewaanStudioMusikRowMapper());

    System.out.println("list size : " + pegawaiList.size());

    if (pegawaiList.isEmpty()) {
        return true;
    } else {
        return false;
    }

}

From source file:de.uhh.l2g.dao.VideoDao.java

/**
 * Gets the by producer id and counter./*from   w w w .j  a  va  2  s. com*/
 *
 * @param remoteUserId the remote user id
 * @param start the start
 * @param ende the ende
 * @return the by producer id and counter
 */
@SuppressWarnings("unchecked")
public List<Video> getByProducerIdAndCounter(Integer remoteUserId, Integer start, Integer ende) {
    JdbcTemplate select = new JdbcTemplate(this.getDataSource());
    List<Video> result = select.query(
            "SELECT v.id, v.title, m.creator, l.name, v.openAccess, v.downloadLink, v.hits, v.uploadDate, v.generationDate, v.filename, v.surl, f.typ, v.hostId, v.producerId, v.lectureseriesId FROM video v, lectureseries l, metadata m, facility f WHERE (v.producerId=? AND v.lectureseriesId=l.id AND v.metadataId=m.id AND v.facilityId=f.id) ORDER BY id DESC LIMIT ?,?;",
            new Object[] { remoteUserId, start, ende },
            new VideoResultProducerListRowMapper(hostDao, producerDao, segmentDao));
    return result;
}

From source file:de.uhh.l2g.dao.VideoDao.java

/**
 * Gets the by producer id and counter andlectureseries id.
 *
 * @param remoteUserId the remote user id
 * @param start the start/*from  w  ww.jav a2 s . com*/
 * @param ende the ende
 * @param lectureseriesId the lectureseries id
 * @return the by producer id and counter andlectureseries id
 */
@SuppressWarnings("unchecked")
public List<Video> getByProducerIdAndCounterAndlectureseriesId(Integer remoteUserId, Integer start,
        Integer ende, Integer lectureseriesId) {
    JdbcTemplate select = new JdbcTemplate(this.getDataSource());
    List<Video> result = select.query(
            "SELECT v.id, v.title, m.creator, l.name, v.openAccess, v.downloadLink, v.hits, v.uploadDate, v.generationDate, v.filename, v.surl, f.typ, v.hostId, v.producerId, v.lectureseriesId FROM video v, lectureseries l, metadata m, facility f WHERE (v.producerId=? AND v.lectureseriesId=? AND v.lectureseriesId=l.id AND v.metadataId=m.id AND v.facilityId=f.id) ORDER BY id DESC LIMIT ?,?;",
            new Object[] { remoteUserId, lectureseriesId, start, ende },
            new VideoResultProducerListRowMapper(hostDao, producerDao, segmentDao));
    return result;
}

From source file:de.uhh.l2g.dao.VideoDao.java

/**
 * Gets the by id for download.//from  w w  w  .  j ava  2s.c o m
 *
 * @param id the id
 * @return the by id for download
 */
@SuppressWarnings("unchecked")
public List<Video> getByIdForDownload(int id) {
    JdbcTemplate select = new JdbcTemplate(this.getDataSource());
    List<Video> returnList = select.query(
            "SELECT id, title, tags, lectureseriesId, ownerId, producerId, containerFormat, filename, resolution, duration, hostId, textId, filesize, generationDate, openAccess, downloadLink, metadataId, surl, hits, uploadDate, permittedToSegment, facilityId, citation2go FROM video WHERE id=?",
            new Object[] { id }, new VideoRowMapper());
    return returnList;
}

From source file:de.uhh.l2g.dao.VideoDao.java

/**
 * Gets the by id./*  w  w  w  .j a  v a2  s  .c om*/
 *
 * @param id the id
 * @return the by id
 */
@SuppressWarnings("unchecked")
public List<Video> getByIdNP(int id) {
    JdbcTemplate select = new JdbcTemplate(this.getDataSource());
    List<Video> returnList = select.query(
            "SELECT v.id, v.title, m.creator, l.name, v.filename,  v.generationDate, f.typ, v.lectureseriesId, v.metadataId, v.hostId, v.producerId FROM video v, metadata m, lectureseries l, facility f  WHERE (v.id = ? AND v.metadataId = m.id AND v.lectureseriesId = l.id AND v.facilityId=f.id  )",
            new Object[] { id }, new VideoResultSearchRowMapper());
    return returnList;
}

From source file:de.uhh.l2g.dao.VideoDao.java

/**
 * Gets the video list by lectureseries id and producer.
 *
 * @param lectureseriesId the lectureseries id
 * @param producerId the producer id//from ww  w. j a  v a2s.com
 * @return the video list by lectureseries id and producer
 */
@SuppressWarnings("unchecked")
public List<Video> getVideoListByLectureseriesIdAndProducer(int lectureseriesId, int producerId) {
    JdbcTemplate select = new JdbcTemplate(this.getDataSource());
    List<Video> result = select.query(
            "SELECT id, title, tags, lectureseriesId, ownerId, producerId, containerFormat, filename, resolution, duration, hostId, textId, filesize, generationDate, openAccess, downloadLink, metadataId, surl, hits, uploadDate, permittedToSegment, facilityId, citation2go FROM video WHERE ( lectureseriesId = ? AND producerId =?)",
            new Object[] { lectureseriesId, producerId }, new VideoRowMapper());
    try {
        this.fillVideoListWithProperties(result);
    } catch (IOException e) {
    }
    return result;
}

From source file:de.uhh.l2g.dao.VideoDao.java

/**
 * Gets the by s url./*  w w  w  .  j  ava2  s . co m*/
 *
 * @param surl the surl
 * @return the by s url
 */
@SuppressWarnings("unchecked")
public List<Video> getBySUrl(String surl) {
    JdbcTemplate select = new JdbcTemplate(this.getDataSource());
    List<Video> result = select.query(
            "SELECT id, title, tags, lectureseriesId, ownerId, producerId, containerFormat, filename, resolution, duration, hostId, textId, filesize, generationDate, openAccess, downloadLink, metadataId, surl, hits, uploadDate, permittedToSegment, facilityId, citation2go FROM video WHERE surl=?",
            new Object[] { surl }, new VideoRowMapper());
    try {
        this.fillVideoListWithProperties(result);
    } catch (IOException e) {
    }

    return result;
}

From source file:de.uhh.l2g.dao.VideoDao.java

/**
 * Gets the by producer id and lectureseries id.
 *
 * @param remoteUserId the remote user id
 * @param lectureseriesId the lectureseries id
 * @return the by producer id and lectureseries id
 *//*from ww  w. j  a  va 2s .  c  o m*/
@SuppressWarnings("unchecked")
public List<Video> getByProducerIdAndLectureseriesId(int remoteUserId, int lectureseriesId) {
    JdbcTemplate select = new JdbcTemplate(this.getDataSource());
    List<Video> result = select.query(
            "SELECT id, title, tags, lectureseriesId, ownerId, producerId, containerFormat, filename, resolution, duration, hostId, textId, filesize, generationDate, openAccess, downloadLink, metadataId, surl, hits, uploadDate, permittedToSegment, facilityId, citation2go FROM video WHERE ( producerId=? AND lectureseriesId=?) ORDER BY id DESC",
            new Object[] { remoteUserId, lectureseriesId }, new VideoRowMapper());
    try {
        this.fillVideoListWithProperties(returnList);
    } catch (IOException e) {
    }

    return result;
}