KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > opencrx > kernel > tools > CopyDb


1 /*
2  * ====================================================================
3  * Project: opencrx, http://www.opencrx.org/
4  * Name: $Id: CopyDb.java,v 1.3 2006/01/20 10:54:56 wfro Exp $
5  * Description: Convert database RID columns from numeric to string format
6  * Revision: $Revision: 1.3 $
7  * Owner: CRIXP AG, Switzerland, http://www.crixp.com
8  * Date: $Date: 2006/01/20 10:54:56 $
9  * ====================================================================
10  *
11  * This software is published under the BSD license
12  * as listed below.
13  *
14  * Copyright (c) 2004-2005, CRIXP Corp., Switzerland
15  * All rights reserved.
16  *
17  * Redistribution and use in source and binary forms, with or without
18  * modification, are permitted provided that the following conditions
19  * are met:
20  *
21  * * Redistributions of source code must retain the above copyright
22  * notice, this list of conditions and the following disclaimer.
23  *
24  * * Redistributions in binary form must reproduce the above copyright
25  * notice, this list of conditions and the following disclaimer in
26  * the documentation and/or other materials provided with the
27  * distribution.
28  *
29  * * Neither the name of CRIXP Corp. nor the names of the contributors
30  * to openCRX may be used to endorse or promote products derived
31  * from this software without specific prior written permission
32  *
33  *
34  * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND
35  * CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES,
36  * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
37  * MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
38  * DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS
39  * BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
40  * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED
41  * TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
42  * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON
43  * ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
44  * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY
45  * OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
46  * POSSIBILITY OF SUCH DAMAGE.
47  *
48  * ------------------
49  *
50  * This product includes software developed by the Apache Software
51  * Foundation (http://www.apache.org/).
52  *
53  * This product includes software developed by contributors to
54  * openMDX (http://www.openmdx.org/)
55  */

