maom919
2009-06-02 09:09 阅读 278
已采纳

Hibernate多表查询的问题

用Hibernate,在数据库表设计的时候不设计表之间外键关系,所以在hbm文件里也不存在one-to-many之类的配置信息,现在要进行多表查询,比如student表和course表,进行查询:select student.sno,course.coursename from sutdent,course where student.courseid=course.id;使用List接受返回类型。我现在需要开发webservice,故在List后要加泛型(List ),否则无法发布。请问这个泛型应该是什么?是不是需要一个student类和course类的中间类?这个中间类是否可以动态生成?(需要查的时候动态创建,查询完毕后动态销毁)。
[b]问题补充:[/b]
请问xiaoyu966,能否加一些注释?我是个初学者,不太能看懂。谢谢了!

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

6条回答 默认 最新

  • 已采纳
    iteye_17762 iteye_17762 2009-06-02 11:56

    我把整个BaseDaoImpl 发上来了,JdbcTemplate jt;是用spring的set注入方式注入的,你也不用改什么东西了。
    Spring配置文件中,加入以下代码



    <bean id="jdbcTemplete"
        class="org.springframework.jdbc.core.JdbcTemplate">
        <constructor-arg ref="dataSource"></constructor-arg>
    </bean>
    

    下面的代码中,动态Bean部分的注释我没有加,你自己去网上搜索一下,有很多解释的

    import java.io.Serializable;
    import java.lang.reflect.InvocationTargetException;
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.ResultSetMetaData;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    import java.util.List;

    import org.apache.commons.beanutils.BasicDynaClass;
    import org.apache.commons.beanutils.DynaBean;
    import org.apache.commons.beanutils.DynaClass;
    import org.apache.commons.beanutils.DynaProperty;
    import org.apache.commons.beanutils.PropertyUtils;
    import org.hibernate.Session;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.orm.hibernate3.HibernateTemplate;
    import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

    import com.global.dao.BaseDao;
    import com.global.db.HibernateSessionFactory;

    public class BaseDaoImpl extends HibernateDaoSupport implements BaseDao {

    private JdbcTemplate jt;
    
    public JdbcTemplate getJt() {
        return jt;
    }
    
    public void setJt(JdbcTemplate jt) {
        this.jt = jt;
    }
    
    public void delete(Object o) {
        super.getHibernateTemplate().delete(o);
    
    }
    
    public Object get(Class cls, Serializable id) {
        return  super.getHibernateTemplate().get(cls, id);
    }
    
    public List list(String hql) {
        return super.getHibernateTemplate().find(hql);
    }
    
    
    public void save(Object o) {
        super.getHibernateTemplate().save(o);
    
    }
    
    public void update(Object o) {
        super.getHibernateTemplate().update(o);
    
    }   
    

    //JdbcTemplate
    public List listArraryByJdbc(String sql, Object[] objs) {
    List list = jt.queryForList(sql, objs);
    return list;
    }

    public List listByJdbc(String sql) {
        List list = jt.queryForList(sql);
        return list;
    }
    
    public int getInt(String sql) {
        int count =jt.queryForInt(sql);
        return count;
    }
    
    public int getInt(String sql, Object[] objs) {
        int count =jt.queryForInt(sql,objs);
        return count;
    }
    
    
    /*用来执行用户自定义select语句,一般用于多表查询时,查询的字段为动态
     * 要用到动态Bean
     * 
     * */
    public List listForJdbcCustom(String sql){
    
        Connection con = null;
        Statement stm = null;
        ResultSet rs = null;
        //结果集元数据,即select * from... 指的是*中的的字段
        ResultSetMetaData rsmd = null;
        List list = new ArrayList();
    
        int columns = 0;
        try {
            //获取Connection
            con = jt.getDataSource().getConnection();
    
            if (con == null)
                return null;
            //获取statement
            stm = con.createStatement();
            if (stm == null)
                return null;
            //执行查询语句
            rs = stm.executeQuery(sql);
            if (rs == null)
                return null;
            //通过结果集,初始化结果集元数据集合
            rsmd = rs.getMetaData();
            //获取列数,即select * from... 指的是*中的的字段数目
            columns = rsmd.getColumnCount();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
        //初始化一个动态属性数组
        DynaProperty[] px = new DynaProperty[columns];
        String prop[] = new String[columns];
    
        for (int i = 0; i < columns; i++) {
            try {
                prop[i] = rsmd.getColumnLabel(i + 1);
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            try {
                px[i] = new DynaProperty(prop[i], Class
                        .forName("java.lang.String"));
    
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
                return null;
            }
        }
        //之后的就是关于动态Bean的知识了,你可以去网上搜索DynaBean或者DynaClass去学习一下相关知识
        DynaClass dynaClass = null;
    
    
        DynaBean dynaBean;
    
        try {
            while (rs.next()) {
    
                dynaClass = new BasicDynaClass("dynaBean", null, px);
    
                try {
                    dynaBean = dynaClass.newInstance();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                    return null;
                } catch (InstantiationException e) {
                    e.printStackTrace();
                    return null;
                }
    
                for (int i = 1; i <= columns; i++) {
                    Object temp = rs.getObject(i);
    
                    if (temp == null)
                        temp = "";
                    else
                        temp = rs.getObject(i).toString();
                    try {
                        PropertyUtils.setProperty(dynaBean, prop[i - 1], temp);
                        // test
                        //System.out.println("prop = " + prop[i - 1]);
                        // test
                        //System.out.println("value = " + temp);
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                        return null;
                    } catch (InvocationTargetException e) {
                        e.printStackTrace();
                        return null;
                    } catch (NoSuchMethodException e) {
                        e.printStackTrace();
                        return null;
                    }
                }
                list.add(dynaBean);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (con != null)
                    con.close();
                if (stm != null)
                    stm.close();
                if (rs != null)
                    rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
    
        }
        return list;    
    }
    

    }

    点赞 评论 复制链接分享
  • iteye_17762 iteye_17762 2009-06-02 09:44

    设计到多表的问题,泛型可以使用Object顶级父类,我给你发一个动态Bean的方法,相当好用,不用再写什么特定的student类和course类的中间类,以后只要遇到多表查询的问题,都可以用这个动态Bean搞定

    public List listForJdbcCustom(String sql){

        Connection con = null;
        Statement stm = null;
        ResultSet rs = null;
        ResultSetMetaData rsmd = null;
        List list = new ArrayList();
    
        int columns = 0;
        try {
            con = jt.getDataSource().getConnection();
    
            if (con == null)
                return null;
            stm = con.createStatement();
            if (stm == null)
                return null;
            rs = stm.executeQuery(sql);
            if (rs == null)
                return null;
    
            rsmd = rs.getMetaData();
            columns = rsmd.getColumnCount();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
        DynaProperty[] px = new DynaProperty[columns];
        String prop[] = new String[columns];
    
        for (int i = 0; i < columns; i++) {
            try {
                prop[i] = rsmd.getColumnLabel(i + 1);
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            try {
                px[i] = new DynaProperty(prop[i], Class
                        .forName("java.lang.String"));
    
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
                return null;
            }
        }
    
        DynaClass dynaClass = null;
    
        DynaBean dynaBean;
    
        try {
            while (rs.next()) {
    
                dynaClass = new BasicDynaClass("dynaBean", null, px);
    
                try {
                    dynaBean = dynaClass.newInstance();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                    return null;
                } catch (InstantiationException e) {
                    e.printStackTrace();
                    return null;
                }
    
                for (int i = 1; i <= columns; i++) {
                    Object temp = rs.getObject(i);
    
                    if (temp == null)
                        temp = "";
                    else
                        temp = rs.getObject(i).toString();
                    try {
                        PropertyUtils.setProperty(dynaBean, prop[i - 1], temp);
                        // test
                        //System.out.println("prop = " + prop[i - 1]);
                        // test
                        //System.out.println("value = " + temp);
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                        return null;
                    } catch (InvocationTargetException e) {
                        e.printStackTrace();
                        return null;
                    } catch (NoSuchMethodException e) {
                        e.printStackTrace();
                        return null;
                    }
                }
                list.add(dynaBean);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (con != null)
                    con.close();
                if (stm != null)
                    stm.close();
                if (rs != null)
                    rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
    
        }
        return list;
    
    }
    

    使用的时候,要把con,stmt初始化一下

    点赞 评论 复制链接分享
  • iteye_17762 iteye_17762 2009-06-02 09:49

    使用的时候,list中取出的对象也是
    DynaBean dynaBean = list.get(i);
    这里面连接数据库的方式是JDBC方式的,给你发一个例子,是通过hibernate获取jdbc连接的,你只要根据这个,把上面的con的获取方式一改就行了

    public double queryDeliverySpotTurnover(String fromTime, String toTime, int deliverySpotID) {
    String sql =" select " +
    " sum(totalprice) as total " +
    " from oder o " +
    " where createtime>= to_date('"+fromTime+"','YYYY-MM-DD') " +
    " and createtime<=to_date('"+toTime+"','YYYY-MM-DD') " +
    " and orderstateid = 4 " +
    " and deliveryspotid = " + deliverySpotID;
    Session session = HibernateSessionFactory.getSession();
    Connection conn = null;
    double total = 0;
    try {
    conn = session.connection();
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery(sql);

        if(rs.next()){
        total = rs.getDouble("total");
        }
        return total;
    
        } catch (Exception e) {
        e.printStackTrace();
        } finally {
        try {
        session.close();
        } catch (Exception e) {
        e.printStackTrace();
        }
        }
        return 0;
    }
    
    点赞 评论 复制链接分享
  • iampurse iampurse 2009-06-02 10:32

    ORMMapping ~
    Google一下就有答案了。

    要不直接用common包的BeanUtil.copyProperty应该也可以实现。

    点赞 评论 复制链接分享
  • this_king this_king 2009-06-02 12:35

    :?:

    点赞 评论 复制链接分享
  • hehexixi hehexixi 2009-06-02 16:23

    如果不想再建一个实体类,只需要select new Map(student.sno,course.coursename) from sutdent,course where student.courseid=course.id就OK了,返回List,
    不过得设置org.hibernate.hql.ast.ASTQueryTranslatorFactory

    点赞 评论 复制链接分享

相关推荐