MySQL resultset to java array

Question

I'm not doing very well with what I'm trying to do at the moment. The aim is to to get all information from a MySQL table and put it into an array. Once into an array I will use the priority queue in java and try to get it to work with that.... but that's not the issue I'm stuck on at the moment.

public static void array(String args[]) { // create priority queue

   try { 
        String url = "jdbc:mysql://localhost:3306/project"; 
        Connection conn = DriverManager.getConnection(url,"root","nbuser"); 

        PreparedStatement stmt = conn.prepareStatement("SELECT user_id,s_date,e_date,d_date,department,projectname,projectapplication FROM booking");
        ResultSet rs; 
        rs=stmt.executeQuery();

                  List<User> userList = new ArrayList<User>();
        while(rs.next()) {
        User user = new User(); //issue here with non-static variable
        user.userid(rs.getString("user_id"));
        user.s_date(rs.getObject("s_date"));
        user.e_date(rs.getObject("e_date"));
        user.d_date(rs.getObject("d_date"));
        user.department(rs.getObject("department"));
        user.projectname(rs.getObject("projectname"));
        user.projectapplication(rs.getObject("projectapplication"));
        user.priority(rs.getObject("priority"));

        userList.add(user);
     }

        conn.close(); 
    } catch (Exception e) { 
        System.err.println("Got an exception! "); 
        System.err.println(e.getMessage()); 
    } 
}

The aim is to get it to run through all the different sections in the table and populate them into an array, but I'm stuck on populating the array.

Any help would be appreciated thanks.

EDIT:

public class User {
public String userid;
public String s_date;
public String e_date;
public String d_date;
public String department;
public String projectname;
public String projectapplication;
public int priority;

    private void userid(String string) {
        throw new UnsupportedOperationException("Not yet implemented");
    }

    private void s_date(Object object) {
        throw new UnsupportedOperationException("Not yet implemented");
    }

    private void e_date(Object object) {
        throw new UnsupportedOperationException("Not yet implemented");
    }

    private void d_date(Object object) {
        throw new UnsupportedOperationException("Not yet implemented");
    }

    private void department(Object object) {
        throw new UnsupportedOperationException("Not yet implemented");
    }

    private void projectname(Object object) {
        throw new UnsupportedOperationException("Not yet implemented");
    }

    private void projectapplication(Object object) {
        throw new UnsupportedOperationException("Not yet implemented");
    }

    private void priority(Object object) {
        throw new UnsupportedOperationException("Not yet implemented");
    }
}

similar to this?

You're selecting a lot of fields. Which of them need to be added to array? — Andrew Logvinov
– Andrew Logvinov, Commented Feb 7, 2013 at 21:16
It would be far better and easier if you can use an ORM like JPA. Look for it's implementation that suits you, like, Hibernate, EclipseLink, whatever. They will be easier to work with — Rohit Jain
– Rohit Jain, Commented Feb 7, 2013 at 21:16

ogzd · Accepted Answer · 2013-02-07 22:50:04Z

1

Create a User class with the appropriate fields in the SQL table and keep each user in a list.

  List<User> userList = new ArrayList<User>();
  while(rs.next()) {
     User user = new User();
     user.setId(rs.getString("user_id"));
     user.setStartDate(rs.getTimestamp("s_date");
     ....
     userList.add(user);
  }
  return userList;

EDIT:

  class User {
   private String id;
   private Date startDate;
   //other fields need to be declared as well as their setter methods

   public void setStartDate(Date startDate) {
      this.startDate = startDate;
   }

   public void setId(String id) {
      this.id = id;
   }
  }

I would also recommend to take a look at JPA and Hibernate.

edited Feb 7, 2013 at 22:50

answered Feb 7, 2013 at 21:20

ogzd

5,7422 gold badges28 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Curious_Bop Over a year ago

User class like added above in my edit? so from there on would it be a case of user.(stringname)rs.getObject("....") ? bit unsure in regards to this

ogzd Over a year ago

It depends on the fields, for example if you keep date fields as timestamp in db, I would go with Date class not String

ogzd Over a year ago

you can use rs.getString(fieldname) or you can use rs.getObject(fieldname) and cast it afterward

Curious_Bop Over a year ago

I've editted my class in the edit section then implemented your function in the original question but commented in where I'm having an error, also is the class right or am I completely off track (most likely case)

ogzd Over a year ago

It is better practice to name setter methods with set prefix with field name. For example; setId, setDepartmant ..

|

Collectives™ on Stack Overflow

MySQL resultset to java array

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related