List of usage examples for org.hibernate.type StandardBasicTypes STRING
StringType STRING
To view the source code for org.hibernate.type StandardBasicTypes STRING.
Click Source Link
From source file:ke.co.mspace.nonsmppmanager.service.SMSOutServiceImpl.java
@Override public Map<String, String> smsOutGroupByUser(String startDate, String endDate) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.getTransaction().begin();// w w w. j a v a 2 s . c om Criteria criteria = session.createCriteria(SMSOut.class); Calendar startAnotherDate = null; Calendar endAnotherDate = null; try { startAnotherDate = stringToCalendar(startDate); endAnotherDate = stringToCalendar(endDate); } catch (ParseException ex) { Logger.getLogger(SMSOutServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } /* * This is where the report is going to come from * projectionList.add(Projections.sqlGroupProjection("YEAR(time_submitted) as yearSubmitted, MONTHNAME(STR_TO_DATE(MONTH(time_submitted), '%m')) as monthSubmitted, time_submitted as timeSubmitted", "timeSubmitted", new String[] { "monthSubmitted", "timeSubmitted", "yearSubmitted" }, new Type[] { StandardBasicTypes.STRING })); */ ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.sqlGroupProjection( "YEAR(time_submitted) as yearSubmitted, MONTHNAME(STR_TO_DATE(MONTH(time_submitted), '%m')) as timeSubmitted, user as userName", "yearSubmitted, timeSubmitted, userName", new String[] { "yearSubmitted", "timeSubmitted", "userName" }, new Type[] { StandardBasicTypes.LONG, StandardBasicTypes.STRING, StandardBasicTypes.STRING })); projectionList.add(Projections.rowCount()); criteria.setProjection(projectionList); criteria.addOrder(Order.asc("timeSubmitted")); criteria.add(Restrictions.between("timeSubmitted", startDate, endDate)); List<Object[]> results = criteria.list(); Map<String, Integer> json = new LinkedHashMap<>(); Set<String> months = new LinkedHashSet<>(); Set<String> users = new LinkedHashSet<>(); while (startAnotherDate.before(endAnotherDate)) { String month = startAnotherDate.getDisplayName(Calendar.MONTH, Calendar.LONG, Locale.getDefault()); int year = startAnotherDate.get(Calendar.YEAR); for (Object[] aResult : results) { json.put(year + "-" + month + "-" + aResult[2], 0); months.add(aResult[0] + "-" + aResult[1]); users.add(String.valueOf(aResult[2])); } startAnotherDate.add(Calendar.MONTH, 1); } String madeUp = null; for (String aString : json.keySet()) { for (Object[] aResult : results) { madeUp = aResult[0] + "-" + aResult[1] + "-" + aResult[2]; if (aString.equals(madeUp)) { json.put(aString, Integer.parseInt(String.valueOf(aResult[3]))); } } } StringBuilder builder = new StringBuilder(); for (String aMonth : months) { builder.append("["); builder.append('"'); builder.append(aMonth.substring(0, 8)); builder.append('"'); builder.append(','); for (String aString : json.keySet()) { if (aString.contains(aMonth)) { builder.append(json.get(aString)); builder.append(","); } } builder.append("]"); if (builder.length() > 0) { if (builder.charAt(builder.lastIndexOf("]") - 1) == ',') { builder.deleteCharAt(builder.lastIndexOf("]") - 1); } } builder.append(","); } if (builder.length() > 0) { builder.deleteCharAt(builder.lastIndexOf(",")); } StringBuilder userBuilder = new StringBuilder(); userBuilder.append("["); for (String aUser : users) { userBuilder.append('"'); userBuilder.append(aUser); userBuilder.append('"'); userBuilder.append(','); } userBuilder.append(']'); if (userBuilder.length() > 0) { if (userBuilder.charAt(userBuilder.lastIndexOf("]") - 1) == ',') { userBuilder.deleteCharAt(userBuilder.lastIndexOf("]") - 1); } } System.out.println("A new builder : " + builder.toString()); System.out.println("The Users : " + userBuilder.toString()); Map<String, String> mapResult = new HashMap<>(); mapResult.put("data", builder.toString()); mapResult.put("users", userBuilder.toString()); session.getTransaction().commit(); return mapResult; }
From source file:nl.lawinegevaar.jaytalk2014.jpajooq.AlternativeFirebirdDialect.java
License:Open Source License
public AlternativeFirebirdDialect() { registerFunction("substring", new SQLFunctionTemplate(StandardBasicTypes.STRING, "substring(?1 from ?2 for ?3)")); }
From source file:nl.rivm.cib.epidemes.geodb.jdbc.PostgisDialectExtended.java
License:Apache License
public PostgisDialectExtended() { super();/*ww w.j a v a2 s . co m*/ registerFunction("Transform", new StandardSQLFunction("ST_Transform", StandardBasicTypes.STRING)); registerFunction("AsGeoJson", new StandardSQLFunction("ST_AsGeoJson", StandardBasicTypes.STRING)); registerFunction("AsGml", new StandardSQLFunction("ST_AsGml", StandardBasicTypes.STRING)); registerFunction("AsKml", new StandardSQLFunction("ST_AsKml", StandardBasicTypes.STRING)); registerFunction("Srid", new StandardSQLFunction("ST_Srid", StandardBasicTypes.INTEGER)); }
From source file:nl.strohalm.cyclos.dao.accounts.AccountDAOImpl.java
License:Open Source License
public IteratorList<AccountDailyDifference> iterateDailyDifferences(final MemberAccount account, final Period period) { Map<String, Object> params = new HashMap<String, Object>(); params.put("accountId", account.getId()); QueryParameter beginParameter = HibernateHelper.getBeginParameter(period); QueryParameter endParameter = HibernateHelper.getEndParameter(period); if (beginParameter != null) { params.put("begin", beginParameter.getValue()); }/* w ww .j a va 2 s .c om*/ if (endParameter != null) { params.put("end", endParameter.getValue()); } StringBuilder sql = new StringBuilder(); sql.append(" select type, date(d.date) as date, sum(amount) as amount "); sql.append(" from ( "); sql.append(" select 'B' as type, t.process_date as date, "); sql.append(" case when t.chargeback_of_id is null then "); sql.append(" case when t.from_account_id = :accountId then -t.amount else t.amount end "); sql.append(" else "); sql.append(" case when t.to_account_id = :accountId then t.amount else -t.amount end "); sql.append(" end as amount "); sql.append(" from transfers t "); sql.append(" where (t.from_account_id = :accountId or t.to_account_id = :accountId) "); sql.append(" and t.process_date is not null "); if (beginParameter != null) { sql.append(" and t.process_date " + beginParameter.getOperator() + " :begin"); } if (endParameter != null) { sql.append(" and t.process_date " + endParameter.getOperator() + " :end"); } sql.append(" union "); sql.append(" select 'R', r.date, r.amount "); sql.append(" from amount_reservations r "); sql.append(" where r.account_id = :accountId "); if (beginParameter != null) { sql.append(" and r.date " + beginParameter.getOperator() + " :begin"); } if (endParameter != null) { sql.append(" and r.date " + endParameter.getOperator() + " :end"); } sql.append(" ) d "); sql.append(" group by type, date(d.date) "); sql.append(" order by date(d.date) "); SQLQuery query = getSession().createSQLQuery(sql.toString()); query.addScalar("type", StandardBasicTypes.STRING); query.addScalar("date", StandardBasicTypes.CALENDAR_DATE); query.addScalar("amount", StandardBasicTypes.BIG_DECIMAL); getHibernateQueryHandler().setQueryParameters(query, params); ScrollableResults results = query.scroll(ScrollMode.SCROLL_INSENSITIVE); return new IteratorListImpl<AccountDailyDifference>(new DiffsIterator(results)); }
From source file:nl.strohalm.cyclos.dao.accounts.AccountDAOImpl.java
License:Open Source License
public Iterator<MemberTransactionDetailsReportData> membersTransactionsDetailsReport( final MembersTransactionsReportParameters params) { final StringBuilder sql = new StringBuilder(); final Map<String, Object> parameters = new HashMap<String, Object>(); // Find the transfer types ids Set<Long> ttIds = null; if (CollectionUtils.isNotEmpty(params.getPaymentFilters())) { ttIds = new HashSet<Long>(); for (PaymentFilter pf : params.getPaymentFilters()) { pf = getFetchDao().fetch(pf, PaymentFilter.Relationships.TRANSFER_TYPES); final Long[] ids = EntityHelper.toIds(pf.getTransferTypes()); CollectionUtils.addAll(ttIds, ids); }//from ww w . jav a2 s .c om } // Get the member group ids Set<Long> groupIds = null; if (CollectionUtils.isNotEmpty(params.getMemberGroups())) { groupIds = new HashSet<Long>(); CollectionUtils.addAll(groupIds, EntityHelper.toIds(params.getMemberGroups())); } // Get the period final Period period = params.getPeriod(); final QueryParameter beginParameter = HibernateHelper.getBeginParameter(period); final QueryParameter endParameter = HibernateHelper.getEndParameter(period); // Set the parameters final boolean useTT = CollectionUtils.isNotEmpty(ttIds); if (useTT) { parameters.put("ttIds", ttIds); } if (beginParameter != null) { parameters.put("beginDate", beginParameter.getValue()); } if (endParameter != null) { parameters.put("endDate", endParameter.getValue()); } parameters.put("processed", Payment.Status.PROCESSED.getValue()); // Build the sql string sql.append( " select u.username, m.name, bu.username broker_username, b.name broker_name, h.account_type_name, h.date, h.amount, h.description, h.related_username, h.related_name, h.transfer_type_name, h.transaction_number"); sql.append( " from members m inner join users u on m.id = u.id left join members b on m.member_broker_id = b.id left join users bu on b.id = bu.id,"); sql.append(" ("); if (params.isCredits()) { appendMembersTransactionsDetailsReportSqlPart(sql, useTT, beginParameter, endParameter, true, true); sql.append(" union"); appendMembersTransactionsDetailsReportSqlPart(sql, useTT, beginParameter, endParameter, true, false); if (params.isDebits()) { sql.append(" union"); } } if (params.isDebits()) { appendMembersTransactionsDetailsReportSqlPart(sql, useTT, beginParameter, endParameter, false, true); sql.append(" union"); appendMembersTransactionsDetailsReportSqlPart(sql, useTT, beginParameter, endParameter, false, false); } sql.append(" ) h"); sql.append(" where m.id = h.member_id"); if (groupIds != null) { parameters.put("groupIds", groupIds); sql.append(" and m.group_id in (:groupIds)"); } sql.append(" order by m.name, u.username, h.account_type_name, h.date desc, h.transfer_id desc"); // Prepare the query final SQLQuery query = getSession().createSQLQuery(sql.toString()); final Map<String, Type> columns = new LinkedHashMap<String, Type>(); columns.put("username", StandardBasicTypes.STRING); columns.put("name", StandardBasicTypes.STRING); columns.put("broker_username", StandardBasicTypes.STRING); columns.put("broker_name", StandardBasicTypes.STRING); columns.put("account_type_name", StandardBasicTypes.STRING); columns.put("date", StandardBasicTypes.CALENDAR); columns.put("amount", StandardBasicTypes.BIG_DECIMAL); columns.put("description", StandardBasicTypes.STRING); columns.put("related_username", StandardBasicTypes.STRING); columns.put("related_name", StandardBasicTypes.STRING); columns.put("transfer_type_name", StandardBasicTypes.STRING); columns.put("transaction_number", StandardBasicTypes.STRING); for (final Map.Entry<String, Type> entry : columns.entrySet()) { query.addScalar(entry.getKey(), entry.getValue()); } getHibernateQueryHandler().setQueryParameters(query, parameters); // Create a transformer, which will read rows as Object[] and transform // them to MemberTransactionDetailsReportData final Transformer<Object[], MemberTransactionDetailsReportData> transformer = new Transformer<Object[], MemberTransactionDetailsReportData>() { public MemberTransactionDetailsReportData transform(final Object[] input) { final MemberTransactionDetailsReportData data = new MemberTransactionDetailsReportData(); int i = 0; for (final Map.Entry<String, Type> entry : columns.entrySet()) { final String columnName = entry.getKey(); // Column names are transfer_type_name, property is // transferTypeName String propertyName = WordUtils.capitalize(columnName, COLUMN_DELIMITERS); propertyName = Character.toLowerCase(propertyName.charAt(0)) + propertyName.substring(1); propertyName = StringUtils.replace(propertyName, "_", ""); PropertyHelper.set(data, propertyName, input[i]); i++; } return data; } }; return new ScrollableResultsIterator<MemberTransactionDetailsReportData>(query, transformer); }
From source file:nl.strohalm.cyclos.dao.members.ReferenceDAOImpl.java
License:Open Source License
public List<PaymentAwaitingFeedbackDTO> searchPaymentsAwaitingFeedback( final PaymentsAwaitingFeedbackQuery query) { final ResultType resultType = query.getResultType(); final PageParameters pageParameters = query.getPageParameters(); final boolean countOnly = resultType == ResultType.PAGE && pageParameters != null && pageParameters.getMaxResults() == 0; // There are 2 tables which contains payments that can have feedback: transfers and scheduled payments // As we need an union, we need a native SQL final Member member = query.getMember(); Boolean expired = query.getExpired(); final StringBuilder sql = new StringBuilder(); sql.append(" select "); if (countOnly) { sql.append(" count(*) as row_count"); } else {/*from ww w.j a v a2s .c o m*/ sql.append(" * "); } sql.append(" from ( "); { sql.append( " select t.id, t.type_id as transferTypeId, false as scheduled, t.date, t.amount, tm.id as memberId, tm.name as memberName, ta.owner_name as memberUsername"); sql.append( " from transfers t inner join transfer_types tt on t.type_id = tt.id inner join accounts ta on t.to_account_id = ta.id inner join members tm on ta.member_id = tm.id"); if (member != null) { sql.append(" inner join accounts a on t.from_account_id = a.id"); } sql.append(" left join refs tf on tf.transfer_id = t.id"); sql.append(" where tt.requires_feedback = true"); sql.append(" and t.date >= tt.feedback_enabled_since"); sql.append(" and t.parent_id is null"); sql.append(" and t.chargeback_of_id is null"); sql.append(" and t.scheduled_payment_id is null"); sql.append(" and t.process_date is not null"); if (expired != null) { sql.append(" and t.feedback_deadline " + (expired ? "<" : ">=") + " now()"); } sql.append(" and tf.id is null"); if (member != null) { sql.append(" and a.member_id = :memberId"); } sql.append(" union "); sql.append(" select sp.id, sp.type_id, true, sp.date, sp.amount, tm.id, tm.name, ta.owner_name"); sql.append( " from scheduled_payments sp inner join transfer_types tt on sp.type_id = tt.id inner join accounts ta on sp.to_account_id = ta.id inner join members tm on ta.member_id = tm.id"); if (member != null) { sql.append(" inner join accounts a on sp.from_account_id = a.id"); } sql.append(" left join refs tf on tf.scheduled_payment_id = sp.id"); sql.append(" where tt.requires_feedback = true"); if (expired != null) { sql.append(" and sp.feedback_deadline " + (expired ? "<" : ">=") + " now()"); } sql.append(" and sp.date >= tt.feedback_enabled_since"); sql.append(" and tf.id is null"); if (member != null) { sql.append(" and a.member_id = :memberId"); } } sql.append(") as awaiting "); if (!countOnly) { sql.append("order by date"); } SQLQuery sqlQuery = getSession().createSQLQuery(sql.toString()); if (member != null) { sqlQuery.setLong("memberId", member.getId()); } if (countOnly) { // Handle the special case for count only sqlQuery.addScalar("row_count", StandardBasicTypes.INTEGER); int count = ((Number) sqlQuery.uniqueResult()).intValue(); return new PageImpl<PaymentAwaitingFeedbackDTO>(pageParameters, count, Collections.<PaymentAwaitingFeedbackDTO>emptyList()); } else { // Execute the search sqlQuery.addScalar("id", StandardBasicTypes.LONG); sqlQuery.addScalar("transferTypeId", StandardBasicTypes.LONG); sqlQuery.addScalar("scheduled", StandardBasicTypes.BOOLEAN); sqlQuery.addScalar("date", StandardBasicTypes.CALENDAR); sqlQuery.addScalar("amount", StandardBasicTypes.BIG_DECIMAL); sqlQuery.addScalar("memberId", StandardBasicTypes.LONG); sqlQuery.addScalar("memberName", StandardBasicTypes.STRING); sqlQuery.addScalar("memberUsername", StandardBasicTypes.STRING); getHibernateQueryHandler().applyPageParameters(pageParameters, sqlQuery); // We'll always use an iterator, even if it is for later adding it to a list Iterator<PaymentAwaitingFeedbackDTO> iterator = new ScrollableResultsIterator<PaymentAwaitingFeedbackDTO>( sqlQuery, new Transformer<Object[], PaymentAwaitingFeedbackDTO>() { public PaymentAwaitingFeedbackDTO transform(final Object[] input) { PaymentAwaitingFeedbackDTO dto = new PaymentAwaitingFeedbackDTO(); dto.setId((Long) input[0]); dto.setTransferTypeId((Long) input[1]); dto.setScheduled(Boolean.TRUE.equals(input[2])); dto.setDate((Calendar) input[3]); dto.setAmount((BigDecimal) input[4]); dto.setMemberId((Long) input[5]); dto.setMemberName((String) input[6]); dto.setMemberUsername((String) input[7]); TransferType transferType = (TransferType) getSession().load(TransferType.class, dto.getTransferTypeId()); dto.setCurrency(getFetchDao().fetch(transferType.getCurrency())); return dto; } }); if (resultType == ResultType.ITERATOR) { return new IteratorListImpl<PaymentAwaitingFeedbackDTO>(iterator); } else { List<PaymentAwaitingFeedbackDTO> list = new ArrayList<PaymentAwaitingFeedbackDTO>(); CollectionUtils.addAll(list, iterator); DataIteratorHelper.close(iterator); if (resultType == ResultType.PAGE) { // For page, we need another search for the total count query.setPageForCount(); int totalCount = PageHelper.getTotalCount(searchPaymentsAwaitingFeedback(query)); return new PageImpl<PaymentAwaitingFeedbackDTO>(pageParameters, totalCount, list); } else { return list; } } } }
From source file:org.ambraproject.models.AnnotationTest.java
License:Apache License
@Test @SuppressWarnings("unchecked") public void testLoadTypeFromStringRepresentation() { final Long userId = (Long) hibernateTemplate .save(new UserProfile("email@LoadType.org", "displayNameForLoadType", "pass")); final Long articleId = (Long) hibernateTemplate.save(new Article("id:doi-for-LoadType")); hibernateTemplate.execute(new HibernateCallback() { @Override/* ww w . j a v a 2 s .c om*/ public Object doInHibernate(Session session) throws HibernateException, SQLException { session.createSQLQuery( "insert into annotation (created, lastModified, userProfileID, articleID, type, annotationURI) " + "values (?,?,?,?,?,?)") .setParameter(0, Calendar.getInstance().getTime(), StandardBasicTypes.DATE) .setParameter(1, Calendar.getInstance().getTime(), StandardBasicTypes.DATE) .setParameter(2, userId, StandardBasicTypes.LONG) .setParameter(3, articleId, StandardBasicTypes.LONG) .setParameter(4, "Comment", StandardBasicTypes.STRING) .setParameter(5, "unique-annotation-uri-for-loadTypeFromString", StandardBasicTypes.STRING) .executeUpdate(); return null; } }); List<Annotation> results = (List<Annotation>) hibernateTemplate .find("from Annotation where annotationUri = ?", "unique-annotation-uri-for-loadTypeFromString"); assertEquals(results.size(), 1, "didn't store annotation correctly"); assertEquals(results.get(0).getType(), AnnotationType.COMMENT, "Type wasn't loaded correctly"); }
From source file:org.candlepin.hibernate.EmptyStringUserType.java
License:Open Source License
@Override public Object nullSafeGet(ResultSet rs, String[] names, SessionImplementor si, Object owner) throws HibernateException, SQLException { String value = (String) StandardBasicTypes.STRING.nullSafeGet(rs, names[0], si); return (value == null) ? "" : value; }
From source file:org.candlepin.hibernate.EmptyStringUserType.java
License:Open Source License
@Override public void nullSafeSet(PreparedStatement st, Object value, int index, SessionImplementor si) throws HibernateException, SQLException { StandardBasicTypes.STRING.nullSafeSet(st, value, index, si); }
From source file:org.codehaus.groovy.grails.orm.hibernate.cfg.AbstractGrailsDomainBinder.java
License:Apache License
protected void bindMapSecondPass(GrailsDomainClassProperty property, Mappings mappings, Map<?, ?> persistentClasses, org.hibernate.mapping.Map map, String sessionFactoryBeanName) { bindCollectionSecondPass(property, mappings, persistentClasses, map, sessionFactoryBeanName); SimpleValue value = new SimpleValue(mappings, map.getCollectionTable()); bindSimpleValue(getIndexColumnType(property, STRING_TYPE), value, true, getIndexColumnName(property, sessionFactoryBeanName), mappings); PropertyConfig pc = getPropertyConfig(property); if (pc != null && pc.getIndexColumn() != null) { bindColumnConfigToColumn(getColumnForSimpleValue(value), getSingleColumnConfig(pc.getIndexColumn())); }// www . j av a2 s. c o m if (!value.isTypeSpecified()) { throw new MappingException("map index element must specify a type: " + map.getRole()); } map.setIndex(value); if (!property.isOneToMany() && !property.isManyToMany()) { SimpleValue elt = new SimpleValue(mappings, map.getCollectionTable()); map.setElement(elt); String typeName = getTypeName(property, getPropertyConfig(property), getMapping(property.getDomainClass())); if (typeName == null) { if (property.isBasicCollectionType()) { typeName = property.getReferencedPropertyType().getName(); } else { typeName = StandardBasicTypes.STRING.getName(); } } bindSimpleValue(typeName, elt, false, getMapElementName(property, sessionFactoryBeanName), mappings); elt.setTypeName(typeName); map.setInverse(false); } else { map.setInverse(false); } }