Example usage for org.hibernate.type StringType INSTANCE

List of usage examples for org.hibernate.type StringType INSTANCE

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.type.*
  5. StringType
  6. INSTANCE

Introduction

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

Prototype

StringType INSTANCE

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

Click Source Link

Usage

From source file:org.squashtest.tm.service.internal.repository.hibernate.TestPlanFilteringHelper.java

License:Open Source License

public static void setFilters(Query query, Filtering filtering, ColumnFiltering columnFiltering) { // NOSONAR:START This is basically a huge switch

    if (filtering.isDefined()) {
        query.setParameter("userLogin", filtering.getFilter());
    }//from   w ww  .j a  v a2s.c  o  m

    if (columnFiltering.hasFilter(PROJECT_DATA)) {
        query.setParameter(PROJECT_FILTER, anywhereToken(columnFiltering.getFilter(PROJECT_DATA)));
    }

    if (columnFiltering.hasFilter(REFERENCE_DATA)) {
        query.setParameter(REFERENCE_FILTER, anywhereToken(columnFiltering.getFilter(REFERENCE_DATA)));
    }

    if (columnFiltering.hasFilter(TESTCASE_DATA)) {
        query.setParameter(TESTCASE_FILTER, anywhereToken(columnFiltering.getFilter(TESTCASE_DATA)));
    }

    if (columnFiltering.hasFilter(WEIGHT_DATA)) {
        query.setParameter(WEIGHT_FILTER, columnFiltering.getFilter(WEIGHT_DATA), StringType.INSTANCE);
    }

    if (columnFiltering.hasFilter(DATASET_DATA)) {
        query.setParameter(DATASET_FILTER, anywhereToken(columnFiltering.getFilter(DATASET_DATA)));
    }

    if (columnFiltering.hasFilter(TESTSUITE_DATA)) {
        query.setParameter(TESTSUITE_FILTER, anywhereToken(columnFiltering.getFilter(TESTSUITE_DATA)));
    }

    if (columnFiltering.hasFilter(STATUS_DATA)) {
        query.setParameter(STATUS_FILTER, columnFiltering.getFilter(STATUS_DATA), StringType.INSTANCE);
    }

    if (columnFiltering.hasFilter(USER_DATA) && !"0".equals(columnFiltering.getFilter(USER_DATA))) {
        query.setParameter(USER_FILTER, Long.parseLong(columnFiltering.getFilter(USER_DATA)));
    }

    if (columnFiltering.hasFilter(TestPlanFilteringHelper.LASTEXEC_DATA)) {
        setQueryStartAndEndDateParameters(columnFiltering, query);
    }

}

From source file:org.squashtest.tm.service.security.acls.jdbc.DerivedPermissionsManager.java

License:Open Source License

private Collection<Long> findUsers(ObjectIdentity identity) {

    // first find the parties managing that thing
    Query query = em.unwrap(Session.class).createSQLQuery(FIND_PARTIES_USING_IDENTITY);
    query.setParameter("id", identity.getIdentifier(), LongType.INSTANCE);
    query.setParameter("class", identity.getType(), StringType.INSTANCE);
    query.setResultTransformer(new SqLIdResultTransformer());

    Collection<Long> partyIds = query.list();

    // then find the corresponding users
    Collection<Long> userIds = new HashSet<>();
    for (Long id : partyIds) {
        userIds.addAll(findMembers(id));
    }/*ww w .  j av  a2 s.c  o  m*/

    return userIds;

}

From source file:org.yamj.core.api.model.builder.DataItemTools.java

License:Open Source License

/**
 * Add scalars for the data items//from   w w w  .  j  ava  2s .c  o  m
 *
 * @param sqlScalars
 * @param dataItems
 */
public static void addDataItemScalars(SqlScalars sqlScalars, List<DataItem> dataItems) {
    for (DataItem item : dataItems) {
        if (item.isNotColumn()) {
            // This is not a specific scalar and is not needed
            continue;
        }

        if (item == DataItem.TOP_RANK) {
            sqlScalars.addScalar("topRank", IntegerType.INSTANCE);
        } else {
            //  default approach
            sqlScalars.addScalar(item.toString().toLowerCase(), StringType.INSTANCE);
        }
    }
}

