|
1
56 package org.opencrx.kernel.tools;
57
58 import java.sql.Connection  ;
59 import java.sql.DriverManager ;
60 import java.sql.PreparedStatement ;
61 import java.sql.ResultSet ;
62 import java.sql.ResultSetMetaData ;
63 import java.sql.SQLException ;
64 import java.util.ArrayList ;
65 import java.util.HashSet ;
66 import java.util.List ;
67 import java.util.Properties ;
68 import java.util.Set ;
69
70 import javax.naming.Context ;
71 import javax.naming.InitialContext ;
72 import javax.naming.NamingException ;
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 private static void copyDbObject(
82 String dbObject,
83 Connection connSource,
84 Connection connTarget
85 ) throws SQLException {
86
87 String currentStatement = null;
88
89 Database_1Jdbc2 db = new Database_1Jdbc2();
90
91 try {
92 PreparedStatement s = connTarget.prepareStatement(
94 currentStatement = "DELETE FROM " + dbObject
95 );
96 s.executeUpdate();
97 s.close();
98
99 s = connSource.prepareStatement(
101 currentStatement = "SELECT * FROM " + dbObject
102 );
103 s.executeQuery();
104 ResultSet rs = s.getResultSet();
105 ResultSetMetaData rsm = rs.getMetaData();
106 FastResultSet frs = new FastResultSet(rs);
107 int nRows = 0;
108
109 while(frs.next()) {
110 String statement = "INSERT INTO " + dbObject;
112 List statementParameters = new ArrayList ();
113 List columnNames = new ArrayList ();
114 for(
115 int j = 0;
116 j < rsm.getColumnCount();
117 j++
118 ) {
119 String 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 try {
136 PreparedStatement 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 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 e) {
164 e.printStackTrace();
165 System.out.println("statement: " + currentStatement);
166 }
167 }
168
169 private static void copyNamespace(
171 Connection connSource,
172 Connection connTarget,
173 String namespaceId,
174 int startFromDbObject
175 ) {
176 String 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 SparseList dbObjects = new OffsetArrayList();
186
187 PreparedStatement 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 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 processedDbObjects = new HashSet ();
203 for(
204 int i = startFromDbObject;
205 i < dbObjects.size();
206 i++
207 ) {
208 String dbObject = (String )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 e) {
222 e.printStackTrace();
223 System.out.println("statement: " + currentStatement);
224 }
225 }
226
227 public static void main(
229 String [] args
230 ) {
231 try {
232 Context componentEnvironment = (Context )new InitialContext ().lookup("java:comp/env");
233
234 Class.forName((String )componentEnvironment.lookup("jdbcDriverSource"));
236 Properties props = new Properties ();
237 props.put("user", componentEnvironment.lookup("usernameSource"));
238 props.put("password", componentEnvironment.lookup("passwordSource"));
239 Connection connSource = DriverManager.getConnection(
240 (String )componentEnvironment.lookup("jdbcUrlSource"),
241 props
242 );
243 connSource.setAutoCommit(true);
244
245 Class.forName((String )componentEnvironment.lookup("jdbcDriverTarget"));
247 props = new Properties ();
248 props.put("user", componentEnvironment.lookup("usernameTarget"));
249 props.put("password", componentEnvironment.lookup("passwordTarget"));
250 Connection connTarget = DriverManager.getConnection(
251 (String )componentEnvironment.lookup("jdbcUrlTarget"),
252 props
253 );
254 connTarget.setAutoCommit(true);
255
256 copyDbObject(
257 "prefs_Preference",
258 connSource,
259 connTarget
260 );
261 Number startFromDbObject = (Number )componentEnvironment.lookup("kernel.startFromDbObject");
263 copyNamespace(
264 connSource,
265 connTarget,
266 "kernel",
267 startFromDbObject == null ? 0 : startFromDbObject.intValue()
268 );
269 startFromDbObject = (Number )componentEnvironment.lookup("security.startFromDbObject");
271 copyNamespace(
272 connSource,
273 connTarget,
274 "security",
275 startFromDbObject == null ? 0 : startFromDbObject.intValue()
276 );
277 }
278 catch(NamingException e) {
279 e.printStackTrace();
280 }
281 catch (ClassNotFoundException e) {
282 e.printStackTrace();
283 }
284 catch (SQLException e) {
285 e.printStackTrace();
286 }
287 }
288
289 }
290
291 |
Popular Tags |
Java API By Example, From Geeks To Geeks.