This assignment will have you create tables and populate them using SQL. You wil
ID: 3536204 • Letter: T
Question
This assignment will have you create tables and
populate them using SQL. You will then use Java to
connect to the database and run queries against them, displaying
results in a JTable.
As you are modifying LibrarySystem.java, you may choose to create
an Eclipse project so you can compile and modify it there as
normal, but to compile it, you will need to add these .jar files to
your Eclipse path: derby.jar and derbytools.jar - to
test the database part of the application, you MUST run the command
line utility, run.bat. I don't know how to add these jar files to
the question but if you need them I will be happy to find a way to
send them to you. I greatly appreciate all help. Thanks!
1. Run the setup script, setup.bat.
"echo off
echo Setting Up Derby Classpath.
set CLASSPATH=libderby.jar;libderbytools.jar;.
echo Creating Database(BooksDB) and Tables prepopulated with
data.
java -Dij.database=jdbc:derby:BooksDB;create=true
org.apache.derby.tools.ij sqlooksTables.sql >
week14_jdbc.log
echo Database created..."
2. Open the file: sqlooksTables.sql.
Add an SQL CREATE TABLE statement that will create a database table
holding check-out information. Include the following fields
required for checkin/out information (you may wish to add
more):
Table Name:
CHECK_OUTS
Fields: ISBN,
PATRON_SSN, DUE_DATE (ISBN should be the only primary key)
- Note how the existing SQL statements in this file are formatted
with a semicolon character at the end of each statement. Make sure
that your CREATE TABLE statement ends with this semicolon as
well.
"
DROP TABLE BOOKS;
DROP TABLE PATRONS;
DROP TABLE CHECK_OUTS;
CREATE TABLE BOOKS
(
ISBN
INTEGER CONSTRAINT BOOKS_PK PRIMARY KEY,
TITLE
VARCHAR(1000),
AUTHOR_LAST_NAME
VARCHAR(200),
AUTHOR_FIRST_NAME VARCHAR(200)
);
-- Insert a few default records into this table
INSERT INTO BOOKS
(ISBN, TITLE, AUTHOR_LAST_NAME,
AUTHOR_FIRST_NAME)
VALUES (123456, 'Absolute Java', 'Savitch',
'Walter');
INSERT INTO BOOKS
(ISBN, TITLE, AUTHOR_LAST_NAME,
AUTHOR_FIRST_NAME)
VALUES (098765, 'The Lost Symbol', 'Brown',
'Dan');
INSERT INTO BOOKS
(ISBN, TITLE, AUTHOR_LAST_NAME,
AUTHOR_FIRST_NAME)
VALUES (555433, 'Fast Food Nation', 'Schlosser',
'Eric');
COMMIT;
CREATE TABLE PATRONS
(
SSN
VARCHAR(11) CONSTRAINT PATRONS_PK PRIMARY KEY,
LAST_NAME
VARCHAR(200),
FIRST_NAME VARCHAR(200),
DOB
DATE
);
-- insert some default PATRON information
-- create the CHECK_OUTS table with at least the following
fields:
-- ISBN (primary key), SSN (of the patron),
DUE_DATE
4) INSERT statements have been provided for the BOOKS table. Create
SQL statements to insert at least 3 records into the PATRONS table.
Records for CHECK_OUTS are optional.
5) Re-run the file, setup.bat to implement these changes - DO NOT
run this file again, or all records will be dropped!
6) Update the Java class file, javaLibrarySystem.java where you
see a comment such as "// - complete this". You will be
creating a JDBC connection to the database using the connection
driver, "org.apache.derby.jdbc.EmbeddedDriver" and the protocol
"jdbc:derby:" to retrieve and write to the database.
"/**
* LibrarySystem.java
* Main program to test reading/writing library system
information to/from a
* database.
* @author Instructor Chemerys
*/
import java.awt.BorderLayout;
import java.awt.Color;
import java.awt.Dimension;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Arrays;
import java.util.Vector;
import javax.swing.BorderFactory;
import javax.swing.Box;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTabbedPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.table.DefaultTableModel;
public class LibrarySystem extends JFrame {
public static final String
MAIN_TAB = "Inventory";
public static final String
ADD_PATRON_TAB = "Add Patron";
public static final String
ADD_BOOK_TAB = "Add Book";
public static final String
CHECKOUT_TAB = "Check Out";
public static final String
CHECKIN_TAB = "Check In";
public static final
Dimension FIELD_SIZE = new Dimension(350, 20);
public static final
String[] INVENTORY_COLUMN_NAMES = new String[] {
"TITLE", "AUTHOR", "ISBN", "BORROWER", "DUE_DATE"};
protected DefaultTableModel
booksTableModel;
protected JTabbedPane
mainTab;
protected JTextField
fName;
protected JTextField
lName;
protected JTextField
dob;
protected JTextField
ssn;
protected JTextField
addIsbn;
protected JTextField
title;
protected JTextField
authorLName;
protected JTextField
authorFName;
protected JTextField
checkinBookIsbn;
protected JTextField
checkoutSsn;
protected JTextField
checkoutBookIsbn;
/** Constructor to display
the main GUI. */
public LibrarySystem()
{
setTitle("Blue Ridge Library System");
getContentPane().setLayout(new BorderLayout());
getContentPane().add(createMainPanel(), BorderLayout.CENTER);
pack();
setVisible(true);
}
/** Create a simple
banner.
*/
protected JPanel
createHeaderPanel() {
JPanel northPanel = new JPanel(new BorderLayout());
northPanel.setBackground(new Color(173, 216, 230));
northPanel.setBorder(BorderFactory.createEmptyBorder(3,3,3,3));
northPanel.add(new JLabel("Blue Ridge Library System"),
JLabel.CENTER);
return northPanel;
}
/** Create GUI panel to
display basic functions of a library system.
*
@return JPanel
*/
public JTabbedPane
createMainPanel() {
mainTab = new JTabbedPane();
mainTab.add(createMainTab(), MAIN_TAB);
mainTab.add(createPatronTab(), ADD_PATRON_TAB);
mainTab.add(createBookTab(), ADD_BOOK_TAB);
mainTab.add(createCheckinTab(), CHECKIN_TAB);
mainTab.add(createCheckoutTab(), CHECKOUT_TAB);
return mainTab;
}
/** Create the main panel
which displays the summary of all books in
* the system and who they are currently
checked out to (or if they
* are not checked out.
*/
protected JPanel
createMainTab() {
// create an empty non-editable table with default column
names
JTable booksTable = new
JTable();
booksTable.setAutoResizeMode(JTable.AUTO_RESIZE_ALL_COLUMNS);
booksTableModel = new DefaultTableModel(null, // will call
getBooks() below
new Vector(Arrays.asList(INVENTORY_COLUMN_NAMES))) { // column
names
// Overridden method to indicate that no cells are editable
public boolean isCellEditable(int row, int column) {
return false;
}
};
booksTable.setModel(booksTableModel);
refreshInventory(); // set initial data
JPanel buttonPanel = new JPanel();
JButton refreshButton = new JButton("Refresh Inventory");
refreshButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
refreshInventory();
}
});
buttonPanel.add(refreshButton);
JPanel mainPanel = new JPanel(new
BorderLayout());
mainPanel.setBorder(BorderFactory.createTitledBorder(
"Current Inventory Status"));
mainPanel.add(new JScrollPane(booksTable),
BorderLayout.CENTER);
mainPanel.add(buttonPanel, BorderLayout.SOUTH);
return mainPanel;
}
/** Creates the panel that
displays controls to add a new borrower to
* the library system.
*/
protected JPanel
createPatronTab() {
fName = new JTextField();
lName = new JTextField();
ssn = new JTextField();
dob = new JTextField();
// set the size for one field, when using GridLayout all others
will match
fName.setPreferredSize(FIELD_SIZE);
JPanel labelPanel = new JPanel(new GridLayout(4, 1));
labelPanel.add(new JLabel("First Name: ", JLabel.RIGHT));
labelPanel.add(new JLabel("Last Name: ", JLabel.RIGHT));
labelPanel.add(new JLabel("Date of Birth: ", JLabel.RIGHT));
labelPanel.add(new JLabel("SSN: ", JLabel.RIGHT));
JPanel entryPanel = new JPanel(new GridLayout(4, 1));
entryPanel.add(fName);
entryPanel.add(lName);
entryPanel.add(dob);
entryPanel.add(ssn);
Box entryBox = Box.createHorizontalBox();
entryBox.add(labelPanel);
entryBox.createHorizontalStrut(15);
entryBox.add(entryPanel);
JPanel buttonPanel = new JPanel();
JButton submitButton = new JButton("Add Patron");
submitButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
if(addPatron()) {
fName.setText("");
lName.setText("");
ssn.setText("");
dob.setText("");
refreshInventory();
}
}
});
buttonPanel.add(submitButton);
JPanel wPanel = new JPanel(new BorderLayout());
wPanel.setBorder(BorderFactory.createTitledBorder("Enter Patron
Information: "));
wPanel.add(entryBox, BorderLayout.WEST);
JPanel panel = new JPanel(new BorderLayout());
panel.add(wPanel, BorderLayout.NORTH);
panel.add(buttonPanel, BorderLayout.SOUTH);
return panel;
}
/** Creates the panel that
displays controls to add a new book to
* the library system.
*/
protected JPanel
createBookTab() {
addIsbn = new JTextField();
title = new JTextField();
authorLName = new JTextField();
authorFName = new JTextField();
// set the size for one field, when using GridLayout all others
will match
addIsbn.setPreferredSize(FIELD_SIZE);
JPanel labelPanel = new JPanel(new GridLayout(4, 1));
labelPanel.add(new JLabel("ISBN: ", JLabel.RIGHT));
labelPanel.add(new JLabel("Title: ", JLabel.RIGHT));
labelPanel.add(new JLabel("Author's Last Name: ",
JLabel.RIGHT));
labelPanel.add(new JLabel("Author's First Name: ",
JLabel.RIGHT));
JPanel entryPanel = new JPanel(new GridLayout(4, 1));
entryPanel.add(addIsbn);
entryPanel.add(title);
entryPanel.add(authorLName);
entryPanel.add(authorFName);
Box entryBox = Box.createHorizontalBox();
entryBox.add(labelPanel);
entryBox.createHorizontalStrut(15);
entryBox.add(entryPanel);
JPanel buttonPanel = new JPanel();
JButton submitButton = new JButton("Add Book");
submitButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
if(addBook()) {
addIsbn.setText("");
title.setText("");
authorLName.setText("");
authorFName.setText("");
refreshInventory();
}
}
});
buttonPanel.add(submitButton);
JPanel wPanel = new JPanel(new BorderLayout());
wPanel.setBorder(BorderFactory.createTitledBorder("Enter Book
Information: "));
wPanel.add(entryBox, BorderLayout.WEST);
JPanel panel = new JPanel(new BorderLayout());
panel.add(wPanel, BorderLayout.NORTH);
panel.add(buttonPanel, BorderLayout.SOUTH);
return panel;
}
/** Creates the panel that
displays controls to check a book back in to
* the library system.
*/
protected JPanel
createCheckinTab() {
checkinBookIsbn = new JTextField();
checkinBookIsbn.setPreferredSize(FIELD_SIZE);
JPanel labelPanel = new JPanel(new GridLayout(1, 1));
labelPanel.add(new JLabel("ISBN: ", JLabel.RIGHT));
JPanel entryPanel = new JPanel(new GridLayout(1, 1));
entryPanel.add(checkinBookIsbn);
Box entryBox = Box.createHorizontalBox();
entryBox.add(labelPanel);
entryBox.createHorizontalStrut(15);
entryBox.add(entryPanel);
JPanel buttonPanel = new JPanel();
JButton submitButton = new JButton("Checkin Book");
submitButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
if(checkin()) {
checkinBookIsbn.setText("");
refreshInventory();
}
}
});
buttonPanel.add(submitButton);
JPanel wPanel = new JPanel(new BorderLayout());
wPanel.setBorder(BorderFactory.createTitledBorder("Enter ISBN of
Book being checked in: "));
wPanel.add(entryBox, BorderLayout.WEST);
JPanel panel = new JPanel(new BorderLayout());
panel.add(wPanel, BorderLayout.NORTH);
panel.add(buttonPanel, BorderLayout.SOUTH);
return panel;
}
/** Creates the panel that
displays controls to check a book out of
* the library system.
*/
protected JPanel
createCheckoutTab() {
checkoutBookIsbn = new JTextField();
checkoutSsn = new JTextField();
checkoutBookIsbn.setPreferredSize(FIELD_SIZE);
JPanel labelPanel = new JPanel(new GridLayout(2, 1));
labelPanel.add(new JLabel("ISBN: ", JLabel.RIGHT));
labelPanel.add(new JLabel("Patron SSN: ", JLabel.RIGHT));
JPanel entryPanel = new JPanel(new GridLayout(2, 1));
entryPanel.add(checkoutBookIsbn);
entryPanel.add(checkoutSsn);
Box entryBox = Box.createHorizontalBox();
entryBox.add(labelPanel);
entryBox.createHorizontalStrut(15);
entryBox.add(entryPanel);
JPanel buttonPanel = new JPanel();
JButton submitButton = new JButton("Checkout Book");
submitButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
if(checkout()) {
checkoutBookIsbn.setText("");
checkoutSsn.setText("");
refreshInventory();
}
}
});
buttonPanel.add(submitButton);
JPanel wPanel = new JPanel(new BorderLayout());
wPanel.setBorder(BorderFactory.createTitledBorder("Enter
information for book being checked out: "));
wPanel.add(entryBox, BorderLayout.WEST);
JPanel panel = new JPanel(new BorderLayout());
panel.add(wPanel, BorderLayout.NORTH);
panel.add(buttonPanel, BorderLayout.SOUTH);
return panel;
}
/** Method to retrieve a
listing of all books in the library system
* along with the borrower that currently
has the book checked out,
* or an empty cell if the book is
available.
*
This method will be called after every action (Add Patron, Add
Book,
* etc..) and when explicitly
refreshed on the main tab.
* @return
Vector - Data to populate the booksTableModel
* Each Vector contains a single row of
Data added to the larger
* Vector of data to populate the
table
*/
protected void
refreshInventory() {
// - create the SQL statement passed to
executeQuery()
// the column order should match
INVENTORY_COLUMN_NAMES
Vector tableData = executeQuery(sql);
if(tableData != null) {
booksTableModel.setDataVector(tableData,
new Vector(Arrays.asList(INVENTORY_COLUMN_NAMES)));
}
}
/** Shared method to
execute any SQL statement and return a Vector of
* Vectors that can be used to populate a
simple JTable.
* @param
sql
* @return
Vector of Vectors
*/
protected Vector
executeQuery(String sql) {
// , complete this method to create the SQL
connection
// and retrieve results from a call to
Statement.executeQuery()
// Use the ResultSet to iterator through each row, adding to
// a Vector of Vectors used to populate a table
model
// To make this method reusable, use ResultSet.getObject() to
retrieve values
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
Vector tableData = new
Vector();
}
/** - complete this method.
* @return TRUE
if add was successful, FALSE otherwise
*/
protected boolean
addPatron() {
}
/** - complete this method.
* @return TRUE
if add was successful, FALSE otherwise
*/
protected boolean addBook()
{
}
/** - complete this method.
* @return TRUE
if add was successful, FALSE otherwise
*/
protected boolean
checkout() {
}
/** - complete this method.
* @return TRUE
if add was successful, FALSE otherwise
*/
protected boolean checkin()
{
}
/** Main driver containing
a menu of options
*/
public static void
main(String[] args) {
new LibrarySystem();
}
}
"
7) To test your application, run the execution script, run.bat
which recompiles your Java class with a reference to the active
database instance
Run.bat -
"echo off
echo Setting Up Derby Classpath.
set CLASSPATH=libderby.jar;libderbytools.jar;.
echo Compiling Java Program...
javac javaLibrarySystem.java -d .
echo Running Java Program...
java LibrarySystem MyTestDB
echo done"
Explanation / Answer
Adding External JARs
Note: It is much better to reference JARs that exist in your project or in other projects - this allows you to check in all of your dependencies to your version control system. (You are using version control, right?)
Method 1
This is the preferred method, as it allows different developers of a common project to locate their external jars in different places.
1Right click the project name and navigate to Build Path.
2Select Configure Build Path... and the project properties window will appear showing your build path configurations.
3Click Add Variable...
4Click Configure Variables...
5Click New...
6Type a name for the new variable. For example, if these are JARs for Tomcat, perhaps call it TOMCAT_JARS
7Browse to the directory that contains the JAR for the path. (You can also select a specific jar file for the variable, if you prefer.)
8Click Ok to define the variable.
9Click Ok to close the preferences dialog.
10Select the variable from the list.
11Click Extend...
12Select the JAR(s) that you want to add to the classpath.
13Click Ok to close the extend dialog.
14Click Ok to close the new classpath variable dialog.
15Click Ok to close the build path setup dialog.
If you share the project with someone else, they must also define the variable. They can define it under Window->Preferences->Java->Build Path->Classpath Variables
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.