Java tutorial
///////////////////////////////////////////////////////////// // ContentIdexDaoImpl.java // gooru-api // Created by Gooru on 2014 // Copyright (c) 2014 Gooru. All rights reserved. // http://www.goorulearning.org/ // Permission is hereby granted, free of charge, to any person obtaining // a copy of this software and associated documentation files (the // "Software"), to deal in the Software without restriction, including // without limitation the rights to use, copy, modify, merge, publish, // distribute, sublicense, and/or sell copies of the Software, and to // permit persons to whom the Software is furnished to do so, subject to // the following conditions: // The above copyright notice and this permission notice shall be // included in all copies or substantial portions of the Software. // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. ///////////////////////////////////////////////////////////// /** * */ package org.ednovo.gooru.infrastructure.persistence.hibernate.index; import java.util.List; import java.util.Map; import java.util.Set; import org.ednovo.gooru.core.api.model.Resource; import org.hibernate.Criteria; import org.hibernate.Query; import org.hibernate.SQLQuery; import org.hibernate.type.StandardBasicTypes; import org.springframework.stereotype.Repository; @Repository @SuppressWarnings("unchecked") public class ContentIdexDaoImpl extends IndexDaoImpl implements ContentIndexDao { private final String GET_COLLECTION_INFO_FOR_RESOURCE = "SELECT c.content_id, c.gooru_oid, l.lesson, l.goals, l.vocabulary, l.narration, l.notes, r.distinguish FROM learnguide l INNER JOIN resource r ON r.content_id = l.content_id INNER JOIN content c on c.content_id = l.content_id WHERE c.content_id = :contentId"; private final String GET_SCOLLECTION_INFO_FOR_RESOURCE = "SELECT c.content_id, c.gooru_oid, r.title FROM collection_item ci INNER JOIN content c ON c.content_id=ci.collection_content_id INNER JOIN resource r ON r.content_id=c.content_id WHERE ci.resource_content_id = :contentId"; private final String GET_COLLECTION_TAXONOMY_CODE_ID = "SELECT cc.code_id FROM content_classification cc WHERE cc.content_id = :contentId"; private static final String GET_COLLECTION_ITEMS = "SELECT c.gooru_oid,r.type_name,r.title,c.sharing,ci.*,r.description,info.text,r.grade,r.category,r.url,rs.attribution,rs.domain_name FROM content c INNER JOIN resource r ON r.content_id = c.content_id INNER JOIN collection_item ci ON ci.resource_content_id = c.content_id LEFT JOIN resource_info info ON info.resource_info_id=r.resource_info_id LEFT JOIN resource_source rs ON rs.resource_source_id = r.resource_source_id where ci.collection_content_id=:contentId"; private static final String GET_SEGMENTS_BY_QUIZ_ID = "SELECT name FROM assessment_segment WHERE assessment_id=:contentId"; private static final String GET_COLLECTION_TITLE = "SELECT l.lesson FROM learnguide l INNER JOIN content c ON c.content_id = l.content_id WHERE c.gooru_oid =:gooruOid"; private static final String GET_COLLECTION_SEGMENTS_SQL = "SELECT s.title, s.segment_id from segment s WHERE s.resource_id=:contentId"; private static final String GET_INSTANCE_BY_SEGMENT_SQL = "SELECT title , resource_id from resource_instance where segment_id IN (:segmentIds)"; private static final String GET_QUIZ_BY_QUESTION = "SELECT c.gooru_oid FROM assessment_segment a INNER JOIN assessment_segment_question_assoc ass ON ( ass.segment_id = a.segment_id ) INNER JOIN content c ON (c.content_id = a.assessment_id ) INNER JOIN content ct ON ( ass.question_id = ct.content_id ) WHERE ct.gooru_oid = :gooruOid"; private static final String GET_QUIZ_TITLE = "SELECT a.name FROM assessment a INNER JOIN content c ON (c.content_id = a.assessment_id) WHERE c.gooru_oid =:gooruOid"; private static final String GET_RESOURCE_INFO = "SELECT ri.text, ri.tags, ri.num_of_pages FROM resource_info ri WHERE ri.resource_id= :contentId"; private static final String GET_RESOURCE_INSTANCES = "SELECT ri.resource_instance_id, ri.title, ri.description, ri.narrative FROM resource_instance ri WHERE ri.resource_id= :contentId"; private static final String CONTENT_ID = "contentId"; private static final String GOORU_OID = "gooruOid"; private static final String SEGMENT_IDS = "segmentIds"; private static final String GET_COLLECTION_INFO = "SELECT l.lesson AS lesson,l.vocabulary AS vocabulary,l.goals AS goals,l.notes AS notes,l.narration AS narration,l.source AS source,l.collection_gooru_oid AS collectionGooruOid,l.assessment_gooru_oid AS assessmentGooruOid FROM content c INNER JOIN learnguide l ON l.content_id=c.content_id WHERE c.content_id=:contentId"; private static final String GET_SCOLLECTION_INFO = "SELECT c.collection_type AS 'scollection.collectionType', c.narration_link AS 'scollection.narrationLink', c.notes AS 'scollection.notes', c.key_points AS 'scollection.keyPoints', c.language AS 'scollection.language', c.goals AS 'scollection.goals', c.estimated_time AS 'scollection.estimatedTime', c.network AS 'scollection.network', r.tags, r.text FROM collection c INNER JOIN resource r ON r.content_id =c.content_id WHERE c.content_id =:contentId"; private static final String GET_QUIZ_INFO = "select a.name,a.vocabulary As 'quiz.vocabulary', a.learning_objectives as 'quiz.learningObjectives', a.source as 'quiz.source', a.collection_gooru_oid as 'quiz.collectionGooruOid', a.quiz_gooru_oid as 'quiz.quizGooruOid', a.import_code as 'quiz.importCode', group_concat(seg.name SEPARATOR ' ~~ ') as segmentTitles, l.lesson as 'quiz.quizCollectionName' FROM assessment a left join assessment_segment seg on seg.assessment_id = a.assessment_id left JOIN content cc ON cc.gooru_oid = a.collection_gooru_oid left JOIN learnguide l on l.content_id = cc.content_id WHERE a.assessment_id=:contentId"; private static final String GET_QUESTION_AND_HINT = "select group_concat(aa.answer_text SEPARATOR ' ~~ ') AS 'question.answerTexts',group_concat(ah.hint_text SEPARATOR ' ~~ ') as 'question.hintTexts' FROM assessment_answer aa left join assessment_hint ah on ah.question_id = aa.question_id WHERE aa.question_id=:contentId"; private static final String GET_ASSETS = "select a.name,aqaa.asset_key from assessment_question_asset_assoc aqaa inner join asset a on aqaa.asset_id = a.asset_id where aqaa.question_id=:contentId"; private static final String GET_RATING_BY_CONTENT_ID = "select sum((case when isnull(`ra`.`score`) then 0 when (`ra`.`score` > 0) then 1 else 0 end)) AS `voteUp`,sum((case when isnull(`ra`.`score`) then 0 when (`ra`.`score` < 0) then 1 else 0 end)) AS `voteDown` from (`annotation` `a` join `rating` `ra` on((`ra`.`content_id` = `a`.`content_id`))) WHERE a.resource_id=:contentId group by `a`.`resource_id`"; private static final String GET_SUBSCRIPTION_BY_CONTENT_ID = "select count(0) AS `subscriberCount` from (`content` `c` join `annotation` `a` on((`a`.`resource_id` = `c`.`content_id`))) where (`a`.`type_name` = 'subscription') AND a.resource_id=:contentId group by `a`.`resource_id`"; private final String GET_SCOLLECTION_ITEM_IDS_BY_RESOURCE_ID = "SELECT collection_item_id FROM collection_item WHERE resource_content_id = :contentId"; @Override public List<Object[]> getCollectionSegments(Long contentId) { return createSQLQuery(GET_COLLECTION_SEGMENTS_SQL).setParameter(CONTENT_ID, contentId).list(); } @Override public List<Object[]> getResourceInstances(Set<String> segmentIds) { return createSQLQuery(GET_INSTANCE_BY_SEGMENT_SQL).setParameterList(SEGMENT_IDS, segmentIds).list(); } @Override public List<String> getQuizSegments(Long contentId) { return createSQLQuery(GET_SEGMENTS_BY_QUIZ_ID).setParameter(CONTENT_ID, contentId).list(); } @Override public String getCollectionTitle(String gooruOid) { List<String> list = createSQLQuery(GET_COLLECTION_TITLE).setParameter(GOORU_OID, gooruOid).list(); return list.size() > 0 ? (String) list.get(0) : null; } @Override public List<String> getQuestionQuiz(String gooruOid) { return (List<String>) createSQLQuery(GET_QUIZ_BY_QUESTION).setParameter(GOORU_OID, gooruOid).list(); } @Override public String getQuizTitle(String gooruOid) { List<String> result = createSQLQuery(GET_QUIZ_TITLE).setParameter(GOORU_OID, gooruOid).list(); return result.size() > 0 ? result.get(0) : null; } @Override public List<Object[]> getCollectionItems(Long contentId) { return createSQLQuery(GET_COLLECTION_ITEMS).setParameter(CONTENT_ID, contentId).list(); } @Override public List<Object[]> getResourceCollections(Long contentId) { return createSQLQuery(GET_COLLECTION_INFO_FOR_RESOURCE).setLong(CONTENT_ID, contentId).list(); } @Override public List<Object[]> getResourceSCollections(Long contentId) { return createSQLQuery(GET_SCOLLECTION_INFO_FOR_RESOURCE).setLong(CONTENT_ID, contentId).list(); } @Override public List<Integer> getCollectionTaxonomyIds(Long contentId) { return createSQLQuery(GET_COLLECTION_TAXONOMY_CODE_ID).setLong(CONTENT_ID, contentId).list(); } private Query createSQLQuery(String query) { return getSessionFactory().getCurrentSession().createSQLQuery(query); } @Override public List<Object[]> getResourceInstances(Long contentId) { return createSQLQuery(GET_RESOURCE_INSTANCES).setLong(CONTENT_ID, contentId).list(); } @Override public Object[] getResourceInfo(Long contentId) { List<Object[]> list = createSQLQuery(GET_RESOURCE_INFO).setLong(CONTENT_ID, contentId).list(); return list.size() > 0 ? (Object[]) list.get(0) : null; } @Override public Object[] getCollectionInfo(long contentId) { List<Object[]> list = createSQLQuery(GET_COLLECTION_INFO).setLong(CONTENT_ID, contentId).list(); return list.size() > 0 ? (Object[]) list.get(0) : null; } @Override public Object[] getSCollectionInfo(long contentId) { List<Object[]> list = createSQLQuery(GET_SCOLLECTION_INFO).setLong(CONTENT_ID, contentId).list(); return list.size() > 0 ? (Object[]) list.get(0) : null; } @Override public Map<String, Object> getQuizInfo(long contentId) { Query query = createSQLQuery(GET_QUIZ_INFO).setLong(CONTENT_ID, contentId); query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP); List results = query.list(); return (results.size() > 0 ? (Map<String, Object>) results.get(0) : null); } @Override public Object[] getStorageArea(String organizationUid, Boolean s3UploadFlag) { String sql = "SELECT sa.cdn_path,sa.area_path From organization o INNER JOIN storage_area sa ON "; if (!s3UploadFlag) { sql += " o.nfs_storage_area_id = sa.storage_area_id"; } else { sql += " o.s3_storage_area_id = sa.storage_area_id"; } sql += " WHERE o.organization_uid='" + organizationUid + "'"; List<Object[]> list = createSQLQuery(sql).list(); return list.size() > 0 ? (Object[]) list.get(0) : null; } @Override public Map<String, Object> getAnswerAndHint(long contentId) { Query query = createSQLQuery(GET_QUESTION_AND_HINT).setLong(CONTENT_ID, contentId); query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP); List results = query.list(); return (results.size() > 0 ? (Map<String, Object>) results.get(0) : null); } @Override public List<Object[]> getAssets(long contentId) { List<Object[]> list = createSQLQuery(GET_ASSETS).setLong(CONTENT_ID, contentId).list(); return list; } @Override public Object[] getRatingByContentId(long contentId) { List<Object[]> list = createSQLQuery(GET_RATING_BY_CONTENT_ID).setLong(CONTENT_ID, contentId).list(); return list.size() > 0 ? (Object[]) list.get(0) : null; } @Override public Long getSubscriptionCountByContentId(long contentId) { SQLQuery query = getSessionFactory().getCurrentSession().createSQLQuery(GET_SUBSCRIPTION_BY_CONTENT_ID); query.addScalar("subscriberCount", StandardBasicTypes.LONG); query.setLong(CONTENT_ID, contentId); List<Long> list = query.list(); return (list.size() > 0 ? list.get(0) : 0); } @Override public Resource findResourceByContentGooruId(String gooruOid) { List<Resource> resources = getSessionFactory().getCurrentSession() .createQuery("SELECT r FROM Resource r where r.gooruOid ='" + gooruOid + "'").list(); return resources.size() == 0 ? null : resources.get(0); } @Override public List<String> getCollectionItemIdsByResourceId(Long contentId) { return createSQLQuery(GET_SCOLLECTION_ITEM_IDS_BY_RESOURCE_ID).setLong(CONTENT_ID, contentId).list(); } }