home *** CD-ROM | disk | FTP | other *** search
/ PC Extra 07 & 08 / pca1507.iso / intface / pca / special4 / software / eclipse.project / WEB-INF / classes / nl / pcactive / db / Relatives.java < prev    next >
Encoding:
Java Source  |  2003-05-20  |  8.9 KB  |  393 lines
  1. package nl.pcactive.db;
  2.  
  3. import java.sql.Connection;
  4. import java.sql.PreparedStatement;
  5. import java.sql.ResultSet;
  6. import java.util.Map;
  7. import java.util.TreeMap;
  8. import java.util.Vector;
  9.  
  10. import nl.pcactive.bean.FamilyBean;
  11. import nl.pcactive.bean.IndividualBean;
  12.  
  13. /**
  14.  * <p>Description: GEDCOM 2 DATABASE</p>
  15.  * <p>Copyright: Copyright (c) 2002</p>
  16.  * <p>Company: Pc-Active</p>
  17.  * @author Benny Lootens
  18.  * @version 2.0
  19.  */
  20. public class Relatives {
  21.  
  22.     /** if you use other tables name, you can change them here */
  23.     public interface Tables {
  24.  
  25.         String INDIVIDUAL = "t_individual";
  26.         String FAMILY = "t_family";
  27.         String CHILD = "t_child";
  28.     }
  29.  
  30.     /** @retrieves all relatives */
  31.     public static Map getRelatives(
  32.         boolean living,
  33.         boolean death,
  34.         String letter) {
  35.         long s = System.currentTimeMillis();
  36.  
  37.         // my database: done 270ms in mysql, 231ms in postgresql  -- 1638 individuals
  38.  
  39.         Connection con = null;
  40.         PreparedStatement pstmt = null;
  41.         ResultSet rs = null;
  42.         TreeMap map = new TreeMap();
  43.  
  44.         try {
  45.  
  46.             String sql = " select * from " + Tables.INDIVIDUAL;
  47.             if (living && death) {
  48.                 // do nothing
  49.  
  50.                 if (letter != null && letter.length() == 1)
  51.                     sql += " WHERE lastname like '" + letter + "%'";
  52.             } else {
  53.                 if (living) {
  54.                     sql += " WHERE length(deathdate) = 0 ";
  55.                 } else {
  56.                     sql += " WHERE length(deathdate) > 0 ";
  57.                 }
  58.  
  59.                 if (letter != null && letter.length() == 1)
  60.                     sql += " AND lastname like '" + letter + "%'";
  61.  
  62.             }
  63.  
  64.             con = Jdbc.openConnection();
  65.             pstmt = con.prepareStatement(sql);
  66.             rs = pstmt.executeQuery();
  67.             while (rs.next()) {
  68.                 IndividualBean bean = new IndividualBean();
  69.                 bean.populate(rs);
  70.                 map.put(
  71.                     bean.getLast()
  72.                         + " "
  73.                         + bean.getFirst()
  74.                         + " ("
  75.                         + bean.getId()
  76.                         + ")",
  77.                     bean);
  78.             }
  79.         } catch (Exception ex) {
  80.             ex.printStackTrace(System.err);
  81.         } finally {
  82.             Jdbc.close(rs);
  83.             Jdbc.close(pstmt);
  84.             Jdbc.close(con);
  85.  
  86.             System.out.println(
  87.                 "getRelatives-elapsed time = "
  88.                     + (System.currentTimeMillis() - s)
  89.                     + "ms");
  90.         }
  91.         return map;
  92.     }
  93.  
  94.     /** @return an individual by the given id */
  95.     public static IndividualBean getIndividualById(int id) {
  96.         Connection con = null;
  97.         PreparedStatement pstmt = null;
  98.         ResultSet rs = null;
  99.         IndividualBean bean = new IndividualBean();
  100.  
  101.         try {
  102.             con = Jdbc.openConnection();
  103.             pstmt =
  104.                 con.prepareStatement(
  105.                     " select * from "
  106.                         + Tables.INDIVIDUAL
  107.                         + " where individual=?");
  108.             pstmt.setInt(1, id);
  109.             rs = pstmt.executeQuery();
  110.             if (rs.next()) {
  111.                 bean.populate(rs);
  112.             }
  113.         } catch (Exception ex) {
  114.             ex.printStackTrace(System.err);
  115.         } finally {
  116.             Jdbc.close(rs);
  117.             Jdbc.close(pstmt);
  118.             Jdbc.close(con);
  119.         }
  120.         return bean;
  121.     }
  122.  
  123.     /** @retrieves all families */
  124.     public static Map getFamilies(
  125.         boolean living,
  126.         boolean death,
  127.         String letter) {
  128.         long s = System.currentTimeMillis();
  129.  
  130.         // my database: done 10286ms in mysql, 3385ms in postgresql  -- 578 families
  131.  
  132.         Connection con = null;
  133.         PreparedStatement pstmt = null, pstmtIndiv = null;
  134.         ResultSet rs = null, rs2 = null;
  135.         TreeMap map = new TreeMap();
  136.  
  137.         try {
  138.  
  139.             String sql =
  140.                 " select * from "
  141.                     + Tables.FAMILY
  142.                     + ", "
  143.                     + Tables.INDIVIDUAL
  144.                     + " where "
  145.                     + Tables.FAMILY
  146.                     + ".husband = "
  147.                     + Tables.INDIVIDUAL
  148.                     + ".individual ";
  149.  
  150.             if (letter != null && letter.length() == 1)
  151.                 sql += " AND "
  152.                     + Tables.INDIVIDUAL
  153.                     + ".lastname like '"
  154.                     + letter
  155.                     + "%'";
  156.  
  157.             //        if (living && death) {
  158.             //          // do nothing
  159.             //        }
  160.             //        else {
  161.             //          if (living) {
  162.             //            sql += " WHERE length(deathdate) = 0 ";
  163.             //          }
  164.             //          else {
  165.             //            sql += " WHERE length(deathdate) > 0 ";
  166.             //          }
  167.             //        }
  168.  
  169.             con = Jdbc.openConnection();
  170.             pstmt = con.prepareStatement(sql);
  171.  
  172.             pstmtIndiv =
  173.                 con.prepareStatement(
  174.                     "select * from "
  175.                         + Tables.INDIVIDUAL
  176.                         + " where individual=?");
  177.  
  178.             rs = pstmt.executeQuery();
  179.             while (rs.next()) {
  180.                 FamilyBean bean = new FamilyBean();
  181.                 bean.populate(rs);
  182.  
  183.                 String husband = "";
  184.  
  185.                 pstmtIndiv.setInt(1, Integer.parseInt(bean.getHusband()));
  186.                 rs2 = pstmtIndiv.executeQuery();
  187.                 if (rs2.next())
  188.                     husband =
  189.                         rs2.getString("lastname")
  190.                             + " "
  191.                             + rs2.getString("firstname");
  192.                 Jdbc.close(rs2);
  193.  
  194.                 String wife = "";
  195.  
  196.                 pstmtIndiv.setInt(1, Integer.parseInt(bean.getWife()));
  197.                 rs2 = pstmtIndiv.executeQuery();
  198.                 if (rs2.next())
  199.                     wife =
  200.                         rs2.getString("lastname")
  201.                             + " "
  202.                             + rs2.getString("firstname");
  203.                 Jdbc.close(rs2);
  204.  
  205.                 map.put(
  206.                     husband + " x " + wife + " (" + bean.getId() + ")",
  207.                     bean);
  208.             }
  209.         } catch (Exception ex) {
  210.             ex.printStackTrace(System.err);
  211.         } finally {
  212.             Jdbc.close(rs2);
  213.             Jdbc.close(pstmtIndiv);
  214.             Jdbc.close(rs);
  215.             Jdbc.close(pstmt);
  216.             Jdbc.close(con);
  217.  
  218.             System.out.println(
  219.                 "getFamilies-elapsed time = "
  220.                     + (System.currentTimeMillis() - s)
  221.                     + "ms");
  222.         }
  223.         return map;
  224.     }
  225.  
  226.     /** @return an family by the given id */
  227.     public static FamilyBean getFamilyById(int id) {
  228.         Connection con = null;
  229.         PreparedStatement pstmt = null;
  230.         ResultSet rs = null;
  231.         FamilyBean bean = new FamilyBean();
  232.  
  233.         try {
  234.             con = Jdbc.openConnection();
  235.             pstmt =
  236.                 con.prepareStatement(
  237.                     " select * from " + Tables.FAMILY + " where family=?");
  238.             pstmt.setInt(1, id);
  239.             rs = pstmt.executeQuery();
  240.             if (rs.next()) {
  241.                 bean.populate(rs);
  242.             }
  243.         } catch (Exception ex) {
  244.             ex.printStackTrace(System.err);
  245.         } finally {
  246.             Jdbc.close(rs);
  247.             Jdbc.close(pstmt);
  248.             Jdbc.close(con);
  249.         }
  250.         return bean;
  251.     }
  252.  
  253.     /** @retrieves all children for a given family */
  254.     public static Map getChildrenByFamily(int familyid) {
  255.         Connection con = null;
  256.         PreparedStatement pstmt = null;
  257.         ResultSet rs = null;
  258.         TreeMap map = new TreeMap();
  259.  
  260.         try {
  261.             con = Jdbc.openConnection();
  262.             pstmt =
  263.                 con.prepareStatement(
  264.                     " select child from " + Tables.CHILD + " where family=?");
  265.             pstmt.setInt(1, familyid);
  266.  
  267.             rs = pstmt.executeQuery();
  268.             while (rs.next()) {
  269.                 IndividualBean bean = getIndividualById(rs.getInt("child"));
  270.                 map.put(
  271.                     bean.getLast()
  272.                         + " "
  273.                         + bean.getFirst()
  274.                         + " ("
  275.                         + bean.getId()
  276.                         + ")",
  277.                     bean);
  278.             }
  279.         } catch (Exception ex) {
  280.             ex.printStackTrace(System.err);
  281.         } finally {
  282.             Jdbc.close(rs);
  283.             Jdbc.close(pstmt);
  284.             Jdbc.close(con);
  285.         }
  286.         return map;
  287.     }
  288.  
  289.     /** @return an family related to the given individual  */
  290.     public static Object[] getFamilyByIndividualId(int id) {
  291.         Connection con = null;
  292.         PreparedStatement pstmt = null;
  293.         ResultSet rs = null;
  294.         Vector v = new Vector();
  295.  
  296.         try {
  297.             con = Jdbc.openConnection();
  298.             pstmt =
  299.                 con.prepareStatement(
  300.                     " select * from "
  301.                         + Tables.FAMILY
  302.                         + " where husband=? or wife=? ");
  303.             pstmt.setInt(1, id);
  304.             pstmt.setInt(2, id);
  305.             rs = pstmt.executeQuery();
  306.             while (rs.next()) {
  307.                 FamilyBean bean = new FamilyBean();
  308.                 bean.populate(rs);
  309.                 v.addElement(bean);
  310.             }
  311.         } catch (Exception ex) {
  312.             ex.printStackTrace(System.err);
  313.         } finally {
  314.             Jdbc.close(rs);
  315.             Jdbc.close(pstmt);
  316.             Jdbc.close(con);
  317.         }
  318.         return v.toArray();
  319.     }
  320.  
  321.     public static IndividualBean getParentByChildId(int childid, String sex) {
  322.  
  323.         long s = System.currentTimeMillis();
  324.  
  325.         Connection con = null;
  326.         PreparedStatement pstmt = null, pstmt2 = null;
  327.         ResultSet rs = null;
  328.         IndividualBean bean = new IndividualBean();
  329.  
  330.         try {
  331.             int individualid = 0, familyid = 0;
  332.  
  333.             con = Jdbc.openConnection();
  334.  
  335.             pstmt =
  336.                 con.prepareStatement(
  337.                     " select * from "
  338.                         + Tables.INDIVIDUAL
  339.                         + " where individual=?");
  340.  
  341.             // get family to which the child belongs
  342.                 pstmt2 =
  343.                     con
  344.                         .prepareStatement(
  345.                             " select family from "
  346.                             + Tables.CHILD
  347.                             + " where child=? order by family desc" // latest family first
  348.     );
  349.             pstmt2.setInt(1, childid);
  350.             rs = pstmt2.executeQuery();
  351.             if (rs.next()) {
  352.                 familyid = rs.getInt("family");
  353.             }
  354.             Jdbc.close(rs);
  355.             Jdbc.close(pstmt2);
  356.  
  357.             // get parent via the family
  358.             pstmt2 =
  359.                 con.prepareStatement(
  360.                     " select "
  361.                         + ("F".equals(sex) ? "wife" : "husband")
  362.                         + " from "
  363.                         + Tables.FAMILY
  364.                         + " where family=? ");
  365.             pstmt2.setInt(1, familyid);
  366.             rs = pstmt2.executeQuery();
  367.             if (rs.next()) {
  368.                 individualid = rs.getInt("F".equals(sex) ? "wife" : "husband");
  369.             }
  370.             Jdbc.close(rs);
  371.  
  372.             pstmt.setInt(1, individualid);
  373.  
  374.             rs = pstmt.executeQuery();
  375.             if (rs.next()) {
  376.                 bean.populate(rs);
  377.             }
  378.         } catch (Exception ex) {
  379.             ex.printStackTrace(System.err);
  380.         } finally {
  381.             Jdbc.close(rs);
  382.             Jdbc.close(pstmt);
  383.             Jdbc.close(pstmt2);
  384.             Jdbc.close(con);
  385.             System.out.println(
  386.                 "getParentByChild-elapsed time = "
  387.                     + (System.currentTimeMillis() - s)
  388.                     + "ms");
  389.         }
  390.         return bean;
  391.     }
  392.  
  393. }