0

I have a String data type adjacency matrix in Java:

String[][] A;

I want to read my adjacency matrix A into a MySQL table. The problem is I never know how many rows/columns I will need (nor would I want to create all the columns either). I think the trick to solving this problem is creating columns on the fly. How do I add columns "on the fly?"

Once I can add columns on the fly, I could then read in the matrix row by row.

  1. I want to be able to view the data as a matrix.

All help is greatly appreciated!

CodeKingPlusPlus
  • 15,383
  • 51
  • 135
  • 216

2 Answers2

3

Instead of going through the trouble of setting up variable columns, create a simple database table A:

row     integer
col     integer
content varchar

This gives you the flexibility to easily avoid empty entries and you have one database table row for every array item.

You have two options:

  1. Generate one database row for every element of your array including nulls, or
  2. Generate one database row for every element that is not null.

For option 1, this code will provide any element:

private String getElement(int row, int col) {
    String result = null;
    try {
        if(rs.absolute(row*colNumber+col+1))
            result=rs.getString("content");
    } catch (SQLException ex) {
        System.out.println(ex.getMessage());
    }
    return result;
}

For option 2, the content of the requested element at row,col is found like this:

private String getElement(int row, int col) {
    String result = null;
    String query = "SELECT * from A where row=? and col=?";
    try {
        PreparedStatement preps = con.prepareStatement(query);
        preps.setInt(1, row);
        preps.setInt(2, col);
        preps.execute();
        rs = preps.getResultSet();
        if (rs.next()) {
            result = rs.getString("content");
        }
    } catch (SQLException ex) {
        System.out.println(ex.getMessage());
    }

    return result;
}

It is obvious that option 1 is accessing elements faster. Option 2 saves database space.

PS: If you have an array of arrays of varying length option 1 will not work.
colNumber is the fixed number of columns of your String array.

Costis Aivalis
  • 13,680
  • 3
  • 46
  • 47
  • 1
    This is clearly the correct solution. You should always avoid dynamically changing table schema when possible. – Joe K Dec 27 '12 at 22:45
  • I see how this is a lot more efficient, is there a way to query the data in this format so that I can physically look at my data as a matrix? I really want to be able to physically view the data as an n by n matrix. – CodeKingPlusPlus Dec 28 '12 at 01:52
  • In case you end up with an array of known row and column numbers, if you add all elements to the database, even the empty ones **rs.absolute(row*colunmNumber+column+1)** will position your cursor to the right row. If you do not have a fixed number of columns, or you do not wish to have a database row for empty elements, then write a method **String getElement(int row, int col)** and select the needed record. It should return null, if the element is empty, or the String if not... – Costis Aivalis Dec 28 '12 at 15:39
  • Could you give a little more detail about your solution? I do not know what you mean by cursor and the method headers you wrote and how it applies to this problem. – CodeKingPlusPlus Dec 28 '12 at 19:07
0

Maybe you can add columns to the table. Imagine your table is named "MYTABLE": In SQL Server you can write:

ALTER TABLE MYTABLE ADD COLUMN COLX varchar(255);

where X is the number of column you'll need.

This option, will allow you to add columns on your table, but maybe the solution is to create a new table dropping down the previous one:

DROP TABLE MYTABLE;
CREATE TABLE MYTABLE(COL1 varchar(255), COL2 varchar(255)...);

Let me know if it was that your problem. Thanks.

tpgalan
  • 196
  • 1
  • 14