56 package org.opencrx.kernel.tools;
57
58 import java.sql.Connection JavaDoc;
59 import java.sql.DriverManager JavaDoc;
60 import java.sql.PreparedStatement JavaDoc;
61 import java.sql.ResultSet JavaDoc;
62 import java.sql.ResultSetMetaData JavaDoc;
63 import java.sql.SQLException JavaDoc;
64 import java.util.ArrayList JavaDoc;
65 import java.util.HashSet JavaDoc;
66 import java.util.List JavaDoc;
67 import java.util.Properties JavaDoc;
68 import java.util.Set JavaDoc;
69
70 import javax.naming.Context JavaDoc;
71 import javax.naming.InitialContext JavaDoc;
72 import javax.naming.NamingException JavaDoc;
73
74 import org.openmdx.compatibility.base.collection.OffsetArrayList;
75 import org.openmdx.compatibility.base.collection.SparseList;
76 import org.openmdx.compatibility.base.dataprovider.layer.persistence.jdbc.Database_1Jdbc2;
77
78 public class CopyDb {
79
80     //---------------------------------------------------------------------------
81
private static void copyDbObject(
82         String JavaDoc dbObject,
83         Connection JavaDoc connSource,
84         Connection JavaDoc connTarget
85     ) throws SQLException JavaDoc {
86
87         String JavaDoc currentStatement = null;
88                     
89         Database_1Jdbc2 db = new Database_1Jdbc2();
90         
91         try {
92             // Delete all rows from target
93
PreparedStatement JavaDoc s = connTarget.prepareStatement(
94                 currentStatement = "DELETE FROM " + dbObject
95             );
96             s.executeUpdate();
97             s.close();
98             
99             // Read all rows from source
100
s = connSource.prepareStatement(
101                 currentStatement = "SELECT * FROM " + dbObject
102             );
103             s.executeQuery();
104             ResultSet JavaDoc rs = s.getResultSet();
105             ResultSetMetaData JavaDoc rsm = rs.getMetaData();
106             FastResultSet frs = new FastResultSet(rs);
107             int nRows = 0;
108             
109             while(frs.next()) {
110                 // Read row from source and prepare INSERT statement
111
String JavaDoc statement = "INSERT INTO " + dbObject;
112                 List JavaDoc statementParameters = new ArrayList JavaDoc();
113                 List JavaDoc columnNames = new ArrayList JavaDoc();
114                 for(
115                     int j = 0;
116                     j < rsm.getColumnCount();
117                     j++
118                 ) {
119                     String JavaDoc columnName = rsm.getColumnName(j+1);
120                     if(frs.getObject(columnName) != null) {
121                         statement += (statementParameters.size() == 0 ? " (" : ", ") + columnName;
122                         columnNames.add(columnName);
123                         statementParameters.add(
124                             frs.getObject(columnName)
125                         );
126                     }
127                 }
128                 statement += ") VALUES (";
129                 for(int j = 0; j < statementParameters.size(); j++) {
130                     statement += j == 0 ? "?" : ", ?";
131                 }
132                 statement += ")";
133                 
134                 // Add row to target
135
try {
136                     PreparedStatement JavaDoc t = connTarget.prepareStatement(
137                         currentStatement = statement
138                     );
139                     for(int j = 0; j < statementParameters.size(); j++) {
140                         db.setPreparedStatementValue(
141                             connTarget,
142                             t,
143                             j+1,
144                             statementParameters.get(j)
145                         );
146                     }
147                     t.executeUpdate();
148                     t.close();
149                 }
150                 catch(Exception JavaDoc e) {
151                     System.out.println("Insert failed. Reason: " + e.getMessage());
152                     System.out.println("statement=" + statement);
153                     System.out.println("parameters=" + statementParameters);
154                 }
155                 nRows++;
156                 if(nRows % 1000 == 0) {
157                     System.out.println(nRows + " rows copied");
158                 }
159             }
160             rs.close();
161             s.close();
162         }
163         catch (SQLException JavaDoc e) {
164             e.printStackTrace();
165             System.out.println("statement: " + currentStatement);
166         }
167     }
168     
169     //---------------------------------------------------------------------------
170
private static void copyNamespace(
171         Connection JavaDoc connSource,
172         Connection JavaDoc connTarget,
173         String JavaDoc namespaceId,
174         int startFromDbObject
175     ) {
176         String JavaDoc currentStatement = null;
177         try {
178             System.out.println();
179             System.out.println("Copying namespace " + namespaceId);
180             
181             System.out.println();
182             System.out.println("Reading configuration from source");
183
184             // dbObject
185
SparseList dbObjects = new OffsetArrayList();
186             
187             PreparedStatement JavaDoc ps = connSource.prepareStatement(
188                 currentStatement = "SELECT * FROM prefs_Preference WHERE (object_rid = ?) AND (object_oid = ?) ORDER BY object_idx"
189             );
190             ps.setString(1, "preference/" + namespaceId);
191             ps.setString(2, "PERSISTENCE:dbObject");
192             ResultSet JavaDoc rs = ps.executeQuery();
193             while(rs.next()) {
194                 dbObjects.set(
195                     rs.getInt("object_idx"),
196                     rs.getString("string_value")
197                 );
198             }
199             rs.close();
200             ps.close();
201                         
202             Set JavaDoc processedDbObjects = new HashSet JavaDoc();
203             for(
204                 int i = startFromDbObject;
205                 i < dbObjects.size();
206                 i++
207             ) {
208                 String JavaDoc dbObject = (String JavaDoc)dbObjects.get(i);
209                 if((dbObject != null) && (dbObject.length() > 0) && !processedDbObjects.contains(dbObject)) {
210                     System.out.println("Copying table " + i + " (" + dbObject + ")");
211                     copyDbObject(
212                         dbObject,
213                         connSource,
214                         connTarget
215                     );
216                     processedDbObjects.add(dbObject);
217                 }
218             }
219             System.out.println("INFO: Done");
220         }
221         catch (SQLException JavaDoc e) {
222             e.printStackTrace();
223             System.out.println("statement: " + currentStatement);
224         }
225     }
226
227    //-----------------------------------------------------------------------
228
public static void main(
229         String JavaDoc[] args
230     ) {
231         try {
232             Context JavaDoc componentEnvironment = (Context JavaDoc)new InitialContext JavaDoc().lookup("java:comp/env");
233
234             // Source connection
235
Class.forName((String JavaDoc)componentEnvironment.lookup("jdbcDriverSource"));
236             Properties JavaDoc props = new Properties JavaDoc();
237             props.put("user", componentEnvironment.lookup("usernameSource"));
238             props.put("password", componentEnvironment.lookup("passwordSource"));
239             Connection JavaDoc connSource = DriverManager.getConnection(
240                 (String JavaDoc)componentEnvironment.lookup("jdbcUrlSource"),
241                 props
242             );
243             connSource.setAutoCommit(true);
244
245             // Target connection
246
Class.forName((String JavaDoc)componentEnvironment.lookup("jdbcDriverTarget"));
247             props = new Properties JavaDoc();
248             props.put("user", componentEnvironment.lookup("usernameTarget"));
249             props.put("password", componentEnvironment.lookup("passwordTarget"));
250             Connection JavaDoc connTarget = DriverManager.getConnection(
251                 (String JavaDoc)componentEnvironment.lookup("jdbcUrlTarget"),
252                 props
253             );
254             connTarget.setAutoCommit(true);
255                         
256             copyDbObject(
257                 "prefs_Preference",
258                 connSource,
259                 connTarget
260             );
261             // Namespace kernel
262
Number JavaDoc startFromDbObject = (Number JavaDoc)componentEnvironment.lookup("kernel.startFromDbObject");
263             copyNamespace(
264                 connSource,
265                 connTarget,
266                 "kernel",
267                 startFromDbObject == null ? 0 : startFromDbObject.intValue()
268             );
269             // Namespace security
270
startFromDbObject = (Number JavaDoc)componentEnvironment.lookup("security.startFromDbObject");
271             copyNamespace(
272                 connSource,
273                 connTarget,
274                 "security",
275                 startFromDbObject == null ? 0 : startFromDbObject.intValue()
276             );
277         }
278         catch(NamingException JavaDoc e) {
279             e.printStackTrace();
280         }
281         catch (ClassNotFoundException JavaDoc e) {
282             e.printStackTrace();
283         }
284         catch (SQLException JavaDoc e) {
285             e.printStackTrace();
286         }
287     }
288     
289 }
290
291 //---------------------------------------------------------------------------
292
Popular Tags