Java Code Examples for org.hibernate.Session.createSQLQuery()

The following are Jave code examples for showing how to use createSQLQuery() of the org.hibernate.Session class. You can vote up the examples you like. Your votes will be used in our system to get more good examples.
+ Save this method
Example 1
Project: ImageClassify   File: MarkService.java   View Source Code Vote up 7 votes
@Override
public List<Integer> getImgIdAble(int thresholdValue) {
	List<Integer> imgidList = new ArrayList<Integer>();
	Session session = HibernateUtils.getSession(); //生成session实例
    Transaction tx = session.beginTransaction();  //创建transaction实例
    try {  
    	Query query = session.createSQLQuery("SELECT img_id FROM image_mark  GROUP BY img_id HAVING COUNT(*) >= " + thresholdValue);  
 		imgidList = query.list();
    	tx.commit();            //提交事务 
	} catch (Exception e) {
		e.printStackTrace();
		tx.rollback();          //回滚事务 
	}finally {
	  HibernateUtils.closeSession();  //关闭Session实例
	}
	return imgidList;
}
 
Example 2
Project: Equella   File: AbstractCopyrightDao.java   View Source Code Vote up 7 votes
protected void deleteHoldingExtras(Session session, long itemId)
{
	SQLQuery sqlQuery = session
		.createSQLQuery(sqlQuery("delete from %h_authors where %h_id in (select id from %h where item_id = ?)"));
	sqlQuery.setLong(0, itemId);
	sqlQuery.executeUpdate();
	sqlQuery = session
		.createSQLQuery(sqlQuery("delete from %h_ids where %h_id in (select id from %h where item_id = ?)"));
	sqlQuery.setLong(0, itemId);
	sqlQuery.executeUpdate();
}
 
Example 3
Project: esup-sgc   File: GenericCardIdService.java   View Source Code Vote up 7 votes
@Override
public String generateCardId(Long cardId) {
	Card card = Card.findCard(cardId);
	if(card.getDesfireIds().get(appName) == null || card.getDesfireIds().get(appName).isEmpty()) {
		Session session = (Session) entityManager.getDelegate();
		SQLQuery existSequenceQuery = session.createSQLQuery("SELECT 1 FROM pg_class where relname = '" + postgresqlSequence + "'");
		if(existSequenceQuery.list().isEmpty()) {
			SQLQuery createSequenceQuery = session.createSQLQuery("CREATE SEQUENCE " + postgresqlSequence);
			int createSequenceQueryResult = createSequenceQuery.executeUpdate();
			log.info("create sequence result : " + createSequenceQueryResult);
		}
		SQLQuery nextValQuery = session.createSQLQuery("SELECT nextval('" + postgresqlSequence + "')");
		BigInteger nextVal = (BigInteger)nextValQuery.list().get(0);
		card.getDesfireIds().put(appName, Long.toString(nextVal.longValue() + getIdCounterBegin(card)));
		card.merge();
	}
	return card.getDesfireIds().get(appName);
}
 
Example 4
Project: jeesupport   File: AbsSupportDao.java   View Source Code Vote up 6 votes
@Override
@SuppressWarnings( "unchecked" )
public < T > List< T > selectBySQL( String _db , String _sql , int _first , int _limit , Object[] _param ,
				Class< T > _cls ) {
	Session sess = _get_session( _db );
	Query query = sess.createSQLQuery( _sql );
	query.setFirstResult( _first );
	query.setMaxResults( _limit );
	int i = 0;
	if ( _param != null ) for ( Object o : _param )
		query.setParameter( i++ , o );
	return query.list();
}
 
Example 5
Project: djpt   File: DaoImpl.java   View Source Code Vote up 6 votes
public void updateBySql(String sql, Object[] vals) {
	Session session = hibernateTemplate.getSessionFactory().openSession();
	
	org.hibernate.Transaction tx = session.beginTransaction();
	
	SQLQuery query = session.createSQLQuery(sql);
	if(vals != null) {
		for(int i=0;i<vals.length;i++) {
			if(vals[i] instanceof Integer) {
				query.setInteger(i, (Integer)vals[i]);
			} else {
				query.setString(i, vals[i].toString());
			}
		}
	}
	query.executeUpdate();
	
	try {
		tx.commit();
	} catch (Exception e) {
		e.printStackTrace();
		tx.rollback();
	}
	session.close(); 
}
 
