Hibernate Named Query

使用Hibernate命名查询，是可以把查询sql写xml里面的。

[color=darkred]applicationContext.xml:[/color]

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
	   xmlns:p="http://www.springframework.org/schema/p"
	   xsi:schemaLocation="http://www.springframework.org/schema/beans 
	   					   http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">

	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver">
		</property>
		<property name="url" value="jdbc:mysql://localhost:3306/test"></property>
		<property name="username" value="root"></property>
		<property name="password" value="root"></property>
	</bean>

	<bean id="sessionFactory"
		class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
		<property name="dataSource">
			<ref bean="dataSource" />
		</property>
		<property name="hibernateProperties">
			<props>
				<prop key="hibernate.dialect">
					org.hibernate.dialect.MySQLDialect
				</prop>
			</props>
		</property>
		<property name="mappingDirectoryLocations">
			<value>classpath:config/hibernate/hbm/</value>
		</property>
	</bean>

	<bean id="userDao" class="com.my.dao.UserDao">
		<property name="sessionFactory" ref="sessionFactory"></property>
	</bean>

	<bean id="userService" class="com.my.service.UserService">
		<property name="userDao" ref="userDao"></property>
	</bean>
</beans>

[color=darkred]UserNamed.hbm.xml:[/color]

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
<hibernate-mapping>
	<sql-query name="queryByName">
		select 
			pk_id,f_name,f_createTime,f_email 
		from 
			t_hibernate_named 
		where 
			f_name = :name  
	</sql-query>

	<sql-query name="queryByStartDateToEndDate">
		select 
			pk_id,f_name,f_createTime,f_email
		from
			t_hibernate_named
		where
			f_createTime between :startDate and :endDate
	</sql-query>
</hibernate-mapping>

[color=darkred]一些日期处理：[/color]
[color=darkred]DateUtils.java:[/color]

package com.my.commons.time;

import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

import org.apache.commons.lang.time.DateFormatUtils;

public class DateUtils extends DateFormatUtils {
	// 得到24小时制的年月日时分秒
	public static final String YYYY_MM_DD_HH24_MM_SS = "yyyy-MM-dd HH:mm:ss";

	/**
	 * 将日期时间字符串转换为时间型
	 * 
	 * @param srcDateTime
	 *            待转化的日期时间字符串.
	 * 
	 * @return 转化后的日期时间.
	 */
	public static Date string2Date(String srcDateTime) {
		Date date = null;
		try {
			date = new SimpleDateFormat(YYYY_MM_DD_HH24_MM_SS)
					.parse(srcDateTime);
		} catch (ParseException e) {
			e.printStackTrace();
		}
		return date;
	}

	/**
	 * 将Date转换为Timestamp
	 * 
	 * @param date
	 * @return
	 */
	public static Timestamp getTimestamp(Date date) {
		return Timestamp.valueOf(format(date, YYYY_MM_DD_HH24_MM_SS));
	}

	/**
	 * 将Date转换为String
	 * 
	 * @param date
	 * @return
	 */
	public static String formatYMDH24MS(Date date) {
		return format(date, YYYY_MM_DD_HH24_MM_SS);
	}

	/**
	 * 将Timestamp转换为String
	 * 
	 * @param timestamp
	 * @return
	 */
	public static String formatYMDH24MS(Timestamp timestamp) {
		return format(timestamp, YYYY_MM_DD_HH24_MM_SS);
	}

	/**
	 * 根据 日期时间和显示模式得到String
	 * 
	 * @param timestamp
	 * @param pattern
	 * @return
	 */
	public static String format(Timestamp timestamp, String pattern) {
		return format(timestamp.getTime(), pattern);
	}
}

[color=darkred]TimeZone.java:[/color]

package com.my.commons.time;

import java.io.Serializable;
import java.util.Date;

public class TimeZone implements Serializable {
	private static final long serialVersionUID = 1L;

	// 起始日期
	private Date startDate;
	// 结束日期
	private Date endDate;

//省略get、set和构造方法。
}

[color=darkred]UserDao.java:[/color]

package com.my.dao;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.Query;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.my.commons.time.TimeZone;
import com.my.po.User;

