Archive for January, 2015
Java JDBC Tutorial – Using MySQL Triggers to keep copy of previous value
Last Updated on Monday, 26 January 2015 Written by Chad Darby Monday, 26 January 2015
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.
Java JDBC Tutorial – Inserting Data with User Input
Last Updated on Thursday, 16 June 2016 Written by Chad Darby Friday, 23 January 2015
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: firstname.lastname@example.org 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.
Java JDBC Tutorial – Inserting Date and Time
Last Updated on Tuesday, 4 October 2016 Written by Chad Darby Tuesday, 6 January 2015
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: