Hibernate Named Query 2
applicationContext.xml:
- <?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>
<?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>
UserNamed.hbm.xml:
- <?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>
<?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>
一些日期处理: DateUtils.java:
- 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);
- }
- }
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); } }
TimeZone.java:
- 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和构造方法。
- }
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和构造方法。 }
UserDao.java:
- 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;
- }
- }
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; } }
UserService.java:
- 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;
- }
- }
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; } }
User.java:
- 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和构造方法。
- }
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和构造方法。 }
UserServiceTest.java:
- 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());
- }
- }
- }