ArcSDE学习笔记--------增、删、改、查操作
建立连接
1 package org.lq.ssm.gp.controller; 2 3 import com.esri.sde.sdk.client.SeConnection; 4 import com.esri.sde.sdk.client.SeException; 5 6 public class SDEDBManager { 7 8 9 10 private static String server = ""; //SDE ip地址 11 private static String instance = ""; //ArcSDE服务器端口号 12 private static String database = ""; //数据库名 13 private static String username = ""; //数据库账户 14 private static String password = ""; //数据库密码 15 /** 16 * 建立SDE连接 17 * @date 2017-4-13 18 * @return 19 */ 20 public static SeConnection getConn() { 21 SeConnection conn = null; 22 if (conn == null) { 23 try { 24 conn = new SeConnection(server, instance, database, username, password); 25 if(conn!=null){ 26 System.out.println("连接SDE成功！"); 27 } 28 } catch (SeException ex) { 29 ex.printStackTrace(); 30 } 31 } 32 return conn; 33 } 34 35 }
建立与数据库的连接
1 package org.lq.ssm.gp.controller; 2 3 4 5 import java.io.InputStream; 6 import java.sql.Connection; 7 import java.sql.DriverManager; 8 import java.util.Properties; 9 10 11 12 13 14 public class DBUtil { 15 16 //连接数据库的路径 17 private static String url; 18 //连接数据库的用户名 19 private static String user; 20 //连接数据库的密码 21 private static String pwd; 22 //用于管理不同线程所获取的连接 23 private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>(); 24 //静态块 25 static{ 26 try{ 27 //读取配置文件 28 Properties prop = new Properties(); 29 InputStream is = DBUtil.class.getClassLoader().getResourceAsStream("config.properties"); 30 prop.load(is); 31 is.close(); 32 33 //获取驱动 34 String driver = prop.getProperty("jdbc.className"); 35 36 System.out.println(driver); 37 //获取地址 38 url = prop.getProperty("jdbc.url"); 39 40 //获取用户名 41 user = prop.getProperty("jdbc.username"); 42 43 //获取密码 44 pwd = prop.getProperty("jdbc.password"); 45 46 //注册驱动 47 Class.forName(driver); 48 49 }catch(Exception e){ 50 e.printStackTrace(); 51 } 52 } 53 54 55 /** 56 * 获取一个连接 57 * @return 58 * @throws Exception 59 */ 60 public static Connection getConnection() throws Exception{ 61 try{ 62 /* 63 * 通过DriverManager创建一个数据库的连接 64 * 并返回 65 */ 66 Connection conn = DriverManager.getConnection(url,user,pwd); 67 /* 68 * ThreadLocal的set方法 69 * 会将当前线程作为key,并将给定的值 70 * 作为value存入内部的map中保存。 71 */ 72 tl.set(conn); 73 return conn; 74 }catch(Exception e){ 75 e.printStackTrace(); 76 //通知调用者，创建连接出错 77 throw e; 78 } 79 } 80 81 /** 82 * 关闭给定的连接 83 */ 84 public static void closeConnection(){ 85 try{ 86 Connection conn = tl.get(); 87 if(conn != null){ 88 conn.close(); 89 tl.remove(); 90 } 91 }catch(Exception e){ 92 e.printStackTrace(); 93 } 94 } 95 96 97 98 99 }
增加操作
1 public int save(LandUser land) { 2 SeConnection conn = SDEDBManager.getConn(); 3 if (conn != null) { 4 try { 5 6 points="1 1,2 2,3 3";//点坐标的格式是这样的。数据是随便填的 7 8 SeInsert insert = new SeInsert(conn); // 使用当前的连接句柄Connection ,创建一个SeInsert对象. 9 insert.intoTable("SDE.ztl", new String[]{"SHAPE","picid", "ybMarkNo", "ybgeometry", "dzbjh", "userId", 10 "userName", "userZjh", "userYhkh", "userKhh", "userMobile", "userQygs", "userMs", 11 "landDkbh", "landName"}); 12 13 insert.setWriteMode(true);// 开启buffer,设置SeInsert对象为可写模式 14 SeLayer layer = new SeLayer(conn, "SDE.ztl", "SHAPE"); 15 16 SeCoordinateReference cr = layer.getCoordRef(); // 获得图层空间参考 17 SeRow row = insert.getRowToSet();// 获取SeInsert对象中将要添加的SeRow对象。 18 SeShape shape = new SeShape(cr); 19 shape.generateFromText("POLYGON(("+points+"))"); //增加面 20 // shape.generateFromText("LINESTRING("+points+")"); //增加线 21 row.setShape(0, shape); 22 row.setNString(1, land.getPicid()); 23 row.setNString(2, land.getYb_MarkNo()); 24 row.setNString(3, land.getYb_geometry()); 25 row.setNString(4, land.getDzbjh()); 26 row.setNString(5, land.getUserId()); 27 row.setNString(6, land.getUserName()); 28 row.setNString(7, land.getUserZjh()); 29 row.setNString(8, land.getUserYhkh()); 30 row.setNString(9, land.getUserKhh()); 31 row.setNString(10, land.getUserMobile()); 32 row.setNString(11, land.getUserQygs()); 33 row.setNString(12, land.getUserMs()); 34 row.setNString(13, land.getLandDkbh()); 35 row.setNString(14, land.getLandName()); 36 37 insert.execute(); 38 insert.flushBufferedWrites(); 39 insert.close(); 40 return 1; 41 } catch (Exception e) { 42 e.printStackTrace(); 43 return 0; 44 } finally { 45 try { 46 conn.close(); 47 } catch (SeException e) { 48 e.printStackTrace(); 49 } 50 } 51 } 52 return 2; 53 } 54
删除操作
1 public static int delete(String id){ 2 try { 3 Connection conn = DBUtil.getConnection(); 4 Statement state = conn.createStatement(); 5 String sql="delete from SDE.ztl where landDkbh='"+id+"'"; 6 // 执行Sql，得到结果集 7 int flag = state.executeUpdate(sql); 8 System.out.println("执行sql"); 9 state.close(); 10 DBUtil.closeConnection(); 11 12 if(flag>0){ 13 return 1; 14 }else{ 15 return 0; 16 } 17 } catch (Exception e) { 18 e.printStackTrace(); 19 return 0; 20 } 21 22 }
修改操作
1 public static int update(String id){ 2 try { 3 Connection conn = DBUtil.getConnection(); 4 Statement state = conn.createStatement(); 5 String sql="update SDE.ztl set " + 6 "picid = '"+land.getPicid()+"',"+ 7 "ybMarkNo ='"+land.getYb_MarkNo()+"',"+ 8 "ybgeometry ='"+land.getYb_geometry()+"',"+ 9 "dzbjh ='"+land.getDzbjh()+"',"+ 10 "userName ='"+land.getUserName()+"',"+ 11 "userZjh ='"+land.getUserZjh()+"',"+ 12 "userYhkh ='"+land.getUserYhkh()+"',"+ 13 "userKhh ='"+land.getUserKhh()+"',"+ 14 "userMobile ='"+land.getUserMobile()+"',"+ 15 "userQygs ='"+land.getUserQygs()+"',"+ 16 "userMs ='"+land.getUserMs()+"',"+ 17 "landDkbh ='"+land.getLandDkbh()+"',"+ 18 "landName ='"+land.getLandName()+"'"+ 19 " where userId = '"+land.getUserId()+"'"; 20 // 执行Sql，得到结果集 21 int flag = state.executeUpdate(sql); 22 System.out.println("执行sql"); 23 state.close(); 24 DBUtil.closeConnection(); 25 26 if(flag>0){ 27 return 1; 28 }else{ 29 return 0; 30 } 31 } catch (Exception e) { 32 e.printStackTrace(); 33 return 0; 34 } 35 36 }
查询操作
1 public static List<LandUser> selectAll(String sql){ 2 3 List<LandUser> list = new ArrayList<LandUser>(); 4 try { 5 Connection conn = DBUtil.getConnection(); 6 Statement state = conn.createStatement(); 7 8 String sql="select picid,ybMarkNo,ybgeometry,dzbjh,userId," + 9 "userName,userZjh,userYhkh,userKhh,userMobile,userQygs,userMs," + 10 "landDkbh,landName from SDE.ztl" ; 11 // 执行Sql，得到结果集 12 ResultSet rs = state.executeQuery(sql); 13 while (rs.next()) { 14 LandUser land = new LandUser(); 15 land.setPicid(rs.getString("picid")); 16 land.setYb_MarkNo(rs.getString("ybMarkNo")); 17 land.setYb_geometry(rs.getString("ybgeometry")); 18 land.setDzbjh(rs.getString("dzbjh")); 19 land.setUserId(rs.getString("userId")); 20 land.setUserName(rs.getString("userName")); 21 land.setUserZjh(rs.getString("userZjh")); 22 land.setUserYhkh(rs.getString("userYhkh")); 23 land.setUserKhh(rs.getString("userKhh")); 24 land.setUserMobile(rs.getString("userMobile")); 25 land.setUserQygs(rs.getString("userQygs")); 26 land.setUserMs(rs.getString("userMs")); 27 land.setLandDkbh(rs.getString("landDkbh")); 28 land.setLandName(rs.getString("landName")); 29 30 list.add(land); 31 } 32 rs.close(); 33 state.close(); 34 DBUtil.closeConnection(); 35 return list; 36 } catch (Exception e) { 37 e.printStackTrace(); 38 return list; 39 } 40 41 } 42
