MySQL作为空间数据库

 

最近做的一个项目需要使用空间数据库，而原先的数据库是MySQL，所以花了点时间小小学习了下。

1、  需要准备的jar包有：

antlr-2.7.7.jar

asm.jar

asm-attrs.jar

cglib-nodep-2.1_3.jar

commons-collections-2.1.1.jar

commons-logging-1.1.1.jar

dom4j-1.6.jar

hibernate3.jar

hibernate-spatial-1.0-M2.jar

hibernate-spatial-mysql-1.0-M2.jar

hibernate-spatial-postgis-1.0.M1.jar

jta.jar

jts-1.8.jar

log4j-1.2.9.jar

mysql.jar

postgresql-8.2-504.jdbc3.jar

2、  写实体类：

Event.java

package com.chainless.postgreSql; import java.util.Date; import com.vividsolutions.jts.geom.Coordinate; import com.vividsolutions.jts.geom.Point; public class Event { private Long id; private String title; private Date date; private Point location; private Coordinate coordinate; public Coordinate getCoordinate() { return coordinate; } public void setCoordinate(Coordinate coordinate) { this.coordinate = coordinate; } public Event() { } public Long getId() { return id; } public void setId(Long id) { this.id = id; } public Date getDate() { return date; } public void setDate(Date date) { this.date = date; } public String getTitle() { return title; } public void setTitle(String title) { this.title = title; } public Point getLocation() { return this.location; } public void setLocation(Point location) { this.location = location; } }   

3、  映射文件：

Event.hbm.xml

<?xml version="1.0" encoding="utf-8"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"> <hibernate-mapping> <class name="com.chainless.postgreSql.Event" table="events"> <id name="id" type="java.lang.Long"> <column name="EVENT_ID" /> <generator class="native" /> </id> <property name="date" type="java.util.Date" column="EVENT_DATE"/> <property name="title" type="java.lang.String" column="EVENT_TITLE"/> <property name="location" type="org.hibernatespatial.mysql.MySQLGeometryUserType" column="EVENT_LOC"/> <property name="coordinate" column="EVENT_COO"/> </class> </hibernate-mapping>

4、  Hibernate配置文件：

hibernate.cfg.xml(采用平常的MySQL配置)

<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"> <hibernate-configuration> <session-factory> <!-- Database connection settings --> <property name="connection.driver_class">com.mysql.jdbc.Driver</property> <property name="connection.url">jdbc:mysql://localhost:3306/events</property> <property name="connection.username">root</property> <property name="connection.password">root</property> <property name="connection.pool_size">1</property> <property name="dialect">org.hibernatespatial.mysql.MySQLSpatialDialect</property> <!-- Enable Hibernate's automatic session context management --> <property name="current_session_context_class">thread</property> <!-- Disable the second-level cache --> <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property> <!-- Echo all executed SQL to stdout --> <property name="show_sql">true</property> <!-- Drop and re-create the database schema on startup --> <property name="hbm2ddl.auto">create</property> <!-- mapping --> <mapping resource="com/chainless/postgreSql/Event.hbm.xml" /> </session-factory> </hibernate-configuration>

5、  测试类：

Test.java

package com.chainless.postgreSql; import java.util.Date; import java.util.List; import org.hibernate.Session; import org.hibernate.Transaction; import com.vividsolutions.jts.geom.Coordinate; import com.vividsolutions.jts.geom.Geometry; import com.vividsolutions.jts.geom.Point; import com.vividsolutions.jts.io.ParseException; import com.vividsolutions.jts.io.WKTReader; public class Test { public static void main(String[] args) { Test test = new Test(); for(int i=5; i<51; i=i+5){ test.createAndStoreEvent("My Event_" + i, new Date(), "POINT(" + i*10 + " " + i*20 + ")", new Coordinate(i*10, i*20, i*30)); } List<Event> list = test.readEvent(); for(Event event : list){ System.out.println(event.getTitle() + "/tPoint: " + event.getLocation().getX() + "/t" + event.getLocation().getY() + "/tCoordinate:/t" + event.getCoordinate().x + "/t" + event.getCoordinate().y + "/t" + event.getCoordinate().z); } } private void createAndStoreEvent(String title, Date theDate, String wktPoint, Coordinate coordinate) { // First interpret the WKT string to a point WKTReader fromText = new WKTReader(); Geometry geom = null; try { geom = fromText.read(wktPoint); } catch (ParseException e) { throw new RuntimeException("Not a WKT string:" + wktPoint); } if (!geom.getGeometryType().equals("Point")) { throw new RuntimeException("Geometry must be a point. Got a " + geom.getGeometryType()); } Session session = HbnUtil.getSession(); Transaction tran = session.beginTransaction(); Event theEvent = new Event(); theEvent.setTitle(title); theEvent.setDate(theDate); theEvent.setLocation((Point) geom); theEvent.setCoordinate(coordinate); session.save(theEvent); tran.commit(); HbnUtil.closeSession(); } @SuppressWarnings("unchecked") private List<Event> readEvent(){ Session session = HbnUtil.getSession(); Transaction tran = session.beginTransaction(); String hql = "from Event"; List<Event> eventList = (List<Event>) session.createQuery(hql).list(); tran.commit(); HbnUtil.closeSession(); return eventList; } }

6、  工具类：

HbnUtil.java

package com.chainless.postgreSql; import org.hibernate.HibernateException; import org.hibernate.Session; import org.hibernate.SessionFactory; import org.hibernate.cfg.Configuration; public class HbnUtil { private static SessionFactory sf; private static ThreadLocal<Session> threadlocal = new ThreadLocal<Session>(); static { Configuration config = null; try { config = new Configuration().configure(); sf = config.buildSessionFactory(); } catch (Exception e) { e.printStackTrace(); } } public static Session getSession() { Session s = (Session) threadlocal.get(); if (s == null) { s = sf.openSession(); threadlocal.set(s); } return s; } public static void closeSession() { Session s = (Session) threadlocal.get(); if (s != null) { try { s.close(); } catch (HibernateException e) { e.printStackTrace(); } threadlocal.set(null); } } }