Hibernate 执行原始SQL语句

在hibernate中有时不需要用到表的映射，需要直接执行SQL语句。

其中sessionFactory在配置文件中配置，SSH详细配置见http://blog.csdn.net/xumengxing/article/details/8586012

在dao层中定义两个常见函数用于查询和执行操作。

dao代码如下：

package com.hsinghsu.testSSH.dao.impl;  
  
import java.util.List;  
  
import javax.annotation.Resource;  
  
import org.hibernate.SQLQuery;  
import org.hibernate.Session;  
import org.hibernate.SessionFactory;  
import org.springframework.stereotype.Repository;  
  
import com.hsinghsu.testSSH.dao.BaseDao;  
  
@Repository(value = "baseDao")  
public class BaseDaoImpl implements BaseDao {  
  
    @Resource(name = "sessionFactory")  
    private SessionFactory sessionFactory;  
  
    public Session getSession() {  
        return sessionFactory.getCurrentSession();  
    }  
  
    public List queryBySql(String sql) {  
        List<Object[]> list = getSession().createSQLQuery(sql).list();  
        return list;  
    }  
      
    public int excuteBySql(String sql)  
    {  
        int result ;  
        SQLQuery query = this.getSession().createSQLQuery(sql);  
        result = query.executeUpdate();  
        return result;  
    }  
  
}  

service代码如下：

package com.hsinghsu.testSSH.service.impl;  
  
import java.util.List;  
  
import org.springframework.beans.factory.annotation.Autowired;  
import org.springframework.stereotype.Service;  
  
import com.hsinghsu.testSSH.dao.BaseDao;  
import com.hsinghsu.testSSH.service.BaseService;  
  
@Service(value = "baseService")  
public class BaseServiceImpl implements BaseService {  
    @Autowired  
    private BaseDao baseDao;  
  
    public List queryBySql(String sql) {  
        List<Object[]> list = baseDao.queryBySql(sql);  
        return list;  
    }  
      
    public int excuteBySql(String sql){  
        return baseDao.excuteBySql(sql);  
    }  
}  

测试代码如下：

package com.hsinghsu.testSSH.service;  
  
import static org.junit.Assert.assertNotNull;  
import static org.junit.Assert.assertTrue;  
  
import java.util.List;  
  
import org.junit.Test;  
import org.springframework.beans.factory.annotation.Autowired;  
  
import com.hsinghsu.testSSH.SpringTestBase;  
  
public class BaseServiceTest  extends SpringTestBase{  
    @Autowired  
    private BaseService baseService;  
      
    @Test  
    public void testConfig() {  
         assertNotNull(baseService);  
         assertTrue(baseService != null);  
    }  
      
    @Test  
    public void testQueryBySql()  
    {  
        String sql = "select * from T_TEST_USER WHERE SEX = 1";  
        List<Object[]> list = baseService.queryBySql(sql);  
        System.out.println("---"+list.size());  
        for(Object[] obj :list)  
        {  
            System.out.println(obj[0]+" -- "+ obj[1]+" -- "+obj[2]);  
        }  
    }  
      
    @Test  
    public void testExcuteBySql()  
    {  
//      String sql = "UPDATE T_TEST_USER SET NAME = 'GSS09', PWD = 'GG007', AGE = 24, SEX = 1 WHERE ID = 6";  
//      String sql = "UPDATE T_TEST_USER SET NAME = 'GSS29', PWD = 'GG007' WHERE ID = 6";  
//      String sql = "insert into T_TEST_USER(ID,NAME,PWD,AGE,SEX) values(7,'gogo08','gogopwd08',24,1)";  
        String sql = "delete from T_TEST_USER where ID = 7";  
  
        int result = baseService.excuteBySql(sql);  
        System.out.println("---009:"+result);  
    }  
         
}