List of usage examples for org.hibernate Session createNativeQuery
NativeQuery createNativeQuery(String sqlString);
From source file:de.tudarmstadt.ukp.wikipedia.api.Wikipedia.java
License:Apache License
/** * Tests, whether a page with the given pageID exists. * Trying to retrieve a pageID that does not exist in Wikipedia throws an exception. * * @param pageID A pageID./*from www.ja v a2 s.com*/ * @return True, if a page with that pageID exits, false otherwise. */ public boolean existsPage(int pageID) { // TODO carefully, this is a hack to provide a much quicker way to test whether a page exists. // Encoding the title in this way surpasses the normal way of creating a title first. // This should get a unit test to make sure the encoding function is in line with the title object. // Anyway, I do not like this hack :-| if (pageID < 0) { return false; } Session session = this.__getHibernateSession(); session.beginTransaction(); List returnList = session.createNativeQuery("select p.id from PageMapLine as p where p.pageID = :pageId") .setParameter("pageId", pageID, IntegerType.INSTANCE).list(); session.getTransaction().commit(); return returnList.size() != 0; }
From source file:info.rmapproject.auth.dao.ApiKeyDaoImpl.java
License:Apache License
@Override @SuppressWarnings("unchecked") public URI getAgentUriByKeySecret(String accessKey, String secret) throws RMapAuthException { URI agentUri = null;//from w w w . java 2 s . co m Session session = this.sessionFactory.getCurrentSession(); Query<String> query = session.createNativeQuery( "select distinct rmapAgentUri from ApiKeys " + "inner join Users on ApiKeys.userId = Users.userId " + "where accessKey=:accessKey and secret=:secret"); query.setParameter("accessKey", accessKey); query.setParameter("secret", secret); List<String> rmapAgentUris = query.list(); if (rmapAgentUris != null && !rmapAgentUris.isEmpty()) { LOG.info("User list loaded successfully"); String agentId = rmapAgentUris.get(0); try { agentUri = new URI(agentId); } catch (URISyntaxException ex) { throw new RMapAuthException(ex); } } return agentUri; }
From source file:info.rmapproject.auth.dao.UserDaoImpl.java
License:Apache License
@Override @SuppressWarnings("unchecked") public User getUserByProviderAccount(String idProvider, String providerAccountId) throws RMapAuthException { Session session = this.sessionFactory.getCurrentSession(); Query<User> query = session.createNativeQuery("select Users.* from Users " + "inner join UserIdentityProviders on UserIdentityProviders.userId = Users.userId " + "where identityProvider=:idProvider and providerAccountId=:providerAccountId"); query.setParameter("idProvider", idProvider); query.setParameter("providerAccountId", providerAccountId); List<User> users = query.list(); if (users != null && !users.isEmpty()) { LOG.info("User list loaded successfully"); return users.get(0); } else {/*from w w w . ja va 2s . c om*/ return null; } }
From source file:module.ImportPlatformFromFile.java
License:Open Source License
public ImportPlatformFromFile() { // === Display === System.out.println("\n================ BEGIN Module " + this.getClass().getName() + "================"); // === INPUT === String idPlatform = "GPL97"; String inputfile = this.getInputDirectory() + this.getDirSeparator() + "GPL97-17394.txt"; String gpl = idPlatform.toLowerCase().trim(); // ===== Session PostgreSQL ===== SessionFactory sessionFactory = HibernateUtil .buildSessionFactory("config/epimed_semantic.hibernate.cfg.xml"); Session session = sessionFactory.openSession(); // ===== DAO ===== OmGeneDao geneDao = new OmGeneDao(session); // ===== Session Mongo ===== MongoClient mongoClient = MongoUtil.buildMongoClient(); MongoDatabase db = mongoClient.getDatabase("epimed_experiments"); try {//from w w w . j a v a 2 s . c o m // === Begin transaction === session.beginTransaction(); // ===== Load file ===== System.out.println("ID Platform " + gpl); System.out.println("LOADING \t " + inputfile); System.out.println("Please wait... "); List<String> listRows = fileService.loadTextFile(inputfile); // List<String> listRows = webService.loadGeoData(idPlatform); System.out.println("File sucessfully LOADED"); // ===== Recognize header ===== List<String> header = fileService.readHeader(listRows, "\t"); if (header == null || header.isEmpty()) { throw new ImportDataException("The header is empty"); } else { System.out.println("Header " + header); } Integer indId = fileService.findIndex(header, "ID"); Integer indGbacc = fileService.findIndex(header, "GB_ACC"); Integer indEntrez = fileService.findIndex(header, "ENTREZ"); if (indId == null || indGbacc == null || indEntrez == null) { throw new ImportDataException("Header not recognized: " + "ID index=" + indId + ", GB_ACC index=" + indGbacc + ", ENTREZ index=" + indEntrez); } else { System.out.println("The following header items are recognized:"); System.out.println("\t ID index=" + indId + ": " + header.get(indId)); System.out.println("\t GB_ACC index=" + indGbacc + ": " + header.get(indGbacc)); System.out.println("\t ENTREZ index=" + indEntrez + ": " + header.get(indEntrez)); } // ===== Recognize data ===== List<List<String>> data = fileService.readData(listRows, "\t"); if (data == null || data.isEmpty()) { throw new ImportDataException("The data are empty"); } else { System.out.println( "The data are sucessfully loaded: rows " + data.size() + ", columns " + data.get(0).size()); } // ===== Create specific tables ===== String sqlCheckTableProbe = "select * from information_schema.tables WHERE table_schema = 'hs' and table_name='om_probe_" + gpl + "'"; List<Object> result = session.createNativeQuery(sqlCheckTableProbe).getResultList(); String tableProbe = "hs.om_probe_" + gpl; String tableGP = "hs.om_gp_" + gpl; if (result == null || result.isEmpty()) { // Table probe String sqlCreateTableProbe = "create table " + tableProbe + "(id_probe VARCHAR(50) not null," + " genbank_acc VARCHAR(50) null," + " constraint pk_om_probe_" + gpl + " primary key (id_probe))"; session.createNativeQuery(sqlCreateTableProbe).executeUpdate(); // Table gp String sqlCreateTableGP = "create table " + tableGP + "(id_probe VARCHAR(50) not null," + " id_gene INT4 not null," + " constraint pk_om_gp_" + gpl + " primary key (id_probe, id_gene))"; session.createNativeQuery(sqlCreateTableGP).executeUpdate(); // Foregn keys String sqlAlterTableProbe = "alter table " + tableGP + " add constraint fk_gp_probe_" + gpl + " foreign key (id_probe)" + " references " + tableProbe + " (id_probe) on delete restrict on update restrict"; session.createNativeQuery(sqlAlterTableProbe).executeUpdate(); String sqlAlterTableGene = "alter table " + tableGP + " add constraint fk_gp_gene_" + gpl + " foreign key (id_gene)" + " references hs.om_gene (id_gene) on delete restrict on update restrict"; session.createNativeQuery(sqlAlterTableGene).executeUpdate(); } // ===== Import data ===== for (int i = 0; i < data.size(); i++) { // for (int i=0; i<10; i++) { List<String> dataline = data.get(i); String idProbe = dataline.get(indId).trim(); String genbankAcc = dataline.get(indGbacc).trim(); String sqlInsertProbe = "insert into " + tableProbe + " values('" + idProbe + "', null)"; if (genbankAcc != null && !genbankAcc.isEmpty()) { sqlInsertProbe = "insert into " + tableProbe + " values('" + idProbe + "', '" + genbankAcc + "')"; } session.createNativeQuery(sqlInsertProbe).executeUpdate(); OmGenbankUnigene gu = session.get(OmGenbankUnigene.class, genbankAcc); if (gu == null && genbankAcc != null && !genbankAcc.isEmpty()) { gu = new OmGenbankUnigene(); gu.setGenbankAcc(genbankAcc); session.save(gu); } String listEntrez = null; String[] parts = null; if (indEntrez < dataline.size()) { listEntrez = dataline.get(indEntrez).trim(); parts = listEntrez.split("[///\\p{Space}]"); for (String entrezString : parts) { Integer entrez = null; try { entrez = Integer.parseInt(entrezString); } catch (NumberFormatException e) { // nothing to do } if (entrez != null) { OmGene gene = geneDao.find(entrez); if (gene == null) { gene = geneDao.createGene(entrez, null); } String sqlInsertGP = "insert into " + tableGP + " values('" + idProbe + "', " + entrez + ")"; session.createNativeQuery(sqlInsertGP).executeUpdate(); } } } if (i % 1000 == 0) { System.out.println(i + "\t" + idProbe + "\t" + genbankAcc + "\t" + listEntrez + "\t" + Arrays.toString(parts)); } if (i % 20 == 0) { session.flush(); } } // ===== Subscribe platform ===== OmPlatform platform = session.get(OmPlatform.class, idPlatform); if (platform != null) { platform.setEnabled(true); session.update(platform); } else { MongoCollection<Document> collection = db.getCollection("platforms"); Document docPlatform = collection.find(Filters.eq("_id", idPlatform)).first(); String title = docPlatform.getString("title"); String manufacturer = docPlatform.getString("manufacturer"); platform = new OmPlatform(); platform.setIdPlatform(idPlatform); platform.setTitle(title); platform.setManufacturer(manufacturer); platform.setEnabled(true); session.save(platform); } // ===== Rights ===== String sqlRights; String[] users = { "epimed_prod", "epimed_web", "epimed_script" }; for (String user : users) { sqlRights = "GRANT SELECT ON ALL TABLES IN SCHEMA hs TO " + user; session.createNativeQuery(sqlRights).executeUpdate(); } sqlRights = "GRANT ALL ON ALL TABLES IN SCHEMA hs TO epimed_admin"; session.createNativeQuery(sqlRights).executeUpdate(); // === Commit transaction === session.getTransaction().commit(); // session.getTransaction().rollback(); } catch (Exception e) { session.getTransaction().rollback(); System.out.println("ROLLBACK in module " + this.getClass().getName()); e.printStackTrace(); } finally { if (session.isOpen()) { session.close(); } sessionFactory.close(); mongoClient.close(); } // === Display === System.out.println("================ END Module " + this.getClass().getName() + "================"); }
From source file:org.kitodo.MockDatabase.java
License:Open Source License
/** * Clean database after class. Truncate all tables, reset id sequences and clear * session.//from w w w . jav a 2 s. co m */ public static void cleanDatabase() { Session session = HibernateUtil.getSession(); Transaction transaction = session.beginTransaction(); session.createNativeQuery("SET FOREIGN_KEY_CHECKS = 0").executeUpdate(); Set<String> tables = new HashSet<>(); String query = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='PUBLIC'"; List tableResult = session.createNativeQuery(query).getResultList(); for (Object table : tableResult) { tables.add((String) table); } for (String table : tables) { session.createNativeQuery("TRUNCATE TABLE " + table).executeUpdate(); } Set<String> sequences = new HashSet<>(); query = "SELECT SEQUENCE_NAME FROM INFORMATION_SCHEMA.SEQUENCES WHERE SEQUENCE_SCHEMA='PUBLIC'"; List sequencesResult = session.createNativeQuery(query).getResultList(); for (Object test : sequencesResult) { sequences.add((String) test); } for (String sequence : sequences) { session.createNativeQuery("ALTER SEQUENCE " + sequence + " RESTART WITH 1").executeUpdate(); } session.createNativeQuery("SET FOREIGN_KEY_CHECKS = 1").executeUpdate(); session.clear(); transaction.commit(); }
From source file:org.mycore.frontend.cli.MCRClassification2Commands.java
License:Open Source License
@MCRCommand(syntax = "repair category with empty labels", help = "fixes all categories with no labels (adds a label with categid as @text for default lang)", order = 110) public static void repairEmptyLabels() { Session session = MCRHIBConnection.instance().getSession(); String deleteEmptyLabels = "delete from {h-schema}MCRCategoryLabels where text is null or trim(text) = ''"; int affected = session.createNativeQuery(deleteEmptyLabels).executeUpdate(); LOGGER.info("Deleted " + affected + " labels."); String sqlQuery = "select cat.classid,cat.categid from {h-schema}MCRCategory cat left outer join {h-schema}MCRCategoryLabels label on cat.internalid = label.category where label.text is null"; @SuppressWarnings("unchecked") List<Object[]> list = session.createNativeQuery(sqlQuery).getResultList(); for (Object resultList : list) { Object[] arrayOfResults = (Object[]) resultList; String classIDString = (String) arrayOfResults[0]; String categIDString = (String) arrayOfResults[1]; MCRCategoryID mcrCategID = new MCRCategoryID(classIDString, categIDString); MCRLabel mcrCategLabel = new MCRLabel(MCRConstants.DEFAULT_LANG, categIDString, null); MCRCategoryDAOFactory.getInstance().setLabel(mcrCategID, mcrCategLabel); LOGGER.info("fixing category with class ID \"" + classIDString + "\" and category ID \"" + categIDString + "\""); }/*from www. j av a 2 s .com*/ LOGGER.info("Fixing category labels completed!"); }
From source file:org.mycore.frontend.cli.MCRClassification2Commands.java
License:Open Source License
@MCRCommand(syntax = "repair position in parent", help = "fixes all categories gaps in position in parent", order = 120) @SuppressWarnings("unchecked") public static void repairPositionInParent() { Session session = MCRHIBConnection.instance().getSession(); // this SQL-query find missing numbers in positioninparent String sqlQuery = "select parentid, min(cat1.positioninparent+1) from {h-schema}MCRCategory cat1 " + "where cat1.parentid is not null and not exists" + "(select 1 from {h-schema}MCRCategory cat2 " + "where cat2.parentid=cat1.parentid and cat2.positioninparent=(cat1.positioninparent+1))" + "and cat1.positioninparent not in " + "(select max(cat3.positioninparent) from {h-schema}MCRCategory cat3 " + "where cat3.parentid=cat1.parentid) group by cat1.parentid"; for (List<Object[]> parentWithErrorsList = session.createNativeQuery(sqlQuery) .getResultList(); !parentWithErrorsList .isEmpty(); parentWithErrorsList = session.createNativeQuery(sqlQuery).getResultList()) { for (Object[] parentWithErrors : parentWithErrorsList) { Number parentID = (Number) parentWithErrors[0]; Number firstErrorPositionInParent = (Number) parentWithErrors[1]; LOGGER.info("Category " + parentID + " has the missing position " + firstErrorPositionInParent + " ..."); repairCategoryWithGapInPos(parentID, firstErrorPositionInParent); LOGGER.info("Fixed position " + firstErrorPositionInParent + " for category " + parentID + "."); }// ww w .j a v a 2 s . co m } sqlQuery = "select parentid, min(cat1.positioninparent-1) from {h-schema}MCRCategory cat1 " + "where cat1.parentid is not null and not exists" + "(select 1 from {h-schema}MCRCategory cat2 " + "where cat2.parentid=cat1.parentid and cat2.positioninparent=(cat1.positioninparent-1))" + "and cat1.positioninparent not in " + "(select max(cat3.positioninparent) from {h-schema}MCRCategory cat3 " + "where cat3.parentid=cat1.parentid) and cat1.positioninparent > 0 group by cat1.parentid"; while (true) { List<Object[]> parentWithErrorsList = session.createNativeQuery(sqlQuery).getResultList(); if (parentWithErrorsList.isEmpty()) { break; } for (Object[] parentWithErrors : parentWithErrorsList) { Number parentID = (Number) parentWithErrors[0]; Number wrongStartPositionInParent = (Number) parentWithErrors[1]; LOGGER.info("Category " + parentID + " has the the starting position " + wrongStartPositionInParent + " ..."); repairCategoryWithWrongStartPos(parentID, wrongStartPositionInParent); LOGGER.info("Fixed position " + wrongStartPositionInParent + " for category " + parentID + "."); } } LOGGER.info("Repair position in parent finished!"); }
From source file:org.mycore.frontend.cli.MCRClassification2Commands.java
License:Open Source License
public static void repairCategoryWithWrongStartPos(Number parentID, Number wrongStartPositionInParent) { Session session = MCRHIBConnection.instance().getSession(); String sqlQuery = "update {h-schema}MCRCategory set positioninparent= positioninparent -" + wrongStartPositionInParent + "-1 where parentid=" + parentID + " and positioninparent > " + wrongStartPositionInParent; session.createNativeQuery(sqlQuery).executeUpdate(); }
From source file:org.mycore.frontend.cli.MCRClassification2Commands.java
License:Open Source License
private static void repairCategoryWithGapInPos(Number parentID, Number firstErrorPositionInParent) { Session session = MCRHIBConnection.instance().getSession(); // the query decrease the position in parent with a rate. // eg. posInParent: 0 1 2 5 6 7 // at 3 the position get faulty, 5 is the min. of the position greather // 3/* w ww . jav a2 s . c om*/ // so the reate is 5-3 = 2 String sqlQuery = "update {h-schema}MCRCategory set positioninparent=(positioninparent - (select min(positioninparent) from {h-schema}MCRCategory where parentid=" + parentID + " and positioninparent > " + firstErrorPositionInParent + ")+" + firstErrorPositionInParent + ") where parentid=" + parentID + " and positioninparent > " + firstErrorPositionInParent; session.createNativeQuery(sqlQuery).executeUpdate(); }
From source file:org.mycore.frontend.cli.MCRClassification2Commands.java
License:Open Source License
private static void checkEmptyLabels(String classID, List<String> log) { Session session = MCRHIBConnection.instance().getSession(); String sqlQuery = "select cat.categid from {h-schema}MCRCategory cat left outer join {h-schema}MCRCategoryLabels label on cat.internalid = label.category where cat.classid='" + classID + "' and (label.text is null or trim(label.text) = '')"; @SuppressWarnings("unchecked") List<String> list = session.createNativeQuery(sqlQuery).getResultList(); for (String categIDString : list) { log.add("EMPTY lables for category " + new MCRCategoryID(classID, categIDString)); }/*from www.j ava 2s . c o m*/ }