Pessoal,
Criei um Servlet para buscar o nome das cidades no mysql baseado no estado. Quando passo para o servlet a UF do estado, o ResultSet traz os valores corretamente, quando passo o ID do estado, o ResultSet retorna vazio.
Nos dois casos, se eu pegar a query gerada, o mysql retorna corretamente as cidades, passando o ID ou a UF.
Esse é o meu Servlet:
import java.io.IOException;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.SerializationFeature;
import br.com.guiatelesul.sig.models.tables.CityTableModel;
public class GetCitiesByStateServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public GetCitiesByStateServlet() {
}
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
if(request.getParameter("q").isEmpty() == false || request.getParameter("q") != null){
CityTableModel[] cities = null;
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/BD", "USER",
"PASS");
Statement st = con.createStatement();
String query = "select cit_ID,cit_NAME from wp_city,wp_state where cit_IDSTATE = sta_ID and (sta_SHORTNAME = '"
+ String.valueOf(request.getParameter("q")) + "' or sta_ID = '" + String.valueOf(request.getParameter("q"))
+ "') order by cit_NAME";
ResultSet rs = st.executeQuery(query);
rs.last();
cities = new CityTableModel[rs.getRow()];
rs.first();
if (cities.length > 0) {
int count = 0;
do {
cities[count] = new CityTableModel();
cities[count].setId(Long.parseLong(rs.getString(1)));
cities[count].setName(rs.getString(2));
count++;
} while (rs.next());
}
} catch (Exception e) {
e.printStackTrace();
}
ObjectMapper objectMapper = new ObjectMapper();
// configure Object mapper for pretty print
objectMapper.configure(SerializationFeature.INDENT_OUTPUT, true);
// writing to console, can write to any output stream such as file
StringWriter stringEmp = new StringWriter();
objectMapper.writeValue(stringEmp, cities);
response.setContentType("application/json");
response.getWriter().print(stringEmp);
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
}
}
Poderiam me ajudar?