Source code: javatools/db/DbUpdater.java
1 /*
2 Javatools (modified version) - Some useful general classes.
3 Copyright (C) 2002-2003 Chris Bitmead (original) Antonio Petrelli (modified)
4
5 This program is free software; you can redistribute it and/or modify
6 it under the terms of the GNU General Public License as published by
7 the Free Software Foundation; either version 2 of the License, or
8 (at your option) any later version.
9
10 This program is distributed in the hope that it will be useful,
11 but WITHOUT ANY WARRANTY; without even the implied warranty of
12 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 GNU General Public License for more details.
14
15 You should have received a copy of the GNU General Public License
16 along with this program; if not, write to the Free Software
17 Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
18
19 Contact me at: brenmcguire@users.sourceforge.net
20 */
21 package javatools.db;
22 import java.util.*;
23 import java.sql.*;
24 import javatools.util.FileLog;
25
26 /**
27 * A class used to update records from SQL tables. The constructor is not
28 * public. To obtain a DbUpdater call DbTable.updater(); Example: To update all
29 * the people who are (younger than 18 or older than 80) and whose name is
30 * "Fred"... to have a favourite_team of "Raiders"; <PRE>
31 * DbDatabase db = ...;
32 * DbTable people = db.getTable("PEOPLE");
33 * DbUpdater updater = people.deleter();
34 * updater.addColumn(people.getColumn("FAVOURITE_TEAM"), "Raiders");
35 * updater.setWhere(people.getColumn("AGE").lessThan(new Integer(18)).or(
36 * people.getColumn("AGE").greaterThan(new Integer(80))).and(
37 * people.getColumn("NAME").equal("FRED"));
38 * int numberOfPeopleUpdated = updater.execute();
39 * </PRE> This is equivilent to... <PRE>
40 * UPDATE PEOPLE SET FAVOURITE_TEAM='Raiders' WHERE (AGE < 18 OR AGE > 80 ) AND NAME='Fred'
41 * </PRE> Note the use of equal(), NOT equals(). To get more fancy, to update
42 * the same group of people to have a favourite team the same as Bill's team,
43 * we use a sub-select... <PRE>
44 * DbDatabase db = ...;
45 * DbTable people = db.getTable("PEOPLE");
46 * DbSelector bills_team = db.selector();
47 * bills_team.addColumn(people.getColumn("FAVOURITE_TEAM"));
48 * bills_team.setWhere(people.getColumn("NAME").equal("BILL"));
49 * DbUpdater updater = people.deleter();
50 * updater.addColumn(people.getColumn("FAVOURITE_TEAM"), bills_team);
51 * updater.setWhere(people.getColumn("AGE").lessThan(new Integer(18)).or(
52 * people.getColumn("AGE").greaterThan(new Integer(80))).and(
53 * people.getColumn("NAME").equal("FRED"));
54 * int numberOfPeopleUpdated = updater.execute();
55 * </PRE> This is equivilent to... <PRE>
56 * UPDATE PEOPLE SET FAVOURITE_TEAM=(SELECT FAVOURITE_TEAM FROM PEOPLE WHERE NAME='Bill')
57 * WHERE (AGE < 18 OR AGE > 80 ) AND NAME='Fred'
58 * </PRE>
59 *
60 * @author Chris Bitmead (original), Antonio Petrelli (modified)
61 * @created December 13, 2001
62 * @version 0.2.0
63 * @commentedby Antonio Petrelli
64 */
65
66 public class DbUpdater {
67 /** The table to update.
68 */
69 DbAbstractTable table;
70 /** The list of columns to update.
71 */
72 List intoList = new ArrayList();
73 /** The list of data to update.
74 */
75 List fromList = new ArrayList();
76 /** The where condition, specifying which rows will be affected.
77 */
78 DbExpr where;
79
80 /** Creates a new DbUpdater.
81 * @param table The table to be updated.
82 */
83 DbUpdater(DbAbstractTable table) {
84 this.table = table;
85 }
86
87 /**
88 * Set the where condition on which records to update.
89 *
90 * @param where The new where value
91 */
92 public void setWhere(DbExpr where) {
93 this.where = where;
94 }
95
96 /**
97 * Add a column specification to update. The new value can either be a raw
98 * value - Integer, String, java.sql.Date etc. Or it can be a DbSelector in
99 * the case of a sub-select.
100 *
101 * @param into The column to update.
102 * @param from The new value.
103 */
104 public void addColumn(DbColumn into, Object from) {
105 intoList.add(into);
106 fromList.add(from);
107 }
108
109 /** Sets in one time the column list and the value list.
110 * @param pIntoList The column list.
111 * @param pFromList The value list.
112 */
113 public void setLists(List pIntoList, List pFromList) {
114 intoList = pIntoList;
115 fromList = pFromList;
116 }
117
118 /**
119 * Execute this delete command on a specific connection.
120 *
121 * @param dbcon The connection to use.
122 * @return The number of record affected.
123 * @exception DbException If something goes wrong.
124 */
125 public int execute(DbConnection dbcon) throws DbException {
126 try {
127 PreparedStatement stmt = dbcon.con.prepareStatement(getQueryString());
128 setSqlValues(stmt, 1);
129 return stmt.executeUpdate();
130 } catch (SQLException e) {
131 throw new DbException(e);
132 }
133 }
134
135 /**
136 * Execute this command on the default connection.
137 *
138 * @return The number of record affected.
139 * @exception DbException If something goes wrong.
140 */
141 public int execute() throws DbException {
142 return execute(table.db.getThreadConnection());
143 }
144
145 /** Sets values for the statement.
146 * @param stmt The statement to use.
147 * @param i An index (obscure).
148 * @throws DbException If something goes wrong.
149 * @throws SQLException If something goes wrong.
150 * @return An index (obscure).
151 */
152 int setSqlValues(PreparedStatement stmt, int i) throws DbException, SQLException {
153 Iterator intoi = intoList.iterator();
154 Iterator fromi = fromList.iterator();
155 while (intoi.hasNext()) {
156 DbColumn intocol = (DbColumn) intoi.next();
157 Object fromcol = fromi.next();
158 if (fromcol instanceof DbSelector) {
159 i = ((DbSelector) fromcol).setSqlValues(stmt, i, null);
160 } else {
161 i = DbSelector.setSqlValue(stmt, i, fromcol, intocol);
162 }
163 }
164 i = where.setSqlValues(stmt, i);
165 return i;
166 }
167
168 /** Returns the query string for this object.
169 * @throws DbException If something goes wrong.
170 * @return The requested query string.
171 */
172 String getQueryString() throws DbException {
173 String rtn = "UPDATE " + table.getFullTableName() + " ";
174 int i = 0;
175 Iterator intoi = intoList.iterator();
176 Iterator fromi = fromList.iterator();
177 while (intoi.hasNext()) {
178 DbColumn intocol = (DbColumn) intoi.next();
179 Object fromcol = fromi.next();
180 if (i != 0) {
181 rtn += ", ";
182 } else {
183 rtn += " SET ";
184 }
185 rtn += intocol.getName() + " = ";
186 if (fromcol instanceof DbSelector) {
187 rtn += "(" + ((DbSelector) fromcol).getQueryString() + ")";
188 } else {
189 rtn += "?";
190 }
191 i++;
192 }
193 if (where != null) {
194 rtn += " WHERE ";
195 rtn += where.getQueryString();
196 }
197 FileLog.singleton().debug("DbSelector", rtn);
198 return rtn;
199 }
200
201 }