skrueger/db/PGUtil.java

package skrueger.db;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.log4j.Logger;
import org.opengis.referencing.crs.CoordinateReferenceSystem;

import schmitzm.lang.LangUtil;

/**
 * Hilfsmethoden für PostgreSQL
 */
public class PGUtil {
        static final Logger log = Logger.getLogger(PGUtil.class);

        /**
         * Querys the <code>geometry_columns</code> table is part of every postgis
         * installation and must/should describe the geometry columns and tables.
         */
        static public String[] getColumnsDescribedInGeometryColumnsTable(Statement s) {
                String[] columns = new String[0];
                try {
                        ResultSet askGeoe = s
                                        .executeQuery("SELECT f_table_name FROM geometry_columns;");
                        while (askGeoe.next()) {
                                columns = LangUtil.extendArray(columns, askGeoe.getString(1));
                        }
                } catch (SQLException e) {
                        log.error(e, e);
                }

                return columns;

        }

        public static int getSridForCRS(CoordinateReferenceSystem crs) {
                String full = crs.toWKT().toUpperCase(); // ...
                // AUTHORITY["EPSG","900913"]]
                String s1 = "AUTHORITY[\"EPSG\",\"";
                int p1 = full.indexOf(s1);
                if (p1 >= 0) {
                        String s = full.substring(p1 + s1.length());
                        int p2 = s.indexOf("\"]");
                        if (p2 > 0) {
                                s = s.substring(0, p2);
                                return Integer.parseInt(s);
                        }
                }
                return -1;
        }

        public static void createOrUpdateGeometrsColumnsEntry(String tableName,
                        String geoColumnName, CoordinateReferenceSystem crs) {
                createOrUpdateGeometrsColumnsEntry(tableName, geoColumnName,
                                getSridForCRS(crs));
        }

        /**
         * Creates or Updates
         * 
         * @param tableName
         * @param geoColumnName
         * @param srid
         */
        public static void createOrUpdateGeometrsColumnsEntry(String tableName,
                        String geoColumnName, int srid) {

                String createGeometryEntrySQL = "INSERT INTO geometry_columns (f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, \"type\") VALUES ( '', 'public', '"
                                + tableName
                                + "', '"
                                + geoColumnName
                                + "', 2, "
                                + srid
                                + ",'MULTIPOLYGON' ) LIMIT 1;";
        }

        // TODO isGeoColumn();

        /**
         * @param binding
         *            Eine "einfache" Javaklasse, die in PG abgebildet werden soll.
         * @return einen PG-spezifischen Datentypenamen für einen Javatyp zurück,
         *         z.b. "double precision" für <code>Double.class</code>
         */
        public static String getColTypeName(Class<?> binding) {

                if (binding.isAssignableFrom(Double.class)) {
                        return "double precision"; // eg 'number' at oracle
                } else if (binding.isAssignableFrom(String.class)) {
                        return "text";
                } else if (binding.isAssignableFrom(Integer.class)) {
                        return "integer";
                } else if (binding.isAssignableFrom(Long.class)) {
                        return "bigint";
                }

                throw new RuntimeException("DB Type mapping for " + binding
                                + " not yet implemented.");
        }

}
1	alfonx	778	package skrueger.db;
2
3	alfonx	1195	import java.sql.ResultSet;
4			import java.sql.SQLException;
5			import java.sql.Statement;
6
7			import org.apache.log4j.Logger;
8			import org.opengis.referencing.crs.CoordinateReferenceSystem;
9
10			import schmitzm.lang.LangUtil;
11
12	alfonx	778	/**
13			* Hilfsmethoden für PostgreSQL
14			*/
15			public class PGUtil {
16	alfonx	1195	static final Logger log = Logger.getLogger(PGUtil.class);
17	alfonx	778
18			/**
19	alfonx	1195	* Querys the <code>geometry_columns</code> table is part of every postgis
20			* installation and must/should describe the geometry columns and tables.
21			*/
22			static public String[] getColumnsDescribedInGeometryColumnsTable(Statement s) {
23			String[] columns = new String[0];
24			try {
25			ResultSet askGeoe = s
26			.executeQuery("SELECT f_table_name FROM geometry_columns;");
27			while (askGeoe.next()) {
28			columns = LangUtil.extendArray(columns, askGeoe.getString(1));
29			}
30			} catch (SQLException e) {
31			log.error(e, e);
32			}
33
34			return columns;
35
36			}
37
38			public static int getSridForCRS(CoordinateReferenceSystem crs) {
39			String full = crs.toWKT().toUpperCase(); // ...
40			// AUTHORITY["EPSG","900913"]]
41			String s1 = "AUTHORITY[\"EPSG\",\"";
42			int p1 = full.indexOf(s1);
43			if (p1 >= 0) {
44			String s = full.substring(p1 + s1.length());
45			int p2 = s.indexOf("\"]");
46			if (p2 > 0) {
47			s = s.substring(0, p2);
48			return Integer.parseInt(s);
49			}
50			}
51			return -1;
52			}
53
54			public static void createOrUpdateGeometrsColumnsEntry(String tableName,
55			String geoColumnName, CoordinateReferenceSystem crs) {
56			createOrUpdateGeometrsColumnsEntry(tableName, geoColumnName,
57			getSridForCRS(crs));
58			}
59
60			/**
61			* Creates or Updates
62			*
63			* @param tableName
64			* @param geoColumnName
65			* @param srid
66			*/
67			public static void createOrUpdateGeometrsColumnsEntry(String tableName,
68			String geoColumnName, int srid) {
69
70			String createGeometryEntrySQL = "INSERT INTO geometry_columns (f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, \"type\") VALUES ( '', 'public', '"
71			+ tableName
72			+ "', '"
73			+ geoColumnName
74			+ "', 2, "
75			+ srid
76			+ ",'MULTIPOLYGON' ) LIMIT 1;";
77			}
78
79			// TODO isGeoColumn();
80
81			/**
82	alfonx	778	* @param binding
83			* Eine "einfache" Javaklasse, die in PG abgebildet werden soll.
84	alfonx	1195	* @return einen PG-spezifischen Datentypenamen für einen Javatyp zurück,
85			* z.b. "double precision" für <code>Double.class</code>
86	alfonx	778	*/
87			public static String getColTypeName(Class<?> binding) {
88
89			if (binding.isAssignableFrom(Double.class)) {
90			return "double precision"; // eg 'number' at oracle
91			} else if (binding.isAssignableFrom(String.class)) {
92			return "text";
93			} else if (binding.isAssignableFrom(Integer.class)) {
94			return "integer";
95			} else if (binding.isAssignableFrom(Long.class)) {
96			return "bigint";
97			}
98
99			throw new RuntimeException("DB Type mapping for " + binding
100			+ " not yet implemented.");
101			}
102
103			}
Name	Value
svn:eol-style	native
svn:keywords	Id URL
svn:mime-type	text/plain