Start line:  
End line:  

Snippet Preview

Snippet HTML Code

Stack Overflow Questions
Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements. See the NOTICE file distributed with this work for additional information regarding copyright ownership. The ASF licenses this file to You under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.
 
 
 package org.apache.mahout.cf.taste.impl.model.jdbc;
 
 
 import java.util.List;

An abstract superclass for JDBC-related org.apache.mahout.cf.taste.model.DataModel implementations, providing most of the common functionality that any such implementation would need.

Performance will be a concern with any JDBC-based org.apache.mahout.cf.taste.model.DataModel. There are going to be lots of simultaneous reads and some writes to one table. Make sure the table is set up optimally -- for example, you'll want to establish indexes.

You'll also want to use connection pooling of some kind. Most J2EE containers like Tomcat provide connection pooling, so make sure the javax.sql.DataSource it exposes is using pooling. Outside a J2EE container, you can use packages like Jakarta's DBCP to create a javax.sql.DataSource on top of your database whose java.sql.Connections are pooled.

 
 public abstract class AbstractJDBCDataModel extends AbstractJDBCComponent implements JDBCDataModel {
 
   private static final Logger log = LoggerFactory.getLogger(AbstractJDBCDataModel.class);
 
   public static final String DEFAULT_PREFERENCE_TABLE = "taste_preferences";
   public static final String DEFAULT_USER_ID_COLUMN = "user_id";
   public static final String DEFAULT_ITEM_ID_COLUMN = "item_id";
   public static final String DEFAULT_PREFERENCE_COLUMN = "preference";
 
   private final DataSource dataSource;
   private final String preferenceTable;
   private final String userIDColumn;
   private final String itemIDColumn;
   private final String preferenceColumn;
   private final String getPreferenceSQL;
   private final String getUserSQL;
   private final String getAllUsersSQL;
   private final String getNumItemsSQL;
   private final String getNumUsersSQL;
   private final String setPreferenceSQL;
   private final String removePreferenceSQL;
   private final String getUsersSQL;
   private final String getItemsSQL;
   private final String getPrefsForItemSQL;
   private final String getNumPreferenceForItemSQL;
   private final String getNumPreferenceForItemsSQL;
   private int cachedNumUsers;
   private int cachedNumItems;
   private final Cache<LongIntegeritemPrefCounts;
 
   protected AbstractJDBCDataModel(DataSource dataSource,
                                   String getPreferenceSQL,
                                   String getUserSQL,
                                   String getAllUsersSQL,
                                   String getNumItemsSQL,
                                  String getNumUsersSQL,
                                  String setPreferenceSQL,
                                  String removePreferenceSQL,
                                  String getUsersSQL,
                                  String getItemsSQL,
                                  String getPrefsForItemSQL,
                                  String getNumPreferenceForItemSQL,
                                  String getNumPreferenceForItemsSQL) {
    this(dataSource,
        ,
        ,
        getPreferenceSQL,
        getUserSQL,
        getAllUsersSQL,
        getNumItemsSQL,
        getNumUsersSQL,
        setPreferenceSQL,
        removePreferenceSQL,
        getUsersSQL,
        getItemsSQL,
        getPrefsForItemSQL,
        getNumPreferenceForItemSQL,
        getNumPreferenceForItemsSQL);
  }
  protected AbstractJDBCDataModel(DataSource dataSource,
                                  String preferenceTable,
                                  String userIDColumn,
                                  String itemIDColumn,
                                  String preferenceColumn,
                                  String getPreferenceSQL,
                                  String getUserSQL,
                                  String getAllUsersSQL,
                                  String getNumItemsSQL,
                                  String getNumUsersSQL,
                                  String setPreferenceSQL,
                                  String removePreferenceSQL,
                                  String getUsersSQL,
                                  String getItemsSQL,
                                  String getPrefsForItemSQL,
                                  String getNumPreferenceForItemSQL,
                                  String getNumPreferenceForItemsSQL) {
    .debug("Creating AbstractJDBCModel...");
    checkNotNullAndLog("preferenceTable"preferenceTable);
    checkNotNullAndLog("userIDColumn"userIDColumn);
    checkNotNullAndLog("itemIDColumn"itemIDColumn);
    checkNotNullAndLog("preferenceColumn"preferenceColumn);
    checkNotNullAndLog("dataSource"dataSource);
    checkNotNullAndLog("getUserSQL"getUserSQL);
    checkNotNullAndLog("getAllUsersSQL"getAllUsersSQL);
    checkNotNullAndLog("getPreferenceSQL"getPreferenceSQL);
    checkNotNullAndLog("getNumItemsSQL"getNumItemsSQL);
    checkNotNullAndLog("getNumUsersSQL"getNumUsersSQL);
    checkNotNullAndLog("setPreferenceSQL"setPreferenceSQL);
    checkNotNullAndLog("removePreferenceSQL"removePreferenceSQL);
    checkNotNullAndLog("getUsersSQL"getUsersSQL);
    checkNotNullAndLog("getItemsSQL"getItemsSQL);
    checkNotNullAndLog("getPrefsForItemSQL"getPrefsForItemSQL);
    checkNotNullAndLog("getNumPreferenceForItemSQL"getNumPreferenceForItemSQL);
    checkNotNullAndLog("getNumPreferenceForItemsSQL"getNumPreferenceForItemsSQL);
    if (!(dataSource instanceof ConnectionPoolDataSource)) {
      .warn("You are not using ConnectionPoolDataSource. Make sure your DataSource pools connections " +
          "to the database itself, or database performance will be severely reduced.");
    }
    this. = preferenceTable;
    this. = userIDColumn;
    this. = itemIDColumn;
    this. = preferenceColumn;
    this. = dataSource;
    this. = getPreferenceSQL;
    this. = getUserSQL;
    this. = getAllUsersSQL;
    this. = getNumItemsSQL;
    this. = getNumUsersSQL;
    this. = setPreferenceSQL;
    this. = removePreferenceSQL;
    this. = getUsersSQL;
    this. = getItemsSQL;
    this. = getPrefsForItemSQL;
    this. = getNumPreferenceForItemSQL;
    this. = getNumPreferenceForItemsSQL;
    this. = -1;
    this. = -1;
    this. = new Cache<LongInteger>(new ItemPrefCountRetriever(getNumPreferenceForItemSQL));
  }

  

Returns:
the javax.sql.DataSource that this instance is using
  public DataSource getDataSource() {
    return ;
  }
  public String getPreferenceTable() {
    return ;
  }
  public String getUserIDColumn() {
    return ;
  }
  public String getItemIDColumn() {
    return ;
  }
  public String getPreferenceColumn() {
    return ;
  }
    .debug("Retrieving all users...");
    return new ResultSetIDIterator();
  }

  
  public PreferenceArray getPreferencesFromUser(long idthrows TasteException {
    .debug("Retrieving user ID '{}'"id);
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
      conn = .getConnection();
      stmt.setFetchSize(getFetchSize());
      setLongParameter(stmt, 1, id);
      .debug("Executing SQL query: {}");
      rs = stmt.executeQuery();
      List<Preferenceprefs = new ArrayList<Preference>();
      while (rs.next()) {
        prefs.add(buildPreference(rs));
      }
      if (prefs.isEmpty()) {
        throw new NoSuchUserException();
      }
      return new GenericUserPreferenceArray(prefs);
    } catch (SQLException sqle) {
      .warn("Exception while retrieving user"sqle);
      throw new TasteException(sqle);
    } finally {
      IOUtils.quietClose(rsstmtconn);
    }
  }
    .debug("Exporting all data");
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
      conn = .getConnection();
      stmt.setFetchSize(getFetchSize());
      .debug("Executing SQL query: {}");
      rs = stmt.executeQuery();
      Long currentUserID = null;
      List<PreferencecurrentPrefs = new ArrayList<Preference>();
      while (rs.next()) {
        long nextUserID = getLongColumn(rs, 1);
        if (currentUserID != null && !currentUserID.equals(nextUserID)) {
          if (!currentPrefs.isEmpty()) {
            result.put(currentUserIDnew GenericUserPreferenceArray(currentPrefs));
            currentPrefs.clear();
          }
        } else {
          currentPrefs.add(buildPreference(rs));
        }
        currentUserID = nextUserID;
      }
      if (!currentPrefs.isEmpty()) {
        result.put(currentUserIDnew GenericUserPreferenceArray(currentPrefs));
      }
      return result;
    } catch (SQLException sqle) {
      .warn("Exception while exporting all data"sqle);
      throw new TasteException(sqle);
    } finally {
      IOUtils.quietClose(rsstmtconn);
    }
  }
    .debug("Exporting all data");
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    FastByIDMap<FastIDSetresult = new FastByIDMap<FastIDSet>();
    try {
      conn = .getConnection();
      stmt.setFetchSize(getFetchSize());
      .debug("Executing SQL query: {}");
      rs = stmt.executeQuery();
      boolean currentUserIDSet = false;
      long currentUserID = 0L; // value isn't used
      FastIDSet currentItemIDs = new FastIDSet(2);
      while (rs.next()) {
        long nextUserID = getLongColumn(rs, 1);
        if (currentUserIDSet && currentUserID != nextUserID) {
          if (!currentItemIDs.isEmpty()) {
            result.put(currentUserIDcurrentItemIDs);
            currentItemIDs = new FastIDSet(2);
          }
        } else {
          currentItemIDs.add(getLongColumn(rs, 2));
        }
        currentUserID = nextUserID;
        currentUserIDSet = true;
      }
      if (!currentItemIDs.isEmpty()) {
        result.put(currentUserIDcurrentItemIDs);
      }
      return result;
    } catch (SQLException sqle) {
      .warn("Exception while exporting all data"sqle);
      throw new TasteException(sqle);
    } finally {
      IOUtils.quietClose(rsstmtconn);
    }
  }

  
  public FastIDSet getItemIDsFromUser(long idthrows TasteException {
    .debug("Retrieving items for user ID '{}'"id);
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
      conn = .getConnection();
      stmt.setFetchSize(getFetchSize());
      setLongParameter(stmt, 1, id);
      .debug("Executing SQL query: {}");
      rs = stmt.executeQuery();
      FastIDSet result = new FastIDSet();
      while (rs.next()) {
        result.add(getLongColumn(rs, 2));
      }
      if (result.isEmpty()) {
        throw new NoSuchUserException();
      }
      return result;
    } catch (SQLException sqle) {
      .warn("Exception while retrieving item s"sqle);
      throw new TasteException(sqle);
    } finally {
      IOUtils.quietClose(rsstmtconn);
    }
  }
  public Float getPreferenceValue(long userIDlong itemIDthrows TasteException {
    .debug("Retrieving preferences for item ID '{}'"itemID);
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
      conn = .getConnection();
      stmt.setFetchSize(1);
      setLongParameter(stmt, 1, userID);
      setLongParameter(stmt, 2, itemID);
      .debug("Executing SQL query: {}");
      rs = stmt.executeQuery();
      if (rs.next()) {
        return rs.getFloat(1);
      } else {
        return null;
      }
    } catch (SQLException sqle) {
      .warn("Exception while retrieving prefs for item"sqle);
      throw new TasteException(sqle);
    } finally {
      IOUtils.quietClose(rsstmtconn);
    }
  }
    .debug("Retrieving all items...");
    return new ResultSetIDIterator();
  }
  public PreferenceArray getPreferencesForItem(long itemIDthrows TasteException {
    List<Preferencelist = doGetPreferencesForItem(itemID);
    return new GenericItemPreferenceArray(list);
  }
  protected List<PreferencedoGetPreferencesForItem(long itemIDthrows TasteException {
    .debug("Retrieving preferences for item ID '{}'"itemID);
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
      conn = .getConnection();
      stmt.setFetchSize(getFetchSize());
      setLongParameter(stmt, 1, itemID);
      .debug("Executing SQL query: {}");
      rs = stmt.executeQuery();
      List<Preferenceprefs = new ArrayList<Preference>();
      while (rs.next()) {
        prefs.add(buildPreference(rs));
      }
      return prefs;
    } catch (SQLException sqle) {
      .warn("Exception while retrieving prefs for item"sqle);
      throw new TasteException(sqle);
    } finally {
      IOUtils.quietClose(rsstmtconn);
    }
  }
  public int getNumItems() throws TasteException {
    if ( < 0) {
       = getNumThings("items");
    }
    return ;
  }
  public int getNumUsers() throws TasteException {
    if ( < 0) {
       = getNumThings("users");
    }
    return ;
  }
  public int getNumUsersWithPreferenceFor(long... itemIDsthrows TasteException {
    if (itemIDs == null) {
      throw new IllegalArgumentException("itemIDs is null");
    }
    int length = itemIDs.length;
    if (length == 0 || length > 2) {
      throw new IllegalArgumentException("Illegal number of item IDs: " + length);
    }
    return length == 1 ?
        .get(itemIDs[0]) :
        getNumThings("user preferring items"itemIDs);
  }
  private int getNumThings(String nameString sqllong... argsthrows TasteException {
    .debug("Retrieving number of {} in model"name);
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
      conn = .getConnection();
      stmt.setFetchSize(getFetchSize());
      if (args != null) {
        for (int i = 1; i <= args.lengthi++) {
          setLongParameter(stmtiargs[i-1]);
        }
      }
      .debug("Executing SQL query: {}"sql);
      rs = stmt.executeQuery();
      rs.next();
      return rs.getInt(1);
    } catch (SQLException sqle) {
      .warn("Exception while retrieving number of " + namesqle);
      throw new TasteException(sqle);
    } finally {
      IOUtils.quietClose(rsstmtconn);
    }
  }
  public void setPreference(long userIDlong itemIDfloat valuethrows TasteException {
    if (Float.isNaN(value)) {
      throw new IllegalArgumentException("Invalid value: " + value);
    }
    .debug("Setting preference for user {}, item {}"userIDitemID);    
    Connection conn = null;
    PreparedStatement stmt = null;
    try {
      conn = .getConnection();
      stmt = conn.prepareStatement();
      setLongParameter(stmt, 1, userID);
      setLongParameter(stmt, 2, itemID);
      stmt.setDouble(3, value);
      stmt.setDouble(4, value);
      .debug("Executing SQL update: {}");
      stmt.executeUpdate();
    } catch (SQLException sqle) {
      .warn("Exception while setting preference"sqle);
      throw new TasteException(sqle);
    } finally {
      IOUtils.quietClose(nullstmtconn);
    }
  }
  public void removePreference(long userIDlong itemIDthrows TasteException {
    .debug("Removing preference for user '{}', item '{}'"userIDitemID);
    Connection conn = null;
    PreparedStatement stmt = null;
    try {
      conn = .getConnection();
      stmt = conn.prepareStatement();
      setLongParameter(stmt, 1, userID);
      setLongParameter(stmt, 2, itemID);
      .debug("Executing SQL update: {}");
      stmt.executeUpdate();
    } catch (SQLException sqle) {
      .warn("Exception while removing preference"sqle);
      throw new TasteException(sqle);
    } finally {
      IOUtils.quietClose(nullstmtconn);
    }
  }
  public void refresh(Collection<RefreshablealreadyRefreshed) {
     = -1;
     = -1;
  }
  // Some overrideable methods to customize the class behavior:
  protected Preference buildPreference(ResultSet rsthrows SQLException {
    return new GenericPreference(getLongColumn(rs, 1), getLongColumn(rs, 2), rs.getFloat(3));
  }

  
Subclasses may wish to override this if ID values in the file are not numeric. This provides a hook by which subclasses can inject an org.apache.mahout.cf.taste.model.IDMigrator to perform translation.
  protected long getLongColumn(ResultSet rsint positionthrows SQLException {
    return rs.getLong(position);
  }

  
Subclasses may wish to override this if ID values in the file are not numeric. This provides a hook by which subclasses can inject an org.apache.mahout.cf.taste.model.IDMigrator to perform translation.
  protected void setLongParameter(PreparedStatement stmtint positionlong valuethrows SQLException {
    stmt.setLong(positionvalue);
  }

  

An java.util.Iterator which returns items from a java.sql.ResultSet. This is a useful way to iterate over all user data since it does not require all data to be read into memory at once. It does however require that the DB connection be held open. Note that this class will only release database resources after hasNext() has been called and has returned false; callers should make sure to "drain" the entire set of data to avoid tying up database resources.

  private final class ResultSetIDIterator implements LongPrimitiveIterator {
    private final Connection connection;
    private final Statement statement;
    private final ResultSet resultSet;
    private boolean closed;
    private ResultSetIDIterator(String sqlthrows TasteException {
      try {
         = .getConnection();
        .debug("Executing SQL query: {}"sql);
         = .executeQuery(sql);
        boolean anyResults = .next();
        if (!anyResults) {
          close();
        }
      } catch (SQLException sqle) {
        close();
        throw new TasteException(sqle);
      }
    }
    @Override
    public boolean hasNext() {
      boolean nextExists = false;
      if (!) {
        try {
          if (.isAfterLast()) {
            close();
          } else {
            nextExists = true;
          }
        } catch (SQLException sqle) {
          .warn("Unexpected exception while accessing ResultSet; continuing..."sqle);
          close();
        }
      }
      return nextExists;
    }
    @Override
    public Long next() {
      return nextLong();
    }
    @Override
    public long nextLong() {
      if (!hasNext()) {
        throw new NoSuchElementException();
      }
      try {
        long ID = getLongColumn(, 1);
        .next();
        return ID;
      } catch (SQLException sqle) {
        // No good way to handle this since we can't throw an exception
        .warn("Exception while iterating"sqle);
        close();
        throw new NoSuchElementException("Can't retrieve more due to exception: " + sqle);
      }
    }
    @Override
    public long peek() {
      if (!hasNext()) {
        throw new NoSuchElementException();
      }
      try {
        return getLongColumn(, 1);
      } catch (SQLException sqle) {
        // No good way to handle this since we can't throw an exception
        .warn("Exception while iterating"sqle);
        close();
        throw new NoSuchElementException("Can't retrieve more due to exception: " + sqle);
      }
    }

    
    @Override
    public void remove() {
      throw new UnsupportedOperationException();
    }
    private void close() {
       = true;
      IOUtils.quietClose();
    }
    @Override
    public void skip(int n) {
      if (n >= 1) {
        try {
          advanceResultSet(n);
        } catch (SQLException sqle) {
          .warn("Exception while iterating over items"sqle);
          close();
        }
      }
    }
  }
  private class ItemPrefCountRetriever implements Retriever<LongInteger> {
    private final String getNumPreferenceForItemSQL;
    private ItemPrefCountRetriever(String getNumPreferenceForItemSQL) {
      this. = getNumPreferenceForItemSQL;
    }
    @Override
    public Integer get(Long keythrows TasteException {
      return getNumThings("user preferring item"key);
    }
  }
New to GrepCode? Check out our FAQ X