无聊到爆了。 今天整理了自己辛苦一天的学习成果。
spring mvc 整合hibernate+自定义sql处理操作+数据库连接池
数据库连接池。
首先你得知道什么是数据库连接池。
数据库连接池负责分配、管理和释放数据库连接,它允许应用程序重复使用一个现有的数据库连接,而不是再重新建立一个;释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏。这项技术能明显提高对数据库操作的性能。
少说多写,贴代码
mysql-dataSource-BasicDataSource.xml mysql数据库连接池配置
<?xml version="1.0" encoding="utf-8" ?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:mvc="http://www.springframework.org/schema/mvc"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.1.xsd
http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-4.1.xsd"
>
<!-- 解析数据库用户名和密码 -->
<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="locations">
<list>
<value>classpath:/spring/xml/database.properties</value>
</list>
</property>
</bean>
<!-- mysql 数据库连接池 -->
<bean id="mysql_driver_manager_dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<!-- 使用的JDBC驱动的完整有效的java 类名 -->
<property name="driverClassName">
<value>${jdbc.driverClassName}</value>
</property>
<!-- 传递给JDBC驱动的用于建立连接的URL -->
<property name="url">
<value>${jdbc.url}</value>
</property>
<!-- 传递给JDBC驱动的用于建立连接的用户名 -->
<property name="username">
<value>${jdbc.username}</value>
</property>
<!-- 传递给JDBC驱动的用于建立连接的密码 -->
<property name="password">
<value>${jdbc.password}</value>
</property>
<!-- 返回的数据库连接(connection)是否采用默认提交 -->
<property name="defaultAutoCommit">
<value>true</value>
</property>
<!-- 设置数据源是否仅能执行只读操作, 默认值为 false -->
<property name="defaultReadOnly">
<value>false</value>
</property>
<!-- 最大活动连接: 连接池在同一时间能够分配的最大活动连接的数量,如果设置为非正数则表示不限制 -->
<property name="maxActive">
<value>${jdbc.maxActive}</value>
</property>
<!-- 最大空闲连接: 连接池中容许保持空闲状态的最大连接数量,超过的空闲连接将被释放,如果设置为负数表示不限制 -->
<property name="maxIdle">
<value>${jdbc.maxIdle}</value>
</property>
<!-- 最小空闲连接: 连接池中容许保持空闲状态的最小连接数量,低于这个数量将创建新的连接,如果设置为0则不创建 -->
<property name="minIdle">
<value>${jdbc.minIdle}</value>
</property>
<!-- 最大等待时间: 当没有可用连接时,连接池等待连接被归还的最大时间(以毫秒计数),超过时间则抛出异常,如果设置为-1表示无限等待 -->
<property name="maxWait">
<value>${jdbc.maxWait}</value>
</property>
<!-- SQL查询,用来验证从连接池取出的连接,在将连接返回给调用者之前.如果指定,则查询必须是一个SQL SELECT并且必须返回至少一行记录 -->
<property name="validationQuery">
<value>${jdbc.validationQuery}</value>
</property>
<!-- 初始化连接: 连接池启动时创建的初始化连接数量-->
<property name="initialSize">
<value>${jdbc.initialSize}</value>
</property>
<!-- 标记当Statement或连接被泄露时是否打印程序的stack traces日志。被泄露的Statements和连接的日志添加在每个连接打开或者生成新的Statement,因为需要生成stack trace。 -->
<property name="logAbandoned">
<value>false</value>
</property>
<!-- 标记是否删除泄露的连接,如果他们超过了removeAbandonedTimout的限制.如果设置为true, 连接被认为是被泄露并且可以被删除,如果空闲时间超过removeAbandonedTimeout.设置为true可以为写法糟糕的没有关闭连接的程序修复数据库连接. -->
<property name="removeAbandoned">
<value>true</value>
</property>
<!-- 一次数据库操作执行时间超过多少秒的连接被认为是需要移除的 -->
<property name="removeAbandonedTimeout">
<value>${jdbc.removeAbandonedTimeout}</value>
</property>
<!-- 指明连接是否被空闲连接回收器(如果有)进行检验.如果检测失败,则连接将被从池中去除.注意: 设置为true后如果要生效,validationQuery参数必须设置为非空字符串-->
<property name="testWhileIdle">
<value>true</value>
</property>
<!-- 指明是否在归还到池中前进行检验 注意: 设置为true后如果要生效,validationQuery参数必须设置为非空字符串 -->
<property name="testOnReturn">
<value>false</value>
</property>
<!-- 指明是否在从池中取出连接前进行检验,如果检验失败,则从池中去除连接并尝试取出另一个.注意: 设置为true后如果要生效,validationQuery参数必须设置为非空字符串 -->
<property name="testOnBorrow">
<value>true</value>
</property>
<!-- 连接在池中保持空闲而不被空闲连接回收器线程 (如果有)回收的最小时间值,单位毫秒 -->
<property name="minEvictableIdleTimeMillis">
<value>${jdbc.minEvictableIdleTimeMillis}</value>
</property>
<!-- 开启池的prepared statement 池功能 -->
<property name="poolPreparedStatements">
<value>false</value>
</property>
<!-- 在每次空闲连接回收器线程(如果有)运行时检查的连接数量 -->
<property name="numTestsPerEvictionRun">
<value>${jdbc.numTestsPerEvictionRun}</value>
</property>
</bean>
</beans>
其中 database.properties 就是对${jdbc.xxxxx}的一个映射文件,不懂的查百度
spring.xml spring 组件配置
<?xml version="1.0" encoding="utf-8" ?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:mvc="http://www.springframework.org/schema/mvc"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.1.xsd
http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-4.1.xsd"
>
<!-- 扫描这个包下的所有组件 -->
<context:component-scan base-package="spring.beans" />
<context:annotation-config />
<import resource="mysql-dataSource-BasicDataSource.xml" />
<import resource="hibernate.xml" />
</beans>
hibernate.xml hibernate 配置文件
<?xml version="1.0" encoding="utf-8" ?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:mvc="http://www.springframework.org/schema/mvc"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.1.xsd
http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-4.1.xsd"
>
<bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean" scope="request">
<property name="dataSource">
<ref bean="mysql_driver_manager_dataSource"/>
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
<prop key="hibernate.show_sql">true</prop>
</props>
</property>
<property name="annotatedClasses">
<list>
<value>hibernate.mapping.table.Student</value>
</list>
</property>
</bean>
</beans>
核心组件 db_service 供给给其它外部组件调用
package spring.beans.db;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Service;
import spring.beans.db.custom.CustomDbService;
import spring.beans.db.hibernate.HibernateService;
@Service("db_service")
@Scope("request")
public class DbService
{
/**
* 自定义封装数据库操作对象
* */
@Value(value="#{custom_db_service}")
public CustomDbService customDb;
/**
* hibernate 数据库封装操作对象
* */
@Value(value="#{hibernate_db_service}")
public HibernateService hibernateService;
}
自定义数据库操作组件 CustomDbService
package spring.beans.db.custom;
import java.sql.Connection;
import java.util.List;
import java.util.Map;
import spring.beans.db.hibernate.HibernateService;
public interface CustomDbService
{
/**
* 数据库连接
* */
public void setConnection(Connection conn);
public Connection getConnection();
/**
* 查询所有数据
* @param
* String 要执行的sql语句
* @param
* String[] keyVal 映射数组
* @return
* List<Map<String,String>> result 查询结果集
* */
public List<Map<String,String>> query(String sql,String[] keyVal);
public List<Map<String,String>> query(String sql);
/**
* 查询单行数据
* @param
* String 要执行的sql语句
* @param
* String[] keyVal 映射数组
* @return
* Map<String,String> result 查询结果集
* */
public Map<String,String> find(String sql,String[] keyVal);
public Map<String,String> find(String sql);
/**
* 更新数据
* @param
* String 要执行的sql语句
* @param
* String[] keyVal 映射数组
* @return
* int resultInt 受影响的行数
* */
public int update(String sql,String[] keyVal);
public int update(String sql);
/**
* 新增数据
* @param
* String 要执行的sql语句
* @param
* String[] keyVal 映射数组
* @return
* int resultInt 新增成功行数
* */
public int insert(String sql,String[] keyVal);
public int insert(String sql);
/**
* 删除数据库
* @param
* String 要执行的sql语句
* @param
* String[] keyVal 映射数组
* @return
* int 受影响的行数
* */
public int delete(String sql,String[] keyVal);
public int delete(String sql);
/**
* 调用存储过程
* @param
* String callFunc 存储过程名称
* List<Map<String,String>> 存储过程参数值 如:Map<"int","22">
* */
public List<Map<String,String>> callResult(String callFunc,List<DataType> keyVal);
public List<Map<String,String>> callResult(String callFunc);
/**
* 事务处理
* @param
* String[] sqls 要处理的sql语句
* @return
* boolean 是否处理成功
* */
public boolean transcationSql(String[] sqls);
/**
* 自动提交
* */
public void autoCommit(boolean commit);
}
对CustomDbService 接口的实现类
CustomDbRealization
package spring.beans.db.custom;
import java.sql.Connection;
import java.util.List;
import java.util.ArrayList;
import java.util.Map;
import java.util.HashMap;
import java.sql.CallableStatement;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import javax.annotation.PostConstruct;
import javax.annotation.PreDestroy;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Repository;
import spring.beans.db.hibernate.HibernateService;
/**
* mysql 操作实现类 - 容器
* author : 谭勇
* create_date : 2017-04-13
* */
@Repository("custom_db_service")
@Scope("request")
public final class CustomDbRealization implements CustomDbService
{
private Connection conn;
/**
* 对象初始化
* */
@PostConstruct
public void initDb()
{
}
@Value(value="#{hibernate_db_service}")
public void hibernateToConnection(HibernateService hibernate)
{
setConnection(hibernate.getConnection());
}
@Override
public void setConnection(Connection conn)
{
this.conn = conn;
}
@Override
public Connection getConnection()
{
return this.conn;
}
@Override
public List<Map<String, String>> query(String sql, String[] keyVal)
{
PreparedStatement pre = null;
ResultSet result = null;
ResultSetMetaData meta = null;
try{
pre = conn.prepareStatement(sql);
if(keyVal != null)
{
//映射到问号
for(int i=1;i<=keyVal.length;i++)
{
pre.setString(i, keyVal[i-1]);
}
}
result = pre.executeQuery();
if (result.next())
{
meta = result.getMetaData();
result.last();
List<Map<String,String>> list = new ArrayList<Map<String,String>>(result.getRow());
result.first();
int propertiesLength = meta.getColumnCount();
do{
Map<String,String> map = new HashMap<String,String>(propertiesLength);
for(int i=1;i<=propertiesLength;i++)
{
String keyName = meta.getColumnName(i);
map.put(keyName, result.getString(keyName));
}
list.add(map);
}while(result.next());
return list;
}
}catch(SQLException e)
{
e.printStackTrace();
}finally{
closePreparedStatement(pre);
closeResultSet(result);
}
return null;
}
@Override
public List<Map<String, String>> query(String sql)
{
return query(sql,null);
}
@Override
public Map<String, String> find(String sql, String[] keyVal)
{
PreparedStatement pre = null;
ResultSet result = null;
ResultSetMetaData meta = null;
try{
pre = conn.prepareStatement(sql);
if(keyVal != null)
{
//映射到问号
for(int i=1;i<=keyVal.length;i++)
{
pre.setString(i, keyVal[i-1]);
}
}
result = pre.executeQuery();
if (result.next())
{
meta = result.getMetaData();
int propertiesLength = meta.getColumnCount();
Map<String,String> map = new HashMap<String,String>(propertiesLength);
for(int i=1;i<=propertiesLength;i++)
{
String keyName = meta.getColumnName(i);
map.put(keyName, result.getString(keyName));
}
return map;
}
}catch(SQLException e)
{
e.printStackTrace();
}finally{
closePreparedStatement(pre);
closeResultSet(result);
}
return null;
}
@Override
public Map<String, String> find(String sql)
{
return find(sql,null);
}
@Override
public int update(String sql, String[] keyVal)
{
PreparedStatement pre = null;
try{
pre = conn.prepareStatement(sql);
if(keyVal != null)
{
//映射到问号
for(int i=1;i<=keyVal.length;i++)
{
pre.setString(i, keyVal[i-1]);
}
}
return pre.executeUpdate();
}catch(SQLException e)
{
e.printStackTrace();
}finally{
closePreparedStatement(pre);
}
return 0;
}
@Override
public int update(String sql)
{
return update(sql,null);
}
@Override
public int insert(String sql, String[] keyVal)
{
PreparedStatement pre = null;
try{
pre = conn.prepareStatement(sql);
if(keyVal != null)
{
//映射到问号
for(int i=1;i<=keyVal.length;i++)
{
pre.setString(i, keyVal[i-1]);
}
}
return pre.executeUpdate();
}catch(SQLException e)
{
e.printStackTrace();
}finally{
closePreparedStatement(pre);
}
return 0;
}
@Override
public int insert(String sql)
{
return insert(sql,null);
}
@Override
public int delete(String sql, String[] keyVal)
{
PreparedStatement pre = null;
try{
pre = conn.prepareStatement(sql);
if(keyVal != null)
{
//映射到问号
for(int i=1;i<=keyVal.length;i++)
{
pre.setString(i, keyVal[i-1]);
}
}
return pre.executeUpdate();
}catch(SQLException e)
{
e.printStackTrace();
}finally{
closePreparedStatement(pre);
}
return 0;
}
@Override
public int delete(String sql)
{
return delete(sql,null);
}
/**
* 调用存储过程
* @param
* String callFunc 存储过程名
* */
public List<Map<String,String>> callResult(String callFunc,List<DataType> keyVal)
{
String call = "{call " + callFunc + "}";
ResultSetMetaData meta = null;
CallableStatement callableStatement= null;
ResultSet result = null;
try{
callableStatement = conn.prepareCall(call);
if(keyVal != null)
{
for(int i=1;i<=keyVal.size();i++)
{
DataType data = keyVal.get(i-1);
switch(data.getType())
{
case ValueTypeSource.STRING:
callableStatement.setString(i, String.valueOf(data.getValue()));
break;
case ValueTypeSource.INT:
callableStatement.setInt(i, Integer.valueOf(data.getValue()));
break;
case ValueTypeSource.LONG:
callableStatement.setLong(i, Long.valueOf(data.getValue()));
break;
case ValueTypeSource.DOUBLE:
callableStatement.setDouble(i, Double.valueOf(data.getValue()));
break;
default:
callableStatement.setString(i,String.valueOf(data.getValue()));
}
}
}
callableStatement.execute();
result = callableStatement.getResultSet();
meta = result.getMetaData();
result.last();
List<Map<String,String>> list = new ArrayList<Map<String,String>>(result.getRow());
result.first();
int propertiesLength = meta.getColumnCount();
do{
Map<String,String> map = new HashMap<String,String>(propertiesLength);
for(int i=1;i<=propertiesLength;i++)
{
String keyName = meta.getColumnName(i);
map.put(keyName, result.getString(keyName));
}
list.add(map);
}while(result.next());
return list;
}catch(SQLException e)
{
e.printStackTrace();
return null;
}finally{
closeCallableStatement(callableStatement);
closeResultSet(result);
}
}
@Override
public List<Map<String,String>> callResult(String callFunc)
{
return callResult(callFunc,null);
}
/**
* 关闭资源链接
* */
private void closePreparedStatement(PreparedStatement pre)
{
if(pre != null)
{
try
{
pre.close();
}catch(SQLException e)
{
e.printStackTrace();
}
}
}
private void closeResultSet(ResultSet result)
{
if(result != null)
{
try
{
result.close();
}catch(SQLException e)
{
e.printStackTrace();
}
}
}
private void closeCallableStatement(CallableStatement call)
{
if(call != null)
{
try
{
call.close();
}catch(SQLException e)
{
e.printStackTrace();
}
}
}
private void closeConnection(Connection conn)
{
if(conn != null)
{
try
{
conn.close();
}catch(SQLException e)
{
e.printStackTrace();
}
}
}
/**
* 对象注销
* */
@PreDestroy
public void closeDb()
{
closeConnection(conn);
}
/**
* 事务处理 只支持(删除,修改,新增)
* */
@Override
public boolean transcationSql(String[] sqls)
{
autoCommit(false);
PreparedStatement pre = null;
boolean runSqlClient = true;
try{
for(String sql : sqls){
pre = conn.prepareStatement(sql);
int resultInt = pre.executeUpdate();
if(resultInt <= 0)
{
runSqlClient = false;
break;
}
}
if(runSqlClient)
conn.commit();
else
conn.rollback();
}catch(SQLException e)
{
runSqlClient = false;
try
{
conn.rollback();
}catch(SQLException e1)
{
}
}finally{
closePreparedStatement(pre);
autoCommit(true);
}
if(runSqlClient)
return true;
else
return false;
}
/**
* 是否自动提交
* */
@Override
public void autoCommit(boolean commit)
{
try{
conn.setAutoCommit(commit);
}catch(SQLException e)
{
}
}
}
CustomDbRealization 中调用存储过程需要的扩展类
DataType
package spring.beans.db.custom;
public final class DataType
{
private String keyName;
private String value;
private int type;
public DataType(){}
public DataType(String keyName,String value,int type)
{
setKeyName(keyName);
setValue(value);
setType(type);
}
public void setKeyName(String keyName)
{
this.keyName = keyName;
}
public void setValue(String value)
{
this.value = value;
}
public void setType(int type)
{
this.type = type;
}
public String getKeyName()
{
return keyName;
}
public String getValue()
{
return value;
}
public int getType()
{
return type;
}
}
ValueType
package spring.beans.db.custom;
public enum ValueType
{
INT(ValueTypeSource.INT),
STRING(ValueTypeSource.STRING),
DOUBLE(ValueTypeSource.DOUBLE),
CHAR(ValueTypeSource.CHAR),
DATE(ValueTypeSource.DATE),
BLOB(ValueTypeSource.BLOB),
LONG(ValueTypeSource.LONG);
private int type;
private ValueType(int type)
{
this.type = type;
}
public int getType()
{
return type;
}
}
ValueTypeSource
package spring.beans.db.custom;
public final class ValueTypeSource
{
public final static int INT=1,
STRING=2,
DOUBLE=3,
CHAR=4,
DATE=5,
LONG=6,
BLOB=7;
}
至此 自定义数据库操作类结束
hibernate 数据库操作类
HibernateService 对外提供访问的接口
package spring.beans.db.hibernate;
import java.sql.Connection;
import org.hibernate.Session;
public interface HibernateService
{
/**
* 查询一条数据
* @param String hql hql语句
* @return Object obj 实例Object
* */
public Object hqlFind(String hql);
/**
* 查询一条数据
* @param String hql hql语句
* @param Map<String,Object> keyVal 映射对象
* @return Object obj 实例Object
* */
public Object hqlFind(String hql,Map<String,Object> keyVal);
/**
* hql 执行
* @param String hql hql语句
* @return int 受影响的行数
* */
public int hqlExecute(String hql);
/**
* hql 执行
* @param String hql hql语句
* @param Map<String,Object> keyVal 映射对象
* @return int 受影响的行数
* */
public int hqlExecute(String hql,Map<String,Object> keyVal);
/**
* 查询多条数据
* @param String hql hql语句
* @return List<Object> list 实例Object List
* */
public List<Object> hqlQuery(String hql);
/**
* 查询多条数据
* @param String hql hql语句
* @param Map<String,Object> keyVal 映射对象
* @return List<Object> list 实例Object List
* */
public List<Object> hqlQuery(String hql,Map<String,Object> keyVal);
/**
* 查询多条数据 分页查询
* @param String hql hql语句
* @param Map<String,Object> keyVal 映射对象
* @param int pageStart 起始位置
* @param int pageSize 分页长度
* @return List<Object> list 实例Object List
* */
public List<Object> hqlQuery(String hql,Map<String,Object> keyVal,int pageStart,int pageSize);
/**
* 新增数据
* @param Object obj 新增数据
* @return boolean bol 是否新增成功
* */
public boolean insert(Object obj);
/**
* 新增数据
* @param List<Object> list 新增数据List
* @return boolean bol 是否新增成功
* */
public boolean insert(List<Object> list);
/**
* 删除指定数据
* @param Object obj 删除数据
* @return boolean bol 是否删除成功
* */
public boolean delete(Object obj);
/**
* 删除指定数据
* @param List<Object> list 数据删除List
* @return boolean bol 是否删除成功
* */
public boolean delete(List<Object> list);
/**
* 更新数据
* @param Object obj 需要更新的数据
* @return boolean bol 是否更新成功
* */
public boolean update(Object obj);
/**
* 更新数据
* @param List<Object> list 需要更新的数据List
* @return boolean bol 是否更新成功
* */
public boolean update(List<Object> list);
/**
* get Connection
* */
public Connection getConnection();
/**
* get Session
* */
public Session getSession();
/**
* get sessionFactory
* */
public SessionFactory getSessionFactory();
/**
* 原声sql查询 查询一条数据
* @param String sql 原生sql查询
* @return Object
* */
public Object sqlFindQuery(String sql);
/**
* 原声sql查询
* @param String sql 原生sql查询
* @param Class objClass 需要装载的数据对象class
* @return List<Object>
* */
public Object sqlFindQuery(String sql,Class objClass);
/**
* 原声sql查询 查询一条数据
* @param String sql 原生sql查询
* @param Class objClass 需要装载的数据对象class
* @param Map<String,Object> keyVal 映射关系对象
* @return List<Object>
* */
public Object sqlFindQuery(String sql,Class objClass,Map<String,Object> keyVal);
/**
* 原声sql查询 查询list 数据集合
* @param String sql 原生sql查询
* @return List<Object>
* */
public List<Object> sqlQuery(String sql);
/**
* 原声sql查询 查询list 数据集合
* @param String sql 原生sql查询
* @param Class objClass 需要装载的数据对象class
* @return List<Object>
* */
public List<Object> sqlQuery(String sql,Class objClass);
/**
* 原声sql查询 查询list 数据集合
* @param String sql 原生sql查询
* @param Class objClass 需要装载的数据对象class
* @param Map<String,Object> keyVal 映射关系对象
* @return List<Object>
* */
public List<Object> sqlQuery(String sql,Class objClass,Map<String,Object> keyVal);
/**
* 关闭该session
* */
public void closeSession();
}
对 HibernateService 接口的实现类
package spring.beans.db.hibernate;
import java.sql.Connection;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import javax.annotation.PreDestroy;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;
/**
* 数据库操作服务
* */
@Component("hibernate_db_component")
@Scope("request")
public class HibernateRealization implements HibernateComponent
{
protected SessionFactory sessionFactory;
protected Session session;
public void setSession(Session session)
{
this.session = session;
}
@Override
public Session getSession()
{
return session;
}
@Value(value="#{sessionFactory}")
public void setSessionFactory(SessionFactory sessionFactory)
{
this.sessionFactory = sessionFactory;
this.setSession(sessionFactory.openSession());
}
@Override
public SessionFactory getSessionFactory()
{
return sessionFactory;
}
@Override
public boolean insert(Object obj)
{
Transaction trans = null;
boolean executeClient = true;
try{
trans = session.beginTransaction();
session.save(obj);
trans.commit();
}catch(Exception e)
{
executeClient = false;
if(trans != null)trans.rollback();
}
return executeClient;
}
@Override
public boolean insert(List<Object> list)
{
Transaction trans = null;
boolean executeClient = true;
try{
trans = session.beginTransaction();
for(Object obj : list)
{
session.save(obj);
}
trans.commit();
}catch(Exception e)
{
executeClient = false;
if(trans != null)trans.rollback();
}
return executeClient;
}
@Override
public boolean delete(Object obj)
{
Transaction trans = null;
boolean executeClient = true;
try{
trans = session.beginTransaction();
session.delete(obj);
trans.commit();
}catch(Exception e)
{
executeClient = false;
if(trans != null)trans.rollback();
}
return executeClient;
}
@Override
public boolean delete(List<Object> list)
{
Transaction trans = null;
boolean executeClient = true;
try{
trans = session.beginTransaction();
for(Object obj : list)
{
session.delete(obj);
}
trans.commit();
}catch(Exception e)
{
executeClient = false;
if(trans != null)trans.rollback();
}
return executeClient;
}
@Override
public boolean update(Object obj)
{
Transaction trans = null;
boolean executeClient = true;
try{
trans = session.beginTransaction();
session.update(obj);
trans.commit();
}catch(Exception e)
{
executeClient = false;
if(trans != null)trans.rollback();
}
return executeClient;
}
@Override
public boolean update(List<Object> list)
{
Transaction trans = null;
boolean executeClient = true;
try{
trans = session.beginTransaction();
for(Object obj : list)
{
session.update(obj);
}
trans.commit();
}catch(Exception e)
{
executeClient = false;
if(trans != null)trans.rollback();
}
return executeClient;
}
@Override
public Connection getConnection()
{
WorkConnection work = new WorkConnection();
session.doWork(work);
return work.getConnection();
}
/**
* 对象注销
* */
@PreDestroy
public void closeDb()
{
closeSession();
}
@Override
public Object hqlFind(String hql)
{
return hqlFind(hql,null);
}
@Override
public Object hqlFind(String hql, Map<String, Object> keyVal)
{
Transaction trans = null;
Object result;
try{
trans = session.beginTransaction();
Query query = session.createQuery(hql);
if(keyVal != null && !keyVal.isEmpty())
{
Set<String> keySet = keyVal.keySet();
Iterator<String> iter = keySet.iterator();
while(iter.hasNext())
{
String key = iter.next();
query.setParameter(key, keyVal.get(key));
}
}
result = query.uniqueResult();
trans.commit();
}catch(Exception e)
{
result = null;
if(trans != null)trans.rollback();
}
return result;
}
@Override
public List<Object> hqlQuery(String hql)
{
return hqlQuery(hql,null);
}
@Override
public List<Object> hqlQuery(String hql, Map<String, Object> keyVal)
{
return hqlQuery(hql,keyVal,-1,0);
}
@Override
public List<Object> hqlQuery(String hql, Map<String, Object> keyVal,int pageStart, int pageSize) {
Transaction trans = null;
List<Object> list;
try{
trans = session.beginTransaction();
Query query = session.createQuery(hql);
if(keyVal != null && !keyVal.isEmpty())
{
Set<String> keySet = keyVal.keySet();
Iterator<String> iter = keySet.iterator();
while(iter.hasNext())
{
String key = iter.next();
query.setParameter(key, keyVal.get(key));
}
}
if(pageStart != -1 && pageSize > 0)
{
query.setFirstResult(pageStart);
query.setMaxResults(pageSize);
}
list = query.list();
trans.commit();
}catch(Exception e)
{
list = null;
if(trans != null)trans.rollback();
}
return list;
}
@Override
public int hqlExecute(String hql)
{
return hqlExecute(hql,null);
}
@Override
public int hqlExecute(String hql,Map<String,Object> keyVal)
{
Transaction trans = null;
int resultInt = 0;
try{
trans = session.beginTransaction();
Query hqlQuery = session.createQuery(hql);
if(keyVal != null && !keyVal.isEmpty())
{
Set<String> keySet = keyVal.keySet();
Iterator<String> iter = keySet.iterator();
while(iter.hasNext())
{
String key = iter.next();
hqlQuery.setParameter(key, keyVal.get(key));
}
}
resultInt = hqlQuery.executeUpdate();
trans.commit();
}catch(Exception e)
{
if(trans != null) trans.rollback();
}
return resultInt;
}
@Override
public List<Object> sqlQuery(String sql)
{
return sqlQuery(sql,null,null);
}
@Override
public List<Object> sqlQuery(String sql,Class objClass)
{
return sqlQuery(sql,objClass,null);
}
@Override
public List<Object> sqlQuery(String sql,Class objClass,Map<String,Object> keyVal)
{
Transaction trans = null;
List<Object> list = null;
try{
trans = session.beginTransaction();
SQLQuery sqlQuery = session.createSQLQuery(sql);
if(objClass != null)
{
sqlQuery.addEntity(objClass);
}
if(keyVal != null && !keyVal.isEmpty())
{
Set<String> keySet = keyVal.keySet();
Iterator<String> iter = keySet.iterator();
while(iter.hasNext())
{
String key = iter.next();
sqlQuery.setParameter(key, keyVal.get(key));
}
}
list = sqlQuery.list();
trans.commit();
}catch(Exception e)
{
if(trans != null) trans.rollback();
}
return list;
}
@Override
public Object sqlFindQuery(String sql)
{
return sqlFindQuery(sql,null,null);
}
@Override
public Object sqlFindQuery(String sql,Class objClass)
{
return sqlFindQuery(sql,objClass,null);
}
@Override
public Object sqlFindQuery(String sql,Class objClass,Map<String,Object> keyVal)
{
Transaction trans = null;
Object Obj = null;
try{
trans = session.beginTransaction();
SQLQuery sqlQuery = session.createSQLQuery(sql);
if(objClass != null)
{
sqlQuery.addEntity(objClass);
}
if(keyVal != null && !keyVal.isEmpty())
{
Set<String> keySet = keyVal.keySet();
Iterator<String> iter = keySet.iterator();
while(iter.hasNext())
{
String key = iter.next();
sqlQuery.setParameter(key, keyVal.get(key));
}
}
Obj = sqlQuery.uniqueResult();
trans.commit();
}catch(Exception e)
{
if(trans != null) trans.rollback();
}
return Obj;
}
@Override
public void closeSession()
{
session.close();
}
}
再简明说明一下 CustomDbService 和 HibernateService 之间的关系
CustomDbService 依赖 HibernateService。
为什么?
因为CustomDbService 中的数据库连接 是从HibernateService 中获得的(getConnection())
然后我们就可以调用从DbService的实例中去分别调用不同的数据库操作实现了。
当然 DbService 的实例 得通过@Resource(name=”db_service”) 或者是 @Value(value=”#{db_service}”) 获得哦。