// 默认顺序createtime desc
public List<T> findByMapWithCond(Map<String, Object> params, String orderBy, Boolean asc) {
StringBuilder hql = new StringBuilder("from " + className);
String orderStr;
if (null == orderBy) {
orderStr = " order by createtime desc";
} else {
String ascStr = asc ? " asc" : " desc";
orderStr = " order by " + orderBy + ascStr;
}
Map<String, Object> alterParams = new HashMap<>();
Query query;
if (null != params && !params.isEmpty()) {
hql.append(" where ");
for (String field : params.keySet()) {
int i = field.indexOf(" ");
Assert.isTrue(i != -1, "Wrong condition, must have space inside!");
String ramdonName = "_" + Utils.getRandomString(8);
hql.append(field).append(" :").append(ramdonName).append(" and ");
if (field.contains("like")) {
String likeStr = "%" + params.get(field) + "%";
alterParams.put(ramdonName, likeStr);
} else alterParams.put(ramdonName, params.get(field));
}
hql.append("1=1");
query = getSession().createQuery(hql.append(orderStr).toString());
for (String field : alterParams.keySet()) query.setParameter(field, alterParams.get(field));
} else query = getSession().createQuery(hql.append(orderStr).toString());
return query.list();
}
private void restoreMembers(Data data) {
Query q = newQuery(Data.class);
ObjectSet objectSet = q.execute();
Data rdata = (Data) objectSet.next();
data.i_map = rdata.i_map;
data.i_helper = rdata.i_helper;
}
private int getHttpsPort() {
try {
MBeanServer mBeanServer = MBeanServerFactory.findMBeanServer(null).get(0);
QueryExp query = Query.eq(Query.attr("Scheme"), Query.value("https"));
Set<ObjectName> objectNames = mBeanServer.queryNames(null, query);
if (objectNames != null && objectNames.size() > 0) {
for (ObjectName objectName : objectNames) {
String name = objectName.toString();
if (name.indexOf("port=") > -1) {
String[] parts = name.split("port=");
String port = parts[1];
try {
int portNum = Integer.parseInt(port);
return portNum;
} catch (NumberFormatException e) {
logger.error("Error parsing https port:" + port);
return -1;
}
}
}
}
} catch (Throwable t) {
logger.error("Error getting https port:", t);
}
return -1;
}
public int getCountOfSubscribers(int forumId, int userId) {
// TODO Auto-generated method stub
Session session = this.sessionFactory.openSession();
Transaction tx = null;
int countOfSubscribers = 0;
try {
tx = session.beginTransaction();
Query query = null;
if (userId == 0) {
query = session.createQuery("select count(*) from Subscription where forumId = :forumId");
query.setParameter("forumId", forumId);
} else {
query =
session.createQuery(
"select count(*) from Subscription where forumId = :forumId and userId = :userId");
query.setParameter("forumId", forumId);
query.setParameter("userId", userId);
}
Long count = (Long) query.uniqueResult();
countOfSubscribers = count.intValue();
// System.out.println("No of subscribers.."+countOfSubscribers);
} catch (HibernateException e) {
if (tx != null) {
tx.rollback();
e.printStackTrace();
}
} finally {
session.close();
}
return countOfSubscribers;
}
@Override
public List<Reply> getRepliesToPost(int postId) {
// TODO Auto-generated method stub
Session session = sessionFactory.openSession();
Transaction tx = null;
List<Reply> replyArr = new ArrayList<Reply>();
try {
tx = session.beginTransaction();
Query query =
session.createQuery("from Reply where postId = :postId order by createdDate desc");
query.setParameter("postId", postId);
replyArr = query.list();
/* for(Reply reply : replyArr){
System.out.println("Reply - id----"+reply.getReplyId());
System.out.println("Reply - Description----"+reply.getDescription());
System.out.println("Reply - Post id----"+reply.getPostId());
}*/
} catch (HibernateException e) {
if (tx != null) {
tx.rollback();
e.printStackTrace();
}
} finally {
session.close();
}
return replyArr;
}
// FIXME
public void _testDescendOne() {
Query q = newQuery();
q.constrain(STVectorEU.class);
q.descend("col").constrain(new Integer(17));
expect(q, new int[] {0});
}
@Test
public void testCaseInsensitive() {
sql2o
.createQuery(
"create table testCI(id2 int primary key, value2 varchar(20), sometext varchar(20), valwithgetter varchar(20))")
.executeUpdate();
Query query =
sql2o.createQuery(
"insert into testCI(id2, value2, sometext, valwithgetter) values(:id, :value, :someText, :valwithgetter)");
for (int i = 0; i < 20; i++) {
query
.addParameter("id", i)
.addParameter("value", "some text " + i)
.addParameter("someText", "whatever " + i)
.addParameter("valwithgetter", "spaz" + i)
.addToBatch();
}
query.executeBatch();
List<CIEntity> ciEntities =
sql2o
.createQuery("select * from testCI")
.setCaseSensitive(false)
.executeAndFetch(CIEntity.class);
assertTrue(ciEntities.size() == 20);
// test defaultCaseSensitive;
sql2o.setDefaultCaseSensitive(false);
List<CIEntity> ciEntities2 =
sql2o.createQuery("select * from testCI").executeAndFetch(CIEntity.class);
assertTrue(ciEntities2.size() == 20);
}
public void testDescendToObject() {
Query q = st.query();
Object[] r = store();
q.constrain(new STHashtableT());
q.descend("col").descend("foo1").constrain("bar");
st.expect(q, new Object[] {r[5], r[6]});
}
public static void main(String[] args) {
Session session = null;
try {
SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
session = sessionFactory.openSession();
Contact contact;
String SQL_QUERY = "select c.id, c.firstName from Contact c where c.id >= 3";
Query query = session.createQuery(SQL_QUERY);
Iterator it = query.iterate();
while (it.hasNext()) {
Object[] row = (Object[]) it.next();
System.out.println("------------------------------------------------------");
System.out.println("ID : " + row[0]);
System.out.println("First Name : " + row[1]);
}
System.out.println("Done");
} catch (Exception e) {
// tx.rollback();
System.out.println(e.getMessage());
} finally {
// session.flush();
session.close();
}
}
void printFailedResultSetTest(
Query query,
QueryExecution qe,
ResultSetRewindable qrExpected,
ResultSetRewindable qrActual) {
PrintStream out = System.out;
out.println();
out.println("=======================================");
out.println("Failure: " + description());
out.println("Query: \n" + query);
// if ( qe != null && qe.getDataset() != null )
// {
// out.println("Data: \n"+qe.getDataset().asDatasetGraph()) ;
// }
out.println("Got: " + qrActual.size() + " --------------------------------");
qrActual.reset();
ResultSetFormatter.out(out, qrActual, query.getPrefixMapping());
qrActual.reset();
out.flush();
out.println("Expected: " + qrExpected.size() + " -----------------------------");
qrExpected.reset();
ResultSetFormatter.out(out, qrExpected, query.getPrefixMapping());
qrExpected.reset();
out.println();
out.flush();
}
/** Read all address. */
public void test() throws Exception {
EntityManager manager = createEntityManager();
Query query = manager.createQuery("Select a from Address a where a.city = :city");
query.setParameter("city", "Ottawa");
List result = list(query, manager);
manager.close();
}
private Item storedItem(String id) {
Query query = newQuery(Item.class);
query.descend("_id").constrain(id);
ObjectSet<Item> result = query.execute();
Assert.isTrue(result.hasNext());
return result.next();
}
public void testDescendOne() {
Query q = newQuery();
q.constrain(STHashSetUTestCase.class);
q.descend("col").constrain(new Integer(17));
expect(q, new int[] {3, 4});
}
public void testDescendToObject() {
Query q = newQuery();
q.constrain(new STHashSetUTestCase());
q.descend("col").descend("foo1").constrain("bar");
expect(q, new int[] {5, 6});
}
private Collection<ConversationSummary> fetchCorrespondents(final User user) {
final Map<String, ConversationSummary> correspondants =
new HashMap<String, ConversationSummary>();
{
final Query query =
query(
"select m.sender.name, m.receiver.name, count(m), m.read from PrivateMessageImpl m where (m.receiver=:user OR "
+ "(m.sender=:user)) AND(m.deleter IS NULL OR m.deleter <> :user) "
+ "group by m.sender.name, m.receiver.name, m.read");
query.setParameter("user", user);
for (final Object[] row : (List<Object[]>) query.getResultList()) {
final boolean sent = row[0].equals(((UserImpl) user).getBareName());
final String name = (String) (sent ? row[1] : row[0]);
final ConversationSummary previous = correspondants.get(name);
final long count = ((Number) row[2]).longValue();
final long newCount;
if (row[3].equals(Boolean.FALSE) && !sent) newCount = count;
else newCount = 0;
if (previous != null) {
correspondants.put(
name,
new ConversationSummary(
UserStringImpl.valueOf(name),
count + previous.messageCount,
newCount + previous.newMessageCount));
} else
correspondants.put(
name, new ConversationSummary(UserStringImpl.valueOf(name), count, newCount));
}
}
return new TreeSet<ConversationSummary>(correspondants.values());
}
public List<TbAplicativo> datbAplicativos() {
sesion = sessionFactory.openSession();
Query query = sesion.getNamedQuery("TbAplicativo.findAll");
List<TbAplicativo> tbAplicativo = query.list();
sesion.close();
return tbAplicativo;
}
private void separatePrivateMessagesFromWorkout(final Long id) {
final Query query =
entityManager.createNativeQuery(
"UPDATE MESSAGES SET WORKOUT_ID=NULL WHERE WORKOUT_ID=:id AND RECEIVER_ID IS NOT NULL");
query.setParameter("id", id);
query.executeUpdate();
}
public void testDescendOne() {
Query q = st.query();
Object[] r = store();
q.constrain(STOwnCollectionW.class);
q.descend("col").constrain(new Integer(17));
st.expect(q, new Object[] {r[3], r[4]});
}
/** ************ Helper stuff ***************** */
private void createAndFillUserTable() {
int rowCount = 10000;
sql2o
.createQuery(
"create table User(\n"
+ "id int identity primary key,\n"
+ "name varchar(20),\n"
+ "email varchar(255),\n"
+ "text varchar(100))")
.executeUpdate();
Query insQuery =
sql2o.createQuery("insert into User(name, email, text) values (:name, :email, :text)");
Date before = new Date();
for (int idx = 0; idx < rowCount; idx++) {
insQuery
.addParameter("name", "a name " + idx)
.addParameter("email", String.format("*****@*****.**", idx))
.addParameter("text", "some text")
.addToBatch();
}
insQuery.executeBatch();
Date after = new Date();
Long span = after.getTime() - before.getTime();
System.out.println(
String.format("inserted %d rows into User table. Time used: %s ms", rowCount, span));
insertIntoUsers += rowCount;
}
public <E extends BaseEntity> List<E> find(String ql, Object... args) {
Query query = createQuery(ql, args);
@SuppressWarnings("unchecked")
List<E> resultList = query.getResultList();
return resultList;
}
private void checkForSharedSourceCommand(AccessNode aNode) {
// create a top level key to avoid the full command toString
String modelName = aNode.getModelName();
Command cmd = aNode.getCommand();
// don't share full scans against internal sources, it's a waste of buffering
if (CoreConstants.SYSTEM_MODEL.equals(modelName)
|| CoreConstants.SYSTEM_ADMIN_MODEL.equals(modelName)
|| TempMetadataAdapter.TEMP_MODEL.getName().equals(modelName)) {
if (!(cmd instanceof Query)) {
return;
}
Query query = (Query) cmd;
if (query.getOrderBy() == null && query.getCriteria() == null) {
return;
}
}
AccessNode other = sharedCommands.get(cmd);
if (other == null) {
sharedCommands.put(cmd, aNode);
} else {
if (other.info == null) {
other.info = new RegisterRequestParameter.SharedAccessInfo();
other.info.id = sharedId.getAndIncrement();
}
other.info.sharingCount++;
aNode.info = other.info;
}
}
public List<NewMessageData> fetchNewMessagesCount(final User user) {
final Query query =
query(
"select new com.nraynaud.sport.data.NewMessageData(m.sender.name, count(m)) from PrivateMessageImpl m where m.receiver = :user and m.read = false group by m.sender.name");
query.setParameter("user", user);
return query.getResultList();
}
@Override
public void execute(
QueryMaster.QueryMasterContext context,
QueryContext queryContext,
Query query,
ExecutionBlockId finalExecBlockId,
Path finalOutputDir)
throws Exception {
CatalogService catalog = context.getWorkerContext().getCatalog();
SubQuery lastStage = query.getSubQuery(finalExecBlockId);
TableMeta meta = lastStage.getTableMeta();
TableStats stats = lastStage.getTableStat();
CreateTableNode createTableNode = (CreateTableNode) lastStage.getBlock().getPlan();
TableDesc tableDescTobeCreated =
new TableDesc(
createTableNode.getTableName(),
createTableNode.getTableSchema(),
meta,
finalOutputDir);
if (createTableNode.hasPartition()) {
tableDescTobeCreated.setPartitionMethod(createTableNode.getPartitionMethod());
}
stats.setNumBytes(getTableVolume(query.systemConf, finalOutputDir));
tableDescTobeCreated.setStats(stats);
query.setResultDesc(tableDescTobeCreated);
catalog.addTable(tableDescTobeCreated);
}
private Collection<GroupData> fetchGroupDataForUser(
final User user, final boolean restrictToSuscribed) {
final String ifConnectedColumns =
"max(ifnull(USER_ID=:userId, false))>0, sum(ifnull(USER_ID=:userId AND LAST_VISIT<PUBLIC_MESSAGES.`DATE`, false))";
final Query query =
entityManager.createNativeQuery(
"select GROUPS.ID, name, count(DISTINCT GROUP_USER.USER_ID), "
+ (user != null ? ifConnectedColumns : " 0,0")
+ " from GROUPS left join GROUP_USER on GROUP_ID=ID "
+ " left join PUBLIC_MESSAGES on PUBLIC_MESSAGES.GROUP_ID=GROUP_USER.GROUP_ID "
+ (restrictToSuscribed ? " where GROUP_USER.USER_ID=:userId" : "")
+ " group by GROUPS.ID order by CREATION_DATE");
if (user != null) query.setParameter("userId", user.getId());
final List<Object[]> list = query.getResultList();
final Collection<GroupData> result = new ArrayList<GroupData>(list.size());
for (final Object[] o : list)
result.add(
new GroupData(
((Number) o[0]).longValue(),
UserStringImpl.valueOf(String.valueOf(o[1])),
((Number) o[2]).longValue(),
((Number) o[3]).intValue() != 0,
((Number) o[4]).intValue()));
return result;
}
@Override
public void deleteSubscription(int userId, int forumId) {
// TODO Auto-generated method stub
Session session = this.sessionFactory.openSession();
Transaction tx = null;
try {
tx = session.beginTransaction();
Query query =
session.createQuery(
"delete from Subscription where userId = :userId and forumId = :forumId");
query.setParameter("userId", userId);
query.setParameter("forumId", forumId);
int result = query.executeUpdate();
System.out.println("Execute query..");
if (result > 0) {
tx.commit();
// System.out.println("Subscription Removed..");
} else {
// System.out.println("Subscription does not exist");
tx.rollback();
}
} catch (HibernateException e) {
if (tx != null) {
tx.rollback();
e.printStackTrace();
}
} finally {
session.close();
}
}
private PaginatedCollection<User> fetchGroupMembers(final Group group) {
final String queryString =
"select u from GroupImpl g inner join g.members u where g=:group order by u.name";
final Query query = query(queryString);
query.setParameter("group", group);
return paginateList(0, 100, query.getResultList());
}
@Override
public List<Forum> getForumsOfaUser(int userId) {
// TODO Auto-generated method stub
List<Forum> forums = new ArrayList<Forum>();
Session session = sessionFactory.openSession();
Transaction tx = null;
try {
tx = session.beginTransaction();
Query query = session.createQuery("from Subscription where userId = :userId");
query.setString("userId", String.valueOf(userId));
List<Subscription> userSubscribedGroups = query.list();
for (Subscription subsc : userSubscribedGroups) {
query = session.createQuery("from Forum where forumId = :forumId");
query.setParameter("forumId", subsc.getForumId());
// add this to a list variable query.uniqueResult();
forums.add((Forum) query.uniqueResult());
}
} catch (HibernateException e) {
if (tx != null) {
tx.rollback();
e.printStackTrace();
}
} finally {
session.close();
}
return forums;
}
private Double fetchGlobalDistance(final Group group) {
final Query query =
query(
"select sum(w.distance) from GroupImpl g left join g.members u left join u.workouts w where g=:group");
query.setParameter("group", group);
return (Double) query.getSingleResult();
}
public void testDescendOne() {
Query q = st.query();
Object[] r = store();
q.constrain(STVectorEU.class);
q.descend("col").constrain(new Integer(17));
st.expect(q, new Object[] {r[0]});
}
public User getUser(String username) {
User result = null;
Session session;
Object user;
session = SessionFactoryUtil.getInstance().getCurrentSession();
try {
Transaction transaction = null;
user = null;
try {
transaction = session.beginTransaction();
Query query =
session.createQuery("from User as user where user.userName='******'");
user = query.uniqueResult();
transaction.commit();
} catch (HibernateException e) {
transaction.rollback();
e.printStackTrace();
}
if (user != null) {
result = (User) user;
}
} finally {
if (session.isOpen()) {
if (session.isOpen()) {
session.disconnect();
session.close();
}
}
}
return result;
}
