从ldap xmpp中拿数据插入到数据库中

 

package Data;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.*;
import javax.naming.NamingEnumeration;
import javax.naming.directory.SearchControls;
import javax.naming.directory.SearchResult;
import javax.naming.ldap.InitialLdapContext;
import org.jivesoftware.smack.ConnectionConfiguration;
import org.jivesoftware.smack.Roster;
import org.jivesoftware.smack.RosterEntry;
import org.jivesoftware.smack.XMPPConnection;
import org.jivesoftware.smack.packet.Presence;


/** *//**
 * It is used to connect the jive then
 * get the user list from the jive with the correct
 * status. Then check the name of the
 * online user in jive thru a Ldap in 192.168.1.200.
 * Then input the UserId as roomId, name and
 * the status into a DataBase
 *
 * @author  Alking
 */
public class InputDatas extends javax.swing.JFrame ...{
    // ldap definiens variable
    String ldapUrl,ldapUser,ldapPwd,OU,LdapDC;
    // XMPP definiens variable
    String XMPPUrl,XMPPUser,XMPPPwd;
    // DataBase definiens variable
    String DataBase,DataBaseTable,DataBaseUser,DataBasePwd;
    
    public Exception exception;
    
    
    /** *//** Creates new form InputDatas */
    public InputDatas() ...{
        initComponents();
    }
    
