10 Replies Latest reply: Feb 21, 2012 3:12 AM by gimbal2 RSS

    Best way to retrieve mssql data without affecting the app in java swing

    917733
      Hi guys im developing a chat program using java swing and mssql 2005 for which im getting data from more than 5 tables..

      to get that done i used Timer in java so that it checks for new message every 2 second.. But the problem is it affects the performance and smoothness of my app..

      So i tried using Thread which enhanced some performance but not upto the clients expectation..

      is there any other way to do this so that checking for new messages and user availability will be done on one part and app runs smoothly on other part like splitting the process into two to ensure performance...?
        • 1. Re: Best way to retrieve mssql data without affecting the app in java swing
          gimbal2
          Yep.

          a) make sure the network speed is good enough
          b) make sure your database is properly optimized (indexes, etc. etc.)
          c) make sure your SQL queries are optimized according to the specific performance specifications of the DBMS
          d) use connection pooling (for example, Apache commons dbcp)


          Opening connections is expensive, connection pools manage a limited set of connections for you which remain open so you don't have that expense.
          • 2. Re: Best way to retrieve mssql data without affecting the app in java swing
            917733
            Thanks for the reply gimbal2. Ok im not much expert in mssql also... so im posting the code used for connecting database... is am i right in connection pool..? plz suggest me...
            I use the function getMSSQLData() in all my class to fetch data from server.
            /*
                 Functons used for Connecting MSSQL Database (ConnectODBC.java)
            */
            
            import java.sql.*;
            import java.util.HashMap;
            import java.util.Map;
            import java.util.Vector;
            import java.util.logging.Level;
            import java.util.logging.Logger;
            
            public class ConnectODBC {
                ResultSet rs;
                PreparedStatement stat;
                Connection con = null;
                
                public String dbLocation = "xxx.xxx.xxx.xxx";
                public int dbPort = 1433;
                public String dbName = "xxxxxxxx";
                public String dbUser = "xxxx";
                public String dbPass = "***********";
                
                public ConnectODBC(){
                    //System.out.println("ConnectODBC initialized . . .");
                }
                
                private Vector connectDB() throws SQLException{
                    String conString;
                    Vector conOutput = new Vector();
                    try {
                        if(con == null){
                            System.out.println("connecting to database");
                            // Connect with a url string and properties
                            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
                            
                            // MS-SQL 2005
                            conString   =   "jdbc:sqlserver://" + dbLocation + ":" + dbPort + ";" +
                                            "database=" + dbName + ";" +
                                            "user=" + dbUser + ";" +
                                            "password=" + dbPass ;
                            //System.out.println(conString);
                            con = DriverManager.getConnection(conString);
                            conOutput.addElement("success");
                            conOutput.addElement("success");
                            if(con!=null) System.out.println("connectDB: Connection Successfull...");
                        }
                        else{
                            conOutput.addElement("success");
                            conOutput.addElement("success");
                        }
                    } catch (Exception e) {
                        System.out.println("connectDB: " + e.getMessage());
                        String errMsg = e.getMessage();
                        if(errMsg.equals("Cannot open database \""+dbName+"\" requested by the login. The login failed.")){
                            conOutput.addElement("err3");
                            conOutput.addElement("Failed to Load Database . . .");
                        }
                        else if(errMsg.equals("Login failed for user '"+dbUser+"'.")){
                            conOutput.addElement("err2");
                            conOutput.addElement("Invalid Database User . . .");
                        }
                        else{
                            conOutput.addElement("err1");
                            conOutput.addElement("Failed to Connect Database . . .");
                        }
                        /*else if(errMsg.equals("The TCP/IP connection to the host "+dbLocation+", port "+dbPort+" has failed. Error: \"connect timed out. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.\".")){
                            conOutput.addElement("err1");
                            conOutput.addElement("Failed to Connect Database . . .");
                        }*/
                        con =   null;
                    }
                    return conOutput;
                }
                
                public Vector getMSSQLData() {
                    Vector dbData = new Vector();
                    dbData.addElement("errinv");
                    System.out.println("No Query Found . . .");
                    return dbData;
                }
                
                public Vector getMSSQLData(String SQL){
                    int dbr = 0;
                    Vector dbData = new Vector();
                    Vector dbCols = new Vector();
                    Vector dbRows = new Vector();
                    if(SQL != null && !SQL.equals("")){
                        try {
                            // Connect Database
                            Vector dbCon = connectDB();
                            
                            // Add Output Data
                            dbData.addElement(dbCon.get(0));
                            dbData.addElement(dbCon.get(1));
                            dbData.addElement(SQL);
            
                            // Check Connection Status
                            if(dbCon.get(0).equals("success")){
                                // Execute Query
                                stat = con.prepareStatement(SQL);
                                rs = stat.executeQuery();
            
                                // Fetch Columns
                                ResultSetMetaData rsMetaData = rs.getMetaData();
                                int numberOfColumns = rsMetaData.getColumnCount();
                                for (int i = 1; i <= numberOfColumns; i++){
                                  dbCols.addElement(rsMetaData.getColumnName(i));
                                }
                                
                                // Add Output Data
                                dbData.addElement(dbCols.size());
                                dbData.addElement(dbCols);
            
                                // Fetch All Rows
                                while(rs.next()){
                                    Vector dbRow = new Vector();
                                    for (int i = 1; i <= dbCols.size(); i++){
                                        dbRow.addElement(rs.getString(i));
                                    }
                                    dbRows.addElement(dbRow);
                                    dbr++;
                                }
                                
                                // Add Output Data
                                dbData.addElement(dbr);
                                dbData.addElement(dbRows);
            
                                // Close ResultSet
                                rs.close();
                                //con.close();
                            }
                        } catch (Exception e) {
                            // Get Error Message
                            if(e.getMessage().equals("The statement did not return a result set.")){
                                dbData.addElement("success");
                                dbData.addElement("success");
                                dbData.addElement(SQL);
                                dbData.addElement(1);
                            }
                            else{
                                System.out.println("getMSSQLData: " + e.getMessage());
                                dbData.addElement("err4");
                                dbData.addElement(e.getMessage());
                            }
                        }
                    }
                    return dbData;
                }
                
                public String getItemData(Vector sqlData, String sqlField){
                    String outputTxt = null;
                    int sqlRow = 0;
                    
                    // Check if the given data is a valid result from database
                    if(sqlData.size() == 7 && !sqlData.get(5).equals(0)){
                        // Check if field is mentioned
                        if(sqlField != null && !sqlField.equals("")){
                            // If mentioned check if it is available
                            int fieldPos = -1;
                            if(!sqlData.get(3).equals(0)){
                                Vector fieldObj = (Vector) sqlData.get(4);
                                for(int fo=0; fo<Integer.parseInt(sqlData.get(3).toString()); fo++){
                                    if(fieldObj.get(fo).equals(sqlField)){
                                        fieldPos = fo;
                                        break;
                                    }
                                }
                            }
                            
                            // If row found get its data
                            if(fieldPos > -1 && sqlRow < Integer.parseInt(sqlData.get(5).toString())){
                                Vector dataObj = (Vector) sqlData.get(6);
                                Vector dataRowObj = (Vector) dataObj.get(sqlRow);
                                outputTxt = (String) dataRowObj.get(fieldPos);
                            }
                        }
                    }
                    
                    return outputTxt;
                }
                
                public String getItemData(Vector sqlData, String sqlField, int sqlRow){
                    String outputTxt = null;
                    sqlRow = (sqlRow >= 0)?sqlRow:0;
                    
                    // Check if the given data is a valid result from database
                    if(sqlData.size() == 7 && !sqlData.get(5).equals(0)){
                        // Check if field is mentioned
                        if(sqlField != null && !sqlField.equals("")){
                            // If mentioned check if it is available
                            int fieldPos = -1;
                            if(!sqlData.get(3).equals(0)){
                                Vector fieldObj = (Vector) sqlData.get(4);
                                for(int fo=0; fo<Integer.parseInt(sqlData.get(3).toString()); fo++){
                                    if(fieldObj.get(fo).equals(sqlField)){
                                        fieldPos = fo;
                                        break;
                                    }
                                }
                            }
                            
                            // If row found get its data
                            if(fieldPos > -1 && sqlRow < Integer.parseInt(sqlData.get(5).toString())){
                                Vector dataObj = (Vector) sqlData.get(6);
                                Vector dataRowObj = (Vector) dataObj.get(sqlRow);
                                outputTxt = (String) dataRowObj.get(fieldPos);
                            }
                        }
                    }
                    
                    return outputTxt;
                }
                
                public Vector doMSSQLQuery() {
                    Vector dbData = new Vector();
                    dbData.addElement("errinv");
                    System.out.println("No Query Found . . .");
                    return dbData;
                }
                
                public Vector doMSSQLQuery(String SQL){
                    int dbr = 0;
                    Vector dbData = new Vector();
                    Vector dbCols = new Vector();
                    Vector dbRows = new Vector();
                    
                    if(SQL != null && !SQL.equals("")){
                        try {
                            // Connect Database
                            Vector dbCon = connectDB();
                            
                            // Check Connection Status
                            if(dbCon.get(0).equals("success")){
                                // Execute Query
                                stat = con.prepareStatement(SQL);
                                rs = stat.executeQuery();
            
                                // Add Output Data
                                dbData.addElement(dbCon.get(0));
                                dbData.addElement(dbCon.get(1));
                                dbData.addElement(SQL);
            
                                // Fetch All Rows
                                while(rs.next()){
                                    dbr++;
                                }
                                
                                // Add Output Data
                                dbData.addElement(dbr);
            
                                // Close ResultSet
                                rs.close();
                                //con.close();
                            }
                        } catch (Exception e) {
                            // Get Error Message
                            if(e.getMessage().equals("The statement did not return a result set.")){
                                dbData.addElement("success");
                                dbData.addElement("success");
                                dbData.addElement(SQL);
                                dbData.addElement(1);
                            }
                            else{
                                System.out.println("doMSSQLData: " + e.getMessage());
                                dbData.addElement("err4");
                                dbData.addElement(e.getMessage());
                            }
                        }
                    }
                    return dbData;
                }
                
                public Vector getAffectedRows(){
                    System.out.println("getAffectedRows: Invalid Data . . .");
                    return new Vector();
                }
                public Vector getAffectedRows(Vector dbRows, Vector dbData){
                    Vector affRow = new Vector();
                    if(!dbRows.isEmpty() && !dbData.isEmpty()){
                        int affRowCount = dbRows.size();
                        int affDataCount = dbData.size();
                        
                        for(int dc=0; dc<affDataCount; dc++){
                            Map dbArr = new HashMap();
                            for(int rc=0; rc<affRowCount; rc++){
                                dbArr.put(
                                    dbRows.get(rc).toString(),
                                    ((Vector) dbData.get(dc)).get(rc).toString()
                                );
                            }
                            affRow.add(dbArr);
                        }
                    }
                    return affRow;
                }
                
                public static void main(String[] args) {
                    ConnectODBC odbc = new ConnectODBC();
                    try {
                        odbc.connectDB();
                        /*Vector output = odbc.doMySQLQuery("select * from table");
                        System.out.println(output);
                        output = odbc.doMySQLQuery("select * from table");
                        //output = odbc.getMySQLData("select * from table");
                        System.out.println(output);*/
                    } catch (SQLException ex) {
                        Logger.getLogger(ConnectODBC.class.getName()).log(Level.SEVERE, null, ex);
                    }
                }
            }
            • 3. Re: Best way to retrieve mssql data without affecting the app in java swing
              DarrylBurke
              If you haven't already done so, go through the tutorial on Concurrency in Swing

              db
              • 4. Re: Best way to retrieve mssql data without affecting the app in java swing
                917733
                Well thankyou but plz ensure me that these functions are enough for concurrency or i need to work on code...?

                Thank you...
                • 5. Re: Best way to retrieve mssql data without affecting the app in java swing
                  gimbal2
                  SymDesigner wrote:
                  so im posting the code used for connecting database... is am i right in connection pool..?
                  No, you are just creating a connection manually. But you use only one and you keep it open during the entire program I see, so you don't have to worry about it in this case.

                  PS: be sure to add a method where the connection is closed again, which is called when the program ends. Get in the habit of cleaning up resources.
                  • 6. Re: Best way to retrieve mssql data without affecting the app in java swing
                    917733
                    Thank you all for the valuables answers.. the real problem is not with the thread or swingworker.. but the database connectivity... so i switched to socket programming and now everything works very speed and im happy with that...

                    now im facing a tiny issue (i believe) in socket programming... when sending other language text apart from english i get strange text.. after long research in google i made some correction to getBytes() to getBytes("UTF-8") and tried to send some arabic text..

                    while connecting socket locally it works fine and i receive the arabic text but when testing from online i get the same strange text...

                    here is the text i tried...

                    "مرحبا" (this is the arab text of "hello") which displayed to me as "مرØبا"

                    How do i resolve this issue...?
                    • 7. Re: Best way to retrieve mssql data without affecting the app in java swing
                      gimbal2
                      No. Research unicode - in-depth. And I'm not talking about "unicode in Java" either; research how unicode works in general.

                      And be aware that there is a big difference between data (like unicode text) and the presentation of data (like printing unicode text in some component).
                      • 8. Re: Best way to retrieve mssql data without affecting the app in java swing
                        917733
                        Well that would be gud if i have time... but my company is giving hectic pressure to finish off the project... please suggest me some examples or quick starters so that it would be helpfull for me... if there is any code available, that would be much more helpfull for me...
                        • 9. Re: Best way to retrieve mssql data without affecting the app in java swing
                          gimbal2
                          Sorry, you are not going to do it by taking shortcuts. You have to understand this stuff, from A to Z. So get cracking. I found this article a good read to open up the mind:

                          http://www.joelonsoftware.com/articles/Unicode.html
                          • 10. Re: Best way to retrieve mssql data without affecting the app in java swing
                            EJP
                            Well that would be gud if i have time... but my company is giving hectic pressure to finish off the project
                            You don't have time to get it wrong.