# Notes

# Passing an SQL Statement to the DBMS

Once you have established a connection, you must get a reference to a Statement object before you can issue SQL statements to the DBMS

A Statement object has an executeQuery method that returns a reference to a ResultSet object
A ResultSet object contains the results of the query

	Connection conn = DriverManager.getConnection(DB_URL);
	Statement stmt = conn.createStatement();
	String sqlStatement = "SELECT Description FROM Coffee";
	ResultSet result = stmt.executeQuery(sqlStatement);

# Getting a Row from the ResultSet Object

A ResultSet object has an internal cursor
- Points to a specific row in the ResultSet
- The row to which it points is the current row
- Initially positioned just before the first row
- Can be moved from row to row to examine all rows
A ResultSet object's next method result.next(); moves the cursor to the next row in the ResultSet
- moves to first row in a newly created ResultSet
- moves to the next row each time it is called
A ResultSet object's next method returns a Boolean value
- true if successfully moved to the next row
- false if there are no more rows
A while loop can be used to move through all the rows of a newly created ResultSet

	while (result.next())
	{
	// Process the current row.
	}

# Getting Columns in a ResultSet Object

You use one of the ResultSet object's get methods to retrieve the contents of a specific column in the current row.
Can pass an argument for either the column number or the column name

	System.out.println(result.getString(1));
	System.out.println(result.getString(2));

	System.out.println(result.getString("Description"));
	System.out.println(result.getDouble("ProdNum"));

# Inserting rows with JDBC

To issue an INSERT statement, you must get a reference to a Statement object

The Statement object has an executeUpdate method
Accepts a string containing the SQL INSERT statement as an argument
Returns an int value for the number of rows inserted

	String sqlStatement = "INSERT INTO Coffee " +
	"(ProdNum, Price, Description)" +
	" VALUES " +
	"('22-001', 8.65, 'Honduran Dark')";

	int rows = stmt.executeUpdate(sqlStatement);

rows should contain the value 1 , indicating that one row was inserted

# Updating Rows with JDBC

To issue an UPDATE statement, you must get a reference to a Statement object

The Statement object has an executeUpdate method
Accepts a string containing the SQL UPDATE statement as an argument
Returns an int value for the number of rows affected

Example

	String sqlStatement = "UPDATE Coffee " +
	"SET Price = 9.95" +
	" WHERE " +
	"Description = 'Brazilian Decaf'";

	int rows = stmt.executeUpdate(sqlStatement);

rows indicates the number of rows that were changed

# Deleting Rows with JDBC

To issue a DELETE statement, you must get a reference to a Statement object

The Statement object has an executeUpdate method
Accepts a string containing the SQL DELETE statement as an argument
Returns an int value for the number of rows that were deleted

Example

	String sqlStatement = "DELETE FROM Coffee " +
	"WHERE ProdNum = '20-001'";

	int rows = stmt.executeUpdate(sqlStatement);

rows indicates the number of rows that were deleted

# Creating a New Database with Java DB

The ;create=true attribute creates a new database when appended to the database URL

"jdbc:derby:EntertainmentDB;create=true"

Creates an empty database named EntertainmentDB
The CREATE TABLE statement can be used to create tables
Java DB creates a folder with the name of the database on your system
Delete the database folder to delete the database

# Scrollable Result Sets

By default, a ResultSet object is created with a read-only concurrency level and the cursor is limited to forward movement

A scrollable result set can be created with the overloaded version of the Connection object's createStatement method

conn.createStatement(type, concur);

type is a constant for the scrolling type
concur is a constant for the concurrency level

Example

	Statement stmt =
	conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
	ResultSet.CONCUR_READ_ONLY);

Creates a scrollable result set that is read-only and insensitive to database changes

# The ResultSet Scrolling Types

ResultSet.TYPE_FORWARD_ONLY
- Default scrolling type
- Cursor moves forward only
ResultSet.TYPE_SCROLL_INSENSITIVE
- Cursor moves both forward and backward
- Changes made to the database do not appear
ResultSet.TYPE_SCROLL_SENSITIVE
- Cursor moves both forward and backward
- Changes made to the database appear as soon as they are made

# The ResultSet Concurrency Levels

ResultSet.CONCUR_READ_ONLY
- Default concurrency level
- Read-only version of data from the database
- Cannot change database by altering result set
ResultSet.CONCUR_UPDATEABLE
- Result set is updateable
- Changes can be made to the result set and saved to the database
- Uses methods that allow changes to be made to the database without issuing SQL statements

