how to improve my program code ?

[Alvin chew]

hi, good day, can anyone give me some ideas how to i improve my program, should i have few more classes rather than just keep all my code in one class ? thanks in advance for viewing and answering

public class DBConnection {
    
    private int numrow ;
    private int tbloption;
    
    private String tablename = "";
    private ArrayList arr = null;
    private String dataary[] ;
    
    public void setConnection(String tblname)
    {
        tablename = tblname;
        boolean valid = false;
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        StringBuffer sb = new StringBuffer();
        arr = new ArrayList();
       
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
 
            con = DriverManager.getConnection(
                "jdbc:mysql://localhost/mycrmdb", "root", "root");
 
            
            
            stmt = con.createStatement();
            rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tablename);
            rs.next();
            numrow    = rs.getInt(1);
            
            rs = stmt.executeQuery("select * from " + tablename);
            
            setTableOption(tablename);
            
            //ResultSetMetaData rsm = rs.getMetaData();
           // int numcolumn = rsm.getColumnCount(); get number of column
            
           
            dataary = new String[numrow];
            int index = 0;
            
            while(rs.next())
            {
                switch(getTableOption())
                {
                    case 1:
                    {
                         System.out.println("choose 1");
                    
                        arr.add(new NewCampaignFrm(rs.getString(1),rs.getString(2),rs.getString(3),
                                                   rs.getString(4),rs.getString(5),rs.getString(6),
                                                   rs.getString(7),rs.getString(8),rs.getString(9),
                                                   rs.getString(10),rs.getString(11),rs.getString(12),
                                                   rs.getString(13),rs.getString(14)));
                      
                    }break;
                    
                    
                    case 2:
                    {
                        System.out.println("choose 2");
                        arr.add(new NewCompanyFrm(rs.getString(1),rs.getString(2),rs.getString(3),
                                                  rs.getString(4),rs.getString(5),rs.getString(6),
                                                  rs.getString(7),rs.getString(8),rs.getString(9),
                                                  rs.getString(10),rs.getString(11),rs.getString(12),
                                                  rs.getString(13),rs.getString(14),rs.getString(15),
                                                  rs.getString(16)));
                    }break;
                    
                    case 3:
                    {
                        System.out.println("choose 3");
                        arr.add(new NewCompetitorFrm(rs.getString(1),rs.getString(2),rs.getString(3),
                                                     rs.getString(4),rs.getString(5),rs.getString(6),
                                                     rs.getString(7),rs.getString(8),rs.getString(9),
                                                     rs.getString(10),rs.getString(11),rs.getString(12),
                                                     rs.getString(13),rs.getString(14),rs.getString(15)));
                    }break;
                
                    case 4:
                    {
                        System.out.println("choose 4");
                        arr.add(new NewComplaintFrm(rs.getString(1),rs.getString(2),rs.getString(3),
                                                  rs.getString(4),rs.getString(5),rs.getString(6),
                                                  rs.getString(7),rs.getString(8),rs.getString(9),
                                                  rs.getString(10)));
                    }break;
                    
                    case 5:
                    {
                        System.out.println("choose 5");
                        arr.add(new NewContactFrm(rs.getString(1),rs.getString(2),rs.getString(3),
                                                     rs.getString(4),rs.getString(5),rs.getString(6),
                                                     rs.getString(7),rs.getString(8),rs.getString(9),
                                                     rs.getString(10),rs.getString(11),rs.getString(12),
                                                     rs.getString(13)));
                    }break;
                    
                    case 6:
                    {
                        System.out.println("choose 6");
                        arr.add(new NewProductFrm(rs.getString(1),rs.getString(2),rs.getString(3),
                                                     rs.getString(4),rs.getString(5),rs.getString(6),
                                                     rs.getString(7),rs.getString(8),rs.getString(9),
                                                     rs.getString(10),rs.getString(11),rs.getString(12),
                                                     rs.getString(13)));
                    }break;
                    
                    case 7:
                    {
                        System.out.println("choose 7");
                        arr.add(new ProductQueryFrm(rs.getString(1),rs.getString(2)));
                    }break;
                 
                    default :
                        System.out.println("while loop problem");
                        break;
                }
                
                
            }          
            System.out.println("running....");
            
           
            
           
        }
        catch (ClassNotFoundException e) {
            e.printStackTrace();
           System.out.println("No Connection Connect ..");
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            try { if (rs != null) rs.close(); } catch (SQLException e) {};
            try { if (stmt != null) stmt.close(); } catch (SQLException e) {};
            try { if (con != null) con.close(); } catch (SQLException e) {};
        }
        
        
    }
    
    public void setTableOption(String tblname)
    {
        if(tblname.equalsIgnoreCase("newcampaign"))
        {tbloption=1;}
        
        if(tblname.equalsIgnoreCase("newcompany"))
        {tbloption=2;}
        
        if(tblname.equalsIgnoreCase("newcompetitor"))
        {tbloption=3;}
        
        if(tblname.equalsIgnoreCase("newcomplaint"))
        {tbloption=4;}
        
        if(tblname.equalsIgnoreCase("newcontact"))
        {tbloption=5;}
        
        if(tblname.equalsIgnoreCase("newproduct"))
        {tbloption=6;}
        
        if(tblname.equalsIgnoreCase("productquery"))
        {tbloption=7;}
    }
    
    public int getTableOption()
    {
        return this.tbloption;
    }
    
       
       
    public String getCampaignData()
    {
       StringBuffer sb = new StringBuffer();
       Iterator ite = arr.iterator();
       
       while(ite.hasNext())
       {
            NewCampaignFrm nc = (NewCampaignFrm) ite.next();
            sb.append("<tr><td>" + nc.getCam_code() + "</td> " +"<td>" + nc.getCam_name() +"</td>"
                      +"<td>" + nc.getCam_type() +"</td>"  + "<td>" + nc.getCam_status() +"</td>"
                      +"<td>" + nc.getCam_start_day() +"</td>"  +"<td>" + nc.getCam_start_month() +"</td>"
                      +"<td>" + nc.getCam_start_year() +"</td>"  +"<td>" + nc.getCam_end_day() +"</td>"
                      +"<td>" + nc.getCam_end_month() +"</td>"+"<td>" + nc.getCam_end_year() +"</td>"
                      +"<td>" + nc.getCam_revenue() +"</td>"+"<td>" + nc.getCam_amount() +"</td>"
                      +"<td>" + nc.getCam_respond() +"</td>"+"<td>" + nc.getCam_desc() +"</td></tr>"); 
       }
      return sb.toString();
    }
    
    
     public String getCompanyData()
    {
       //same function as getCampaignData() but for other form     }
     
      public String getCompetitorData()
    {
       //same function as getCampaignData() but for other form         }
    
      
      public String getComplaintData()
    {
       //same function as getCampaignData() but for other form           
      
public String getContactData()
    {
       //same function as getCampaignData() but for other form         }
      
      public String getProductData()
    {
       //same function as getCampaignData() but for other form         }
      
       public String getProductQueryData()
    {
       StringBuffer sb = new StringBuffer();
       Iterator ite = arr.iterator();
       
       while(ite.hasNext())
       {
           ProductQueryFrm nc = (ProductQueryFrm) ite.next();
            sb.append("<tr><td>" + nc.getPro_query_code() + "</td> " +"<td>" + 
nc.getPro_query_name()+"</td></tr>"); 
       }
      return sb.toString();
    }
      
    // this is main method to test data existance 
    public static void main(String arg[])
    {
        DBConnection d = new DBConnection();
        d.setConnection("newcompany");
        System.out.println(d.getCompanyData());
               
        
    }
}

[Please type "&lt;td>" instead of "<td>", -- the latter breaks the post ]
[ November 28, 2004: Message edited by: Mapraputa Is ]

[Lasse Koskela]

Yes, you should definitely break it into more classes or into more methods.

A good starting point might be to go through a list of code smells and think whether some of those are present in your code (and then fix them, of course).
[ November 28, 2004: Message edited by: Lasse Koskela ]

[Gerald Davis]

I would basically say the same as the other dude; break up methods into smaller ones.

The code between the case statements basically do the same thing, so make them into static method that returns new NewCompanyFrm object, you case statements will look like this.

case 2:
{
System.out.println("choose 2");
int[] alist = [1,2,4,5,7,8,10,11,13,14,16] //however arrays are implemented
arr.add(funcNewCompanyform(alist);
}break;

You code seems very complicated, have you looked for a better way of doing this. Could PHP or JSP help you; the mixing of HTML and source code makes changing HTML a pain. If you must use Java this way, maybe there are simple librarys on Internet that could hide the complication of ODBC database access to simplify the code for you.