queries.java

很好的WebSphereCE轻量级J2EE服务器使用入门的范例程序

package com.ibm.db2.simplej2ee;

import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.xml.parsers.ParserConfigurationException;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;

import com.ibm.db2.jcc.DB2Xml;

public class Queries
{
	// connection-related objects
	DBUtil dbutil = null;
	
	// xml-related objects
	XmlUtil xmlUtil = null;
	
	public Queries() throws FileNotFoundException, SQLException, ParserConfigurationException
	{
		dbutil = new DBUtil();
		xmlUtil = new XmlUtil();
	}
	
	/*
	 * @param date A string-representation of a date in the format YYYY-MM-DD.
	 * @returns A Document object with all the results from the query for events
	 * 	occurring at date. If 'date' is null, return a Document object with
	 * 	all the results from the query for events for any date.
	 */
	public Document getEventsAtDate(String date)
	{
		Connection con = dbutil.getConnection();
		PreparedStatement stmt = null;
		Document doc = xmlUtil.createDocument("events");
		Element rootEl = doc.getDocumentElement();
		String query = null;
		
		try
		{
			query =
				"SELECT xmlelement(name \"event\", xmlforest( "
				+ "trim(id) as \"id\", title as \"title\", type as \"type\", "
				+ "description as \"description\", "
				+ "date(starttime) as \"date\", "
				+ "time(starttime) as \"starttime\")) as event "
				+ "FROM meetup.events ";
			
			if (date != null)
			{
				query += "WHERE date(starttime) <= ? and ? <= date(endtime) ";
			}
			
			query += "ORDER BY id ";

			stmt = con.prepareStatement(query);
				
			if (date != null)
			{
				stmt.setString(1, date);
				stmt.setString(2, date);
			}

			ResultSet rs = stmt.executeQuery();
			Document evtDoc = null;
			while (rs.next())
			{
				DB2Xml db2xml = (DB2Xml) rs.getObject(1);
				evtDoc = xmlUtil.db2XmlToDocument(db2xml);
				Node n = doc.importNode(evtDoc.getDocumentElement(), true);
				rootEl.appendChild(n);
			}
		}
		catch (SQLException e)
		{
			// TODO Auto-generated catch block
			System.err.println("Query: " + query);
			e.printStackTrace();
		}
		
		dbutil.closeConnection(con);
		return doc;
	}
	
	/*
	 * @returns Number of speakers, as an integer. -1 if query failed.
	 */
	public int getNumberOfSpeakers()
	{
		Connection con = dbutil.getConnection();
		PreparedStatement stmt = null;
		String query = null;
		int numberOfSpeakers = -1;
		
		try
		{
			query =
				"values(xmlcast(xmlquery("
				+ "'count(distinct-values(db2-fn:xmlcolumn(\"MEETUP.EVENTS.SPEAKERS\")/speakers/speaker))' returning sequence"
				+ ") as integer))";
		 
			stmt = con.prepareStatement(query);
				
			ResultSet rs = stmt.executeQuery();
			
			if (rs.next())
			{
				numberOfSpeakers = rs.getInt(1); 
			}
			
		}
		catch (SQLException e) {
			// TODO: handle exception
			System.err.println("Query: " + query);
			e.printStackTrace();
		}		

		dbutil.closeConnection(con);
		return numberOfSpeakers;
	}
	
	/*
	 * @param company The name of the company
	 * @returns A Document object with all the results from the query for the speakers
	 * 	from the specified company. If 'company' is null, return a Document object with
	 * 	all the results from the query for the speakers from all companies.
	 */
	public Document getSpeakersFromCompany(String companyName)
	{
		Connection con = dbutil.getConnection();
		Statement stmt = null;
		String query = null;

		Document evtDoc = null;
		
		try
		{
			stmt = con.createStatement();
	
			if (companyName != null)
			{
				companyName = dbutil.cleanXQueryString(companyName);
			}
			
			query =
				"values(xmlquery(' "
				+ "<events> "
				+ "{ "
				+ "	for $c in db2-fn:xmlcolumn(\"MEETUP.EVENTS.SPEAKERS\")/speakers/speaker ";
			
			if (companyName != null)
			{
				query += "	where matches($c/company, \"" + companyName + "\", \"i\") ";
			}				
			
			query += " order by $c/lastname "
				+ "	return $c "
				+ "} "
				+ "</events> "
				+ "'))";
			
			ResultSet rs = stmt.executeQuery(query);
			
			if (rs.next())
			{
				DB2Xml db2xml = (DB2Xml) rs.getObject(1);
				evtDoc = xmlUtil.db2XmlToDocument(db2xml);
			}
		}
		catch (SQLException e)
		{
			// TODO Auto-generated catch block
			System.err.println("Query: " + query);
			e.printStackTrace();
		}
		
		dbutil.closeConnection(con);
		return evtDoc;
	}
	
	/*
	 * Insert a row into the events table.
	 * @param id Event ID, as a String
	 * @param title, as a String
	 * @param type, as a String
	 * @param startDateTime, as a String in the form yyyy-mm-dd hh:mm:00
	 * @param endDateTime, as a String in the form yyyy-mm-dd hh:mm:00
	 * @param description, as a String
	 * @param lastNames, as an array of Strings
	 * @param firstNames, as an array of Strings
	 * @param companyNames, as an array of Strings
	 */
	public void insertEvent(String id, String title, String type,
			String startDateTime, String endDateTime,
			String description, String[] lastNames, String[] firstNames,
			String[] companyNames)
	{
		Connection con = dbutil.getConnection();
		PreparedStatement stmt = null;
		String insert = null;
		
		try
		{
			insert =
				"INSERT INTO meetup.events (id, title, type, startTime," +
				"	endTime, description, speakers)" +
				"	VALUES (?, ?, ?, ?, ?, ?, ?)";
		 
			stmt = con.prepareStatement(insert);
			
			stmt.setString(1, id);
			stmt.setString(2, title);
			stmt.setString(3, type);
			stmt.setString(4, startDateTime);
			stmt.setString(5, endDateTime);
			stmt.setString(6, description);
			
			// create the XML document describing the speakers
			String speakers = "<speakers>";
			for (int i = 0; i < lastNames.length; i++)
			{
				if (lastNames[i] != null && firstNames[i] != null && companyNames[i] != null)
				{
					speakers += "<speaker>";
					speakers += "<lastname>" + lastNames[i] + "</lastname>";
					speakers += "<firstname>" + firstNames[i] + "</firstname>";
					speakers += "<company>" + companyNames[i] + "</company>";
					speakers += "</speaker>";
				}
			}
			speakers += "</speakers>";
			
			stmt.setString(7, speakers);
				
			stmt.execute();			
		}
		catch (SQLException e)
		{
			// TODO: handle exception
			System.err.println("Insert: " + insert);
			e.printStackTrace();
		}		

		dbutil.closeConnection(con);
	}

}