In the previous guide, we talked about creating the database, opening a JFrame, and adding data to the database. In this guide, we will be talking about Showing, Updating, and Deleting items from the database with the use of more JFrames. If you haven't read Part One, I strongly suggest you do so. Link: https://steemit.com/java/@zothuro/jframe-and-databases-part-i
Showing:
To show the database, you need a ResultSet statement. This takes the results from the Database and displays it onto the JTable that is on the JFrame. With that being said, lets get coding!
JTable table;
table = new JTable();
table.setBounds(201, 200, -189, -116);
frame.getContentPane().add(table);
This code segment create the table and adds it to the JFrame, which is also known as frame. Now, lets add a try catch statement.
JTable table = new JTable();
table.setBounds(10, 85, 214, 116);
try{
conn = DriverManager.getConnection("jdbc:sqlite:C://example//connect//name.db");
String sql = "SELECT * FROM name";
pst = conn.prepareStatement(sql);
rs=pst.executeQuery();
table.setModel(DbUtils.resultSetToTableModel(rs));
} catch(Exception ex){
JOptionPane.showMessageDialog(null, ex);
}
frame.getContentPane().add(table);
As you can see in the code, we are connecting to the database, getting it's contents, and displaying it to the table. This allows us to see what's going on with the database.
Updating:
When you create data, like Usernames, Passwords, etc. You want the ability to edit the information without opening the database with the Database Manager Software. I will be showing you guys how to EDIT the database.
With our previous code, we can add a few things and change up the layout a little. This code UPDATES the database where it takes the old name that was inputted and sets it to the new one.
JButton btnNewButton = new JButton("Update");
btnNewButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent arg0) {
try {
String url = "UPDATE name SET Name = '"+NewName.getText()+"' WHERE Name = '"+OldName.getText()+"'";
conn = DriverManager.getConnection("jdbc:sqlite:C:\\example\\connect\\name.db");
pst=conn.prepareStatement(url);
pst.execute();
JOptionPane.showMessageDialog(null, "Updated!");
} catch (Exception e) {
JOptionPane.showMessageDialog(null, e);
}
}
});
btnNewButton.setBounds(287, 76, 107, 23);
frame.getContentPane().add(btnNewButton);
Deleting:
Lets say you mess up big time and you don't have time to update the database. We can do this by deleting from the database the same way we have been imputing it and updating it.
JButton btnDelete = new JButton("Delete");
btnDelete.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent arg0) {
try {
String url = "DELETE FROM name WHERE Name = '"+OldName.getText()+"'";
conn = DriverManager.getConnection("jdbc:sqlite:C:\\example\\connect\\name.db");
pst=conn.prepareStatement(url);
pst.execute();
JOptionPane.showMessageDialog(null, "Deleted!");
} catch (Exception e) {
}
}
});
btnDelete.setBounds(287, 110, 107, 23);
frame.getContentPane().add(btnDelete);
OVER CODE FROM LAST GUIDE AND THIS:
Now, your total code should look like the following:
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.File;
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 javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JTextField;
import net.proteanit.sql.DbUtils;
import javax.swing.JTable;
public class SteemIt {
private static JFrame frame;
private static JTextField OldName;
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
private JTable table;
private JTextField NewName;
public static void main(String[] args) {
File dir = new File("C:\\example\\connect");
File teachdat = new File("C:\\example\\connect\\name.db");
try {
File sqlite = new File("C:\\example");
File db = new File("C:\\example\\connect");
sqlite.mkdir();
db.mkdir();
} catch(Exception ex) {
JOptionPane.showMessageDialog(null, ex);
}
if (!dir.exists()) { System.out.println("There is no con folder in example"); }
if (dir.exists() && !teachdat.exists()) {
String url = "jdbc:sqlite:C:\\example\\connect\\name.db";
String sql = "CREATE TABLE IF NOT EXISTS name (\n"
+ "Name);";
try (Connection conn = DriverManager.getConnection(url);
Statement stmt = conn.createStatement()) {
stmt.execute(sql);
} catch(SQLException exe) {
JOptionPane.showMessageDialog(null, exe);;
}
} else {
System.out.println("There is already the name logger database >_>");
}
new SteemIt();
}
private SteemIt() {
frame = new JFrame();
frame.setBounds(450, 450, 420, 250);
frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
frame.getContentPane().setLayout(null);
OldName = new JTextField();
OldName.setBounds(107, 11, 170, 14);
frame.getContentPane().add(OldName);
OldName.setColumns(10);
JLabel lblNewLabel = new JLabel("Name:");
lblNewLabel.setBounds(10, 11, 87, 14);
frame.getContentPane().add(lblNewLabel);
JButton btnSubmit = new JButton("Submit");
btnSubmit.setBounds(287, 8, 107, 23);
btnSubmit.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
try {
String sql = "INSERT INTO name"
+ "(Name)"
+ "VALUES(?)";
conn = DriverManager.getConnection("jdbc:sqlite:C:\\example\\connect\\name.db");
pst = conn.prepareStatement(sql);
pst.setString(1, OldName.getText());
pst.executeUpdate();
JOptionPane.showMessageDialog(null, "Inserted successfully!");
} catch(SQLException ex){
JOptionPane.showMessageDialog(null, ex);
}
}
});
frame.getContentPane().add(btnSubmit);
JButton btnCancel = new JButton("Cancel");
btnCancel.setBounds(287, 42, 107, 23);
btnCancel.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent arg0) {
frame.dispose();
}
});
frame.getContentPane().add(btnCancel);
table = new JTable();
table.setBounds(10, 51, 267, 150);
try{
conn = DriverManager.getConnection("jdbc:sqlite:C://example//connect//name.db");
String sql = "SELECT * FROM name";
pst = conn.prepareStatement(sql);
rs=pst.executeQuery();
table.setModel(DbUtils.resultSetToTableModel(rs));
} catch(Exception ex){
JOptionPane.showMessageDialog(null, ex);
}
frame.getContentPane().add(table);
JLabel lblNewName = new JLabel("New Name:");
lblNewName.setBounds(10, 26, 87, 14);
frame.getContentPane().add(lblNewName);
NewName = new JTextField();
NewName.setColumns(10);
NewName.setBounds(107, 26, 170, 14);
frame.getContentPane().add(NewName);
JButton btnNewButton = new JButton("Update");
btnNewButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent arg0) {
try {
String url = "UPDATE name SET Name = '"+NewName.getText()+"' WHERE Name = '"+OldName.getText()+"'";
conn = DriverManager.getConnection("jdbc:sqlite:C:\\example\\connect\\name.db");
pst=conn.prepareStatement(url);
pst.execute();
JOptionPane.showMessageDialog(null, "Updated!");
} catch (Exception e) {
JOptionPane.showMessageDialog(null, e);
}
}
});
btnNewButton.setBounds(287, 76, 107, 23);
frame.getContentPane().add(btnNewButton);
JButton btnDelete = new JButton("Delete");
btnDelete.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent arg0) {
try {
String url = "DELETE FROM name WHERE Name = '"+OldName.getText()+"'";
conn = DriverManager.getConnection("jdbc:sqlite:C:\\example\\connect\\name.db");
pst=conn.prepareStatement(url);
pst.execute();
JOptionPane.showMessageDialog(null, "Deleted!");
} catch (Exception e) {
}
}
});
btnDelete.setBounds(287, 110, 107, 23);
frame.getContentPane().add(btnDelete);
frame.setVisible(true);
}
}
If you run this program, you should get something like this:
Summary:
You should have learned the following:
Update Data to the database
Remove data from the database
View data from the database using a table