Initial commit of code.

3 files changed, 428 insertions, 0 deletions

diff --git a/src/com/joshwalters/bookcatalog/bookdatabase/BookAlreadyInDatabase.java b/src/com/joshwalters/bookcatalog/bookdatabase/BookAlreadyInDatabase.java
new file mode 100644
index 0000000..b6428ef
--- /dev/null
+++ b/src/com/joshwalters/bookcatalog/bookdatabase/BookAlreadyInDatabase.java
@@ -0,0 +1,34 @@
+/*
+ *      Book Catalog - Catalog your book collection.
+ *      
+ *      Copyright (C) 2009 Joshua Walters
+ *      URL: http://joshwalters.com
+ *      
+ *      This file is part of Book Catalog.
+ *      
+ *      Book Catalog is free software: you can redistribute it and/or modify
+ *      it under the terms of the GNU General Public License as published by
+ *      the Free Software Foundation, either version 3 of the License, or
+ *      (at your option) any later version.
+ *      
+ *      Book Catalog is distributed in the hope that it will be useful,
+ *      but WITHOUT ANY WARRANTY; without even the implied warranty of
+ *      MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+ *      GNU General Public License for more details.
+ *      
+ *      You should have received a copy of the GNU General Public License
+ *      along with Book Catalog.  If not, see <http://www.gnu.org/licenses/>.
+ */
+
+package com.joshwalters.bookcatalog.bookdatabase;
+
+/**
+ * Thrown if the book is already in the database.
+ * 
+ * @author Josh Walters
+ */
+public class BookAlreadyInDatabase extends Exception {
+
+	/** The serial ID */
+	private static final long serialVersionUID = -433273330040981967L;
+}
\ No newline at end of file
diff --git a/src/com/joshwalters/bookcatalog/bookdatabase/BookDatabase.java b/src/com/joshwalters/bookcatalog/bookdatabase/BookDatabase.java
new file mode 100644
index 0000000..9f06aba
--- /dev/null
+++ b/src/com/joshwalters/bookcatalog/bookdatabase/BookDatabase.java
@@ -0,0 +1,360 @@
+/*

+ *      Book Catalog - Catalog your book collection.

+ *      

+ *      Copyright (C) 2009 Joshua Walters

+ *      URL: http://joshwalters.com

+ *      

+ *      This file is part of Book Catalog.

+ *      

+ *      Book Catalog is free software: you can redistribute it and/or modify

+ *      it under the terms of the GNU General Public License as published by

+ *      the Free Software Foundation, either version 3 of the License, or

+ *      (at your option) any later version.

+ *      

+ *      Book Catalog is distributed in the hope that it will be useful,

+ *      but WITHOUT ANY WARRANTY; without even the implied warranty of

+ *      MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

+ *      GNU General Public License for more details.

+ *      

+ *      You should have received a copy of the GNU General Public License

+ *      along with Book Catalog.  If not, see <http://www.gnu.org/licenses/>.

+ */

+

+package com.joshwalters.bookcatalog.bookdatabase;

+

+import java.sql.Connection;

+import java.sql.DriverManager;

+import java.sql.PreparedStatement;

+import java.sql.ResultSet;

+import java.sql.SQLException;

+import java.sql.Statement;

+import java.util.Vector;

+

+/**

+ * Manages the database of books.

+ * 

+ * @author Josh Walters

+ */

