Java Video Tutorial 38
Posted by Derek Banas on May 6, 2012 in Java Video Tutorial, Statements SQL, Web Design | 39 comments
In my previous JTable Video Tutorial I showed you how to pull information from a database and work with it in a JTable.
In this tutorial I show you how to add and delete rows in a JTable that will then effect the database. I also cover allowing the user to change the database on a cell basis.
After this tutorial, you’ll be able to do anything with Java JTables.
If you like videos like this, tell Google [googleplusone]
Also, feel free to share it
Code from the Video
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 Lesson38 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","turtledove");
// 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;
*/
Hi Derek,
great job, those Java/Swing videos! You are almost at the level of Paul Hagerty’s lectures on iOS that is available on iTunes U.
One topic that I was looking for and couldn’t find so far: The overall structure of a MVC based swing application. What components take over what duties, what are the typical interfaces, visibility between the components etc. Wouldn’t that be a nice topic for you? Our can you highlight me a resource?
Best regards,
— Till.
I’m going to cover most of the most popular design patterns very soon. You can expect that tutorial to start next week. I’m sure the Stanford tutorials must be good. I don’t watch anyone else though, because I don’t want to be influenced by them.
Very soon I’ll completely change the way I do tutorials. I want them to be more fun. I figure that is the only way I can improve.
More fun is always good! Although I must admit your tutorials are the best quality I have seen so far: seeing you typing the things keeps the mental flow in sync, the production quality and layout are really professional!
What could help to explain more complex and high level topics are documents (mainly graphical models) beside the code. This way one could even understand MVC 😉
Thank you very much. I’m not sure how I can improve them. Not that I think they are perfect, but I’ve been perplexed by why I don’t get more views? I guess I spend very little time on marketing / networking and that is the reason. This is all just a fun hobby and definitely not even a part time job.
The design pattern tutorial is coming 🙂
hiii, nice video as usual, thank you very much…
if possible make some videos for logging in java..
also with log4j..
thank you..
I’ll see what I can do. I was never a big fan of logging because it really junks up the code. I’ll see if I can find some great reasons to use this. Thanks for the request 🙂
Hi,
Thanks for making such a wonderful website. You are the man who is really helping student like me.
From my very early age I am planning to make a desktop application for super shop management. But I am not getting proper guideline to make my dream successful. I have some knowledge in java. Would you please give me guideline about how to get started and what I need to learn , from where I could learn etc .
Thanks
You’re very welcome 🙂 I’m not sure what Super Shop Management is? If you can give me an example or explain what you want to do further, I’ll be happy to help
Hi Derek,
Thanks for sharing your knowledge.
I am knew to the world of java programming and find your videos very helpful.
If you can I would really like to see a set that shows how to pull information
from a database and work with it in a JList and on item selected open a detail
form to update and delete a field in the database.
Hi Joseph,
You are very welcome. I’m glad to help. I pretty much covered Java Jlists here. I then show how to query databases with Java here.
There is more information on Java and MySQL here. In this tutorial JTables and MySQL, I show how to delete information from the database using Swing components.
If I didn’t cover what you need feel free to ask. I’m going to revisit JTables, MySQL and sabermetrics soon. I’m going to make an app that has multiple JTables that work together.
Derek
Yes Derek I’ve been playing around with your base code and can now put things together. Thanks.
Hi admin,
Deleting a row doent work for me, seems like a cursor probleme:
java.sql.SQLException: [Microsoft][Gestionnaire de pilotes ODBC] État de curseur non valide
at sun.jdbc.odbc.JdbcOdbcResultSet.setPos(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcResultSet.deleteRow(Unknown Source)
Just like if the programe doesn’t know which row to delete 😐
What version of Java are you using?
1.6
Thanks.
You’re welcome
Very Nice video. I follow your code.. it works well without any errors. But there is a little thing about deleting rows.
when I select a row and press “remove” button.
In the swing interface the selected row is deleted (as expected).
In the actual database the last row is deleted whatever the selected row was (not expected).
This is the code for remove button:
JButton removeEmployee = new JButton("Remove Selected");
removeEmployee.addActionListener(new ActionListener()
{
public void actionPerformed(ActionEvent e)
{
dTableModel.removeRow(table.getSelectedRow());
try
{
resultRows.absolute(table.getSelectedRow());
resultRows.deleteRow();
} catch (SQLException e1)
{
e1.printStackTrace();
}
}
});
Admin.. I am sorry about the previous comment, I didn’t know that I should be logged in to see it.
Ok here is the solution to my problem.
JButton removeEmployee = new JButton("Remove Selected");
removeEmployee.addActionListener(new ActionListener()
{
public void actionPerformed(ActionEvent e)
{
try
{
resultSet.absolute(table.getSelectedRow()+1);
resultSet.deleteRow();
dTableModel.removeRow(table.getSelectedRow());
} catch (SQLException e1)
{
e1.printStackTrace();
}
}
});
I put it and solved it in
The link where I put and solved it is here
http://stackoverflow.com/questions/12104882/java-delete-a-selected-row-resultset-deletes-last-row-from-the-database-instea
Thank you very much for pointing this out and finding an answer 🙂 I’m normally better at answering comments but my wife is pregnant and she is due today 🙂 Thanks again – Derek
Thank you! Same issue for me, now fixed
Hi Derek.
(By Derek):
>I’m going to make an app that has multiple JTables that work >together.
It will be nice if you can cover that.
When I press add to the SLQ database I get this error:
Invalid cursor state – no current row.
Do you know what the problem is?
I solved the problem with direct SQL statement. But you code for the add button doesnt work for me. Not sure why. I think it looks to initialize the cursor position.
I’ll have to look into that. Did you change the code in any way? I’m glad you figured it out
dear Derek,
I have a question about synchronizing between the database and Jtable, I been playing around this example.
the Id number would always skip over the expected sequent order when I entered a new record by adding a record.
e.g,if there are only 2 records in the database, the new inserted row’s ID should be 3, it turned out to be 18 or other number instead, depending on how many operations I had done in between. it seems that java could memorize the number of time that i had added or removed the record. I couldn’t figure it out how to fix it. what is the general approach to synchronise with database?
Here is a page I linked to a while back when I was doing just that. It worked for me. I hope it helps
thank you so much for this video… its been three days m searching for this kinda program but nothing worked for me.. thanks for this program…. 🙂
thanks for a wonderful video.. i have been searching for this kinda topic for past three days.. but in vain.. thank u so much this helped me alot..:)
You’re very welcome 🙂 I’m glad it helped
@derek.. i want to display a jtable that is used for fetching data from database , when i click on a row it should open a new frame with that particular information that has been clicked.. help me out!!!!!! 🙁
table.addMouseListener(new MouseAdapter() {
public void mouseClicked(MouseEvent e) {
Do Your Stuff Here
}
});
i have followed the tutorial bu have been unable to store the records to the database. My connection is in another class
public class DatabaseConnection {public static Connection getDBConnection() {
Connection connection;
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/accounting","root","jinXED007");
return connection;
} catch (Exception ex) {
return null;
}
}
What errors are you getting?
Hi Derek, I’ve been trying your code and I can add/remove information but when I try to change the cells the only cell that I’m available the edit and see in the database is “state”.
When I try to edit other cells nothing happeds :/ I’m using java version 7.0
Did you see part 37 and set up the database? Sorry I’m not sure aside from that
using the JTextField
1)how can we check for String length error, for example entering SSN length Not greater then 7
2)how to empty the JTextField after adding a row
3)if we add a new Row how to make that row permanent in JTable
thank you
I cover all of that over the course of my Java tutorial. You could catch the number of numbers entered by checking length in a keylistener. I have tutorials on JTextField methods and such as well. Here is every Java tutorial in one place Java Video Tutorial. I hope it helps 🙂
i meant SSN length not greater 9 sorry
Yes an SSN is 9 numbers in length in the US