Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login
×

You must be logged to download. Click here to login

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

Preparing the database in Java for Live Updates in Web Applications

In this article we will see a few of the possible methods of how to prepare the database for live updates in web application.

Introduction

It is hard to overestimate the importance of having correct data. Whether supporting executives in business decision making, providing customer support operatives with up-to-date status information, or allowing customers to make purchase decisions, accurate data is essential. And because the nature of data in a modern business is to change rapidly, accurate means up-to-date. It is therefore becoming more important than ever that information displays are updated when the data they show changes.

There are a variety of methods that can be used to update a display in a web site with new information. These vary from the simplest (prompting automatic page reloads using either the Refresh HTTP response header or its equivalent HTML “meta” tag) to much more advanced systems (using server push techniques with javascript-initiated HTTP requests, a combination commonly known as Comet, or more recently alternative data streaming APIs like WebSockets). The result is that what would once have been achieved with an ActiveX object, Java applet or even a Flash application is now much more likely to be achieved with a pure Javascript option.

In this article, I will describe a few of the possible methods of implementing this kind of feature, but before beginning we need to ensure we have a suitable application. I will describe the variation in more detail later, but for now you may consider it important to understand the basic approach that I describe in that article. I am also using Jackson for JSON serialization;

Database

Figure 1: Database

Laying the groundwork: update timestamps and publish/subscribe

The first thing we need to consider is that our application will be attempting to retrieve a list of changes that have happened since the page that is updating itself was generated. In order to achieve this, we often need to be able to examine a record and tell if it has been updated. There are two basic approaches we can use: we can add a timestamp field to each record and update it whenever we update the record, or we can use a sequentially-incremented number. I have chosen the former, because it is simpler to implement, but it is worth bearing in mind that this approach has a few drawbacks: it only works if all the servers the application runs on have synchronized clocks, and it can become confused if updates occur multiple times within the resolution of your clock. For an application with a single server, infrequent updates, and a tolerance for occasional failures, it is appropriate however.

Update sequence numbers are more complicated to implement, because you need a source of sequence numbers that is guaranteed to increase monotonically. One possible source that may be worth considering is the numbering of records in a database table whose primary key column is set up to be auto-generated using an appropriate sequence (e.g. in MySQL, a table whose key field is declared as “INT PRIMARY KEY AUTO_INCREMENT”, or in PostgreSQL a SERIAL column).

I’ve chosen to set up the balance field in the ‘accounts’ table of my example application for live updates, so the changes I need to make are as follows:

Listing 1: SQL changes

 
alter table accounts add balanceUpdated datetime not null;
update accounts set balanceUpdated=now();

I’ve chosen to use MySQL’s “datetime” type despite the fact that its resolution is only whole seconds because it allows for easier debugging (as times are presented in an understandable format). For a final application, I would consider the alternative of storing millisecond values in a “longint” field.

I need to update my Account object and the AccountDAO data access object to store, use, and read this new field:

Listing 2: Account changes

 
public class Account
{
	private int id;
	private double balance;
	private Customer customer;
	private Date opened = new Date();
	private Date balanceUpdated = new Date();
	
	public void setBalance (double balance)
	{
		this.balance = balance;
		balanceUpdated = new Date ();
	}
		
	public Date getBalanceUpdated()
	{
		return balanceUpdated;
	}
	
	public void setBalanceUpdated(Date balanceUpdated)
	{
		this.balanceUpdated = balanceUpdated;
	}

	...
}	

As well as adding a getter and setter for the new field, I arrange for it to be automatically updated whenever setBalance() is called. This means I do not need to update existing code that modifies it, however I do need to be careful in my DAO code that setBalance() is called before setBalanceUpdated(), otherwise the stored value would be lost.

As well as supporting the addition of a timestamp field so I can find changes that have already happened, I need a method for objects to be informed when new changes occur. I have chosen to use a publish/subscribe (also known as Observer or Listener) pattern to achieve this. Interested objects can inform the AccountDAO object that they want to be informed about changes, and it will then call them back with details of each Account object it stores in the database.

