Solutions to IT problems

Solutions I found when learning new IT stuff

Posts Tagged ‘datatables

Creating a Framework for Chemical Structure Search – Part 9

leave a comment »

Series Overview

This is Part 9 – Putting it all together of the “Creating a Framework for Chemical Structure Search“-Series.

Previous posts:


In this final post I’m going to show you a basic Spring MVC 3 Web Application I made based on MoleculeDatabaseFramework.


This Web Application MDFSimpleWebApp lets you

  • import ChemicalCompounds from an SD-File
  • do a chemical substructure search for compounds
  • view the search hits in a paged, tabular fashion
  • view individual search hits
  • download all search hits as SD-File

This is of course only a subset of all the features offered by MoleculeDatabaseFramework but it gives you a general idea how the framework works in terms of writing code and performance.


MDFSimpleWebApp contains 1 ChemicalCompound implementation called SimnpleCompound. It is the most basic possible implementation of ChemicalCompound with no additional properties.

There is also the entity SimpleLot which extends Containable. However it is not yet currently used within the application.

Repository and Service

MoleculeDatabaseFramework requires that you create a repository interface, a repository implementation (for chemical structure searching), a service interface and a service for each of your entities. Hence I created a SimpleCompoundRepository, SimpleCompoundRepositoryImpl, SimpleCompoundService and SimpleCompoundServiceImpl. These classes offer no custom search methods. They just implement all the methods required by the framework. See the Repository- and Service Packages.


This is the controller for SimpleCompound. The controller takes web requests and passes them on the Service Layer, in this case this is SimpleCompoundService, an implementation of ChemicalCompoundService. The controller exposes certain methods from the service like importing of SD-Files, chemical substructure searching or image rendering of chemical structures.

Rendering Images of chemical structures

For displaying chemical compounds I choose the option to dynamically generate images of all chemical structures in the compound. This functionality is also provided by MoleculeDatabaseFramework. Hence the according controller method is very simple:

