数据库查询方法汇集(笔记)

Hibernate：首先，在配置文件hibernate.cfg.xml中配置数据源其次,在HibernateSessionFactory中加载配置文件（数据源）初始化并创建session 用持久化对象hibernate时从创建Session session开始：Session session = sessionfactory.openSession();有session后可创建Criteria、Query、SQLQuery对象操作数据库Spring：首先，在spring.xml中配置sessionFactory数据源以及mapping等资源其次，在dao类的配置中需要注入sessionFactory，继承HibernateDaoSupport类然后，利用HibernateTemplate模板类进行操作数据库如果用到分页查询，需要创建获得session对象return (List) getHibernateTemplate().execute(new HibernateCallback() {public Object doInHibernate(Session session) throws HibernateException {Query query = session.createQuery(queryString);query.setProperties(args);return query.list();}});如果用纯SQL，可用sping的JdbcTemplate的模板方法进行操作数据库，需要在dao 类中注入dataSource，并继承JdbcTemplate如下：private JdbcTemplate jdbcTemplate;private DataSource dataSource;public JdbcTemplate getJdbcTemplate() {if (jdbcTemplate == null) {jdbcTemplate = new JdbcTemplate(dataSource);}return jdbcTemplate;}public void setDataSource(DataSource dataSource) {this.dataSource = dataSource;}1.创建一个条件面向对象的查询对象CriteriaCriteria : 代表一次查询Criterion：代表一个查询条件Restrictions：代表查询条件的工具类。

步骤：1 获取Hibernate的Session对象2.已Session对象创建Criteria对象3.使用Restrictions的静态方法创建Criterion4.向Criteria查询中添加查询条件5.执行Criteria的List方法返回查询结果Criteria criteria = session.createCriteria(Class persistentClass) criteria.add(Expression.eq("name","zgl"));备注：条件表达式：Expression 和Restrictions关系–继承ng.Objectorg.hibernate.criterion.Restrictionsorg.hibernate.criterion.Expression对象：DetachedCriteria为了查询条件和Session分离而重复使用查询条件，使用DetachedCriteria对象DetachedCriteria detachedCriteria =DetachedCriteria.forClass(MyTest.class);detachedCriteria.add(Expression.eq("name", "zgl"));detachedCriteria.addOrder(Order.asc("age"));当查询时再和session绑定：Criteria c = detachedCriteria.getExecutableCriteria(session);Iterator it = c.list().iterator();类Projections：分组、统计2. 创建HQL语句查询对象Querypublic Query createQuery(String queryString)throws HibernateException S tring hql = "from com.Ts_User";Query query = session.createQuery(hql);List querylist = query.list();【提示】HQL语句中对象路径写全路径类名，并区分大小写A属性查询：查询结果只需要对象的某个属性如：只需name 和age 两个属性，则用查询结果为Object[]数组String HQL = “select name , age from Ts_User ”;B：提供更新语句String HQL = “update Ts_User set age=18 ”;Query query = session.createQuery(hql);Int ret = query.executeUpdate();C:删除一样String HQL =”delete Ts_User where age=18”D：参数查询用到参数尽量用?占位符表示，并query.setProperties(Object bena)List<Object> args = new ArrayList<Object>();args.add(propName);args.add(distCode);List<PropertiesConfig> list = find("from com.sf.module.sysmgr.domain.PropertiesConfig where propName=? and distCode=?", args);联合查询：1.inner join2.left outer join3.right outer join4.full join如： String HQL = “from ts_user user inner join fetch user.address”其中fethc 必不好少，否则返回为包括 user 和 address对象全部属性3.创建SQL语句查询对象Querypublic SQLQuery createSQLQuery(String queryString)throws HibernateException原SQL 查询：1.获取Hibernate Session对象2.编写SQL语句3.以SQL语句作为参数，调用Session的createSQLQuery方法创建查询对象。

4.调用SQLQuery对象的addScalar()或者addEntity()方法将选出来的结果与标量值或实体进行关联。

5.如果SQL语句保护参数，则调用Query的setXx方法赋值。

