Java DB Examples

Example #1

File: Member.java Project: ssoward/HomeTeaching

 /*
  * fetch for a single pid
  */
 public void fetch(String pid) throws DNFException {
   DB db = new DB();
   try {
     Connection conn = db.openConnection();
     Statement stm = conn.createStatement();
     String sql = "SELECT * FROM " + TABLE_NAME + " where pid='" + pid + "'"; // join user_roles on
     ResultSet rset = stm.executeQuery(sql);
     while (rset.next()) {
       this.pid = rset.getString("pid");
       this.firstName = rset.getString("first_name");
       this.lastName = rset.getString("last_name");
       this.addr1 = rset.getString("addr1");
       this.addr2 = rset.getString("addr2");
       this.email = rset.getString("email");
       this.birth = rset.getString("birth");
       this.phone = rset.getString("phone");
       this.cell = rset.getString("cell");
       this.fam_role = rset.getString("fam_role");
       this.familypid = rset.getString("fam_pid");
       this.mem_role = rset.getString("mem_role");
       this.middleName = rset.getString("middle_name");
       this.has_comp = rset.getString("has_comp");
     }
     rset.close();
     stm.close();
     conn.close();
   } catch (SQLException e) {
     e.printStackTrace();
   }
 }

Example #2

File: Member.java Project: ssoward/HomeTeaching

  public void store() {
    com.soward.db.DB db = new com.soward.db.DB();
    Connection conn;
    Statement stm = null;
    try {
      conn = db.openConnection();
      stm = conn.createStatement();
      if (this.assertStorable()) {
        // check to see that the family pid is set if so do nothing, else
        // create new family entry and get key for familypid.
        // System.out.println("FamPid: "+familypid);
        if (!StringUtil.isSet(this.familypid)) {
          Family newFamily = new Family();
          String famName = "";
          // if pid not set, this is a new family.
          famName = lastName + "_" + firstName;

          this.familypid = newFamily.saveId(famName, getMem_role().equals(MemberDomain.ELDER));
        }
        // System.out.println("FamPid: "+familypid);
        // check to see if this is an update
        // by checking for a mempid
        // if memberpid exists, do update
        if (!hasPid()) {
          String sql =
              "insert into "
                  + TABLE_NAME
                  + " "
                  + COL_NAMES
                  + "values("
                  + null
                  + ","
                  + "'"
                  + (firstName.replaceAll("'", "&#39"))
                  + "'"
                  + ","
                  + "'"
                  + (lastName.replaceAll("'", "&#39"))
                  + "'"
                  + ","
                  + "'"
                  + (addr1.replaceAll("'", "&#39"))
                  + "'"
                  + ","
                  + "'"
                  + (addr2.replaceAll("'", "&#39"))
                  + "'"
                  + ","
                  + "'"
                  + (email.replaceAll("'", "&#39"))
                  + "'"
                  + ","
                  + "'"
                  + (birth.replaceAll("'", "&#39"))
                  + "'"
                  + ","
                  + "'"
                  + (cell.replaceAll("'", "&#39"))
                  + "'"
                  + ","
                  + "'"
                  + (fam_role.replaceAll("'", "&#39"))
                  + "'"
                  + ","
                  + "'"
                  + (familypid.replaceAll("'", "&#39"))
                  + "'"
                  + ","
                  + "'"
                  + (mem_role.replaceAll("'", "&#39"))
                  + "'"
                  + ","
                  + "'"
                  + (middleName.replaceAll("'", "&#39"))
                  + "'"
                  + ","
                  + "'"
                  + (phone.replaceAll("'", "&#39"))
                  + "'"
                  + ","
                  + "'"
                  + (has_comp.replaceAll("'", "&#39"))
                  + "'"
                  + ")";
          //                  System.out.println(sql);
          stm.executeUpdate(sql);
        } else {
          String sql =
              "update "
                  + TABLE_NAME
                  + " set "
                  + "first_name  ='"
                  + (firstName.replaceAll("'", "&#39"))
                  + "'"
                  + ","
                  + "last_name   ='"
                  + (lastName.replaceAll("'", "&#39"))
                  + "'"
                  + ","
                  + "addr1       ='"
                  + (addr1.replaceAll("'", "&#39"))
                  + "'"
                  + ","
                  + "addr2       ='"
                  + (addr2.replaceAll("'", "&#39"))
                  + "'"
                  + ","
                  + "email       ='"
                  + (email.replaceAll("'", "&#39"))
                  + "'"
                  + ","
                  + "birth       ='"
                  + (birth.replaceAll("'", "&#39"))
                  + "'"
                  + ","
                  + "cell        ='"
                  + (cell.replaceAll("'", "&#39"))
                  + "'"
                  + ","
                  + "fam_role    ='"
                  + (fam_role.replaceAll("'", "&#39"))
                  + "'"
                  + ","
                  + "fam_pid     ='"
                  + (familypid.replaceAll("'", "&#39"))
                  + "'"
                  + ","
                  + "mem_role    ='"
                  + (mem_role.replaceAll("'", "&#39"))
                  + "'"
                  + ","
                  + "middle_name ='"
                  + (middleName.replaceAll("'", "&#39"))
                  + "'"
                  + ","
                  + "phone       ='"
                  + (phone.replaceAll("'", "&#39"))
                  + "'"
                  + ","
                  + "has_comp    ='"
                  + (has_comp.replaceAll("'", "&#39"))
                  + "'"
                  + " "
                  + "where pid='"
                  + pid
                  + "'";
          //                  System.out.println(sql);
          stm.executeUpdate(sql);
        }
        conn.close();
        stm.close();
      }
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  }