Java tutorial
/* * Copyright 2011 Research Studios Austria Forschungsgesellschaft mBH * * This file is part of easyrec. * * easyrec is free software: you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * the Free Software Foundation, either version 3 of the License, or * (at your option) any later version. * * easyrec is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with easyrec. If not, see <http://www.gnu.org/licenses/>. */ package org.easyrec.mahout.store.impl; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.apache.mahout.cf.taste.common.NoSuchItemException; import org.apache.mahout.cf.taste.common.NoSuchUserException; import org.apache.mahout.cf.taste.common.TasteException; import org.apache.mahout.cf.taste.impl.common.FastIDSet; import org.apache.mahout.cf.taste.impl.common.LongPrimitiveIterator; import org.apache.mahout.cf.taste.impl.model.GenericItemPreferenceArray; import org.apache.mahout.cf.taste.impl.model.GenericPreference; import org.apache.mahout.cf.taste.impl.model.GenericUserPreferenceArray; import org.apache.mahout.cf.taste.model.PreferenceArray; import org.easyrec.mahout.store.MahoutDataModelMappingDAO; import org.easyrec.mahout.store.iterator.LongResultSetIteratorMysql; import org.easyrec.store.dao.BaseActionDAO; import org.easyrec.utils.spring.cache.annotation.ShortCacheable; import org.easyrec.utils.spring.store.dao.annotation.DAO; import org.springframework.dao.EmptyResultDataAccessException; import org.springframework.jdbc.core.ResultSetExtractor; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.support.JdbcDaoSupport; import javax.sql.DataSource; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Types; import java.text.MessageFormat; import java.util.Date; /** * This class provides methods to access data in a datamining/rulemining database. * <p/> * <p><b>Company: </b> * SAT, Research Studios Austria</p> * <p/> * <p><b>Copyright: </b> * (c) 2006</p> * <p/> * <p><b>last modified:</b><br/> * $Author: pmarschik $<br/> * $Date: 2011-02-11 18:35:47 +0100 (Fr, 11 Feb 2011) $<br/> * $Revision: 17681 $</p> * * @author David Mann */ @DAO public class MahoutDataModelMappingDAOMysqlImpl extends JdbcDaoSupport implements MahoutDataModelMappingDAO { FastIDSetExtractor fastIDSetExtractor = new FastIDSetExtractor(); GenericPreferenceRowMapper genericPreferenceRowMapper = new GenericPreferenceRowMapper(); GenericBooleanPreferenceRowMapper genericBooleanPreferenceRowMapper = new GenericBooleanPreferenceRowMapper(); private final String getUserIDsQuery = MessageFormat.format( "SELECT DISTINCT {0} FROM {1} WHERE {2} = ? AND {3} <= ? AND {4} = ?", BaseActionDAO.DEFAULT_USER_COLUMN_NAME, BaseActionDAO.DEFAULT_TABLE_NAME, BaseActionDAO.DEFAULT_TENANT_COLUMN_NAME, BaseActionDAO.DEFAULT_ACTION_TIME_COLUMN_NAME, BaseActionDAO.DEFAULT_ACTION_TYPE_COLUMN_NAME); private final String getPreferencesFromUserQuery = MessageFormat.format( "SELECT DISTINCT {0}, {1} ,{2} FROM {3} WHERE {4} = ? AND {5} <= ? AND {6} = ? AND {7} = ?", BaseActionDAO.DEFAULT_USER_COLUMN_NAME, BaseActionDAO.DEFAULT_ITEM_COLUMN_NAME, BaseActionDAO.DEFAULT_RATING_VALUE_COLUMN_NAME, BaseActionDAO.DEFAULT_TABLE_NAME, BaseActionDAO.DEFAULT_TENANT_COLUMN_NAME, BaseActionDAO.DEFAULT_ACTION_TIME_COLUMN_NAME, BaseActionDAO.DEFAULT_USER_COLUMN_NAME, BaseActionDAO.DEFAULT_ACTION_TYPE_COLUMN_NAME); private final String getItemIDsFromUserQuery = MessageFormat.format( "SELECT DISTINCT {0} FROM {1} WHERE {2} = ? AND {3} <= ? and {4} = ? AND {5} = ?", BaseActionDAO.DEFAULT_ITEM_COLUMN_NAME, BaseActionDAO.DEFAULT_TABLE_NAME, BaseActionDAO.DEFAULT_TENANT_COLUMN_NAME, BaseActionDAO.DEFAULT_ACTION_TIME_COLUMN_NAME, BaseActionDAO.DEFAULT_USER_COLUMN_NAME, BaseActionDAO.DEFAULT_ACTION_TYPE_COLUMN_NAME); private final String getItemIDsQuery = MessageFormat.format( "SELECT DISTINCT {0} FROM {1} WHERE {2} = ? AND {3} <= ? AND {4} = ?", BaseActionDAO.DEFAULT_ITEM_COLUMN_NAME, BaseActionDAO.DEFAULT_TABLE_NAME, BaseActionDAO.DEFAULT_TENANT_COLUMN_NAME, BaseActionDAO.DEFAULT_ACTION_TIME_COLUMN_NAME, BaseActionDAO.DEFAULT_ACTION_TYPE_COLUMN_NAME); private final String getPreferencesForItemQuery = MessageFormat.format( "SELECT {0}, {1} ,{2} FROM {3} WHERE {4} = ? AND {5} <= ? AND {6}=? AND {7} = ? ORDER BY {6}", BaseActionDAO.DEFAULT_USER_COLUMN_NAME, BaseActionDAO.DEFAULT_ITEM_COLUMN_NAME, BaseActionDAO.DEFAULT_RATING_VALUE_COLUMN_NAME, BaseActionDAO.DEFAULT_TABLE_NAME, BaseActionDAO.DEFAULT_TENANT_COLUMN_NAME, BaseActionDAO.DEFAULT_ACTION_TIME_COLUMN_NAME, BaseActionDAO.DEFAULT_ITEM_COLUMN_NAME, BaseActionDAO.DEFAULT_ACTION_TYPE_COLUMN_NAME); private final String getPreferenceQuery = MessageFormat.format( "SELECT {0} FROM {1} WHERE {2} = ? AND {5} <= ? AND {3}=? AND {4}=? AND {6} = ? ORDER BY {5} DESC LIMIT 1", BaseActionDAO.DEFAULT_RATING_VALUE_COLUMN_NAME, BaseActionDAO.DEFAULT_TABLE_NAME, BaseActionDAO.DEFAULT_TENANT_COLUMN_NAME, BaseActionDAO.DEFAULT_USER_COLUMN_NAME, BaseActionDAO.DEFAULT_ITEM_COLUMN_NAME, BaseActionDAO.DEFAULT_ACTION_TIME_COLUMN_NAME, BaseActionDAO.DEFAULT_ACTION_TYPE_COLUMN_NAME); private final String getBooleanPreferenceQuery = MessageFormat.format( "SELECT COUNT(*) FROM {0} WHERE {1} = ? AND {4} <= ? AND {2}=? AND {3}=? AND {5} = ? ORDER BY {4} DESC LIMIT 1", BaseActionDAO.DEFAULT_TABLE_NAME, BaseActionDAO.DEFAULT_TENANT_COLUMN_NAME, BaseActionDAO.DEFAULT_USER_COLUMN_NAME, BaseActionDAO.DEFAULT_ITEM_COLUMN_NAME, BaseActionDAO.DEFAULT_ACTION_TIME_COLUMN_NAME, BaseActionDAO.DEFAULT_ACTION_TYPE_COLUMN_NAME); private final String getPreferenceTimeQuery = MessageFormat.format( "SELECT {0} FROM {1} WHERE {2} = ? AND {0} <= ? AND {3}=? AND {4}=? AND {5} = ? ORDER BY {0} DESC LIMIT 1", BaseActionDAO.DEFAULT_ACTION_TIME_COLUMN_NAME, BaseActionDAO.DEFAULT_TABLE_NAME, BaseActionDAO.DEFAULT_TENANT_COLUMN_NAME, BaseActionDAO.DEFAULT_USER_COLUMN_NAME, BaseActionDAO.DEFAULT_ITEM_COLUMN_NAME, BaseActionDAO.DEFAULT_ACTION_TYPE_COLUMN_NAME); private final String getNumUsersQuery = MessageFormat.format( "SELECT COUNT(DISTINCT {0}) FROM {1} WHERE {2} = ? AND {3} <= ? AND {4} = ?", BaseActionDAO.DEFAULT_USER_COLUMN_NAME, BaseActionDAO.DEFAULT_TABLE_NAME, BaseActionDAO.DEFAULT_TENANT_COLUMN_NAME, BaseActionDAO.DEFAULT_ACTION_TIME_COLUMN_NAME, BaseActionDAO.DEFAULT_ACTION_TYPE_COLUMN_NAME); private final String getNumItemsQuery = MessageFormat.format( "SELECT COUNT(DISTINCT {0}) FROM {1} WHERE {2} = ? AND {3} <= ? AND {4} = ?", BaseActionDAO.DEFAULT_ITEM_COLUMN_NAME, BaseActionDAO.DEFAULT_TABLE_NAME, BaseActionDAO.DEFAULT_TENANT_COLUMN_NAME, BaseActionDAO.DEFAULT_ACTION_TIME_COLUMN_NAME, BaseActionDAO.DEFAULT_ACTION_TYPE_COLUMN_NAME); private final String getNumUsersWithPreferenceForQuery = MessageFormat.format( "SELECT COUNT(DISTINCT {0},{1}) FROM {2} WHERE {3} = ? AND {4} <= ? AND {1} = ? AND {5} = ?", BaseActionDAO.DEFAULT_USER_COLUMN_NAME, BaseActionDAO.DEFAULT_ITEM_COLUMN_NAME, BaseActionDAO.DEFAULT_TABLE_NAME, BaseActionDAO.DEFAULT_TENANT_COLUMN_NAME, BaseActionDAO.DEFAULT_ACTION_TIME_COLUMN_NAME, BaseActionDAO.DEFAULT_ACTION_TYPE_COLUMN_NAME); private final String getNumUsersWithPreferenceForTwoQuery = MessageFormat.format( "SELECT COUNT(*) FROM (SELECT {0} FROM {2} WHERE {3} = ? AND {4} <= ? AND ({1} = ? OR {1} = ?) AND {5} = ? group by {0} having count(distinct {1}) = 2) as mycount", BaseActionDAO.DEFAULT_USER_COLUMN_NAME, BaseActionDAO.DEFAULT_ITEM_COLUMN_NAME, BaseActionDAO.DEFAULT_TABLE_NAME, BaseActionDAO.DEFAULT_TENANT_COLUMN_NAME, BaseActionDAO.DEFAULT_ACTION_TIME_COLUMN_NAME, BaseActionDAO.DEFAULT_ACTION_TYPE_COLUMN_NAME); private final String hasPreferenceValuesQuery = MessageFormat.format( "SELECT (COUNT(DISTINCT {0},{1}) > 0) FROM {2} WHERE {3} = ? AND {4} <= ? AND {5} = ?", BaseActionDAO.DEFAULT_USER_COLUMN_NAME, BaseActionDAO.DEFAULT_ITEM_COLUMN_NAME, BaseActionDAO.DEFAULT_TABLE_NAME, BaseActionDAO.DEFAULT_TENANT_COLUMN_NAME, BaseActionDAO.DEFAULT_ACTION_TIME_COLUMN_NAME, BaseActionDAO.DEFAULT_ACTION_TYPE_COLUMN_NAME); private final String userExistsQuery = MessageFormat.format( "Select count(*) from {0} where {1}=? and {2}=? AND {3} <= ? AND {4} = ?", BaseActionDAO.DEFAULT_TABLE_NAME, BaseActionDAO.DEFAULT_USER_COLUMN_NAME, BaseActionDAO.DEFAULT_TENANT_COLUMN_NAME, BaseActionDAO.DEFAULT_ACTION_TIME_COLUMN_NAME, BaseActionDAO.DEFAULT_ACTION_TYPE_COLUMN_NAME); public MahoutDataModelMappingDAOMysqlImpl(DataSource dataSource) { setDataSource(dataSource); } @ShortCacheable @Override public LongPrimitiveIterator getUserIDs(int tenantId, Date cutoffDate, int actionTypeId) { Object[] args = new Object[] { tenantId, cutoffDate, actionTypeId }; int[] argTypes = new int[] { Types.INTEGER, Types.TIMESTAMP, Types.INTEGER }; return new LongResultSetIteratorMysql(getDataSource(), getUserIDsQuery, args, argTypes); } @Override public PreferenceArray getPreferencesFromUser(int tenantId, Date cutoffDate, long userID, int actionTypeId) throws TasteException { Object[] args = new Object[] { tenantId, cutoffDate, userID, actionTypeId }; int[] argTypes = new int[] { Types.INTEGER, Types.TIMESTAMP, Types.INTEGER, Types.INTEGER }; try { return new GenericUserPreferenceArray(getJdbcTemplate().query(getPreferencesFromUserQuery, args, argTypes, genericPreferenceRowMapper)); } catch (EmptyResultDataAccessException e) { logger.warn("An error occurred!", e); throw new NoSuchUserException(userID); } } @Override public PreferenceArray getBooleanPreferencesFromUser(int tenantId, Date cutoffDate, long userID, int actionTypeId) throws TasteException { Object[] args = new Object[] { tenantId, cutoffDate, userID, actionTypeId }; int[] argTypes = new int[] { Types.INTEGER, Types.TIMESTAMP, Types.INTEGER, Types.INTEGER }; try { return new GenericUserPreferenceArray(getJdbcTemplate().query(getPreferencesFromUserQuery, args, argTypes, genericBooleanPreferenceRowMapper)); } catch (EmptyResultDataAccessException e) { logger.warn("An error occurred!", e); throw new NoSuchUserException(userID); } } @Override public FastIDSet getItemIDsFromUser(int tenantId, Date cutoffDate, long userID, int actionTypeId) throws TasteException { Object[] args = new Object[] { tenantId, cutoffDate, userID, actionTypeId }; int[] argTypes = new int[] { Types.INTEGER, Types.TIMESTAMP, Types.INTEGER, Types.INTEGER }; try { return getJdbcTemplate().query(getItemIDsFromUserQuery, args, argTypes, fastIDSetExtractor); } catch (EmptyResultDataAccessException e) { logger.warn("An error occurred!", e); throw new NoSuchUserException(userID); } } @ShortCacheable @Override public LongPrimitiveIterator getItemIDs(int tenantId, Date cutoffDate, int actionTypeId) { Object[] args = new Object[] { tenantId, cutoffDate, actionTypeId }; int[] argTypes = new int[] { Types.INTEGER, Types.TIMESTAMP, Types.INTEGER }; return new LongResultSetIteratorMysql(getDataSource(), getItemIDsQuery, args, argTypes); } @Override public PreferenceArray getPreferencesForItem(int tenantId, Date cutoffDate, long itemID, int actionTypeId) throws TasteException { Object[] args = new Object[] { tenantId, cutoffDate, itemID, actionTypeId }; int[] argTypes = new int[] { Types.INTEGER, Types.TIMESTAMP, Types.INTEGER, Types.INTEGER }; try { return new GenericItemPreferenceArray(getJdbcTemplate().query(getPreferencesForItemQuery, args, argTypes, genericPreferenceRowMapper)); } catch (EmptyResultDataAccessException e) { logger.warn("An error occurred!", e); throw new NoSuchItemException(itemID); } } @Override public PreferenceArray getBooleanPreferencesForItem(int tenantId, Date cutoffDate, long itemID, int actionTypeId) throws TasteException { Object[] args = new Object[]{tenantId, cutoffDate, itemID, actionTypeId}; int[] argTypes = new int[]{Types.INTEGER, Types.TIMESTAMP, Types.INTEGER, Types.INTEGER}; try { return new GenericItemPreferenceArray(getJdbcTemplate().query(getPreferencesForItemQuery, args, argTypes, genericBooleanPreferenceRowMapper)); } catch (EmptyResultDataAccessException e) { logger.warn("An error occurred!", e) throw new NoSuchItemException(itemID); } } @Override public Float getPreferenceValue(int tenantId, Date cutoffDate, long userID, long itemID, int actionTypeId) throws TasteException { Object[] args = new Object[] { tenantId, cutoffDate, userID, itemID, actionTypeId }; int[] argTypes = new int[] { Types.INTEGER, Types.TIMESTAMP, Types.INTEGER, Types.INTEGER, Types.INTEGER }; try { return (Float) getJdbcTemplate().queryForObject(getPreferenceQuery, args, argTypes, Float.class); } catch (EmptyResultDataAccessException e) { //as mahout/taste doesn't catch the NoSuchUserException, we don't throw it to save time logger.warn("An error occurred!", e); return null; } } @Override public Float getBooleanPreferenceValue(int tenantId, Date cutoffDate, long userID, long itemID, int actionTypeId) throws TasteException { Object[] args = new Object[] { tenantId, cutoffDate, userID, itemID, actionTypeId }; int[] argTypes = new int[] { Types.INTEGER, Types.TIMESTAMP, Types.INTEGER, Types.INTEGER, Types.INTEGER }; int numberOfActions = getJdbcTemplate().queryForInt(getBooleanPreferenceQuery, args, argTypes); if (numberOfActions == 0) { //as mahout/taste doesn't catch the NoSuchUserException, we don't throw it to save time return null; } else { return 1f; } } @Override public Long getPreferenceTime(int tenantId, Date cutoffDate, long userID, long itemID, int actionTypeId) throws TasteException { Object[] args = new Object[] { tenantId, cutoffDate, userID, itemID, actionTypeId }; int[] argTypes = new int[] { Types.INTEGER, Types.TIMESTAMP, Types.INTEGER, Types.INTEGER, Types.INTEGER }; try { return getJdbcTemplate().queryForObject(getPreferenceTimeQuery, args, argTypes, Date.class).getTime(); } catch (EmptyResultDataAccessException e) { //as mahout/taste doesn't catch the NoSuchUserException, we don't throw it to save time logger.warn("An error occurred!", e); return null; } } @ShortCacheable @Override public int getNumItems(int tenantId, Date cutoffDate, int actionTypeId) { Object[] args = new Object[] { tenantId, cutoffDate, actionTypeId }; int[] argTypes = new int[] { Types.INTEGER, Types.TIMESTAMP, Types.INTEGER }; return getJdbcTemplate().queryForInt(getNumItemsQuery, args, argTypes); } @ShortCacheable @Override public int getNumUsers(int tenantId, Date cutoffDate, int actionTypeId) { Object[] args = new Object[] { tenantId, cutoffDate, actionTypeId }; int[] argTypes = new int[] { Types.INTEGER, Types.TIMESTAMP, Types.INTEGER }; return getJdbcTemplate().queryForInt(getNumUsersQuery, args, argTypes); } @Override public int getNumUsersWithPreferenceFor(int tenantId, Date cutoffDate, long itemID, int actionTypeId) { Object[] args = new Object[] { tenantId, cutoffDate, itemID, actionTypeId }; int[] argTypes = new int[] { Types.INTEGER, Types.TIMESTAMP, Types.INTEGER, Types.INTEGER }; return getJdbcTemplate().queryForInt(getNumUsersWithPreferenceForQuery, args, argTypes); } @Override public int getNumUsersWithPreferenceFor(int tenantId, Date cutoffDate, long itemID1, long itemID2, int actionTypeId) { Object[] args = new Object[] { tenantId, cutoffDate, itemID1, itemID2, actionTypeId }; int[] argTypes = new int[] { Types.INTEGER, Types.TIMESTAMP, Types.INTEGER, Types.INTEGER, Types.INTEGER }; return getJdbcTemplate().queryForInt(getNumUsersWithPreferenceForTwoQuery, args, argTypes); } @Override public boolean hasPreferenceValues(int tenantId, Date cutoffDate, int actionTypeId) { Object[] args = new Object[] { tenantId, cutoffDate, actionTypeId }; int[] argTypes = new int[] { Types.INTEGER, Types.TIMESTAMP, Types.INTEGER }; return (getJdbcTemplate().queryForInt(hasPreferenceValuesQuery, args, argTypes) == 1); } @Override public float getMaxPreference(int tenantId, Date cutoffDate, int actionTypeId) { return 10f; // TODO : SELECT MIN(ratingValue) FROM ACTION // WHERE tenantId = 45 AND actionTime <= // "2011-06-18 21:12:21" AND ratingValue != ""; } @Override public float getMinPreference(int tenantId, Date cutoffDate, int actionTypeId) { return 0f; } private boolean userExists(int tenantId, Date cutoffDate, long userID, int itemTypeId) { Object[] args = new Object[] { userID, tenantId, cutoffDate, itemTypeId }; int[] argTypes = new int[] { Types.INTEGER, Types.INTEGER, Types.TIMESTAMP, Types.INTEGER }; return getJdbcTemplate().queryForInt(userExistsQuery, args, argTypes) != 0; } /* Here you will find the RowMapper & DataSet Extractors used to handle the data relieved from the sql requests. */ /** * This DataSet Extractor is used to Map the mysql result to the FastIDSet Java object. */ private static class FastIDSetExtractor implements ResultSetExtractor<FastIDSet> { // logging private final Log logger = LogFactory.getLog(this.getClass()); @Override public FastIDSet extractData(ResultSet rs) { FastIDSet result = new FastIDSet(); try { while (rs.next()) { result.add(rs.getLong(1)); } } catch (SQLException e) { logger.error("An error occured during FastIDSet ResultSet extraction", e); throw new RuntimeException(e); } return result; } } /** * This RowMapper is used to Map the mysql result Row's to the GenericPreference Java object. */ private class GenericPreferenceRowMapper implements RowMapper { @Override public Object mapRow(ResultSet rs, int rowNum) throws SQLException { return new GenericPreference(rs.getLong(1), rs.getLong(2), rs.getFloat(3)); } } /** * This RowMapper is used to Map the mysql result Row's to the GenericPreference Java object. * its a special implementation to handle boolean recommender which only have BUY VIEW Actions. */ private class GenericBooleanPreferenceRowMapper implements RowMapper { @Override public Object mapRow(ResultSet rs, int rowNum) throws SQLException { return new GenericPreference(rs.getLong(1), rs.getLong(2), 1.0f); } } }