Example usage for org.hibernate.type LongType INSTANCE

Introduction

In this page you can find the example usage for org.hibernate.type LongType INSTANCE.

Prototype

LongType INSTANCE

To view the source code for org.hibernate.type LongType INSTANCE.

Click Source Link

Usage

From source file:org.yamj.core.database.dao.ApiDao.java

License:Open Source License

/**
 * Get a count of the jobs along with a count
 *
 * @param requiredJobs/*from  w  w  w.j a va 2s.  c  om*/
 * @return
 */
public List<CountGeneric> getJobCount(List<String> requiredJobs) {
    LOG.info("getJobCount: Required Jobs: {}", (requiredJobs == null ? "all" : requiredJobs));
    SqlScalars sqlScalars = new SqlScalars();

    sqlScalars.addToSql("SELECT job AS item, COUNT(*) AS count");
    sqlScalars.addToSql("FROM cast_crew");
    if (CollectionUtils.isNotEmpty(requiredJobs)) {
        sqlScalars.addToSql("WHERE job IN (:joblist)");
        sqlScalars.addParameter("joblist", requiredJobs);
    }
    sqlScalars.addToSql("GROUP BY job");

    sqlScalars.addScalar("item", StringType.INSTANCE);
    sqlScalars.addScalar("count", LongType.INSTANCE);

    return executeQueryWithTransform(CountGeneric.class, sqlScalars, null);
}

---

From source file:org.yamj.core.database.dao.ApiDao.java

License:Open Source License

public void statSeriesCount() {
    SqlScalars sqlScalars = new SqlScalars();
    sqlScalars.addToSql("SELECT s.id AS seriesId, title, start_year AS seriesYear");
    sqlScalars.addToSql("FROM series s");

    sqlScalars.addScalar(SERIES_ID, LongType.INSTANCE);
    sqlScalars.addScalar(TITLE, StringType.INSTANCE);
    sqlScalars.addScalar(SERIES_YEAR, IntegerType.INSTANCE);

    // Get the results
    List<ApiSeriesInfoDTO> seriesResults = executeQueryWithTransform(ApiSeriesInfoDTO.class, sqlScalars, null);
    if (!seriesResults.isEmpty()) {
        // Set the default oldest and newest
        ApiSeriesInfoDTO oldest = seriesResults.get(0);
        ApiSeriesInfoDTO newest = seriesResults.get(0);

        for (ApiSeriesInfoDTO series : seriesResults) {
            if (series.getYear() > newest.getYear()) {
                newest = series;// w  w w.j  a va2 s  .  c  o m
            }
            if (series.getYear() < oldest.getYear()) {
                oldest = series;
            }
        }
    }

    // Process the results into statistics
}

---

From source file:org.yamj.core.database.dao.ApiDao.java

License:Open Source License

/**
 * Get list of external IDs for a metadata object.
 * /*from  w w w  .  j  a  v a 2s .co m*/
 * @param type the metadata type
 * @param id the id of the metadata object
 * @return
 */
private List<ApiExternalIdDTO> getExternalIdsForId(MetaDataType type, Long id) {
    SqlScalars sqlScalars = new SqlScalars();

    if (type == MetaDataType.SERIES) {
        sqlScalars.addToSql(
                "SELECT ids.series_id AS id, ids.sourcedb_id AS externalId, ids.sourcedb AS sourcedb,");
        sqlScalars.addToSql(
                "concat(coalesce(ser.skip_scan_api,''),';',coalesce(ser.skip_scan_nfo,'')) like concat('%',ids.sourcedb,'%') as skipped");
        sqlScalars.addToSql("FROM series ser, series_ids ids");
        sqlScalars.addToSql("WHERE ser.id=:id AND ids.series_id=ser.id");
    } else if (type == MetaDataType.SEASON) {
        sqlScalars.addToSql(
                "SELECT ids.season_id AS id, ids.sourcedb_id AS externalId, ids.sourcedb AS sourcedb, 0 as skipped");
        sqlScalars.addToSql("FROM season_ids ids");
        sqlScalars.addToSql("WHERE ids.season_id=:id");
    } else if (type == MetaDataType.PERSON) {
        sqlScalars.addToSql(
                "SELECT ids.person_id AS id, ids.sourcedb_id AS externalId, ids.sourcedb AS sourcedb,");
        sqlScalars.addToSql("coalesce(p.skip_scan_api,'') like concat('%',ids.sourcedb,'%') as skipped");
        sqlScalars.addToSql("FROM person p, person_ids ids");
        sqlScalars.addToSql("WHERE p.id=:id AND ids.person_id=p.id");
    } else {
        sqlScalars.addToSql(
                "SELECT ids.videodata_id AS id, ids.sourcedb_id AS externalId, ids.sourcedb AS sourcedb,");
        sqlScalars.addToSql(
                "concat(coalesce(vd.skip_scan_api,''),';',coalesce(vd.skip_scan_nfo,'')) like concat('%',ids.sourcedb,'%') as skipped");
        sqlScalars.addToSql("FROM videodata vd, videodata_ids ids");
        sqlScalars.addToSql("WHERE vd.id=:id AND ids.videodata_id=vd.id");
    }

    sqlScalars.addParameter(ID, id);

    sqlScalars.addScalar(ID, LongType.INSTANCE);
    sqlScalars.addScalar("externalId", StringType.INSTANCE);
    sqlScalars.addScalar("sourcedb", StringType.INSTANCE);
    sqlScalars.addScalar("skipped", BooleanType.INSTANCE);

    return executeQueryWithTransform(ApiExternalIdDTO.class, sqlScalars, null);
}

