content: /**
   * Licensed to the Apache Software Foundation (ASF) under one
   * or more contributor license agreements.  See the NOTICE file
   * distributed with this work for additional information
   * regarding copyright ownership.  The ASF licenses this file
   * to you under the Apache License, Version 2.0 (the
   * "License"); you may not use this file except in compliance
   * with the License.  You may obtain a copy of the License at
   *
   *   http://www.apache.org/licenses/LICENSE-2.0
   *
   * Unless required by applicable law or agreed to in writing,
   * software distributed under the License is distributed on an
   * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
   * KIND, either express or implied.  See the License for the
   * specific language governing permissions and limitations
   * under the License.
   */
  package org.apache.aries.samples.blog.persistence.jdbc;
  
  import java.sql.Connection;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Statement;
  import java.text.ParseException;
  import java.util.ArrayList;
  import java.util.Date;
  import java.util.List;
  import javax.annotation.PostConstruct;
  import javax.annotation.PreDestroy;
  import javax.inject.Inject;
  import javax.sql.DataSource;
  
  import org.apache.aries.blueprint.annotation.bean.Activation;
  import org.apache.aries.blueprint.annotation.bean.Bean;
  import org.apache.aries.blueprint.annotation.service.Service;
  import org.apache.aries.samples.blog.api.persistence.BlogPersistenceService;
  import org.apache.aries.samples.blog.persistence.jdbc.entity.AuthorImpl;
  import org.apache.aries.samples.blog.persistence.jdbc.entity.EntryImpl;
  
  /**
   * This class is the implementation of the blogPersistenceService
   */
  @Service(classes = BlogPersistenceService.class)
  @Bean(activation = Activation.LAZY)
  public class BlogPersistenceServiceImpl implements BlogPersistenceService {
  	private DataSource dataSource;
  
      private Statements statements;
  
      public BlogPersistenceServiceImpl() {
          this.statements = new Statements();
      }
  
  	/**
  	 * set data source
  	 */
      @Inject
  	public void setDataSource(DataSource dataSource) {
  		this.dataSource = dataSource;
  	}
  
      @PostConstruct
      public void init() {
          Statement s = null;
          Connection connection = null;
  
          try {
              connection = dataSource.getConnection();
  
              s = connection.createStatement();
              String[] createStatments = this.statements
                      .getCreateSchemaStatements();
              for (int i = 0; i < createStatments.length; i++) {
                  s.execute(createStatments[i]);
              }
          } catch (SQLException e) {
              e.printStackTrace();
              throw new RuntimeException(e);
          } finally {
              if (s != null) {
                  try {
                      s.close();
                  } catch (Throwable e) {
                  }
              }
              if (connection != null) {
                  try {
                      connection.close();
                  } catch (Throwable e) {
                  }
              }
          }
      }
  
      @PreDestroy
      public void destroy() {
          Statement s = null;
          Connection connection = null;
  
          try {
              connection = dataSource.getConnection();
  
              s = connection.createStatement();
              String[] dropStatments = this.statements.getDropSchemaStatements();
              for (int i = 0; i < dropStatments.length; i++) {
                  s.execute(dropStatments[i]);
              }
          } catch (SQLException e) {
              e.printStackTrace();
              throw new RuntimeException(e);
          } finally {
              if (s != null) {
                  try {
                      s.close();
                  } catch (Throwable e) {
                  }
              }
              if (connection != null) {
                  try {
                      connection.close();
                  } catch (Throwable e) {
                  }
              }
          }
      }
  
  	/**
  	 * Create an author record
  	 *
  	 * @param a
  	 *            The author object to be created
  	 * @throws ParseException
  	 * @throws ParseException
  	 */
  	public void createAuthor(String email, Date dob, String name,
  			String displayName, String bio) {
  
  
  		try {
  			Connection connection = dataSource.getConnection();
  			String sql = "INSERT INTO AUTHOR VALUES (?,?,?,?,?)";
  
  			PreparedStatement ppsm = connection.prepareStatement(sql);
  			ppsm.setString(1, email);
  			ppsm.setString(2, bio);
  			ppsm.setString(3, displayName);
  			if (dob != null)
  				ppsm.setDate(4, new java.sql.Date(dob.getTime()));
  			else
  				ppsm.setDate(4, null);
  			ppsm.setString(5, name);
  			int insertRows = ppsm.executeUpdate();
  			ppsm.close();
  			connection.close();
  
  			if (insertRows != 1)
  				throw new IllegalArgumentException("The Author " + email
  						+ " cannot be inserted.");
  		} catch (SQLException e) {
  			e.printStackTrace();
  			throw new IllegalArgumentException(e.getMessage());
  		}
  
  	}
  
  	/**
  	 * Create a blog entry record
  	 *
  	 * @param a
  	 * 			The author
  	 * @param title
  	 * 			The title of the post
  	 * @param blogText
  	 * 			The text of the post
  	 * @param tags
  	 *
  	 */
  	public void createBlogPost(String authorEmail, String title, String blogText,
  			List<String> tags) {
  
  		AuthorImpl a = getAuthor(authorEmail);
  
  		if(title == null) title = "";
  		Date publishDate = new Date(System.currentTimeMillis());
  		if(tags == null) tags = new ArrayList<String>();
  
  
  		try {
  			Connection connection = dataSource.getConnection();
  			// let's find the max id from the blogentry table
  			String sql = "SELECT max(id) FROM BLOGENTRY";
  			PreparedStatement ppsm = connection.prepareStatement(sql);
  			ResultSet rs = ppsm.executeQuery();
  			// we only expect to have one row returned
  			rs.next();
  
  			long max_id = rs.getLong(1);
  			ppsm.close();
  
  			long post_id = max_id + 1;
  			sql = "INSERT INTO BLOGENTRY VALUES (?,?,?,?,?,?)";
  
  		    ppsm = connection.prepareStatement(sql);
  			ppsm.setLong(1, post_id);
  			ppsm.setString(2, blogText);
  			if (publishDate != null)
  				ppsm
  						.setDate(3, new java.sql.Date(publishDate
  								.getTime()));
  			else
  				ppsm.setDate(3, null);
  			ppsm.setString(4, title);
  
  		    ppsm.setDate(5, null);
  			ppsm.setString(6, a.getEmail());
  			int rows = ppsm.executeUpdate();
  			if (rows != 1)
  				throw new IllegalArgumentException(
  						"The blog entry record cannot be inserted: "
  								+ blogText);
  			ppsm.close();
  
  			// insert a row in the relationship table
  
  			sql = "INSERT INTO Author_BlogEntry VALUES (?,?)";
  			ppsm = connection.prepareStatement(sql);
  			ppsm.setString(1, a.getEmail());
  			ppsm.setLong(2, post_id);
  
  			rows = ppsm.executeUpdate();
  			ppsm.close();
  			connection.close();
  
  			if (rows != 1)
  				throw new IllegalArgumentException(
  						"The Author_BlogEntry record cannot be inserted: "
  								+ a.getEmail() + " , " + post_id);
  
  		} catch (SQLException sqle) {
  			sqle.printStackTrace();
  		}
  
  	}
  
  	/**
  	 * Find the blog entry record with the specified title
  	 *
  	 * @param The title to be searched
  	 * @return The blogEntry record
  	 */
  	public EntryImpl findBlogEntryByTitle(String title) {
  
  		EntryImpl be = null;
  
  		String sql = "SELECT * FROM BlogEntry e WHERE e.title = '" + title
  				+ "'";
  
  		List<EntryImpl> blogEntries = findBlogs(sql);
  
  		// just return the first blog entry for the time being
  		if ((blogEntries != null) && (blogEntries.size() > 0))
  			be = blogEntries.get(0);
  		return be;
  	}
  
  	/**
  	 * Return all author records in the Author table
  	 *
  	 * @return the list of Author records
  	 */
  	public List<AuthorImpl> getAllAuthors() {
  		String sql = "SELECT * FROM Author";
  
  		List<AuthorImpl> list = findAuthors(sql);
  
  		return list;
  	}
  
  	/**
  	 * Return all blog entry records from BlogEntry table with the most recent
  	 * published blog entries first
  	 *
  	 * @return a list of blogEntry object
  	 */
  	public List<EntryImpl> getAllBlogEntries() {
  		String sql = "SELECT * FROM BlogEntry b ORDER BY b.publishDate DESC";
  
  		List<EntryImpl> list = findBlogs(sql);
  
  		return list;
  	}
  
  	/**
  	 * Return the number of the blog entry records
  	 *
  	 * @return the number of the blog Entry records
  	 */
  	public int getNoOfBlogEntries() {
  
  		int count = 0;
  
  		String sql = "SELECT count(*) FROM BLOGENTRY";
  		try {
  			Connection connection = dataSource.getConnection();
  			PreparedStatement ppsm = connection.prepareStatement(sql);
  			ResultSet rs = ppsm.executeQuery();
  			// we only expect to have one row returned
  			rs.next();
  			count = rs.getInt(1);
  			ppsm.close();
  			connection.close();
  
  		} catch (SQLException sqle) {
  			sqle.printStackTrace();
  		}
  		return count;
  	}
  
  	/**
  	 * Return the portion of blog Entries
  	 *
  	 * @param firstPostIndex
  	 *            The index of the first blog entry to be returned
  	 * @param noOfPosts
  	 *            The number of blog entry to be returned
  	 * @return The list of the blog entry record
  	 */
  	public List<EntryImpl> getBlogEntries(int firstPostIndex, int noOfPosts) {
  		String sql = "SELECT * FROM BlogEntry b ORDER BY b.publishDate DESC";
  		List<EntryImpl> emptyList = new ArrayList<EntryImpl>();
  		List<EntryImpl> blogs = findBlogs(sql);
  		// we only return a portion of the list
  		if (blogs == null)
  			return emptyList;
  		// We need to make sure we won't throw IndexOutOfBoundException if the
  		// supplied
  		// index is out of the list range
  		int maximumIndex = blogs.size();
  		// if the first index is minus or greater than the last item index of
  		// the list, return an empty list
  		if ((firstPostIndex < 0) || (noOfPosts <= 0)
  				|| (firstPostIndex > maximumIndex))
  			return emptyList;
  		// return the required number of the blog entries or the available blog
  		// entries
  		int lastIndex = noOfPosts + firstPostIndex;
  		// we need to make sure we return the blog entries at most up to the
  		// final record
  
  		return (blogs.subList(firstPostIndex,
  				(lastIndex > maximumIndex) ? maximumIndex : lastIndex));
  
  	}
  
  	/**
  	 * Return the author with the specified email address
  	 *
  	 * @param emailAddress
  	 *            The email address
  	 * @return The author record
  	 */
  	public AuthorImpl getAuthor(String emailAddress) {
  		String sql = "SELECT * FROM AUTHOR a where a.email='" + emailAddress
  				+ "'";
  		List<AuthorImpl> authors = findAuthors(sql);
  
  		if (authors.size() == 0)
  			return null;
  		else if (authors.size() > 1)
  			throw new IllegalArgumentException(
  					"Email address should be unique per author");
  
  		return authors.get(0); // just return the first author
  	}
  
  	/**
  	 * Return the blog entries modified between the date range of [start, end]
  	 *
  	 * @param start
  	 *            The start date
  	 * @param end
  	 *            The end date
  	 * @return The list of blog entries
  	 */
  	public List<EntryImpl> getBlogEntriesModifiedBetween(Date start, Date end) {
  
  		// String sql = "SELECT * FROM BlogEntry b WHERE (b.updatedDate >= " +
  		// startTS +" AND b.updatedDate <= " + endTS + ") OR (b.publishDate >= "
  		// +startTS + " AND b.publishDate <= " + endTS +
  		// ") ORDER BY b.publishDate ASC";
  		String sql = "SELECT * FROM BlogEntry b WHERE (Date(b.updatedDate) BETWEEN '"
  				+ start
  				+ "' AND '"
  				+ end
  				+ "') OR (Date(b.publishDate) BETWEEN '"
  				+ start
  				+ "' AND  '"
  				+ end + "') ORDER BY b.publishDate ASC";
  
  		return findBlogs(sql);
  
  	}
  
  	/**
  	 * Return a list of blog entries belonging to the author with the specified
  	 * email address
  	 *
  	 * @param emailAddress
  	 *            the author's email address
  	 * @return The list of blog entries
  	 */
  	public List<EntryImpl> getBlogsForAuthor(String emailAddress) {
  
  		String sql = "SELECT * FROM BlogEntry b WHERE b.AUTHOR_EMAIL='"
  				+ emailAddress + "'";
  		return findBlogs(sql);
  	}
  
  	/**
  	 * Update the author record
  	 *
  	 * @param email
  	 * 			The email associated with an author
  	 * @param dob
  	 * 			The author's date of birth
  	 * @param name
  	 * 			the author's name
  	 * @param displayName
  	 * 			The displayName
  	 * @param bio
  	 * 			The aouthor's bio
  	 */
  	public void updateAuthor(String email, Date dob, String name,
  			String displayName, String bio) {
  
  
  		String sql = "UPDATE AUTHOR a SET bio = ?, displayName = ?, dob = ?, name =? WHERE email ='"
  				+ email + "'";
  		int updatedRows = 0;
  		try {
  			Connection connection = dataSource.getConnection();
  			PreparedStatement ppsm = connection.prepareStatement(sql);
  			ppsm.setString(1, bio);
  			ppsm.setString(2, displayName);
  			if (dob != null)
  				ppsm.setDate(3, new java.sql.Date(dob.getTime()));
  			else
  				ppsm.setDate(3, null);
  			ppsm.setString(4, name);
  			updatedRows = ppsm.executeUpdate();
  
  			ppsm.close();
  			connection.close();
  
  			if (updatedRows != 1)
  				throw new IllegalArgumentException("The Author " + email
  						+ " cannot be updated.");
  		} catch (SQLException e) {
  			e.printStackTrace();
  		}
  	}
  
  	/**
  	 * Update the blog entry record
  	 *
  	 *
  	 */
  	public void updateBlogEntry(long id, String email, String title, String blogText, List<String> tags, Date updatedDate) {
  
  		if (id == -1)
  			throw new IllegalArgumentException(
  					"Not a BlogEntry returned by this interface");
  		EntryImpl b = getBlogEntryById(id);
  		String sql_se = "SELECT * FROM BLOGENTRY bp WHERE bp.id = " + id;
  		String email_old = null;
  		// let's find out the email address for the blog post to see whether the
  		// table Author_BlogEntry needs to be updated
  		// if the email is updated, we need to update the table Author_BlogEntry
  		// to reflect the change.
  		try {
  			Connection connection = dataSource.getConnection();
  			PreparedStatement ppsm = connection.prepareStatement(sql_se);
  			ResultSet rs = ppsm.executeQuery();
  			// there should be just one record
  			rs.next();
  			email_old = rs.getString("AUTHOR_EMAIL");
  			ppsm.close();
  			connection.close();
  		} catch (SQLException sqle) {
  			sqle.printStackTrace();
  		}
  		String sql = "UPDATE BLOGENTRY bp SET bp.blogText = ?, bp.publishDate = ?, bp.title = ?, bp.updatedDate = ?, bp.AUTHOR_EMAIL = ? where bp.id = "
  				+ id;
  		int updatedRows = 0;
  
  		try {
  			Connection connection = dataSource.getConnection();
  			PreparedStatement ppsm = connection.prepareStatement(sql);
  			ppsm.setString(1, blogText);
  			if (b.getPublishDate() != null)
  				ppsm
  						.setDate(2, new java.sql.Date(b.getPublishDate()
  								.getTime()));
  			else
  				ppsm.setDate(2, null);
  			ppsm.setString(3, b.getTitle());
  			if (b.getUpdatedDate() != null)
  				ppsm
  						.setDate(4, new java.sql.Date(b.getUpdatedDate()
  								.getTime()));
  			else
  				ppsm.setDate(4, null);
  
  			ppsm.setString(5, email);
  			updatedRows = ppsm.executeUpdate();
  
  			ppsm.close();
  
  			connection.close();
  
  			if (updatedRows != 1)
  				throw new IllegalArgumentException("The Blog " + b.getId()
  						+ " cannot be updated.");
  		} catch (SQLException e) {
  			e.printStackTrace();
  		}
  
  		// if the email address is changed, we need to need to update the
  		// relationship table Author_BlogEntry
  		if ((email_old != null) && (!!!email_old.equals(email))) {
  			// update the table Author_BlogEntry
  			String sql_ab = "UDPATE Author_BlogEntry ab SET ab.AUTHOR_EMAIL = '"
  					+ email + "'";
  			updatedRows = 0;
  			try {
  				Connection connection = dataSource.getConnection();
  				PreparedStatement ppsm = connection.prepareStatement(sql_ab);
  				updatedRows = ppsm.executeUpdate();
  				ppsm.close();
                  connection.close();
  				if (updatedRows != 1)
  					throw new IllegalArgumentException(
  							"The Author_BlogEntry with the postsID "
  									+ b.getId() + " cannot be updated.");
  			} catch (SQLException e) {
  				e.printStackTrace();
  			}
  		}
  	}
  
  	/**
  	 * Delete the author record with the specified email address
  	 *
  	 * @param emailAddress
  	 *            The author's email address
  	 *
  	 */
  	public void removeAuthor(String emailAddress) {
  
  		// we need to remove the author and its blog entries
  
  		try {
  			String sql = "DELETE FROM BLOGENTRY bp WHERE bp.AUTHOR_EMAIL = '"
  					+ emailAddress + "'";
  			Connection connection = dataSource.getConnection();
  			PreparedStatement ppsm = connection.prepareStatement(sql);
  			ppsm.executeUpdate();
  			ppsm.close();
  
  			// delete the records from Author_BlogEntry
  			sql = "DELETE FROM Author_BlogEntry ab WHERE ab.AUTHOR_EMAIL = '"
  					+ emailAddress + "'";
  			ppsm = connection.prepareStatement(sql);
  			ppsm.executeUpdate();
  			ppsm.close();
  
  			// delete the author record
  			sql = "DELETE FROM Author a WHERE a.EMAIL = '" + emailAddress + "'";
  			ppsm = connection.prepareStatement(sql);
  			ppsm.executeUpdate();
  			ppsm.close();
  			connection.close();
  
  		} catch (SQLException sqle) {
  			sqle.printStackTrace();
  		}
  
  	}
  
  	/**
  	 * Delete the blog entry record specified by the blogEntry
  	 *
  	 * @param blogEntry
  	 *            the blog entry record to be deleted
  	 */
  	public void removeBlogEntry(long id) {
  		if (id == -1)
  			throw new IllegalArgumentException(
  					"Not a BlogEntry returned by this interface");
  
  		try {
  			String sql = "DELETE FROM BLOGENTRY bp WHERE bp.id = "
  					+ id;
  			Connection connection = dataSource.getConnection();
  			PreparedStatement ppsm = connection.prepareStatement(sql);
  			ppsm.executeUpdate();
  			ppsm.close();
  			// We also need to delete the records from Author_BlogEntry, as this
  			// table is a kind of link between author and blogentry record
  			sql = "DELETE FROM Author_BlogEntry ab WHERE ab.POSTS_ID = "
  					+ id;
  			ppsm = connection.prepareStatement(sql);
  			ppsm.executeUpdate();
  			ppsm.close();
  			connection.close();
  
  		} catch (SQLException sqle) {
  			sqle.printStackTrace();
  		}
  
  	}
  
  	/**
  	 * Return the blog entry record with the specified id
  	 *
  	 * @param postId
  	 *            The blogEntry record id
  	 */
  	public EntryImpl getBlogEntryById(long postId) {
  		String sql = "SELECT * FROM BlogEntry b WHERE b.id = " + postId;
  		List<EntryImpl> blogs = findBlogs(sql);
  		if (blogs.size() == 0)
  			return null;
  		if (blogs.size() > 1)
  			throw new IllegalArgumentException("Blog id is not unique");
  		return blogs.get(0);
  	}
  
  	/**
  	 * Return a list of authors with the sql query
  	 *
  	 * @param sql
  	 *            The SQL query
  	 * @return A list of author records
  	 */
  	private List<AuthorImpl> findAuthors(String sql) {
  		List<AuthorImpl> authorList = new ArrayList<AuthorImpl>();
  
  		try {
  			Connection connection = dataSource.getConnection();
  			PreparedStatement ppsm = connection.prepareStatement(sql);
  
  			ResultSet ars = ppsm.executeQuery();
  
  			while (ars.next()) {
  				AuthorImpl ar = new AuthorImpl();
  				ar.setBio(ars.getString("bio"));
  				ar.setDisplayName(ars.getString("displayName"));
  				ar.setDob(ars.getDate("dob"));
  				String email = ars.getString("email");
  				ar.setEmail(email);
  				ar.setName(ars.getString("name"));
  
  				// let's find the blog entries for the author
  				String sql_be = "SELECT * FROM BLOGENTRY be WHERE be.AUTHOR_EMAIL = '"
  						+ email + "'";
  				PreparedStatement ppsm2 = connection.prepareStatement(sql_be);
  				ResultSet rs = ppsm2.executeQuery();
  
  				List<EntryImpl> blogs = new ArrayList<EntryImpl>();
  				while (rs.next()) {
  					EntryImpl blog = new EntryImpl();
  					blog.setAuthor(ar);
  					blog.setId(rs.getLong("id"));
  					blog.setBlogText(rs.getString("blogText"));
  					blog.setPublishDate(rs.getDate("publishDate"));
  					blog.setTitle(rs.getString("title"));
  					blog.setUpdatedDate(rs.getDate("updatedDate"));
  					blogs.add(blog);
  				}
  				ar.setEntries(blogs);
  				authorList.add(ar);
  				ppsm2.close();
  			}
  		    ppsm.close();
  			connection.close();
  		} catch (Exception e) {
  			e.printStackTrace();
  		}
  		return authorList;
  	}
  
  	/**
  	 * Return a list of blog entries with the sql query
  	 *
  	 * @param sql
  	 *            The sql query to be executed
  	 * @return a list of blogEntry records
  	 */
  	private List<EntryImpl> findBlogs(String sql) {
  		List<EntryImpl> blogEntryList = new ArrayList<EntryImpl>();
  
  		try {
  			Connection connection = dataSource.getConnection();
  			PreparedStatement ppsm = connection.prepareStatement(sql);
  			ResultSet blogrs = ppsm.executeQuery();
  
  			while (blogrs.next()) {
  				EntryImpl be = new EntryImpl();
  				be.setId(blogrs.getLong("id"));
  				be.setBlogText(blogrs.getString("blogText"));
  				be.setPublishDate(blogrs.getDate("publishDate"));
  				be.setTitle(blogrs.getString("title"));
  				be.setUpdatedDate(blogrs.getDate("updatedDate"));
  				// find the author email address
  				String author_email = blogrs.getString("AUTHOR_EMAIL");
  				String author_sql = "SELECT * FROM Author a WHERE a.email ='"
  						+ author_email + "'";
  				List<AuthorImpl> authors = findAuthors(author_sql);
  				// there should be just one entry, as email is the primary key
  				// for the Author table
  				if (authors.size() != 1)
  					throw new IllegalArgumentException(
  							"We got more than one author with the same email address. This is wrong");
  				else
  					be.setAuthor(authors.get(0));
  				blogEntryList.add(be);
  			}
  			ppsm.close();
  			connection.close();
  		} catch (Exception e) {
  			e.printStackTrace();
  		}
  		return blogEntryList;
  	}
  }
  