JDBCGroupProvider.java 10.7 KB
Newer Older
Matt Tucker's avatar
Matt Tucker committed
1 2 3 4
/**
 * $Revision: $
 * $Date: $
 *
5
 * Copyright (C) 2005-2008 Jive Software. All rights reserved.
Matt Tucker's avatar
Matt Tucker committed
6
 *
7 8 9 10 11 12 13 14 15 16 17
 * Licensed 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.
Matt Tucker's avatar
Matt Tucker committed
18 19
 */

20
package org.jivesoftware.openfire.group;
Matt Tucker's avatar
Matt Tucker committed
21

22 23 24 25 26
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
27 28 29
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
Matt Tucker's avatar
Matt Tucker committed
30

31 32 33 34 35 36 37
import org.jivesoftware.database.DbConnectionManager;
import org.jivesoftware.openfire.XMPPServer;
import org.jivesoftware.util.JiveGlobals;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.xmpp.packet.JID;

Matt Tucker's avatar
Matt Tucker committed
38 39 40
/**
 * The JDBC group provider allows you to use an external database to define the make up of groups.
 * It is best used with the JDBCAuthProvider to provide integration between your external system and
41
 * Openfire.  All data is treated as read-only so any set operations will result in an exception.
Matt Tucker's avatar
Matt Tucker committed
42
 *
43
 * To enable this provider, set the following in the system properties:
Matt Tucker's avatar
Matt Tucker committed
44
 *
45 46 47
 * <ul>
 * <li><tt>provider.group.className = org.jivesoftware.openfire.group.JDBCGroupProvider</tt></li>
 * </ul>
Matt Tucker's avatar
Matt Tucker committed
48 49 50
 *
 * Then you need to set your driver, connection string and SQL statements:
 *
51 52 53 54 55 56 57 58 59 60
 * <ul>
 * <li><tt>jdbcProvider.driver = com.mysql.jdbc.Driver</tt></li>
 * <li><tt>jdbcProvider.connectionString = jdbc:mysql://localhost/dbname?user=username&amp;password=secret</tt></li>
 * <li><tt>jdbcGroupProvider.groupCountSQL = SELECT count(*) FROM myGroups</tt></li>
 * <li><tt>jdbcGroupProvider.allGroupsSQL = SELECT groupName FROM myGroups</tt></li>
 * <li><tt>jdbcGroupProvider.userGroupsSQL = SELECT groupName FORM myGroupUsers WHERE username=?</tt></li>
 * <li><tt>jdbcGroupProvider.descriptionSQL = SELECT groupDescription FROM myGroups WHERE groupName=?</tt></li>
 * <li><tt>jdbcGroupProvider.loadMembersSQL = SELECT username FORM myGroupUsers WHERE groupName=? AND isAdmin='N'</tt></li>
 * <li><tt>jdbcGroupProvider.loadAdminsSQL = SELECT username FORM myGroupUsers WHERE groupName=? AND isAdmin='Y'</tt></li>
 * </ul>
Matt Tucker's avatar
Matt Tucker committed
61
 *
62 63 64 65 66 67 68
 * In order to use the configured JDBC connection provider do not use a JDBC
 * connection string, set the following property
 *
 * <ul>
 * <li><tt>jdbcGroupProvider.useConnectionProvider = true</tt></li>
 * </ul>
 *
Matt Tucker's avatar
Matt Tucker committed
69 70
 * @author David Snopek
 */
71
public class JDBCGroupProvider extends AbstractGroupProvider {
Matt Tucker's avatar
Matt Tucker committed
72

73 74
	private static final Logger Log = LoggerFactory.getLogger(JDBCGroupProvider.class);

75
    private String connectionString;
Matt Tucker's avatar
Matt Tucker committed
76 77 78 79 80 81 82