    /** *//**
     * This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    // <editor-fold defaultstate="collapsed" desc=" 生成的代码 ">//GEN-BEGIN:initComponents
    private void initComponents() ...{
        LdapUrlLabel = new javax.swing.JLabel();
        LdapNameLabel = new javax.swing.JLabel();
        LdapPwdLabel = new javax.swing.JLabel();
        OULabel = new javax.swing.JLabel();
        ldapDCLabel = new javax.swing.JLabel();
        XMPPServerUrlLabel = new javax.swing.JLabel();
        XMPPNameLabel = new javax.swing.JLabel();
        XMPPPwdLabel = new javax.swing.JLabel();
        DataBaseLabel = new javax.swing.JLabel();
        DataBaseTableLabel = new javax.swing.JLabel();
        DataBaseNameLabel = new javax.swing.JLabel();
        DataBasePwdLabel = new javax.swing.JLabel();
        LdapUrlTextField = new javax.swing.JTextField();
        LdapNameTextField = new javax.swing.JTextField();
        LdapPwdTextField = new javax.swing.JTextField();
        OUTextField = new javax.swing.JTextField();
        ldapDCTextField = new javax.swing.JTextField();
        XMPPServerUrlTextField = new javax.swing.JTextField();
        XMPPNameTextField = new javax.swing.JTextField();
        XMPPPwdTextField = new javax.swing.JTextField();
        DataBaseTextField = new javax.swing.JTextField();
        DataBaseTableTextField = new javax.swing.JTextField();
        DataBaseNameTextField = new javax.swing.JTextField();
        DataBasePwdTextField = new javax.swing.JTextField();
        InputButton = new javax.swing.JButton();
        ExceptionLabel = new javax.swing.JLabel();

        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);
        LdapUrlLabel.setText("LdapUrl:");

        LdapNameLabel.setText("LdapName:");

        LdapPwdLabel.setText("LdapPwd:");

        OULabel.setText("OU:");

        ldapDCLabel.setText("ldapDC:");

        XMPPServerUrlLabel.setText("XMPPServerUrl:");

        XMPPNameLabel.setText("XMPPName:");

        XMPPPwdLabel.setText("XMPPPwd:");

        DataBaseLabel.setText("DataBase:");

        DataBaseTableLabel.setText("DataBaseTable:");

        DataBaseNameLabel.setText("DataBaseName:");

        DataBasePwdLabel.setText("DataBasePwd:");

        LdapUrlTextField.setText("192.168.1.200");

        LdapNameTextField.setText("gz");

        LdapPwdTextField.setText("123456");

        OUTextField.setText("SpotLight");

        ldapDCTextField.setText("DC=awm64,DC=com");

        XMPPServerUrlTextField.setText("192.168.1.200");

        XMPPNameTextField.setText("spotlight");

        XMPPPwdTextField.setText("123456");

        DataBaseTextField.setText("//localhost:1433;DatabaseName=alking");

        DataBaseTableTextField.setText("jive_status");

        DataBaseNameTextField.setText("sa");

        InputButton.setText("Input");
        InputButton.addMouseListener(new java.awt.event.MouseAdapter() ...{
            public void mouseClicked(java.awt.event.MouseEvent evt) ...{
                InputButtonMouseClicked(evt);
            }
        });

        ExceptionLabel.setAutoscrolls(true);

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addGap(54, 54, 54)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                    .addComponent(OULabel)
                    .addComponent(LdapPwdLabel)
                    .addComponent(LdapNameLabel)
                    .addComponent(LdapUrlLabel)
                    .addComponent(XMPPPwdLabel)
                    .addComponent(ldapDCLabel)
                    .addComponent(XMPPServerUrlLabel)
                    .addComponent(XMPPNameLabel)
                    .addComponent(DataBasePwdLabel)
                    .addComponent(DataBaseNameLabel)
                    .addComponent(DataBaseTableLabel)
                    .addComponent(DataBaseLabel))
                .addGap(48, 48, 48)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(DataBaseTextField)
                    .addComponent(LdapUrlTextField, javax.swing.GroupLayout.DEFAULT_SIZE, 196, Short.MAX_VALUE)
                    .addComponent(LdapNameTextField, javax.swing.GroupLayout.DEFAULT_SIZE, 196, Short.MAX_VALUE)
                    .addComponent(LdapPwdTextField, javax.swing.GroupLayout.DEFAULT_SIZE, 196, Short.MAX_VALUE)
                    .addComponent(OUTextField, javax.swing.GroupLayout.DEFAULT_SIZE, 196, Short.MAX_VALUE)
                    .addComponent(ldapDCTextField, javax.swing.GroupLayout.DEFAULT_SIZE, 196, Short.MAX_VALUE)
                    .addComponent(XMPPServerUrlTextField, javax.swing.GroupLayout.DEFAULT_SIZE, 196, Short.MAX_VALUE)
                    .addComponent(XMPPNameTextField, javax.swing.GroupLayout.DEFAULT_SIZE, 196, Short.MAX_VALUE)
                    .addComponent(XMPPPwdTextField, javax.swing.GroupLayout.DEFAULT_SIZE, 196, Short.MAX_VALUE)
                    .addComponent(DataBaseTableTextField, javax.swing.GroupLayout.DEFAULT_SIZE, 196, Short.MAX_VALUE)
                    .addComponent(DataBaseNameTextField, javax.swing.GroupLayout.DEFAULT_SIZE, 196, Short.MAX_VALUE)
                    .addComponent(DataBasePwdTextField, javax.swing.GroupLayout.DEFAULT_SIZE, 196, Short.MAX_VALUE))
                .addGap(49, 49, 49))
            .addGroup(layout.createSequentialGroup()
                .addGap(153, 153, 153)
                .addComponent(InputButton, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                .addGap(212, 212, 212))
            .addGroup(layout.createSequentialGroup()
                .addGap(18, 18, 18)
                .addComponent(ExceptionLabel, javax.swing.GroupLayout.PREFERRED_SIZE, 386, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addContainerGap(20, Short.MAX_VALUE))
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(LdapUrlLabel)
                    .addComponent(LdapUrlTextField, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(LdapNameLabel)
                    .addComponent(LdapNameTextField, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(LdapPwdLabel)
                    .addComponent(LdapPwdTextField, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(OULabel)
                    .addComponent(OUTextField, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                    .addComponent(ldapDCLabel)
                    .addComponent(ldapDCTextField, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(XMPPServerUrlTextField, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(XMPPServerUrlLabel))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(XMPPNameTextField, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(XMPPNameLabel))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(XMPPPwdTextField, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(DataBaseTextField, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(DataBaseLabel))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(DataBaseTableTextField, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(DataBaseTableLabel))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(DataBaseNameTextField, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(DataBaseNameLabel)))
                    .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                        .addComponent(XMPPPwdLabel)
                        .addGap(80, 80, 80)))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(DataBasePwdTextField, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(DataBasePwdLabel))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(InputButton)
                .addGap(20, 20, 20)
                .addComponent(ExceptionLabel, javax.swing.GroupLayout.PREFERRED_SIZE, 27, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
        );
        pack();
    }// </editor-fold>//GEN-END:initComponents
    
    private void InputButtonMouseClicked(java.awt.event.MouseEvent evt) ...{//GEN-FIRST:event_InputButtonMouseClicked
        int i=0;
        ldapUrl = LdapUrlTextField.getText();
        ldapUser = LdapNameTextField.getText();
        ldapPwd = LdapPwdTextField.getText();
        OU = OUTextField.getText();
        LdapDC = ldapDCTextField.getText();
        
        XMPPUrl = XMPPServerUrlTextField.getText();
        XMPPUser = XMPPNameTextField.getText();
        XMPPPwd = XMPPPwdTextField.getText();
        
        DataBase=DataBaseTextField.getText();
        DataBaseTable = DataBaseTableTextField.getText();
        DataBaseUser = DataBaseNameTextField.getText();
        DataBasePwd = DataBasePwdTextField.getText();
        
        InitialLdapContext ctx = null;
        Hashtable hashtable = null;
        // Set up default values for LDAP info
        String url = "ldap://"+ldapUrl+":389";
        String username =  "cn="+ldapUser+",cn=Users,"+LdapDC;
        String base = "OU="+OU+","+LdapDC;
        try...{
            hashtable = new Hashtable();
            hashtable.put("java.naming.ldap.version", "3");
            hashtable.put("java.naming.factory.initial","com.sun.jndi.ldap.LdapCtxFactory");
            hashtable.put("java.naming.security.authentication", "Simple");
            hashtable.put("java.naming.referral", "follow");
            hashtable.put("java.naming.provider.url", url);
            hashtable.put("java.naming.security.principal", username);
            hashtable.put("java.naming.security.credentials", ldapPwd);
            // Make LDAP connection
            ctx = new InitialLdapContext(hashtable, null);
            System.out.println(" Ldap Connection established");
            // Set up Search Controls
            SearchControls sc = new SearchControls();
            sc.setSearchScope(SearchControls.SUBTREE_SCOPE);

            ConnectionConfiguration  config=new ConnectionConfiguration(XMPPUrl,5222);
            XMPPConnection con=new XMPPConnection(config);
            con.connect();
            con.login(XMPPUser,XMPPPwd);
            
            Roster roster = con.getRoster();
            Collection<RosterEntry> entries = roster.getEntries();
            
            String sql,sqlurl;
            Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
            //TODO change my datebase
            sqlurl="jdbc:microsoft:sqlserver:"+DataBase;
            Connection conn=DriverManager.getConnection(sqlurl,DataBaseUser,DataBasePwd);
            for (RosterEntry entry : entries) ...{
                i++;
                sql="insert into "+DataBaseTable+" values(?,?,?)";
                PreparedStatement pstmt = conn.prepareStatement(sql);
                String user=entry.getUser().substring(0,entry.getUser().indexOf("@"));
                //test 1160 complete
//                String user="1160";
                pstmt.setString(1,user);
                Presence presence=roster.getPresence(entry.getUser());                
                NamingEnumeration results = ctx.search(base,"sAMAccountName="+user, sc);
                if (results.hasMore() == false)...{
                    pstmt.setInt(2,0);
                    pstmt.setString(3," ");
                    System.out.println("sAMAccountName="+user+" user not exist");
                    
                }else if (results == null)...{
                    pstmt.setInt(2,0);
                    pstmt.setString(3," ");
                    System.out.println("results == null");
                }else...{
                    SearchResult sr = (SearchResult)results.next();
                    javax.naming.directory.Attributes attrs = sr.getAttributes();
                    if(presence.isAvailable()==true)...{
//                        if(name.toString().equals("displayName: "+user)){
                        pstmt.setInt(2,1);
                        pstmt.setString(3,attrs.get("displayname").get(0).toString());
                        System.out.println("displayname="+attrs.get("displayname").get(0).toString());
                        
//                        }
                    }else ...{
                        pstmt.setInt(2,0);
                        pstmt.setString(3," ");
                        System.out.println("sAMAccountName="+user+"name.toString()==null");
                    }
                }
                pstmt.executeUpdate();
                ExceptionLabel.setText("success. Total "+i+" note!");
                ExceptionLabel.getText();
            }
            con.disconnect();
            ctx.close();
        }catch (Exception e)...{
            System.out.println("Exception:"+e);
            exception=e;
            if(exception== null)...{
                ExceptionLabel.setText("");
            }else...{
                ExceptionLabel.setText(exception.toString());
                ExceptionLabel.getText();
            }
        }
    }//GEN-LAST:event_InputButtonMouseClicked
    
    /** *//**
     * Main method is the main method of the entrance,
     * is running on the main method of finding running,
     * running after a java UI
     * @param args the command line arguments
     */
    public static void main(String args[]) ...{
        java.awt.EventQueue.invokeLater(new Runnable() ...{
            public void run() ...{
                new InputDatas().setVisible(true);
            }
        });
    }