---

From source file:org.yamj.core.database.dao.ApiDao.java

License:Open Source License

private List<ApiBoxedSetDTO> getBoxedSetsForId(MetaDataType type, Long id) {
    SqlScalars sqlScalars = new SqlScalars();
    sqlScalars.addToSql("SELECT bs.id, bs.name,");
    sqlScalars.addToSql(//  w w  w .  j ava2 s . c om
            "(select count(bo2.id) from boxed_set_order bo2 where bo2.boxedset_id=bs.id) as memberCount");
    sqlScalars.addToSql("FROM boxed_set bs");
    sqlScalars.addToSql("JOIN boxed_set_order bo ON bs.id=bo.boxedset_id");
    if (type == MetaDataType.SERIES) {
        sqlScalars.addToSql("WHERE bo.series_id=:id");
    } else if (type == MetaDataType.SEASON) {
        sqlScalars.addToSql("JOIN season sea ON sea.series_id=bo.series_id AND sea.id=:id");
    } else if (type == MetaDataType.EPISODE) {
        sqlScalars.addToSql("JOIN season sea ON sea.series_id=bo.series_id");
        sqlScalars.addToSql("JOIN videodata vd ON vd.season_id=sea.id AND vd.id=:id");
    } else {
        // defaults to movie
        sqlScalars.addToSql("WHERE bo.videodata_id=:id");
    }
    sqlScalars.addToSql("GROUP BY bs.id, bs.name");

    sqlScalars.addParameter(ID, id);

    sqlScalars.addScalar(ID, LongType.INSTANCE);
    sqlScalars.addScalar("name", StringType.INSTANCE);
    sqlScalars.addScalar("memberCount", IntegerType.INSTANCE);

    return executeQueryWithTransform(ApiBoxedSetDTO.class, sqlScalars, null);
}

---

From source file:org.yamj.core.database.dao.ApiDao.java

License:Open Source License

