1. myEclipse中sql問題
我跟你當初的想法一樣，自己做了一個sql生成工具，通過根據傳入的參數 比如table 查詢條件，查詢列名等信息查詢。。等一下我貼給你一部分，你參考一下，記得採納哦
package javaUtil;
import java.lang.reflect.Field;
import java.util.ArrayList;
sql工具
public class SqlUtil {
//生成查詢的sql語句，三個參數最後一個是通過下面那個工具方法生成的，前兩個都是在action定義
public static String getSelectSql(String table,ArrayList<String> columns,ArrayList<String> condition){
//查詢表中全部的信息columns==null||"".equals(columns) condition==null||"".equals(condition)
String sql="select ";
if(columns.size()==0){
sql+="* from "+table;
//根據給的condition查詢表全部信息
}else{
//依照條件查詢
for(int i=0;i<(columns.size());i++){
if(i!=(columns.size()-1)){
sql+=columns.get(i)+",";
}else{
sql+=columns.get(i)+" from "+table;
}
}
}
if(condition.size()==0){
sql+=" ";
}else{
sql+=" where ";
for(int i=0;i<(condition.size());i++){
if(i!=(condition.size()-1)){
sql+=condition.get(i)+ " and ";
}else{
sql+=condition.get(i);
}
}
}
return sql;
}
//獲取一個對象中，所有屬性的屬性名字和值
//通過這些值轉化成ArrayList<String>類型的數據，以便生成sql使用
public static ArrayList<String> setAllComponentsName(Object f) {
// 獲取f對象對應類中的所有屬性域
Field[] fields = f.getClass().getDeclaredFields();
ArrayList<String> condition=new ArrayList<String>();
for(int i = 0 , len = fields.length; i < len; i++) {
// 對於每個屬性，獲取屬性名
String varName = fields[i].getName();
try {
// 獲取原來的訪問控制許可權
boolean accessFlag = fields[i].isAccessible();
// 修改訪問控制許可權
fields[i].setAccessible(true);
// 獲取在對象f中屬性fields[i]對應的對象中的變數
Object o = fields[i].get(f);
//把條件中的null拋掉
if(o==null||"".equals(o)){
System.out.println("這個值為空不輸出");
}else{
//把一個條件轉化成sql語言存到condition中
condition.add(i, varName + "=" + o);
}
// 恢復訪問控制許可權
fields[i].setAccessible(accessFlag);
} catch (IllegalArgumentException ex) {
ex.printStackTrace();
} catch (IllegalAccessException ex) {
ex.printStackTrace();
}
}
return condition;
}
}
測試工具
package javaUtil;
import java.util.ArrayList;
public class TestSqlUtil {
/**
* 測試這個sql生成工具的main方法，模擬後台的action
* @param args
*/
public static void main(String[] args) {
//模擬，假設這兩個數據時從頁面接收到的
String id="1";
String name="2";
//建立工具的三個條件，如下是怎麼設置的
String table="user";
ArrayList<String> columns=new ArrayList<String>();
ArrayList<String> a=new ArrayList<String>();;
//在action中定義查詢那個列的數據
columns.add(0, "use");
columns.add(1, "id");
//在action中通過接受的數據設置條件查詢的條件
//接受數據的類是自己建立，裡面含有資料庫全部表的列名
ConditionSqlUse condition =new ConditionSqlUse();
//設置傳遞數據的值。
condition.setId(id);
condition.setName(name);
//通過這個對象調用工具方法生成條件語句的list
a =SqlUtil.setAllComponentsName(condition);
//通過上面得出的幾個條件生成sql語句
String sql=SqlUtil.getSelectSql(table, columns, a);
System.out.print(sql);
}
你自己研究一下，當初我也是研究了好久，這個代碼是通過java來根據參數自動生成sql的工具
2. 如何在C#中用程序執行指定的SQL腳本文件，實現自動安裝創建資料庫
只在此山中,雲深不知處.
3. mybatis怎麼自動生成是sql語句
Mybatis-Generator這個工具就是拿來生成文件的，不過需要你設置一下連接的資料庫以及表的路徑。生成之後就可以用了，不過生成的都是基本的sql語句，如果想要復雜一些的還是需要自己來寫的。不過一般來說，很多數據處理都是直接查出來在伺服器處理，而不是用sql處理，因為有些時候你寫的SQL語句問題很多
4. 敢不敢更懶，自己編寫的mysql C++自動代碼生成工具
實現目標： （1）簡單的SQL增，刪，改，查 代碼生成。（C++代碼結構） （2）自動編寫MYSQL的連接池技術。 （3）同時生成資料庫的SQL腳本（可以直接創建資料庫表結構） （4）自動生成對應SQL語句的C++類結構和介面。供上層系統調用。 （5）自動生成可以自己檢測的數據函數測試介面。 （6）自動生成資料庫配置文件的相關創建，讀寫。 （7）代碼支持自動融合，也就是說，已經編寫的代碼，在下次生成中，如果未發生變化，就不會被重寫。
5. SQL注入哪些工具最有效
SQL Power Injector是一款在.Net 1.1中創建的應用程序，可幫助滲透測試人員在網頁上查找和利用SQL注入。
特徵
支持Windows，Unix和Linux操作系統
SQL Server，Oracle，MySQL，Sybase / Adaptive Server和DB2兼容
SSL支持
自動從網頁上的表單或IFrame載入參數（GET或POST）
檢測並瀏覽框架集
自動檢測網站語言的選項
檢測並添加載入頁面進程期間使用的Cookie（Set-Cookie檢測）
自動查找提交頁面，其方法（GET或POST）以不同的顏色顯示
可以直接在Datagrids中創建/修改/刪除載入的字元串和Cookie參數
單個SQL注入
盲目的SQL注入
比較頁面的真實和錯誤響應或cookie中的結果
時間延遲
SQL注入在自定義瀏覽器中的響應
可以使用HTML上下文顏色查看返回頁面的HTML代碼源並在其中搜索
微調參數和cookie注入
可以參數化預期結果的長度和計數以優化應用程序執行SQL注入所用的時間
創建/編輯預設的ASCII字元，以優化盲注SQL請求數/請求速度
多線程（最多可配置50個）
選項可以通過空的評論/ ** /針對IDS或過濾器檢測來替換空間
在發送之前自動編碼特殊字元
自動檢測響應頁面中的預定義SQL錯誤
在響應頁面中自動檢測預定義的單詞或句子
實時結果
將會話保存並載入到XML文件中
自動查找正面答案和負面答案頁面之間差異的功能
可以創建一個范圍列表，它將替換隱藏的SQL注入字元串中的變數（>）並自動為您播放它們
使用文本文件中的預定義列表自動重播變數范圍
Firefox插件，它將啟動SQL Power Injector以及當前網頁的所有信息及其會話上下文（參數和cookie）
兩個集成工具：Hex和Char編碼器和MS SQL @options解釋器
可以編輯Referer
可以選擇一個用戶代理（或者甚至在用戶代理XML文件中創建一個）
可以使用設置窗口配置應用程序
支持可配置的代理
軟體截圖
地址：http://www.sqlpowerinjector.com/index.htm
ilo--，Reversing.org - sqlbftools
地址：https://packetstormsecurity.com/files/download/43795/sqlbftools-1.2.tar.gz
Bernardo Damele AG：sqlmap，自動SQL注入工具
介紹
sqlmap是一款開源滲透測試工具，可自動檢測和利用SQL注入漏洞並接管資料庫伺服器。它具有強大的檢測引擎，針對終極滲透測試人員的眾多特性，以及從資料庫指紋識別，從資料庫獲取數據，到訪問底層文件系統以及在操作系統上執行命令的各種開關，帶外連接。
特徵
完全支持MySQL，Oracle，PostgreSQL，Microsoft SQL Server，Microsoft Access，IBM DB2，SQLite，Firebird，Sybase，SAP MaxDB，HSQLDB和Informix資料庫管理系統。
完全支持六種SQL注入技術：基於布爾的盲，基於時間的盲，基於錯誤，基於UNION查詢，堆棧查詢和帶外。
支持直接連接資料庫而不通過SQL注入，通過提供DBMS憑證，IP地址，埠和資料庫名稱。
支持枚舉用戶，密碼哈希，特權，角色，資料庫，表和列。
自動識別密碼哈希格式並支持使用基於字典的攻擊對其進行破解。
支持完全轉儲資料庫表，根據用戶的選擇提供一系列條目或特定列。用戶也可以選擇僅轉儲每列條目中的一系列字元。
支持搜索特定的資料庫名稱，跨所有資料庫的特定表或所有資料庫表的特定列。例如，這對於識別包含自定義應用程序憑證的表格非常有用，其中相關列的名稱包含名稱和傳遞等字元串。
當資料庫軟體是MySQL，PostgreSQL或Microsoft SQL Server時，支持從資料庫伺服器底層文件系統下載和上載任何文件。
當資料庫軟體是MySQL，PostgreSQL或Microsoft SQL Server時，支持執行任意命令並在資料庫伺服器底層操作系統上檢索它們的標准輸出。
支持在攻擊者機器和資料庫伺服器底層操作系統之間建立帶外狀態TCP連接。該通道可以是互動式命令提示符，Meterpreter會話或圖形用戶界面（VNC）會話，可以根據用戶的選擇進行選擇。
通過Metasploit的Meterpreter 命令支持資料庫進程'用戶許可權升級
地址：http://sqlmap.org/
icesurfer：SQL Server接管工具 - sqlninja
介紹
喜歡從Microsoft SQL Server上的SQL注入到資料庫上的完整GUI訪問？採用一些新的SQL注入技巧，在注冊表中添加幾個遠程鏡頭以禁用數據執行保護，混合一個自動生成調試腳本的小Perl，將所有這些放在一個帶有Metasploit包裝器的振動器中，只有sqlninja的攻擊模塊之一！
Sqlninja是一款旨在利用以Microsoft SQL Server作為後端的Web應用程序中的SQL注入漏洞的工具。
其主要目標是在易受攻擊的資料庫伺服器上提供遠程訪問，即使在非常惡劣的環境中也是如此。滲透測試人員應該使用它來幫助和自動化發現SQL注入漏洞時接管資料庫伺服器的過程。
特徵
完整的文檔可以在tarball中找到，也可以在這里找到，但是這里列出了忍者的功能：
遠程SQL Server的指紋（版本，執行查詢的用戶，用戶許可權，xp_cmdshell可用性，資料庫身份驗證模式）
數據提取，基於時間或通過DNS隧道
與Metasploit3集成，通過VNC伺服器注入獲得對遠程資料庫伺服器的圖形化訪問，或者僅上傳Meterpreter
通過vbscript或debug.exe僅上傳可執行的HTTP請求（不需要FTP / TFTP）
直接和反向綁定，TCP和UDP
當沒有可用於直接/反向外殼的TCP / UDP埠時，DNS隧道偽外殼，但資料庫伺服器可以解析外部主機名
ICMP隧道外殼，當沒有TCP / UDP埠可用於直接/反向外殼，但資料庫可以Ping您的盒子
蠻力的'sa'密碼（2種口味：基於字典和增量）
如果找到'sa'密碼，許可權將升級到系統管理員組
創建自定義的xp_cmdshell，如果原始的已被刪除
TCP / UDP埠可以從目標SQL Server掃描到攻擊機器，以便找到目標網路防火牆允許的埠並將其用於反向shell
迴避技術混淆了一些IDS / IPS / WAF
與churrasco.exe集成，通過令牌綁架將許可權升級到w2k3上的SYSTEM
支持CVE-2010-0232，將sqlservr.exe的許可權升級到SYSTEM
地址：https://sourceforge.net/projects/sqlninja/files/sqlninja/sqlninja-0.2.999-alpha1.tgz/download
6. SQL sever 2008圖形操作後自動生成sql代碼，怎麼弄
你說的是跟蹤系統的sql：
1.開始菜單->所有程序 ->Microsoft SQL Server 2008->性能工具->SQL Server Profiler；
2.開始菜單->所有程序 ->SQL Server Management Studio，菜單工具->SQL Server Profiler
7. 填入表名列名條件自動生成SQL語句的工具
ALTER proc [dbo].[proc_insert] (@tablename varchar(256),@where varchar(max))
as
begin
set nocount on
declare @sqlstr varchar(MAX)
declare @sqlstr1 varchar(MAX)
declare @sqlstr2 varchar(MAX)
select @sqlstr='select ''INSERT '+@tablename
select @sqlstr1=''
select @sqlstr2=' ('
select @sqlstr1= ' VALUES ( ''+'
select @sqlstr1=@sqlstr1+col+'+'',''+' ,@sqlstr2=@sqlstr2+name +',' from (select case
-- when a.xtype =173 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
when a.xtype =36 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
when a.xtype =127 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(20),'+a.name +')'+' end'
when a.xtype =104 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(1),'+a.name +')'+' end'
when a.xtype =175 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
when a.xtype =61 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'
when a.xtype =106 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),'+a.name +')'+' end'
when a.xtype =62 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'
when a.xtype =56 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(11),'+a.name +')'+' end'
when a.xtype =60 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'
when a.xtype =239 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
when a.xtype =108 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),'+a.name +')'+' end'
when a.xtype =231 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
when a.xtype =59 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'
when a.xtype =58 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'
when a.xtype =52 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(12),'+a.name +')'+' end'
when a.xtype =122 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'
when a.xtype =48 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(6),'+a.name +')'+' end'
-- when a.xtype =165 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
when a.xtype =167 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
else '''NULL'''
end as col,a.colid,a.name
from syscolumns a
where a.id = object_id(@tablename) and a.xtype <>189 and a.xtype <>34 and a.xtype <>35 -- and a.xtype <>36
AND COLUMNPROPERTY(a.id, a.name, 'IsIdentity') <> 1
)t order by colid
select @sqlstr=@sqlstr+left(@sqlstr2,len(@sqlstr2)-1)+') '+left(@sqlstr1,len(@sqlstr1)-3)+')'' from '+@tablename + ' where '+@where
-- print @sqlstr
exec(@sqlstr)
set nocount off
end
8. 處理數據批量生成sql插入語句
處理數據批量生成sql插入語句
最近在做一個天氣預報模塊，首先需要將客戶端公網ip轉換成所在城市，然後將所在城市名轉換成對應的城市代碼，在網上找到了城市代碼，但是需要處理一下，看了看，有三百多城市及對應的城市代碼，想存到資料庫。就想著做一個數據處理自動生成sql語句的工具，提高效率。
1 直轄市
2 "北京","上海","天津","重慶"
3 "101010100","101020100","101030100","101040100"
4
5 特別行政區
6 "香港","澳門"
7 "101320101","101330101"
8
9 黑龍江
10 "哈爾濱","齊齊哈爾","牡丹江","大慶","伊春","雙鴨山","鶴崗","雞西","佳木斯","七台河","黑河","綏化","大興安嶺"
11 "101050101","101050201","101050301","101050901","101050801","101051301","101051201","101051101","101050401","101051002","101050601","101050501","101050701"
12
13 吉林
14 "長春","延吉","吉林","白山","白城","四平","松原","遼源","大安","通化"
15 "101060101","101060301","101060201","101060901","101060601","101060401","101060801","101060701","101060603","101060501"
16
17 遼寧
18 "沈陽","大連","葫蘆島","盤錦","本溪","撫順","鐵嶺","遼陽","營口","阜新","朝陽","錦州","丹東","鞍山"
19 "101070101","101070201","101071401","101071301","101070501","101070401","101071101","101071001","101070801","101070901","101071201","101070701","101070601","101070301"
20
21 內蒙古
22 "呼和浩特","呼倫貝爾","錫林浩特","包頭","赤峰","海拉爾","烏海","鄂爾多斯","通遼"
23 "101080101","101081000","101080901","101080201","101080601","101081001","101080301","101080701","101080501"
24
25 河北
26 "石家莊","唐山","張家口","廊坊","邢台","邯鄲","滄州","衡水","承德","保定","秦皇島"
27 "101090101","101090501","101090301","101090601","101090901","101091001","101090701","101090801","101090402","101090201","101091101"
28
29 河南
30 "鄭州","開封","洛陽","平頂山","焦作","鶴壁","新鄉","安陽","濮陽","許昌","漯河","三門峽","南陽","商丘","信陽","周口","駐馬店"
31 "101180101","101180801","101180901","101180501","101181101","101181201","101180301","101180201","101181301","101180401","101181501","101181701","101180701","101181001","101180601","101181401","101181601"
32
33 山東
34 "濟南","青島","淄博","威海","曲阜","臨沂","煙台","棗庄","聊城","濟寧","菏澤","泰安","日照","東營","德州","濱州","萊蕪","濰坊"
35 "101120101","101120201","101120301","101121301","101120710","101120901","101120501","101121401","101121701","101120701","101121001","101120801","101121501","101121201","101120401","101121101","101121601","101120601"
36
37 山西
38 "太原","陽泉","晉城","晉中","臨汾","運城","長治","朔州","忻州","大同","呂梁"
39 "101100101","101100301","101100601","101100401","101100701","101100801","101100501","101100901","101101001","101100201","101101101"
40
41 江蘇
42 "南京","蘇州","崑山","南通","太倉","吳縣","徐州","宜興","鎮江","淮安","常熟","鹽城","泰州","無錫","連雲港","揚州","常州","宿遷"
43 "101190101","101190401","101190404","101190501","101190408","101190406","101190801","101190203","101190301","101190901","101190402","101190701","101191201","101190201","101191001","101190601","101191101","101191301"
44
45 安徽
46 "合肥","巢湖","蚌埠","安慶","六安","滁州","馬鞍山","阜陽","宣城","銅陵","淮北","蕪湖","毫州","宿州","淮南","池州"
47 "101220101","101221601","101220201","101220601","101221501","101221101","101220501","101220801","101221401","101221301","101221201","101220301","101220901","101220701","101220401","101221701"
48
49 陝西
50 "西安","韓城","安康","漢中","寶雞","咸陽","榆林","渭南","商洛","銅川","延安"
51 "101110101","101110510","101110701","101110801","101110901","101110200","101110401","101110501","101110601","101111001","101110300"
52
53 寧夏
54 "銀川","固原","中衛","石嘴山","吳忠"
55 "101170101","101170401","101170501","101170201","101170301"
56
57 甘肅
58 "蘭州","白銀","慶陽","酒泉","天水","武威","張掖","甘南","臨夏","平涼","定西","金昌"
59 "101160101","101161301","101160401","101160801","101160901","101160501","101160701","101050204","101161101","101160301","101160201","101160601"
60
61 青海
62 "西寧","海北","海西","黃南","果洛","玉樹","海東","海南"
63 "101150101","101150801","101150701","101150301","101150501","101150601","101150201","101150401"
64
65 湖北
66 "武漢","宜昌","黃岡","恩施","荊州","神農架","十堰","咸寧","襄陽","孝感","隨州","黃石","荊門","鄂州"
67 "101200101","101200901","101200501","101201001","101200801","101201201","101201101","101200701","101200201","101200401","101201301","101200601","101201401","101200301"
68
69 湖南
70 "長沙","邵陽","常德","郴州","吉首","株洲","婁底","湘潭","益陽","永州","岳陽","衡陽","懷化","韶山","張家界"
71 "101250101","101250901","101250601","101250501","101251501","101250301","101250801","101250201","101250701","101251401","101251001","101250401","101251201","101250202","101251101"
72
73 浙江
74 "杭州","湖州","金華","寧波","麗水","紹興","衢州","嘉興","台州","舟山","溫州"
75 "101210101","101210201","101210901","101210401","101210801","101210501","101211001","101210301","101210601","101211101","101210701"
76
77 江西
78 "南昌","萍鄉","九江","上饒","撫州","吉安","鷹潭","宜春","新余","景德鎮","贛州"
79 "101240101","101240901","101240201","101240301","101240401","101240601","101241101","101240501","101241001","101240801","101240701"
80
81 福建
82 "福州","廈門","龍岩","南平","寧德","莆田","泉州","三明","漳州"
83 "101230101","101230201","101230701","101230901","101230301","101230401","101230501","101230801","101230601"
84
85 貴州
86 "貴陽","安順","赤水","遵義","銅仁","六盤水","畢節","凱里","都勻"
87 "101260101","101260301","101260208","101260201","101260601","101260801","101260701","101260501","101260401"
88
89 四川
90 "成都","瀘州","內江","涼山","阿壩","巴中","廣元","樂山","綿陽","德陽","攀枝花","雅安","宜賓","自貢","甘孜州","達州","資陽","廣安","遂寧","眉山","南充"
91 "101270101","101271001","101271201","101271601","101271901","101270901","101272101","101271401","101270401","101272001","101270201","101271701","101271101","101270301","101271801","101270601","101271301","101270801","101270701","101271501","101270501"
92
93 廣東
94 "廣州","深圳","潮州","韶關","湛江","惠州","清遠","東莞","江門","茂名","肇慶","汕尾","河源","揭陽","梅州","中山","德慶","陽江","雲浮","珠海","汕頭","佛山"
95 "101280101","101280601","101281501","101280201","101281001","101280301","101281301","101281601","101281101","101282001","101280901","101282101","101281201","101281901","101280401","101281701","101280905","101281801","101281401","101280701","101280501","101280800"
96
97 廣西
98 "南寧","桂林","陽朔","柳州","梧州","玉林","桂平","賀州","欽州","貴港","防城港","百色","北海","河池","來賓","崇左"
99 "101300101","101300501","101300510","101300301","101300601","101300901","101300802","101300701","101301101","101300801","101301401","101301001","101301301","101301201","101300401","101300201"
100
101 雲南
102 "昆明","保山","楚雄","德宏","紅河","臨滄","怒江","曲靖","思茅","文山","玉溪","昭通","麗江","大理"
103 "101290101","101290501","101290801","101291501","101290301","101291101","101291201","101290401","101290901","101290601","101290701","101291001","101291401","101290201"
104
105 海南
106 "海口","三亞","儋州","瓊山","通什","文昌"
107 "101310101","101310201","101310205","101310102","101310222","101310212"
108
109 新疆
110 "烏魯木齊","阿勒泰","阿克蘇","昌吉","哈密","和田","喀什","克拉瑪依","石河子","塔城","庫爾勒","吐魯番","伊寧"
111 "101130101","101131401","101130801","101130401","101131201","101131301","101130901","101130201","101130301","101131101","101130601","101130501","101131001"
112
113 西藏
114 "拉薩","阿里","昌都","那曲","日喀則","山南","林芝"
115 "101140101","101140701","101140501","101140601","101140201","101140301","101140401"
116
117 台灣
118 "台北","高雄"
119 "101340102","101340201"
城市代碼
一看上去很亂的，而且對應關系是每個省城市一行，代碼一行，分別用引號引起，用逗號分隔，每行間都沒有符號分隔，省名沒有用引號。首先是想著把省名去掉，因為每個城市名都是不相同的。想著每兩行兩行的去處理，但是也要費不少功夫，還容易出錯。就想個索性一次性的全處理的演算法。
ps：界面很簡單，上面是輸入數據，中間是轉換，下面是輸出數據。
後台主要代碼：
[csharp] view plain
private void button1_Click(object sender, EventArgs e)
{
string data = textBox1.Text.Replace("r", "").Replace("n", "").Replace("t", "").Replace(" ", "").Replace(" ", "").Replace(" ", "");
MatchCollection matchsdata = matches(data, ""[sS]*?"");
string[,] temps = new string[matchsdata.Count / 2, 2];
int count0 = 0;
int count1 = 0;
string input = string.Empty;
foreach (Match m in matchsdata)
{
string tempdata = m.Value.Replace(""", "");
try
{
int tryp = int.Parse(tempdata);
temps[count1, 1] = tempdata;
count1++;
}
catch (Exception ex)
{
temps[count0, 0] = tempdata;
count0++;
}
}
for (int i = 0; i < (matchsdata.Count / 2); i++)
{
input += "insert into tbl_CityCode(c_city,c_code) values( + temps[i, 0] + , + temps[i, 1] + )rn";
}
textBox2.Text = input;
}
public static MatchCollection matches(string str, string exp)
{
return Regex.Matches(str, exp, RegexOptions.IgnoreCase);
}
首先是將輸入的數據處理，去除換行符，空格什麼的。然後你應該是會得到一行數據，然後通過正則表達式匹配出所有帶引號的數據，你會發現需要的數據全部都是用引號引起來的，但是怎樣區分城市名和城市代碼呢，它們是混在一起的。不用擔心，你發現了嗎？城市名是字元串，城市代碼是一串數字，我們只要將匹配出的數據數組遍歷，每一行數據都去轉換成int類型，這樣城市名的行就會報錯，在catch中捕捉，這一行就是城市名，沒錯的就是城市代碼，把數據一次存到一個二維數組，對應的列中就行了。這樣就會獲得了相對應的城市名和城市代碼。生成的sql語句要對應相應的資料庫表。
表結構：
轉換完了將生成的sql語句放到查詢器中執行就ok了。共處理了349個城市。
最後不放心自己的演算法，隨機抽查了幾條數據，沒有錯誤。
<script type="text/javascript"><!-- google_ad_client = "ca-pub-1944176156128447"; /* cnblogs 首頁橫幅 */ google_ad_slot = "5419468456"; google_ad_width = 728; google_ad_height = 90; //--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>
9. java如何根據實體類圖生成sql腳本
自動生成C#和Java實體類，自動生連接符為「&」和「+」的INSERT,UPDATE,DELETE,SELECT語句，支持簡單的數據查詢修改。