JWorld@TW the best professional Java site in Taiwan
      註冊 | 登入 | 全文檢索 | 排行榜  

» JWorld@TW » JDBC/SQL討論區  

按列印兼容模式列印這個話題 列印話題    把這個話題寄給朋友 寄給朋友    訂閱主題
reply to topicthreaded modego to previous topicgo to next topic
本主題所含的標籤
作者 sql搜尋
sunhome125





發文: 7
積分: 0
於 2017-05-16 14:56 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
public class Testcompound {

static String sql;
static double mass = 546.106676;
static double[] keyinvaluepositive = {546.1316};
static double[] keyinvaluenegative = {527.0874, 625.0561};
static double MIN = mass - 0.015;
static double MAX = mass + 0.015;

public static void compound() {
ArrayList<ArrayList<String>> MolecularFormula = new ArrayList<ArrayList<String>>();
ArrayList<String> compoundname = new ArrayList<>();
ArrayList<String> formulalist = new ArrayList<>();
ArrayList<Double> greadlist = new ArrayList<>();
ArrayList<Double> PeriodictableSubtract = new ArrayList<>();
ArrayList<String> listtakeaformula = new ArrayList<>();
ArrayList<Integer> Comparison = new ArrayList<>();
ArrayList<Double> positive = new ArrayList<>();
ArrayList<Double> Periodiclist1 = new ArrayList<>();
ArrayList<Double> Periodiclist2 = new ArrayList<>();
int x, s = 0;
Double PeriodictableSubtractcount = 0.0, PeriodictableSubtractcountall = 1.0, gread = 0.0, PeriodictableSubtractcountall2 = 1.0;
String formula = "";

try {
for (int h = 0; h < keyinvaluepositive.length; h++) {
positive.add(keyinvaluepositive[h] - 1.007276);
}
for (int h = 0; h < keyinvaluenegative.length; h++) {
positive.add(keyinvaluenegative[h] + 1.007276);
}
Collections.reverse(positive);
System.out.println(positive);
String url = "jdbc:mysql://localhost:3306/?user=root&password=1234";
Connection conn = DriverManager.getConnection(url);
Statement stmt = conn.createStatement();
stmt.executeUpdate("use COMPOUND");
String sql = "select `MolecularFormula`,`MolecularWeight` from compounddata0514 WHERE (`MolecularWeight` <=" + MAX + ") AND `MolecularWeight`>= " + MIN;
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
if (!(compoundname.contains(rs.getString(1).replaceAll("\\s+", "")))) {
if (rs.getString(1).contains("C") && rs.getString(1).contains("H")) {
compoundname.add(rs.getString(1));
}
}
}
MolecularFormula.add(compoundname);

System.out.println(MolecularFormula.get(0));
for (int i = 0; i < positive.size(); i++) {
ArrayList<String> compoundname1 = new ArrayList<>();
String sq2 = "select `MolecularFormula`,`MolecularWeight` from compounddata0514 WHERE (`MolecularWeight` <=" + (positive.getLight Bulb + 0.015) + ") AND `MolecularWeight`>= " + (positive.getLight Bulb - 0.015);
ResultSet rs1 = stmt.executeQuery(sq2);
while (rs1.next()) {
if (!(compoundname1.contains(rs1.getString(1).replaceAll("\\s+", "")))) {
if (rs1.getString(1).contains("C") && rs1.getString(1).contains("H")) {
compoundname1.add(rs1.getString(1));
}
}
}
MolecularFormula.add(compoundname1);

}

for (int m = 0; m < MolecularFormula.size(); m++) {
Comparison.add(0);
}

for (int i = 0; i < MolecularFormula.size(); i++) {
System.out.println("MolecularFormula" + i + MolecularFormula.getLight Bulb.size() + " " + MolecularFormula.getLight Bulb);
}
for (int i = 2; i <= MolecularFormula.size(); i++) {
PeriodictableSubtractcountall = PeriodictableSubtractcountall * i;
}
for (int i = 2; i <= MolecularFormula.size() - 2; i++) {
PeriodictableSubtractcountall2 = PeriodictableSubtractcountall2 * i;
}

PeriodictableSubtractcountall = (PeriodictableSubtractcountall / PeriodictableSubtractcountall2) / 2;

do {

for (int v = 0; v < MolecularFormula.size(); v++) {
if (MolecularFormula.get(v).size() > 0) {
listtakeaformula.add(MolecularFormula.get(v).get(Comparison.get(v)));
}

}
for (int g = 0; g < listtakeaformula.size() - 1; g++) {
for (int h = g + 1; h < listtakeaformula.size(); h++) {
Periodiclist1 = Periodiclist(listtakeaformula.getPresent);
Periodiclist2=Periodiclist(listtakeaformula.getCool);
System.out.println(Periodiclist1);
System.out.println(Periodiclist2);
for (int d = 0; d < Periodiclist(listtakeaformula.getPresent).size(); d++) {
PeriodictableSubtract.add(Periodiclist1.getFood - Periodiclist2.getFood);
System.out.println(PeriodictableSubtract);
}
Collections.sort(PeriodictableSubtract);
if (PeriodictableSubtract.get(0) >= 0) {
PeriodictableSubtractcount = PeriodictableSubtractcount + 1.0;
}
PeriodictableSubtract.clear();
Periodiclist1.clear();
Periodiclist2.clear();
}

}

gread = PeriodictableSubtractcount / PeriodictableSubtractcountall;
if (gread > 0.2) {
greadlist.add(gread);
for (int z = 0; z < listtakeaformula.size(); z++) {
formula = formula.concat("/" + listtakeaformula.getSleepy);
}
formulalist.add(formula);
formula = "";
}
PeriodictableSubtractcount = 0.0;
gread = 0.0;
Comparison.set(Comparison.size() - 1, s + 1);
s++;
for (int f = 0; f < Comparison.size(); f++) {
for (int g = 0; g < Comparison.size(); g++) {
if (Comparison.getPresent > MolecularFormula.getPresent.size() - 1) {
if (Comparison.get(0) > MolecularFormula.get(0).size() - 1) {
break;
}
Comparison.set(g, 0);
Comparison.set(g - 1, Comparison.get(g - 1) + 1);
s = 0;
}
}
}
listtakeaformula.clear();
} while (Comparison.get(0) < MolecularFormula.get(0).size());
System.out.println(Comparison);
System.out.println("分數" + greadlist);
System.out.println("分子式" + formulalist);
positive.clear();
} catch (SQLException e) {
System.out.printlnEnvelope;
}
}

