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

» JWorld@TW » JDBC/SQL討論區  

按列印兼容模式列印這個話題 列印話題    把這個話題寄給朋友 寄給朋友    訂閱主題
reply to postflat 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
話題樹型展開
人氣 標題 作者 字數 發文時間
470 sql搜尋 sunhome125 11276 2017-05-16 14:56
349 Re:sql搜尋 kentyeh 243 2017-05-17 02:28
366 Re:sql搜尋 sunhome125 393 2017-05-17 02:40
355 Re:sql搜尋 javaX 110 2017-05-17 03:02
371 Re:sql搜尋 kentyeh 649 2017-05-17 03:21
361 Re:sql搜尋 sunhome125 97 2017-05-18 13:42
» JWorld@TW »  JDBC/SQL討論區

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

JWorld@TW 本站商標資訊

Powered by Powerful JuteForum® Version Jute 1.5.8