Java Storing Multiple Rows From Select Queries

Question

This is actually a re-do of an older question of mine that I have completely redone because my old question seemed to confuse people.

I have written a Java program that Queries a database and is intended to retrieve several rows of data. I have previously written the program in Informix-4GL and I am using a sql cursor to loop through the database and store each row into a "dynamic row of record". I understand there are no row of records in Java so I have ended up with the following code.

public class Main {

     // DB CONNECT VARIABLE ===========================
     static Connection gv_conn = null;

     // PREPARED STATEMENT VARIABLES ==================
     static PreparedStatement users_sel = null;
     static ResultSet users_curs = null;
     static PreparedStatement uinfo_sel = null;
     static ResultSet uinfo_curs = null;

     // MAIN PROGRAM START ============================

     public static void main(String[] args) {

          try {
               // CONNECT TO DATABASE CODE
          } catch(Exception log) {
               // YOU FAILED CODE
          }

          f_prepare(); // PREPARE THE STATEMENTS

          ArrayList<Integer> list_id = new ArrayList<Integer>();
          ArrayList<String> list_name = new ArrayList<String>();
          ArrayList<Integer> list_info = new ArrayList<String>();
          ArrayList<String> list_extra = new ArrayList<String>();

          try {

               users_sel.setInt(1, 1);
               users_curs = users_sel.executeQuery();

               // RETRIEVE ROWS FROM USERS
               while (users_curs.next()) {

                    int lv_u_id = users_curs.getInt("u_id");
                    String lv_u_name = users_curs.getString("u_name");

                    uinfo_sel.setInt(1, lv_u_id);
                    uinfo_curs = uinfo_sel.executeQuery();

                    // RETRIEVE DATA FROM UINFO RELATIVE TO USER
                    String lv_ui_info = uinfo_curs.getString("ui_info");
                    String lv_ui_extra = uinfo_curs.getString("ui_extra");

                    // STORE DATA I WANT IN THESE ARRAYS
                    list_id.add(lv_u_id);
                    list_name.add(lv_u_name);
                    list_info.add(lv_ui_info);
                    list_extra.add(lv_ui_extra);

               }

          } catch(SQLException log) {
               // EVERYTHING BROKE
          }

          // MAKING SURE IT WORKED
          System.out.println(
               list_id.get(0) +
               list_name.get(0) +
               list_info.get(0) +
               list_extra.get(0)
          );

          // TESTING WITH ARBITRARY ROWS
          System.out.println(
               list_id.get(2) +
               list_name.get(5) +
               list_info.get(9) +
               list_extra.get(14)
          );

     }

     // PREPARE STATEMENTS SEPARATELY =================

     public static void f_prepare() {

          String lv_sql = null;

          try {

               lv_sql = "select * from users where u_id >= ?"
               users_sel = gv_conn.prepareStatement(lv_sql);

               lv_sql = "select * from uinfo where ui_u_id = ?"
               uinfo_sel = gv_conn.prepareStatement(lv_sql)

          } catch(SQLException log) {
               // IT WON'T FAIL COZ I BELIEEEVE
          }

     }

}

class DBConn {
   // connect to SQLite3 code
}

All in all this code works, I can hit the database once, get all the data I need, store it in variables and work with them as I please however this does not feel right and I think it's far from the most suited way to do this in Java considering I can do it with only 15 lines of code in Informix-4GL.

Can anyone give me advice on a better way to achieve a similar result?

James Watson · Accepted Answer · 2015-03-17 16:36:19Z

1

In order to use Java effectively you need to use custom objects. What you have here is a lot of static methods inside a class. It seems that you are coming from a procedural background and if you try to use Java as a procedural language, you will not much value from using it. So first off create a type, you can plop it right inside your class or create it as a separate file:

class User
{
   final int id;
   final String name;
   final String info;
   final String extra;

   User(int id, String name, String info, String extra)
   {
     this.id = id;
     this.name = name;
     this.info = info;
     this.name = name;
   }

   void print()
   {
     System.out.println(id + name + info + extra);
   }
}

Then the loop becomes:

      List<User> list = new ArrayList<User>();

      try {

           users_sel.setInt(1, 1);
           users_curs = users_sel.executeQuery();

           // RETRIEVE ROWS FROM USERS
           while (users_curs.next()) {

                int lv_u_id = users_curs.getInt("u_id");
                String lv_u_name = users_curs.getString("u_name");

                uinfo_sel.setInt(1, lv_u_id);
                uinfo_curs = uinfo_sel.executeQuery();

                // RETRIEVE DATA FROM UINFO RELATIVE TO USER
                String lv_ui_info = uinfo_curs.getString("ui_info");
                String lv_ui_extra = uinfo_curs.getString("ui_extra");

                User user = new User(lv_u_id, lv_u_name, lv_ui_info, lv_ui_extra);

                // STORE DATA
                list.add(user);
        }

      } catch(SQLException log) {
           // EVERYTHING BROKE
      }

      // MAKING SURE IT WORKED
      list.get(0).print();

This doesn't necessarily address the number of lines. Most people who use Java don't interact with databases with this low-level API but in general, if you are looking to get down to the fewest number of lines (a questionable goal) Java isn't going to be your best choice.

answered Mar 17, 2015 at 16:36

James Watson

4742 silver badges11 bronze badges

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

4 Comments

TheLovelySausage Over a year ago

Although this solution is still a bit lengthy (I am used to procedural scripting as you said so maybe it's just a different coding standard). What's great about this though is that the class makes it super reusable so that's always great. Thanks a bunch

James Watson Over a year ago

When learning a new language, I have found it is best to attempt to reserve judgement until you have at least some basic proficiency. You are on the right track here with regard to reuse. If you do it right, you'll find that overall, your solutions end up being shorter since you can can write the logic once. I've given a pretty poor example to keep it simple. There are many places you can learn more about Java design.

TheLovelySausage Over a year ago

I know it's not right to make judgements without knowing any better, however i just wanted to get my goal across clearly. To write clean, correct and reusable code. I understand Java is not the best language for database driven programs (which is why i use Informix-4GL). I much prefer to hear from Java professionals who can point me in the right direction. This has solved half my problem and still have to find the best Java way of achieving what i want

James Watson Over a year ago

Generally Java programs use one or more of a selection of libraries that abstract a lot of these details away. Most of these fall in a category called Object-to-Relational-Mapping (ORM) tools. I hesitate to recommend many of these tools such as Hibernate to someone like yourself who has a strong understanding of SQL. They tend only work well for a subset of applications and when you design the DB around the assumptions of the tool designer. MyBatis is one that my team has had good results with.

Will Hartung · Accepted Answer · 2015-03-17 16:25:37Z

Your code is actually quite close to box stock JDBC.

The distinction is that in Java, rather than having a discrete collection of arrays per field, we'd have a simple Java Bean, and a collection of that.

Some examples:

public class ListItem {
    Integer id;
    String name;
    Integer info;
    String extra;

    … constructors and setters/getters ellided …

}

List<ListItems> items = new ArrayList<>();


…

while(curs.next()) {   
    ListItem item = new ListItem();
    item.setId(curs.getInt(1));
    item.setName(curs.getString(2));
    item.setInfo(curs.getInfo(3));
    item.setExtra(curs.getString(4));

    items.add(item);
}

This is more idiomatic, and of course does not touch on the several frameworks and libraries available to make DB access a bit easier.

Collectives™ on Stack Overflow

Java Storing Multiple Rows From Select Queries

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related