mysqlstorage.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.sql.Date;
import java.util.Calendar;

import javax.naming.NamingException;

import org.apache.log4j.Logger;

/**
 * @author pjaol
 * 
 */
public class MysqlStorage implements IStorage {

	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.storeage.IStorage#addDatum(java.lang.String)
	 */
public boolean addDatum(String url) throws StorageException {

		try {
			PreparedStatement stmt = conn
					.prepareStatement("insert into datum(url, date) values (?,?);");
			stmt.setString(1, url);
			stmt.setDate(2, _now());		
			return stmt.execute();
		} catch (SQLException e) {
			throw new StorageException(1,getClass().getName(),"addDatum",
						"addDatum failed to add datum\n" + e.getMessage(), null);
		}
	}
	/*
	 * (non-Javadoc)
	 * 
	 * @see com.pjaol.ifodder.storeage.IStorage#addListItem(int, int,
	 *      java.lang.String)
	 */
	public boolean addListItem(int user_id, int datum_id, String description) throws StorageException {

		PreparedStatement stmt;
		try {
			deleteListItem(user_id, datum_id);
			stmt = conn
					.prepareStatement("insert into list_item(user_id, datum_id, description, date) values(?,?,?,?);");
			stmt.setInt(1, user_id);
			stmt.setInt(2, datum_id);
			stmt.setString(3, description);
			stmt.setDate(4, _now());
			return stmt.execute();
		} catch (SQLException e) {
			throw new StorageException(1,getClass().getName(),"addListItem",
					"addListItem failed to add datum\n" + e.getMessage(), null);
		}
		
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.pjaol.ifodder.storeage.IStorage#addTag(int, int,
	 *      java.lang.String)
	 */
	public boolean addTag(int user_id, int li_id, String tag) throws StorageException {
		PreparedStatement stmt;
		try {
			stmt = conn
					.prepareStatement("insert into tag_item(user_id, li_id, tag, date) values(?,?,?,?);");
			stmt.setInt(1, user_id);
			stmt.setInt(2, li_id);
			stmt.setString(3, tag);
			stmt.setDate(4,_now());

			return stmt.execute();
		} catch (SQLException e) {
			
			throw new StorageException(1,getClass().getName(), "addTag",
					"addTag failed to add a tag\n" + e.getMessage(), null);
		}
		
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.pjaol.ifodder.storeage.IStorage#getListId(int, int)
	 */
	public int getListId(int user_id, int datum_id) throws StorageException {
		PreparedStatement stmt;
		try {
			stmt = conn
					.prepareStatement("select id from list_item where datum_id = ? and user_id = ?;");
			stmt.setInt(1, datum_id);
			stmt.setInt(2, user_id);
			ResultSet rs = stmt.executeQuery();
			
			if (rs.first())
				return rs.getInt("id");
		} catch (SQLException e) {
			throw new StorageException(1,getClass().getName(),"getListId",
					"getListId failed to get a list item id\n" + e.getMessage(), null);
		}
		return -1;
		
	}

	/*
	 *  (non-Javadoc)
	 * @see com.pjaol.ifodder.storage.IStorage#deleteListItem(int, int)
	 */
	public boolean deleteListItem (int user_id, int datum_id) throws StorageException {
		
		try {
			//tag_item in db has a foreign key with cascade on delete turned on.
			PreparedStatement stmt = conn
					.prepareStatement("delete list_item from list_item " +
									"where  list_item.datum_id = ? and list_item.user_id= ?;");
			stmt.setInt(1, datum_id);
			stmt.setInt(2, user_id);
			
			return stmt.execute();
			
		} catch (SQLException e) {
			throw new StorageException(1,getClass().getName(),"deleteListItem",
					"deleteListItem failed to delete a list item id\n" + e.getMessage(), null);
		}

	}
	/*
	 * (non-Javadoc)
	 * 
	 * @see com.pjaol.ifodder.storeage.IStorage#getDatumId(java.lang.String)
	 */
	public int getDatumId(String url) throws StorageException {
		
		try {
			PreparedStatement stmt = conn
					.prepareStatement("select id from datum where url = ?;");
			stmt.setString(1, url);
			ResultSet rs = stmt.executeQuery();
			if( rs.first())	
				return rs.getInt("id");
			
		} catch (SQLException e) {
			throw new StorageException(1,getClass().getName(), "getDatumId",
					"getDatumId failed to get a datum id\n" + e.getMessage(),null);
		}
		return -1;
	}

	private Date _now() {
		return new Date(Calendar.getInstance().getTimeInMillis());
	}

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