public ApiBoxedSetDTO getBoxedSet(ApiWrapperSingle<ApiBoxedSetDTO> wrapper) {
    OptionsBoxedSet options = (OptionsBoxedSet) wrapper.getOptions();
    SqlScalars sqlScalars = generateSqlForBoxedSet(options);

    List<ApiBoxedSetDTO> boxsets = executeQueryWithTransform(ApiBoxedSetDTO.class, sqlScalars, wrapper);
    if (CollectionUtils.isEmpty(boxsets)) {
        return null;
    }//from w w  w . j  a v a 2s .c o m

    // get the first boxed set which has been retrieved by the given id
    ApiBoxedSetDTO boxedSet = boxsets.get(0);

    if (options.hasDataItem(DataItem.MEMBER)) {
        // get members
        sqlScalars = new SqlScalars();
        sqlScalars.addToSql("SELECT vd.id");
        sqlScalars.addToSql(SQL_COMMA_SPACE_QUOTE + MetaDataType.MOVIE + SQL_AS_VIDEO_TYPE_STRING);
        sqlScalars.addToSql(
                ", bo1.ordering, vd.title, vd.title_original AS originalTitle, vd.publication_year AS year,vd.release_date AS releaseDate,");
        sqlScalars.addToSql("min(vd.watched_nfo or vd.watched_file or vd.watched_api) as watched");
        sqlScalars.addToSql(DataItemTools.addSqlDataItems(options.splitDataItems(), "vd").toString());
        sqlScalars.addToSql("FROM boxed_set_order bo1");
        sqlScalars.addToSql("JOIN videodata vd ON bo1.videodata_id=vd.id");
        sqlScalars.addToSql("WHERE bo1.boxedset_id=" + options.getId());
        sqlScalars.addToSql(SQL_UNION);
        sqlScalars.addToSql("SELECT ser.id");
        sqlScalars.addToSql(SQL_COMMA_SPACE_QUOTE + MetaDataType.SERIES + SQL_AS_VIDEO_TYPE_STRING);
        sqlScalars.addToSql(
                ", bo2.ordering, ser.title, ser.title_original AS originalTitle, ser.start_year AS year,null as releaseDate,");
        sqlScalars.addToSql(
                "(SELECT min(vid.watched_nfo or vid.watched_file or vid.watched_api) from videodata vid,season sea where vid.season_id=sea.id and sea.series_id=ser.id) as watched");
        sqlScalars.addToSql(DataItemTools.addSqlDataItems(options.splitDataItems(), "ser").toString());
        sqlScalars.addToSql("FROM boxed_set_order bo2");
        sqlScalars.addToSql("JOIN series ser ON bo2.series_id=ser.id");
        sqlScalars.addToSql("WHERE bo2.boxedset_id=" + options.getId());
        sqlScalars.addToSql(options.getSortString());

        sqlScalars.addScalar(ID, LongType.INSTANCE);
        sqlScalars.addScalar("videoTypeString", StringType.INSTANCE);
        sqlScalars.addScalar("ordering", IntegerType.INSTANCE);
        sqlScalars.addScalar(TITLE, StringType.INSTANCE);
        sqlScalars.addScalar(ORIGINAL_TITLE, StringType.INSTANCE);
        sqlScalars.addScalar(YEAR, IntegerType.INSTANCE);
        sqlScalars.addScalar("releaseDate", DateType.INSTANCE);
        sqlScalars.addScalar(WATCHED, BooleanType.INSTANCE);
        DataItemTools.addDataItemScalars(sqlScalars, options.splitDataItems());

        List<ApiBoxedSetMemberDTO> members = this.executeQueryWithTransform(ApiBoxedSetMemberDTO.class,
                sqlScalars, null);
        boxedSet.setMembers(members);
    }

    if (options.hasDataItem(DataItem.ARTWORK)) {
        LOG.trace("Adding artwork for ID {}", options.getId());
        Map<Long, List<ApiArtworkDTO>> artworkList;
        if (CollectionUtils.isNotEmpty(options.getArtworkTypes())) {
            artworkList = getArtworkForId(MetaDataType.BOXSET, options.getId(), options.getArtworkTypes());
        } else {
            artworkList = getArtworkForId(MetaDataType.BOXSET, options.getId());
        }

        if (artworkList.containsKey(options.getId())) {
            boxedSet.setArtwork(artworkList.get(options.getId()));
        }
    }

    return boxedSet;
}

---

From source file:org.yamj.core.database.dao.ApiDao.java

License:Open Source License