Example 6
Project: userinterface   File: Authorization.java   View Source Code Vote up 6 votes
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        String login = new String(request.getParameter("login").getBytes("ISO-8859-1"), "UTF-8");
        String password = new String(request.getParameter("password").getBytes("ISO-8859-1"), "UTF-8");

        login = login.equals("null") ? "": login;
        password = password.equals("null") ? "": password;

        if (request.getParameter("action").equals("login")) {
            if (!login.equals("") && !password.equals("")) {
                Session ORMSession = HibernateUtil.getSessionFactory().openSession();
                Query query = ORMSession.createSQLQuery("SELECT \"Login\", \"Password\" FROM users WHERE \"Login\" = '" + login + "' AND \"Password\" = '" + password + "'");
                List result = query.list();
                ORMSession.close();
                if (result.size() > 0) {
                    request.getSession().setAttribute("login", login);
                }
            }
        } else {
            request.getSession().setAttribute("login", "");
        }

        request.getRequestDispatcher("index.jsp").forward(request, response);
    }
 
Example 7
Project: Hibernate_Native_SQL_Scalar_Using_DAO_Using_MAVEN   File: DAO_IMPL.java   View Source Code Vote up 6 votes
@Override
public List<Object[]> getList() {
	//create the session
	Session ses=null;
	//get the session
	ses=HibernateUtil.getSession();
		//create the list of class object
		List<Object[]> list=null;
		//create the named parameterised the query
		SQLQuery query=ses.createSQLQuery("select PRODID,PRODNAME from PRODUCT where price>:price");
		//pass the parameter to query
		query.setString("price", "500");
	//add scalar to query
	query.addScalar("PRODID", StandardBasicTypes.INTEGER);
	query.addScalar("PRODNAME", StandardBasicTypes.STRING);
		//execute the query
		list=query.list();
			//close the session
		HibernateUtil.closeSession(ses);
		//return the list
		return list;
}
 
Example 8
Project: agile-wroking-backend   File: ScheduleRepositoryImpl.java   View Source Code Vote up 5 votes
@Transactional
@Override
public List<ScheduleVO> findByDate(Date date) {
	String sql = "select  t.schedule_id as scheduleId  ,t.date,t.meeting_room_id  as meetingRoomId,t.title,t.open_id as openId,m.room_no as roomNo,t.start_time as startTime,t.end_time as endTime, t.repeat_mode as repeatMode   from (select  p.schedule_id,p.date,s.meeting_room_id,s.title, s.creator_open_id open_id,s.start_time,s.end_time,s.repeat_mode from participant p left join  schedule  s on    p.schedule_id = s.id  ) as t left join meeting_room m on t.meeting_room_id  = m.id  where (t.date=?) or (repeat_mode='W')";
	Session session = entityManager.unwrap(org.hibernate.Session.class);
	SQLQuery query = session.createSQLQuery(sql);
	@SuppressWarnings("unchecked")
	List<ScheduleVO> scheduleVOs = query.setResultTransformer(Transformers.aliasToBean(ScheduleVO.class))
			.setParameter(0, date).list();
	return scheduleVOs.stream().filter(s -> s.isNeedInclude(date)).map(s -> {
		s.setDate(date);
		return s;
	}).sorted().collect(Collectors.toList());

}
 
Example 9
Project: Equella   File: AbstractCopyrightDao.java   View Source Code Vote up 5 votes
protected void deletePortionExtras(Session session, long itemId)
{
	SQLQuery sqlQuery = session
		.createSQLQuery(sqlQuery("delete from %p_authors where %p_id in (select id from %p where item_id = ?)"));
	sqlQuery.setLong(0, itemId);
	sqlQuery.executeUpdate();
	sqlQuery = session
		.createSQLQuery(sqlQuery("delete from %p_topics where %p_id in (select id from %p where item_id = ?)"));
	sqlQuery.setLong(0, itemId);
	sqlQuery.executeUpdate();
}
 