    private String groupCountSQL;
    private String descriptionSQL;
    private String allGroupsSQL;
    private String userGroupsSQL;
    private String loadMembersSQL;
    private String loadAdminsSQL;
83
    private boolean useConnectionProvider;
Matt Tucker's avatar
Matt Tucker committed
84

85 86
    private XMPPServer server = XMPPServer.getInstance();  

Matt Tucker's avatar
Matt Tucker committed
87 88 89 90
    /**
     * Constructor of the JDBCGroupProvider class.
     */
    public JDBCGroupProvider() {
91 92 93 94 95 96 97 98 99 100
        // Convert XML based provider setup to Database based
        JiveGlobals.migrateProperty("jdbcProvider.driver");
        JiveGlobals.migrateProperty("jdbcProvider.connectionString");
        JiveGlobals.migrateProperty("jdbcGroupProvider.groupCountSQL");
        JiveGlobals.migrateProperty("jdbcGroupProvider.allGroupsSQL");
        JiveGlobals.migrateProperty("jdbcGroupProvider.userGroupsSQL");
        JiveGlobals.migrateProperty("jdbcGroupProvider.descriptionSQL");
        JiveGlobals.migrateProperty("jdbcGroupProvider.loadMembersSQL");
        JiveGlobals.migrateProperty("jdbcGroupProvider.loadAdminsSQL");

101 102 103 104 105 106 107 108 109 110 111 112 113
        useConnectionProvider = JiveGlobals.getBooleanProperty("jdbcGroupProvider.useConnectionProvider");

        if (!useConnectionProvider) {
            // Load the JDBC driver and connection string.
            String jdbcDriver = JiveGlobals.getProperty("jdbcProvider.driver");
            try {
                Class.forName(jdbcDriver).newInstance();
            }
            catch (Exception e) {
                Log.error("Unable to load JDBC driver: " + jdbcDriver, e);
                return;
            }
            connectionString = JiveGlobals.getProperty("jdbcProvider.connectionString");
Matt Tucker's avatar
Matt Tucker committed
114 115
        }

116
        // Load SQL statements
117 118 119 120 121 122
        groupCountSQL = JiveGlobals.getProperty("jdbcGroupProvider.groupCountSQL");
        allGroupsSQL = JiveGlobals.getProperty("jdbcGroupProvider.allGroupsSQL");
        userGroupsSQL = JiveGlobals.getProperty("jdbcGroupProvider.userGroupsSQL");
        descriptionSQL = JiveGlobals.getProperty("jdbcGroupProvider.descriptionSQL");
        loadMembersSQL = JiveGlobals.getProperty("jdbcGroupProvider.loadMembersSQL");
        loadAdminsSQL = JiveGlobals.getProperty("jdbcGroupProvider.loadAdminsSQL");
Matt Tucker's avatar
Matt Tucker committed
123 124
    }

125 126 127 128 129 130
    private Connection getConnection() throws SQLException {
        if (useConnectionProvider)
            return DbConnectionManager.getConnection();
        return DriverManager.getConnection(connectionString);
    }

131
    @Override
Matt Tucker's avatar
Matt Tucker committed
132 133 134
    public Group getGroup(String name) throws GroupNotFoundException {
        String description = null;

135
        Connection con = null;
Matt Tucker's avatar
Matt Tucker committed
136
        PreparedStatement pstmt = null;
137
        ResultSet rs = null;
Matt Tucker's avatar
Matt Tucker committed
138
        try {
139
            con = getConnection();
140
            pstmt = con.prepareStatement(descriptionSQL);
Matt Tucker's avatar
Matt Tucker committed
141
            pstmt.setString(1, name);
142
            rs = pstmt.executeQuery();
Matt Tucker's avatar
Matt Tucker committed
143 144 145 146 147 148 149
            if (!rs.next()) {
                throw new GroupNotFoundException("Group with name "
                        + name + " not found.");
            }
            description = rs.getString(1);
        }
        catch (SQLException e) {
150
            Log.error(e.getMessage(), e);
Matt Tucker's avatar
Matt Tucker committed
151 152
        }
        finally {
153
            DbConnectionManager.closeConnection(rs, pstmt, con);
Matt Tucker's avatar
Matt Tucker committed
154 155 156 157 158 159 160
        }
        Collection<JID> members = getMembers(name, false);
        Collection<JID> administrators = getMembers(name, true);
        return new Group(name, description, members, administrators);
    }