first()
Moves the cursor to the first row
last()
Moves the cursor to the last row
next()
Moves the cursor to the next row
previous()
Moves the cursor to the previous row
relative(rows)
Moves the cursor the number specified by the rows argument relative to the current row
- A positive rows value will move the cursor forward
- A negative rows value will move the cursor backward
absolute(rows)
Moves the cursor to the row number specified by the rows argument
- A rows value of 1 will move the cursor to the first row
- A rows value of 2 will move cursor to the second row
- And so on until the last row

# Determining the Number of Rows in a Result Set

ResultSet navigation methods can be used to determine the number of rows in a result set

Example

	resultSet.last() // Move to the last row
	int numRows = resultSet.getRow(); // Get the last row's number and store the value
	resultSet.first(); // Move back to the first row

# Result Set Metadata

Metadata refers to data that describes other data
A ResultSet object has metadata that describes a result set
Can be used to determine many things about a result set
- Number of columns
- Column names
- Column data types
- And much more
Useful for submitting SQL queries in applications
ResultSetMetaData is an interface in the java.sql package
The getMetaData method of a ResultSet object returns a reference to a ResultSetMetaData object.

ResultSetMetaData meta = resultSet.getMetaData();

Creates a ResultSetMetaData object reference variable named meta

# A Few `ResultSetMetaData` Methods

Method	Description
`int getColumnCount()`	Returns the number of columns in the result set.
`String getColumnName(int col)`	Returns the name of the column specified by the integer `col` . The first column is column 1.
`String getColumnTypeName(int col)`	Returns the name of the data type of the column specified by the integer `col` . The first column is column 1. The data type name returned is the database-specific SQL data type.
`int getColumnDisplaySize(int col)`	Returns the display width, in characters, of the column specified by the integer `col` . The first column is column 1.
`String getTableName(int col)`	Returns the name of the table associated with the column specified by the integer `col` . The first column is column 1.

# The `JTable` Class

The JTable class is a Swing component that displays data in a two-dimensional table

Jtable(Object[][] rowData, Object[] colNames)

rowData is a two-dimensional array of objects
- Contains data that will be displayed in the table
- Each row becomes a row of data in the table
- Each column becomes a column of data in the table
- JTable calls toString method of each object to get values
colNames is a one-dimensional array of objects
- Contains the column names to display
- JTable calls toString method of each object to get value

# Setting Up a Simple JTable Component

	String[] colNames = {"Name", "Telephone" };
	String[][] rowData = {
	{ "Jean", "555-2222" },
	{ "Tim", "555-1212" },
	{ "Matt", "555-9999" },
	{ "Rose", "555-4545" },
	{ "Geri", "555-5214" },
	{ "Shawn", "555-7821" },
	{ "Renee", "555-9640" },
	{ "Joe", "555-8657" }
	};

	JTable myTable = new JTable(rowData, colNames);
	JScrollPane scrollPane = new JScrollPane(JTable);

The figure shows an example of how the table appears in a frame

# Displaying Query Results in a JTable

This window appears first
The user enters a SELECT statement and clicks the Submit button

This window appears next
It displays the results in a Jtable component

# JDBC Auto Commit Mode

By default, the JDBC Connection class operates in auto commit mode.
In auto commit mode
- All updates that are made to the database are made permanent as soon as they are executed.
When auto commit mode is turned off
- Changes do not become permanent until a commit command is executed
- A rollback command can be used to undo changes

# JDBC Transaction Methods

To turn auto commit mode off
- Call the Connection class's setAutoCommit method
- Pass the argument false
conn.setAutoCommit(false);
To execute a commit command
- Call the Connection class's commit method
conn.commit();
To execute a rollback command
- Call the Connection class's rollback method
conn.rollback();

Example

	conn.setAutoCommit(false);
	// Attempt the transaction
	try
	{
	// Update the inventory records.
	stmt.executeUpdate(updateStatement);
	// Add the order to the UnpaidOrder table.
	stmt.executeUpdate(insertStatement);
	// Commit all these updates.
	conn.commit();
	// The commit method is called in the try block
	}
	catch (SQLException ex)
	{
	// Roll back the changes.
	conn.rollback();
	// The rollback method is called in the catch block
	}

# Stored Procedures

Many commercial database systems allow you to create SQL statements and store them in the DBMS itself
These SQL statements are called stored procedures
- Can be executed by other applications using the DBMS
- Ideal for SQL statements that are used often in a variety of applications
- Usually execute faster than SQL statements that are submitted from applications outside the DBMS
Each DBMS has its own syntax for creating a stored procedure in SQL
To execute a stored procedure, you must create a CallableStatement object
CallableStatement is an interface in the java.sql package
To create a CallableStatement object, you call the Connection class's prepareCall statement

