How to generalize resultset queries?

Question

at the moment I'm working on a script that reads several values from different tables of one database. Every time I start a request, I have to open a statement and create a new resultset which leads to horrible, repetative code. What would be a good way of generalizing this and how can this be done?

Some elements from my code. At the moment there's just one statement and the closing has to be inserted. One of the primary reasons I ask this question.

public static void main(String[] args) throws Exception
{           
    Connection c = null;
    Statement stmt = null;

    try 
    {
        //set up database connection
        Class.forName("org.sqlite.JDBC");
        c = DriverManager.getConnection("jdbc:sqlite:/nfs/home/mals/p/pu2002/workspace/Database2");
        c.setAutoCommit(false);    
        stmt = c.createStatement();
        //end

        //get task id to work with
        String Task_id = null;
        if(args.length != 0) //if an argument was passed, Task_id will be the first element of the array args (arguments)
        {
            Task_id = args[0];
        }
        else if(args.length == 0) //if no arguments were passed, the highest number in the column id from tasks_task will be selected and set as Task_id
        {
            ResultSet TTask_id = stmt.executeQuery("SELECT max(id) FROM tasks_task");
            int t_id = TTask_id.getInt(1);
            Task_id = String.valueOf(t_id);
            TTask_id.close();
        }
        //end

        //get solution IDs from taks_ids
        ArrayList<Integer> List_solIDs = new ArrayList<Integer>(); //create an empty array list
        ResultSet SSolution_task_id = stmt.executeQuery("SELECT id FROM solutions_solution WHERE task_id ="+Task_id + " AND final = 1;"); //Sqlite3-Ausdruck SELECT..., Task IDs verändern pro Aufgabe - "SELECT * FROM solutions_solution where task_id ="+Task_id +";"
        while (SSolution_task_id.next()) //loops through all elements of SSolution_task_id
        {
                List_solIDs.add(SSolution_task_id.getInt("id")); //adds all elements of the resultset SSolution_task_id to the list List_solIDs 
        }
        SSolution_task_id.close();
        //end




        //get logs according to content type
        int count = List_solIDs.size();
        String log_javaBuilder = null;
        List<String> log_JunitChecker = new ArrayList<String>();

        for (int i = 0; i < count; i++) 
        {
            boolean sol_id_valid = false;
            String solID = String.valueOf(List_solIDs.get(i));

            try 
            {
                ResultSet AAttestation_sol_id = stmt.executeQuery("SELECT * FROM attestation_attestation WHERE solution_id =" +solID+";");
                int Returned = AAttestation_sol_id.getInt("final_grade_id");
            }
            catch(Exception e)
            {
                sol_id_valid = true;
            }

            if(sol_id_valid ==true)
            {
            try
            {   
                ResultSet CCresult_javaBuilder = stmt.executeQuery("SELECT log FROM checker_checkerresult WHERE solution_id = " +solID+ " AND content_type_id = 22;");  //"SELECT id FROM checker_checkerresult where solution_id = " +List_solIDs.get(i)+ ";"
                log_javaBuilder = CCresult_javaBuilder.getString("log");
                CCresult_javaBuilder.close();


                ResultSet CCresult_Junit_checker = stmt.executeQuery("SELECT log FROM checker_checkerresult WHERE solution_id = " +solID+ " AND content_type_id = 24;");

                while (CCresult_Junit_checker.next())
                {
                    log_JunitChecker.add(CCresult_Junit_checker.getString("log"));
                }
                CCresult_Junit_checker.close();
            }

            catch (Exception e)
            {
                log_JunitChecker.add(null);
            }
        //end

All types of potential improvements will be welcome.

P.S.: Tried googling.

Answer 1

What if you try to use generics on methods? this is a quick example, just for illustration, you must improve all this :)

resource: official docs

  public static <T> List<T> getSingleValueList(ResultSet rs, Class<T> clazz, String colName) throws Exception {
    ArrayList<T> list = new ArrayList<T>();
    while (rs.next()) {//loops through all elements of generic list
      list.add((T) rs.getObject(colName)); //adds all elements of the resultset rs to the list
    }
    rs.close();

    return list;
  }

  public static <T> T getSingleValue(ResultSet rs, Class<T> clazz, String colName) throws Exception {
    try {
      if (rs.next()) {//loops through all elements of generic list
        return (T) rs.getObject(colName);
      } else {
        throw new Exception("no value found.");
      }
    } finally {
      rs.close();
    }
  }

  public static void main(String[] args) throws Exception {
    Connection c = null;
    Statement stmt = null;

    try {
      //set up database connection
      Class.forName("org.sqlite.JDBC");
      c = DriverManager.getConnection("jdbc:sqlite:/nfs/home/mals/p/pu2002/workspace/Database2");
      c.setAutoCommit(false);
      stmt = c.createStatement();
    //end

      //get task id to work with
      String Task_id = null;
      if (args.length != 0) //if an argument was passed, Task_id will be the first element of the array args (arguments)
      {
        Task_id = args[0];
      } else if (args.length == 0) //if no arguments were passed, the highest number in the column id from tasks_task will be selected and set as Task_id
      {
        ResultSet TTask_id = stmt.executeQuery("SELECT max(id) FROM tasks_task");
        int t_id = TTask_id.getInt(1);
        Task_id = String.valueOf(t_id);
        TTask_id.close();
      }
    //end

      //get solution IDs from taks_ids
      ResultSet SSolution_task_id = stmt.executeQuery("SELECT id FROM solutions_solution WHERE task_id =" + Task_id + " AND final = 1;"); //Sqlite3-Ausdruck SELECT..., Task IDs verändern pro Aufgabe - "SELECT * FROM solutions_solution where task_id ="+Task_id +";"
      List<Integer> List_solIDs = getSingleValueList(SSolution_task_id, Integer.class, "id"); //create an empty array list
      //end

      //get logs according to content type
      int count = List_solIDs.size();
      String log_javaBuilder = null;
      List<String> log_JunitChecker = new ArrayList<String>();
      List<String> tmplog_JunitChecker;

      for (int i = 0; i < count; i++) {
        boolean sol_id_valid = false;
        String solID = String.valueOf(List_solIDs.get(i));

        try {
          ResultSet AAttestation_sol_id = stmt.executeQuery("SELECT * FROM attestation_attestation WHERE solution_id =" + solID + ";");
          Integer Returned = getSingleValue(AAttestation_sol_id, Integer.class, "final_grade_id");
        } catch (Exception e) {
          sol_id_valid = true;
        }

        if (sol_id_valid == true) {
          try {
            ResultSet CCresult_javaBuilder = stmt.executeQuery("SELECT log FROM checker_checkerresult WHERE solution_id = " + solID + " AND content_type_id = 22;");  //"SELECT id FROM checker_checkerresult where solution_id = " +List_solIDs.get(i)+ ";"
            log_javaBuilder = getSingleValue(CCresult_javaBuilder, String.class, "log");

            ResultSet CCresult_Junit_checker = stmt.executeQuery("SELECT log FROM checker_checkerresult WHERE solution_id = " + solID + " AND content_type_id = 24;");

            tmplog_JunitChecker = getSingleValueList(CCresult_Junit_checker, String.class, "log");
            log_JunitChecker.addAll(tmplog_JunitChecker);
          } catch (Exception e) {
            log_JunitChecker.add(null);
          }
          //end  
        }
      }
    } catch (Exception eeee) {
      //handle it
    }
  }

I hope I gave you a light.

Anyway, frameworks in almost all cases help a lot.

Answer 2

Better than writing generic method by yourself it is always better to use some framework, There are many JPA implementations out there which solve not only this issue but also takes care of multiple persistence layer boiler plate code. Start JPA from Here. You can also use Spring JDBC template as well to solve problem mentioned above Spring JDBC Documentation.

Now, if you really don't want any framework dependency and finish this code quite fast, You can define your own JDBCTemplate class which takes query and parameter map and return ResultSet. This class can handle open connection, query execution and closing connection etc.

Answer 3

Seems you want to look at using some ORM layer e.g. http://hibernate.org/orm/

What you're looking for is probably a higher-level layer which
abstracts you from the underlying lower-level JDBC type of coding.