sql and database

Question

I have this method in my database class.and I want to get a part of data from a column which is "dateOfBirth" in MySQL table ,but I don't know why the list.size() is "0" but when i use System.out.println() in my code it will show just the first line of sql table ,although I have two rows!!!

my method:

   public static int getBirthPercent(String i) throws SQLException {
    Statement stmt = conn.createStatement();
    List<String> list = null;
    if (i.equals("O")) {


        ResultSet rst = stmt.executeQuery("SELECT dateOfBirth from birthtable");
        while (rst.next()) {
            String s1 = rst.getString(1);
            if (rst.wasNull()) {
                s1 = null;
            }
            String s2 = s1.substring(s1.length() - 4);
            int s3 = Integer.parseInt(s2);
            if (list == null && s3 < 1970) {
                list = new ArrayList<String>();
                list.add(s2);

            } else {
                list = new ArrayList<String>(0);

            }

        }


    }
    if (i.equals("N")) {

        ResultSet rst = stmt.executeQuery("SELECT dateOfBirth from birthtable");
        while (rst.next()) {
            String s1 = rst.getString(1);
            if (rst.wasNull()) {
                s1 = null;
            }
            String s2 = s1.substring(s1.length() - 4);
            int s3 = Integer.parseInt(s2);
            if (list == null && s3 > 2000) {
                list = new ArrayList<String>();
                list.add(s2);
                System.out.println(list);

            } else {
                list = new ArrayList<String>(0);

            }

        }
    }

it will return "0" for all "if" situation but the System.out.println() ,shows [2006] which is one of my row's column's year,although I have two rows it must show [2006,2009].but it doesn't!!!

Answer 1

Now try this code, and let us know. Cheers.

   public static int getBirthPercent(String i) throws SQLException {

    Statement stmt = conn.createStatement();
    ResultSet rst = null;
    List<String> list = new ArrayList<String>();
    if (i.equals("O")) {
        rst = stmt.executeQuery("SELECT dateOfBirth from birthtable");
        while (rst.next()) {
            String s1 = rst.getString(1);
            if (s1 != null && !s1.isEmpty()) {
               String s2 = s1.substring(s1.length() - 4);
               int n = Integer.parseInt(s2);
               if (n < 1970) {
                 list.add(s2);                    
               }
            }
        }
    }
    if (i.equals("N")) {
        rst = stmt.executeQuery("SELECT dateOfBirth from birthtable");
        while (rst.next()) {
            String s1 = rst.getString(1);
            if (s1 != null && !s1.isEmpty()) {
               String s2 = s1.substring(s1.length() - 4);
               int n = Integer.parseInt(s2);
               if (n > 2000) {
                list.add(s2);
               }
            }
        }
    }

    System.out.println(list);                    

   }

Enough refactoring for now. Try to do more for yourself. For example,

• look into commons-lang StringUtils to replace null checking,
• use Date object to store dates and use rs.getDate() instead,
• you can use Calendar object to get the year out. Or even SimpleDateFormat object would work too
• etc...