private static SqlScalars generateSqlForBoxedSet(OptionsBoxedSet options) {
    SqlScalars sqlScalars = new SqlScalars();
    sqlScalars.addToSql(/*from  w w w .ja v  a2 s  .  c  o  m*/
            "SELECT s.id, s.name, count(s.member) as memberCount, min(s.watched_set) as watched FROM (");
    sqlScalars.addToSql("SELECT bs1.id, bs1.name, bo1.id as member,");
    sqlScalars.addToSql("min(vd1.watched_nfo or vd1.watched_file or vd1.watched_api) as watched_set");
    sqlScalars.addToSql("FROM boxed_set bs1");
    sqlScalars.addToSql("LEFT OUTER JOIN boxed_set_order bo1 ON bs1.id=bo1.boxedset_id");
    sqlScalars.addToSql("LEFT OUTER JOIN videodata vd1 ON bo1.videodata_id=vd1.id");
    if (options.getId() > 0L) {
        sqlScalars.addToSql("WHERE bs1.id=" + options.getId());
    }
    sqlScalars.addToSql(SQL_UNION);
    sqlScalars.addToSql("SELECT bs2.id, bs2.name, bo2.id as member,");
    sqlScalars.addToSql(
            "(SELECT min(vid.watched_nfo or vid.watched_file or vid.watched_api) from videodata vid,season sea where vid.season_id=sea.id and sea.series_id=ser.id) as watched_set");
    sqlScalars.addToSql("FROM boxed_set bs2");
    sqlScalars.addToSql("LEFT OUTER JOIN boxed_set_order bo2 ON bs2.id=bo2.boxedset_id");
    sqlScalars.addToSql("LEFT OUTER JOIN series ser ON bo2.series_id=ser.id");
    if (options.getId() > 0L) {
        sqlScalars.addToSql("WHERE bs2.id=" + options.getId());
    }
    sqlScalars.addToSql(") AS s");
    sqlScalars.addToSql("GROUP BY s.id, s.name");
    if (options.getId() <= 0L) {
        if (options.getWatched() != null) {
            if (options.getWatched()) {
                sqlScalars.addToSql(" HAVING min(s.watched_set)=1");
            } else {
                sqlScalars.addToSql(" HAVING min(s.watched_set)=0");
            }
        }
        sqlScalars.addToSql(options.getSortString());
    }

    sqlScalars.addScalar(ID, LongType.INSTANCE);
    sqlScalars.addScalar("name", StringType.INSTANCE);
    sqlScalars.addScalar("memberCount", IntegerType.INSTANCE);
    sqlScalars.addScalar(WATCHED, BooleanType.INSTANCE);

    return sqlScalars;
}

---

From source file:org.yamj.core.database.dao.CommonDao.java

License:Open Source License

public List<ApiGenreDTO> getGenres(ApiWrapperList<ApiGenreDTO> wrapper) {
    OptionsSingleType options = (OptionsSingleType) wrapper.getOptions();

    SqlScalars sqlScalars = new SqlScalars();
    sqlScalars.addScalar("name", StringType.INSTANCE);

    sqlScalars.addToSql("SELECT DISTINCT ");
    if (options.getFull()) {
        sqlScalars.addToSql("g.id as id, g.name as name, ");
        sqlScalars.addToSql("CASE ");
        sqlScalars.addToSql(" WHEN g.target_api is not null THEN g.target_api ");
        sqlScalars.addToSql(" WHEN g.target_xml is not null THEN g.target_xml ");
        sqlScalars.addToSql(" ELSE g.name ");
        sqlScalars.addToSql("END as target ");

        sqlScalars.addScalar("id", LongType.INSTANCE);
        sqlScalars.addScalar("target", StringType.INSTANCE);
    } else {/*from ww w  . j  a  va  2s  .  co m*/
        sqlScalars.addToSql("CASE ");
        sqlScalars.addToSql(" WHEN g.target_api is not null THEN g.target_api ");
        sqlScalars.addToSql(" WHEN g.target_xml is not null THEN g.target_xml ");
        sqlScalars.addToSql(" ELSE g.name ");
        sqlScalars.addToSql("END as name ");
    }
    sqlScalars.addToSql("FROM genre g ");

    boolean addWhere = true;
    if (options.getType() != null) {
        if (MetaDataType.MOVIE == options.getType()) {
            sqlScalars.addToSql("JOIN videodata_genres vg ON g.id=vg.genre_id ");
        } else {
            sqlScalars.addToSql("JOIN series_genres sg ON g.id=sg.genre_id ");
        }
    }
    if (options.getUsed() != null && options.getUsed()) {
        sqlScalars.addToSql("WHERE (exists (select 1 from videodata_genres vg where vg.genre_id=g.id) ");
        sqlScalars.addToSql(" or exists (select 1 from series_genres sg where sg.genre_id=g.id)) ");
        addWhere = false;
    }

    sqlScalars.addToSql(options.getSearchString(addWhere));
    sqlScalars.addToSql(options.getSortString());

    return executeQueryWithTransform(ApiGenreDTO.class, sqlScalars, wrapper);
}

---

From source file:org.yamj.core.database.dao.CommonDao.java

License:Open Source License

