import java.awt.BorderLayout;

import java.awt.Font;

import java.awt.event.ActionEvent;

import java.awt.event.ActionListener;

// Needed to track when the user clicks on a table cell

import java.awt.event.MouseAdapter;

import java.awt.event.MouseEvent;

//The API for accessing and processing data stored in a database

import java.sql.*;

import java.text.ParseException;

// Allows you to convert from string to date or vice versa

import java.text.SimpleDateFormat;

import javax.swing.JButton;

import javax.swing.JFrame;

import javax.swing.JLabel;

import javax.swing.JOptionPane;

import javax.swing.JPanel;

import javax.swing.JScrollPane;

import javax.swing.JTable;

import javax.swing.JTextField;

import javax.swing.table.DefaultTableModel;

public class JavaLesson38 extends JFrame{

static JLabel lFirstName, lLastName, lState, lBirthDate;

static JTextField tfFirstName, tfLastName, tfState, tfBirthDate;

static java.util.Date dateBirthDate, sqlBirthDate;

static ResultSet rows;

// Holds row values for the table

static Object[][] databaseResults;

// Holds column names for the table

static Object[] columns = {"ID", "First Name", "Last Name", "State", "Birth Date"};

// DefaultTableModel defines the methods JTable will use

// I'm overriding the getColumnClass

static DefaultTableModel dTableModel = new DefaultTableModel(databaseResults, columns){

public Class getColumnClass(int column) {

Class returnValue;

// Verifying that the column exists (index > 0 && index < number of columns

if ((column >= 0) && (column < getColumnCount())) {

returnValue = getValueAt(0, column).getClass();

} else {

// Returns the class for the item in the column

returnValue = Object.class;

}

return returnValue;

}

};

// Create a JTable using the custom DefaultTableModel

static JTable table = new JTable(dTableModel);

public static void main(String[] args){

JFrame frame = new JFrame();

frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

// A connection object is used to provide access to a database

Connection conn = null;

try {

// The driver allows you to query the database with Java

// forName dynamically loads the class for you

Class.forName("com.mysql.jdbc.Driver");

// DriverManager is used to handle a set of JDBC drivers

// getConnection establishes a connection to the database

// You must also pass the userid and password for the database

conn = DriverManager.getConnection("jdbc:mysql://localhost/samp_db","mysqladm","rickmoss");

// Statement objects executes a SQL query

// createStatement returns a Statement object

Statement sqlState = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,

ResultSet.CONCUR_UPDATABLE);

// This is the query I'm sending to the database

String selectStuff = "Select pres_id, first_name, last_name, state, birth from president";

// A ResultSet contains a table of data representing the

// results of the query. It can not be changed and can

// only be read in one direction

rows = sqlState.executeQuery(selectStuff);

// Temporarily holds the row results

Object[] tempRow;

// next is used to iterate through the results of a query

while(rows.next()){

tempRow = new Object[]{rows.getInt(1), rows.getString(2), rows.getString(3), rows.getString(4), rows.getDate(5)};

/* You can also get other types

* int getInt()

* boolean getBoolean()

* double getDouble()

* float getFloat()

* long getLong()

* short getShort()

*/

// Add the row of data to the JTable

dTableModel.addRow(tempRow);

}

}

catch (SQLException ex) {

// String describing the error

System.out.println("SQLException: " + ex.getMessage());

// Vendor specific error code

System.out.println("VendorError: " + ex.getErrorCode());

}

catch (ClassNotFoundException e) {

// Executes if the driver can't be found

e.printStackTrace();

}

// Increase the font size for the cells in the table

table.setFont(new Font("Serif", Font.PLAIN, 26));

// Increase the size of the cells to allow for bigger fonts

table.setRowHeight(table.getRowHeight()+16);

// Allows the user to sort the data

table.setAutoCreateRowSorter(true);

// Adds the table to a scrollpane

JScrollPane scrollPane = new JScrollPane(table);

// Adds the scrollpane to the frame

frame.add(scrollPane, BorderLayout.CENTER);

// Creates a button that when pressed executes the code

// in the method actionPerformed

JButton addPres = new JButton("Add President");

addPres.addActionListener(new ActionListener(){

public void actionPerformed(ActionEvent e){

String sFirstName = "", sLastName = "", sState = "", sDate = "";

// getText returns the value in the text field

sFirstName = tfFirstName.getText();

sLastName = tfLastName.getText();

sState = tfState.getText();

sDate = tfBirthDate.getText();

sqlBirthDate = getADate(sDate);

try {

// Moves the database to the row where data will be placed

rows.moveToInsertRow();

// Update the values in the database

rows.updateString("first_name", sFirstName);

rows.updateString("last_name", sLastName);

rows.updateString("state", sState);

rows.updateDate("birth", (Date) sqlBirthDate);

// Inserts the changes to the row values in the database

rows.insertRow();

// Directly updates the values in the database

rows.updateRow();

} catch (SQLException e1) {

e1.printStackTrace();

}

int presID = 0;

try {

// Go to the last row inserted and get the id

rows.last();

presID = rows.getInt(1);

} catch (SQLException e1) {

e1.printStackTrace();

}

Object[] president = {presID, sFirstName, sLastName, sState, sqlBirthDate};

// Add the row of values to the JTable

dTableModel.addRow(president);

}

});

JButton removePres = new JButton("Remove President");

removePres.addActionListener(new ActionListener(){

public void actionPerformed(ActionEvent e){

// Will remove which ever row that is selected

dTableModel.removeRow(table.getSelectedRow());

try {

// Moves the database to the row currently selected

// getSelectedRow returns the row number for the selected row

rows.absolute(table.getSelectedRow());

// Deletes the selected row from the database

rows.deleteRow();

} catch (SQLException e1) {

// TODO Auto-generated catch block

e1.printStackTrace();

}

}

});

// Define values for my labels

lFirstName = new JLabel("First Name");

lLastName = new JLabel("Last Name");

lState = new JLabel("State");

lBirthDate = new JLabel("Birthday");

// Define the size of text fields

tfFirstName = new JTextField(15);

tfLastName = new JTextField(15);

tfState = new JTextField(2);

// Set default text and size for text field

tfBirthDate = new JTextField("yyyy-MM-dd", 10);

// Create a panel to hold editing buttons and fields

JPanel inputPanel = new JPanel();

// Put components in the panel

inputPanel.add(lFirstName);

inputPanel.add(tfFirstName);

inputPanel.add(lLastName);

inputPanel.add(tfLastName);

inputPanel.add(lState);

inputPanel.add(tfState);

inputPanel.add(lBirthDate);

inputPanel.add(tfBirthDate);

inputPanel.add(addPres);

inputPanel.add(removePres);

// Add the component panel to the frame

frame.add(inputPanel, BorderLayout.SOUTH);

// When the user clicks on a cell they'll be able to change the value

table.addMouseListener(new MouseAdapter(){

public void mouseReleased(MouseEvent me){

String value = JOptionPane.showInputDialog(null,"Enter Cell Value:");

// Makes sure a value is changed only if OK is clicked

if (value != null)

{

table.setValueAt(value, table.getSelectedRow(), table.getSelectedColumn());

}

try {

// Move to the selected row

rows.absolute(table.getSelectedRow()+1);

// Get the name of the selected column

String updateCol = dTableModel.getColumnName(table.getSelectedColumn());

// Previous to Java 1.7 you couldn't use Strings in a Switch

// If you get an error here it is because you aren't using Java 1.7

switch (updateCol) {

// Uses a different update method depending on the data type

case "birth":

sqlBirthDate = getADate(value);

rows.updateDate(updateCol, (Date) sqlBirthDate);

rows.updateRow();

break;

default:

rows.updateString(updateCol, value);

System.out.println("Current Row: " + rows.getRow());

rows.updateRow();

break;

}

} catch (SQLException e) {

// Commented out so the user can delete rows

// e.printStackTrace();

}

}

});

frame.setSize(1200, 500);

frame.setVisible(true);

}

// Will convert from string to date

public static java.util.Date getADate(String sDate){

SimpleDateFormat dateFormatter = new SimpleDateFormat("yyyy-MM-dd");

try {

dateBirthDate = dateFormatter.parse(sDate);

sqlBirthDate = new java.sql.Date(dateBirthDate.getTime());

} catch (ParseException e1) {

e1.printStackTrace();

}

return sqlBirthDate;

}

}

/*

* ALTER TABLE president

ADD COLUMN pres_id INT AUTO_INCREMENT NOT NULL FIRST,

ADD PRIMARY KEY(pres_id);

alter table president modify city varchar(20); # Allow city to be NULL

delete from president where pres_id = 42;

*/