KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > apache > derby > impl > load > Import


1 /*
2
3    Derby - Class org.apache.derby.impl.load.Import
4
5    Licensed to the Apache Software Foundation (ASF) under one or more
6    contributor license agreements. See the NOTICE file distributed with
7    this work for additional information regarding copyright ownership.
8    The ASF licenses this file to You under the Apache License, Version 2.0
9    (the "License"); you may not use this file except in compliance with
10    the License. You may obtain a copy of the License at
11
12       http://www.apache.org/licenses/LICENSE-2.0
13
14    Unless required by applicable law or agreed to in writing, software
15    distributed under the License is distributed on an "AS IS" BASIS,
16    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
17    See the License for the specific language governing permissions and
18    limitations under the License.
19
20  */
21
22 package org.apache.derby.impl.load;
23
24 import java.sql.ResultSet JavaDoc;
25 import java.sql.SQLException JavaDoc;
26 import java.sql.SQLWarning JavaDoc;
27 import java.sql.Statement JavaDoc;
28 import java.sql.PreparedStatement JavaDoc;
29 import java.sql.Connection JavaDoc;
30 import java.sql.ResultSetMetaData JavaDoc;
31 import java.sql.DatabaseMetaData JavaDoc;
32 import java.util.*;
33
34 /**
35  * This class implements import of data from a URL into a table.
36  * Import functions provided here in this class shouble be called through
37  * Systement Procedures. Import uses VTI , which is supprted only through
38  * Systemem procedures mechanism.
39  */
40
41 public class Import extends ImportAbstract{
42
43     private String JavaDoc inputFileName;
44
45     /**
46      * Constructior to Invoke Import from a select statement
47      * @param inputFileName The URL of the ASCII file from which import will happen
48      * @exception Exception on error
49      */
50     public Import(String JavaDoc inputFileName, String JavaDoc columnDelimiter,
51                       String JavaDoc characterDelimiter, String JavaDoc codeset,
52                       int noOfColumnsExpected) throws SQLException JavaDoc
53     {
54
55         try{
56             this.inputFileName = inputFileName;
57             this.noOfColumnsExpected = noOfColumnsExpected;
58             controlFileReader = new ControlInfo();
59             controlFileReader.setControlProperties(characterDelimiter,
60                                                    columnDelimiter, codeset);
61             doImport();
62
63         }catch(Exception JavaDoc e)
64         {
65             throw LoadError.unexpectedError(e);
66         }
67     }
68
69
70     private void doImport() throws Exception JavaDoc
71     {
72         if (inputFileName == null)
73             throw LoadError.dataFileNull();
74         doAllTheWork();
75
76     }
77
78     
79     /**
80      * SYSCS_IMPORT_TABLE system Procedure from ij or from a Java application
81      * invokes this method to perform import to a table from a file.
82      * @param connection The Cloudscape database connection URL for the database containing the table
83      * @param schemaName The name of the schema where table to import exists
84      * @param tableName Name of the Table the data has to be imported to.
85      * @param inputFileName Name of the file from which data has to be imported.
86      * @param columnDelimiter Delimiter that seperates columns in the file
87      * @param characterDelimiter Delimiter that is used to quiote non-numeric types
88      * @param codeset Codeset of the data in the file
89      * @param replace Indicates whether the data in table has to be replaced or
90      * appended.(0 - append , > 0 Replace the data)
91      * @exception SQL Exception on errors
92      */
93
94     public static void importTable(Connection JavaDoc connection, String JavaDoc schemaName,
95                               String JavaDoc tableName, String JavaDoc inputFileName,
96                               String JavaDoc columnDelimiter, String JavaDoc characterDelimiter,
97                               String JavaDoc codeset, short replace)
98         throws SQLException JavaDoc {
99
100
101         performImport(connection, schemaName, null, //No columnList
102 null , //No column indexes
103 tableName, inputFileName, columnDelimiter,
104                       characterDelimiter, codeset, replace);
105     }
106
107
108
109         
110     /**
111      * SYSCS_IMPORT_DATA system Procedure from ij or from a Java application
112      * invokes this method to perform import to a table from a file.
113      * @param connection The Cloudscape database connection URL for the database containing the table
114      * @param schemaName The name of the schema where table to import exists
115      * @param tableName Name of the Table the data has to be imported to.
116      * @param insertColumnList Comma Seperated column name list to which data
117      * has to be imported from file.eg: 'c2,c2,'c3'.
118      * @param columnIndexes Comma sepearted Lit Index of the columns in the file(first column
119                                  starts at 1). eg: '3 ,4 , 5'
120      * @param inputFileName Name of the file from which data has to be imported.
121      * @param columnDelimiter Delimiter that seperates columns in the file
122      * @param characterDelimiter Delimiter that is used to quiote non-numeric types
123      * @param codeset Codeset of the data in the file
124      * @param replace Indicates whether the data in table has to be replaced or
125      * appended.(0 - append , > 0 Replace the data)
126      * @exception SQL Exception on errors
127      */
128     public static void importData(Connection JavaDoc connection, String JavaDoc schemaName,
129                                   String JavaDoc tableName, String JavaDoc insertColumnList,
130                                   String JavaDoc columnIndexes,
131                                   String JavaDoc inputFileName, String JavaDoc columnDelimiter,
132                                   String JavaDoc characterDelimiter,
133                                   String JavaDoc codeset, short replace)
134         throws SQLException JavaDoc
135     {
136         
137
138             performImport(connection, schemaName, insertColumnList,columnIndexes,
139                           tableName, inputFileName, columnDelimiter,
140                           characterDelimiter, codeset, replace);
141     }
142
143
144     /*
145      * This function creates and executes SQL Insert statement that performs the
146      * the import using VTI.
147      * eg:
148      * insert into T1 select (cast column1 as DECIMAL), (cast column2 as
149      * INTEGER) from new org.apache.derby.impl.load.Import('extin/Tutor1.asc') as importvti;
150      *
151      */
152     private static void performImport(Connection JavaDoc connection, String JavaDoc schemaName,
153                                       String JavaDoc insertColumnList, String JavaDoc columnIndexes,
154                                       String JavaDoc tableName,
155                                       String JavaDoc inputFileName, String JavaDoc columnDelimiter,
156                                       String JavaDoc characterDelimiter, String JavaDoc codeset,
157                                       short replace)
158         throws SQLException JavaDoc
159     {
160
161         if (connection == null)
162             throw LoadError.connectionNull();
163         
164     
165         
166         if (tableName == null)
167             throw LoadError.entityNameMissing();
168         
169
170         ColumnInfo columnInfo = new ColumnInfo(connection , schemaName ,
171                                                tableName, insertColumnList,
172                                                columnIndexes, COLUMNNAMEPREFIX);
173
174         /* special handling of single quote delimiters
175          * Single quote should be writeen with an extra quote otherwise sql will
176          * throw syntac error.
177          * i.e to recognize a quote it has to be appended with extra quote ('')
178          */
179         if(characterDelimiter!=null && characterDelimiter.equals("'"))
180             characterDelimiter = "''";
181         if(columnDelimiter !=null && columnDelimiter.equals("'"))
182             columnDelimiter = "''";
183         
184
185         StringBuffer JavaDoc sb = new StringBuffer JavaDoc("new ");
186         sb.append("org.apache.derby.impl.load.Import");
187         sb.append("(") ;
188         sb.append( (inputFileName !=null ? "'" + inputFileName + "'" : null));
189         sb.append(",") ;
190         sb.append( (columnDelimiter !=null ? "'" + columnDelimiter + "'" : null));
191         sb.append(",") ;
192         sb.append( (characterDelimiter !=null ? "'" + characterDelimiter + "'" : null));
193         sb.append(",") ;
194         sb.append( (codeset !=null ? "'" + codeset + "'" : null));
195         sb.append(", ");
196         sb.append( columnInfo.getExpectedNumberOfColumnsInFile());
197         sb.append(" )") ;
198
199         String JavaDoc importvti = sb.toString();
200
201         // delimit the table and schema names with quotes.
202 // because they might have been created as quoted
203 // identifiers(for example when reserved words are used, names are quoted)
204
205         // Import procedures are to be called with case-senisitive names.
206 // Incase of delimited table names, they need to be passed as defined
207 // and when they are not delimited, they need to be passed in upper
208 // case, because all undelimited names are stored in the upper case
209 // in the database.
210
211         String JavaDoc entityName = (schemaName == null ? "\""+ tableName + "\"" :
212                              "\"" + schemaName + "\"" + "." + "\"" + tableName + "\"");
213
214         String JavaDoc insertModeValue;
215         if(replace > 0)
216             insertModeValue = "replace";
217         else
218             insertModeValue = "bulkInsert";
219
220         String JavaDoc cNamesWithCasts = columnInfo.getColumnNamesWithCasts();
221         String JavaDoc insertColumnNames = columnInfo.getInsertColumnNames();
222         if(insertColumnNames !=null)
223             insertColumnNames = "(" + insertColumnNames + ") " ;
224         else
225             insertColumnNames = "";
226         String JavaDoc insertSql = "INSERT INTO " + entityName + insertColumnNames +
227             " --DERBY-PROPERTIES insertMode=" + insertModeValue + "\n" +
228             " SELECT " + cNamesWithCasts + " from " +
229             importvti + " AS importvti" ;
230
231         //prepare the import statement to hit any errors before locking the table
232 PreparedStatement JavaDoc ips = connection.prepareStatement(insertSql);
233         
234         //lock the table before perfoming import, because there may
235 //huge number of lockes aquired that might have affect on performance
236 //and some possible dead lock scenarios.
237 Statement JavaDoc statement = connection.createStatement();
238         String JavaDoc lockSql = "LOCK TABLE " + entityName + " IN EXCLUSIVE MODE";
239         statement.executeUpdate(lockSql);
240
241         //execute the import operaton.
242 ips.executeUpdate();
243         statement.close();
244         ips.close();
245
246     }
247
248
249     /** virtual method from the abstract class
250      * @exception Exception on error
251      */
252     ImportReadData getImportReadData() throws Exception JavaDoc {
253         return new ImportReadData(inputFileName, controlFileReader);
254     }
255 }
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
Popular Tags