CODEWITHSUNDEEP
javamysqlarraysswingnetbeans
Osiris93
Osiris93

Reputation: 189

Storing data from MySQL into multi-dimensional array?

I am trying to store data which is called from MySQL database table where the data will be based on a specific user number. I have set the connection using a PreparedStatement and a ResultSet. With using a while loop, I need to call up the data from the database and store it in this array. Once stored, I need to call up this data in the array to be displayed in text fields on the interface.

Here is the coding:

public static void find(int x)
{
  Class.forName("com.mysql.jdbc.Driver");
           Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/database","root","root");

            PreparedStatement stmt=conn.PrepareStatement("Select * from employee where userNum ='" + x +"'");
            ResultSet rs = stmt.executeQuery();
          while(rs.next())
          {

          }

I am trying to put some statements inside of the while loop in order to display the data from the array to the interface.

Upvotes: 0

Views: 1482

Answers (1)

WillShackleford
WillShackleford

Reputation: 7018

Instead of using string concatenation to get the x value into the query, put a ? in the string and then use the setInt(1, x) function to set the value to be substituted for the query. Indexing starts with 1.

Making the result set TYPE_SCROLL_INSENSITIVE is necessary for using first() after going through the list just to get the row count to initialize the 2D array. It would probably be better to get specific types for each column instead of just treating everything as an object. But if it is useful it can be done.

PreparedStatement stmt = conn.prepareStatement("Select * from employee where userNum=?",
        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE
);
stmt.setInt(1, x);

ResultSet rs = stmt.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();
List l = new ArrayList();
rs.first();
int rowcount = 0;
do {
    rowcount++;
} while (rs.next());
rs.first();
int rowindex = 0;
Object array2D[][] = new Object[rowcount][];
do {
    array2D[rowindex] = new Object[numberOfColumns];
    for (int i = 0; i < numberOfColumns; i++) {
        array2D[rowindex][i] = rs.getObject(i + 1);
    }
    // prints each row on separate line
    System.out.println("array2D[" + rowindex + "] = " + Arrays.toString(array2D[rowindex])); 
    rowindex++;
} while (rs.next());
System.out.println("array2D = " + Arrays.deepToString(array2D));

Upvotes: 1

Related Questions