+public class BookDatabase {

+

+	/** The SQL statement. */

+	private Statement statement;

+	/** The SQL result set. */

+	private ResultSet resultSet;

+	/** The connection to the SQL database. */

+	private Connection connection;

+

+	/**

+	 * Automatically disconnect from the database.

+	 */

+	protected void finalize() {

+		disconnectFromDatabase();

+	}

+

+	/**

+	 * Connects to the database. If the table for the record of books does not

+	 * exist yet, then it will be created.

+	 * 

+	 * @throws SQLException

+	 * @throws ClassNotFoundException

+	 */

+	public void connectToDatabase() throws SQLException, ClassNotFoundException {

+		try {

+			// Connect to the database.

+			Class.forName("org.sqlite.JDBC");

+			// Create the database file.

+			connection = DriverManager

+					.getConnection("jdbc:sqlite:bookcatalog.db");

+			statement = connection.createStatement();

+		} catch (SQLException e) {

+			throw e;

+		} catch (ClassNotFoundException e) {

+			throw e;

+		}

+		// Set the timeout to unlimited.

+		statement.setQueryTimeout(0);

+		// Create the table if it does not exist.

+		statement.executeUpdate("create table if not exists bookcatalog("

+				+ "Title text, Author text, Date text,"

+				+ "Description text, ISBN text, Price text,"

+				+ "Publisher text, Subject text, Notes text)");

+	}

+

+	/**

+	 * Check to see if a book is in the database.

+	 * 

+	 * @param ISBN

+	 * @return True if book is in database, false if not.

+	 * @throws SQLException

+	 */

+	public boolean isBookInDatabase(String ISBN) throws SQLException {

+		// The SQL statement.

+		PreparedStatement preparedStatement = connection

+				.prepareStatement("select * from bookcatalog where ISBN = ?");

+		preparedStatement.setString(1, ISBN);

+		resultSet = preparedStatement.executeQuery();

+		boolean doesBookExist = resultSet.next();

+		resultSet.close();

+		return doesBookExist;

+	}

+

+	/**

+	 * Check if a book in the database has a price assigned to it.

+	 * 

+	 * @param ISBN

+	 * @return True if the book has price associated with it. False if not.

+	 * @throws SQLException

+	 */

+	boolean bookHasPrice(String ISBN) throws SQLException {

+		// The SQL statement

+		PreparedStatement preparedStatement = connection

+				.prepareStatement("select Price from bookcatalog where ISBN = ?");

+		preparedStatement.setString(1, ISBN);

+		resultSet = preparedStatement.executeQuery();

+		resultSet.next();

+		// Get the price value

+		String bookPriceInCatalog = resultSet.getString(1);

+		resultSet.close();

+		if (bookPriceInCatalog == null) {

+			return false;

+		} else {

+			return true;

+		}

+	}

+

+	/**

+	 * Sets the price for a book.

+	 * 

+	 * @param ISBN

+	 * @param Price

+	 * @throws SQLException

+	 */

+	void setBookPrice(String ISBN, String Price) throws SQLException {

+		// The SQL statement

+		PreparedStatement preparedStatement = connection

+				.prepareStatement("update bookcatalog set Price = ? where ISBN = ?");

+		preparedStatement.setString(1, Price);

+		preparedStatement.setString(2, ISBN);

+		if (preparedStatement.execute()) {

+			resultSet = statement.getResultSet();

+			if (resultSet != null) {

+				resultSet.close();

+			}

+		}

+	}

+

+	/**

+	 * Delete a book from the database.

+	 * 

+	 * @param ISBN

+	 *            Used to identify the book to delete.

+	 * @throws SQLException

+	 */

+	public void deleteBook(String ISBN) throws SQLException {

+		// The SQL statement

+		PreparedStatement preparedStatement = connection

+				.prepareStatement("delete from bookcatalog where ISBN = ?");

+		preparedStatement.setString(1, ISBN);

+		if (preparedStatement.execute()) {

+			resultSet = statement.getResultSet();

+			if (resultSet != null) {

+				resultSet.close();

+			}

+		}

+	}

+

+	/**

+	 * Updates a book entry with new information.

+	 * 

+	 * @param Title

+	 * @param Author

+	 * @param Date

+	 * @param Description

+	 * @param ISBN

+	 * @param Price

+	 * @param Publisher

+	 * @param Subject

+	 * @param Notes

+	 * @throws SQLException

+	 */

+	public void updateBook(String Title, String Author, String Date,

+			String Description, String ISBN, String Price, String Publisher,

+			String Subject, String Notes) throws SQLException {

+		// The SQL statement

+		PreparedStatement preparedStatement = connection

+				.prepareStatement("update bookcatalog set Title = ?, Author = ?, Date = ?, Description = ?, Price = ?, Publisher = ?, Subject = ?, Notes = ? where ISBN = ?");

+		preparedStatement.setString(1, Title);

+		preparedStatement.setString(2, Author);

+		preparedStatement.setString(3, Date);

+		preparedStatement.setString(4, Description);

+		preparedStatement.setString(5, Price);

+		preparedStatement.setString(6, Publisher);

+		preparedStatement.setString(7, Subject);

+		preparedStatement.setString(8, Notes);

+		preparedStatement.setString(9, ISBN);

+		if (preparedStatement.execute()) {

+			resultSet = statement.getResultSet();

+			if (resultSet != null) {

+				resultSet.close();

+			}

+		}

+	}

+

+	/**

+	 * Inserts a book into the database.

+	 * 

+	 * @param Title

+	 * @param Author

+	 * @param Date

+	 * @param Description

+	 * @param ISBN

+	 * @param Price

+	 * @param Publisher

+	 * @param Subject

+	 * @throws SQLException

+	 * @throws BookAlreadyInDatabase

+	 */

+	public void insertBook(String Title, String Author, String Date,

+			String Description, String ISBN, String Price, String Publisher,

+			String Subject) throws SQLException, BookAlreadyInDatabase {

+		// Check to see if the book is in the database

+		if (!isBookInDatabase(ISBN)) {

+			// The SQL statement

+			PreparedStatement preparedStatement = connection

+					.prepareStatement("insert into bookcatalog values(?,?,?,?,?,?,?,?,?);");

+			// Set the values for the prepared statement

+			preparedStatement.setString(1, Title);

+			preparedStatement.setString(2, Author);

+			preparedStatement.setString(3, Date);

+			preparedStatement.setString(4, Description);

+			preparedStatement.setString(5, ISBN);

+			preparedStatement.setString(6, Price);

+			preparedStatement.setString(7, Publisher);

+			preparedStatement.setString(8, Subject);

+			// Set the notes for the book blank by default.

+			preparedStatement.setString(9, "");

+			if (preparedStatement.execute()) {

+				resultSet = statement.getResultSet();

+				if (resultSet != null) {

+					resultSet.close();

+				}

+			}

+		} else {

+			// If the book exists in the database check to see if it has a price

+			// assigned to it

+			if (!bookHasPrice(ISBN) && Price != null) {

+				// If the book doesn't have a price associated with it, give it

+				// one.

+				setBookPrice(ISBN, Price);

+			} else {

+				// The book is already in the database and we don't have/need

+				// the price.

+				throw new BookAlreadyInDatabase();

+			}

+		}

+	}

+

+	/**

+	 * Get the database in a multidimensional string for use with a Swing table.

+	 * 

+	 * @return The database in a multidimensional string.

+	 * @throws SQLException

+	 * @throws EmptyDatabase

+	 */

+	public String[][] getDatabaseForTable() throws SQLException, EmptyDatabase {

+		// Get the number of books in the database.

+		resultSet = statement

+				.executeQuery("select count(ISBN) from bookcatalog");

+		if (resultSet.next()) {

+			int rows = resultSet.getInt(1);

+			resultSet.close();

+			// Select all the data in the database.

+			resultSet = statement.executeQuery("select * from bookcatalog");

+			// Create a new multidimensional string that will store the

+			// database.

+			String data[][] = new String[rows][9];

+			resultSet.next();

+			// Iterate over the database and store the data in the string.

+			for (int i = 0; i < rows; i++) {

+				for (int j = 1; j <= 9; j++) {

+					data[i][j - 1] = resultSet.getString(j);

+				}

+				resultSet.next();

+			}

+			resultSet.close();

+			// Return the database in string format.

+			return data;

+		}

+		throw new EmptyDatabase();

+	}

+

+	/**

+	 * This gets the database for a Swing table, but limits the results to

+	 * entries that match the search string.

+	 * 

+	 * @param searchString

+	 * @return

+	 * @throws SQLException

+	 * @throws EmptyDatabase

+	 */

+	public String[][] searchDatabaseForTable(String searchString)

+			throws SQLException, EmptyDatabase {

+		String[][] data = null;

+		Vector<String> cells = new Vector<String>();

+		// Remove all percent signs from the string.

+		searchString = searchString.replace("\\%", "");

+		// Add percent signs at beginning and end of string.

+		searchString = "%" + searchString + "%";

+		// Replace space characters with percent signs.

+		searchString = searchString.replace(" ", "%");

+		/*

+		 * Look for entries in the database that are similar to the search

+		 * string.

+		 */

+		PreparedStatement preparedStatement = connection

+				.prepareStatement("select * from bookcatalog where Title like ? or Author like ? or Date like ? or Description like ? or ISBN like ? or Price like ? or Publisher like ? or Subject like ? or Notes like ?");

+		preparedStatement.setString(1, searchString);

+		preparedStatement.setString(2, searchString);

+		preparedStatement.setString(3, searchString);

+		preparedStatement.setString(4, searchString);

+		preparedStatement.setString(5, searchString);

+		preparedStatement.setString(6, searchString);

+		preparedStatement.setString(7, searchString);

+		preparedStatement.setString(8, searchString);

+		preparedStatement.setString(9, searchString);

+		resultSet = preparedStatement.executeQuery();

+		// Store the result data in a vector.

+		while (resultSet.next()) {

+			for (int i = 1; i <= 9; i++) {

+				cells.add(resultSet.getString(i));

+			}

+		}

+		// Create a new multidimensional string to store the database search

+		// result.

+		data = new String[cells.size() / 9][9];

+		// Store the results in the string.

+		for (int i = 0; i < cells.size() / 9; i++) {

+			for (int j = 0; j < 9; j++) {

+				data[i][j] = (String) cells.get((i * 9) + j);

+			}

+		}

+		// Return the data in string format.

+		return data;

+	}

+

+	/**

+	 * Disconnect from the database. This will be called upon GC, but is best to

+	 * call it manually.

+	 * 

+	 * @throws SQLException

+	 */

+	public void disconnectFromDatabase() {

+		try {

+			if (connection.isClosed() != true) {

+				connection.close();

+			}

+		} catch (SQLException e) {

+			// Do nothing

+		}

+	}

+}
\ No newline at end of file
diff --git a/src/com/joshwalters/bookcatalog/bookdatabase/EmptyDatabase.java b/src/com/joshwalters/bookcatalog/bookdatabase/EmptyDatabase.java
new file mode 100644
index 0000000..1ee98bf
--- /dev/null
+++ b/src/com/joshwalters/bookcatalog/bookdatabase/EmptyDatabase.java
@@ -0,0 +1,34 @@
+/*
+ *      Book Catalog - Catalog your book collection.
+ *      
+ *      Copyright (C) 2009 Joshua Walters
+ *      URL: http://joshwalters.com
+ *      
+ *      This file is part of Book Catalog.
+ *      
+ *      Book Catalog is free software: you can redistribute it and/or modify
+ *      it under the terms of the GNU General Public License as published by
+ *      the Free Software Foundation, either version 3 of the License, or
+ *      (at your option) any later version.
+ *      
+ *      Book Catalog is distributed in the hope that it will be useful,
+ *      but WITHOUT ANY WARRANTY; without even the implied warranty of
+ *      MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+ *      GNU General Public License for more details.
+ *      
+ *      You should have received a copy of the GNU General Public License
+ *      along with Book Catalog.  If not, see <http://www.gnu.org/licenses/>.
+ */
+
+package com.joshwalters.bookcatalog.bookdatabase;
+
+/**
+ * Thrown if the database is empty.
+ * 
+ * @author Josh Walters
+ */
+public class EmptyDatabase extends Exception {
+
+	/** The serial ID */
+	private static final long serialVersionUID = 7140054511201111418L;
+}
\ No newline at end of file