# JDBC Dates

	import java.sql.Connection;
	import java.sql.DriverManager;
	import java.sql.PreparedStatement;
	import java.sql.SQLException;
	import java.sql.Statement;

	public class JDBCDates {
	// instance fields - db objects
	static Connection connect = null;
	static Statement statement = null;

	public static Connection getConnection() {
	// Setup the connection with the DB
	try {
	connect = DriverManager
	.getConnection("jdbc:mysql://www.papademas.net:3307/mydb?autoReconnect=true&useSSL=false"
	+ "&user=dbuser&password=db1");
	} catch (SQLException e) {
	e.printStackTrace();
	}
	return connect;
	}
	public static void createTable() {
	// set up SQL Query table creation string
	final String createDateTable = "CREATE TABLE jp_dates(id INT AUTO_INCREMENT PRIMARY KEY, startDate DATETIME)";

	try {

	//*******create table

	statement = getConnection().createStatement();

	statement.executeUpdate(createDateTable);

	System.out.println("Created table in given database...");

	//*******end create table
	// close connection/statement objects
	statement.close();
	connect.close();
	} catch (Exception e) {
	System.out.println(e.getMessage());
	}

	}
	public static void addRecs() throws SQLException {
	// objects for SQL Query inserts

	PreparedStatement ps = (PreparedStatement) getConnection()
	.prepareStatement("Insert into jp_dates(startDate) values(?)");
	java.sql.Timestamp date = new java.sql.Timestamp(new java.util.Date().getTime());
	ps.setTimestamp(1, date);
	ps.executeUpdate();

	//or alternatively
	/*
	statement = getConnection().createStatement();
	int result = statement
	.executeUpdate("Insert into jp_dates(startDate) values(now())");
	*/
	System.out.println("Record inserted into given database...");
	}

	public static void main(String[] args) throws SQLException {
	createTable();
	addRecs();
	}
	}

# DB Exercise

# Java Database Connectivity (JDBC)

Create a Java Project and name it CoffeeConnect .
Download the jar file accompanying this exercise from Blackboard. mysql-connector-java-8.0.22.zip
1. Create a folder in your project folder called libs .
2. Copy the jar file into your libs folder.
3. Right click on your project folder and choose Build Path > Configure Build Path…
4. Click on Libraries tab then Modulepath, then the Add JARs… button
5. At the pop up expand your Coffee_Connect folder and expand your libs folder. Choose your jar file and press OK.
6. Click Apply and Close to exit.
Download the Dao java file accompanying this exercise from Blackboard, and copy and paste the file into your src folder. Do the same for the TableViewer file as well.

Dao.java

	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.Scanner;

	public class Dao {

	// Code database URL
	static final String DB_URL = "jdbc:mysql://www.papademas.net:3307/510labs?autoReconnect=true&useSSL=false";
	// Database credentials
	static final String USER = "db510", PASS = "510";

	static Statement stmt;

	public static Connection connect() throws SQLException {
	return DriverManager.getConnection(DB_URL, USER, PASS);
	}

	public Dao() {
	try {
	// initialize any objects here
	stmt = connect().createStatement();
	} catch (SQLException e) {
	e.printStackTrace();
	}
	}

	public static void menu() {
	String menuItems = "1.Create table\n2.Insert recs\n3.Update recs\n4.Delete recs\n5.View Recs (Console)\n6.View Recs (Window)\n7.Exit";
	System.out.println(menuItems);
	}

	public static void createTables() {
	// finish implementing...
	}

	public static void updates(String desc, int id) {
	String SQL = "Update papa_coffee SET prod_desc = ? WHERE id = ?";
	// use prepared statement
	try (PreparedStatement pstmt = connect().prepareStatement(SQL)) {
	pstmt.setString(1, desc);
	pstmt.setInt(2, id);
	pstmt.executeUpdate();
	} catch (SQLException e) {
	e.printStackTrace();
	}
	}

	public static void deletes(int id) {
	// finish implementing...
	}

	public static void inserts() throws InterruptedException {

	// create an insert statement
	String sql = "";

	System.out.println("Inserting records...");
	try {
	Thread.sleep(3000); // sleep for 3 seconds
	sql = "Insert Into papa_coffee(prod_name,prod_desc,prod_price)"
	+ " values('J Papa''s','Strong Brew',29.55)";
	// execute query for insert(s)
	stmt = connect().createStatement();
	stmt.executeUpdate(sql);

	// System.out.println("Records inserted");

	} catch (SQLException e) {
	e.printStackTrace();
	}
	}

	public static void retrieveRecords() {

	try {
	// get record data from result set object
	ResultSet rs = stmt.executeQuery("Select * from papa_coffee");
	while (rs.next()) {

	int id = rs.getInt(1);
	String desc = rs.getString("prod_desc");
	double price = Double.parseDouble(rs.getString("prod_price"));

	// print records to console
	System.out.println("Id : " + id + "\t Desc. : " + desc + "\t Price: " + price);
	}
	rs.close();

	} catch (SQLException e) {
	e.printStackTrace();
	}
	}

	public static void retrieveRecords2() {

	try {
	// get record data from result set object
	ResultSet rs = stmt.executeQuery("Select * from papa_coffee");
	new TableViewer().runView(rs); // display records in window
	rs.close();
	} catch (SQLException e) {
	e.printStackTrace();
	}

	}

	public static void main(String args[]) throws InterruptedException {

	new Dao();
	Scanner sc = new Scanner(System.in);
	do {
	menu();
	int choice = sc.nextInt();
	switch (choice) {
	case 1:// createTables();
	break;
	case 2:
	inserts();
	break;
	case 3:
	updates("Nova Coffee", 1);
	break;
	case 4:
	// delete some id
	// deletes(id);
	break;
	case 5:
	retrieveRecords();
	break;
	case 6:
	retrieveRecords2();
	break;
	case 7:
	System.out.println("bye for now!");
	System.exit(0);
	}
	System.out.println();
	} while (true);
	}
	}