From source file:org.yamj.core.api.model.DataItemTools.java

License:Open Source License

/**
 * Add scalars for the data items//  www .j a  v  a 2  s  .co m
 *
 * @param sqlScalars
 * @param dataItems
 */
public static void addDataItemScalars(SqlScalars sqlScalars, List<DataItem> dataItems) {
    for (DataItem item : dataItems) {
        if (item.isNotColumn()) {
            // This is not a specific scalar and is not needed
            continue;
        }
        if (item == DataItem.TOP_RANK) {
            sqlScalars.addScalar("topRank", LongType.INSTANCE);
            continue;
        }
        // This is the default approach
        sqlScalars.addScalar(item.toString().toLowerCase(), StringType.INSTANCE);
    }
}

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

License:Open Source License

/**
 * Generate the query and load the results into the wrapper
 *
 * @param wrapper/*from  w w  w  .j  av a  2  s. c om*/
 */
@SuppressWarnings({ "unchecked", "rawtypes" })
public void getVideoList(ApiWrapperList<ApiVideoDTO> wrapper) {
    OptionsIndexVideo options = (OptionsIndexVideo) wrapper.getOptions();
    IndexParams params = new IndexParams(options);

    SqlScalars sqlScalars = new SqlScalars(generateSqlForVideoList(params));
    sqlScalars.addScalar(ID, LongType.INSTANCE);
    sqlScalars.addScalar("videoTypeString", StringType.INSTANCE);
    sqlScalars.addScalar(TITLE, StringType.INSTANCE);
    sqlScalars.addScalar(ORIGINAL_TITLE, StringType.INSTANCE);
    sqlScalars.addScalar(VIDEO_YEAR, IntegerType.INSTANCE);
    sqlScalars.addScalar(SERIES_ID, LongType.INSTANCE);
    sqlScalars.addScalar(SEASON_ID, LongType.INSTANCE);
    sqlScalars.addScalar(SEASON, LongType.INSTANCE);
    sqlScalars.addScalar(WATCHED, BooleanType.INSTANCE);

    if (params.includeNewest() || params.excludeNewest()) {
        sqlScalars.addScalar("newest", TimestampType.INSTANCE);
    }

    // add Scalars for additional data item columns
    DataItemTools.addDataItemScalars(sqlScalars, params.getDataItems());
    // add additional parameters
    params.addScalarParameters(sqlScalars);

    DataItemTools.addDataItemScalars(sqlScalars, params.getDataItems());

    List<ApiVideoDTO> queryResults = executeQueryWithTransform(ApiVideoDTO.class, sqlScalars, wrapper);
    wrapper.setResults(queryResults);

    if (CollectionUtils.isNotEmpty(queryResults)) {

        if (params.hasDataItem(DataItem.GENRE)) {
            LOG.trace("Adding genres to index videos");
            for (ApiVideoDTO video : queryResults) {
                video.setGenres(this.getGenresForId(video.getVideoType(), video.getId()));
            }
        }

        if (params.hasDataItem(DataItem.STUDIO)) {
            LOG.trace("Adding studios to index videos");
            for (ApiVideoDTO video : queryResults) {
                video.setStudios(this.getStudiosForId(video.getVideoType(), video.getId()));
            }
        }

        if (params.hasDataItem(DataItem.COUNTRY)) {
            LOG.trace("Adding countries to index videos");
            for (ApiVideoDTO video : queryResults) {
                video.setCountries(this.getCountriesForId(video.getVideoType(), video.getId()));
            }
        }

        if (params.hasDataItem(DataItem.CERTIFICATION)) {
            LOG.trace("Adding certifications to index videos");
            for (ApiVideoDTO video : queryResults) {
                video.setCertifications(this.getCertificationsForId(video.getVideoType(), video.getId()));
            }
        }

        if (params.hasDataItem(DataItem.RATING)) {
            LOG.trace("Adding ratings to index videos");
            for (ApiVideoDTO video : queryResults) {
                video.setRatings(this.getRatingsForId(video.getVideoType(), video.getId()));
            }
        }

        if (params.hasDataItem(DataItem.AWARD)) {
            LOG.trace("Adding awards to index videos");
            for (ApiVideoDTO video : queryResults) {
                video.setAwards(this.getAwardsForId(video.getVideoType(), video.getId()));
            }
        }

        if (params.hasDataItem(DataItem.EXTERNALID)) {
            LOG.trace("Adding external IDs to index videos");
            for (ApiVideoDTO video : queryResults) {
                video.setExternalIds(this.getExternalIdsForId(video.getVideoType(), video.getId()));
            }
        }

        if (params.hasDataItem(DataItem.BOXSET)) {
            LOG.trace("Adding boxed sets to index videos");
            for (ApiVideoDTO video : queryResults) {
                video.setBoxedSets(this.getBoxedSetsForId(video.getVideoType(), video.getId()));
            }
        }

        if (params.hasDataItem(DataItem.TRAILER)) {
            LOG.trace("Adding trailers to index videos");
            for (ApiVideoDTO video : queryResults) {
                video.setTrailers(this.getTrailersForId(video.getVideoType(), video.getId()));
            }
        }

        if (CollectionUtils.isNotEmpty(options.getArtworkTypes())) {
            // Create and populate the ID list
            Map<MetaDataType, List<Long>> ids = new EnumMap<>(MetaDataType.class);
            for (MetaDataType mdt : MetaDataType.values()) {
                ids.put(mdt, new ArrayList());
            }

            Map<String, ApiVideoDTO> results = new HashMap<>();

            for (ApiVideoDTO video : queryResults) {
                // Add the item to the map for further processing
                results.put(ApiArtworkDTO.makeKey(video), video);
                // Add the ID to the list
                ids.get(video.getVideoType()).add(video.getId());
            }

            boolean foundArtworkIds = Boolean.FALSE; // Check to see that we have artwork to find
            // Remove any blank entries
            for (MetaDataType mdt : MetaDataType.values()) {
                if (CollectionUtils.isEmpty(ids.get(mdt))) {
                    ids.remove(mdt);
                } else {
                    // We've found an artwork, so we can continue
                    foundArtworkIds = Boolean.TRUE;
                }
            }

            if (foundArtworkIds) {
                LOG.trace("Found artwork to process, IDs: {}", ids);
                addArtworks(ids, results, options);
            } else {
                LOG.trace("No artwork found to process, skipping.");
            }
        } else {
            LOG.trace("Artwork not required, skipping.");
        }
    } else {
        LOG.debug("No results found to process.");
    }
}

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

