Notes

package com.highradius.Action;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import com.google.gson.Gson;
import com.google.gson.JsonArray;
import com.google.gson.JsonObject;

public class fetch {
int page;

public int getPage() {
return page;
}
public void setPage(int page) {
this.page = page;
}


public String execute(){
final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
final String DB_URL = "jdbc:mysql://localhost:3306/sakila";
final String USER = "root";
final String PASS = "root";
Connection conn = null;
ArrayList <Pojo> film = new ArrayList<Pojo>();
int count=0;
int page=getPage();
try {
//register JDBC driver
Class.forName("com.mysql.cj.jdbc.Driver");
//open a connection
conn = DriverManager.getConnection(DB_URL,USER,PASS);
Statement statement = conn.createStatement();
ResultSet rs1 = statement.executeQuery("SELECT COUNT(title) FROM film where isDeleted=0");
while(rs1.next()) {count=rs1.getInt("COUNT(title)");}
int records_per_page = 10;
Statement statement1 = conn.createStatement();
ResultSet rs = statement1.executeQuery("SELECT film.film_id,film.title,film.release_year,film.special_features,film.rating,language.name,film.director,film.description FROM film JOIN LANGUAGE ON film.language_id=language.language_id where isDeleted=0 LIMIT "+(page-1)*records_per_page+","+records_per_page);
while(rs.next())
{
Pojo ob = new Pojo();
ob.setFilmId(rs.getInt("film_id"));
ob.setTitle(rs.getString("title"));
ob.setReleaseYear(rs.getInt("release_year"));
ob.setSpecialFeatures(rs.getString("special_features"));
ob.setRating(rs.getString("rating"));
ob.setLanguage(rs.getString("name"));
ob.setDirectorName(rs.getString("director"));
ob.setDescription(rs.getString("description"));
film.add(ob);
}
}catch(Exception e) {
//handle error for class.forName
e.printStackTrace();
}
Gson gson = new Gson();
JsonArray arr= gson.toJsonTree(film).getAsJsonArray();
JsonObject obj = new JsonObject();
obj.addProperty("total",count);
obj.add("data",arr);
System.out.print(obj);
return "success";
}
}