6.调用Query的list方法返回查询结果集。

原生SQL语句1. 标量查询（object对象）【说明】：SQLQuery是Query的子接口public Object doInHibernate(Session session)throws HibernateException, SQLException {SQLQuery sqlQuery = session.createSQLQuery("select * from ts_module connect by parent_id = prior module_id start with module_code = ?");sqlQuery.setString(0, code);sqlQuery.addScalar(“name”,Hibernate.INTEGER);return sqlQuery.list();}});}实体查询：（实体对象）在分页查询时，会用到1.setMaxResults –每次查询的最大行数2.setFirstResult 从多少行开始返回结果public List<Module> getModulesByCode(final String code) { return (List)getHibernateTemplate().execute(newHibernateCallback() {public Object doInHibernate(Session session)throws HibernateException, SQLException {SQLQuery sqlQuery = session.createSQLQuery("select * from ts_module connect by parent_id = prior module_id start with module_code = ?");sqlQuery.setString(0, code);sqlQuery.addEntity(Module.class);return sqlQuery.list();}});}JDBC查询：在用JDBC查询之前需要连接数据源语句：JdbcTemplate中的封装了很多实用的方法。

private JdbcTemplate jdbcTemplate;private DataSource dataSource;public JdbcTemplate getJdbcTemplate() {if (jdbcTemplate == null) {jdbcTemplate = new JdbcTemplate(dataSource);}return jdbcTemplate;}首先来介绍一下用JdbcTemplate来查询数据的方法。