Example 10
Project: school-express-delivery   File: UserDao.java   View Source Code Vote up 5 votes
public String newUsersId() {
    Session session = sessionFactory.getCurrentSession();
    SQLQuery l = session.createSQLQuery("SELECT MAX(user_ID) FROM users");
    String id = (String) l.list().get(0);
    Integer idd = Integer.valueOf(id);
    String newid = String.valueOf(idd + 1);
    return newid;
}
 
Example 11
Project: school-express-delivery   File: ComplaintDao.java   View Source Code Vote up 5 votes
public String newId() {
    Session session = sessionFactory.getCurrentSession();
    Calendar calendar = Calendar.getInstance();
    String year = calendar.get(Calendar.YEAR) + "";
    int month = calendar.get(Calendar.MONTH) + 1;
    int day = calendar.get(Calendar.DAY_OF_MONTH);

    String prefix = year + (month < 10 ? "0" + month : month) + (day < 10 ? "0" + day : day);


    StringBuilder builder = new StringBuilder();
    String sql = builder.append("SELECT MAX(complaint_ID) FROM complaint WHERE complaint_ID LIKE '")
            .append(prefix)
            .append("%' ")
            .toString();
    SQLQuery l = session.createSQLQuery(sql);
    List list = l.list();
    String id = (String) list.get(0);
    if (id == null || "null".equals(id)) {
        //当天没有,生成新的订单号
        id = prefix + "00000";
        return id;
    } else {
        long idd = Long.valueOf(id);
        String newid = String.valueOf(idd + 1);
        return newid;
    }
}
 
Example 12
Project: school-express-delivery   File: ReviewDao.java   View Source Code Vote up 5 votes
public String newId() {
    Session session = sessionFactory.getCurrentSession();
    Calendar calendar = Calendar.getInstance();
    String year = calendar.get(Calendar.YEAR) + "";
    int month = calendar.get(Calendar.MONTH) + 1;
    int day = calendar.get(Calendar.DAY_OF_MONTH);

    String prefix = year + (month < 10 ? "0" + month : month) + (day < 10 ? "0" + day : day);


    StringBuilder builder = new StringBuilder();
    String sql = builder.append("SELECT MAX(review_ID) FROM review WHERE review_ID LIKE '")
            .append(prefix)
            .append("%' ")
            .toString();
    SQLQuery l = session.createSQLQuery(sql);
    List list = l.list();
    String id = (String) list.get(0);
    if (id == null || "null".equals(id)) {
        //当天没有,生成新的订单号
        id = prefix + "00000";
        return id;
    } else {
        long idd = Long.valueOf(id);
        String newid = String.valueOf(idd + 1);
        System.out.println(newid);
        return newid;
    }
}
 
Example 13
Project: ImageClassify   File: CompositionService.java   View Source Code Vote up 5 votes
/**
 * 获取总任务量排行
 */
@Override
public List<UserRank> getRankListOfAmountTask(int start, int num) {
	// String sqlOfAmountOfTask = "";
	 List<UserRank> amountOfTasks = new ArrayList<UserRank>();
	 Session session = HibernateUtils.getSession();//生成Session实例
	 Transaction tx = session.beginTransaction();//生成事务实例 
	 String sql = "SELECT username,sex,avatar_url,COUNT(*) AS amount FROM task LEFT JOIN USER ON user.user_id = task.user_id WHERE task.task_iscommit = 1 GROUP BY task.user_id ORDER BY amount DESC";
	  
	 try { 
		 Query query = session.createSQLQuery(sql); 
		 query.setFirstResult(start-1);
		 query.setMaxResults(num);
		 amountOfTasks = query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
	     //调用session的get()方法,找到此用户到内存中
	    tx.commit();//提交事务
	} catch (Exception e) {
		e.printStackTrace();
		tx.rollback();//事务回滚
	}finally{
		HibernateUtils.closeSession();//关闭session实例
	}     
	 List<UserRank> amountOfTasks2 = new ArrayList<UserRank>(); 
	 
	 for (int i = 0; i < amountOfTasks.size(); i++) { 
		 UserRank amountOfTask = new UserRank();
		  Map map = (Map) amountOfTasks.get(i); 
		  amountOfTask.setUsername(map.get("username")+""); 
		  amountOfTask.setSex(map.get("sex")+"");
		  amountOfTask.setAvatar_url(map.get("avatar_url")+"");
		  amountOfTask.setAmount(map.get("amount")+"");
		  amountOfTask.setIntegral("");
		  amountOfTask.setAccuracy("");
		  amountOfTasks2.add(amountOfTask);
	 }  
	 return amountOfTasks2; 
}
 