@SuppressWarnings("unchecked")
public class UserDao extends HibernateDaoSupport {
	/**
	 * 根据名称查询
	 * 
	 * @param name
	 * @return
	 */
	public List queryByName(String name) {
         //"queryByName"就是UserNamed.hbm.xml里的name，
		Query queryNamed = getSession().getNamedQuery("queryByName");
         //"name"就是UserNamed.hbm.xml里name为queryByName的 :name 。
		queryNamed.setString("name", name);
		List result = queryNamed.list();

		List resultList = new ArrayList();
		User user = null;

		for (Object obj : result) {
			user = new User();
			Object[] o = (Object[]) obj;

			user.setId((Integer) o[0]);
			user.setName((String) o[1]);
			user.setCreateTime((Timestamp) o[2]);
			user.setEmail((String) o[3]);

			resultList.add(user);
		}
		return resultList;
	}
	/**
	 * 根据指定日期查询
	 * @param timeZone
	 * @return
	 */
	public List queryByStartDateToEndDate(TimeZone timeZone){
		Query queryNamed = getSession().getNamedQuery("queryByStartDateToEndDate");
		queryNamed.setTimestamp("startDate", timeZone.getStartDate());
		queryNamed.setTimestamp("endDate", timeZone.getEndDate());
		List result = queryNamed.list();

		List resultList = new ArrayList();
		User user = null;

		for(Object obj : result){
			user = new User();
			Object[] o = (Object[])obj;

			user.setId((Integer)o[0]);
			user.setName((String)o[1]);
			user.setCreateTime((Timestamp)o[2]);
			user.setEmail((String)o[3]);

			resultList.add(user);
		}
		return resultList;
	}
}

[color=darkred]UserService.java:[/color]

package com.my.service;

import java.util.List;

import com.my.commons.time.TimeZone;
import com.my.dao.UserDao;
import com.my.po.User;

@SuppressWarnings("unchecked")
public class UserService {
	private UserDao userDao;

	/**
	 * 根据名称查询
	 * 
	 * @return
	 */
	public List<User> getByName(String name) {
		List<User> userList = userDao.queryByName(name);
		if (userList.size() > 0) {
			return userList;
		}
		return null;
	}

	/**
	 * 根据指定日期查询
	 * 
	 * @param timeZone
	 * @return
	 */
	public List<User> getByStartDateToEndDate(TimeZone timeZone) {
		List<User> userList = userDao.queryByStartDateToEndDate(timeZone);
		if (userList.size() > 0) {
			return userList;
		}
		return null;
	}

	public void setUserDao(UserDao userDao) {
		this.userDao = userDao;
	}
}

[color=darkred]User.java:[/color]

package com.my.po;

import java.io.Serializable;
import java.sql.Timestamp;

public class User implements Serializable{
	private static final long serialVersionUID = 1L;

	// ID
	private int id;
	// 名称
	private String name;
	// 创建日期
	private Timestamp createTime;
	// 电子邮箱
	private String email;
//省略get、set和构造方法。
}

[color=darkred]UserServiceTest.java:[/color]

package com.my.test;

import java.util.List;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.my.commons.time.DateUtils;
import com.my.commons.time.TimeZone;
import com.my.po.User;
import com.my.service.UserService;

public class UserServiceTest {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext(
				"applicationContext.xml");
		UserService userService = (UserService) context.getBean("userService");

		UserServiceTest.testGetByName(userService);
		// UserServiceTest.testGetByStartDateToEndDate(userService);
	}

	private static void testGetByName(UserService us) {
		List<User> userList = us.getByName("hao");
		for (User user : userList) {
			System.out.println(user.getId() + " , " + user.getName() + " , "
					+ user.getCreateTime() + " , " + user.getEmail());
		}
	}

	private static void testGetByStartDateToEndDate(UserService us) {
		List<User> userList = us.getByStartDateToEndDate(new TimeZone(DateUtils
				.string2Date("2011-02-07 10:00:00"), DateUtils
				.string2Date("2011-03-09 23:15:59")));
		for (User user : userList) {
			System.out.println(user.getId() + " , " + user.getName() + " , "
					+ user.getCreateTime() + " , " + user.getEmail());
		}
	}

}

表结构：

[img]http://dl.iteye.com/upload/attachment/496563/586e4db1-aca7-3960-a813-26e599171ed2.jpg[/img]