Java Session.createSQLQuery方法代码示例

import org.hibernate.Session; //导入方法依赖的package包/类
@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;
}
 

import org.hibernate.Session; //导入方法依赖的package包/类
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();
}
 

import org.hibernate.Session; //导入方法依赖的package包/类
@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);
}
 

import org.hibernate.Session; //导入方法依赖的package包/类
@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();
}
 

import org.hibernate.Session; //导入方法依赖的package包/类
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(); 
}
 

import org.hibernate.Session; //导入方法依赖的package包/类
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);
    }
 

import org.hibernate.Session; //导入方法依赖的package包/类
@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;
}
 

import org.hibernate.Session; //导入方法依赖的package包/类
@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());

}
 

import org.hibernate.Session; //导入方法依赖的package包/类
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();
}
 

import org.hibernate.Session; //导入方法依赖的package包/类
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;
}
 

import org.hibernate.Session; //导入方法依赖的package包/类
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;
    }
}
 

import org.hibernate.Session; //导入方法依赖的package包/类
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;
    }
}
 

import org.hibernate.Session; //导入方法依赖的package包/类
/**
 * 获取总任务量排行
 */
@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; 
}
 

import org.hibernate.Session; //导入方法依赖的package包/类
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);
    }
 

import org.hibernate.Session; //导入方法依赖的package包/类
@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;
}