1. 返回指定对象类型的List(query方法)public List getProductModle(String value) throws DataAccessException {List resultList = new ArrayList();List args = new ArrayList();String sql = "select F_CPUT_MOD_ID as id ,F_CPUT_MOD_NAME as name from CPUT_MOD_T WHERE F_MARKET_LOCA_ID = ? order by F_CPUT_MOD_ID";if(StringUtils.isNotEmpty(value)){args.add(value);}resultList =this.getJdbcTemplate().query(sql,args.toArray(),new ProductModleRowMapper());return resultList;}//封装产品型号结果集private class ProductModleRowMapper implements RowMapper{ public Object mapRow(final ResultSet rs, final int rowNum) throws SQLException {ProductModleVO vo = new ProductModleVO();vo.setProModleId((String)rs.getString("id"));vo.setProModleName((String)rs.getString("name"));return vo;}}}2.返回指定类型的结果(queryForObject方法)public String getCurrentPhase(String arg) throwsDataAccessException{String currentPhase = "";String sql="select F_CURR_YEAR from COMPETITION_BASE_T whereF_COMPETITION_ID=?";Object[] o ={arg};try {currentPhase =(String)this.getJdbcTemplat().queryForObject(sql,o, String.class);} catch (Exception e) {currentPhase = "";e.printStackTrace();}return currentPhase;}3.查询结果返回Int类型(queryForInt方法)public int countUploadedReasonZone(String areano) {String sql = "select count(*) from mapareadept where areano=?";Object[] args = new Object[]{areano};return getJdbcTemplate().queryForInt(sql , args);}4.查询结果返回Map类型 (queryForMap方法)String sql= "selectT.F_COMPETE_ORDER_STATE ,T.F_GENERATED_ORDERS " +"from COMPETITION_BASE_T t " + "where T.F_COMPETITION_ID = ?";Map state = (Map)this.getJdbcTemplate().queryForMap(sql,newObject[]{args});String compete_order_state =state.get("F_COMPETE_ORDER_STATE").toString();String generated_orders = state.get("F_GENERATED_ORDERS").toString();5.查询结果集直接返回list (queryForList方法)List list = new ArrayList();String sql="select T.F_OFF_PERIOD,T.F_PROBABILITY fromORDER_OFF_PERIOD_T t "+"where T.F_COMPETITION_ID=?";Object[] args ={arg};try {list = this.getJdbcTemplate().queryForList(sql, args);} catch (Exception e) {e.printStackTrace();}--------------------------------------------不带参数----------------------------------------------------public List<AreaManagerMail> getManagerRoleArea() {String sql = "select ername,min(a.email_addr) email,min(a.regioncode) regioncode,d.dist_code from TS_USER_NOS a,ts_user_role b,TS_USER_DEPT c,tm_department d where er_id=er_id and b.role_id=6 and er_id=er_id and c.dept_id=d.dept_id group by d.dist_code,ername order by username";List<Map<String, Object>> results = (List<Map<String, Object>>) getJdbcTemplate() .queryForList(sql);if (results == null || results.size() == 0)return null;List<AreaManagerMail> list = new ArrayList<AreaManagerMail>(results.size());for (Map<String, Object> row : results) {AreaManagerMail mail = new AreaManagerMail();mail.setUsername((String) row.get("username"));mail.setEmail((String) row.get("email"));mail.setRegioncode((String) row.get("regioncode"));mail.setDistCode((String) row.get("dist_code"));list.add(mail);}return list;}-----------------------------------------------带参数--------------------------------------------------------public long getCountByDistCode(String distCode){String sql = "select count(0) from ts_user_nos t,tm_user_address t2 where t.cust_code=? and er_id = er_id";if (SystemConstant.SG_DIST_CODE.equals(distCode)){sql += " and t2.country_code=?";}else{sql += " and t2.city_code=?";}return getJdbcTemplate ().queryForLong(sql,new Object[] { SystemConstant.NON_ACCREDITED_CUST_CODE, distCode }, new int[] { Types.V ARCHAR, Types.V ARCHAR });}public List<E> find(final String queryString, final Map<String, Object> args) throws DaoException {return (List<E>) getHibernateTemplate().execute(new HibernateCallback() {public Object doInHibernate(Session session) throws HibernateException {Query query = session.createQuery(queryString);query.setProperties(args);return query.list();}}, true);}Hibernate HQL查询插入更新（update）实例Student 是一个对象,student 是数据库中的一个表.查询所有的Student对象时,最简单的HQL语句是: from Student,也可以写成 select s from Student (as)s. 注:这的as可以省略1:简单的查询遍历对象:遍历StudentQuery query=session.createQuery("form Student"); //注: 如果Student对象不是唯一的,那么需要写上包名,如: from test.Student test为包名.List list=query.list();for(int i=0;i<list.size();i++){Student stu=(Student)list.get(i);System.out.println(stu.getName());}注意: 如果执行HQL语句"from Student,Course",并不时单单返回两个对象,而是返回两个对象的笛卡尔积,这类似SQL语句中字段的全外连接.实际的应用中,"from Student,Course"这种语句几乎是不回出现的.2:属性查询:----单个属性查询:Query query=session.createQuery("select form Student s");List list=query.list();for(int i=0;i<list.size();i++){String name=(String)list.get(i);System.out.println(name);}----多个属性查询:Query query=session.createQuery("select ,s.age form Student s");List list=query.list();for(int i=0;i<list.size();i++){Object obj[]=(Object[])list.get(i); //取得list中的第i个对象System.out.println(obj[0]+"的年龄为: "+obj[1]);}3:实例化查询:实例化查询结果可以说是对属性查询的一重改进.在使用属性查询时由于使用对象数组,操作和理解不太方便,如果将以个Object[]中的成员封装成一个对象就方便多了.Query query=session.createQuery("select new Student(,s.age) form Student s"); List list=query.list();for(int i=0;i<list.size();i++){Student stu=(Student)list.get(i);System.out.println(stu.getName());}注:运行这个程序的时候,需要一个new Student(,s.age)构造函数.在Student.java 中编写这个构造函数.public Student(String name,int age){=name;this.age=age;}4:查询链接:与SQL查询一样,HQL也支持连接查询,如内连接,外连接和交叉连接.支持的链接类型是从ANSI SQL中借鉴来的.1: inner jion (内连接)2: left outer join (左外连接)3: right outer join(右外连接)4: full join(全连接--不常用)inner jion 可以简写为join.正常情况下必须要建关联。