001: /*
002: This file is part of BORG.
003:
004: BORG is free software; you can redistribute it and/or modify
005: it under the terms of the GNU General Public License as published by
006: the Free Software Foundation; either version 2 of the License, or
007: (at your option) any later version.
008:
009: BORG is distributed in the hope that it will be useful,
010: but WITHOUT ANY WARRANTY; without even the implied warranty of
011: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
012: GNU General Public License for more details.
013:
014: You should have received a copy of the GNU General Public License
015: along with BORG; if not, write to the Free Software
016: Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
017:
018: Copyright 2003 by Mike Berger
019: */
020:
021: package net.sf.borg.model.db.jdbc;
022:
023: import java.sql.Connection;
024: import java.sql.PreparedStatement;
025: import java.sql.ResultSet;
026: import java.sql.SQLException;
027: import java.util.ArrayList;
028: import java.util.Collection;
029:
030: import net.sf.borg.model.beans.Address;
031: import net.sf.borg.model.beans.KeyedBean;
032: import net.sf.borg.model.db.BeanDB;
033:
034: /**
035: *
036: * this is the JDBC layer for access to the addresses table
037: */
038: class AddrJdbcDB extends JdbcBeanDB implements BeanDB {
039:
040: AddrJdbcDB(String url, String username) throws Exception {
041: super (url, username);
042: }
043:
044: AddrJdbcDB(Connection conn) {
045: super (conn);
046: }
047:
048: public void addObj(KeyedBean bean, boolean crypt) throws Exception {
049: PreparedStatement stmt = connection_
050: .prepareStatement("INSERT INTO addresses ( address_num, username, "
051: + "first_name, last_name, nickname, email, screen_name, work_phone,"
052: + "home_phone, fax, pager, street, city, state, zip, country, company,"
053: + "work_street, work_city, work_state, work_zip, work_country, webpage, notes, birthday, new, modified, deleted) "
054: + " VALUES "
055: + "( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
056:
057: Address addr = (Address) bean;
058:
059: stmt.setInt(1, addr.getKey());
060: stmt.setString(2, username_);
061:
062: stmt.setString(3, addr.getFirstName());
063: stmt.setString(4, addr.getLastName());
064: stmt.setString(5, addr.getNickname());
065: stmt.setString(6, addr.getEmail());
066: stmt.setString(7, addr.getScreenName());
067: stmt.setString(8, addr.getWorkPhone());
068: stmt.setString(9, addr.getHomePhone());
069: stmt.setString(10, addr.getFax());
070: stmt.setString(11, addr.getPager());
071: stmt.setString(12, addr.getStreetAddress());
072: stmt.setString(13, addr.getCity());
073: stmt.setString(14, addr.getState());
074: stmt.setString(15, addr.getZip());
075: stmt.setString(16, addr.getCountry());
076: stmt.setString(17, addr.getCompany());
077: stmt.setString(18, addr.getWorkStreetAddress());
078: stmt.setString(19, addr.getWorkCity());
079: stmt.setString(20, addr.getWorkState());
080: stmt.setString(21, addr.getWorkZip());
081: stmt.setString(22, addr.getWorkCountry());
082: stmt.setString(23, addr.getWebPage());
083: stmt.setString(24, addr.getNotes());
084: java.util.Date bd = addr.getBirthday();
085: if (bd != null)
086: stmt.setDate(25, new java.sql.Date(bd.getTime()));
087: else
088: stmt.setDate(25, null);
089: stmt.setInt(26, toInt(addr.getNew()));
090: stmt.setInt(27, toInt(addr.getModified()));
091: stmt.setInt(28, toInt(addr.getDeleted()));
092: stmt.executeUpdate();
093:
094: writeCache(addr);
095:
096: }
097:
098: public void delete(int key) throws Exception {
099: PreparedStatement stmt = connection_
100: .prepareStatement("DELETE FROM addresses WHERE address_num = ? AND username = ?");
101: stmt.setInt(1, key);
102: stmt.setString(2, username_);
103: stmt.executeUpdate();
104:
105: delCache(key);
106: }
107:
108: public Collection getKeys() throws Exception {
109: ArrayList keys = new ArrayList();
110: PreparedStatement stmt = connection_
111: .prepareStatement("SELECT address_num FROM addresses WHERE username = ? ORDER BY last_name, first_name");
112: stmt.setString(1, username_);
113: ResultSet rs = stmt.executeQuery();
114: while (rs.next()) {
115: keys.add(new Integer(rs.getInt("address_num")));
116: }
117:
118: return (keys);
119:
120: }
121:
122: public int nextkey() throws Exception {
123: PreparedStatement stmt = connection_
124: .prepareStatement("SELECT MAX(address_num) FROM addresses WHERE username = ?");
125: stmt.setString(1, username_);
126: ResultSet r = stmt.executeQuery();
127: int maxKey = 0;
128: if (r.next())
129: maxKey = r.getInt(1);
130: curMaxKey_ = Math.max(curMaxKey_, maxKey);
131: return ++curMaxKey_;
132: }
133:
134: public KeyedBean newObj() {
135: return (new Address());
136: }
137:
138: PreparedStatement getPSOne(int key) throws SQLException {
139: PreparedStatement stmt = connection_
140: .prepareStatement("SELECT * FROM addresses WHERE address_num = ? AND username = ?");
141: stmt.setInt(1, key);
142: stmt.setString(2, username_);
143: return stmt;
144: }
145:
146: PreparedStatement getPSAll() throws SQLException {
147: PreparedStatement stmt = connection_
148: .prepareStatement("SELECT * FROM addresses WHERE username = ?");
149: stmt.setString(1, username_);
150: return stmt;
151: }
152:
153: KeyedBean createFrom(ResultSet r) throws SQLException {
154: Address addr = new Address();
155: addr.setKey(r.getInt("address_num"));
156: addr.setFirstName(r.getString("first_name"));
157: addr.setLastName(r.getString("last_name"));
158: addr.setNickname(r.getString("nickname"));
159: addr.setEmail(r.getString("email"));
160: addr.setScreenName(r.getString("screen_name"));
161: addr.setWorkPhone(r.getString("work_phone"));
162: addr.setHomePhone(r.getString("home_phone"));
163: addr.setFax(r.getString("fax"));
164: addr.setPager(r.getString("pager"));
165: addr.setStreetAddress(r.getString("street"));
166: addr.setCity(r.getString("city"));
167: addr.setState(r.getString("state"));
168: addr.setZip(r.getString("zip"));
169: addr.setCountry(r.getString("country"));
170: addr.setCompany(r.getString("company"));
171: addr.setWorkStreetAddress(r.getString("work_street"));
172: addr.setWorkCity(r.getString("work_city"));
173: addr.setWorkState(r.getString("work_state"));
174: addr.setWorkZip(r.getString("work_zip"));
175: addr.setWorkCountry(r.getString("work_country"));
176: addr.setWebPage(r.getString("webpage"));
177: addr.setNotes(r.getString("notes"));
178: addr.setNew(r.getInt("new") != 0);
179: addr.setModified(r.getInt("modified") != 0);
180: addr.setDeleted(r.getInt("deleted") != 0);
181: if (r.getDate("birthday") != null)
182: addr.setBirthday(new java.util.Date(r.getDate("birthday")
183: .getTime()));
184: return addr;
185: }
186:
187: public void updateObj(KeyedBean bean, boolean crypt)
188: throws Exception {
189:
190: PreparedStatement stmt = connection_
191: .prepareStatement("UPDATE addresses SET "
192: + "first_name = ?, last_name = ?, nickname = ?, email = ?, screen_name = ?, work_phone = ?,"
193: + "home_phone = ?, fax = ?, pager = ?, street = ?, city = ?, state = ?, zip = ?, country = ?, company = ?,"
194: + "work_street = ?, work_city = ?, work_state = ?, work_zip = ?, work_country = ?, webpage = ?, notes = ?, birthday = ?, new = ?, modified = ?, deleted = ? "
195: + " WHERE address_num = ? AND username = ?");
196:
197: Address addr = (Address) bean;
198:
199: stmt.setString(1, addr.getFirstName());
200: stmt.setString(2, addr.getLastName());
201: stmt.setString(3, addr.getNickname());
202: stmt.setString(4, addr.getEmail());
203: stmt.setString(5, addr.getScreenName());
204: stmt.setString(6, addr.getWorkPhone());
205: stmt.setString(7, addr.getHomePhone());
206: stmt.setString(8, addr.getFax());
207: stmt.setString(9, addr.getPager());
208: stmt.setString(10, addr.getStreetAddress());
209: stmt.setString(11, addr.getCity());
210: stmt.setString(12, addr.getState());
211: stmt.setString(13, addr.getZip());
212: stmt.setString(14, addr.getCountry());
213: stmt.setString(15, addr.getCompany());
214: stmt.setString(16, addr.getWorkStreetAddress());
215: stmt.setString(17, addr.getWorkCity());
216: stmt.setString(18, addr.getWorkState());
217: stmt.setString(19, addr.getWorkZip());
218: stmt.setString(20, addr.getWorkCountry());
219: stmt.setString(21, addr.getWebPage());
220: stmt.setString(22, addr.getNotes());
221: java.util.Date bd = addr.getBirthday();
222: if (bd != null)
223: stmt.setDate(23, new java.sql.Date(bd.getTime()));
224: else
225: stmt.setDate(23, null);
226: stmt.setInt(24, toInt(addr.getNew()));
227: stmt.setInt(25, toInt(addr.getModified()));
228: stmt.setInt(26, toInt(addr.getDeleted()));
229:
230: stmt.setInt(27, addr.getKey());
231: stmt.setString(28, username_);
232:
233: stmt.executeUpdate();
234:
235: delCache(addr.getKey());
236: writeCache(addr);
237: }
238:
239: }
|