how to read multiple ms access table using jdbc

[emint]

hi all
i got problem reading multiple table from ms access using jdbc. i read two table from one class but i got 12 table to read. so how can i do dat? any help will be great
thank you

[masijade]

By adding more queries?

If you mean you have "12" "mdb" files to read, then open 12 connections. If you mean you have to read information from multiple tables within the same "mdb" file, then add additional queries for them. If you need the data from all the tables combined in a single query, then you need to use "join" and that is an SQL and not a Java question.

[emint]

it is not a java question??? why not??

public void buildStatement() throws SQLException {
      statement = connection.createStatement();
      statement1 = connection.createStatement();
      statement2 = connection.createStatement();
      statement3 = connection.createStatement();
     //more statement...................
   }


    public void executeQuery() throws SQLException {

   boolean foundResults = statement.execute("SELECT * FROM tblMaze1");
   boolean foundResults1 = statement1.execute("SELECT * FROM tblMaze2");
    boolean foundResults2 = statement2.execute("SELECT * FROM tblMaze3");
    //more statement
   if(foundResults){
      ResultSet set = statement.getResultSet();

      if(set!=null) displayResults(set);

   }
   if(foundResults1){
      ResultSet set1 = statement1.getResultSet();

      if(set1!=null) displayResults1(set1);

   }
   if(foundResults2){
      ResultSet set2 = statement2.getResultSet();

      if(set2!=null) displayResults2(set2);

   }
   if(foundResults3){
      ResultSet set3 = statement3.getResultSet();

      if(set3!=null) displayResults3(set3);

   }
   if(foundResults4){
      ResultSet set4 = statement4.getResultSet();

      if(set4!=null) displayResults4(set4);

   }
   if(foundResults5){
      ResultSet set5 = statement5.getResultSet();

      if(set5!=null) displayResults5(set5);

   }
   if(foundResults6){
      ResultSet set6 = statement6.getResultSet();

      if(set6!=null) displayResults6(set6);

   }
   if(foundResults7){
      ResultSet set7 = statement7.getResultSet();

      if(set7!=null) displayResults7(set7);

   }

   if(foundResults8){
      ResultSet set8 = statement8.getResultSet();

      if(set8!=null) displayResults8(set8);

   }

    if(foundResults9){
      ResultSet set9 = statement9.getResultSet();

      if(set9!=null) displayResults9(set9);

   }
   if(foundResults10){
      ResultSet set10 = statement10.getResultSet();

      if(set10!=null) displayResults10(set10);

   }
   if(foundResults11){
      ResultSet set11 = statement11.getResultSet();

      if(set11!=null) displayResults11(set11);

   }

   else {
      connection.close();
   }
}
void displayResults(ResultSet rs) throws SQLException {
      ResultSetMetaData metaData = rs.getMetaData();
      int columns=metaData.getColumnCount();

      while(rs.next()){
         for(int i=1;i<=columns;++i) {
            text+=rs.getString(i);
         }
      }
      

   }
void displayResults1(ResultSet rs1) throws SQLException {
      ResultSetMetaData metaData = rs1.getMetaData();
      int columns=metaData.getColumnCount();

      while(rs1.next()){
         for(int i=1;i<=columns;++i) {
            text1+=rs1.getString(i);
         }
      }
      

   }

   void displayResults2(ResultSet rs2) throws SQLException {
      ResultSetMetaData metaData = rs2.getMetaData();
      int columns=metaData.getColumnCount();

      
      }

[emint]

this is how i done. i m jus looking if there is better way to do it. thx anyway

[masijade]

Because, like I said, a "join" in the where clause of your SQL query is a much better way to do it (you only need one query), and the construction of that query (as it is an SQL query) is an SQL question and not a Java question.

[emint]

Thank you for that, would u mind to give meh example of joining sql pls thx

[masijade]

select table1.field1, table1.field2, table2.field1
from table1, table2
where table1.field3 = table2.field5

Again, this is an SQL question.

[emint]

its ok it is SQL question, but did not make any sance. thank anyway

[masijade]

Then you need to explain better exactly what it is you are trying to accomplish. "I need the data from 12 tables" doesn't really tell us anything, and there is no reason why we should have to study your code to puzzle out and guess at what it is you are trying to do, when you could simply tell us.

[emint]

Then you need to explain better exactly what it is you are trying to accomplish. "I need the data from 12 tables" doesn't really tell us anything, and there is no reason why we should have to study your code to puzzle out and guess at what it is you are trying to do, when you could simply tell us.

Sorry for that. well i got one single mdb file with 12 different tables. i just want to read all 12 tables and want to store each table's value in 12 different strings. Thats what i want to achieve ... hope is it clear now. thank you.

[masijade]

Then I would build a single method that takes a StringBuilder (for storing the value), and an SQL statement and set those off.

public void executeStatement(StringBuilder sb, String query) {
    try {
        Statement stmt = conn.createStatement();  // or however you wish to get/create a statement
        try {
            ResultSet rs = stmt.executeQuery(query);
            try {
                int fields = rs.getMetaData().getColumnCount();
                while(rs.next()) {
                    for (int i = 1; i <= fields; i++) {
                        sb.append(rs.getString(i)).append(",");
                    }
                    sb.replace(sb.length() - 1, sb.length(), "\n");  // replace trailing comma with newline
                }
            } finally {
                try { rs.close(); } catch (Exception e) {}
            }
        } finally {
            try { stmt.close(); } catch (Exception e) {}
        }
    } catch (SQLException sqle) {
        sqle.printStackTrace();
    }
}

...
    StringBuilder sb = new StringBuilder();
    executeStatement(sb, "SELECT * FROM tblMaze1");
    String rsMaze1 = sb.toString();
    sb.setLength(0);
    executeStatement(sb, "SELECT * FROM tblMaze2");
    String rsMaze2 = sb.toString();
    sb.setLength(0);
...

[emint]

thank very very much. it works fine and understood. thank you again and sorry for confuse. thank you