1 package net.javacoding.jspider.core.storage.jdbc; 2 3 import net.javacoding.jspider.core.storage.EMailAddressDAO; 4 import net.javacoding.jspider.core.storage.spi.EMailAddressDAOSPI; 5 import net.javacoding.jspider.core.storage.spi.StorageSPI; 6 import net.javacoding.jspider.core.model.*; 7 import net.javacoding.jspider.core.logging.Log; 8 import net.javacoding.jspider.core.logging.LogFactory; 9 10 import java.sql.*; 11 import java.util.ArrayList ; 12 13 16 class EMailAddressDAOImpl implements EMailAddressDAOSPI { 17 18 protected Log log; 19 protected StorageSPI storage; 20 protected DBUtil dbUtil; 21 22 public EMailAddressDAOImpl(StorageSPI storage, DBUtil dbUtil) { 23 this.log = LogFactory.getLog(EMailAddressDAO.class); 24 this.storage = storage; 25 this.dbUtil = dbUtil; 26 } 27 28 public void register(ResourceInternal resource, EMailAddressInternal address) { 29 Connection connection = dbUtil.getConnection(); 30 31 Statement st = null; 32 ResultSet rs = null; 33 34 try { 35 st = connection.createStatement(); 36 rs = st.executeQuery("select count(*) as count from jspider_email_address where address='" + address.getAddress() + "'"); 37 rs.next(); 38 int count = rs.getInt("count"); 39 if (count == 0) { 40 st = connection.createStatement(); 41 st.executeUpdate("insert into jspider_email_address ( address ) values ( '" + address.getAddress() + "' )"); 42 st = connection.createStatement(); 43 ResultSet rs2 = st.executeQuery("select id from jspider_email_address where address = '" + address.getAddress() + "'"); 44 rs2.next(); 45 address.setId(rs2.getInt("id")); 46 } 47 48 rs = st.executeQuery("select count(*) as count from jspider_email_address, jspider_email_address_reference where jspider_email_address_reference.resource=" + resource.getId() + " and jspider_email_address_reference.address = jspider_email_address.id and jspider_email_address.id = " + address.getId()); 49 rs.next(); 50 st = connection.createStatement(); 51 if ( rs.getInt("count") == 0 ) { 52 st.executeUpdate("insert into jspider_email_address_reference ( resource, address, count ) values ( " + resource.getId() + "," + address.getId() + ", 1 )"); 53 } else { 54 st.executeUpdate("update jspider_email_address_reference set count = count + 1 where address = " + address.getId() + " and resource = " + resource.getId() ); 55 } 56 } catch (SQLException e) { 57 log.error("SQLException", e); 58 } finally { 59 dbUtil.safeClose(rs, log); 60 dbUtil.safeClose(st, log); 61 } 62 } 63 64 public EMailAddressInternal find(String address) { 65 Statement st = null; 66 ResultSet rs = null; 67 EMailAddressInternal emailAddress = null; 68 try { 69 Connection connection = dbUtil.getConnection(); 70 st = connection.createStatement(); 71 rs = st.executeQuery("select * from jspider_email_address where address = '" + address + "'"); 72 while (rs.next()) { 73 emailAddress = createEMailAddressFromRecord(rs); 74 } 75 } catch (SQLException e) { 76 log.error("SQLException", e); 77 } finally { 78 dbUtil.safeClose(rs, log); 79 dbUtil.safeClose(st, log); 80 } 81 return emailAddress; 82 } 83 84 public EMailAddressInternal[] findByResource(ResourceInternal resource) { 85 ArrayList al = new ArrayList (); 86 Statement st = null; 87 ResultSet rs = null; 88 try { 89 Connection connection = dbUtil.getConnection(); 90 st = connection.createStatement(); 91 rs = st.executeQuery("select address.id, address.address from jspider_email_address address, jspider_email_address_reference ref where ref.address=address.id and ref.resource=" + resource.getId()); 92 while (rs.next()) { 93 al.add(createEMailAddressFromRecord(rs)); 94 } 95 } catch (SQLException e) { 96 log.error("SQLException", e); 97 } finally { 98 dbUtil.safeClose(rs, log); 99 dbUtil.safeClose(st, log); 100 } 101 return (EMailAddressInternal[]) al.toArray(new EMailAddressInternal[al.size()]); 102 } 103 104 public EMailAddressReferenceInternal[] findReferencesByResource(ResourceInternal resource) { 105 ArrayList al = new ArrayList (); 106 Statement st = null; 107 ResultSet rs = null; 108 try { 109 Connection connection = dbUtil.getConnection(); 110 st = connection.createStatement(); 111 rs = st.executeQuery("select jspider_email_address.address, jspider_email_address_reference.resource, jspider_email_address_reference.count from jspider_email_address, jspider_email_address_reference where jspider_email_address_reference.resource=" + resource.getId() + " and jspider_email_address_reference.address = jspider_email_address.id"); 112 while (rs.next()) { 113 al.add(createEMailAddressReferenceFromRecord(rs)); 114 } 115 } catch (SQLException e) { 116 log.error("SQLException", e); 117 } finally { 118 dbUtil.safeClose(rs, log); 119 dbUtil.safeClose(st, log); 120 } 121 return (EMailAddressReferenceInternal[]) al.toArray(new EMailAddressReferenceInternal[al.size()]); 122 } 123 124 protected EMailAddressInternal createEMailAddressFromRecord(ResultSet rs) throws SQLException { 125 int id = rs.getInt("id"); 126 String address = rs.getString("address"); 127 return new EMailAddressInternal(id, address); 128 } 129 130 protected EMailAddressReferenceInternal createEMailAddressReferenceFromRecord(ResultSet rs) throws SQLException { 131 int resource = rs.getInt("resource"); 132 String address = rs.getString("address"); 133 int count = rs.getInt("count"); 134 return new EMailAddressReferenceInternal(storage, resource, address, count); 135 } 136 137 } 138 | Popular Tags |