diff options
Diffstat (limited to 'src/com/joshwalters/bookcatalog/bookdatabase/BookDatabase.java')
| -rw-r--r-- | src/com/joshwalters/bookcatalog/bookdatabase/BookDatabase.java | 360 |
1 files changed, 360 insertions, 0 deletions
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 |