You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@aries.apache.org by jb...@apache.org on 2010/02/24 15:26:33 UTC
svn commit: r915800 -
/incubator/aries/trunk/samples/blog-sample/blog-persistence/src/main/java/org/apache/aries/samples/blog/persistence/BlogPersistenceServiceImpl.java
Author: jbohn
Date: Wed Feb 24 14:26:33 2010
New Revision: 915800
URL: http://svn.apache.org/viewvc?rev=915800&view=rev
Log:
ARIES-199 - it looks like r915049 was intended to revert r914984 but missed one part - BlogPersistenceServiceImpl - I hope it is OK if I fix it to get the build working again
Modified:
incubator/aries/trunk/samples/blog-sample/blog-persistence/src/main/java/org/apache/aries/samples/blog/persistence/BlogPersistenceServiceImpl.java
Modified: incubator/aries/trunk/samples/blog-sample/blog-persistence/src/main/java/org/apache/aries/samples/blog/persistence/BlogPersistenceServiceImpl.java
URL: http://svn.apache.org/viewvc/incubator/aries/trunk/samples/blog-sample/blog-persistence/src/main/java/org/apache/aries/samples/blog/persistence/BlogPersistenceServiceImpl.java?rev=915800&r1=915799&r2=915800&view=diff
==============================================================================
--- incubator/aries/trunk/samples/blog-sample/blog-persistence/src/main/java/org/apache/aries/samples/blog/persistence/BlogPersistenceServiceImpl.java (original)
+++ incubator/aries/trunk/samples/blog-sample/blog-persistence/src/main/java/org/apache/aries/samples/blog/persistence/BlogPersistenceServiceImpl.java Wed Feb 24 14:26:33 2010
@@ -1,207 +1,659 @@
-/**
- * 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;
-
-import java.util.ArrayList;
-import java.util.Date;
-import java.util.List;
-
-import javax.persistence.EntityManager;
-import javax.persistence.PersistenceContext;
-import javax.persistence.Query;
-
-import org.apache.aries.samples.blog.persistence.api.BlogPersistenceService;
-import org.apache.aries.samples.blog.persistence.api.Entry;
-import org.apache.aries.samples.blog.persistence.entity.AuthorImpl;
-import org.apache.aries.samples.blog.persistence.entity.EntryImpl;
-
-/**
- * This class is the implementation of the blogPersistenceService
- */
-public class BlogPersistenceServiceImpl implements BlogPersistenceService {
-
- private EntityManager em;
-
- public BlogPersistenceServiceImpl() {
- }
-
- // Annotation scanning injects us with an EntityManager, the associated
- // persistenceUnit details can be found in the persistence.xml associated
- // with this bundle.
- @PersistenceContext(unitName = "blogExample")
- public void setEntityManager(EntityManager e) {
- em = e;
- }
-
- public void createAuthor(String email, Date dob, String name,
- String displayName, String bio) {
- AuthorImpl a = new AuthorImpl();
- a.setEmail(email);
- a.setName(name);
- a.setDisplayName(displayName);
- a.setBio(bio);
- a.setDob(dob);
- em.persist(a);
-
- }
-
- public void createBlogPost(String authorEmail, String title,
- String blogText, List<String> tags) {
-
- AuthorImpl a = em.find(AuthorImpl.class, authorEmail);
- EntryImpl b = new EntryImpl();
-
- Date publishDate = new Date(System.currentTimeMillis());
-
- b.setBlogText(blogText);
- b.setAuthor(a);
- b.setTitle((title == null) ? "" : title);
- b.setPublishDate(publishDate);
- b.setTags((tags == null) ? new ArrayList<String>() : tags);
-
- a.updateEntries(b);
- em.persist(b);
- em.merge(b.getAuthor());
- }
-
- public Entry findBlogEntryByTitle(String title) {
- Query q = em
- .createQuery("SELECT e FROM BLOGENTRY e WHERE e.title = ?1");
- q.setParameter(1, title);
- Entry b = (Entry) q.getSingleResult();
- return b;
- }
-
- public List<AuthorImpl> getAllAuthors() {
- @SuppressWarnings("unchecked")
- List<AuthorImpl> list = em.createQuery("SELECT a FROM AUTHOR a")
- .getResultList();
-
- return list;
- }
-
- public List<EntryImpl> getAllBlogEntries() {
- @SuppressWarnings("unchecked")
- List<EntryImpl> list = em.createQuery(
- "SELECT b FROM BLOGENTRY b ORDER BY b.publishDate DESC")
- .getResultList();
- return list;
-
- }
-
- public int getNoOfBlogEntries() {
- Number n = (Number) em.createQuery(
- "SELECT COUNT(b) FROM BLOGENTRY b").getSingleResult();
- return n.intValue();
- }
-
- public List<EntryImpl> getBlogEntries(int firstPostIndex, int noOfPosts) {
- Query q = em
- .createQuery("SELECT b FROM BLOGENTRY b ORDER BY b.publishDate DESC");
- q.setFirstResult(firstPostIndex);
- q.setMaxResults(noOfPosts);
-
- @SuppressWarnings("unchecked")
- List<EntryImpl> list = q.getResultList();
-
- return list;
- }
-
- public AuthorImpl getAuthor(String emailAddress) {
- AuthorImpl a = em.find(AuthorImpl.class, emailAddress);
- return a;
- }
-
- public List<EntryImpl> getBlogEntriesModifiedBetween(Date start, Date end) {
- Query q = em
- .createQuery("SELECT b FROM BLOGENTRY b WHERE (b.updatedDate >= :start AND b.updatedDate <= :end) OR (b.publishDate >= :start AND b.publishDate <= :end) ORDER BY b.publishDate ASC");
- q.setParameter("start", start);
- q.setParameter("end", end);
-
- @SuppressWarnings("unchecked")
- List<EntryImpl> list = q.getResultList();
-
- return list;
- }
-
- public List<EntryImpl> getBlogsForAuthor(String emailAddress) {
-
- List<EntryImpl> list = em.find(AuthorImpl.class, emailAddress)
- .getEntries();
-
- return list;
-
- }
-
- public void updateAuthor(String email, Date dob, String name,
- String displayName, String bio) {
- AuthorImpl a = em.find(AuthorImpl.class, email);
- a.setEmail(email);
- a.setName(name);
- a.setDisplayName(displayName);
- a.setBio(bio);
- a.setDob(dob);
- em.merge(a);
- }
-
- public void updateBlogEntry(long id, String email, String title,
- String blogText, List<String> tags, Date updatedDate) {
- EntryImpl b = em.find(EntryImpl.class, id);
- b.setTitle(title);
- b.setBlogText(blogText);
- b.setTags(tags);
- b.setUpdatedDate(updatedDate);
-
- em.merge(b);
- }
-
- public void removeAuthor(String emailAddress) {
- em.remove(em.find(AuthorImpl.class, emailAddress));
- }
-
- public void removeBlogEntry(long id) {
- EntryImpl b = em.find(EntryImpl.class, id);
- b = em.merge(b);
- b.getAuthor().getEntries().remove(b);
-
- em.remove(em.merge(b));
- em.merge(b.getAuthor());
-
- }
-
- public EntryImpl getBlogEntryById(long postId) {
- EntryImpl b = em.find(EntryImpl.class, postId);
- return b;
- }
-
- public void setPublishDate (long postId, Date date) {
- //Added for testing
- EntryImpl b = em.find(EntryImpl.class, postId);
- b.setPublishDate(date);
- em.merge(b);
- }
-
- public void setUpdatedDate (long postId, Date date) {
- //Added for testing
- EntryImpl b = em.find(EntryImpl.class, postId);
- b.setUpdatedDate(date);
- em.merge(b);
- }
-}
+/**
+ * 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;
+
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.text.ParseException;
+import java.util.ArrayList;
+import java.util.Date;
+import java.util.List;
+
+import javax.sql.DataSource;
+
+import org.apache.aries.samples.blog.persistence.api.BlogPersistenceService;
+import org.apache.aries.samples.blog.persistence.entity.AuthorImpl;
+import org.apache.aries.samples.blog.persistence.entity.EntryImpl;
+
+/**
+ * This class is the implementation of the blogPersistenceService
+ */
+public class BlogPersistenceServiceImpl implements BlogPersistenceService {
+ private DataSource dataSource;
+
+
+ /**
+ * set data source
+ */
+ public void setDataSource(DataSource dataSource) {
+ this.dataSource = dataSource;
+ }
+
+ /**
+ * 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;
+ }
+}