由于最新的geotools工具类不支持8.0+以上的MySQL数据库,故而开发该工具用于数据处理。采用javaFX开发的一个很简单的数据导入工具,对于5.0+的版本,大家参考网上的相关代码即可,也可在我代码的基础上稍加改造。
5.0+版本推荐路径:https://blog.csdn.net/weixin_40184249/article/details/88644932
工具页面如下,已上传资源,参见 shp2mysql.jar 包:
主要源码开源如下:
1, fxml界面布局文件
<?xml version="1.0" encoding="UTF-8"?> <?import java.net.*?> <?import javafx.geometry.*?> <?import javafx.scene.control.*?> <?import javafx.scene.layout.*?> <?import javafx.scene.text.*?> <GridPane fx:controller="cn.piesat.java.AppController" xmlns:fx="http://javafx.com/fxml" alignment="CENTER" hgap="10" vgap="10" styleClass="root"> <padding><Insets top="25" right="25" bottom="10" left="25" /></padding> <!-- 数据库配置信息 --> <Text id="dataSet" text="数据库配置" GridPane.columnIndex="0" GridPane.rowIndex="0" GridPane.columnSpan="3"/> <Label text="服务器:" GridPane.columnIndex="0" GridPane.rowIndex="1" /> <TextField fx:id="host" GridPane.columnIndex="1" GridPane.rowIndex="1" GridPane.columnSpan="4"/> <Label text="端口:" GridPane.columnIndex="0" GridPane.rowIndex="2" /> <TextField fx:id="port" GridPane.columnIndex="1" GridPane.rowIndex="2" GridPane.columnSpan="4"/> <Label text="数据库:" GridPane.columnIndex="0" GridPane.rowIndex="3" /> <TextField fx:id="dataBase" GridPane.columnIndex="1" GridPane.rowIndex="3" GridPane.columnSpan="4"/> <Label text="用户名:" GridPane.columnIndex="0" GridPane.rowIndex="4" /> <TextField fx:id="user" GridPane.columnIndex="1" GridPane.rowIndex="4" GridPane.columnSpan="4"/> <Label text="密码:" GridPane.columnIndex="0" GridPane.rowIndex="5" /> <TextField fx:id="password" GridPane.columnIndex="1" GridPane.rowIndex="5" GridPane.columnSpan="4"/> <HBox spacing="10" alignment="bottom_right" GridPane.columnIndex="4" GridPane.rowIndex="6"> <Button text="测试" onAction="#handleDataSourceTestAction" /> </HBox> <!-- Shp文件导入 --> <Text id="shpImp" text="Shp文件导入" GridPane.columnIndex="6" GridPane.rowIndex="0" GridPane.columnSpan="4"/> <Label text="shp路径:" GridPane.columnIndex="6" GridPane.rowIndex="1" /> <TextField fx:id="shpFilePath" GridPane.columnIndex="7" GridPane.rowIndex="1" GridPane.columnSpan="4"/> <Label text="表名:" GridPane.columnIndex="6" GridPane.rowIndex="2" /> <TextField fx:id="tableName" GridPane.columnIndex="7" GridPane.rowIndex="2" GridPane.columnSpan="4"/> <Text fx:id="result" GridPane.columnIndex="6" GridPane.rowSpan="3" GridPane.columnSpan="4" GridPane.halignment="RIGHT" GridPane.rowIndex="3" /> <HBox spacing="10" alignment="bottom_right" GridPane.columnIndex="10" GridPane.rowIndex="6"> <Button text="导入" onAction="#handleShpImpAction" /> </HBox> <stylesheets> <URL value="@App.css" /> </stylesheets> </GridPane>
2,布局文件引入的样式文件
root { display: block; } .root { -fx-background-color: rgba(250,250,250,1); } .label { -fx-font-size: 12px; -fx-font-weight: bold; -fx-text-fill: #333333; -fx-effect: dropshadow( gaussian , rgba(255,255,255,0.5) , 0,0,0,1 ); } #dataSet, #shpImp { -fx-font-size: 24px; -fx-font-family: "Arial Black"; -fx-fill: #818181; -fx-effect: innershadow( three-pass-box , rgba(0,0,0,0.7) , 6, 0.0 , 0 , 2 ); } #result { -fx-fill: FIREBRICK; -fx-font-weight: bold; -fx-effect: dropshadow( gaussian , rgba(255,255,255,0.5) , 0,0,0,1 ); } .button { -fx-text-fill: white; -fx-font-family: "Arial Narrow"; -fx-font-weight: bold; -fx-background-color: linear-gradient(#61a2b1, #2A5058); -fx-effect: dropshadow( three-pass-box , rgba(0,0,0,0.6) , 5, 0.0 , 0 , 1 ); } .button:hover { -fx-background-color: linear-gradient(#2A5058, #61a2b1); }
3, FX启动类
package cn.piesat.java; import javafx.application.Application; import javafx.fxml.FXMLLoader; import javafx.scene.Parent; import javafx.scene.Scene; import javafx.scene.image.Image; import javafx.stage.Stage; import java.util.SimpleTimeZone; import java.util.TimeZone; public class AppMain extends Application { public static void main(String[] args) { Object o = TimeZone.getDefault(); launch(args); } @Override public void start(Stage stage) throws Exception { Parent root = FXMLLoader.load(getClass().getResource("/cn/piesat/resource/App.fxml")); Scene scene = new Scene(root, 800, 400); stage.setScene(scene); stage.setTitle("Shp2Mysql导入工具(仅支持8.0+)");//设置标题 stage.getIcons().add(new Image(getClass().getResourceAsStream("/cn/piesat/resource/icon.png"))); stage.show(); } }
4,界面交互控制文件
package cn.piesat.java; import javafx.concurrent.Task; import javafx.event.ActionEvent; import javafx.fxml.FXML; import javafx.scene.text.Text; import javafx.scene.control.TextField; import org.apache.commons.dbcp.BasicDataSource; import org.geotools.data.mysql.MySQLDialectBasic; import org.geotools.data.shapefile.ShapefileDataStore; import org.geotools.data.simple.SimpleFeatureCollection; import org.geotools.data.simple.SimpleFeatureIterator; import org.geotools.data.simple.SimpleFeatureSource; import org.geotools.jdbc.JDBCDataStore; import org.locationtech.jts.geom.GeometryCollection; import org.opengis.feature.simple.SimpleFeature; import org.opengis.feature.simple.SimpleFeatureType; import org.opengis.feature.type.AttributeDescriptor; import org.opengis.geometry.Geometry; import java.io.File; import java.nio.charset.Charset; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.text.SimpleDateFormat; import java.util.*; import java.util.regex.Matcher; import java.util.regex.Pattern; public class AppController { @FXML private Text result; @FXML private TextField host; @FXML private TextField port; @FXML private TextField user; @FXML private TextField dataBase; @FXML private TextField password; @FXML private TextField shpFilePath; @FXML private TextField tableName; /** * 测试数据库连接 * @param event */ @FXML protected void handleDataSourceTestAction(ActionEvent event) { Connection conn = getConnect(); if(null != conn){ try{ result.setText("数据库连接成功!"); conn.close(); }catch (Exception e){ System.out.println(e.getMessage()); result.setText("数据库连接失败!" + e.getMessage()); } } } /** * 导入数据 * @param event */ @FXML protected void handleShpImpAction(ActionEvent event) { if("".equals(shpFilePath.getText().trim()) || "".equals(tableName.getText().trim())){ result.setText("参数配置有误,请重新输入!"); return; } File file = new File(shpFilePath.getText()); if(!file.exists()){ result.setText("Shp文件不存在,请检查后再输入!"); return; } Task<Void> progressTask = new Task<Void>(){ @Override protected void failed() { super.failed(); result.setText("数据导入失败!"); } @Override protected Void call() throws Exception { try{ SimpleFeatureSource featureSource = readShp(shpFilePath.getText()); createTable(getJDBCDataStore(), featureSource); writeShp2Mysql(getConnect(), featureSource); }catch (Exception e){ result.setText(e.getMessage()); } return null; } }; new Thread(progressTask).start(); result.setText("正在导入数据,请稍候!"); } /** * 获取数据库连接 * @return */ public Connection getConnect(){ Connection conn = null; result.setText(""); if("".equals(host.getText().trim()) || "".equals(port.getText().trim()) || "".equals(user.getText().trim()) ||"".equals(password.getText().trim()) ||"".equals(dataBase.getText().trim())){ return null; } try{ //连接数据库参数 jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC String url = new StringBuffer("jdbc:mysql://").append(host.getText()).append(":").append(port.getText()) .append("/").append(dataBase.getText()).append("?useSSL=false&characterEncoding=UTF-8&serverTimezone=UTC").toString(); conn = DriverManager.getConnection(url, user.getText(), password.getText()); }catch (Exception e){ e.printStackTrace(); result.setText(e.getMessage()); } return conn; } /** * 获取MySQLDataStoreFactory * @return */ public JDBCDataStore getJDBCDataStore(){ JDBCDataStore dataStore = new JDBCDataStore(); BasicDataSource dataSource = new BasicDataSource(); String url = new StringBuffer("jdbc:mysql://").append(host.getText()).append(":").append(port.getText()) .append("/").append(dataBase.getText()).append("?useSSL=false&serverTimezone=UTC").toString(); dataSource.setUrl(url); dataSource.setUsername(user.getText()); dataSource.setPassword(password.getText()); dataStore.setDataSource(dataSource); MySQLDialectBasic dialect = new MySQLDialectBasic(dataStore); dialect.setStorageEngine("InnoDB"); dataStore.setSQLDialect(dialect); return dataStore; } /** * 使用geotools读取shp文件获取SimpleFeatureSource对象 * @return */ public SimpleFeatureSource readShp(String shpfile){ SimpleFeatureSource featureSource =null; try { File file = new File(shpfile); ShapefileDataStore shpDataStore = null; shpDataStore = new ShapefileDataStore(file.toURI().toURL()); //设置编码 Charset charset = Charset.forName("GBK"); shpDataStore.setCharset(charset); String tableName = shpDataStore.getTypeNames()[0]; featureSource = shpDataStore.getFeatureSource (tableName); shpDataStore.dispose(); }catch (Exception e){ e.printStackTrace(); } return featureSource; } /** * 创建数据库表 * @param dataStore * @param featureSource */ public void createTable(JDBCDataStore dataStore, SimpleFeatureSource featureSource){ try { SimpleFeatureType schema = featureSource.getSchema(); // 1,删除已有库表 try{ String dropTableSql = "DROP TABLE " + tableName.getText(); dataStore.getDataSource().getConnection().prepareStatement(dropTableSql).execute(); }catch (Exception e){ System.out.println("表不存在!"); } try{ String dropTableSql1 = "DROP TABLE " + schema.getTypeName(); dataStore.getDataSource().getConnection().prepareStatement(dropTableSql1).execute(); }catch (Exception e){ System.out.println("表不存在!"); } // 2,创建新表 dataStore.createSchema(schema); dataStore.dispose(); } catch (Exception e) { e.printStackTrace(); } } /** * 将shp数据写入数据库 * @param conn * @param featureSource * @throws SQLException */ public void writeShp2Mysql(Connection conn, SimpleFeatureSource featureSource ) throws SQLException { result.setText("正在导入数据,请稍候!"); if(null == conn){ result.setText("数据库连接失败!"); return; } SimpleFeatureType schema = featureSource.getSchema(); //开始写入数据 try { SimpleFeatureCollection featureCollection = featureSource.getFeatures(); SimpleFeatureIterator features = featureCollection.features(); String[] columnNames = new String[schema.getAttributeCount()]; for(int i = 0; i < schema.getAttributeCount(); ++i) { AttributeDescriptor attributeType = schema.getDescriptor(i); columnNames[i] = attributeType.getLocalName(); } List<List<Object>> dataList = new ArrayList<>(); while (features.hasNext()) { List<Object> attrs = new ArrayList<>(); SimpleFeature feature = features.next(); for (int i = 0; i < feature.getAttributeCount(); i++) { attrs.add(feature.getAttribute(i)); } dataList.add(attrs); } StringBuilder columnSb = new StringBuilder(); columnSb.append("INSERT INTO ").append(schema.getTypeName()).append("("); for(String columnName: columnNames){ columnSb.append(columnName).append(","); } String colunmSql = columnSb.substring(0, columnSb.length()-1) + ") VALUES "; StringBuilder sb = new StringBuilder(colunmSql); for(List<Object> attrs: dataList){ StringBuffer msb = new StringBuffer("("); for(Object o: attrs){ if(o instanceof GeometryCollection || o instanceof Geometry){ msb.append("ST_GeomFromText('").append(String.valueOf(o)).append("')").append(","); }else if(o instanceof String){ msb.append("'").append(String.valueOf(o)).append("'").append(","); }else if(o instanceof Date){ String dateStr = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(o); msb.append("'").append(dateStr).append("'").append(","); } else{ msb.append(String.valueOf(o)).append(","); } } sb.append(msb.substring(0, msb.length()-1)).append(")").append(","); } String insertSql = toChinese(sb.substring(0, sb.length()-1)); conn.prepareStatement(insertSql).execute(); String renameTableSql = "RENAME TABLE " + schema.getTypeName() +" TO " + tableName.getText() +""; conn.prepareStatement(renameTableSql).execute(); result.setText("共导入数据" + dataList.size() + "条.\n\r恭喜,数据导入成功!"); } catch (Exception e) { e.printStackTrace(); result.setText(e.getMessage()); }finally { if(null != featureSource && null != featureSource.getDataStore()){ featureSource.getDataStore().dispose(); } if(null != conn){ conn.close(); } } } /** * 判断是否为中文 * @param c * @return */ private static boolean isChinese(char c) { Character.UnicodeBlock ub = Character.UnicodeBlock.of(c); if (ub == Character.UnicodeBlock.CJK_UNIFIED_IDEOGRAPHS || ub == Character.UnicodeBlock.CJK_COMPATIBILITY_IDEOGRAPHS || ub == Character.UnicodeBlock.CJK_UNIFIED_IDEOGRAPHS_EXTENSION_A || ub == Character.UnicodeBlock.GENERAL_PUNCTUATION || ub == Character.UnicodeBlock.CJK_SYMBOLS_AND_PUNCTUATION || ub == Character.UnicodeBlock.HALFWIDTH_AND_FULLWIDTH_FORMS) { return true; } return false; } /** * 判断是否存在中文乱码 * @param strName * @return */ public static boolean isMessyCode(String strName) { Pattern p = Pattern.compile("\\s*|\t*|\r*|\n*"); Matcher m = p.matcher(strName); String after = m.replaceAll(""); String temp = after.replaceAll("\\p{P}", ""); char[] ch = temp.trim().toCharArray(); float chLength = 0 ; float count = 0; for (int i = 0; i < ch.length; i++) { char c = ch[i]; if (!Character.isLetterOrDigit(c)) { if (!isChinese(c)) { count = count + 1; } chLength++; } } float result = count / chLength ; if (result > 0.4) { return true; } else { return false; } } /** * 将乱码中文重新编码为UTF-8格式 * @param msg * @return */ public static String toChinese(String msg){ if(isMessyCode(msg)){ try { return new String(msg.getBytes("GBK"), "UTF-8"); } catch (Exception e) { } } return msg ; } }