Struts 2 get Json from DAO into the action class

Question

I have some unique probably understanding issue. I have jQuery Ajax call which call Struts2 action class which in turn calls DAO to get some data. However for some reason I do not get any data or any error, just a blank response when I add DAO operation in action class. Something is wrong and I need some eyes to tell me what is wrong,

This is my ajax call,

 $.ajax({
            url: "S2",
            //force to handle it as text
            dataType: 'json',
            type: 'GET',
            cache: false,
            contentType: "application/json;charset=utf-8",
            data:{state_code:state_code },
            beforeSend: function (xhr, setting) {
                var url = setting.url;
                url = url.replace("&_=", "&t=");
                setting.url = url;
                },
            success: function (data) {              
                console.log(JSON.stringify(data));
            }
        });

This is my DAO Class,

public List<BnGetCitiesbyStateCodeBn> GetCitiesbyStateCode(String state_code) throws SQLException {
        List<BnGetCitiesbyStateCodeBn> cities = new LinkedList<>();
        if (dbConnection != null) {
            Statement stmt = dbConnection.createStatement();

            try {
                rs = stmt.executeQuery("select Distinct(city) from cities_extended WHERE state_code = '" + state_code + "'");
                while (rs.next()) {
                    BnGetCitiesbyStateCodeBn city = null;
                    city = new BnGetCitiesbyStateCodeBn();
                    city.setState_code(rs.getString("state_code"));
                    city.setCity(rs.getString("city"));
                    cities.add(city);
                    logger.info("Cities retreived are " + cities);
                    System.out.println(cities);
                }
            } catch (Exception e) {
                logger.info("Error retreving Cities " + e);
            } finally {
                dbConnection.close();
            }
        }
        return cities;

    }

This is my Action class,

public class S2 extends ActionSupport {

    private static final long serialVersionUID = 5686197289029560661L;
    private static final Logger logger = LogManager.getLogger(S2.class);
    private String state_code;
    private String t;
    private List<BnGetCitiesbyStateCodeBn> cities;

    public S2() {
    }

    public void setState_code(String state_code) {
        this.state_code = state_code;
    }

    public String getT() {
        return t;
    }

    public void setT(String t) {
        this.t = t;
    }

    public List<BnGetCitiesbyStateCodeBn> getCities() {
        return cities;
    }

    public void setCities(List<BnGetCitiesbyStateCodeBn> cities) {
        this.cities = cities;
    }

    public String getState_code() {
        logger.info("State code is " + state_code);
        return state_code;
    }

    @Override
    public String execute() {
        try {
            GetCitiesbyStateCode citydao = new GetCitiesbyStateCode();
            cities = citydao.GetCitiesbyStateCode(state_code);
            System.out.println(cities);
        } catch (SQLException ex) {
            logger.error(ex);
            System.out.println(ex);
        }
        logger.info("log4j2 works for Struts Method.Inside Execute Method of S2 Action Class");
         System.out.println("Inside action class");
        return "success";
    }
}

This is my struts xml,

 <package name="json" namespace="/" extends="json-default">  
     <action name="S2" class="json.S2" method="execute">
            <result type="json"></result>
        </action>
    </package>

This is my bean,

public class BnGetCitiesbyStateCodeBn {
    private String city;
    private String state_code;

    public String getCity() {
        return city;
    }

    public void setCity(String city) {
        this.city = city;
    }

    public String getState_code() {
        return state_code;
    }

    public void setState_code(String state_code) {
        this.state_code = state_code;
    }
}

Whenever I comment out the calls to my DAO, I get json response back and everything works like log4j2 etc.,but when I uncomment that part,I get blank response.So I am guessing that probably my json config is missing something.

Appreciate if someone can take a look and let me know.

Ok I think I found the cause,

logger.info("state code in dao class is " + state_code);
                try {
                    rs = stmt.executeQuery("select Distinct city,state_code from cities_extended WHERE state_code = 'AL'");
                } catch (SQLException ex) {
                    logger.error("SQL Exception executing query" +ex);
                }

When I am passing state_code from ui, it gets messed up and when I hardcode the value like AL, it gives me the result perfectly fine. does underscore in parameter name does any harm ?

Answer 1

You should get SQL exception at the line

rs = stmt.executeQuery("select Distinct(city) from cities_extended WHERE state_code = '" + state_code + "'");

distinct is not a function, it's a statement, see a definition and example usage here. You need to modify the query

rs = stmt.executeQuery("select Distinct city from cities_extended WHERE state_code = '" + state_code + "'");

after this change everything should work like log4j2 etc.