com.planetj.taste.impl.model.jdbc
Class MySQLJDBCDataModel

java.lang.Object
  com.planetj.taste.impl.model.jdbc.AbstractJDBCDataModel
      com.planetj.taste.impl.model.jdbc.MySQLJDBCDataModel

All Implemented Interfaces:

Refreshable, DataModel, JDBCDataModel

public class MySQLJDBCDataModel
extends AbstractJDBCDataModel

A DataModel backed by a MySQL database and accessed via JDBC. It may work with other JDBC databases. By default, this class assumes that there is a DataSource available under the JNDI name "jdbc/taste", which gives access to a database with a "taste_preferences" table with the following schema:

user_id	item_id	preference
ABC	123	0.9
ABC	456	0.1
DEF	123	0.2
DEF	789	0.3

user_id must have a type compatible with the Java String type. item_id must have a type compatible with the Java String type. preference must have a type compatible with the Java double type. For example, the following command sets up a suitable table in MySQL, complete with primary key and indexes:

 CREATE TABLE taste_preferences (
   user_id VARCHAR(10) NOT NULL,
   item_id VARCHAR(10) NOT NULL,
   preference FLOAT NOT NULL,
   PRIMARY KEY (user_id, item_id),
   INDEX (user_id),
   INDEX (item_id)
 )
 

Performance Notes

See the notes in AbstractJDBCDataModel regarding using connection pooling. It's pretty vital to performance.

Some experimentation suggests that MySQL's InnoDB engine is faster than MyISAM for these kinds of applications. While MyISAM is the default and, I believe, generally considered the lighter-weight and faster of the two engines, my guess is the row-level locking of InnoDB helps here. Your mileage may vary.

Here are some key settings that can be tuned for MySQL, and suggested size for a data set of around 1 million elements:

• innodb_buffer_pool_size=64M
• myisam_sort_buffer_size=64M
• query_cache_limit=64M
• query_cache_min_res_unit=512K
• query_cache_type=1
• query_cache_size=64M

Thanks to Amila Jayasooriya for contributing MySQL notes above as part of Google Summer of Code 2007.

Author:

Sean Owen

Field Summary

Fields inherited from class com.planetj.taste.impl.model.jdbc.AbstractJDBCDataModel

DEFAULT_DATASOURCE_NAME, DEFAULT_ITEM_ID_COLUMN, DEFAULT_PREFERENCE_COLUMN, DEFAULT_PREFERENCE_TABLE, DEFAULT_USER_ID_COLUMN

Constructor Summary

MySQLJDBCDataModel()
Creates a MySQLJDBCDataModel using the default DataSource (named AbstractJDBCDataModel.DEFAULT_DATASOURCE_NAME and default table/column names.

MySQLJDBCDataModel(javax.sql.DataSource dataSource)
Creates a MySQLJDBCDataModel using the given DataSource and default table/column names.

MySQLJDBCDataModel(javax.sql.DataSource dataSource, java.lang.String preferenceTable, java.lang.String userIDColumn, java.lang.String itemIDColumn, java.lang.String preferenceColumn)
Creates a MySQLJDBCDataModel using the given DataSource and default table/column names.

MySQLJDBCDataModel(java.lang.String dataSourceName)
Creates a MySQLJDBCDataModel using the default DataSource found under the given name, and using default table/column names.

Method Summary

Methods inherited from class com.planetj.taste.impl.model.jdbc.AbstractJDBCDataModel

buildItem, buildPreference, buildUser, getDataSource, getItem, getItem, getItems, getNumItems, getNumUsers, getPreferencesForItem, getPreferencesForItemAsArray, getUser, getUsers, lookupDataSource, refresh, removePreference, setPreference

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Constructor Detail

MySQLJDBCDataModel

public MySQLJDBCDataModel()
                   throws TasteException

Creates a MySQLJDBCDataModel using the default DataSource (named AbstractJDBCDataModel.DEFAULT_DATASOURCE_NAME and default table/column names.

Throws:

TasteException - if DataSource can't be found

MySQLJDBCDataModel

public MySQLJDBCDataModel(java.lang.String dataSourceName)
                   throws TasteException

Creates a MySQLJDBCDataModel using the default DataSource found under the given name, and using default table/column names.

Parameters:

dataSourceName - name of DataSource to look up

Throws:

TasteException - if DataSource can't be found

MySQLJDBCDataModel

public MySQLJDBCDataModel(javax.sql.DataSource dataSource)

Creates a MySQLJDBCDataModel using the given DataSource and default table/column names.

Parameters:

dataSource - DataSource to use

MySQLJDBCDataModel

public MySQLJDBCDataModel(javax.sql.DataSource dataSource,
                          java.lang.String preferenceTable,
                          java.lang.String userIDColumn,
                          java.lang.String itemIDColumn,
                          java.lang.String preferenceColumn)

Creates a MySQLJDBCDataModel using the given DataSource and default table/column names.

Parameters:

dataSource - DataSource to use

preferenceTable - name of table containing preference data

userIDColumn - user ID column name

itemIDColumn - item ID column name

preferenceColumn - preference column name