Inserting MYSQL into JAVA array

Question

Table A

id | salary | company    
1  | 500    | 2    
2  | 700    | 2
3  | 900    | 3
4  | 1100   | 4    
5  | 600    | 5

I want to insert MySQL into a java array. However, my current query is only bringing in the last result of the MySQL table into my database.id[I] array, and then repopulating that same result from I=0 to I=10 for the entire array. Sorry for the abbreviated code, but it is working, just not the way I would like it to.

Ideally, I would like for my database.id[I] array to only bring in from table A id[1] and id[2] as that is where company = 2. Thank you for any assistance!

Statement stmt = conn.createStatement();
String query = String.format("select * from table A where company =2");
ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                for (int i = 0; i < 10; ++i) {
                database.id[i] = rs.getInt("id");
                database.salary[i] = rs.getInt("salary");
}}

id

That's probably because of your for loop overwriting all previously written stuff — user, Mar 24 '17 at 17:32
because your result set is only bringing back company 2 from table ? so you are looping 10 times on two records. ? — Eddie Martinez, Mar 24 '17 at 17:33

score 0 · Answer 1 · answered Mar 24 '17 at 17:41

If you want add values into object, example:

Object user = null;
        List<Object> list = null;
        if(codigoError == 0 && respuesta.trim().equals("DONE")){
              list = new ArrayList<Object>();
              rs = (ResultSet) sentencia.getObject(1);
              while(rs.next()){
                  user = new Object();
                  user.setId(rs.getInt("id"));
                  user.setSalary(rs.getInt("salary"));
                  list.add(user);
              }

And get values:

 List<Object> list = dao.getMaleinUsr();
                if(list != null && !list.isEmpty()){
                    for(Object user : list){
                        log.info("ID::: "+user.getId());
                        log.info("Salary ::: "+user.getSalary());
                    }

score 0 · Answer 2 · answered Mar 24 '17 at 17:54

Your code is setting the same ResultSet row for the length of your array for each row in your ResultSet. You have too many loops; you didn't include the initialization of your array but a List may be better. The below should work for your needs.

Statement stmt = conn.createStatement();
String query = String.format("select * from table A where company =2");
ResultSet rs = stmt.executeQuery(query);

List<Integer> myIds = new ArrayList<>();
List<Integer> mySalaries = new ArrayList<>();

while (rs.next()) {
    myIds.add(rs.getInt("id"));
    mySalaries.add(rs.getInt("salary"));
}

You will have two list containing the data you want from your ResultSet.

Brandon McKenzie · Answer 3 · 2017-03-27T13:50:52.207

Your result set from this query would be:

id | salary | company   
1  | 500    | 2    
2  | 700    | 2

Hand executing your code, this is what happens:

The while loop picks up first result, with id = 1.

Your for loop writes 1 to database.id and 500 to database.salary from indexes 1 through 9. Note that because you preincrement i, index 0 is left unassigned, which is not great.

The while loop picks up second result, with id = 2.

Your for loop writes 2 to database.id and 700 to database.salary from indexes 1 through 9, overwriting what was previously there.

The loop terminates, with only the final record having been recorded.

The minimal change to prevent this would be to drop the inner for loop:

Statement stmt = conn.createStatement();
String query = String.format("select * from table A where company =2");
ResultSet rs = stmt.executeQuery(query);

        int i = 0;
        while (rs.next()) {
            database.id[i] = rs.getInt("id");
            database.salary[i] = rs.getInt("salary");
            i++;
}}

With this change, each value would be stored once in both of your arrays, starting from 0, and would not overwrite each other. That said, be mindful of not overrunning the bounds of your arrays: I would personally use the Collections API (probably a List). Also, it would be better form to make a list of Record objects that contain single values, rather than a Record object with two lists of fields.

Record.java

public class Record {
    private int id;
    private int salary; 
    private int company;

    public Record (int id, int salary, int company) {
        //...set your fields.
    }

    //setters, getters
}

Calling code:

Statement stmt = conn.createStatement();
String query = String.format("select * from table A where company =2");
ResultSet rs = stmt.executeQuery(query);
List<Record> recordlist = new ArrayList<Record>();

        while (rs.next()) {
            recordlist.add(new Record(rs.getInt("id"), rs.getInt("salary"), rs.getInt("company");
}}

score 0 · Answer 4 · edited May 23 '17 at 12:17

Well As I saw you wanna save them in an array

Statement stmt = conn.createStatement();
String query = String.format("select * from table A where company =2");
ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                for (int i = 0; i < 10; ++i) {<--Your for is overriting from 0 to 9 each time rs.next is true, so from 0 to 9 you will have the same value
                database.id[i] = rs.getInt("id"); 
                database.salary[i] = rs.getInt("salary");
}}

Now There is a problem, using an array you must know the size of your response, in order to know how much size you will put on your array.

As vaxquis said : using resultSet.last() followed by resultSet.getRow() will give you the row count, but it may not be a good idea (How do I get the size of a java.sql.ResultSet?)

But why not use a List?

Look we could do something like this:

 ResultSet rs = stmt.executeQuery(query);
         List<Database> ins = new ArrayList<Database>();
          while (rs.next()) {
               Databasein = new Database();
               in.id= rs.getDouble("id");
               in.salary= rs.getDouble("salary");
               ins.add(in);
                }

          System.out.println(ins.size());

So you will get your List with all values but if you want still an array you could change your list to an array , once you have your List with your records you could use

  Database[] bar = ins.toArray(new Database[ins.size()]);

And there you got ;)

hope it helps

Inserting MYSQL into JAVA array

4 Answers4