| 483 |
|
} |
| 484 |
|
|
| 485 |
|
/** |
| 486 |
+ |
* To get the column type for a certain column and table |
| 487 |
+ |
* @param table |
| 488 |
+ |
* @param column |
| 489 |
+ |
* @return |
| 490 |
+ |
*/ |
| 491 |
+ |
public String getColumnType(String table,String column){ |
| 492 |
+ |
String query = "SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS " + |
| 493 |
+ |
"WHERE TABLE_SCHEMA='"+this.dbname+"' AND TABLE_NAME='"+table+"' AND COLUMN_NAME='"+column+"';"; |
| 494 |
+ |
String colType = this.getStringFromDb(query); |
| 495 |
+ |
return colType; |
| 496 |
+ |
} |
| 497 |
+ |
|
| 498 |
+ |
/** |
| 499 |
+ |
* To findout whether a key (i.e. column) is numeric-based or text-based |
| 500 |
+ |
* @param table |
| 501 |
+ |
* @param key |
| 502 |
+ |
* @return true if is numeric-based, false if is text-based |
| 503 |
+ |
*/ |
| 504 |
+ |
public boolean isKeyNumeric(String table, String key){ |
| 505 |
+ |
boolean isNumeric = false; |
| 506 |
+ |
String colType = getColumnType(table,key); |
| 507 |
+ |
if (colType.contains("int") || colType.contains("INT")){ |
| 508 |
+ |
isNumeric = true; |
| 509 |
+ |
} |
| 510 |
+ |
else if (colType.contains("char") || colType.contains("CHAR")){ |
| 511 |
+ |
isNumeric = false; |
| 512 |
+ |
} |
| 513 |
+ |
else { |
| 514 |
+ |
System.err.println("The key '"+key+"' from table '"+table+"' is neither numeric-based (int) nor text-based (char/varchar). Check what's wrong!"); |
| 515 |
+ |
} |
| 516 |
+ |
return isNumeric; |
| 517 |
+ |
} |
| 518 |
+ |
|
| 519 |
+ |
/** |
| 520 |
|
* To get all tables for this MySQLConnection's database. |
| 521 |
|
* @return an array of String with all table names |
| 522 |
|
*/ |
| 553 |
|
* @param table the table name |
| 554 |
|
* @return int array containing all ids |
| 555 |
|
*/ |
| 556 |
< |
public int[] getAllIds4KeyAndTable(String key, String table){ |
| 557 |
< |
int[] allIds=null; |
| 556 |
> |
public Integer[] getAllNumIds4KeyAndTable(String key, String table){ |
| 557 |
> |
Integer[] allIds=null; |
| 558 |
|
try { |
| 559 |
|
Statement S=conn.createStatement(); |
| 560 |
|
String query="SELECT DISTINCT "+key+" FROM "+table+" ORDER BY "+key+";"; |
| 563 |
|
while (R.next()){ |
| 564 |
|
idsAL.add(R.getInt(1)); |
| 565 |
|
} |
| 566 |
< |
allIds=new int[idsAL.size()]; |
| 566 |
> |
allIds=new Integer[idsAL.size()]; |
| 567 |
|
for (int i=0;i<idsAL.size();i++) { |
| 568 |
|
allIds[i]=idsAL.get(i); |
| 569 |
|
} |
| 576 |
|
return allIds; |
| 577 |
|
} |
| 578 |
|
|
| 579 |
+ |
/** |
| 580 |
+ |
* To get all distinct ordered text (i.e. char/varchar column) ids from a certain key and table from this MySQLConnection |
| 581 |
+ |
* @param key the key name |
| 582 |
+ |
* @param table the table name |
| 583 |
+ |
* @return int array containing all ids |
| 584 |
+ |
*/ |
| 585 |
+ |
public String[] getAllTxtIds4KeyAndTable(String key, String table){ |
| 586 |
+ |
String[] allIds=null; |
| 587 |
+ |
try { |
| 588 |
+ |
Statement S=conn.createStatement(); |
| 589 |
+ |
String query="SELECT DISTINCT "+key+" FROM "+table+" ORDER BY "+key+";"; |
| 590 |
+ |
ResultSet R=S.executeQuery(query); |
| 591 |
+ |
ArrayList<String> idsAL=new ArrayList<String>(); |
| 592 |
+ |
while (R.next()){ |
| 593 |
+ |
idsAL.add(R.getString(1)); |
| 594 |
+ |
} |
| 595 |
+ |
allIds=new String[idsAL.size()]; |
| 596 |
+ |
for (int i=0;i<idsAL.size();i++) { |
| 597 |
+ |
allIds[i]=idsAL.get(i); |
| 598 |
+ |
} |
| 599 |
+ |
R.close(); |
| 600 |
+ |
S.close(); |
| 601 |
+ |
} |
| 602 |
+ |
catch (SQLException e){ |
| 603 |
+ |
e.printStackTrace(); |
| 604 |
+ |
} |
| 605 |
+ |
return allIds; |
| 606 |
+ |
} |
| 607 |
|
|
| 608 |
|
/** |
| 609 |
|
* To set the sql_mode of this connection. |
