上一篇中我们已经创建好我们的持久类Persistence,持久类就是对数据库数据进行相应操作的一个工具实现类。
现在来看看Persistence都能干些什么.
代码如下:
package com.lh446.commons;
import java.io.Serializable;
import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import org.apache.commons.beanutils.DynaBean;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.HibernateTemplate;
import com.lh446.commons.code.IsAble;
import com.lh446.commons.code.IsMenu;
import com.lh446.commons.util.BeanHelper;
import com.lh446.commons.util.HQLInfoHelper;
import com.lh446.commons.util.Page;
import com.lh446.commons.util.PageMapBean;
import com.lh446.commons.util.SQLHelper;
import com.lh446.exception.BussinessException;
public class Persistence {
private JdbcTemplate jdbcTemplate;
private HibernateTemplate hibernateTemplate;
public JdbcTemplate getJdbcTemplate() {
return jdbcTemplate;
}
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
public HibernateTemplate getHibernateTemplate() {
return hibernateTemplate;
}
public void setHibernateTemplate(HibernateTemplate hibernateTemplate) {
this.hibernateTemplate = hibernateTemplate;
}
/**
* 保存操作
*
* @param obj
* @return
*/
public Serializable saveObj(Object obj) {
return hibernateTemplate.save(obj);
}
/**
* 更新操作
*
* @param obj
*/
public void updateObj(Object obj) {
hibernateTemplate.update(obj);
}
/**
* 删除操作
*
* @param obj
*/
public void deleteObj(Object obj) {
hibernateTemplate.delete(obj);
}
/**
* 根据主键获取实体
*
* @param clazz
* @param id
* @return
*/
public <T> T getEntityById(Class<T> clazz, Serializable id) {
return (T) hibernateTemplate.get(clazz, id);
}
/**
* 根据主键删除实体
*
* @param <T>
* @param clazz
* @param id
*/
public <T> void deleteObjById(Class<T> clazz, Serializable id) {
hibernateTemplate.delete(getEntityById(clazz, id));
}
/**
* 删除所有实体
*
* @param list
*/
public void deleteAll(Collection<?> list) {
hibernateTemplate.deleteAll(list);
}
/**
* 查询数据
*
* @param hql
* @return
*/
public List queryList(final HQLInfo hql) {
return query(hql, null).getResult();
}
/**
* 查询操作,返回带分页信息的实体
*
* @param hql
* @return
*/
public PageMapBean query(final HQLInfo hql) {
return query(hql, null);
}
/**
* 制定分页信息查询操作,返回带分页信息的实体,
* @param hql
* @param page
* @return
*/
public PageMapBean query(final HQLInfo hql, final Page page) {
if (page != null && page.getPage() == null)
page.setPage(1);
if (page != null && page.getPageSize() == null)
page.setPageSize(10);
HQLInfo hql2 = new HQLInfo();
Page nextPage = new Page();
try {
BeanHelper.copyProperties(hql, hql2);
if (page != null)
BeanHelper.copyProperties(page, nextPage);
} catch (IllegalArgumentException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
} catch (IllegalAccessException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
} catch (InvocationTargetException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
} catch (SecurityException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (NoSuchMethodException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if (page != null && page.getUsepager()) {
Integer size = getTotalSize(hql2);
nextPage.setTotalSize(size);
} else {
nextPage = null;
}
List list;
if (hql.isHQL()) {
list = (List) hibernateTemplate
.executeWithNativeSession(new HibernateCallback() {
@Override
public Object doInHibernate(Session arg0)
throws HibernateException, SQLException {
// TODO Auto-generated method stub
List list = null;
try {
Query query = HQLInfoHelper.createQuery(arg0,
hql);
if (page != null && page.getUsepager()) {
query.setFirstResult((page.getPage() - 1)
* page.getPageSize());
query.setMaxResults(page.getPageSize());
}
list = query.list();
} catch (Exception e) {
e.printStackTrace();
throw new BussinessException("查询数据库出现异常!");
}
return list;
}
});
} else {
list = (List<DynaBean>) jdbcTemplate
.execute(new ConnectionCallback() {
@Override
public Object doInConnection(Connection arg0)
throws SQLException, DataAccessException {
// TODO Auto-generated method stub
PreparedStatement ps = null;
ResultSet rs = null;
List<DynaBean> list = new ArrayList<DynaBean>();
try {
if (page != null && page.getUsepager()) {
hql.setSql(hql.getSql()
+ " limit "
+ ((page.getPage() - 1) * page
.getPageSize()) + ","
+ page.getPageSize());
}
ps = (PreparedStatement) SQLHelper
.createStatement(hql, hibernateTemplate
.getSessionFactory(), arg0);
boolean r = ps.execute();
String message = "";
if (r) {
rs = ps.getResultSet();
while (rs.next()) {
ResultSetMetaData rsdata = rs
.getMetaData();
int a = rsdata.getColumnCount();
if (a < 1) {
return null;
}
DynaBean bean = SQLHelper
.createDynaBeanResult(rsdata,
rs);
if (bean != null)
list.add(bean);
}
}
} catch (Exception e) {
e.printStackTrace();
throw new BussinessException("查询数据库出现异常!");
} finally {
try {
if (rs != null)
rs.close();
} catch (Exception ex) {
ex.printStackTrace();
throw new BussinessException(
"查询数据库出现异常!无法关闭ResultSet!");
}
try {
if (ps != null)
ps.close();
} catch (Exception ex) {
ex.printStackTrace();
throw new BussinessException(
"查询数据库出现异常!无法关闭PreparedStatement!");
}
try {
if (arg0 != null)
arg0.close();
} catch (Exception ex) {
ex.printStackTrace();
throw new BussinessException(
"查询数据库出现异常!无法关闭Connection!");
}
}
return list;
}
});
}
PageMapBean bean = new PageMapBean(nextPage, list);
return bean;
}
/**
* 获取查询的总结果数
* @param hql
* @return
*/
public Integer getTotalSize(HQLInfo hql) {
hql = fomatCountSql(hql);
String sql = SQLHelper.changeHQLtoSQL(hql.getSql(),
hibernateTemplate.getSessionFactory());
List<Integer> list = jdbcTemplate.query(sql, hql.getValues(),
new RowMapper() {
@Override
public Object mapRow(ResultSet rs, int rowNum)
throws SQLException {
// TODO Auto-generated method stub
try {
Integer totalsize = rs.getInt("totalsize");
return totalsize;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
});
if (list != null && list.size() > 0)
return list.get(0);
return 0;
}
/**
* 格式化获取总数量的sql语句
* @param hql
* @return
*/
private HQLInfo fomatCountSql(HQLInfo hql) {
String sql = hql.getSql();
sql = "select count(*) totalsize " + sql.substring(sql.indexOf("from"));
hql.setHQL(false);
hql.setSql(sql);
return hql;
}
public static void main(String[] args) {
ApplicationContext ctx = new ClassPathXmlApplicationContext(
"applicationContext.xml");
StringBuffer bf = new StringBuffer();
bf.append("select a.functionId,a.functionName,a.createTime from Functionn a ");
bf.append(" where a.ismenu='").append(IsMenu.ISMENU).append("'");
bf.append(" and a.isable='").append(IsAble.ABLED).append("'");
bf.append(" group by a.functionId");
bf.append(" order by a.functionId,a.functionindex");
String sql = HQLInfoHelper.formatSQLStr(bf.toString());
String[] params = HQLInfoHelper.getParaNames(sql);
HQLInfo info = HQLInfoHelper.getHQLInfo(sql, params, null);
// String
// aa=SQLHelper.changeHQLtoSQL(ss,(SessionFactory)ctx.getBean("sessionFactory"));
// List<DynaBean>
// list=((Persistence)ctx.getBean("persistence")).query(info);
// for (DynaBean dynaBean : list) {
// System.out.println((Integer)dynaBean.get("functionId"));
// System.out.println((Timestamp)dynaBean.get("createTime"));
// }
}
}
通过上面代码可以看到Persistence无非就是一些常用的增,删,该,查操作。
对于PageMapBean是一个封装了查询数据和分页信息的bean:
package com.lh446.commons.util;
import java.util.List;
public class PageMapBean {
//分页信息
private Page page;
//查询出来的数据
private List result;
public Page getPage() {
return page;
}
public void setPage(Page page) {
this.page = page;
}
public List getResult() {
return result;
}
public void setResult(List result) {
this.result = result;
}
public PageMapBean(Page page, List result) {
super();
this.page = page;
this.result = result;
}
public PageMapBean() {
super();
}
}
对于HQLInfo类是一个封装了查询sql的信息的类,后续将讲到。
Persistence类中的
/**
* 制定分页信息查询操作,返回带分页信息的实体,
* @param hql
* @param page
* @return
*/
public PageMapBean query(final HQLInfo hql, final Page page) 方法就实现了查询操作,支持HQL和jdbc的查询操作。