public static ArrayList<Double> Periodiclist(String A) {
String[] list = {"Fr", "Ra", "Cs", "Ba", "Hf", "Ta", "Re", "Os", "Ir", "Pt", "Au", "Hg", "Tl", "Pb", "Bi", "Po", "At", "Rn", "Rb", "Sr", "Zr", "Nb", "Mo", "Tc", "Ru", "Rh", "Pd", "Ag", "Cd", "ln", "Sn", "Sb", "Te", "Xe", "Ca", "Sc", "Ti", "Cr", "Mn", "Fe", "Co", "Ni", "Cu", "Zn", "Ga", "Ge", "As", "Se", "Br", "Kr", "Na", "Mg", "Al", "Si", "Cl", "Ar", "Li", "Be", "Ne", "He", "H", "B", "C", "N", "O", "F", "P", "S", "K", "V", "Y", "I", "W"};
ArrayList<Double> Periodictable = new ArrayList<>();
int x = 0, checknumber = 0;
String nextstring, next2string, temstring;
Double y, y1;
for (int i = 0; i < list.length; i++) {
Periodictable.add(0.0);
}
for (int i = 0; i < list.length; i++) {
if (A.contains(list[i])) {
x = A.indexOf(list[i]);
if (x + 1 < A.length()) {
nextstring = String.valueOf(A.charAt(x + 1));
if (nextstring.matches("[0-9]")) {
if (x + 2 < A.length()) {
next2string = String.valueOf(A.charAt(x + 2));
if (next2string.matches("[0-9]")) {
y = Double.valueOf(nextstring);
y1 = Double.valueOf(next2string);
Periodictable.removeLight Bulb;
Periodictable.add(i, y * 10 + y1);
} else {
y = Double.valueOf(nextstring);
Periodictable.removeLight Bulb;
Periodictable.add(i, y);
}
} else {
y = Double.valueOf(nextstring);
Periodictable.removeLight Bulb;
Periodictable.add(i, y);
}

}
if (nextstring.matches("[a-z]")) {

temstring = (String) A.subSequence(x, x + 2);
for (int q = 0; q < list.length; q++) {
if (temstring.equals(list[q])) {
checknumber = q;
}
}
if (Periodictable.get(checknumber) == 0) {
if (x + 2 < A.length()) {
next2string = String.valueOf(A.charAt(x + 2));
if (next2string.matches("[0-9]")) {
y1 = Double.valueOf(next2string);
Periodictable.removeLight Bulb;
Periodictable.add(i, y1);
} else {
Periodictable.removeLight Bulb;
Periodictable.add(i, 1.0);
}
} else {
Periodictable.removeLight Bulb;
Periodictable.add(i, 1.0);
}
}

}
if (nextstring.matches("[A-Z]")) {
Periodictable.removeLight Bulb;
Periodictable.add(i, 1.0);
}
} else {
Periodictable.removeLight Bulb;
Periodictable.add(i, 1.0);
}
}
}

return Periodictable;
}