@RequestMapping(value = "/{compoundId}/render", method = RequestMethod.GET)
public void renderCompound(@PathVariable Long compoundId,
		final HttpServletResponse response,
		@RequestParam(defaultValue = "500") int width,
		@RequestParam(defaultValue = "150") int height) throws IOException {
	try (ServletOutputStream out = response.getOutputStream()) {
		IAtomContainer mol = compoundService.getCdkMolecule(compoundId);
		MoleculeRenderer renderer = new MoleculeRenderer(width, height);
		renderer.renderMolecule(mol, out);

and in a web page you just need to add the according image tag.

JSP with JSTL:

<img src="<c:url value="/compound/${compound.getId()}/render?width=500&height=300"/>" />

Or generated in JavaScript:

var html = <img alt="' + smiles + '" src="/MDFSimpleWebApp/compound/'+ compoundId + '/render" />
// insert image into existing html element

As example here an image of the web page for viewing a compound:

rendering example

Importing SD-File

For uploading a file using Spring MVC 3 I followed this tutorial. I had to create the very simple class FileUploadForm and the controller method is rather simple too:

@RequestMapping(value = "/import", method = RequestMethod.POST)
public String importCompounds(Model model, FileUploadForm fileUploadForm,
		BindingResult result)
		throws IOException {

	if (result.hasErrors()) {
		model.addAttribute("hasError", true);
		model.addAttribute("bindingResult", result);
		return "importCompounds";
	Reader reader = new InputStreamReader(fileUploadForm.getFileData().getInputStream(), "US-ASCII");
	EntityImportResult importResult = compoundService.importSDF(reader, true);

	model.addAttribute("hasError", false);
	model.addAttribute("imported", importResult.getImportedEntities().size());
	model.addAttribute("present", importResult.getEntitiesAlreadyInDatabase().size());
	model.addAttribute(new FileUploadForm());

	return "importCompounds";

Chemical Structure Search

Search Form

The Chemical Structure Search is made up of a page that contains a tool for drawing chemical structures and submitting the search and the actual page for displaying search results. For drawing chemical structures MDFSimpleWebApp initially used the JChemPaint Applet but I recently changed it to JSME, a JavaScript based drawing tool. See below the search form with JSME:

Chemical Structure Search Form

Search Result Page

The search results page relies heavily on AJAX using JQuery and the JQuery plugin datatables. The search hits are displayed in paged fashion using datatables server-side processing and hence only 1 page of results is fetched from the database. The results table contains an image of the chemical structure, the compounds name and its CAS number. Clicking on the image will show a JavaScript alert containing the SMILES String of the given chemical structure.

Search Results Page

For each new page a AJAX request is sent to the server and the according page is returned. Note that the initial load of the page can take a bit longer. This is due to the fact that the total amount of hits is determined (eg. no SQL LIMIT-Clause). This count is cached so that all page requests are as fast. However due to how OFFSET and LIMIT work, the higher the page number, the longer the search takes. So if you have a high number of hits (eg. several thousands) the last page will load slower than the first one. If you want to display search hits 10’000 to 10’004 the database will search up to hit number 10’004 and then return the last 5 hits. However in general you should improve your search if you get so many hits.

After the page is returned from the server, the data must be converted to JSON and in a format expected by datatables. To achieve that I create the helper class JQueryDatatablesPage that contains all the properties that datatables requires and the according getters and setters. JQueryDatatablesPage is then converted to JSON using Jackson 2 ObjectMapper.

@RequestMapping(value = "/search", method = RequestMethod.GET, produces = "application/json")
public @ResponseBody
String search(
		@RequestParam int iDisplayStart,
		@RequestParam int iDisplayLength,
		@RequestParam int sEcho, // for datatables draw count
		@RequestParam String structure) throws IOException {

	int pageNumber = (iDisplayStart + 1) / iDisplayLength;
	PageRequest pageable = new PageRequest(pageNumber, iDisplayLength);
	Page<SimpleCompound> page = compoundService.findByChemicalStructure(structure, StructureSearchType.SUBSTRUCTURE, pageable);
	int iTotalRecords = (int) compoundService.count(null);
	int iTotalDisplayRecords = (int) page.getTotalElements();
	JQueryDatatablesPage<SimpleCompound> dtPage = new JQueryDatatablesPage<>(
			page.getContent(), iTotalRecords, iTotalDisplayRecords,

	String result = toJson(dtPage);
	return result;


private String toJson(JQueryDatatablesPage<?> dt) throws IOException {
	ObjectMapper mapper = new ObjectMapper();
	mapper.registerModule(new Hibernate4Module());
	return mapper.writeValueAsString(dt);

Jackson 2 can deal with circular references if your entities are annotated with

@JsonIdentityInfo(generator=ObjectIdGenerators.IntSequenceGenerator.class, property="@id")

You also need to register the Hibernate4Module to deal with Lazy Collections!

Donwload of Search Hits

You can download search result hits as SD-File by clicking on the Download Hits-Link on the search results page. The browser will display a dialog were you want to save the file. This uses the exportSDF()-method of SimpleCompoundService.

@RequestMapping(value = "/downloadHits", method = RequestMethod.GET)
public void downloadHits(@RequestParam String structure, HttpServletResponse response) throws IOException {

	List<Long> ids = compoundService.findByChemicalStructure(structure, StructureSearchType.SUBSTRUCTURE);
	HashSet<String> properties = new HashSet<>();
	String disposition = "attachment; fileName=searchHits-" + structure + ".sdf";
	response.setHeader("Content-Disposition", disposition);
	ServletOutputStream output = response.getOutputStream();
	OutputStreamWriter writer = new OutputStreamWriter (output);
	compoundService.exportSDF(ids, writer, properties);

Final Words

See below a demo video of a Chemical Substructure Search in MDFSimpleWebApp with a database of 65’000 compounds. The demo runs on a dual-core mobile i5 running Windows 7 32-bit with 4 GB of RAM installed or said otherwise: The hardware is pretty mediocre.

MDFSimpleWebApp is hosted on bitbucket. If you want to try out this application you can go to the download section on bitbucket and download a fully working standalone version for Windows 64-bit including PostgreSQL, the Bingo Cartridge for Chemical Structure Searching, tomcat as servlet container and this web application. Note: This file is 105 MB due to PostgreSQL and tomcat being included.


Written by kienerj

June 6, 2013 at 12:41