Hibernate 执行原始SQL语句

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

其中sessionFactory在配置文件中配置，SSH详细配置见http://blog.****.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);
    }    

}