Hibernate实现xml的分页查询

最新推荐文章于 2024-09-28 17:02:20 发布

CLA1989

最新推荐文章于 2024-09-28 17:02:20 发布

阅读量389

点赞数

分类专栏： Hibernate分页查询的实现文章标签： hibernate分页查询 xml

本文链接：https://blog.csdn.net/CLA1989/article/details/80984655

版权

Hibernate分页查询的实现专栏收录该内容

5 篇文章 0 订阅

订阅专栏

第二种：读取xml文件的sql数据

1.特征：

String SQL = "";
String fileName = "/sql/Authorize.xml";

String realPath = getRequest().getSession().getServletContext()
.getRealPath(fileName);
SQL = getSqlText("sql", realPath, "GetAuthorize");

QueryResult<Object[]> q = q = authorizedsignatureManager.getScrollDateByXmlSQL(startIndex,maxIndex, SQL, paramValues.toArray())

2.实现类

/**
*
* 解析xml
* @param nodeName
* @param fileName
* @param xmlId
* @return
*/
public static String getSqlText(String nodeName,String fileName,String xmlId){
String text = "";
try{
DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
DocumentBuilder db = dbf.newDocumentBuilder();
File file= new File(fileName);
Document document = db.parse(file);
// Document document = db.parse(fileName);
NodeList nodes = document.getElementsByTagName(nodeName);

for(int i=0;i<nodes.getLength();i++){
NodeList childList = nodes.item(i).getChildNodes();
for(int j=0;j<childList.getLength();j++){
Node child = childList.item(j);
if("id".toUpperCase().equals(child.getNodeName().toUpperCase())){
String childIDStr = child.getTextContent();
if(childIDStr.equals(xmlId)){
for(int k=0;k<childList.getLength();k++){
if("Text".toUpperCase().equals(childList.item(k).getNodeName().toUpperCase())){
Node childText = nodes.item(i).getChildNodes().item(k);
text = childText.getTextContent();
return text;
}
}

}
}else{
continue;
}
}
}
}catch(Exception e){
}
return text;
}

@SuppressWarnings("unchecked")
@Transactional(propagation = Propagation.NOT_SUPPORTED, readOnly = true)
public QueryResult<Object[]> getScrollDataByXmlSQL(int startIndex,
int maxResult, String SQL, Object[] params) throws Exception {

QueryResult<Object[]> qr = new QueryResult<Object[]>();

SQLQuery query = null;
query = createSQLQuery(SQL, params);
if (startIndex != -1 && maxResult != -1)
query.setFirstResult(startIndex).setMaxResults(maxResult);
qr.setResultlist(query.list());

String countQueryString = "select count(*) from (" + SQL + " )";
query = createSQLQuery(countQueryString, params);
qr.setTotalrecord(new Long(query.uniqueResult().toString()));
return qr;
}

/**
* SQL方式查询
*
* @param sql
* 符合SQL语法的查询语句
* @param values
* 数量可变的条件值,按顺序绑定
*/
public SQLQuery createSQLQuery(final String sql, final Object... values) {
SQLQuery query = getSession().createSQLQuery(sql);

if (values != null) {
for (int i = 0; i < values.length; i++) {
query.setParameter(i, values[i]);
}
}
return query;
}

3.xml文件内容

<?xml version="1.0" encoding="UTF-8"?>
<sqlList>

<sql>
<id>GetAuthorize</id>
<text>select a, b, a.signatureuser, c, a.authorizedid, a.monitorpointtypeid
from (with test as
(select m.monitortypeid a, m.monitortypename b, m.monitorpointtype c
from MonitorType m) 　select a, b,
substr(t.ca,
instr(t.ca, ',', 1, c.lv) + 1,
instr(t.ca, ',', 1, c.lv + 1) -
(instr(t.ca, ',', 1, c.lv) + 1)) AS c from
(select a,
b,
',' || c || ',' AS ca,
length(c || ',') - nvl(length(REPLACE(c, ',')) ， 0) AS cnt
FROM test) t, (select LEVEL lv from dual CONNECT BY LEVEL <= 100) c
where c.lv <= t.cnt)
left join authorizedsignature a on a = a.monitortypeid
and c = a.monitorpointtypeid
where b like ? order by a, c desc
</text>
</sql>

</sqlList>