CODEWITHSUNDEEP
javasqlmultithreading
Marc Rasmussen
Marc Rasmussen

Reputation: 20555

Selecting multiple data from the database at the same time

I am creating a java application that needs to collect loads of data, process the data into objects and return the objects as a list.

All of the data collected is from different tables in my database (some are joined but all of them are different SQL calls)

I was thinking of getting this data through different threads but since multiple threads cannot use the same connection to acess data in the database i would have to create a new connection for each of these threads.

My Question is: what is the best way to acess and process multiple data from database at the same time?

Upvotes: 0

Views: 546

Answers (3)

Juned Ahsan
Juned Ahsan

Reputation: 68715

Ideally the database should be designed in such a way that you should be able to get all the related data in a single query(maybe with JOINS). Not sure if that is possible to achieve in your case.

There are three viable options, one you have already tried by creating multiple threads and fetching the data. I will just add an input to that approach and you may try in case that optimizes. Create Data Fetcher threads to fetch the data from different tables and create one Data Processor thread to process the data as it is fetched by Data Fetcher threads.

or

Second approach can be to to create a stored procedure, which will run directly on the database and can do some data processing for you. This will avoid the need of creating too many threads and doing a lot of processing in your java code.

or

Mix both the approaches to achieve the best results.

Good luck!

Upvotes: 0

vijayk
vijayk

Reputation: 2753

I am using Spring framework.Suppose there is ModelBean class present where all constants are declared.In ModelBean class name is field which are declared.

public class CurriculumReportDaoImpl extends JdbcDaoSupport{
   public List<String> fetchList(){
      String query="";
      List<ModelBean > tempList=new ArrayList<ModelBean >();
      List<Map<String,Object>> record = getJdbcTemplate().queryForList(query);

      for(Map<String,Object> result=record){
         /*create new instance of ModelBean*/
         model=new ModelBean();
         /*"name" is the column name which are fetch from db*/
         model.setName(result.get("name").toString);
         /*now set model in tempList*/
         tempList.add(ModelBean );
      }

      return tempList;
   }
}

If you have many connection Then you can create many List here and set into ModelBean class. I think this will help you.

Upvotes: 0

Lukas Eichler
Lukas Eichler

Reputation: 5913

If you have enough memory I would use a full second level change that syncs to the database. Using a cache makes it extremely faster. If you won't have enough memory on the server/client you can cache your query on the sqlserver with a table that has all the values from your query and this table gets updated every second.

Otherwise you can use a Threadpool with Threads which inserts the queryresults into a shared object for the result.

Upvotes: 1

Related Questions