From b6869036b7839b4540cee1114f44bc5c9b33d9c4 Mon Sep 17 00:00:00 2001 From: Josh Walters Date: Tue, 13 Dec 2011 17:35:57 -0800 Subject: Initial commit of code. --- .../bookdatabase/BookAlreadyInDatabase.java | 34 ++ .../bookcatalog/bookdatabase/BookDatabase.java | 360 +++++++++++++++++++++ .../bookcatalog/bookdatabase/EmptyDatabase.java | 34 ++ 3 files changed, 428 insertions(+) create mode 100644 src/com/joshwalters/bookcatalog/bookdatabase/BookAlreadyInDatabase.java create mode 100644 src/com/joshwalters/bookcatalog/bookdatabase/BookDatabase.java create mode 100644 src/com/joshwalters/bookcatalog/bookdatabase/EmptyDatabase.java (limited to 'src/com/joshwalters/bookcatalog/bookdatabase') 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 . + */ + +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 . + */ + +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 cells = new Vector(); + // 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 . + */ + +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 -- cgit v1.2.3-24-g4f1b