    001    //$HeadURL: https://svn.wald.intevation.org/svn/deegree/base/branches/2.4_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    }