public static void mains(String[] args)
throws ClassNotFoundException, SQLException, ActiveRecordException {
// Register models
Registration.register(Book.class);
Registration.register(Author.class);
// Register activerecord property binders
ActiveRecordPropertyRegister.add("java.lang.String", new StringProperty());
ActiveRecordPropertyRegister.add("java.lang.Integer", new IntegerProperty());
ActiveRecordPropertyRegister.add(
"jcube.activerecord.main.ActiveRecord", new ActiveRecordReferenceProperty());
Connection.establish();
Book book = new Book();
Book foundBook = book.find().first();
System.out.println("foundBook.id " + foundBook.id);
System.out.println("foundBook.name " + foundBook.name);
System.out.println("foundBook.author.id " + foundBook.author.id);
System.out.println("foundBook.author.name " + foundBook.author.name);
System.out.println("foundBook.another_book.id " + foundBook.another_book.id);
System.out.println("foundBook.another_book.name " + foundBook.another_book.name);
java.sql.Connection connection = Connection.getConnection();
connection.close();
// Connection.closePool();
}
public boolean addBookCopy(String isbn) throws LibrarySystemException {
Book book = searchBook(isbn);
if (book == null)
throw new LibrarySystemException(
"No book with isbn " + isbn + " is in the library collection!");
book.addCopy();
return true;
}
@Override
public void updateStock(List<Book> books) {
for (Book book : books) {
if (getStockByIsbn(book.getIsbn()) == 0) {
if (book.getStock() > 0) {
jdbcTemplate.update(
new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection con)
throws SQLException {
String query = "insert into " + stockTable + " (isbn,stock) values(?,?)";
PreparedStatement ps = con.prepareStatement(query);
ps.setString(1, book.getIsbn());
ps.setInt(2, book.getStock());
return ps;
}
});
}
} else {
if (book.getStock() == 0) {
removeStockEntry(book.getIsbn());
}
jdbcTemplate.update(
new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
String query = "update " + stockTable + " set stock = ? where isbn = ?";
PreparedStatement ps = con.prepareStatement(query);
ps.setInt(1, book.getStock());
ps.setString(2, book.getIsbn());
return ps;
}
});
}
}
}
@Override
public void insertBook(Book book) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String date = sdf.format(book.getPubDate());
this.jdbcTemplate.update(
new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
String query =
"insert into "
+ bookTable
+ " (isbn, title, description, price, publisher, pubdate, edition, pages) values (?, ?, ?, ?, ?, ?, ?, ?);";
PreparedStatement ps = con.prepareStatement(query);
ps.setString(1, book.getIsbn());
ps.setString(2, book.getTitle());
ps.setString(3, book.getDescription());
ps.setDouble(4, book.getPrice());
ps.setString(5, book.getPublisher());
ps.setString(6, date);
ps.setInt(7, book.getEdition());
ps.setInt(8, book.getPages());
return ps;
}
});
List<Category> categories = book.getCategories();
for (Category category : categories) {
this.jdbcTemplate.update(
getPSCForInsertingBookCategory(book.getIsbn(), category.getCategoryId()));
}
List<Author> authors = book.getAuthors();
for (Author author : authors) {
this.jdbcTemplate.update(getPSCForInsertingBookAuthor(book.getIsbn(), author.getAuthorID()));
}
}
public static List<Book> searchBook(String key) {
String sql = "exec searchBook '" + key + "'";
Result result = DBHelper.execSql(sql);
SortedMap[] rows = result.getRows();
List<Book> books = new ArrayList<Book>();
for (SortedMap row : rows) {
Book book = new Book();
book.setId(Integer.parseInt(row.get("id").toString().trim()));
book.setName(row.get("name").toString().trim());
book.setISBN(row.get("ISBN").toString().trim());
book.setAuthor(row.get("author").toString().trim());
book.setDescription(row.get("description").toString().trim());
book.setPublishDate(row.get("publishDate").toString().trim());
book.setQuantity(Integer.parseInt(row.get("quantity").toString().trim()));
System.out.println(book);
books.add(book);
}
return books;
}
@Override
public void updateBook(String oldisbn, Book book) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String date = sdf.format(book.getPubDate());
this.jdbcTemplate.update(
new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
String query =
"update "
+ bookTable
+ " set isbn = ?, title = ?, description = ?, price = ?, publisher = ?, pubDate = ?, edition = ?, pages = ? where isbn = ?";
PreparedStatement ps = con.prepareStatement(query);
ps.setString(1, book.getIsbn());
ps.setString(2, book.getTitle());
ps.setString(3, book.getDescription());
ps.setDouble(4, book.getPrice());
ps.setString(5, book.getPublisher());
ps.setString(6, date);
ps.setInt(7, book.getEdition());
ps.setInt(8, book.getPages());
ps.setString(9, oldisbn);
return ps;
}
});
this.jdbcTemplate.update(getPSCForRemoving(bookCategoriesTable, "isbn", book.getIsbn()));
this.jdbcTemplate.update(getPSCForRemoving(bookAuthorTable, "isbn", book.getIsbn()));
List<Category> categories = book.getCategories();
for (Category category : categories) {
this.jdbcTemplate.update(
getPSCForInsertingBookCategory(book.getIsbn(), category.getCategoryId()));
}
List<Author> authors = book.getAuthors();
for (Author author : authors) {
this.jdbcTemplate.update(getPSCForInsertingBookAuthor(book.getIsbn(), author.getAuthorID()));
}
}
/** Construct a search using the advanced search fields and invoke a search. */
private void buildSearchFromForms() {
Book searchBook = new Book();
boolean anythingSearched = false;
/*
* Get Title from field
*/
if (!titleField.getText().isEmpty()) {
anythingSearched = true;
searchBook.title = titleField.getText();
}
/*
* Get Author from field
*/
if (!authorField.getText().isEmpty()) {
anythingSearched = true;
searchBook.author = authorField.getText();
}
/*
* Get Keywords from field
*/
if (!keywordField.getText().isEmpty()) {
anythingSearched = true;
searchBook.description = keywordField.getText();
}
/*
* Get ISBN from field
*/
if (!isbnField.getText().isEmpty()) {
anythingSearched = true;
searchBook.ISBN = Integer.parseInt(isbnField.getText());
}
/*
* If all the fields were blank, don't search
*/
if (!anythingSearched) {
JOptionPane.showMessageDialog(
null, "Nothing Searched", "No Values", JOptionPane.INFORMATION_MESSAGE);
} else {
try {
/*
* Perform a search
*/
ArrayList<Book> books = Controller.searchForBook(searchBook);
/*
* Show the results
*/
PanelsManager.newSearchResults(books);
} catch (Exception e) {
/*
* Print error message if error occurs
*/
e.printStackTrace();
PanelsManager.displayError("Book Search failed\n" + "Contact tech support");
}
}
}
@Override
public Book mapRow(ResultSet rs, int rowNum) throws SQLException {
Book book = new Book();
book.setIsbn(rs.getString("ISBN"));
book.setTitle(rs.getString("title"));
book.setDescription(rs.getString("description"));
book.setPrice(rs.getDouble("price"));
book.setPublisher(rs.getString("publisher"));
book.setPubDate(rs.getDate("pubdate"));
book.setEdition(rs.getInt("edition"));
book.setPages(rs.getInt("pages"));
book.setStock(rs.getInt("stock"));
book.setCategories(getCategoriesbyIsbn(book.getIsbn()));
book.setAuthors(getAuthorsbyIsbn(book.getIsbn()));
return book;
}
/**
* DANIEL: THIS IS IMPORTANnew BadUserIDExceptionT the books will be passed with the default copy
* no (0) If it already exists, please throw a new BadCopyNumberException with the first free
* copyno included. Might want to search for # of copies first, rather than trying to sort through
* the SQLExceptions coming back to see if that was the reason
*
* @param newBook - the book to be added to the DB
* @throws SQLException - if Oracle complains
* @throws BadCopyNumberException - if the copy number is already taken.
*/
public int createNewBook(Book newBook) throws SQLException, BadCopyNumberException {
try {
String statement;
ResultSet rs;
// Copy Number
// Check if there is a
statement =
"select count(*) from BookCopy where callnumber ='"
+ newBook.getCallNumber()
+ "' and copyNo="
+ newBook.getCopyNo();
rs = sql(statement, SQLType.query);
rs.next();
int copyCount = rs.getInt(1);
if (copyCount == 0) {
// book
statement =
"INSERT INTO Book VALUES ('"
+ newBook.getCallNumber()
+ "', "
+ newBook.getISBN()
+ ", '"
+ newBook.getTitle()
+ "', '"
+ newBook.getMainAuthor()
+ "', '"
+ newBook.getPublisher()
+ "', "
+ newBook.getYear()
+ ")";
System.out.println(statement);
System.out.println(newBook.getCopyNo());
sql(statement, SQLType.insert);
// CopyNo
statement =
"INSERT INTO BookCopy VALUES ('"
+ newBook.getCallNumber()
+ "',"
+ newBook.getCopyNo()
+ ",'in')";
System.out.println(statement);
sql(statement, SQLType.insert);
System.out.println(statement);
// Author
// Add Main Author
statement =
"INSERT INTO HasAuthor VALUES ('"
+ newBook.getCallNumber()
+ "', '"
+ newBook.getMainAuthor()
+ "')";
System.out.println(statement);
sql(statement, SQLType.insert);
System.out.println("Size: " + newBook.getAuthors().size());
// Add Secondary Authors
for (String s : newBook.getAuthors()) {
statement =
"INSERT INTO HasAuthor VALUES ('" + newBook.getCallNumber() + "', '" + s + "')";
System.out.println(statement);
sql(statement, SQLType.insert);
}
// Subject
for (String s : newBook.getSubjects()) {
s = s.toLowerCase();
statement =
"INSERT INTO HasSubject VALUES ('" + newBook.getCallNumber() + "', '" + s + "')";
System.out.println(statement);
sql(statement, SQLType.insert);
}
} else if (copyCount >= 1) {
System.out.println("Test: " + newBook.getCopyNo());
statement =
"select max(copyNo) from bookCopy where callNumber='" + newBook.getCallNumber() + "'";
rs = sql(statement, SQLType.query);
rs.next();
copyCount = rs.getInt(1);
String message =
"The copy number"
+ newBook.getCopyNo()
+ "does not exist. The next available copy number is: ";
copyCount++;
statement =
"INSERT INTO BookCopy VALUES ('"
+ newBook.getCallNumber()
+ "', "
+ copyCount
+ ", "
+ "'in' "
+ ")";
System.out.println(statement);
sql(statement, SQLType.insert);
throw new BadCopyNumberException(message, copyCount);
} else {
throw new SQLException("wasn't suposed to get here");
}
return newBook.getCopyNo();
} catch (BadCopyNumberException BDCPY) {
throw BDCPY;
}
}