Today is Sunday, 22nd September 2019

Archive for the ‘MySQL’ Category


Java JDBC Tutorial – Part 12.11: Connect Java Swing GUI to a MySQL Database – Date of Birth

Hi All, Things have being going good with the JDBC Swing GUI video series. I had a YouTube subscriber ask if I could expand on this example and show how to handle dates. So, in this post, we’ll add support for read/write the date of birth for an employee.

Overview of the Steps

  1. Add new database field for Date of Birth
  2. Add a new GUI field for Date of Birth
  3. Add GUI code to read Date of Birth
  4. Add DAO code to read/write Date of Birth

Step 1: Add new a new datebase field for Date of Birth

We need to add a new database field for Date or Birth.

Here’s the updated SQL for the Employee table.

CREATE TABLE <code>employees</code> (
  <code>id</code> int(11) NOT NULL AUTO_INCREMENT,
  <code>last_name</code> varchar(64) DEFAULT NULL,
  <code>first_name</code> varchar(64) DEFAULT NULL,
  <code>email</code> varchar(64) DEFAULT NULL,
  <code>department</code> varchar(64) DEFAULT NULL,
  <code>salary</code> DECIMAL(10,2) DEFAULT NULL,
  <code>date_of_birth</code> DATE DEFAULT NULL,
  PRIMARY KEY (<code>id</code>)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

Step 2: Add a new GUI field for Date of Birth

We need to add a new  field for the Date of Birth field. This field will make use a formatting to make sure the user can only enter numbers for the date. Also, when they attempt to save, we will validate the date field.

Here’s a screen shot of the GUI. Notice the new Date of Birth field.

To make sure the user only enters numbers for the date, we make use of a mask formatter. Here’s the source code for this section:

File: EmployeeDialog.java


String maskFormat = "##/##/####";
MaskFormatter maskFormatter = null;

try {
  maskFormatter = new MaskFormatter(maskFormat);
} catch (Exception exc) {
  exc.printStackTrace();
}

dobFormattedTextField = new JFormattedTextField(maskFormatter);

Step 3: Add GUI code to read Date of Birth

When the user enters the date and selects Save, we need to retrieve the date and set it on the employee object. When we read the date, it is initially a String. We need to convert it to a date object. If the String is an invalid date then this will throw an exception. We can inform the user of the invalid date.

File: EmployeeDialog.java

protected void saveEmployee() {

	// get the employee info from gui
	String firstName = firstNameTextField.getText();
	String lastName = lastNameTextField.getText();
	String email = emailTextField.getText();

	String salaryStr = salaryTextField.getText();
	BigDecimal salary = convertStringToBigDecimal(salaryStr);

	Date dateOfBirth = null;

	try {
		dateOfBirth = getEmployeeDateOfBirth();
	} catch (Exception exc) {
		JOptionPane.showMessageDialog(this,
				"Invalid Date of Birth: " + exc.getMessage(), "Error",
				JOptionPane.ERROR_MESSAGE);
		return;
	}
	...
}

	private Date getEmployeeDateOfBirth() throws Exception {
		
		Date theDate = null;
		String stripped = null;
		
		String info = dobFormattedTextField.getText();
		
		// remove the mask characters
		if (info != null) {
			stripped = info.replaceAll("/", "");
		}
		
		// check for valid date
		if (stripped != null && stripped.trim().length() > 0) {
			theDate = dateFormat.parse(info);
		}
		else {
			theDate = null;
		}
				
		return theDate;
	}

Step 4: Add DAO code to read/write Date of Birth

Finally, in our DAO code, we need to add code to read and write the new date of birth field.

Here’s the snippet for inserting the date into the database.

File: EmployeeDAO.java

	public void addEmployee(Employee theEmployee, int userId) throws Exception {
		PreparedStatement myStmt = null;

		try {
			// prepare statement
			myStmt = myConn.prepareStatement("insert into employees"
					+ " (first_name, last_name, email, salary, date_of_birth)"
					+ " values (?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
			
			// set params
			myStmt.setString(1, theEmployee.getFirstName());
			myStmt.setString(2, theEmployee.getLastName());
			myStmt.setString(3, theEmployee.getEmail());
			myStmt.setBigDecimal(4, theEmployee.getSalary());
			
			java.sql.Date dateOfBirth = null;
			if (theEmployee.getDateOfBirth() != null) {
				dateOfBirth = new java.sql.Date(theEmployee.getDateOfBirth().getTime());
			}

			myStmt.setDate(5, dateOfBirth);
			
			// execute SQL
			myStmt.executeUpdate();	
			
...
}

Now, here’s the code for reading the date field.

File: EmployeeDAO.java

	private Employee convertRowToEmployee(ResultSet myRs) throws SQLException {
		
		int id = myRs.getInt("id");
		String lastName = myRs.getString("last_name");
		String firstName = myRs.getString("first_name");
		String email = myRs.getString("email");
		BigDecimal salary = myRs.getBigDecimal("salary");
		Date dateOfBirth = myRs.getDate("date_of_birth");
		
		Employee tempEmployee = new Employee(id, lastName, firstName, email, salary, dateOfBirth);
		
		return tempEmployee;
	}

Wrap Up

In this post, I wanted to highlight the major changes. I made additional changes to the app such as adding the date field in the Employee class and updating the code in the EmployeeTableModel. You can download the code and review all of those changes. Just search for “dateOfBirth” in the source files.

Download Java Source Code



Java JDBC Tutorial – Part 12.10: Connect Java Swing GUI to a MySQL Database

In this video we will learn how to connect a Java GUI to a MySQL database. We will add support to check a user’s password during login.

Note: All initial passwords are set to: java <--(all lower-case) Please SUBSCRIBE to my YouTube channel.

Download Java Source Code



Java JDBC Tutorial – Part 12.9: Connect Java Swing GUI to a MySQL Database

In this video we will learn how to connect a Java GUI to a MySQL database. We will link database tables to keep track of an employee’s audit history.

Please SUBSCRIBE to my YouTube channel.

About the Program

This program allows a user to add and update employees. Each action on the employee is tracked in the audit_history table.
This demonstrates the linking of database tables. In this case, the audit_history table is linked to the employees table and users table.

To Run the Program

1. Run the MySQL DB script: sql/table-setup.sql
– This script has two new tables: audit_history and users

2. Open the Project in Eclipse
– Run the main program: EmployeeSearchApp.java

3. Login to the application
a. At the login screen, select any employee from the drop-down list
b. The password field is not used, you can skip it
c. Press OK to login

Note: at this point, all of your employee actions will be associated with the user that you just logged in as.

4. Add a new employee
– Click the “Add Employee” button
– Fill out the employee info and save the employee

Note: the employee is saved and a new record is added to the audit_history table

5. View the Audit History
– Select the same employee from the list
– Click the “View History” button
– You will see the actions performed on this employee

You can perform similar actions for update employee.

—-

SQL Table:
The audit history is stored in the table:  audit_history

You can use the following SQL to view the entries in the table:

SELECT history.user_id, history.employee_id, history.action, history.action_date_time, users.first_name, users.last_name
FROM demo.audit_history history, demo.users users
where history.user_id=users.id;

Download Java Source Code