查询例子

一张表BaseTypeEntity中有国家，省份，城市，国家的parentId是0，省的parentId是国家的id，城市的parentId是省的id。输入国家或者省或者城市的中英文查询城市，支持模糊查询

//查询满足条件的城市，将一张表分为三张表用，c代表城市这张表，b代表省这张表，a代表国家这张表
	public List<BaseTypeEntity> getCityQueryList(Map<String, Object> conds){
		String sql="select distinct c from BaseTypeEntity a, BaseTypeEntity b, BaseTypeEntity c";
		sql += getCityListPagingWhereClause(conds);
		Query query = this.getSession().createQuery(sql);
		Integer pageSize = (Integer) conds.get("pageSize");
		Integer startIndex = (Integer) conds.get("startIndex");
		query.setFirstResult(startIndex.intValue());
		query.setMaxResults(pageSize.intValue());
		List<BaseTypeEntity> list = query.list();
		return list;
	}

 

//查询城市的SQL语句
	private String getCityListPagingWhereClause(Map<String, Object> conds){
		
		String sql = " where 1=1 and c.parentId = b.id and b.parentId = a.id and c.dataType = 'city' and b.dataType='province' and a.dataType='country' ";
		
		if (conds != null) {
			
			Set<String> keyset = conds.keySet();
			for (String key : keyset) {
				if (!key.equals("pageSize") && !key.equals("startIndex")) {
					if (key.equals("cnameCountry")){
						sql+= " and a.cname like '%"+conds.get(key)+"%' ";
					}
					
					if (key.equals("enameCountry")){
						sql+= " and a.ename like  '%"+conds.get(key)+"%' ";
					}
					
					if (key.equals("cnameProvince")){
						sql+= " and b.cname like '%"+conds.get(key)+"%' ";
					}
					
					if (key.equals("enameProvince")){
						sql+= " and b.ename like '%"+conds.get(key)+"%' ";
					}
					
					if (key.equals("cnameCity")){
						sql+= " and c.cname like '%"+conds.get(key)+"%' ";
					}
					
					if (key.equals("enameCity")){
						sql+= " and c.ename like '%"+conds.get(key)+"%' ";
					}
				}
			}

		}
		sql += " order by c.id";
		return sql;
	}