(一)
開門見山.今天我要說的是不用HQL執行SAVE和DELETE方法,用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;
}