Is there any way to get records from a database as a comma seperated text file

Question

Friends, I have stuck in this job I have a table empdb having 1000 rows like below

 id    name     desgn
 ---  -------  ----------------
  1   Mike     Analyst
  2   Jim      Manager
  3   John     Engg

etc . etc so on

I want to write a servlet that will query this table and download in text format say "emp_info.txt" like below

 1,sam,Engg
 2,Mike,Excecutive
 3,Jim,Manager

I mean every record in db should be on seperate line

Please guide

Answer 1

We can't get directly records from db as csv file, there are many third-party libraries available for working with CSV files,

Let's take a look at a few of them:

• Open CSV: Another popular and actively-maintained CSV library
• Apache Commons CSV: Apache's CSV offering for working with CSV Files
• Flatpack: An open-source CSV library being actively developed
• CSVeed: Open-source and actively-maintained.

Sample code for open csv, add your open csv jar to you class path. create one csv file and add its location to the code below

File file = new File(filePath); 
    try { 
        // create FileWriter object with file as parameter 
        FileWriter outputfile = new FileWriter(file); 

        // create CSVWriter object filewriter object as parameter 
        CSVWriter writer = new CSVWriter(outputfile); 

        // adding header to csv 
        String[] header = { "id", "name", "design" }; 
        writer.writeNext(header); 

        // add data to csv 
       // in loop
        String[] data = {}
        writer.writeNext(data); 

       // execute this two line until your data remains. 

        // closing writer connection 
        writer.close(); 
    } 
    catch (IOException e) { 
        // TODO Auto-generated catch block 
        e.printStackTrace(); 
    } 

After creating the file set content type as csv and flush the buffer the browser will download the file for end user.

                    resp.setContentType("application/csv");
                    resp.setHeader(HttpHeaders.CONTENT_DISPOSITION, file);
                    inStrm = new FileInputStream(file);
                    FileCopyUtils.copy(inStrm, resp.getOutputStream());
                    resp.flushBuffer();

Answer 2

I assume that you want the user who comes to your web app to be able to download all data in this "empdb" DB table as an "emp_info.txt" file. Just to be clear what is servlet:

Servlet - resides at server side and generates a dynamic web page

You should split this task to different parts:

1. Connect your database with java application by using JPA and e.x Hibernate, also implement a repository.
2. Create service that will user repository to fetch all data from the table and write it to .txt file.
3. Implement button or other functionality in GUI which will use the service you have created and will send the file for the user.