How to count fields in access table with Java?

Question

i am working on a project to edit access tables without access ,but with java so i want to get the count of fields of the table

This is the code :

package DatabaseEditor;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DatabaseOptions {

    Connection con;
    String url = "jdbc:ucanaccess://databases\\Saves.mdb";
    
    public int getFields() {
        int data = "";


        try {
            con = DriverManager.getConnection(url);

            String sql = "SELECT * FROM Workers";
            
            PreparedStatement stat = con.prepareStatement(sql);
            
            stat.executeQuery(sql);
            ResultSet rs = stat.getResultSet();
            
            //Here i want to set data value to count of fields
                            
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return data;
    }

}

getFields() function i'll run it in the start of another script

maybe this will help you https://stackoverflow.com/questions/7886462/how-to-get-row-count-using-resultset-in-java they have a few answers about count rows — Grismak, Aug 31 '22 at 10:27
Do you mean that you want to count the number of columns in a particular database table? Are you familiar with interface [DatabaseMetaData](https://docs.oracle.com/en/java/javase/17/docs/api/java.sql/java/sql/DatabaseMetaData.html)? Call method `getColumns`, iterate through the `ResultSet` and count the number of rows. — Abra, Aug 31 '22 at 16:57

guijianchou · Answer 1 · 2022-08-31T11:49:18.570

1

sql = "show full columns from table_name" and the rs.getFetchSize() is fields count

edited Aug 31 '22 at 11:49

answered Aug 31 '22 at 11:48

guijianchou

21
2

score 0 · Answer 2 · answered Sep 05 '22 at 09:34

As Abra already suggested in the comments, you could use the DatabaseMetaData interface for fetching such information. Below is a minimal code example, credits for research and theory go to Abra.

package DatabaseEditor;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DatabaseOptions {

    Connection con;
    String url = "jdbc:ucanaccess://databases\\Saves.mdb";
    
    public int getFields() {
        try {
            con = DriverManager.getConnection(url);
            DatabaseMetaData metaData = con.getMetaData();
            
            int numberOfFields = 0;
            ResultSet columns = metaData.getColumns(null, null, "Workers", null);
            
            while (columns.next()) {
                ++numberOfFields;
            }

            return numberOfFields;
        } finally {
            con.close();
        }
    }
}

How to count fields in access table with Java?

2 Answers2