public List<ApiGenreDTO> getGenreFilename(ApiWrapperList<ApiGenreDTO> wrapper, String filename) {
    SqlScalars sqlScalars = new SqlScalars();
    sqlScalars.addToSql("SELECT g.id, g.name, ");
    sqlScalars.addToSql("CASE ");
    sqlScalars.addToSql(" WHEN target_api is not null THEN target_api ");
    sqlScalars.addToSql(" WHEN target_xml is not null THEN target_xml ");
    sqlScalars.addToSql(" ELSE name ");
    sqlScalars.addToSql("END as target ");
    sqlScalars.addToSql("FROM mediafile m, mediafile_videodata mv, videodata v, videodata_genres vg, genre g");
    sqlScalars.addToSql("WHERE m.id=mv.mediafile_id");
    sqlScalars.addToSql("AND mv.videodata_id=v.id");
    sqlScalars.addToSql("AND v.id = vg.data_id");
    sqlScalars.addToSql("AND vg.genre_id=g.id");
    sqlScalars.addToSql("AND lower(m.file_name)=:filename");

    sqlScalars.addScalar("id", LongType.INSTANCE);
    sqlScalars.addScalar("name", StringType.INSTANCE);
    sqlScalars.addScalar("target", StringType.INSTANCE);

    sqlScalars.addParameter("filename", filename.toLowerCase());

    return executeQueryWithTransform(ApiGenreDTO.class, sqlScalars, wrapper);
}

---

From source file:org.yamj.core.database.dao.CommonDao.java

License:Open Source License

public List<Studio> getStudios(ApiWrapperList<Studio> wrapper) {
    OptionsSingleType options = (OptionsSingleType) wrapper.getOptions();

    SqlScalars sqlScalars = new SqlScalars();
    sqlScalars.addToSql("SELECT DISTINCT stu.id as id, stu.name as name ");
    sqlScalars.addToSql("FROM studio stu ");

    boolean addWhere = true;
    if (options.getType() != null) {
        if (MetaDataType.MOVIE == options.getType()) {
            sqlScalars.addToSql("JOIN videodata_studios vs ON stu.id=vs.studio_id ");
        } else {//from w  ww.j  ava 2 s  . com
            sqlScalars.addToSql("JOIN series_studios ss ON stu.id=ss.studio_id ");
        }
    }
    if (options.getUsed() != null && options.getUsed()) {
        sqlScalars.addToSql("WHERE (exists (select 1 from videodata_studios vs where vs.studio_id=stu.id) ");
        sqlScalars.addToSql(" or exists (select 1 from series_studios ss where ss.studio_id=stu.id)) ");
        addWhere = false;
    }

    sqlScalars.addToSql(options.getSearchString(addWhere));
    sqlScalars.addToSql(options.getSortString());

    sqlScalars.addScalar("id", LongType.INSTANCE);
    sqlScalars.addScalar("name", StringType.INSTANCE);

    return executeQueryWithTransform(Studio.class, sqlScalars, wrapper);
}

---

From source file:org.yamj.core.database.dao.CommonDao.java

License:Open Source License

public List<ApiCountryDTO> getCountries(ApiWrapperList<ApiCountryDTO> wrapper) {
    OptionsSingleType options = (OptionsSingleType) wrapper.getOptions();

    SqlScalars sqlScalars = new SqlScalars();
    sqlScalars.addToSql("SELECT c.id, c.country_code as countryCode ");
    sqlScalars.addToSql("FROM country c ");

    if (MetaDataType.MOVIE == options.getType()) {
        sqlScalars.addToSql("JOIN videodata_countries vc ON c.id=vc.country_id ");
    } else if (MetaDataType.SERIES == options.getType()) {
        sqlScalars.addToSql("JOIN series_countries sc ON c.id=sc.country_id ");
    }//from w w  w .  j a  va2s.co m

    sqlScalars.addToSql("WHERE (exists (select 1 from videodata_countries vc where vc.country_id=c.id) ");
    sqlScalars.addToSql(" or exists (select 1 from series_countries sc where sc.country_id=c.id)) ");

    sqlScalars.addScalar("id", LongType.INSTANCE);
    sqlScalars.addScalar("countryCode", StringType.INSTANCE);
    sqlScalars.addToSql(options.getSortString());

    return executeQueryWithTransform(ApiCountryDTO.class, sqlScalars, wrapper);
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• »