CODEWITHSUNDEEP
javahsqldbin-memory-database
Thomas Buckley
Thomas Buckley

Reputation: 6056

In-memory DB to provide auto-suggest results to users

I have a list of States/Counties, their corresponsing cites and each cites corresponing towns.

- State1
  - City1
    - Town1
    - Town2
    ...
  - City2
  ...

I need a way to store these within my enterprise java web application.

The relationships between the entities must be maintained i.e. Towns belong to a city and cities belong to a state/county.

The purpose of the data is so that users can be presented with auto-suggest towns and cities and they type into text boxes.

This data may occasionaly change and need to be updated (Probably fairly rare).

So for example:
- User selects state1 from a drop down.
- They now type charchters 'san' into a city input box.
- I would like to perform a search and return a list of cities beginning with 'san' in state1. (Ajax request).

I'm looking for feedback from people that may have had to implement something similar to this before.

Did you use an in-memory DB such as HSQL?

Upvotes: 0

Views: 122

Answers (2)

Sandeep Raman
Sandeep Raman

Reputation: 57

Are you flexible to use a search server in between ? I had a similar requirement on an enterprise system and used Apache Solr (A Lucene-based Search Server) . The Solr configuration had options to parse the database and store an indexed data collection , From the user interface an Ajax request (submission throttling till 2 alphabets are pressed atleast) will invoke the API to the Solr.

This set up gave me amazingly fast results.

Upvotes: 0

Keith
Keith

Reputation: 4184

Yep, I have used HSQL for things like this before. Works well. Define your reference data as a collection of SQL insert statements, and run the schema creation script, plus reference data inserts on app start-up.

Spring now has pretty nice support for embedded databases:
http://static.springsource.org/spring/docs/3.2.x/spring-framework-reference/html/jdbc.html#jdbc-embedded-database-support

<jdbc:embedded-database id="dataSource">
    <jdbc:script location="classpath:schema.sql"/>
    <jdbc:script location="classpath:reference-data.sql"/>
</jdbc:embedded-database>

You then use the above defined dataSource exactly like any other DB, and can switch to a real DB in the future, if necessary, without affecting the any code.

Upvotes: 1

Related Questions