I wrote getRecords() function to get data from MySQL, I tested it in main() and it gives correct result. But when I call it in the Servlet's doGet() function it returns me an empty list. Why?
public class EmpDao {
public static List<Emp> getRecords(int start, int total) {
List<Emp> list = new ArrayList<Emp>();
try {
Connection connection = new DBContextMySQL().getConnection();
PreparedStatement ps = connection.prepareStatement("select * from employee limit "+(start-1)+","+total);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
Emp e = new Emp(rs.getInt(1),
rs.getString(2),
rs.getFloat(3));
list.add(e);
}
} catch (Exception exception) {
System.out.println(exception);;
}
return list;
}
public static void main(String[] args) {
List<Emp> list = EmpDao.getRecords(1,5);
for(Emp e : list){
System.out.println(e.toString());
}
}
///and class DBContexMySQL
public class DBContextMySQL {
public Connection getConnection() throws Exception{
String url = "jdbc:mysql://localhost:3306/"+ dbname + "?useSSL=false&characterEncoding=utf8";
return DriverManager.getConnection(url,username,password);
}
private final String username = "root";
private final String password = "123456789";
private final String dbname = "employee";
public static void main(String[] args) {
try{
Connection con = new DBContextMySQL().getConnection();
Statement stm = con.createStatement();
String sql = "select * from employee";
ResultSet rs = stm.executeQuery(sql);
while (rs.next()){
System.out.println(rs.getString(2));
}
}catch (Exception e){
}
}
}
and doGet method
@WebServlet(urlPatterns = "/ViewServlet", name = "ViewServlet")
public class ViewServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html");
PrintWriter out = response.getWriter();
String spageid = request.getParameter("page");
int pageid = Integer.parseInt(spageid);
int total = 5;
if (pageid == 1) {
} else {
pageid = pageid - 1;
pageid = pageid * total + 1;
}
List<Emp> list = EmpDao.getRecords(pageid, total);
out.print("<h1>Page No: " + spageid + "</h1>");
out.print("<table border='1' cellpadding='4' width='60%'>");
out.print("<tr><th>Id</th><th>Name</th><th>Salary</th>");
for (Emp e : list) {
out.print("<tr><td>" + e.getId() + "</td><td>" + e.getName() + "</td><td>" + e.getSalary() + "</td></tr>");
}
out.print("</table>");
out.print("<a href='ViewServlet?page=1'>1</a> ");
out.print("<a href='ViewServlet?page=2'>2</a> ");
out.print("<a href='ViewServlet?page=3'>3</a> ");
out.close();
}
}