|
1
19
20 package com.sslexplorer.jdbc;
21
22 import java.io.PrintWriter  ;
23 import java.sql.Connection ;
24 import java.sql.DatabaseMetaData ;
25 import java.sql.PreparedStatement ;
26 import java.sql.ResultSet ;
27 import java.sql.SQLException ;
28
29 import org.apache.commons.logging.Log;
30 import org.apache.commons.logging.LogFactory;
31
32
37
38 public class DBDumper {
39
40 final static Log log = LogFactory.getLog(DBDumper.class);
41
42
51 public void dumpToSQL(PrintWriter writer, JDBCConnectionImpl conx, char quoteChar) throws Exception {
52 dumpTable(writer, conx, quoteChar, null);
53 dumpData(writer, conx, quoteChar, null);
54 }
55
56
67 public void dumpTable(PrintWriter writer, JDBCConnectionImpl conx, char quoteChar, String [] tables) throws Exception {
68 Connection jdbcConnection = conx.getConnection();
69 DatabaseMetaData dbMetaData = jdbcConnection.getMetaData();
70
71 if (tables == null) {
72 ResultSet rs = dbMetaData.getTables(null, null, null, null);
73 try {
74 while (rs.next()) {
75 String tableName = rs.getString("TABLE_NAME");
76 String tableType = rs.getString("TABLE_TYPE");
77 if (tableType.equalsIgnoreCase("TABLE")) {
78 dumpTable(writer, conx, quoteChar, new String [] { tableName });
79 }
80 }
81 } finally {
82 rs.close();
83 }
84 } else {
85 for (int i = 0; i < tables.length; i++) {
86 String tableName = tables[i];
87 log.info("Dumping table creation for " + tableName);
88 writer.println("CREATE TABLE " + tableName + " (");
89 boolean first = true;
90
91 ResultSet rs2 = dbMetaData.getColumns(null, null, tableName, "%");
93 try {
94 while (rs2.next()) {
95 if (first) {
96 first = false;
97 } else {
98 writer.println(",");
99 }
100 String columnName = rs2.getString("COLUMN_NAME");
101 String columnType = rs2.getString("TYPE_NAME");
102 int columnSize = rs2.getInt("COLUMN_SIZE");
103 String nullable = rs2.getString("IS_NULLABLE");
104 String nullString = "NULL";
105 if ("NO".equalsIgnoreCase(nullable)) {
106 nullString = "NOT NULL";
107 }
108 writer.print(" " + columnName + " " + columnType);
109 if (columnSize != 0) {
110 if (columnType.equalsIgnoreCase("varchar") && columnSize > 255) {
111 columnSize = 255;
112 }
113 writer.print(" (" + columnSize + ")");
114 }
115 writer.print(" " + nullString);
116
117 }
118 } finally {
119 rs2.close();
120 }
121
122 try {
124 rs2 = dbMetaData.getPrimaryKeys(null, null, tableName);
125 String primaryKeyName = null;
126 StringBuffer primaryKeyColumns = new StringBuffer ();
127 while (rs2.next()) {
128 String thisKeyName = rs2.getString("PK_NAME");
129 if ((thisKeyName != null && primaryKeyName == null) || (thisKeyName == null && primaryKeyName != null)
130 || (thisKeyName != null && !thisKeyName.equals(primaryKeyName))
131 || (primaryKeyName != null && !primaryKeyName.equals(thisKeyName))) {
132 if (primaryKeyColumns.length() > 0) {
133 writer.print(",\n PRIMARY KEY ");
134 if (primaryKeyName != null) {
135 writer.print(primaryKeyName);
136 }
137 writer.print("(" + primaryKeyColumns.toString() + ")");
138 }
139 primaryKeyColumns = new StringBuffer ();
140 primaryKeyName = thisKeyName;
141 }
142 if (primaryKeyColumns.length() > 0) {
143 primaryKeyColumns.append(", ");
144 }
145 primaryKeyColumns.append(rs2.getString("COLUMN_NAME"));
146 }
147 if (primaryKeyColumns.length() > 0) {
148 writer.print(",\n PRIMARY KEY ");
149 if (primaryKeyName != null) {
150 writer.print(primaryKeyName);
151 }
152 writer.print(" (" + primaryKeyColumns.toString() + ")");
153 }
154 } finally {
155 rs2.close();
156 }
157 writer.println("\n);");
158 writer.println();
159 }
160 }
161 }
162
163
174 public void dumpData(PrintWriter writer, JDBCConnectionImpl conx, char quoteChar, String [] tables) throws Exception {
175 Connection jdbcConnection = conx.getConnection();
176 DatabaseMetaData dbMetaData = jdbcConnection.getMetaData();
177
178 if (tables == null) {
179 ResultSet rs = dbMetaData.getTables(null, null, null, null);
180 try {
181 while (rs.next()) {
182 String tableName = rs.getString("TABLE_NAME");
183 String tableType = rs.getString("TABLE_TYPE");
184 if (tableType.equalsIgnoreCase("TABLE")) {
185 dumpData(writer, conx, quoteChar, new String [] { tableName });
186 }
187 }
188 } finally {
189 rs.close();
190 }
191 } else {
192 for (int i = 0; i < tables.length; i++) {
193 String tableName = tables[i];
194 log.info("Dumping data for table " + tableName);
195 PreparedStatement stmt = jdbcConnection.prepareStatement("SELECT * FROM " + tableName);
197 try {
198 ResultSet rs2 = stmt.executeQuery();
199 try {
200 while (rs2.next()) {
201 dumpRow(writer, rs2);
202 }
203 } finally {
204 rs2.close();
205 }
206 } finally {
207 stmt.close();
208 }
209 writer.println();
210 }
211 }
212 }
213
214
221 public void dumpRow(PrintWriter writer, ResultSet resultSet) throws SQLException {
222 String tableName = resultSet.getMetaData().getTableName(1);
223 int columnCount = resultSet.getMetaData().getColumnCount();
224 writer.print("INSERT INTO " + tableName + " VALUES (");
225 for (int j = 0; j < columnCount; j++) {
226 if (j > 0) {
227 writer.print(", ");
228 }
229 Object value = resultSet.getObject(j + 1);
230 if (value == null) {
231 writer.print("NULL");
232 } else {
233 String outputValue = value.toString();
234 if (value instanceof Number ) {
235 writer.print(outputValue);
236 } else {
237
244 outputValue = outputValue.replaceAll("'", "''");
245 writer.print("'" + outputValue + "'");
246 }
247 }
248 }
249 writer.println(");");
250 }
251
252 }
253 |
Popular Tags |
Java API By Example, From Geeks To Geeks.