Source code: javatools/db/DbInserter.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 insert records into SQL tables. The constructor is not
28 * public. To obtain a DbInserter call DbTable.inserter(); Example: To insert a
29 * record into the people table... <PRE>
30 * DbDatabase db = ...;
31 * DbTable people = db.getTable("PEOPLE");
32 * DbInserter inserter = people.inserter();
33 * inserter.addColumn(people.getColumn("NAME"), "Fred"));
34 * inserter.addColumn(people.getColumn("FAVOURITE_TEAM"), "Raiders");
35 * inserter.addColumn(people.getColumn("AGE"), new Integer(30));
36 * int numberOfPeopleInserted = inserter.execute();
37 * </PRE> This is equivilent to... <PRE>
38 * INSERT INTO PEOPLE(NAME, FAVOURITE_TEAM, AGE) VALUES('Fred', 'Raiders', 30)
39 * </PRE> The same thing as above can be achieved using a SELECT clause, and
40 * this can lead us to creating much more complex expressions... <PRE>
41 * DbDatabase db = ...;
42 * DbSelector selector = db.selector();
43 * DbTable people = db.getTable("PEOPLE");
44 * DbInserter inserter = people.inserter(selector);
45 * inserter.addColumn(people.getColumn("NAME"), selector.addColumn("Fred")));
46 * inserter.addColumn(people.getColumn("FAVOURITE_TEAM"), selector.addColumn("Raiders"));
47 * inserter.addColumn(people.getColumn("AGE"), selector.addColumn(new Integer(30)));
48 * int numberOfPeopleInserted = inserter.execute();
49 * </PRE> This is equivilent to... <PRE>
50 * INSERT INTO PEOPLE(NAME, FAVOURITE_TEAM, AGE) SELECT 'Fred', 'Raiders', 30
51 * </PRE> To get more fancy we can insert data that has been selected from
52 * another table. To insert all the people from the PLAYERS table into the
53 * PEOPLE table who are older than 20, and we set their favourite team to be
54 * the team they play for... <PRE>
55 * DbDatabase db = ...;
56 * DbSelector selector = db.selector();
57 * DbTable people = db.getTable("PEOPLE");
58 * DbTable players = db.getTable("PLAYERS");
59 * DbInserter inserter = people.inserter(selector);
60 * inserter.addColumn(people.getColumn("NAME"), selector.addColumn(players.getColumn("NAME")));
61 * inserter.addColumn(people.getColumn("FAVOURITE_TEAM"), selector.addColumn(players.getColumn("TEAM")));
62 * inserter.addColumn(people.getColumn("AGE"), selector.addColumn(players.getColumn("AGE")));
63 * selector.setWhere(players.getColumn("AGE").greaterThan(new Integer(20)));
64 * int numberOfPeopleInserted = inserter.execute();
65 * </PRE> This is equivilent to... <PRE>
66 * INSERT INTO PEOPLE(NAME, FAVOURITE_TEAM, AGE) SELECT NAME, TEAM, AGE FROM PLAYERS WHERE AGE > 20
67 * </PRE>
68 *
69 * @author Chris Bitmead (original), Antonio Petrelli (modified)
70 * @created December 13, 2001
71 * @version 0.2.0
72 */
73
74 public class DbInserter {
75 /** The table to insert values into.
76 */
77 DbAbstractTable table;
78 /** The selector that can be used to add data. It can be <CODE>null</CODE>.
79 */
80 DbSelector selector;
81 /** The list of columns in which data will be stored.
82 */
83 List intoList = new ArrayList();
84 /** The list of values that will be stored.
85 */
86 List fromList = new ArrayList();
87
88 /** Creates a new DbInserter.
89 * @param table The table to insert data into.
90 * @param selector The selector that will add data.
91 */
92 DbInserter(DbAbstractTable table, DbSelector selector) {
93 this.table = table;
94 this.selector = selector;
95 }
96
97 /** Creates a new inserter.
98 * @param table The table to insert data into.
99 */
100 DbInserter(DbAbstractTable table) {
101 this.table = table;
102 this.selector = null;
103 }
104
105 /** Puts data into a prepared statement.
106 * @param stmt The prepared statement.
107 * @param i An index (obscure).
108 * @throws DbException If something goes wrong.
109 * @throws SQLException If something goes wrong.
110 * @return An index, obscure.
111 */
112 public int setSqlValues(PreparedStatement stmt, int i) throws DbException, SQLException {
113 if (selector == null) {
114 Iterator it = fromList.iterator();
115 Iterator intoit = intoList.iterator();
116 while (it.hasNext()) {
117 DbSelector.setSqlValue(stmt, i++, it.next(), (DbColumn) intoit.next());
118 }
119 } else {
120 selector.setSqlValues(stmt, 1, intoList);
121 }
122 return i;
123 }
124
125 /**
126 * Specify the value of a column to add.
127 *
128 * @param into The column we are inserting into.
129 * @param from The column from a selector that we are getting a value from.
130 */
131 public void addColumn(DbColumn into, Object from) {
132 intoList.add(into);
133 fromList.add(from);
134 }
135
136 /** Directly sets intoList and fromList.
137 * @param pIntoList The list containing the columns to put data into.
138 * @param pFromList The list containing the data that will be put.
139 */
140 public void setLists(List pIntoList, List pFromList) {
141 intoList = pIntoList;
142 fromList = pFromList;
143 }
144
145 /**
146 * Execute this command on a specific connection.
147 *
148 * @param dbcon The connection to use.
149 * @return The number of record affected.
150 * @exception DbException If something goes wrong.
151 */
152 public int execute(DbConnection dbcon) throws DbException {
153 try {
154 PreparedStatement stmt = dbcon.con.prepareStatement(getQueryString());
155 setSqlValues(stmt, 1);
156 return stmt.executeUpdate();
157 } catch (SQLException e) {
158 if (e instanceof SQLWarning) {
159 System.out.println("Attention, SQL Warning:");
160 System.out.println(e.getMessage());
161 return 0;
162 }
163 else
164 throw new DbException(e);
165 }
166 }
167
168 /**
169 * Execute this command on the default connection.
170 *
171 * @return The number of record affected.
172 * @exception DbException If something goes wrong.
173 */
174 public int execute() throws DbException {
175 return execute(table.db.getThreadConnection());
176 }
177
178 /** Returns the sub-query-string for VALUES clause.
179 * @return The requested query string.
180 */
181 String getValuesQueryString() {
182 String rtn = " VALUES (";
183 for (int i = 0; i < fromList.size(); i++) {
184 if (i != 0) {
185 rtn += ", ";
186 }
187 rtn += "?";
188 }
189 rtn += ")";
190 return rtn;
191 }
192
193 /** Returns the complete query string.
194 * @throws DbException If something goes wrong.
195 * @return The complete query string.
196 */
197 String getQueryString() throws DbException {
198 String rtn = "INSERT INTO " + table.getFullTableName() + " (";
199 int i = 0;
200 Iterator fieldi = intoList.iterator();
201 while (fieldi.hasNext()) {
202 DbColumn col = (DbColumn) fieldi.next();
203 if (i != 0) {
204 rtn += ", ";
205 }
206 rtn += col.getName();
207 i++;
208 }
209 rtn += ") ";
210 if (selector == null) {
211 rtn += getValuesQueryString();
212 } else {
213 rtn += selector.getQueryString();
214 }
215 FileLog.singleton().debug("DbSelector", rtn);
216 return rtn;
217 }
218
219 }