    private Collection<JID> getMembers(String groupName, boolean adminsOnly) {
161
        List<JID> members = new ArrayList<>();
162 163

        Connection con = null;
Matt Tucker's avatar
Matt Tucker committed
164
        PreparedStatement pstmt = null;
165
        ResultSet rs = null;
Matt Tucker's avatar
Matt Tucker committed
166
        try {
167
            con = getConnection();
Matt Tucker's avatar
Matt Tucker committed
168 169 170 171
            if (adminsOnly) {
                if (loadAdminsSQL == null) {
                    return members;
                }
172
                pstmt = con.prepareStatement(loadAdminsSQL);
Matt Tucker's avatar
Matt Tucker committed
173 174
            }
            else {
175
                pstmt = con.prepareStatement(loadMembersSQL);
Matt Tucker's avatar
Matt Tucker committed
176 177 178
            }

            pstmt.setString(1, groupName);
179
            rs = pstmt.executeQuery();
Matt Tucker's avatar
Matt Tucker committed
180 181 182
            while (rs.next()) {
                String user = rs.getString(1);
                if (user != null) {
183 184 185 186 187 188 189
                    JID userJID;
                    if (user.contains("@")) {
                        userJID = new JID(user);
                    }
                    else {
                        userJID = server.createJID(user, null); 
                    }
Matt Tucker's avatar
Matt Tucker committed
190 191 192 193 194
                    members.add(userJID);
                }
            }
        }
        catch (SQLException e) {
195
            Log.error(e.getMessage(), e);
Matt Tucker's avatar
Matt Tucker committed
196 197
        }
        finally {
198
            DbConnectionManager.closeConnection(rs, pstmt, con);
Matt Tucker's avatar
Matt Tucker committed
199 200 201 202
        }
        return members;
    }

203
    @Override
Matt Tucker's avatar
Matt Tucker committed
204 205
    public int getGroupCount() {
        int count = 0;
206
        Connection con = null;
Matt Tucker's avatar
Matt Tucker committed
207
        PreparedStatement pstmt = null;
208
        ResultSet rs = null;
Matt Tucker's avatar
Matt Tucker committed
209
        try {
210
            con = getConnection();
211 212
            pstmt = con.prepareStatement(groupCountSQL);
            rs = pstmt.executeQuery();
Matt Tucker's avatar
Matt Tucker committed
213 214 215 216 217
            if (rs.next()) {
                count = rs.getInt(1);
            }
        }
        catch (SQLException e) {
218
            Log.error(e.getMessage(), e);
Matt Tucker's avatar
Matt Tucker committed
219 220
        }
        finally {
221
            DbConnectionManager.closeConnection(rs, pstmt, con);
Matt Tucker's avatar
Matt Tucker committed
222 223 224 225
        }
        return count;
    }

226
    @Override
227
    public Collection<String> getGroupNames() {
228
        List<String> groupNames = new ArrayList<>();
229
        Connection con = null;
Matt Tucker's avatar
Matt Tucker committed
230
        PreparedStatement pstmt = null;
231
        ResultSet rs = null;
Matt Tucker's avatar
Matt Tucker committed
232
        try {
233
            con = getConnection();
234 235
            pstmt = con.prepareStatement(allGroupsSQL);
            rs = pstmt.executeQuery();
Matt Tucker's avatar
Matt Tucker committed
236 237 238 239 240
            while (rs.next()) {
                groupNames.add(rs.getString(1));
            }
        }
        catch (SQLException e) {
241
            Log.error(e.getMessage(), e);
Matt Tucker's avatar
Matt Tucker committed
242 243
        }
        finally {
244
            DbConnectionManager.closeConnection(rs, pstmt, con);
Matt Tucker's avatar
Matt Tucker committed
245
        }
246
        return groupNames;
Matt Tucker's avatar
Matt Tucker committed
247 248
    }

249
    @Override
250
    public Collection<String> getGroupNames(int start, int num) {
251
        List<String> groupNames = new ArrayList<>();
252
        Connection con = null;
Matt Tucker's avatar
Matt Tucker committed
253
        PreparedStatement pstmt = null;
254
        ResultSet rs = null;
Matt Tucker's avatar
Matt Tucker committed
255
        try {
256
            con = getConnection();
257 258
            pstmt = DbConnectionManager.createScrollablePreparedStatement(con, allGroupsSQL);
            rs = pstmt.executeQuery();
Matt Tucker's avatar
Matt Tucker committed
259 260 261 262 263 264 265 266
            DbConnectionManager.scrollResultSet(rs, start);
            int count = 0;
            while (rs.next() && count < num) {
                groupNames.add(rs.getString(1));
                count++;
            }
        }
        catch (SQLException e) {
267
            Log.error(e.getMessage(), e);
Matt Tucker's avatar
Matt Tucker committed
268 269
        }
        finally {
270
            DbConnectionManager.closeConnection(rs, pstmt, con);
Matt Tucker's avatar
Matt Tucker committed
271
        }
272
        return groupNames;
Matt Tucker's avatar
Matt Tucker committed
273 274
    }

275
    @Override
276
    public Collection<String> getGroupNames(JID user) {
277
        List<String> groupNames = new ArrayList<>();
278
        Connection con = null;
Matt Tucker's avatar
Matt Tucker committed
279
        PreparedStatement pstmt = null;
280
        ResultSet rs = null;
Matt Tucker's avatar
Matt Tucker committed
281
        try {
282
            con = getConnection();
283
            pstmt = con.prepareStatement(userGroupsSQL);
284
            pstmt.setString(1, server.isLocal(user) ? user.getNode() : user.toString());
285
            rs = pstmt.executeQuery();
Matt Tucker's avatar
Matt Tucker committed
286 287 288 289 290
            while (rs.next()) {
                groupNames.add(rs.getString(1));
            }
        }
        catch (SQLException e) {
291
            Log.error(e.getMessage(), e);
Matt Tucker's avatar
Matt Tucker committed
292 293
        }
        finally {
294
            DbConnectionManager.closeConnection(rs, pstmt, con);
Matt Tucker's avatar
Matt Tucker committed
295
        }
296
        return groupNames;
Matt Tucker's avatar
Matt Tucker committed
297
    }
298
}