Java Video Tutorial 37
In this part of my Java Video Tutorial, I continue showing how to use JTables with MySQL. You’ll learn how to pull information from a database, add a row, delete a row, convert a String into a SQL Date and much more.
If you haven’t seen my Java Swing Tutorial watch it first. Part 1 of my Java JTable Tutorial should also be seen first.
I explain how to load the database below. I also have all of the code.
If you like videos like this tell Google [googleplusone]
How to Load the Database
You’ll need to do the following to read info from the database:
- Open MySQL and create a database with CREATE DATABASE samp_db;
- Type the following USE samp_db;
Exit MySQL and download these files:
Then execute these to commands in your terminal (outside of MySQL):
- mysql5 -u YOURUSERID -p < YOUR create_president.sql LOCATION
- mysql5 -u YOURUSERID -p < YOUR insert_president.sql LOCATION
Use your USERID and PASSWORD. Point to the locations for where you saved the files above. That’s it.
Code from the Video
import java.awt.BorderLayout;
import java.awt.Font;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
//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.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.table.DefaultTableModel;
public class Lesson37 extends JFrame{
static JLabel lFirstName, lLastName, lState, lBirthDate;
static JTextField tfFirstName, tfLastName, tfState, tfBirthDate;
static java.util.Date dateBirthDate, sqlBirthDate;
// Holds row values for the table
static Object[][] databaseResults;
// Holds column names for the table
static Object[] columns = {"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","turtledove");
// Statement objects executes a SQL query
// createStatement returns a Statement object
Statement sqlState = conn.createStatement();
// This is the query I'm sending to the database
String selectStuff = "Select 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
ResultSet 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.getString(1), rows.getString(2), rows.getString(3), rows.getDate(4)};
/* You can also get other types
* int getInt()
* boolean getBoolean()
* double getDouble()
* float getFloat()
* long getLong()
* short getShort()
*/
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();
// Will convert from string to date
SimpleDateFormat dateFormatter = new SimpleDateFormat("yyyy-MM-dd");
try {
dateBirthDate = dateFormatter.parse(sDate);
sqlBirthDate = new java.sql.Date(dateBirthDate.getTime());
} catch (ParseException e1) {
e1.printStackTrace();
}
Object[] president = {sFirstName, sLastName, sState, sqlBirthDate};
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());
}
});
// 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);
frame.setSize(1200, 500);
frame.setVisible(true);
}
}
i love your teaching style ,Thanks for the tutorial.
Thank you 🙂 I’m always looking for ways to improve. Is there anything I could do better? Are there any other topics you’d like to see covered? I’ll cover pretty much any topic
Thank you so much for your tutorials. They are so far the best of all java tutorials I’ve seen on youtube. It would be nice to extend tutorial #36 and 37 to sorting and filtering of jTable. Is sorting and filtering possible with DefaultTableModel?
Thank you 🙂 I think this will help JTable sorting
You already have done a lot for us, and beside my academic study i m practicing web design from you tutorial which isnt finished yet, i check and like your every video and the way u teach us. but im far away from this advance tutorial, but im happy and now im practicing psd to html and fixing bug. pray for me , wish you all the best 🙂 Thank you
Thank you. After you really understand one language you’ll be able to easily learn the rest. If you are ever struggling with a tutorial you can normally work your way through the problem by playing with the code in your own editor. If my comments aren’t good enough you can then look up the functions in the API. It takes awhile, but eventually everything just clicks. When that happens you’ll be able to do anything. I wish you the best
Thank you very much for your tutorials, helped me a lot on my school projects, keep up the good work !
Greeting from Morocco!
Thank you. I’m glad you liked them. I’m very glad to be able to help people all over the world
Your code is working correctly.
I just want to mention something; (I am not sure whether it is good) but here it is.
Once you have imported JFrame(line 16) , and created new JFrame (line 67), Then you don’t need to extends your class by JFrame (line 24).
thank your teacher your effort. i like to say you thanks very much.
You’re very welcome 🙂 Thank you for the kind words
sir u r awesome……………..
Thank you very much 🙂
Hi Derek,
Thank you so much for these tutorials. As an aspiring Java developer/student, I am interested in creating Java-based applications that interface with relational databases..
Instead of hard coding the columns, i decided to use a metadata from the previous tutorial. However, i keep getting “Exception in thread “main” java.lang.ArrayIndexOutOfBoundsException: 4:” error.
Here is my code –
static Object[] columns;(at the class level)
columns = new String[numOfCol];
for(int i=1; i<=numOfCol; i++)
{
// Returns the column name
columns[i] = metaData.getColumnName(i);
System.out.println(i);
}
the console prints numbers 1-3.
Thank you for your help.
Thank you very much 🙂 That error comes from calling for an index that doesn’t exist in your array. I hope that helps
You’re right about things clicking. I’ve been working on Java really hard and suddenly going back to PHP and JS things seem a lot clearer in my head.
I’m experimenting with my own database at the mo and was wondering.. What is the purpose of overriding the getColumnClass method?
I’m glad the information is sticking. I just overwrote it to provide for the extra checks that were needed
Hi Derek,
question:
– Why a zero (0) in getValueAt(0, column)of overidden method getColumnClass(Lesson 37)?
Also, job well done on (all that I have viewed so far) video tutorials.
The video format works well with me. The explanation, timing and focus of the lessons and key points are cohesive. You also have a good way of conveying them.
thanks,
eazl
Hi Eazl,
I’m glad you like the videos. Here is the description of the method getValueAt : public Object getValueAt(int row, int col)
Since I’m only getting the columns with this class, that is the reason I keep row at 0. I hope that helps 🙂
Derek
Hi!
This is a great tutorial, but iI’m having trouble getting the data from mysql. I’m using MAMP. Do I need to change something on the connection url or to MAMP’s configurations? Because I keep on getting “SQLException: Communications link failure” on the console.
Any help would be appreciated. Thank you.
Maria
That exception doesn’t say much. Here is a complete answer for most any error you’d have with that exception StackOverflow
I hope that helps
How about inserting values in to the same database.
Yeah, I’m wondering how to do that too 🙂 Great turtorial, I love this guy!
Hello Mr. Banas
Thank you for the awesome tutorial and assistance. I’m having trouble adding a president because each time I do so the month ends up being October, the day is always the first, and the year always increments by one then what I inputted. Is there any reason why this is happening?
Hello,
Sorry, but I’m not sure. It sounds like you have auto increment set up on some data. Remove those and everything should be ok.
I cannot seem to add the sql files to mysql. I keep getting this error. ERROR 1046 (3D000) at line 1: no database selected
I use the:
#mysql -u root < C:\downloads\insert_president.sql
Thank you for the great tutorials also. So far so good.
You need to create the database first. That should fix that issue.