public static void main(String[] args) {
compound();
}

}

上面是我自己研究出的SQL程式
請問為什麼我在搜尋的時候 (單一個質量)都要花上快10分鐘的時間
請問各位SQL的高手 幫幫小弟我~~~
有什麼方法可以使搜尋的時間變快?
資料庫有39G
我有上網查過 要用索引 但是我不太會用索引

資料庫的樣子

拜託各位高手 傾壤相授


reply to postreply to post
作者 Re:sql搜尋 [Re:sunhome125]
kentyeh





發文: 644
積分: 6
於 2017-05-17 02:28 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
你的扣很亂,下次請包在【code】 裡面(按上面的#字號)
你的查詢只有一個: 查詢 compounddata0514 表格符合 MolecularWeight 範圍內的 資料

要知道查詢速度,請將這個查詢獨立成一個函式,然後再針對這個函式做一個單元測試就知道
整體速度是否慢在這個查詢

其次是資料庫的表格是否針對MolecularWeight這個欄位作索引,如果有索引應該不會慢到那裡去

最後要提醒的是 sql 不要用字串組合,容易被 sql injection


reply to postreply to post
作者 Re:sql搜尋 [Re:kentyeh]
sunhome125





發文: 7
積分: 0
於 2017-05-17 02:40 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
我知道蠻亂的,自學資料庫,有點不太懂要怎麼寫,非常感謝大大願意看
1.建索引的話要不重複且不能是null才能建索引 但是在MolecularWeight因為是分子式的質量 所以會有重複 這樣的ˋ話也可以建嗎??
2. sql 不要用字串組合,容易被 sql injection?
這是指這裡嗎??
String sql = "select `MolecularFormula`,`MolecularWeight` from compounddata0514 WHERE (`MolecularWeight` <=" + MAX + ") AND `MolecularWeight`>= " + MIN;
ResultSet rs = stmt.executeQuery(sql);
請問還有其他方法可以從java呼叫mysql嗎??

謝謝您~


reply to postreply to post
作者 Re:sql搜尋 [Re:sunhome125]
javaX





發文: 188
積分: 0
於 2017-05-17 03:02 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
String sql="select MolecularFormula,MolecularWeigh` from compounddata0514";
試試先將資料存在 arrayList 結束資料庫連線 再跑迴圈處理


reply to postreply to post
教育部:要如何保證畢業即就業
經濟部:所以公司都是我開的
財政部:發前單位請不要幻想能春風化雨
行政院:為什麼該單位發錢的時候都想去當老師
作者 Re:sql搜尋 [Re:sunhome125]
kentyeh





發文: 644
積分: 6
於 2017-05-17 03:21 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
新手有很多學習要完成,可以從程式碼一點一滴來改善

資料庫方面,nullable 欄位是可以建索引的,

又看你的程式,好似每次都在檢查MolecularFormula 含C,H,也可以用"( MolecularFormula LIKE '%C%' OR MolecularFormula LIKE '%H%')過濾,
另外檢查範圍可以改用 MolecularWeight BETWEEN :min TO :max 語法 取代 MolecularWeight>=:min AND MolecularWeight<=:max
如果只是SQL 要多次查詢,但查詢語法都一樣,改用 preparedstatement 會比較好(SQLPlan 只會做一次)

另外 compoundname 一直檢查是否有重覆,也可以考慮改用 Set 取代 List

又大量資料若是無彼此順序相關,可以改用Executor以多執行緒去做,加快整體速度

最後是程式碼太亂;如果是急著交給客戶,那麼技術債留給別人也無所謂,要不然最好按功能別拆分成不同單元,
例如資料庫存取、分子式計算,
拆成不同單元的好處是可以作單元測試,其功效短期看不出來還可能因為多做工而增加專案時程,
但長期來看,當專案一路走下去,功能、模組越來越多,可能根本忘了以前寫的初衷與目的,
有可能因為新增個功能而破壞以前程式碼的完整性,這時單元測試就會出來告訴後面的人那裡可能會出問題,
而不是上線後新增未爆彈


reply to postreply to post
作者 Re:sql搜尋 [Re:kentyeh]
sunhome125





發文: 7
積分: 0
於 2017-05-18 13:42 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
謝謝您
我有改了一下
但是 preparedstatement 我還在研究中
多執行緒也還在研究中
這個不趕時間 可以慢慢研究
希望不久我也能成為幫別人解答的高手

謝謝您~~


reply to postreply to post
» JWorld@TW »  JDBC/SQL討論區

reply to topicthreaded modego to previous topicgo to next topic
  已讀文章
  新的文章
  被刪除的文章
Jump to the top of page

JWorld@TW 本站商標資訊

Powered by Powerful JuteForum® Version Jute 1.5.8