License:Open Source License

/**
 * Search the list of IDs for artwork and add to the artworkList.
 *
 * @param ids/*from   ww  w. jav  a  2s.  c o m*/
 * @param artworkList
 * @param options
 */
private void addArtworks(Map<MetaDataType, List<Long>> ids, Map<String, ApiVideoDTO> artworkList,
        OptionsIndexVideo options) {
    Set<String> artworkRequired = options.getArtworkTypes();
    LOG.debug("Artwork required: {}", artworkRequired);

    if (CollectionUtils.isNotEmpty(artworkRequired)) {
        SqlScalars sqlScalars = new SqlScalars();
        boolean hasMovie = CollectionUtils.isNotEmpty(ids.get(MetaDataType.MOVIE));
        boolean hasSeries = CollectionUtils.isNotEmpty(ids.get(MetaDataType.SERIES));
        boolean hasSeason = CollectionUtils.isNotEmpty(ids.get(MetaDataType.SEASON));
        boolean hasEpisode = CollectionUtils.isNotEmpty(ids.get(MetaDataType.EPISODE));

        if (hasMovie) {
            sqlScalars.addToSql(
                    "SELECT 'MOVIE' as sourceString, v.id as sourceId, a.id as artworkId, al.id as locatedId, ag.id as generatedId, a.artwork_type as artworkTypeString, ag.cache_dir as cacheDir, ag.cache_filename as cacheFilename");
            sqlScalars.addToSql(" FROM videodata v, artwork a");
            sqlScalars.addToSql(SQL_LEFT_JOIN_ARTWORK_LOCATED);
            sqlScalars.addToSql(SQL_LEFT_JOIN_ARTWORK_GENERATED);
            sqlScalars.addToSql(" WHERE v.id=a.videodata_id");
            sqlScalars.addToSql(" AND v.episode<0");
            sqlScalars.addToSql(" AND v.id IN (:movielist)");
            sqlScalars.addToSql(SQL_ARTWORK_TYPE_IN_ARTWORKLIST);
        }

        if (hasMovie && hasSeries) {
            sqlScalars.addToSql(" UNION");
        }

        if (hasSeries) {
            sqlScalars.addToSql(
                    " SELECT 'SERIES' as sourceString, s.id as sourceId, a.id as artworkId, al.id as locatedId, ag.id as generatedId, a.artwork_type as artworkTypeString, ag.cache_dir as cacheDir, ag.cache_filename as cacheFilename");
            sqlScalars.addToSql(" FROM series s, artwork a");
            sqlScalars.addToSql(SQL_LEFT_JOIN_ARTWORK_LOCATED);
            sqlScalars.addToSql(SQL_LEFT_JOIN_ARTWORK_GENERATED);
            sqlScalars.addToSql(" WHERE s.id=a.series_id");
            sqlScalars.addToSql(" AND s.id IN (:serieslist)");
            sqlScalars.addToSql(SQL_ARTWORK_TYPE_IN_ARTWORKLIST);
        }

        if ((hasMovie || hasSeries) && hasSeason) {
            sqlScalars.addToSql(" UNION");
        }

        if (hasSeason) {
            sqlScalars.addToSql(
                    " SELECT 'SEASON' as sourceString, s.id as sourceId, a.id as artworkId, al.id as locatedId, ag.id as generatedId, a.artwork_type as artworkTypeString, ag.cache_dir as cacheDir, ag.cache_filename as cacheFilename");
            sqlScalars.addToSql(" FROM season s, artwork a");
            sqlScalars.addToSql(SQL_LEFT_JOIN_ARTWORK_LOCATED);
            sqlScalars.addToSql(SQL_LEFT_JOIN_ARTWORK_GENERATED);
            sqlScalars.addToSql(" WHERE s.id=a.season_id");
            sqlScalars.addToSql(" AND s.id IN (:seasonlist)");
            sqlScalars.addToSql(SQL_ARTWORK_TYPE_IN_ARTWORKLIST);
        }

        if ((hasMovie || hasSeries || hasSeason) && hasEpisode) {
            sqlScalars.addToSql(" UNION");
        }

        if (hasEpisode) {
            sqlScalars.addToSql(
                    "SELECT 'EPISODE' as sourceString, v.id as sourceId, a.id as artworkId, al.id as locatedId, ag.id as generatedId, a.artwork_type as artworkTypeString, ag.cache_dir as cacheDir, ag.cache_filename as cacheFilename");
            sqlScalars.addToSql(" FROM videodata v, artwork a");
            sqlScalars.addToSql(SQL_LEFT_JOIN_ARTWORK_LOCATED);
            sqlScalars.addToSql(SQL_LEFT_JOIN_ARTWORK_GENERATED);
            sqlScalars.addToSql(" WHERE v.id=a.videodata_id");
            sqlScalars.addToSql(" AND v.episode>-1");
            sqlScalars.addToSql(" AND v.id IN (:episodelist)");
            sqlScalars.addToSql(SQL_ARTWORK_TYPE_IN_ARTWORKLIST);
        }

        sqlScalars.addScalar("sourceString", StringType.INSTANCE);
        sqlScalars.addScalar("sourceId", LongType.INSTANCE);
        sqlScalars.addScalar("artworkId", LongType.INSTANCE);
        sqlScalars.addScalar("locatedId", LongType.INSTANCE);
        sqlScalars.addScalar("generatedId", LongType.INSTANCE);
        sqlScalars.addScalar("artworkTypeString", StringType.INSTANCE);
        sqlScalars.addScalar(CACHE_DIR, StringType.INSTANCE);
        sqlScalars.addScalar(CACHE_FILENAME, StringType.INSTANCE);

        if (hasMovie) {
            sqlScalars.addParameter("movielist", ids.get(MetaDataType.MOVIE));
        }

        if (hasSeries) {
            sqlScalars.addParameter("serieslist", ids.get(MetaDataType.SERIES));
        }

        if (hasSeason) {
            sqlScalars.addParameter("seasonlist", ids.get(MetaDataType.SEASON));
        }

        if (hasEpisode) {
            sqlScalars.addParameter("episodelist", ids.get(MetaDataType.EPISODE));
        }

        sqlScalars.addParameter("artworklist", artworkRequired);

        List<ApiArtworkDTO> results = executeQueryWithTransform(ApiArtworkDTO.class, sqlScalars, null);

        LOG.trace("Found {} artworks", results.size());
        for (ApiArtworkDTO ia : results) {
            LOG.trace("  {} = {}", ia.key(), ia.toString());
            artworkList.get(ia.key()).addArtwork(ia);
        }
    }
}

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

