Querying a date column in database

To query a date column, use

select * from person_table where date_of_birth >= to_date('01/01/2000', 'dd/MM/yyyy')

Querying date with java and sql

Two ways to query a date:
1. by formatting and querying as a string
2. by to_date sql function

public List getPersonList(Timestamp startDate, Timestamp endDate)
{
String startDateStr = new SimpleDateFormat("yyyy-MM-dd hh24:mm:ss").format(startDate);
String endDateStr = new SimpleDateFormat("yyyy-MM-dd hh24:mm:ss").format(startDate);

List toReturn = new ArrayList();
StringBuffer personQuery = new StringBuffer();
personQuery.append("select user from User as user ");

if(startDate != null && endDate != null)
{
registrationGrowthQuery.append(" where activationSentTime >= '" + startDateStr + "' and activationSentTime <= '" + endDateStr + "'");

registrationGrowthQuery.append(" where activationSentTime >= to_date("+startDate+", 'yyyy-MM-dd hh24:mm:ss') and activationSentTime <= to_date("+endDate+", 'yyyy-MM-dd hh24:mm:ss')");
}

Google Map Example

Create a div with id = googleMap

<tr>
<td colspan=2 >
<table border=1>
<tr>
<td>
<div id="googleMap" style="width: 550px; height: 450px"></div>
</td>
</tr>
</table>
<td colspan=1 class="sidebar">
</td>
</tr>

Send the AJAX call to the server to retrieve map data

var request = GXmlHttp.create();
request.open("GET", "http://myserver:8080/MapServlet?method=getGoogleMap", true);

// the getGoogleMap method should be implemented on the server side to retrieve the lat and lng from database and populate the result as an XML and return back to front end

request.onreadystatechange = function()
{
if (request.readyState == 4)
{
if (request.status == 200)
{
var response = request.responseText;
if (response == '<%= MapServlet.ERROR_NO_DATA %>')
{
alert('<bean:message key="errors.map.notInDatabase"/>');
}
else
{
var xmlDoc = GXml.parse(response);
// obtain the array of markers and loop through it
var markers = xmlDoc.documentElement.getElementsByTagName("marker");

for (var i = 0; i < markers.length; i++)
{

var lat = parseFloat(GXml.value(markers[i].getElementsByTagName("lat")[0]));
var lng = parseFloat(GXml.value(markers[i].getElementsByTagName("lng")[0]));
var point = new GLatLng(lat, lng);
var html = GXml.value(markers[i].getElementsByTagName("stnName")[0]);

createMarker(point, html);
}
}
}


function createMarker(point, html)
{
var map = new GMap2(document.getElementById("map"));
map.setCenter(point, 3);
var marker = createInfoMarker(gLatLng, html);
map.addOverlay(marker);

GEvent.addListener(marker, "click", function()
{
marker.openInfoWindowHtml(html);
});
}

Sample XSD for creating the above map xml

<xs:element name="MapObject">
<xs:complexType>
<xs:sequence>
<xs:element name="marker" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="lat" type="xs:string" />
<xs:element name="lng" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>

Create own domian names for pc

To add our own domain name to our pc, we may do the following:

On a Windows XP machine you can find your HOSTS file in the “C:\Windows\System32\drivers\etc” directory.
Windows 2000, it’s ” c:\winnt\system32\drivers\etc\hosts”. And on Linux, it’s just “/etc/hosts”.

Open it up in a text editor, and add the following lines:

127.0.0.1 mydomain.com.localhost

and Reboot

Now you can access the url http://mydomain.com.localhost/

Hibernate basic examples

Example shows basic hibernate usage:

person.hbm.xml

<hibernate-mapping>
<class name="com.test.model.Person" table="person" >

<id name="personId" type="int">
<column name="person_id" />
<generator class="sequence">
<param name="sequence">NEXT_PERSON_ID</param>
<param name="parameters">INCREMENT BY 1 START WITH 1</param>
</generator>
</id>
<property name="personAge" type="java.lang.Integer">
<column name="person_age" />
</property>
<property name="personName" type="string">
<column name="person_name" length="3" not-null="true" />
</property>
<property name="personDateOfBirth" type="date">
<column name="person_dob" length="4" />
</property>
<property name="updateTime" type="timestamp">
<column name="update_time" length="3594" />
</property>

We ca also properties to fetch another table data lazily as:
<property name="personRoles" lazy="false" type="string" >
<formula>
(select pr.person_role from PersonRole pr where pr.person_id=person_id)
</formula>
</property>

One to Many mapping returning a List
<list name="personRoles" lazy="true" fetch="select" inverse="true">
<key>
<column name="person_id" />
<column name="person_role" />
</key>
<list-index column="role_seq" base="1"/>
<one-to-many class="com.test.model.PersonRole" />
</list>

One to Many mapping returning a Set
<set name="personRoles" order-by="role_seq" lazy="true" fetch="select" inverse="true">
<key>
<column name="person_id" />
<column name="person_role" />
</key>


Model class to represent this would be as follows:

public class Person implements java.io.Serializable
{

private int personId;
private Integer personAge;
private String personName;
private Date personDateOfBirth;
private Date updateTime;

// setters and getters
}


DAO to access the persistence layer

class PersonDAOHibernateImpl extends PersonDAO
{
public List<Person> getPersonList()
{
try
{
beginTransaction();
Session session = getSession();
List<Person> instList = session.createCriteria(Person.class)
.addOrder(Order.desc("personId"))
.list();

commitTransaction();
return instList;
}
catch(Exception e)
{
rollbackTransaction();
return Collections.emptyList();
}

}


Some other kind of queries we can use are:

To get a specific name list
List<Person> instList = session.createCriteria(Person.class)
.add(Restrictions.eq("personName", Integer.valueOf(personName)))
.addOrder(Order.desc("personId"))
.list();

can also use this clause in above query where we have a composite id
.add(Restrictions.eq("id.name", Integer.valueOf(personName)))

or
Query q = getSession().createQuery("select personName from Person");
List<Person> personList = q.list();

or if we need to make a join on another table say for example called PersonRoles
Criteria c = session.createCriteria(Person.class)
.add(Restrictions.eq("id.name", Integer.valueOf(personId)))
.setFetchMode("roles", FetchMode.JOIN)
.createAlias("roles", "personRoles", CriteriaSpecification.LEFT_JOIN)
.addOrder(Order.asc("id.personId"))
.addOrder(Order.asc("personRoles.id.roleName"))
.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

or we can create a plain sql statement and query
String updatePerson =
" update person " +
" SET person_name = 'ABC' " +
" WHERE person_id = ? ";

Session session = getSession();
beginTransaction();
Connection conn = session.connection();

PreparedStatement pstmt = conn.prepareStatement(updatePerson);
pstmt.setInt(1, personId);
pstmt.execute();
pstmt.close();

Couple of helpful Ant tasks

<target name="hibernatetool" description="Run Hibernate Tool" unless="hibernate.skip" depends="">
<mkdir dir="${src.resources.hibernate.generated}" />
<echo message="ANT HEAP SIZE = ${env.ANT_OPTS}" />
<taskdef name="hibernatetool" classname="org.hibernate.tool.ant.HibernateToolTask" classpathref="hibernatetools.path" />

<hibernatetool destdir="${src.resources.hibernate.generated}" templatepath="${src.resources.hibernate}">
<jdbcconfiguration
revengfile="${src.resources.hibernate}/hibernate.reveng.xml"
configurationfile="${src.resources.hibernate}/hibernate.cfg.xml"
packagename="${package.name}" />

<hbm2hbmxml/> <!-- This regenerates the hbm mapping files -->

<!-- <hbm2cfgxml/> This cretaes a hibernate.cfg.xml file -->
</hibernatetool>
</target>


<target name="hbm2java" description="Run Hibernate Tool" unless="hibernate.skip" depends="">
<taskdef name="hibernatetool" classname="org.hibernate.tool.ant.HibernateToolTask"
classpathref="hibernatetools.path"
/>

<hibernatetool destdir="${src.resources.hibernate.generated}">
<configuration>
<fileset dir="${src.resources.hibernate.generated}">
<include name="**/Person*.hbm.xml" />
</fileset>
</configuration>

<hbm2java jdk5="false" ejb3="false" />
</hibernatetool>
</target>


hibernate.cfg.xml

<hibernate-configuration>

<session-factory>
<!-- Bind the getCurrentSession() method to the thread, session-per-conversation -->
<property name="current_session_context_class">org.hibernate.context.ThreadLocalSessionContext</property>

<property name="hibernate.connection.url">jdbc:informix-sqli:myhostname:myportnumber/mydatabasename:INFORMIXSERVER=myservername</property>
<property name="hibernate.connection.username">myusername</property>
<property name="hibernate.connection.password">mypassword</property>

<property name="hibernate.connection.release_mode">auto</property>

<property name="transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>
<property name="hibernate.connection.driver_class">com.informix.jdbc.IfxDriver</property>
<property name="dialect">org.hibernate.dialect.InformixDialect</property>
<property name="show_sql">true</property>

<!-- Connection Pool -->
<property name="hibernate.connection.pool_size">3</property>
<property name="hibernate.c3p0.min_size">1</property>
<property name="hibernate.c3p0.max_size">5</property>
<property name="hibernate.c3p0.timeout">1800</property>
<property name="hibernate.c3p0.max_statements">100</property>

<!-- Use EHCache as secondary cache-->
<property name="hibernate.cache.use_query_cache">true</property>
<property name="hibernate.cache.provider_class">org.hibernate.cache.EhCacheProvider</property>


<mapping resource="com/mytest/persistence/orm/hibernate/model/Person.hbm.xml" />

</session-factory>
</hibernate-configuration>


hibernate.reveng.xml

<hibernate-reverse-engineering>
<type-mapping>
<sql-type jdbc-type="5" hibernate-type="integer"/>
</type-mapping>
<table-filter match-catalog="mydatabasename" match-schema="informix" match-name="person" exclude="false" />
</hibernate-reverse-engineering>

Spring basic examples

EXAMPLES USING Spring's ApplicationContext class gives support for application framework services.

import org.springframework.context.ApplicationContext;
import org.springframework.web.context.support.WebApplicationContextUtils;

public class PersonTest
{
public ActionForward execute(ActionMapping mapping, ActionForm form, HttpServletRequest request,
HttpServletResponse response) throws Exception
{
ApplicationContext context = WebApplicationContextUtils.getWebApplicationContext(getServlet().getServletContext());
//ApplicationContext context = WebApplicationContextUtils.getWebApplicationContext(config.getServletContext());
//ApplicationContext context = WebApplicationContextUtils.getWebApplicationContext(request.getSession().getServletContext());
PersonHelper personHelper = (PersonHelper) context.getBean("PersonHelper");
List personList = (List)personHelper.getPersonList();
}

}

public class PersonHelper extends HibernateDaoSupport implements DatabaseHelperInterface
{
public List getPersonList() throws PersonDAOException
{
Session session = SessionFactoryUtils.getSession(this.getSessionFactory(), false);
List personList = new ArrayList();
try {
Query query = session.createQuery("from com.test.Person as person order by perosn.personName");
Iterator it = query.iterate();
while (it.hasNext()) {
Person person = (Person) it.next();
personList.add(person);
}
return personList;
}
catch (HibernateException ex)
{
throw new PersonDAOException("getPersonList(): " + ex.getMessage());
}
finally
{
try
{
session.close();
}
catch (HibernateException ex)
{
logger.fatal(ex);
}
}

}
}

import net.sf.hibernate.SessionFactory;


public interface DatabaseHelperInterface {
public SessionFactory getSessionFactory();

public void setSessionFactory(SessionFactory sessionFactory);
}

In applicationContext.xml

<bean id="sessionFactory" class="org.springframework.orm.hibernate.LocalSessionFactoryBean">
<property name="mappingResources">
<list>
<value>com/onerail/orion/common/hibernate/bean/Person.hbm.xml</value>
<value>com/onerail/orion/common/hibernate/bean/PersonRoles.hbm.xml</value>
</list>
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.connection.username">${hibernate.connection.username}</prop>
<prop key="hibernate.connection.password">${hibernate.connection.password}</prop>
<prop key="hibernate.connection.url">${hibernate.connection.url}</prop>
<prop key="hibernate.dialect">net.sf.hibernate.dialect.InformixDialect</prop>
<prop key="hibernate.connection.driver_class">${hibernate.connection.driver_class}</prop>
<prop key="hibernate.show_sql">true</prop>
<prop key="hibernate.use_outer_join">true</prop>
<prop key="hibernate.transaction.factory_class">net.sf.hibernate.transaction.JDBCTransactionFactory</prop>
<prop key="hibernate.dbcp.minIdle">1</prop>
<!-- <prop key="hibernate.cache.use_query_cache">true</prop> -->
<!-- <prop key="hibernate.connection.pool_size">3</prop> -->
<!-- <prop key="hibernate.cache.provider_class">com.onerail.orion.common.hibernate.patch.OSCacheProvider</prop> -->
<prop key="hibernate.cglib.use_reflection_optimizer">false</prop>

<prop key="hibernate.c3p0.min_size">5</prop>
<prop key="hibernate.c3p0.max_size">20</prop>
<prop key="hibernate.c3p0.timeout">1800</prop>
<prop key="hibernate.c3p0.max_statements">50</prop>

</props>
</property>
</bean>

<!-- placeholderConfig loads extra configuration information which is placed into the expressions eg ${com.test.util.constants.dbUser} -->
<bean id="placeholderConfig"
class="com.onerail.orion.common.hibernate.config.HibernateProperties">
<property name="location"><value>classpath:config/runtime.properties</value></property>
</bean>

<bean id="transactionManager" class="org.springframework.orm.hibernate.HibernateTransactionManager">
<property name="sessionFactory">
<ref bean="sessionFactory"/>
</property>
</bean>

<!-- Spring AOP interceptor used to manage sessions -->
<bean id="hibernateInterceptor" class="org.springframework.orm.hibernate.HibernateInterceptor">
<property name="sessionFactory">
<ref bean="sessionFactory"/>
</property>
</bean>

<bean id="hibernateDaoSupport" class="org.springframework.aop.framework.ProxyFactoryBean">
<property name="proxyInterfaces">
<value>com.onerail.orion.reservation.nontravelproducts.DatabaseHelperInterface,com.onerail.orion.reservation.nontravelftr.DatabaseHelperInterface2</value>
</property>
<property name="interceptorNames">
<list>
<value>hibernateInterceptor</value>
</list>
</property>
</bean>

<!-- To set properties that reference other beans <ref>, subelement of <property> is used as shown below -->

<bean id="PersonHelper" class="com.test.PersonHelper" scope="prototype">
<property name="sessionFactory">
<ref bean="sessionFactory"/>
</property>
</bean>
</beans>




In web.xml
<listener>
<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
</listener>


package com.onerail.orion.common.hibernate.config;

import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.config.PropertyPlaceholderConfigurer;

import java.util.Properties;

public class HibernateProperties extends PropertyPlaceholderConfigurer
implements InitializingBean {
private static Logger logger = SystemLog.getLogger(HibernateProperties.class);

private static boolean started = false;

private static Properties prop = new Properties();

protected String resolvePlaceholder(String s, Properties properties) {
if (started == false) {
synchronized (prop) {
if (started == false)
{
Constants.purge();
prop.setProperty("hibernate.connection.username", Constants.getDbUser());
prop.setProperty("hibernate.connection.password", Constants.getDbPassword());
prop.setProperty("hibernate.connection.url", Constants.getDatabaseUrl());
prop.setProperty("hibernate.connection.driver_class", Constants.getDbDrivers());
started = true;
}
}
}
if (prop.get(s) != null)
return (String) prop.get(s);
return super.resolvePlaceholder(s, properties);
}


public void afterPropertiesSet() throws Exception {
}
}


EXAMPLES USING Spring's BeanFactory class gives configuration framework and basic functionality (bean managing)

public interface PersonIF
{
public String getPersonAddress(String name);
}


public class PersonImpl implements PersonIF

{
private String address;
private int age;
private PersonRole personRole;

public PersonImpl(int myAge)
{
this.age = myAge;
}

public PersonImpl(PersonRole personRole)
{
this.personRole = personRole;
}


public String getAddress(String name)
{
return DatabaseHelper.getAddress(name);
}

public void setAddress(String myAddress)

{
this.address = myAddress;
}

public String getPersonRole(String name)
{
return DatabaseHelper.getRoles(name);
}

public void setPersonRoles(String role)

{
this.role = role;
}

}

public class PersonRole
{
String roleName;

public String getRole()
{
return role;
}

public void setRole(String roleName)
{
this.roleName = roleName;
}
}


Add person.xml as follows:

<beans>
<beans>
<bean id="personBean" class="test.PersonImpl">
<constructor-arg><ref bean="personRoleBean"/></constructor-arg>
<property name="address">
<value>50 Grand Central Road</value>
</property>
<property name="personRole">
<ref local="personRoleBean"/>
</property>
</bean>

<bean id="personRoleBean" class="test.PersonRole"/>

</beans>


TEST class


public class PersonTest
{
public static void main(String args[]) throws Exception
{
try
{
Resource resource = new ClassPathResource("person.xml");
BeanFactory factory = new XmlBeanFactory(resource);
Person personBean = (Person)factory.getBean("personBean");
System.out.println(personBean.getAddress(args[0]));
}
catch(Exception e)
{
e.printStackTrace(e);
}

}

}

XSD sample elements

Example shows how to create simple xsd elements

<xs:complexType name="PersonDetailObject">
<xs:sequence>
<xs:element name="PersonId" type="xs:string" minOccurs="0"/>
<xs:element name="Title" type="xs:string" minOccurs="0"/>
<xs:element name="FirstName" type="xs:string" minOccurs="0"/>
<xs:element name="MiddleName" type="xs:string" minOccurs="0"/>
<xs:element name="LastName" type="xs:string" minOccurs="0"/>
<xs:element name="AddressDetails" type="PersonAddressObject" minOccurs="0" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>

Details of a person:-

<xs:element name="PersonRetrieve">
<xs:complexType>
<xs:sequence>
<xs:element ref="EducationInfo"/>
<xs:element name="PersonDetail" type="PersonDetailObject" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>

<xs:element name="EducationInfo">
<xs:complexType>
<xs:sequence>
<xs:element name="Degree" type="xs:string"/>
<xs:element name="University" type="xs:string"/>
<xs:element name="Specialization" type="xs:string" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
</xs:element>

Search for number of persons:-

<xs:element name="PersonSearch">
<xs:complexType>
<xs:sequence>
<xs:element ref="EducationInfo"/>
<xs:element name="PersonList" minOccurs="0">
<xs:complexType>
<xs:sequence>
<xs:element name="PersonSummary" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="PersonId" type="xs:string"/>
<xs:element name="LastName" type="xs:string" minOccurs="0"/>
<xs:element name="FirstName" type="xs:string" minOccurs="0"/>
<xs:element name="Email" type="xs:string" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="TotalRecordCount" type="xs:int"/>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>