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: import java.util.List;
030:
031: import net.sf.borg.model.beans.KeyedBean;
032: import net.sf.borg.model.beans.Memo;
033: import net.sf.borg.model.db.MemoDB;
034:
035: class MemoJdbcDB extends JdbcDB implements MemoDB {
036:
037: MemoJdbcDB(String url, String username) throws Exception {
038: super (url, username);
039: new JdbcDBUpgrader("select private from memos;",
040: "alter table memos add private integer default '0' NOT NULL;")
041: .upgrade();
042: }
043:
044: MemoJdbcDB(Connection conn) {
045: super (conn);
046: }
047:
048: public void addMemo(Memo m) throws Exception {
049: PreparedStatement stmt = connection_
050: .prepareStatement("INSERT INTO memos ( memoname, username, memotext, new, modified, deleted, palmid, private) "
051: + " VALUES " + "( ?, ?, ?, ?, ?, ?, ?, ?)");
052:
053: stmt.setString(1, m.getMemoName());
054: stmt.setString(2, username_);
055:
056: stmt.setString(3, m.getMemoText());
057: stmt.setInt(4, toInt(m.getNew()));
058: stmt.setInt(5, toInt(m.getModified()));
059: stmt.setInt(6, toInt(m.getDeleted()));
060: if (m.getPalmId() != null)
061: stmt.setInt(7, m.getPalmId().intValue());
062: else
063: stmt.setNull(7, java.sql.Types.INTEGER);
064: stmt.setInt(8, toInt(m.getPrivate()));
065:
066: stmt.executeUpdate();
067:
068: }
069:
070: public void delete(String name) throws Exception {
071: PreparedStatement stmt = connection_
072: .prepareStatement("DELETE FROM memos WHERE memoname = ? AND username = ?");
073: stmt.setString(1, name);
074: stmt.setString(2, username_);
075: stmt.executeUpdate();
076:
077: }
078:
079: public Collection getNames() throws Exception {
080: ArrayList keys = new ArrayList();
081: PreparedStatement stmt = connection_
082: .prepareStatement("SELECT memoname FROM memos WHERE username = ? and deleted = 0 ORDER BY memoname");
083: stmt.setString(1, username_);
084: ResultSet rs = stmt.executeQuery();
085:
086: while (rs.next()) {
087: keys.add(rs.getString("memoname"));
088: }
089:
090: return (keys);
091:
092: }
093:
094: public Memo getMemoByPalmId(int id) throws Exception {
095: PreparedStatement stmt = connection_
096: .prepareStatement("SELECT * FROM memos WHERE username = ? and palmid = ? ");
097: stmt.setString(1, username_);
098: stmt.setInt(2, id);
099: ResultSet r = null;
100: try {
101: Memo m = null;
102: r = stmt.executeQuery();
103: if (r.next()) {
104: m = createFrom(r);
105: }
106: return m;
107: } finally {
108: if (r != null)
109: r.close();
110: if (stmt != null)
111: stmt.close();
112: }
113: }
114:
115: private PreparedStatement getPSOne(String name) throws SQLException {
116: PreparedStatement stmt = connection_
117: .prepareStatement("SELECT * FROM memos WHERE memoname = ? AND username = ?");
118: stmt.setString(1, name);
119: stmt.setString(2, username_);
120: return stmt;
121: }
122:
123: private PreparedStatement getPSAll() throws SQLException {
124: PreparedStatement stmt = connection_
125: .prepareStatement("SELECT * FROM memos WHERE username = ?");
126: stmt.setString(1, username_);
127: return stmt;
128: }
129:
130: private Memo createFrom(ResultSet r) throws SQLException {
131: Memo m = new Memo();
132:
133: m.setMemoName(r.getString("memoname"));
134: m.setMemoText(r.getString("memotext"));
135:
136: m.setNew(r.getInt("new") != 0);
137: m.setModified(r.getInt("modified") != 0);
138: m.setDeleted(r.getInt("deleted") != 0);
139: int palmid = r.getInt("palmid");
140: if (!r.wasNull())
141: m.setPalmId(new Integer(palmid));
142: m.setPrivate(r.getInt("private") != 0);
143:
144: return m;
145: }
146:
147: public Collection readAll() throws Exception {
148: PreparedStatement stmt = null;
149: ResultSet r = null;
150: try {
151: stmt = getPSAll();
152: r = stmt.executeQuery();
153: List lst = new ArrayList();
154: while (r.next()) {
155: KeyedBean bean = createFrom(r);
156: lst.add(bean);
157: }
158: return lst;
159: } finally {
160: if (r != null)
161: r.close();
162: if (stmt != null)
163: stmt.close();
164: }
165: }
166:
167: public Memo readMemo(String name) throws Exception {
168:
169: PreparedStatement stmt = null;
170: ResultSet r = null;
171: try {
172: Memo m = null;
173: stmt = getPSOne(name);
174: r = stmt.executeQuery();
175: if (r.next()) {
176: m = createFrom(r);
177: }
178: return m;
179: } finally {
180: if (r != null)
181: r.close();
182: if (stmt != null)
183: stmt.close();
184: }
185: }
186:
187: public void updateMemo(Memo m) throws Exception {
188:
189: PreparedStatement stmt = connection_
190: .prepareStatement("UPDATE memos SET "
191: + "memotext = ?, new = ?, modified = ?, deleted = ?, palmid = ?, private = ? "
192: + " WHERE memoname = ? AND username = ?");
193:
194: stmt.setString(1, m.getMemoText());
195:
196: stmt.setInt(2, toInt(m.getNew()));
197: stmt.setInt(3, toInt(m.getModified()));
198: stmt.setInt(4, toInt(m.getDeleted()));
199: if (m.getPalmId() != null)
200: stmt.setInt(5, m.getPalmId().intValue());
201: else
202: stmt.setNull(5, java.sql.Types.INTEGER);
203: stmt.setInt(6, toInt(m.getPrivate()));
204: stmt.setString(7, m.getMemoName());
205: stmt.setString(8, username_);
206:
207: stmt.executeUpdate();
208:
209: }
210:
211: }
|