Java tutorial
/**************************************************************** * 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.james.user.jdbc; import java.io.InputStream; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.Collection; import java.util.HashMap; import java.util.Iterator; import java.util.List; import java.util.Locale; import java.util.Map; import javax.annotation.PostConstruct; import javax.inject.Inject; import javax.sql.DataSource; import org.apache.commons.configuration.ConfigurationException; import org.apache.commons.configuration.HierarchicalConfiguration; import org.apache.james.filesystem.api.FileSystem; import org.apache.james.user.api.UsersRepositoryException; import org.apache.james.user.api.model.User; import org.apache.james.user.lib.AbstractJamesUsersRepository; import org.apache.james.util.sql.JDBCUtil; import org.apache.james.util.sql.SqlResources; /** * An abstract base class for creating UserRepository implementations which use * a database for persistence. * * To implement a new UserRepository using by extending this class, you need to * implement the 3 abstract methods defined below, and define the required SQL * statements in an SQLResources file. * * The SQL statements used by this implementation are: * <table> * <tr> * <td><b>Required</b></td> * <td></td> * </tr> * <tr> * <td>select</td> * <td>Select all users.</td> * </tr> * <tr> * <td>insert</td> * <td>Insert a user.</td> * </tr> * <tr> * <td>update</td> * <td>Update a user.</td> * </tr> * <tr> * <td>delete</td> * <td>Delete a user by name.</td> * </tr> * <tr> * <td>createTable</td> * <td>Create the users table.</td> * </tr> * <tr> * <td><b>Optional</b></td> * <td></td> * </tr> * <tr> * <td>selectByLowercaseName</td> * <td>Select a user by name (case-insensitive lowercase).</td> * </tr> * </table> */ @Deprecated public abstract class AbstractJdbcUsersRepository extends AbstractJamesUsersRepository { protected Map<String, String> m_sqlParameters; private String m_sqlFileName; private DataSource m_datasource; // Fetches all Users from the db. private String m_getUsersSql; // This fetch a user by name, ensuring case-insensitive matching. private String m_userByNameCaseInsensitiveSql; // Insert, update and delete sql statements are not guaranteed // to be case-insensitive; this is handled in code. private String m_insertUserSql; private String m_updateUserSql; private String m_deleteUserSql; // The JDBCUtil helper class private JDBCUtil theJDBCUtil; private FileSystem fileSystem; /** * Removes a user from the repository * * @param userName * the user to be removed * @throws UsersRepositoryException */ public void removeUser(String userName) throws UsersRepositoryException { User user = getUserByName(userName); if (user != null) { doRemoveUser(user); } else { throw new UsersRepositoryException("User " + userName + " does not exist"); } } /** * Get the user object with the specified user name. Return null if no such * user. * * @param name * the name of the user to retrieve * * @return the user if found, null otherwise * * @since James 1.2.2 */ public User getUserByName(String name) throws UsersRepositoryException { return getUserByName(name, ignoreCase); } /** * Returns whether or not this user is in the repository * * @return true or false */ public boolean contains(String name) throws UsersRepositoryException { User user = getUserByName(name, ignoreCase); return (user != null); } /** * Returns whether or not this user is in the repository. Names are matched * on a case insensitive basis. * * @return true or false */ public boolean containsCaseInsensitive(String name) throws UsersRepositoryException { User user = getUserByName(name, true); return (user != null); } /** * Test if user with name 'name' has password 'password'. * * @param name * the name of the user to be tested * @param password * the password to be tested * * @return true if the test is successful, false if the password is * incorrect or the user doesn't exist * @since James 1.2.2 */ public boolean test(String name, String password) throws UsersRepositoryException { User user = getUserByName(name, ignoreCase); return user != null && user.verifyPassword(password); } /** * Returns a count of the users in the repository. * * @return the number of users in the repository */ public int countUsers() throws UsersRepositoryException { List<String> usernames = listUserNames(); return usernames.size(); } /** * List users in repository. * * @return Iterator over a collection of Strings, each being one user in the * repository. */ public Iterator<String> list() throws UsersRepositoryException { return listUserNames().iterator(); } /** * Set the DataSourceSelector * * @param m_datasource * the DataSourceSelector */ @Inject public void setDatasource(DataSource m_datasource) { this.m_datasource = m_datasource; } /** * Sets the filesystem service * * @param system * the new service */ @Inject public void setFileSystem(FileSystem system) { this.fileSystem = system; } /** * Initialises the JDBC repository. * <ol> * <li>Tests the connection to the database.</li> * <li>Loads SQL strings from the SQL definition file, choosing the * appropriate SQL for this connection, and performing parameter * substitution,</li> * <li>Initialises the database with the required tables, if necessary.</li> * </ol> * * @throws Exception * if an error occurs */ @PostConstruct public void init() throws Exception { StringBuffer logBuffer; if (getLogger().isDebugEnabled()) { logBuffer = new StringBuffer(128).append(this.getClass().getName()).append(".initialize()"); getLogger().debug(logBuffer.toString()); } theJDBCUtil = new JDBCUtil() { protected void delegatedLog(String logString) { AbstractJdbcUsersRepository.this.getLogger().warn("AbstractJdbcUsersRepository: " + logString); } }; // Test the connection to the database, by getting the DatabaseMetaData. Connection conn = openConnection(); try { DatabaseMetaData dbMetaData = conn.getMetaData(); InputStream sqlFile; try { sqlFile = fileSystem.getResource(m_sqlFileName); } catch (Exception e) { getLogger().error(e.getMessage(), e); throw e; } if (getLogger().isDebugEnabled()) { logBuffer = new StringBuffer(256).append("Reading SQL resources from: ").append(m_sqlFileName) .append(", section ").append(this.getClass().getName()).append("."); getLogger().debug(logBuffer.toString()); } SqlResources sqlStatements = new SqlResources(); sqlStatements.init(sqlFile, this.getClass().getName(), conn, m_sqlParameters); // Create the SQL Strings to use for this table. // Fetches all Users from the db. m_getUsersSql = sqlStatements.getSqlString("select", true); // Get a user by lowercase name. (optional) // If not provided, the entire list is iterated to find a user. m_userByNameCaseInsensitiveSql = sqlStatements.getSqlString("selectByLowercaseName"); // Insert, update and delete are not guaranteed to be // case-insensitive // Will always be called with correct case in username.. m_insertUserSql = sqlStatements.getSqlString("insert", true); m_updateUserSql = sqlStatements.getSqlString("update", true); m_deleteUserSql = sqlStatements.getSqlString("delete", true); // Creates a single table with "username" the Primary Key. String createUserTableSql = sqlStatements.getSqlString("createTable", true); // Check if the required table exists. If not, create it. // The table name is defined in the SqlResources. String tableName = sqlStatements.getSqlString("tableName", true); // Need to ask in the case that identifiers are stored, ask the // DatabaseMetaInfo. // NB this should work, but some drivers (eg mm MySQL) // don't return the right details, hence the hackery below. /* * String tableName = m_tableName; if ( * dbMetaData.storesLowerCaseIdentifiers() ) { tableName = * tableName.toLowerCase(Locale.US); } else if ( * dbMetaData.storesUpperCaseIdentifiers() ) { tableName = * tableName.toUpperCase(Locale.US); } */ // Try UPPER, lower, and MixedCase, to see if the table is there. if (!theJDBCUtil.tableExists(dbMetaData, tableName)) { // Users table doesn't exist - create it. PreparedStatement createStatement = null; try { createStatement = conn.prepareStatement(createUserTableSql); createStatement.execute(); } finally { theJDBCUtil.closeJDBCStatement(createStatement); } logBuffer = new StringBuffer(128).append(this.getClass().getName()).append(": Created table \'") .append(tableName).append("\'."); getLogger().info(logBuffer.toString()); } else { if (getLogger().isDebugEnabled()) { getLogger().debug("Using table: " + tableName); } } } finally { theJDBCUtil.closeJDBCConnection(conn); } } /** * <p> * Configures the UserRepository for JDBC access. * </p> * <p> * Requires a configuration element in the .conf.xml file of the form: * </p> * * <pre> * <repository name="so even " * class="org.apache.james.userrepository.JamesUsersJdbcRepository"> * <!-- Name of the datasource to use --> * <data-source>MailDb</data-source> * <!-- File to load the SQL definitions from --> * <sqlFile>dist/conf/sqlResources.xml</sqlFile> * <!-- replacement parameters for the sql file --> * <sqlParameters table="JamesUsers"/> * </repository> * </pre> * * @see org.apache.james.user.lib.AbstractJamesUsersRepository#doConfigure(org.apache.commons.configuration.HierarchicalConfiguration) */ protected void doConfigure(HierarchicalConfiguration configuration) throws ConfigurationException { StringBuffer logBuffer; if (getLogger().isDebugEnabled()) { logBuffer = new StringBuffer(64).append(this.getClass().getName()).append(".configure()"); getLogger().debug(logBuffer.toString()); } // Parse the DestinationURL for the name of the datasource, // the table to use, and the (optional) repository Key. String destUrl = configuration.getString("[@destinationURL]", null); // throw an exception if the attribute is missing if (destUrl == null) throw new ConfigurationException("destinationURL attribute is missing from Configuration"); // normalise the destination, to simplify processing. if (!destUrl.endsWith("/")) { destUrl += "/"; } // Split on "/", starting after "db://" List<String> urlParams = new ArrayList<String>(); int start = 5; int end = destUrl.indexOf('/', start); while (end > -1) { urlParams.add(destUrl.substring(start, end)); start = end + 1; end = destUrl.indexOf('/', start); } // Build SqlParameters and get datasource name from URL parameters m_sqlParameters = new HashMap<String, String>(); switch (urlParams.size()) { case 3: m_sqlParameters.put("key", urlParams.get(2)); case 2: m_sqlParameters.put("table", urlParams.get(1)); case 1: urlParams.get(0); break; default: throw new ConfigurationException("Malformed destinationURL - " + "Must be of the format \"db://<data-source>[/<table>[/<key>]]\"."); } if (getLogger().isDebugEnabled()) { logBuffer = new StringBuffer(128).append("Parsed URL: table = '").append(m_sqlParameters.get("table")) .append("', key = '").append(m_sqlParameters.get("key")).append("'"); getLogger().debug(logBuffer.toString()); } // Get the SQL file location m_sqlFileName = configuration.getString("sqlFile", null); // Get other sql parameters from the configuration object, // if any. Iterator<String> paramIt = configuration.getKeys("sqlParameters"); while (paramIt.hasNext()) { String rawName = paramIt.next(); String paramName = paramIt.next().substring("sqlParameters.[@".length(), rawName.length() - 1); String paramValue = configuration.getString(rawName); m_sqlParameters.put(paramName, paramValue); } } /** * Produces the complete list of User names, with correct case. * * @return a <code>List</code> of <code>String</code>s representing user * names. */ protected List<String> listUserNames() throws UsersRepositoryException { Collection<User> users = getAllUsers(); List<String> userNames = new ArrayList<String>(users.size()); for (User user : users) { userNames.add(user.getUserName()); } users.clear(); return userNames; } /** * Returns a list populated with all of the Users in the repository. * * @return an <code>Iterator</code> of <code>User</code>s. */ protected Iterator<User> listAllUsers() throws UsersRepositoryException { return getAllUsers().iterator(); } /** * Returns a list populated with all of the Users in the repository. * * @return a <code>Collection</code> of <code>JamesUser</code>s. * @throws UsersRepositoryException */ private Collection<User> getAllUsers() throws UsersRepositoryException { List<User> userList = new ArrayList<User>(); // Build the users into // this list. Connection conn = null; PreparedStatement getUsersStatement = null; ResultSet rsUsers = null; try { conn = openConnection(); // Get a ResultSet containing all users. getUsersStatement = conn.prepareStatement(m_getUsersSql); rsUsers = getUsersStatement.executeQuery(); // Loop through and build a User for every row. while (rsUsers.next()) { User user = readUserFromResultSet(rsUsers); userList.add(user); } } catch (SQLException sqlExc) { sqlExc.printStackTrace(); throw new UsersRepositoryException("Error accessing database", sqlExc); } finally { theJDBCUtil.closeJDBCResultSet(rsUsers); theJDBCUtil.closeJDBCStatement(getUsersStatement); theJDBCUtil.closeJDBCConnection(conn); } return userList; } /** * Adds a user to the underlying Repository. The user name must not clash * with an existing user. * * @param user * the user to add * @throws UsersRepositoryException */ protected void doAddUser(User user) throws UsersRepositoryException { Connection conn = null; PreparedStatement addUserStatement = null; // Insert into the database. try { conn = openConnection(); // Get a PreparedStatement for the insert. addUserStatement = conn.prepareStatement(m_insertUserSql); setUserForInsertStatement(user, addUserStatement); addUserStatement.execute(); } catch (SQLException sqlExc) { sqlExc.printStackTrace(); throw new UsersRepositoryException("Error accessing database", sqlExc); } finally { theJDBCUtil.closeJDBCStatement(addUserStatement); theJDBCUtil.closeJDBCConnection(conn); } } /** * Removes a user from the underlying repository. If the user doesn't exist, * returns ok. * * @param user * the user to remove * @throws UsersRepositoryException */ protected void doRemoveUser(User user) throws UsersRepositoryException { String username = user.getUserName(); Connection conn = null; PreparedStatement removeUserStatement = null; // Delete from the database. try { conn = openConnection(); removeUserStatement = conn.prepareStatement(m_deleteUserSql); removeUserStatement.setString(1, username); removeUserStatement.execute(); } catch (SQLException sqlExc) { sqlExc.printStackTrace(); throw new UsersRepositoryException("Error accessing database", sqlExc); } finally { theJDBCUtil.closeJDBCStatement(removeUserStatement); theJDBCUtil.closeJDBCConnection(conn); } } /** * Updates a user record to match the supplied User. * * @param user * the user to update * @throws UsersRepositoryException */ protected void doUpdateUser(User user) throws UsersRepositoryException { Connection conn = null; PreparedStatement updateUserStatement = null; // Update the database. try { conn = openConnection(); updateUserStatement = conn.prepareStatement(m_updateUserSql); setUserForUpdateStatement(user, updateUserStatement); updateUserStatement.execute(); } catch (SQLException sqlExc) { sqlExc.printStackTrace(); throw new UsersRepositoryException("Error accessing database", sqlExc); } finally { theJDBCUtil.closeJDBCStatement(updateUserStatement); theJDBCUtil.closeJDBCConnection(conn); } } /** * Gets a user by name, ignoring case if specified. This implementation gets * the entire set of users, and scrolls through searching for one matching * <code>name</code>. * * @param name * the name of the user being retrieved * @param ignoreCase * whether the name is regarded as case-insensitive * * @return the user being retrieved, null if the user doesn't exist * @throws UsersRepositoryException */ protected User getUserByNameIterating(String name, boolean ignoreCase) throws UsersRepositoryException { // Just iterate through all of the users until we find one matching. Iterator<User> users = listAllUsers(); while (users.hasNext()) { User user = users.next(); String username = user.getUserName(); if ((!ignoreCase && username.equals(name)) || (ignoreCase && username.equalsIgnoreCase(name))) { return user; } } // Not found - return null return null; } /** * Gets a user by name, ignoring case if specified. If the specified SQL * statement has been defined, this method overrides the basic * implementation in AbstractJamesUsersRepository to increase performance. * * @param name * the name of the user being retrieved * @param ignoreCase * whether the name is regarded as case-insensitive * * @return the user being retrieved, null if the user doesn't exist * @throws UsersRepositoryException */ protected User getUserByName(String name, boolean ignoreCase) throws UsersRepositoryException { // See if this statement has been set, if not, use // simple superclass method. if (m_userByNameCaseInsensitiveSql == null) { return getUserByNameIterating(name, ignoreCase); } // Always get the user via case-insensitive SQL, // then check case if necessary. Connection conn = null; PreparedStatement getUsersStatement = null; ResultSet rsUsers = null; try { conn = openConnection(); // Get a ResultSet containing all users. String sql = m_userByNameCaseInsensitiveSql; getUsersStatement = conn.prepareStatement(sql); getUsersStatement.setString(1, name.toLowerCase(Locale.US)); rsUsers = getUsersStatement.executeQuery(); // For case-insensitive matching, the first matching user will be // returned. User user = null; while (rsUsers.next()) { User rowUser = readUserFromResultSet(rsUsers); String actualName = rowUser.getUserName(); // Check case before we assume it's the right one. if (ignoreCase || actualName.equals(name)) { user = rowUser; break; } } return user; } catch (SQLException sqlExc) { sqlExc.printStackTrace(); throw new UsersRepositoryException("Error accessing database", sqlExc); } finally { theJDBCUtil.closeJDBCResultSet(rsUsers); theJDBCUtil.closeJDBCStatement(getUsersStatement); theJDBCUtil.closeJDBCConnection(conn); } } /** * Reads properties for a User from an open ResultSet. Subclass * implementations of this method must have knowledge of the fields * presented by the "select" and "selectByLowercaseName" SQL statements. * These implemenations may generate a subclass-specific User instance. * * @param rsUsers * A ResultSet with a User record in the current row. * @return A User instance * @throws SQLException * if an exception occurs reading from the ResultSet */ protected abstract User readUserFromResultSet(ResultSet rsUsers) throws SQLException; /** * Set parameters of a PreparedStatement object with property values from a * User instance. Implementations of this method have knowledge of the * parameter ordering of the "insert" SQL statement definition. * * @param user * a User instance, which should be an implementation class which * is handled by this Repostory implementation. * @param userInsert * a PreparedStatement initialised with SQL taken from the * "insert" SQL definition. * @throws SQLException * if an exception occurs while setting parameter values. */ protected abstract void setUserForInsertStatement(User user, PreparedStatement userInsert) throws SQLException; /** * Set parameters of a PreparedStatement object with property values from a * User instance. Implementations of this method have knowledge of the * parameter ordering of the "update" SQL statement definition. * * @param user * a User instance, which should be an implementation class which * is handled by this Repostory implementation. * @param userUpdate * a PreparedStatement initialised with SQL taken from the * "update" SQL definition. * @throws SQLException * if an exception occurs while setting parameter values. */ protected abstract void setUserForUpdateStatement(User user, PreparedStatement userUpdate) throws SQLException; /** * Opens a connection, throwing a runtime exception if a SQLException is * encountered in the process. * * @return the new connection * @throws SQLException */ private Connection openConnection() throws SQLException { return m_datasource.getConnection(); } }