openfire + sqlserver ，以及现有系统的账户集成

我要说的重点就是

1、安装完成之后，openfire的控制台不需要进行任何配置，下面所有的配置可以通过数据库脚本直接搞定。

2、那个conf文件夹里面，也不需要进行任何修改！

总的来讲，如果你要集成现有账户：

只需要修改ofProperty这个表就行了，配置方面不用作其他修改！！！

ONLY DATABASE!!!

ONLY DATABASE!!!

ONLY DATABASE!!!    重要的事情说3遍！！

然后，我的操作视频在：http://v.youku.com/v_show/id_XMTg1NDgzNzc3Mg==.html

话不多说，直接拿源代码去吧。记得修改你自己的。

-- ///////////////////////////先停止OpenFire服务器   /////////////////////////////////////////////BEGIN TRANSACTIONUSE [JetOpenFire_D]GO--  首先修改三个属性(openfire提供了支持JDBC相关的Provider, 可以从其它的数据源获取用户/组数据)：UPDATE ofProperty SET propValue='org.jivesoftware.openfire.auth.JDBCAuthProvider' WHERE name='provider.auth.className';UPDATE ofProperty SET propValue='org.jivesoftware.openfire.group.JDBCGroupProvider' WHERE name='provider.group.className';UPDATE ofProperty SET propValue='org.jivesoftware.openfire.user.JDBCUserProvider' WHERE name='provider.user.className';--接着配置数据源（添加系统属性, 这里使用的是sql server数据库）：INSERT INTO ofProperty VALUES ('jdbcProvider.driver', 'net.sourceforge.jtds.jdbc.Driver')DELETE ofProperty WHERE name='jdbcProvider.connectionString'INSERT INTO ofProperty VALUES ('jdbcProvider.connectionString', 'jdbc:jtds:sqlserver://192.168.1.27:1433/JetCommon_D;user=sa;password=jetmaster;instance=sql2008')-- //////////////////////////// --=== 三个Auth Provider ===----/////////////////////////////////先删除ofProperty的相关条目////////////////////////////DELETE ofProperty WHERE name ='jdbcAuthProvider.passwordSQL'DELETE ofProperty WHERE name ='jdbcAuthProvider.passwordType'DELETE ofProperty WHERE name ='admin.authorizedJIDs'--JDBCUserProvider DELETE ofProperty WHERE name ='jdbcUserProvider.allUsersSQL'DELETE ofProperty WHERE name ='jdbcUserProvider.loadUserSQL'DELETE ofProperty WHERE name ='jdbcUserProvider.userCountSQL'DELETE ofProperty WHERE name ='jdbcUserProvider.searchSQL'DELETE ofProperty WHERE name ='jdbcUserProvider.usernameField'DELETE ofProperty WHERE name ='jdbcUserProvider.nameField'DELETE ofProperty WHERE name ='jdbcUserProvider.emailField'--JDBCGroupProviderDELETE ofProperty WHERE name ='jdbcGroupProvider.allGroupsSQL'DELETE ofProperty WHERE name ='jdbcGroupProvider.descriptionSQL'DELETE ofProperty WHERE name ='jdbcGroupProvider.groupCountSQL' DELETE ofProperty WHERE name ='jdbcGroupProvider.loadAdminsSQL'DELETE ofProperty WHERE name ='jdbcGroupProvider.loadMembersSQL'DELETE ofProperty WHERE name ='jdbcGroupProvider.userGroupsSQL'--/////////////////////////////////再插入更改的新条目////////////////////////////--JDBCAuthProvider INSERT INTO ofProperty VALUES ('jdbcAuthProvider.passwordSQL', 'SELECT Password FROM UserAccount WHERE ObjectName=?')INSERT INTO ofProperty VALUES ('jdbcAuthProvider.passwordType', 'plain')--JDBC User Provider INSERT INTO ofProperty VALUES ('jdbcUserProvider.allUsersSQL', 'SELECT ObjectName FROM UserAccount')INSERT INTO ofProperty VALUES ('jdbcUserProvider.loadUserSQL', 'SELECT ObjectName,ObjectName FROM UserAccount WHERE ObjectName=?')INSERT INTO ofProperty VALUES ('jdbcUserProvider.userCountSQL', 'SELECT COUNT(*) FROM UserAccount')INSERT INTO ofProperty VALUES ('jdbcUserProvider.searchSQL', 'SELECT ObjectName FROM UserAccount WHERE')INSERT INTO ofProperty VALUES ('jdbcUserProvider.usernameField', 'ObjectName')INSERT INTO ofProperty VALUES ('jdbcUserProvider.nameField', 'ObjectName')INSERT INTO ofProperty VALUES ('jdbcUserProvider.emailField', 'ObjectName')--JDBC Group ProviderINSERT INTO ofProperty VALUES ('jdbcGroupProvider.allGroupsSQL', 'SELECT GroupName FROM EmpGroups')INSERT INTO ofProperty VALUES ('jdbcGroupProvider.descriptionSQL', 'SELECT [description] FROM EmpGroups WHERE GroupName=?')INSERT INTO ofProperty VALUES ('jdbcGroupProvider.groupCountSQL', 'SELECT COUNT(*) FROM EmpGroups') INSERT INTO ofProperty VALUES ('jdbcGroupProvider.loadAdminsSQL', 'SELECT ObjectName FROM GroupUsers WHERE GroupName=? AND admin=1')INSERT INTO ofProperty VALUES ('jdbcGroupProvider.loadMembersSQL', 'SELECT ObjectName FROM GroupUsers WHERE GroupName=? AND admin=0')INSERT INTO ofProperty VALUES ('jdbcGroupProvider.userGroupsSQL', 'SELECT GroupName from GroupUsers WHERE ObjectName=?') -- 插入控制台管理员账户-- -- 这里要注意，是 【服务器名称】，是在管理界面看到的那个服务器名称-- 不是admin@127.0.0.1，不是admin@localhost，不是admin@local-- 应该就是你先安装的时候填写的服务器名称，具体可以在服务器设置里面看到。INSERT INTO ofProperty VALUES ('admin.authorizedJIDs', 'admin@192.168.1.27')--//////////////////////////////////////对现有系统的表进行修改//////////////////////////////////////////////USE [JetCommon_D]GO--增加一个EmpGroups表.表示人员组CREATE TABLE EmpGroups (GroupName NVARCHAR(50) PRIMARY KEY NOT NULL,[description] NVARCHAR(255),);--再增加一个GroupUsers表，把管理员添加进来.CREATE TABLE GroupUsers (GroupName NVARCHAR(50) NOT NULL,[ObjectName] NVARCHAR(100) NOT NULL,[admin] int);-- 插入一个管理员用户，其实在系统初始化的时候，这个admin管理员就可能已经增加进去了.-- 系统里面应该有一个默认部门(就是系统的根部门，ID=1)----    这一步你要做，我注释掉是因为我现有数据库里面已经有了这个admin的账户了。---- INSERT INTO [JetCommon_D].[dbo].[Employee]--            ([DeptId]--            ,[EmployeeNO]--            ,[ObjectName]--            ,[StatusId]--            ,[PostId]--            ,[PostRank]--            ,[Sex]--            ,[JoinTime]--            ,[EmployeeType]--            ,[ManageEmployeeGroupId]--            ,[IsSync]--            ,[SortIndex])--      VALUES--            (1--            ,'admin'--            ,'系统管理员'--            ,1--            ,0--            ,0--            ,1--            ,'2016-08-31'--            ,1--            ,0--            ,0--            ,0)-- GO-- INSERT INTO [JetCommon_D].[dbo].[UserAccount]--            ([EmployeeId]--            ,[ObjectName]--            ,[Password]--            ,[LastLogin]--            ,[LastChangePassword])--      VALUES--            ((SELECT Id FROM [JetCommon_D].[dbo].Employee WHERE EmployeeNO='admin')--            ,'admin'--            ,'888888'--            ,GETDATE()--            ,GETDATE()--            )-- GO--插入一个管理组并将admin加入到管理组INSERT INTO EmpGroups VALUES ('admin','administrators')INSERT INTO GroupUsers VALUES ('admin','admin',1);COMMIT TRANSACTION--////////////////////////////////////////自动OpenFire服务器/////////////////////////////////////////