CODEWITHSUNDEEP
javamysqlarrays
Michael Wiggins
Michael Wiggins

Reputation: 112

Read MySQL table into two-dimensional array

I have a rather sticky situation:

I have been tasked with designing an application that will read the contents of a table from a MySQL database into a two-dimensional string array, scalable to 100.

The schema for the table is:

id = INT(11)
Username = VARCHAR(100)
Password = VARCHAR(45)
UserType = VARCHAR(10)
Full Name = VARCHAR(100)
Age = INT(11)
DOB = DATE
Location = VARCHAR(100)
Record_Created = DATETIME

The issue is that I cannot read this to a file and then to the application for security reasons (contains actual admin account information). Can anyone think of an efficient way of doing this?

Upvotes: 0

Views: 1868

Answers (2)

Michael Wiggins
Michael Wiggins

Reputation: 112

I implemented the following design for my above question:

public static String[][] Table() throws SQLException
{
    String[][] Table = null;
    table = conn.createStatement();
    String sql = "select * from usersTable";
    ResultSet rs = table.executeQuery(sql);
    rs.last();
    int rowNumb = rs.getRow();
    ResultSetMetaData rsmd = rs.getMetaData();
    int columnS = rsmd.getColumnCount();

    rs.beforeFirst();
    dbTable= new String[rowNumb][columnS];

    int i=0;
    while(rs.next() && i<rowNumb && rowNumb<100)
    {
        for(int j=0;j<columnS;j++)
        {
            Table[i][j] = rs.getString(j+1);
        }
        i++;
    }
    return Table;
}

Upvotes: 1

Stephen C
Stephen C

Reputation: 718678

.... into a two-dimensional string array, scalable to 100.

I take it that you mean the array can have up to 100 elements.

If so, then one solution is to preallocate an array of size 100 x 9, then query and read the table rows and populate the array.

A second solution is to query, read the table rows, populate a list-of-arrays, and use List.toArray to extract the list contents into a 2-D array.

A final solution is to use select ... count to find the number of rows, and use that to allocate a 2-D array with the correct size. Then proceed as per the first solution.

However, using a 2-D array is a bad idea from an O-O design perspective. Instead, you should declare a class with a field for each of the 9 columns, and then represent the table contents as an array or list of that class.

Upvotes: 2

Related Questions