Listing 3: AccountDAO changes

 
	private Set<AccountChangeListener> listeners = new CopyOnWriteArraySet<>();

	private Account makeAccount (ResultSet resultSet) throws SQLException
	{
		Account result = new Account(resultSet.getInt ("account_id"));
		result.setBalance (resultSet.getDouble ("account_balance"));
		result.setCustomer (customerDAO.makeCustomer (resultSet));
		result.setOpened (new Date(resultSet.getTimestamp ("account_opened").getTime ()));
		result.setBalanceUpdated (new Date(
			resultSet.getTimestamp ("account_balanceUpdated").getTime ()));
		return result;
	}

	public boolean create (Account account) throws SQLException
	{
		try (PreparedStatement s = getConnection().prepareStatement (
			"insert into accounts (balance,customer,opened,balanceUpdated) “+
			“ values (?,?,?,?)"))
		{
			s.setDouble (1, account.getBalance());
			s.setInt (2, account.getCustomer().getId ());
			s.setTimestamp(3, new Timestamp(account.getOpened().getTime()));
			s.setTimestamp(4, new Timestamp(account.getBalanceUpdated().getTime()));

			boolean result = s.execute ();
			notifyChange(account);
			return result;
		}
	}

	public boolean update (Account account) throws SQLException
	{
		try (PreparedStatement s = getConnection().prepareStatement (
			"update accounts set balance=?, customer=?, opened=?, balanceUpdated=? “+
			“where id=?"))
		{
			s.setDouble (1, account.getBalance());
			s.setInt (2, account.getCustomer().getId ());
			s.setTimestamp(3, new Timestamp(account.getOpened().getTime()));		
			s.setTimestamp(4, new Timestamp(account.getBalanceUpdated().getTime()));	
			s.setInt (5, account.getId ());
			
			boolean result = s.execute ();
			notifyChange(account);
			return result;
		}
	}

	public List<Account> getByCustomerUpdatedSince (int id, Date since) throws SQLException
	{
		try (PreparedStatement s = getConnection().prepareStatement (
			"select "+projection ()+","+customerDAO.projection ()+
			" from accounts left join customers on accounts.customer=customers.id"+
			"  where accounts.customer=? and (" +
			"    accounts.opened > ? or " +
			"    accounts.balanceUpdated > ?)"))
		{
			s.setInt (1, id);
			Timestamp tsSince = new Timestamp(since.getTime());
			s.setTimestamp(2, tsSince);
			s.setTimestamp(3, tsSince);
			try (ResultSet r = s.executeQuery ())
			{
				ArrayList<Account> result = new ArrayList<> ();
				while (r.next ()) 
					result.add (makeAccount (r));
				return result;
			}
		}
	}
	
	public void addListener (AccountChangeListener listener)
	{
		listeners.add(listener);
	}
	public void removeListener (AccountChangeListener listener)
	{
		listeners.remove(listener);
	}
	private void notifyChange (Account changed)
	{
		for (AccountChangeListener listener : listeners)
			listener.accountChanged (changed);
	}
	static String projection ()
	{
		return "accounts.id account_id, accounts.balance account_balance, "+
	    		"accounts.customer account_customer, accounts.opened account_opened, " +
	    		"accounts.balanceUpdated account_balanceUpdated";
	}	
}

Each method has been updated to take into account the existence of the new balanceUpdated column, and I have added the listener interface AccountChangeListener (which has only the single method accountChanged(Account)) and the methods to manage the set of listeners. I use a CopyOnWriteArraySet less to avoid requiring synchronization and more to prevent the problems that would otherwise occur if a listener attempts to remove itself from the set during a notification of a change. As I expect the set of listeners to remain small, the overhead of the copy-on-write operations should be small. After each successful query in either update() or create(), calls are inserted to notifyChange(), which calls each registered AccountChangeListener.

With these changes made, I’m ready to start working on an implementation of live updates, which I will begin in the next article.

This is all for this article. See you next time.



My main area of specialization is Java and J2EE. I have worked on many international projects like Recorders,Websites,Crawlers etc.Also i am an Oracle Certified java professional as well as DB2 certified

What did you think of this post?
Services
[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login