Example 14
Project: userinterface   File: CommonStatistics.java   View Source Code Vote up 5 votes
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        String login = String.valueOf(request.getSession().getAttribute("login"));
        if (login.equals("null") || login.equals("")) {
            response.setStatus(response.SC_MOVED_TEMPORARILY);
            response.setHeader("Location", "../index.jsp");
            return;
        }

        List result;
        Session ORMSession = HibernateUtil.getSessionFactory().openSession();
        String queryText = "SELECT SUM(ppr.\"Rank\" + 1), pers.\"Name\" AS pn, site.\"Name\" AS sn\n" +
                "FROM personpagerank AS ppr\n" +
                "LEFT JOIN persons AS pers ON (ppr.\"PersonID\" = pers.\"ID\")\n" +
                "LEFT JOIN pages AS page ON (ppr.\"PageID\" = page.\"ID\")\n" +
                "LEFT JOIN sites AS site ON (page.\"SiteID\" = site.\"ID\")\n" +
                "GROUP BY pn, sn";
        Query query = ORMSession.createSQLQuery(queryText);
        result = query.list();
        ORMSession.close();

        request.setAttribute("table", result);

        String variant = request.getParameter("variant");
        if (variant == null || variant.equals("")) {
            variant = "0";
        }

        String page = request.getParameter("page");
        if (page == null || page.equals("")) {
            page = "1";
        }

        request.getRequestDispatcher("common.jsp?variant=" + variant + "&page=" + page).forward(request, response);
    }
 
Example 15
Project: LibrarySystem   File: ForfeitDaoImpl.java   View Source Code Vote up 5 votes
@Override
public List<ForfeitInfo> getForfeitByReader(Reader reader) {
	// TODO Auto-generated method stub
	String hql = "SELECT f.borrowId,f.forfeit,f.isPay,f.aid FROM forfeitinfo  f,borrowinfo  b where  b.borrowId = f.borrowId and b.readerId =?";
	List list = null;
	try{
		Session session = this.getSession();
		SQLQuery createSQLQuery = session.createSQLQuery(hql);
		createSQLQuery.setInteger(0, reader.getReaderId());
		list = createSQLQuery.list();
		if(list!=null){
			List<ForfeitInfo> infos = new ArrayList<ForfeitInfo>();
			for(int i = 0;i<list.size();i++){
				Object[] objects = (Object[]) list.get(i);
				Integer borrowId = (Integer) objects[0];
				Double forfeit = (Double) objects[1];
				Integer isPay = (Integer) objects[2];
				Integer aid = (Integer) objects[3];
				Admin admin = new Admin();
				admin.setAid(aid);
				BorrowInfo info = new BorrowInfo();
				info.setBorrowId(borrowId);
				ForfeitInfo forfeitInfo = new ForfeitInfo();
				forfeitInfo.setAdmin(admin);
				forfeitInfo.setBorrowId(borrowId);
				forfeitInfo.setForfeit(forfeit);
				forfeitInfo.setIsPay(isPay);
				infos.add(forfeitInfo);
			}
			return infos;
		}
	}catch (Throwable e) {
		e.printStackTrace();
		throw new RuntimeException(e.getMessage());
	}
	return null;
}
 
