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