License:Open Source License

public List<ApiFilmographyDTO> retrieveFilmography(long id, SqlScalars sqlScalars) {
    sqlScalars.addScalar(TYPE_STRING, StringType.INSTANCE);
    sqlScalars.addScalar("job", StringType.INSTANCE);
    sqlScalars.addScalar("role", StringType.INSTANCE);
    sqlScalars.addScalar(TITLE, StringType.INSTANCE);
    sqlScalars.addScalar(ORIGINAL_TITLE, StringType.INSTANCE);
    sqlScalars.addScalar(YEAR, IntegerType.INSTANCE);
    sqlScalars.addScalar("yearEnd", IntegerType.INSTANCE);
    sqlScalars.addScalar("releaseDate", DateType.INSTANCE);
    sqlScalars.addScalar("releaseCountryCode", StringType.INSTANCE);
    sqlScalars.addScalar("description", StringType.INSTANCE);
    sqlScalars.addScalar("videoDataId", LongType.INSTANCE);
    sqlScalars.addScalar(SERIES_ID, LongType.INSTANCE);

    sqlScalars.addParameter(ID, id);// w  w w. j  a  v a 2  s .co  m

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

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

License:Open Source License

/**
 * Generates a list of people in a video
 *
 * @param metaDataType//w w  w  .j  a  v a  2 s  . c  om
 * @param options
 * @return
 */
private static SqlScalars generateSqlForVideoPerson(MetaDataType metaDataType, OptionsId options) {
    SqlScalars sqlScalars = new SqlScalars();

    sqlScalars.addToSql("SELECT DISTINCT p.id,");
    if (options.hasDataItem(DataItem.BIOGRAPHY)) {
        sqlScalars.addToSql("p.biography,");
        sqlScalars.addScalar("biography", StringType.INSTANCE);
    }
    sqlScalars.addToSql("p.name,");
    sqlScalars.addToSql("p.first_name as firstName,");
    sqlScalars.addToSql("p.last_name as lastName,");
    sqlScalars.addToSql("p.birth_day AS birthDay,");
    sqlScalars.addToSql("p.birth_place AS birthPlace,");
    sqlScalars.addToSql("p.birth_name AS birthName,");
    sqlScalars.addToSql("p.death_day AS deathDay,");
    sqlScalars.addToSql("p.death_place AS deathPlace,");
    sqlScalars.addToSql("c.job as job,");
    sqlScalars.addToSql("c.role as role ");
    sqlScalars.addToSql("FROM person p ");

    if (metaDataType == MetaDataType.SERIES) {
        sqlScalars.addToSql("JOIN cast_crew c ON c.person_id=p.id ");
        sqlScalars.addToSql("JOIN videodata vd ON vd.id=c.videodata_id ");
        sqlScalars.addToSql("JOIN season sea ON sea.id=vd.season_id and sea.series_id=:id ");
    } else if (metaDataType == MetaDataType.SEASON) {
        sqlScalars.addToSql("JOIN cast_crew c ON c.person_id=p.id ");
        sqlScalars.addToSql("JOIN videodata vd ON vd.id=c.videodata_id and vd.season_id=:id ");
    } else {
        // defaults to movie/episode
        sqlScalars.addToSql("JOIN cast_crew c ON c.person_id=p.id and c.videodata_id=:id ");
    }

    sqlScalars.addToSql(" WHERE p.id=c.person_id ");

    if (MapUtils.isNotEmpty(options.splitJobs())) {
        sqlScalars.addToSql("AND c.job IN (:jobs)");
        sqlScalars.addParameter("jobs", options.getJobTypesAsSet());
    }

    // Add the search string
    sqlScalars.addToSql(options.getSearchString(Boolean.FALSE));
    // This will default to blank if there's no  required
    sqlScalars.addToSql(options.getSortString());

    // Add the ID
    sqlScalars.addParameter(ID, options.getId());

    sqlScalars.addScalar(ID, LongType.INSTANCE);
    sqlScalars.addScalar("name", StringType.INSTANCE);
    sqlScalars.addScalar("firstName", StringType.INSTANCE);
    sqlScalars.addScalar("lastName", StringType.INSTANCE);
    sqlScalars.addScalar("birthDay", DateType.INSTANCE);
    sqlScalars.addScalar("birthPlace", StringType.INSTANCE);
    sqlScalars.addScalar("birthName", StringType.INSTANCE);
    sqlScalars.addScalar("deathDay", DateType.INSTANCE);
    sqlScalars.addScalar("deathPlace", StringType.INSTANCE);
    sqlScalars.addScalar("job", StringType.INSTANCE);
    sqlScalars.addScalar("role", StringType.INSTANCE);

    LOG.debug("SQL ForVideoPerson: {}", sqlScalars.getSql());
    return sqlScalars;
}

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

License:Open Source License

/**
 * Generate the SQL for the information about a person
 *
 * @param options/*  w ww.  ja  v  a2  s .  c o  m*/
 * @return
 */
private static SqlScalars generateSqlForPerson(OptionsId options) {
    SqlScalars sqlScalars = new SqlScalars();
    // Make sure to set the alias for the files for the Transformation into the class
    sqlScalars.addToSql("SELECT DISTINCT p.id,p.name,");
    if (options.hasDataItem(DataItem.BIOGRAPHY)) {
        sqlScalars.addToSql(" p.biography, ");
        sqlScalars.addScalar("biography", StringType.INSTANCE);
    }
    sqlScalars.addToSql("p.first_name AS firstName, ");
    sqlScalars.addToSql("p.last_name AS lastName, ");
    sqlScalars.addToSql("p.birth_day AS birthDay, ");
    sqlScalars.addToSql("p.birth_place AS birthPlace, ");
    sqlScalars.addToSql("p.birth_name AS birthName, ");
    sqlScalars.addToSql("p.death_day AS deathDay, ");
    sqlScalars.addToSql("p.death_place AS deathPlace ");
    sqlScalars.addToSql("FROM person p");

    if (options.getId() > 0L) {
        sqlScalars.addToSql(" WHERE id=:id");
        sqlScalars.addParameter(ID, options.getId());
    } else {
        if (MapUtils.isNotEmpty(options.splitJobs())) {
            sqlScalars.addToSql(", cast_crew c");
        }

        sqlScalars.addToSql(SQL_WHERE_1_EQ_1);

        if (MapUtils.isNotEmpty(options.splitJobs())) {
            sqlScalars.addToSql(" AND p.id=c.person_id");
            sqlScalars.addToSql(" AND c.job IN (:jobs)");
            sqlScalars.addParameter("jobs", options.getJobTypesAsSet());
        }

        // Add the search string
        sqlScalars.addToSql(options.getSearchString(Boolean.FALSE));
        // This will default to blank if there's no sort required
        sqlScalars.addToSql(options.getSortString());
    }

    sqlScalars.addScalar(ID, LongType.INSTANCE);
    sqlScalars.addScalar("name", StringType.INSTANCE);
    sqlScalars.addScalar("firstName", StringType.INSTANCE);
    sqlScalars.addScalar("lastName", StringType.INSTANCE);
    sqlScalars.addScalar("birthDay", DateType.INSTANCE);
    sqlScalars.addScalar("birthPlace", StringType.INSTANCE);
    sqlScalars.addScalar("birthName", StringType.INSTANCE);
    sqlScalars.addScalar("deathDay", DateType.INSTANCE);
    sqlScalars.addScalar("deathPlace", StringType.INSTANCE);

    return sqlScalars;
}

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

License:Open Source License

private static SqlScalars getSqlArtwork(OptionsIndexArtwork options) {
    SqlScalars sqlScalars = new SqlScalars();

    sqlScalars.addToSql("SELECT a.id AS artworkId,");
    sqlScalars.addToSql(" al.id AS locatedId,");
    sqlScalars.addToSql(" ag.id AS generatedId,");
    sqlScalars.addToSql(" a.season_id AS seasonId,");
    sqlScalars.addToSql(" a.series_id AS seriesId,");
    sqlScalars.addToSql(" a.videodata_id AS videodataId,");
    sqlScalars.addToSql(" a.artwork_type AS artworkTypeString,");
    sqlScalars.addToSql(" ag.cache_filename AS cacheFilename,");
    sqlScalars.addToSql(" ag.cache_dir AS cacheDir");
    sqlScalars.addToSql(" FROM artwork a");
    sqlScalars.addToSql(SQL_LEFT_JOIN_ARTWORK_LOCATED);
    sqlScalars.addToSql(SQL_LEFT_JOIN_ARTWORK_GENERATED);
    sqlScalars.addToSql(SQL_WHERE_1_EQ_1); // Make appending restrictions easier
    if (options != null) {
        if (options.getId() > 0L) {
            sqlScalars.addToSql(" AND a.id=:id");
            sqlScalars.addParameter(ID, options.getId());
        }//  w  w w .  j  a v  a 2 s . c om

        if (CollectionUtils.isNotEmpty(options.getArtwork())) {
            sqlScalars.addToSql(SQL_ARTWORK_TYPE_IN_ARTWORKLIST);
            sqlScalars.addParameter("artworklist", options.getArtwork());
        }

        if (CollectionUtils.isNotEmpty(options.getVideo())) {
            StringBuilder sb = new StringBuilder("AND (");
            boolean first = Boolean.TRUE;
            for (String type : options.getVideo()) {
                MetaDataType mdt = MetaDataType.fromString(type);
                if (first) {
                    first = Boolean.FALSE;
                } else {
                    sb.append(" OR");
                }
                if (mdt == MetaDataType.MOVIE) {
                    sb.append(" videodata_id IS NOT NULL");
                } else if (mdt == MetaDataType.SERIES) {
                    sb.append(" series_id IS NOT NULL");
                } else if (mdt == MetaDataType.SEASON) {
                    sb.append(" season_id IS NOT NULL");
                } else if (mdt == MetaDataType.PERSON) {
                    sb.append(" person_id IS NOT NULL");
                } else if (mdt == MetaDataType.BOXSET) {
                    sb.append(" boxedset_id IS NOT NULL");
                }
            }
            sb.append(")");
            sqlScalars.addToSql(sb.toString());
        }
    }

    // Add the scalars
    sqlScalars.addScalar("artworkId", LongType.INSTANCE);
    sqlScalars.addScalar("locatedId", LongType.INSTANCE);
    sqlScalars.addScalar("generatedId", LongType.INSTANCE);
    sqlScalars.addScalar(SEASON_ID, LongType.INSTANCE);
    sqlScalars.addScalar(SERIES_ID, LongType.INSTANCE);
    sqlScalars.addScalar("videodataId", LongType.INSTANCE);
    sqlScalars.addScalar("artworkTypeString", StringType.INSTANCE);
    sqlScalars.addScalar(CACHE_DIR, StringType.INSTANCE);
    sqlScalars.addScalar(CACHE_FILENAME, StringType.INSTANCE);

    return sqlScalars;
}