Example 16
Project: strqlbuilder   File: StrQLBuilder.java   View Source Code Vote up 4 votes
private org.hibernate.Query createQuery(String sql, AbstractEntityManagerImpl abstractEntityManager, boolean fixJpqlCountQuery)
{
	Session session = abstractEntityManager.getSession();
	return isNative || fixJpqlCountQuery ? session.createSQLQuery(sql) : session.createQuery(sql);
}
 
Example 17
Project: ImageClassify   File: CompositionService.java   View Source Code Vote up 4 votes
@Override
public List<UserRank> getRankListOfAll(int start, int num,String rankidentification) {
	 List<UserRank> userRanks = new ArrayList<UserRank>();
	 Session session = HibernateUtils.getSession();//生成Session实例
	 Transaction tx = session.beginTransaction();//生成事务实例 
	 String sqlOfIntegral = "SELECT username,sex,integral,AVG(mark_accuracy) AS accuracy,avatar_url FROM USER LEFT JOIN image_mark ON user.user_id = image_mark.user_id  GROUP BY USER.user_id ORDER BY integral DESC";
	 String sqlOfAccuracy = "SELECT username,sex,integral,AVG(mark_accuracy) AS accuracy,avatar_url FROM USER LEFT JOIN image_mark ON user.user_id = image_mark.user_id  GROUP BY USER.user_id ORDER BY accuracy DESC";
	 String sql = "";
	 if (rankidentification.equals(Constant.INTEGRAL)) {
			sql = sqlOfIntegral;
	 }else if (rankidentification.equals(Constant.ACCURACY)) {
		 	sql = sqlOfAccuracy;
	 }
	 try { 
		 Query query = session.createSQLQuery(sql); 
		 query.setFirstResult(start-1);
		 query.setMaxResults(num);
		 userRanks = query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
	     //调用session的get()方法,找到此用户到内存中
	    tx.commit();//提交事务
	} catch (Exception e) {
		e.printStackTrace();
		tx.rollback();//事务回滚
	}finally{
		HibernateUtils.closeSession();//关闭session实例
	}     
	 List<UserRank> userRanks2 = new ArrayList<UserRank>(); 
	 
	 for (int i = 0; i < userRanks.size(); i++) { 
		  UserRank userRank = new UserRank();
		  Map map = (Map) userRanks.get(i); 
		  userRank.setUsername(map.get("username")+""); 
		  userRank.setSex(map.get("sex")+"");
		  userRank.setIntegral(map.get("integral")+"");
		  userRank.setAvatar_url(map.get("avatar_url")+"");
		  userRank.setAccuracy(map.get("accuracy")+""); 
		  userRank.setAmount("");
		  userRanks2.add(userRank);
	 }  
	 return userRanks2;  
}
 
Example 18
Project: userinterface   File: DailyStatistics.java   View Source Code Vote up 4 votes
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        String begindate = request.getParameter("begindate");
        String enddate = request.getParameter("enddate");
        String siteId = request.getParameter("siteId");
        String personId = request.getParameter("personId");
        String variant = request.getParameter("variant");
        String page = request.getParameter("page");

        Date begin = MyDate.valueOf(begindate);
        Date end = MyDate.valueOf(enddate);

        if (siteId == null || siteId.equals("")) {
            siteId = "0";
        }

        if (personId == null || personId.equals("")) {
            personId = "0";
        }

        if (variant == null || variant.equals("")) {
            variant = "0";
        }

        if (page == null || page.equals("")) {
            page = "1";
        }

        String login = String.valueOf(request.getSession().getAttribute("login"));
        if (login.equals("null") || login.equals("")) {
            response.setStatus(response.SC_MOVED_TEMPORARILY);
            response.setHeader("Location", "../index.jsp");
            return;
        }

        Session ORMSession = HibernateUtil.getSessionFactory().openSession();
        Query query = ORMSession.createSQLQuery("SELECT \"ID\", \"Name\" FROM sites");

        request.setAttribute("sites", query.list());

        query = ORMSession.createSQLQuery("SELECT \"ID\", \"Name\" FROM persons");

        request.setAttribute("persons", query.list());

        String queryText = "SELECT SUM(ppr.\"Rank\"+1), pers.\"Name\" AS pn, site.\"Name\" AS sn\n" +
                "FROM personpagerank AS ppr\n" +
                "LEFT JOIN persons AS pers ON (ppr.\"PersonID\" = pers.\"ID\")\n" +
                "LEFT JOIN pages AS page ON (ppr.\"PageID\" = page.\"ID\")\n" +
                "LEFT JOIN sites AS site ON (page.\"SiteID\" = site.\"ID\")\n" +
                "WHERE page.\"FoundDateTime\" > '0001-01-01 00:00:00'";
        if (begin != null) {
            queryText += " AND page.\"FoundDateTime\" >= '" + begin + "'";
        }
        if (end != null) {
            queryText += " AND page.\"FoundDateTime\" <= '" + end + "'";
        }
        if (!siteId.equals("0")) {
            queryText += " AND site.\"ID\" = " + siteId;
        }
        if (!personId.equals("0")) {
            queryText += " AND pers.\"ID\" = " + personId;
        }
        queryText += " GROUP BY pn, sn";

        query = ORMSession.createSQLQuery(queryText);

        request.setAttribute("table", query.list());

        ORMSession.close();

        request.getRequestDispatcher("daily.jsp?page=1" +
                "&begindate=" + begindate +
                "&enddate=" + enddate +
                "&siteId=" + siteId +
                "&personId=" + personId +
                "&variant=" + variant +
                "&page=" + page).forward(request, response);

    }
 
