001 //$HeadURL: https://svn.wald.intevation.org/svn/deegree/base/branches/2.3_testing/src/org/deegree/tools/shape/Shp2MySQL.java $
002 /*----------------------------------------------------------------------------
003 This file is part of deegree, http://deegree.org/
004 Copyright (C) 2001-2009 by:
005 Department of Geography, University of Bonn
006 and
007 lat/lon GmbH
008
009 This library is free software; you can redistribute it and/or modify it under
010 the terms of the GNU Lesser General Public License as published by the Free
011 Software Foundation; either version 2.1 of the License, or (at your option)
012 any later version.
013 This library is distributed in the hope that it will be useful, but WITHOUT
014 ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
015 FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
016 details.
017 You should have received a copy of the GNU Lesser General Public License
018 along with this library; if not, write to the Free Software Foundation, Inc.,
019 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
020
021 Contact information:
022
023 lat/lon GmbH
024 Aennchenstr. 19, 53177 Bonn
025 Germany
026 http://lat-lon.de/
027
028 Department of Geography, University of Bonn
029 Prof. Dr. Klaus Greve
030 Postfach 1147, 53001 Bonn
031 Germany
032 http://www.geographie.uni-bonn.de/deegree/
033
034 e-mail: info@deegree.org
035 ----------------------------------------------------------------------------*/
036 package org.deegree.tools.shape;
037
038 import java.sql.Connection;
039 import java.sql.PreparedStatement;
040 import java.util.ArrayList;
041 import java.util.HashMap;
042
043 import org.deegree.datatypes.QualifiedName;
044 import org.deegree.datatypes.Types;
045 import org.deegree.datatypes.UnknownTypeException;
046 import org.deegree.framework.log.ILogger;
047 import org.deegree.framework.log.LoggerFactory;
048 import org.deegree.framework.util.StringTools;
049 import org.deegree.io.DBConnectionPool;
050 import org.deegree.io.shpapi.ShapeFile;
051 import org.deegree.model.feature.Feature;
052 import org.deegree.model.feature.schema.FeatureType;
053 import org.deegree.model.feature.schema.PropertyType;
054 import org.deegree.model.spatialschema.Geometry;
055 import org.deegree.model.spatialschema.WKTAdapter;
056
057 /**
058 *
059 *
060 *
061 * @author <a href="mailto:poth@lat-lon.de">Andreas Poth</a>
062 * @author last edited by: $Author: mschneider $
063 *
064 * @version $Revision: 18195 $, $Date: 2009-06-18 17:55:39 +0200 (Do, 18. Jun 2009) $
065 */
066 public class Shp2MySQL {
067
068 private static final ILogger LOG = LoggerFactory.getLogger( Shp2MySQL.class );
069
070 private ArrayList<String> fileList = new ArrayList<String>();
071
072 private String driver;
073
074 private String url;
075
076 private String user;
077
078 private String password;
079
080 /**
081 * Creates a new Shp2MySQL object.
082 *
083 * @param file
084 * @param driver
085 * @param url
086 * @param user
087 * @param password
088 */
089 public Shp2MySQL( String file, String driver, String url, String user, String password ) {
090 fileList.add( file );
091 this.driver = driver;
092 this.url = url;
093 this.user = user;
094 this.password = password;
095 }
096
097 /**
098 * @throws Exception
099 */
100 public void run()
101 throws Exception {
102
103 for ( int i = 0; i < fileList.size(); i++ ) {
104
105 DBConnectionPool pool = DBConnectionPool.getInstance();
106 Connection conn = pool.acquireConnection( driver, url, user, password );
107 PreparedStatement stmt = conn.prepareStatement( "insert into sos_tab (id, value1,value2,value3) values (?,?,?,?)" );
108
109 ShapeFile sf = new ShapeFile( fileList.get( i ) );
110
111 // delete table if already exists
112 String tabName = sf.getFeatureByRecNo( 1 ).getFeatureType().getName().getLocalName();
113 try {
114 stmt.execute( "drop table " + tabName );
115 } catch ( Exception e ) {
116 System.out.println( "table " + tabName + " does not exist!" );
117 }
118
119 // get createtable sql statement and write it to the file
120 String createTable = getCreateTableStatement( sf.getFeatureByRecNo( 1 ).getFeatureType() );
121 stmt.execute( createTable );
122
123 // create an insert statement for each feature conained in
124 // the shapefile
125 for ( int j = 0; j < sf.getRecordNum(); j++ ) {
126 if ( j % 50 == 0 ) {
127 System.out.print( "." );
128 }
129
130 StringBuffer names = new StringBuffer( "(" );
131 StringBuffer values = new StringBuffer( " VALUES (" );
132
133 Feature feature = sf.getFeatureByRecNo( j + 1 );
134 FeatureType ft = feature.getFeatureType();
135 PropertyType ftp[] = ft.getProperties();
136 for ( int k = 0; k < ftp.length; k++ ) {
137 if ( ftp[k].getType() == Types.GEOMETRY ) {
138 values.append( "GeomFromText(?)" );
139 } else {
140 values.append( '?' );
141 }
142 QualifiedName name = ftp[k].getName();
143 names.append( name.getLocalName() );
144 if ( k < ftp.length - 1 ) {
145 names.append( "," );
146 values.append( "," );
147 }
148 }
149 names.append( ")" );
150 values.append( ")" );
151 LOG.logDebug( "Insert into " + tabName + " " + names + values );
152 stmt = conn.prepareStatement( "Insert into " + tabName + " " + names + values );
153 for ( int k = 0; k < ftp.length; k++ ) {
154 Object value = feature.getProperties( ftp[k].getName() )[0].getValue();
155 if ( ftp[k].getType() == Types.GEOMETRY ) {
156 value = WKTAdapter.export( (Geometry) value ).toString();
157 stmt.setObject( k + 1, value, Types.VARCHAR );
158 } else if ( ftp[k].getType() == Types.VARCHAR || ftp[k].getType() == Types.CHAR ) {
159 if ( value != null ) {
160 value = StringTools.replace( (String) value, "'", "\\'", true );
161 value = StringTools.replace( (String) value, "\"", "\\\"", true );
162 }
163 stmt.setObject( k + 1, value, Types.VARCHAR );
164 } else if ( ftp[k].getType() == Types.DOUBLE || ftp[k].getType() == Types.FLOAT ) {
165 if ( value != null ) {
166 value = Double.parseDouble( value.toString() );
167 }
168 stmt.setObject( k + 1, value, Types.DOUBLE );
169 } else if ( ftp[k].getType() == Types.INTEGER || ftp[k].getType() == Types.BIGINT ) {
170 if ( value != null ) {
171 value = Integer.parseInt( value.toString() );
172 }
173 stmt.setObject( k + 1, value, Types.INTEGER );
174 } else if ( ftp[k].getType() == Types.DATE ) {
175 stmt.setObject( k + 1, value, Types.DATE );
176 }
177
178 }
179 stmt.execute();
180 }
181 sf.close();
182 stmt.close();
183 pool.releaseConnection( conn, driver, url, user, password );
184 }
185
186 LOG.logInfo( "finished!" );
187
188 }
189
190 /**
191 * creates a create table sql statement from the passed <tt>FeatureType</tt>
192 *
193 * @param ft
194 * feature type
195 * @return the created SQL statement
196 */
197 private String getCreateTableStatement( FeatureType ft ) {
198
199 StringBuffer sb = new StringBuffer();
200 String name = ft.getName().getLocalName();
201
202 PropertyType[] ftp = ft.getProperties();
203
204 sb.append( "CREATE TABLE " ).append( name ).append( " (" );
205 for ( int i = 0; i < ftp.length; i++ ) {
206 sb.append( ftp[i].getName().getLocalName() ).append( " " );
207 int type = ftp[i].getType();
208 try {
209 LOG.logDebug( Types.getTypeNameForSQLTypeCode( type ) + " " + ftp[i].getName().getLocalName() );
210 } catch ( UnknownTypeException e ) {
211 // just for debugging purposes
212 }
213 if ( type == Types.VARCHAR ) {
214 sb.append( " VARCHAR(255) " );
215 } else if ( type == Types.DOUBLE || type == Types.FLOAT ) {
216 sb.append( " DOUBLE(20,8) " );
217 } else if ( type == Types.INTEGER || type == Types.BIGINT ) {
218 sb.append( " INT(12) " );
219 } else if ( type == Types.DATE ) {
220 sb.append( " Date " );
221 } else if ( type == Types.GEOMETRY || type == Types.POINT || type == Types.CURVE || type == Types.SURFACE
222 || type == Types.MULTIPOINT || type == Types.MULTICURVE || type == Types.MULTISURFACE ) {
223 sb.append( " GEOMETRY NOT NULL" );
224 }
225 if ( i < ftp.length - 1 ) {
226 sb.append( "," );
227 }
228 }
229 sb.append( ")" );
230 LOG.logDebug( "Create table statement: ", sb );
231 return sb.toString();
232 }
233
234 /**
235 * prints out helping application-information.
236 *
237 * @param n
238 * an integer parameter, which determines which help-information should be given out.
239 */
240 private static void usage( int n ) {
241 switch ( n ) {
242 case 0:
243 System.out.println( "usage: java -classpath .;deegree.jar de.tools.Shp2MySQL "
244 + "[-f shapefile -driver driver -url url -user user -password password \n"
245 + " -d sourcedirectory] [--version] [--help]\n\n arguments:\n"
246 + " -f shapefile reads the input shapefile. must be set\n"
247 + " if -d is not set.\n"
248 + " -d inputdir name of the directory that contains the.\n"
249 + " source shapefiles. must be set if -f is\n"
250 + " not set.\n"
251 + " -driver database driver class JDBC driver class name \n"
252 + " (default: com.mysql.jdbc.Driver) \n"
253 + " -url database connection URL for connecting target database. \n"
254 + " Example: jdbc:mysql://localhost:3306/deegree \n"
255 + " -user user database user name \n"
256 + " -password password database user's password \n\n\n" + "information options:\n"
257 + " --help shows this help.\n"
258 + " --version shows the version and exits.\n" );
259 break;
260 case 1:
261 System.out.println( "Try 'java -classpath .;deegree.jar de.tools.Shp2MySQL --help'\n"
262 + "for more information." );
263 break;
264
265 default:
266 System.out.println( "Unknown usage: \n" + "Try 'java -classpath .;deegree.jar de.tools.Shp2MySQL --help'\n"
267 + "for more information." );
268 break;
269 }
270 }
271
272 /**
273 * @param args
274 * the command line arguments
275 * @throws Exception
276 */
277 public static void main( String[] args )
278 throws Exception {
279
280 if ( args == null || args.length < 6 ) {
281 usage( 0 );
282 System.exit( 1 );
283 return;
284 }
285
286 try {
287 HashMap<String, String> map = new HashMap<String, String>();
288
289 for ( int i = 0; i < args.length; i += 2 ) {
290 map.put( args[i], args[i + 1] );
291 }
292
293 if ( map.get( "-url" ) == null ) {
294 usage( 0 );
295 System.exit( 0 );
296 }
297
298 if ( map.get( "-driver" ) == null ) {
299 map.put( "-driver", "com.mysql.jdbc.Driver" );
300 }
301
302 if ( map.get( "--help" ) != null ) {
303 usage( 0 );
304 System.exit( 0 );
305 }
306
307 if ( map.get( "--version" ) != null ) {
308 System.out.println( "Shp2MySQL version 1.0.0" );
309 System.exit( 0 );
310 }
311
312 // one single file shall be transformed
313 if ( map.get( "-f" ) != null ) {
314
315 String f = map.get( "-f" );
316 if ( f.toUpperCase().endsWith( ".SHP" ) ) {
317 f = f.substring( 0, f.length() - 4 );
318 }
319 Shp2MySQL shp = new Shp2MySQL( f, map.get( "-driver" ), map.get( "-url" ), map.get( "-user" ),
320 map.get( "-password" ) );
321 shp.run();
322 } else {
323 System.out.println( "option -d is not supported at the moment" );
324 // TODO
325 // the files of a whole directory shall be inserted
326 }
327 } catch ( Throwable e ) {
328 e.printStackTrace();
329 } finally {
330 System.exit( 0 );
331 }
332 }
333 }