MySQLConnectionFactory connectionFactory = new MySQLConnectionFactory("mysqldb.geotools.org", "3306", "myCoolSchema");ConnectionPool connectionPool = connectionFactory.getConnectionPool("omcnoleg", "myTrickyPassword123");DataStore dataStore = new MySQLDataStore(connectionPool);username
* @return a MySQLDataStore for the specified parameters
*/
public static MySQLDataStore getInstance(String host, String schema,
String username, String password) throws IOException, SQLException {
return getInstance(host, 3306, schema, username, password);
}
/**
* Utility method for creating a MySQLDataStore from database connection
* parameters.
*
* @param host
* the host name or IP address of the database server
* @param port
* the port number of the database
* @param schema
* the name of the database instance
* @param username
* the database username
* @param password
* the password corresponding to username
* @throws IOException
* if the MySQLDataStore cannot be created because the database
* cannot be properly accessed
* @throws SQLException
* if a MySQL connection pool cannot be established
*/
public static MySQLDataStore getInstance(String host, int port,
String schema, String username, String password)
throws IOException, SQLException {
return new MySQLDataStore(
new MySQLConnectionFactory(host, port, schema)
.getConnectionPool(username, password));
}
/**
* Utility method for getting a FeatureWriter for modifying existing
* features, using no feature filtering and auto-committing. Not used for
* adding new features.
*
* @param typeName
* the feature type name (the table name)
* @return a FeatureWriter for modifying existing features
* @throws IOException
* if the database cannot be properly accessed
*/
public FeatureWriter getFeatureWriter(String typeName) throws IOException {
return getFeatureWriter(typeName, Filter.INCLUDE,
Transaction.AUTO_COMMIT);
}
/**
* Utility method for getting a FeatureWriter for adding new features, using
* auto-committing. Not used for modifying existing features.
*
* @param typeName
* the feature type name (the table name)
* @return a FeatureWriter for adding new features
* @throws IOException
* if the database cannot be properly accessed
*/
public FeatureWriter getFeatureWriterAppend(String typeName)
throws IOException {
return getFeatureWriterAppend(typeName, Transaction.AUTO_COMMIT);
}
/**
* Constructs an AttributeType from a row in a ResultSet. The ResultSet
* contains the information retrieved by a call to getColumns() on the
* DatabaseMetaData object. This information can be used to construct an
* Attribute Type.
*
* * In addition to standard SQL types, this method identifies MySQL 4.1's * geometric datatypes and creates attribute types accordingly. This happens * when the datatype, identified by column 5 of the ResultSet parameter, is * equal to java.sql.Types.OTHER. If a Types.OTHER ends up not being * geometric, this method simply calls the parent class's buildAttributeType * method to do something with it. *
* ** Note: Overriding methods must never move the current row pointer in the * result set. *
* * @param rs * The ResultSet containing the result of a * DatabaseMetaData.getColumns call. * * @return The AttributeType built from the ResultSet. * * @throws SQLException * If an error occurs processing the ResultSet. * @throws DataSourceException * Provided for overriding classes to wrap exceptions caused by * other operations they may perform to determine additional * types. This will only be thrown by the default implementation * if a type is present that is not present in the * TYPE_MAPPINGS. */ protected AttributeType buildAttributeType(ResultSet rs) throws IOException { final int COLUMN_NAME = 4; final int DATA_TYPE = 5; final int TYPE_NAME = 6; // typeNames[0]=geometry_columns // typeNames[1]=spatial_ref_sys try { int dataType = rs.getInt(DATA_TYPE); String tableName = getTypeNames()[2]; String geometryColumnName = rs.getString(COLUMN_NAME); int srid = determineSRID(tableName, geometryColumnName); CoordinateReferenceSystem crs = determineCRS(srid); if (dataType == Types.OTHER) { // this is MySQL-specific; handle it String typeName = rs.getString(TYPE_NAME); String typeNameLower = typeName.toLowerCase(); if ("geometry".equals(typeNameLower)) { if (crs != null) return AttributeTypeFactory.newAttributeType(rs .getString(COLUMN_NAME), Geometry.class, false, 0, null, crs); else return AttributeTypeFactory.newAttributeType(rs .getString(COLUMN_NAME), Geometry.class); } else if ("point".equals(typeNameLower)) { if (crs != null) return AttributeTypeFactory.newAttributeType(rs .getString(COLUMN_NAME), Point.class, false, 0, null, crs); else return AttributeTypeFactory.newAttributeType(rs .getString(COLUMN_NAME), Point.class); } else if ("linestring".equals(typeNameLower)) { return AttributeTypeFactory.newAttributeType(rs .getString(COLUMN_NAME), LineString.class); } else if ("polygon".equals(typeNameLower)) { if (crs != null) return AttributeTypeFactory.newAttributeType(rs .getString(COLUMN_NAME), Polygon.class, false, 0, null, crs); else return AttributeTypeFactory.newAttributeType(rs .getString(COLUMN_NAME), Polygon.class); } else if ("multipoint".equals(typeNameLower)) { if (crs != null) return AttributeTypeFactory.newAttributeType(rs .getString(COLUMN_NAME), MultiPoint.class, false, 0, null, crs); else return AttributeTypeFactory.newAttributeType(rs .getString(COLUMN_NAME), MultiPoint.class); } else if ("multilinestring".equals(typeNameLower)) { return AttributeTypeFactory.newAttributeType(rs .getString(COLUMN_NAME), MultiLineString.class); } else if ("multipolygon".equals(typeNameLower)) { if (crs != null) return AttributeTypeFactory.newAttributeType(rs .getString(COLUMN_NAME), MultiPolygon.class, false, 0, null, crs); else return AttributeTypeFactory.newAttributeType(rs .getString(COLUMN_NAME), MultiPolygon.class); } else if ("geometrycollection".equals(typeNameLower)) { if (crs != null) return AttributeTypeFactory.newAttributeType(rs .getString(COLUMN_NAME), GeometryCollection.class, false, 0, null, crs); else return AttributeTypeFactory.newAttributeType(rs .getString(COLUMN_NAME), GeometryCollection.class); } else { // nothing else we can do return super.buildAttributeType(rs); } } else { return super.buildAttributeType(rs); } } catch (SQLException e) { throw new IOException("SQL exception occurred: " + e.getMessage()); } } /** * @see org.geotools.data.jdbc.JDBCDataStore#getSqlBuilder(java.lang.String) */ public SQLBuilder getSqlBuilder(String typeName) throws IOException { // SQLEncoder encoder = new SQLEncoderMySQL(); replace with this once // it is fully tested, the test cases work, but I don't have a live // mysql database. -ch // SQLEncoder encoder = new SQLEncoder(); SQLEncoderMySQL encoder = new SQLEncoderMySQL(); encoder.setFIDMapper(getFIDMapper(typeName)); return new MySQLSQLBuilder(encoder, getSchema(typeName)); } /** * @see org.geotools.data.jdbc.JDBCDataStore#getGeometryAttributeIO(org.geotools.feature.AttributeType) */ protected AttributeIO getGeometryAttributeIO(AttributeType type, QueryData queryData) { return new WKTAttributeIO(); } protected JDBCFeatureWriter createFeatureWriter(FeatureReader reader, QueryData queryData) throws IOException { LOGGER.fine("returning jdbc feature writer"); return new MySQLFeatureWriter(reader, queryData); } /* * (non-Javadoc) * * @see org.geotools.data.jdbc.JDBC1DataStore#determineSRID(java.lang.String, * java.lang.String) */ protected int determineSRID(String tableName, String geometryColumnName) throws IOException { Connection conn = null; try { String sqlStatement = "SELECT SRID FROM GEOMETRY_COLUMNS " + "WHERE F_TABLE_NAME='" + tableName + "' AND F_GEOMETRY_COLUMN='" + geometryColumnName + "'"; conn = getConnection(Transaction.AUTO_COMMIT); LOGGER.finer("the sql statement for srid is " + sqlStatement); Statement statement = conn.createStatement(); ResultSet result = statement.executeQuery(sqlStatement); if (result.next()) { int retSrid = result.getInt("srid"); JDBCUtils.close(statement); return retSrid; } else { String mesg = "No geometry column row for srid in table: " + tableName + ", geometry column " + geometryColumnName + ", be sure column is defined in GEOMETRY_COLUMNS"; throw new DataSourceException(mesg); } } catch (SQLException sqle) { String message = sqle.getMessage(); throw new DataSourceException(message, sqle); } finally { JDBCUtils.close(conn, Transaction.AUTO_COMMIT, null); } } protected CoordinateReferenceSystem determineCRS(int srid) throws IOException { Connection conn = getConnection(Transaction.AUTO_COMMIT); String wkt = null; try { Statement st = conn.createStatement(); st.execute("select srtext from spatial_ref_sys where srid = " + srid); ResultSet set = st.getResultSet(); if (!set.next()) return null; wkt = set.getString(1); return CRS.parseWKT(wkt); } catch (FactoryException parse) { LOGGER.warning("Unabled to parse WKTEXT into a CRS:" + wkt); } catch (SQLException sql) { LOGGER.warning("No CRS for srid " + srid); } finally { JDBCUtils.close(conn, Transaction.AUTO_COMMIT, null); } return null; } }