Example 19
Project: Library   File: LibraryServicesImpl.java   View Source Code Vote up 3 votes
public List<FineResponse> getAllFines(){
	
	//System.out.println("Paid " + paid);
	Session session = this.sessionFactory.openSession();
	//Transaction transaction = session.beginTransaction();
	
	StringBuilder builder = new StringBuilder();
	
	builder.append("select b.card_id , sum(fine_amt) from fine f ,book_loan b where b.loan_id= f.loan_id and f.paid=0");
	
	/*if(paid){
		builder.append(" and f.paid=1");
	
	}*/
	
	builder.append(" group by b.card_id");
	
	Query query = session.createSQLQuery(builder.toString());
	
	List<Object[]> object = query.list();
	
	List<FineResponse> fineResponses = new ArrayList<FineResponse>();
	
	for(Object[] object2:object){
		FineResponse fineResponse = new FineResponse();
		fineResponse.setCardId((Integer)object2[0]);
		fineResponse.setAmount(Double.toString((Double)object2[1]));
		
		fineResponses.add(fineResponse);
	}
	
	session.close();
	
	
	return fineResponses;
}
 
Example 20
Project: Android_Code_Arbiter   File: HibernateSql.java   View Source Code Vote up 3 votes
public void testQueries(SessionFactory sessionFactory, String input) {

        Session session = sessionFactory.openSession();

        Criteria criteria = session.createCriteria(UserEntity.class);

        //The following would need to be audited

        criteria.add(Restrictions.sqlRestriction("test=1234" + input));

        session.createQuery("select t from UserEntity t where id = " + input);

        session.createSQLQuery(String.format("select * from TestEntity where id = %s ", input));

        //More sqlRestriction signatures

        criteria.add(Restrictions.sqlRestriction("param1  = ? and param2 = " + input,input, StandardBasicTypes.STRING));
        criteria.add(Restrictions.sqlRestriction("param1  = ? and param2 = " + input,new String[] {input}, new Type[] {StandardBasicTypes.STRING}));

        //OK nothing risky here..

        criteria.add(Restrictions.sqlRestriction("test=1234"));

        final String localSafe = "where id=1337";

        session.createQuery("select t from UserEntity t " + localSafe);

        final String localSql = "select * from TestEntity " + localSafe;

        session.createSQLQuery(localSql);

        //More sqlRestriction signatures (with safe binding)

        criteria.add(Restrictions.sqlRestriction("param1  = ?",input, StandardBasicTypes.STRING));
        criteria.add(Restrictions.sqlRestriction("param1  = ? and param2 = ?", new String[] {input}, new Type[] {StandardBasicTypes.STRING}));

    }