I need to fetch all my data from MySQL. I have tried several methods to try to get this to work but still, I'm getting empty list. I had seen a similar problem getting solved by adding com.mysql.cj.jdbc.Driver.zeroDateTimeBehavior=convertToNull in the command, but that just gives me a class not found exception. Does anybody know how to get over this?

My Servlet:

package org.datafetching;

import java.io.IOException;
import java.io.PrintWriter;

import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import com.google.gson.Gson;

@WebServlet("/fetchdata")
public class FetchData extends HttpServlet {
    private static final long serialVersionUID = 1L;
    
    public FetchData() {
    }
    
    
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doPost(request, response);
    }
    
    
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            //Class.forName("com.mysql.cj.jdbc.Driver.zeroDateTimeBehavior=convertToNull");
            Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/db", "name", "pw");
            
            List<People> staffs = new ArrayList<People>();
            PreparedStatement ps=conn.prepareStatement("select * from grey_goose.winter_internship");
            ResultSet rs=ps.executeQuery();
            while (rs.next()) {
                People people = new People();
                staffs.add(people);
            }
            rs.close();
            ps.close();
            conn.close();
            
            Gson gson = new Gson();
            String tablejson = gson.toJson(staffs);
            
            PrintWriter printWriter = response.getWriter();
            response.setContentType("application/json");
            response.setCharacterEncoding("UTF-8");
            printWriter.write(tablejson);
            printWriter.flush();
            
        }
        catch(Exception e) {
            e.printStackTrace();
        }
    }

}

My People class:

package org.datafetching;

public class People {
    
    private Integer sl_no;
    private String business_code;
    private Integer cust_number;
    private String clear_date;
    private Integer buisness_year;
    private Integer doc_id;
    private String posting_date;
    private String document_create_date;
    private String due_in_date;
    private String invoice_currency;
    private String document_type;
    private Integer posting_id;
    private String total_open_amount;
    private String baseline_create_date;
    private String cust_payment_terms;
    private Integer invoice_id;
    
    
    public Integer getSlNo() {
        return sl_no;
    }
    public void setSlNo(Integer sl_no) {
        this.sl_no = sl_no;
    }
    public String getBusinessCode() {
        return business_code;
    }
    public void setBusinessCode(String business_code) {
        this.business_code = business_code;
    }
    public Integer getCustNumber() {
        return cust_number;
    }
    public void setCustNumber(Integer cust_number) {
        this.cust_number = cust_number;
    }
    public String getClearDate() {
        return clear_date;
    }
    public void setClearDate(String clear_date) {
        this.clear_date = clear_date;
    }
    public Integer getBusinessYear() {
        return buisness_year;
    }
    public void setBusinessYear(Integer buisness_year) {
        this.buisness_year = buisness_year;
    }
    public Integer getDocId() {
        return doc_id;
    }
    public void setDocId(Integer doc_id) {
        this.doc_id = doc_id;
    }
    public String getPostingDate() {
        return posting_date;
    }
    public void setPostingDate(String posting_date) {
        this.posting_date = posting_date;
    }
    public String getDocumentCreateDate() {
        return document_create_date;
    }
    public void setDocumentCreateDate(String document_create_date) {
        this.document_create_date = document_create_date;
    }
    public String getDueInDate() {
        return due_in_date;
    }
    public void setDueInDate(String due_in_date) {
        this.due_in_date = due_in_date;
    }
    public String getInvoiceCurrency() {
        return invoice_currency;
    }
    public void setInvoiceCurrency(String invoice_currency) {
        this.invoice_currency = invoice_currency;
    }
    public String getDocumentType() {
        return document_type;
    }
    public void setDocumentType(String document_type) {
        this.document_type = document_type;
    }
    public Integer getPostingId() {
        return posting_id;
    }
    public void setDocumentType(Integer posting_id) {
        this.posting_id = posting_id;
    }
    public String getTotalOpenAmount() {
        return total_open_amount;
    }
    public void setTotalOpenAmount(String total_open_amount) {
        this.total_open_amount = total_open_amount;
    }
    public String getBaselineCreateDate() {
        return baseline_create_date;
    }
    public void setBaselineCreateDate(String baseline_create_date) {
        this.baseline_create_date = baseline_create_date;
    }
    public String getCustPaymentTerms() {
        return cust_payment_terms;
    }
    public void setCustPaymentTerms(String cust_payment_terms) {
        this.cust_payment_terms = cust_payment_terms;
    }
    public Integer getInvoiceId() {
        return invoice_id;
    }
    public void setInvoiceId(Integer invoice_id) {
        this.invoice_id = invoice_id;
    }
}

Edit: Adding an example of my current output

CodePudding user response：

while (rs.next()) {
    People people = new People();
    people.setBusinessYear(rs.getInt(5));
    // ... all the other fields
    staffs.add(people);
}

Read here: https://docs.oracle.com/en/java/javase/11/docs/api/java.sql/java/sql/ResultSet.html

CodePudding user response：

Inside while you have to fill your People object with actual data from ResultSet:

while (rs.next()) {
    People people = new People();
    people.setSlNo(rs.getInt(1));

    // ... and so on

    staffs.add(people);
}

Also I suggest you to replace * in your SQL with comma separated list of columns from your table 'winter_internship', because with * column order is generally not guaranteed.