TableViewer.java

	import java.sql.ResultSet;
	import java.sql.ResultSetMetaData;
	import java.sql.SQLException;
	import java.util.Vector;

	import javax.swing.JFrame;
	import javax.swing.JScrollPane;
	import javax.swing.JTable;
	import javax.swing.table.DefaultTableModel;

	public class TableViewer {
	public void runView(ResultSet rs) {
	// instantiate vector objects to hold column/row data for JTable
	Vector<Vector<Object>> data = new Vector<Vector<Object>>();
	Vector<String> column = new Vector<String>();
	try {
	ResultSetMetaData metaData = rs.getMetaData();
	int columns = metaData.getColumnCount();

	// get column names from table!
	String cols = "";

	for (int i = 1; i <= columns; i++) {
	cols = metaData.getColumnName(i);
	column.add(cols);
	}

	// get row data from table!
	while (rs.next()) {
	Vector<Object> row = new Vector<Object>(columns);

	for (int i = 1; i <= columns; i++)
	row.addElement(rs.getObject(i));
	data.addElement(row);
	}

	DefaultTableModel model = new DefaultTableModel(data, column);

	JTable table = new JTable(model);
	JFrame frame = new JFrame("Record Details");
	frame.setSize(700, 200);
	frame.add(new JScrollPane(table));
	frame.setDefaultCloseOperation(0);
	frame.pack();
	frame.setVisible(true);

	// rs.close(); //close ResultSet instance
	} catch (SQLException e) {
	e.printStackTrace();
	}
	}
	}

Your package should resemble something like this below.

Run your app from the Dao.java file and work some of the CRUD operations!

Modify all references to your own table names in to your Dao file you created in the first DB Exercise #1.

Week 11. Java Database Connectivity

# Notes

# Passing an SQL Statement to the DBMS

# Getting a Row from the ResultSet Object

# Getting Columns in a ResultSet Object

# Inserting rows with JDBC

# Updating Rows with JDBC

# Deleting Rows with JDBC

# Creating a New Database with Java DB

# Scrollable Result Sets

# The ResultSet Scrolling Types

# The ResultSet Concurrency Levels

# ResultSet Navigation Methods

# Determining the Number of Rows in a Result Set

# Result Set Metadata

# A Few `ResultSetMetaData` Methods

# The `JTable` Class

# Setting Up a Simple JTable Component

# Displaying Query Results in a JTable

# JDBC Auto Commit Mode

# JDBC Transaction Methods

# Stored Procedures

# JDBC Dates

# DB Exercise

# Java Database Connectivity (JDBC)

# Notes

# Passing an SQL Statement to the DBMS

# Getting a Row from the ResultSet Object

# Getting Columns in a ResultSet Object

# Inserting rows with JDBC

# Updating Rows with JDBC

# Deleting Rows with JDBC

# Creating a New Database with Java DB

# Scrollable Result Sets

# The ResultSet Scrolling Types

# The ResultSet Concurrency Levels

# ResultSet Navigation Methods

# Determining the Number of Rows in a Result Set

# Result Set Metadata

# A Few ResultSetMetaData Methods

# The JTable Class

# Setting Up a Simple JTable Component

# Displaying Query Results in a JTable

# JDBC Auto Commit Mode

# JDBC Transaction Methods

# Stored Procedures

# JDBC Dates

# DB Exercise

# Java Database Connectivity (JDBC)

Lecture 7. Information Visualization

Week 12. JavaFx

# A Few `ResultSetMetaData` Methods

# The `JTable` Class