使用Oracle中的用户表作为Openfire用户系统（注意事项记录）

1.参考：http://zoeyyoung.github.io/openfire-xmpp-database.html
登陆openfire，添加修改配置属性，页面如下：

或者进入到配置好的数据库中OFPROPERTY表来修改对应的关键字

修改的关键字如下：

UPDATE `openfire`.`ofProperty` SET propValue='org.jivesoftware.openfire.auth.JDBCAuthProvider' WHERE name='provider.auth.className';
UPDATE `openfire`.`ofProperty` SET propValue='org.jivesoftware.openfire.group.JDBCGroupProvider' WHERE name='provider.group.className';
UPDATE `openfire`.`ofProperty` SET propValue='org.jivesoftware.openfire.user.JDBCUserProvider' WHERE name='provider.user.className';

INSERT INTO `openfire`.`ofProperty` VALUES
 ('jdbcProvider.driver', 'com.mysql.jdbc.Driver'),
 ('jdbcProvider.connectionString', 'jdbc:mysql://localhost:3306/office?user=root&password=123456'),
 ('admin.authorizedJIDs', 'admin@localhost,admin@192.168.0.46'),
 ('jdbcAuthProvider.passwordSQL', 'SELECT pwd FROM users WHERE uid=?'),
 ('jdbcAuthProvider.passwordType', 'plain'),
 ('jdbcUserProvider.allUsersSQL', 'SELECT uid FROM users'),
 ('jdbcUserProvider.loadUserSQL', 'SELECT name,email FROM users WHERE uid=?'),
 ('jdbcUserProvider.userCountSQL', 'SELECT COUNT(*) FROM users'),
 ('jdbcUserProvider.searchSQL', 'SELECT uid FROM users WHERE'),
 ('jdbcUserProvider.usernameField', 'uid'),
 ('jdbcUserProvider.nameField', 'name'),
 ('jdbcUserProvider.emailField', 'email');
 ('jdbcGroupProvider.allGroupsSQL', 'SELECT groupName FROM groups'),
 ('jdbcGroupProvider.descriptionSQL', 'SELECT description FROM groups WHERE groupName=?'),
 ('jdbcGroupProvider.groupCountSQL', 'SELECT COUNT(*) FROM groups'),
 ('jdbcGroupProvider.loadAdminsSQL', 'SELECT uid FROM groupusers WHERE groupName=? AND admin=1'),
 ('jdbcGroupProvider.loadMembersSQL', 'SELECT uid FROM groupusers WHERE groupName=? AND admin=0'),
 ('jdbcGroupProvider.userGroupsSQL', 'SELECT groupName from groupusers WHERE uid=?');

注意三个地方
1. 将
provider.auth.className
provider.user.className
分别设置为DefalutAuthProvider/DefalutUserProvider时就是系统默认的设置
（用来更换是否自定义属性）

如果你的用户没有分组的话，不用更新
provider.group.className
以及添加
jdbcGroupProvider.allGroupsSQL
jdbcGroupProvider.descriptionSQL
jdbcGroupProvider.groupCountSQL
jdbcGroupProvider.groupCountSQL
jdbcGroupProvider.loadAdminsSQL
jdbcGroupProvider.loadMembersSQL
jdbcGroupProvider.userGroupsSQL
关键字

2.网上的例子大多数是是连接MYSQL的，这样照搬到Orcale上面是不成功的，主要在需要修改
“jdbcProvider.driver” 设置为
“oracle.jdbc.driver.OracleDriver”

‘jdbcProvider.connectionString’设置为
jdbc:oracle:thin:[username]/[password]@ [openfire-domain]

例如：jdbc:oracle:thin:dlsys/dlsys@192.168.101.29:1521:test

3.如果第三方密码md5加密，也就是’
jdbcAuthProvider.passwordType’设置为 ‘md5’时
对于加密的写入数据库的密码必须为小写，

比如：
明文：admin
MD5加密：202CB962AC59075B964B07152D234B70 无法登陆，校验无法通过
MD5加密：202cb962ac59075b964b07152d234b70 登陆成功