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:de.uhh.l2g.dao.VideoDao.java

/**
 * Gets the by open access and counter and facility id and parent id.
 *
 * @param start the start/*w  w  w  .  j ava 2  s  . c om*/
 * @param ende the ende
 * @param facilityId the facility id
 * @param parentId the parent id
 * @return the by open access and counter and facility id and parent id
 */
@SuppressWarnings("unchecked")
public List<Video> getByOpenAccessAndCounterAndFacilityIdAndParentId(Integer start, Integer ende,
        Integer facilityId, Integer parentId) {

    JdbcTemplate select = new JdbcTemplate(this.getDataSource());
    List<Video> result = select.query(
            "SELECT * FROM video, video_facility WHERE ( video.openAccess=1 AND (video.id=video_facility.videoId AND video_facility.facilityId=?) OR (video.id=video_facility.videoId AND video_facility.facilityId=?) ) ORDER BY id DESC LIMIT ?,?;",
            new Object[] { facilityId, parentId, start, ende }, new VideoRowMapper());
    try {
        this.fillVideoListWithProperties(result);
    } catch (IOException e) {
    }

    return result;
}

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

/**
 * Gets the by facility id.//from www .  j  a  v a2  s .  c  om
 *
 * @param facilityId the facility id
 * @return the by facility id
 */
@SuppressWarnings("unchecked")
public List<Video> getByFacilityId(Integer facilityId) {

    JdbcTemplate select = new JdbcTemplate(this.getDataSource());
    List<Video> result = select.query(
            "SELECT * FROM video, video_facility WHERE video.id=video_facility.videoId AND video_facility.facilityId=? ORDER BY id DESC;",
            new Object[] { facilityId }, new VideoRowMapper());
    try {
        this.fillVideoListWithProperties(result);
    } catch (NullPointerException npe) {
    } catch (IOException e) {
    }

    return result;
}

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

/**
 * Gets the by id./*from  w w  w  . ja v a  2  s  .  com*/
 *
 * @param id the id
 * @return the by id
 */
@SuppressWarnings("unchecked")
public List<Video> getById(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());
    try {
        this.fillVideoListWithProperties(returnList);
    } catch (IOException e) {
    }

    return returnList;
}

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

/**
 * Gets the open access videos by lectureseries number.
 *
 * @param lectureseriesId the lectureseries id
 * @param sort the sort//  w w w. j a v a2 s .co  m
 * @return the open access videos by lectureseries number
 */
@SuppressWarnings("unchecked")
public List<Video> getOpenAccessVideosByLectureseriesNumber(Integer lectureseriesId, String sort) {
    JdbcTemplate select = new JdbcTemplate(this.getDataSource());
    List<Video> result = null;
    result = 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.lectureseriesId=? AND v.openAccess=1 AND v.metadataId = m.id AND v.lectureseriesId = l.id AND v.facilityId=f.id  ) ORDER BY generationDate "
                    + sort,
            new Object[] { lectureseriesId }, new VideoResultSearchRowMapper());
    return result;
}

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

/**
 * Gets the closed access videos by lectureseries id.
 *
 * @param lectureseriesId the lectureseries id
 * @param sort the sort/*from ww w.j av a2s .  c om*/
 * @return the closed access videos by lectureseries id
 */
@SuppressWarnings("unchecked")
public List<Video> getClosedAccessVideosByLectureseriesId(Integer lectureseriesId, String sort) {
    JdbcTemplate select = new JdbcTemplate(this.getDataSource());
    List<Video> result = null;
    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 openAccess=0 AND surl>'') ORDER BY generationDate "
                    + sort,
            new Object[] { lectureseriesId }, new VideoRowMapper());
    try {
        this.fillVideoListWithProperties(result);
    } catch (IOException e) {
    }
    return result;
}

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

/**
 * Gets the open access videos by lectureseries number.
 *
 * @param lectureseriesId the lectureseries id
 * @param sort the sort//from  w ww . ja v a 2s  .  c  o  m
 * @return the open access videos by lectureseries number
 */
@SuppressWarnings("unchecked")
public List<Video> getOpenAccessVideosByLectureseriesNumberAndFillWP(Integer lectureseriesId, String sort) {
    JdbcTemplate select = new JdbcTemplate(this.getDataSource());
    List<Video> result = null;
    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 openAccess=1 ) ORDER BY generationDate "
                    + sort,
            new Object[] { lectureseriesId }, new VideoRowMapper());
    try {
        fillVideoListWithProperties(result);
    } catch (IOException e) {
        e.printStackTrace();
    }
    return result;
}

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

/**
 * Gets the last videos from list./*w  ww  .  j  av  a 2s .  c  o  m*/
 *
 * @return the last video list
 */
public List<Video> getLastVideoList(int first, int last) {
    JdbcTemplate select = new JdbcTemplate(this.getDataSource());
    @SuppressWarnings("unchecked")
    List<Video> videoList = 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, lastvideolist lhl WHERE (v.openAccess=1 AND v.id=lhl.videoId AND v.metadataId = m.id AND v.lectureseriesId = l.id AND v.facilityId=f.id) LIMIT ?, ?;",
            new Object[] { first, last }, new VideoResultSearchRowMapper());
    return videoList;
}

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

/**
 * Gets the by producer id./*from   ww  w. jav  a 2  s.c  o m*/
 *
 * @param remoteUserId the remote user id
 * @return the by producer id
 */
@SuppressWarnings("unchecked")
public List<Video> getByProducerId(int remoteUserId) {
    JdbcTemplate select = new JdbcTemplate(this.getDataSource());
    String request = "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=? ORDER BY id DESC";
    List<Video> result = select.query(request, new Object[] { remoteUserId }, new VideoRowMapper());
    try {
        this.fillVideoListWithProperties(result);
    } catch (IOException e) {
    }
    return result;
}

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

/**
 * Gets the popular videos.//from  w w w . ja  v  a2  s  . c o  m
 *
 * @param start the start
 * @param ende the ende
 * @param facilityId the facility id
 * @return the popular videos
 */
@SuppressWarnings("unchecked")
public List<Video> getPopularVideos(Integer start, Integer ende) {

    List<Video> returnList = new ArrayList<Video>();
    String sqlquery = "";
    JdbcTemplate jdbst = new JdbcTemplate(this.getDataSource());

    // no facility
    sqlquery = "SELECT v.id, v.title, m.creator, l.name, v.filename,  v.generationDate, f.typ, v.lectureseriesId, v.metadataId, v.hostId, v.producerId FROM videohitlist vh, video v, metadata m, lectureseries l, facility f WHERE (v.id = vh.id AND v.metadataId = m.id AND v.lectureseriesId = l.id AND v.facilityId=f.id) ORDER BY hitsperday DESC LIMIT ?, ?";
    returnList = jdbst.query(sqlquery, new Object[] { start, ende }, new VideoResultSearchRowMapper());

    return returnList;
}

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

/**
 * Gets the video list for segments by user id.
 *
 * @param remoteUserId the remote user id
 * @return the video list for segments by user id
 */// w w w. j  av  a 2 s  . com
@SuppressWarnings("unchecked")
public List<Video> getVideoListForSegmentsByUserId(int remoteUserId) {
    JdbcTemplate select = new JdbcTemplate(this.getDataSource());
    String request = "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, segment_user_video WHERE (video.id = segment_user_video.videoId AND segment_user_video.userId = ?) GROUP BY id DESC";
    List<Video> result = select.query(request, new Object[] { remoteUserId }, new VideoRowMapper());
    try {
        this.fillVideoListWithProperties(result);
    } catch (IOException e) {
    }
    return result;
}