mysqlretrival.java

Local Lucene ==================== Provide geographical based searching to lucene in an efficent ma

/**
 * 
 */
package com.pjaol.ifodder.storage;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import javax.naming.NamingException;

import org.apache.log4j.Logger;

import com.javaunderground.jdbc.DebugLevel;
import com.javaunderground.jdbc.DebuggableStatement;
import com.javaunderground.jdbc.StatementFactory;

/**
 * @author pjaol
 * 
 */
public class MysqlRetrival implements IRetrival {

	Connection conn;
	{
		try {
			conn = new DBConnection().getConnection();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (NamingException e) {
			e.printStackTrace();
		}
	}

	Logger _log = Logger.getLogger(getClass());

	/* (non-Javadoc)
	 * @see com.pjaol.ifodder.storage.IRetrival#getListItem(int, int)
	 */
	public ListItem getListItem(int user_id, int li_id) throws StorageException {

		try {
			String sql = "select id, datum_id, user_id, description, date, popularity"
				+ " from list_item where user_id = ? and id = ?;";
			//PreparedStatement stmt = StatementFactory.getStatement(conn, sql, DebugLevel.VERBOSE);
			PreparedStatement stmt = conn.prepareStatement(sql);
			stmt.setInt(1, user_id);
			stmt.setInt(2, li_id);
			
			_log.debug("Stmt : " + stmt.toString());
			ResultSet rs = stmt.executeQuery();
			
			if (rs.first()) {

				ListItem li = new ListItem();
				li.setLi_id(li_id);
				li.setDatum_id(rs.getInt("datum_id"));
				li.setDate(rs.getDate("date"));
				li.setDescription(rs.getString("description"));
				li.setPopularity(rs.getInt("popularity"));
				li.setUser_id(rs.getInt("user_id"));
				li.setTags(getTags(user_id, li_id));

				return li;
			}
			
			return null;
		} catch (SQLException e) {
			throw new StorageException(1, getClass().getName(), "getListItem",
					"getListItem failed to get a list_item\n" + e.getMessage(),
					null);
		}
	}
	
	/* (non-Javadoc)
	 * @see com.pjaol.ifodder.storage.IRetrival#getListItemsByUser(int)
	 */
	@SuppressWarnings("unchecked")
	public Vector getListItemsByUser (int user_id) throws StorageException {
		
		try {
			PreparedStatement stmt = conn.prepareStatement("select id, datum_id, user_id, description, popularity, date from list_item where user_id = ?;");
			stmt.setInt(1, user_id);
			ResultSet rs = stmt.executeQuery();
			Vector results = new Vector();
			while(rs.next()) {
				ListItem li = new ListItem();

				li.setDatum_id(rs.getInt("datum_id"));
				li.setDate(rs.getDate("date"));
				li.setDescription(rs.getString("description"));
				li.setPopularity(rs.getInt("popularity"));
				li.setUser_id(rs.getInt("user_id"));
				
				results.add(li);
			}
			return results;
		} catch (SQLException e) {
			throw new StorageException(1, getClass().getName(), "getListItemsByUser",
					"getListItemsByUser failed to get a list_item\n" + e.getMessage(),
					null);
		}
	}

	/* (non-Javadoc)
	 * @see com.pjaol.ifodder.storage.IRetrival#getTags(int, int)
	 */
	@SuppressWarnings("unchecked")
	public Vector getTags(int user_id, int li_id) throws StorageException {

		try {
			PreparedStatement stmt = null;
			if (li_id != -1) {
				stmt = conn
						.prepareStatement("select tag from tag_item where user_id = ? and li_id = ?;");
				stmt.setInt(1, user_id);
				stmt.setInt(2, li_id);
			} else {
				stmt = conn
						.prepareStatement("select tag from tag_item where user_id = ?;");
				stmt.setInt(1, user_id);
			}
			ResultSet rs = stmt.executeQuery();
			Vector tags = new Vector();

			while (rs.next()) {
				tags.add(rs.getString("tag"));
			}

			return tags;
		} catch (SQLException e) {
			throw new StorageException(1, getClass().getName(), "getTags",
					"getTags failed to get Tags \n" + e.getMessage(), null);
		}
	}

	
	
	/* (non-Javadoc)
	 * @see com.pjaol.ifodder.storage.IRetrival#getTagsByUser(int)
	 */
	public Vector getTagsByUser(int user_id) throws StorageException {
		return getTags(user_id, -1);
	}

	protected void finalize() throws Throwable {
		try {
			conn.close(); // close open files
		} finally {
			super.finalize();
		}
	}
}