Today is Wednesday, 11th January 2017

Archive for the ‘JDBC’ Category


Java JDBC Tutorial – Using MySQL Triggers to keep copy of previous value

I recently had a person ask the following question:

For the employees table, every time I insert a new value in salary column the previous value is overwritten. Can we add new inserted value to the previous salary value before it is overwritten?

Yes, we can accomplish this by using MySQL Triggers. Here’s a quick run of the application so you can see the output.

Employee Salaries
Doe     John     Salary=55000.00     Previous Salary=0.00
Public     Mary     Salary=75000.00     Previous Salary=0.00
Queue     Susan     Salary=130000.00     Previous Salary=0.00
Enter new salary for employees: 25000
Employee Salaries
Doe     John     Salary=25000.00     Previous Salary=55000.00
Public     Mary     Salary=25000.00     Previous Salary=75000.00
Queue     Susan     Salary=25000.00     Previous Salary=130000.00

MySQL supports triggers. Triggers are similar to stored procedures but they are “triggered” when certain events happen on the database. For example, you can have SQL code that is triggered when data is inserted, updated or deleted. You can read more details about MySQL triggers at this link. But for now, I’ll cover the basics of MySQL triggers to address the question at hand.

We’ll use a trigger to copy the old salary information into a new column for later use. Here’s the database table we’ll use, make note of the new column: previous_salary.

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>previous_salary</code> DECIMAL(10,2) DEFAULT NULL,
  PRIMARY KEY (<code>id</code>)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

Here’s the code for the trigger. In a nutshell, it will copy the old salary information to the new previous_salary column. This is SQL code that you will execute with your MySQL admin tool.

delimiter $$
CREATE TRIGGER salary_trigger BEFORE UPDATE on employees
FOR EACH ROW BEGIN
set NEW.previous_salary=OLD.salary;
END$$

Now that the trigger is in place, whenever we perform an update on the database, then it will execute the trigger. Here’s a Java code snippet where we read a new salary from the user. Then we perform the update in the database:

//
// UPDATE SALARIES ... read information from the user
//
// read new salary from command line
scanner = new Scanner(System.in);
            
System.out.print("Enter new salary for employees: ");
double newSalary = scanner.nextDouble();
            
// perform salary update
salaryStmt = myConn.prepareStatement("update employees set salary=?");
salaryStmt.setDouble(1, newSalary);
salaryStmt.executeUpdate();

That’s it. You can download the full source code, including the SQL scripts, using the link below.

Download Source Code

Share


Java JDBC Tutorial – Inserting Data with User Input

In this blog post, I’ll show you how to insert data into the database with user input. When you run the program, it will prompt you to enter your last name, first name and email address. Here’s a sample run of the app:

Enter your last name: Davis
Enter your first name: Sammy
Enter your email: sammy@test.com
Insert complete.

To accomplish this, you’ll make use of the Scanner class that is defined in the java.util package. This class can read input as Strings and Java primitives. In this example, we’ll just use it to read in the last name, first name and email. Here’s the code snippet:

Now, we can use this in our JDBC code to insert data supplied by the user. We could just perform a String concatenation with the values, but that would open us up to SQL injection attacks. Instead, we’ll make use of Prepared Statements that is explained in detail in another blog post here: Java JDBC Prepared Statements.

So here’s the code for setting up the prepared statement and setting the input values from the user.

That’s it. Those are the steps for inserting data into the database with user input.

Download Source Code

Share


Java JDBC Tutorial – Inserting Date and Time

In this blog post, I’ll show you how to insert a date and time value into the database.

The date and time is collectively known as a timestamp. JDBC has builtin support for saving and reading timestamps. This support is provided by java.sql.Timestamp.

Let’s assume we have this database table schema below. Make note of the timestamp column called checkout_time.

Saving a Timestamp

You can save a timestamp value with the following code:

Reading a Timestamp

Likewise, you can read a timestamp value with the following code:

Share