Hibernate 中 执行 原生 SQL 语句

用hibernate的executeQuery来执行SQL

其原理如下(从SessionFactory里获得个Session，在调用session的connection方法,通过Statement来执行静态SQL,最后执行executeQuery就可以了)具体如下:

protected Session session = null; protected Transaction tr = null;

String sql = "insert into as_dept2role(roleid,dept_id)value('"+roleId+"','"+deptId+"')"; session=HibernateSessionFactory.getSession();

session.beginTransaction();
//获取connection,执行静态SQL
Statement state = session.connection().createStatement();
state.executeQuery(sql);
tr.commit(); session.close();

当然关于 关闭SESSION 这些方法我写的简单些,主要是为了写 执行SQL这些方法

对于删除只要写个删除语句就可以了

:Transaction tr = session.beginTransaction();

* session.connection() 方法过时 用下面来代替 *
DataSource ds= SessionFactoryUtils.getDataSource(getSessionFactory());
conn=ds.getConnection();

============================================

（二）

public Object get(Class cls, String szId) {
Object obj = this.getHibernateTemplate().get(cls, szId);
return obj;
}
obj.getsessionFaction.opensession返回session


Session session = dao.openSession();
Connection conn = session.connection();
List recordList = new ArrayList();

StringBuffer sql = new StringBuffer();
sql.append("select b.user_name, c.org_name ");
sql.append("from orgmeetinglinkman a, am_user b, organization c ");
sql.append("where a.login_name = b.login_name ");
sql.append("and a.org_id = c.org_id ");

PreparedStatement ps = conn.prepareStatement(sql.toString());
ResultSet rs = ps.execu

============================================

(三)

public List findWithSQL(final String sql) {
List list = (List) this.getHibernateTemplate().execute(
new HibernateCallback() {
public Object doInHibernate(Session session)
throws SQLException, HibernateException {
SQLQuery query = session.createSQLQuery(sql);
query.addScalar("NX",new org.hibernate.type.StringType());
List children = query.list();
return children;
}
});
return list;
}

/**
* 查询并返回结果集,结果集中的内容已经都转为了字符串
*/
public List<List<String>> findSql(final String sql) {
// TODO Auto-generated method stub
System.out.println("findSql---sql1----->"+sql);
List<List<String>> mainObjList= (List<List<String>>) getHibernateTemplate().execute(new HibernateCallback() {

public Object doInHibernate(Session session)
throws HibernateException, SQLException {


int n=-1;
Connection con=null;
PreparedStatement stmt=null;
ResultSet rs=null;

try
{

DataSource ds= SessionFactoryUtils.getDataSource(getSessionFactory());
if(ds==null)
{
throw new SQLException("get dataSource is null");
}
con=ds.getConnection();
System.out.println("findSql---sql2----->"+sql);
stmt=con.prepareStatement(sql);
rs=stmt.executeQuery();

} catch (HibernateException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}

List<List<String>> list=new ArrayList<List<String>>();//每行为一个list
try
{
ResultSetMetaData rsmd=rs.getMetaData();
int colsNum=rsmd.getColumnCount();//取得列数
int rsNum=0;
while(rs.next())
{
rsNum++;
System.out.println("rsNum==="+rsNum+" ");
List<String> subList=new ArrayList<String> ();//每列的类型为string
for(int i=1;i<=colsNum;i++)
{
System.out.println("\ti==="+i);
//int type= rsmd.getColumnType(i);
String columnType=getDataType(rsmd.getColumnType(i),rsmd.getScale(i));
String val="";
if(columnType.equalsIgnoreCase("Date"))
{
Timestamp timest= rs.getTimestamp(i);
if(timest!=null)
{
long times=timest.getTime();
Date date=new Date(times);
SimpleDateFormat df=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss",Locale.CHINA);
val=df.format(date);
}
}
else if(columnType.equalsIgnoreCase("Number"))
{
Object obj=rs.getObject(i);
if(obj!=null)
{
int m=rs.getInt(i);
val=Integer.toString(m);
}
}
else if(columnType.equalsIgnoreCase("blob"))
{
val="不支持blob数据的读取";
}
else if(columnType.equalsIgnoreCase("clob"))
{
val=getOracleClobField(rs, i);

}
else
{
val=rs.getString(i);
}
if(val==null)
{
val="";
}
subList.add(val);
}
if(subList.size()>0)
{
list.add(subList);
}
}
}
catch(Exception ex5)
{
ex5.printStackTrace();
System.out.println("ex5.getMessage="+ex5.getMessage());
list=null;
}
finally
{
try {
if (rs != null) {
rs.close();
rs = null;
}
} catch (Exception e2) {
// TODO: handle exception
e2.printStackTrace();
System.out.println("e2.getMessage="+e2.getMessage());
}
try {
if (stmt != null) {
stmt.close();
stmt = null;
}
} catch (Exception e3) {
// TODO: handle exception
e3.printStackTrace();
System.out.println("e3.getMessage="+e3.getMessage());
}
try {
if (con != null) {
con.close();
con = null;
}
} catch (Exception e4) {
// TODO: handle exception
e4.printStackTrace();
System.out.println("e4.getMessage="+e4.getMessage());
}

}
return list;
}

});
return mainObjList;
}

// String columnType=getDataType(rmd.getColumnType(i),rmd.getScale(i));
private String getOracleClobField(ResultSet rset, int index)
throws Exception
{
StringBuffer buffS = new StringBuffer();
Clob clob = rset.getClob(index + 1);
if(clob == null)
return " ";
Reader reader = clob.getCharacterStream();
char buff[] = new char[1024];
for(int len = 0; (len = reader.read(buff)) != -1;)
buffS.append(buff, 0, len);
return buffS.toString();
}

private static String getDataType(int type,int scale)
{
String dataType="";

switch(type){
case Types.LONGVARCHAR: //-1
dataType="Long";
break;
case Types.CHAR: //1
dataType="Character";
break;
case Types.NUMERIC: //2
switch(scale)
{
case 0:
dataType="Number";
break;
case -127:
dataType="Float";
break;
default:
dataType="Number";
}
break;
case Types.VARCHAR: //12
dataType="String";
break;
case Types.DATE: //91
dataType="Date";
break;
case Types.TIMESTAMP: //93
dataType="Date";
break;
case Types.BLOB :
dataType="Blob";
break;
default:
dataType="String";
}
return dataType;
}