I've MySQL table which contains list of files I need to populate in a table on my jsp page. I've attached the screen shot of table contents. I was referring example given here. But example given is just for one file. It would be really helpful if experts can point me to an example to follow... Thank you very much in advance.
First Step: Database to Controller: Grab Data from your database by creating DAO(data access Object) For Example:
public class Document {
String document_name;
String document_date;
String document_RevisedName;
public String getDocument_name() {
return document_name;
}
public void setDocument_name(String document_name) {
this.document_name = document_name;
}
public String getDocument_date() {
return document_date;
}
public void setDocument_date(String document_date) {
this.document_date = document_date;
}
public String getDocument_RevisedName() {
return document_RevisedName;
}
public void setDocument_RevisedName(String document_RevisedName) {
this.document_RevisedName = document_RevisedName;
}
}
Create a Interface Document_DAO:
public interface Document_DAO {
ArrayList<Document>getAllDocuments();
}
Implement above Interface to a Implementation Class Document_DAO_Imp
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.mysql.jdbc.Connection;
import Utils.DataSource;
import dao_class.Class;
public class Docuemnt_DAO_IMp implements Document_DAO{
Connection conn = null;
DataSource dataSource = new DataSource();
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
String query= "";
ArrayList<Document> documentList= new ArrayList<>();
@Override
public ArrayList<Document> getAllDocuments() {
conn = (Connection) dataSource.createConnection();
try{
query = "SELECT * FROM youTableName";
preparedStatement = conn.clientPrepareStatement(query);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
Document doc= new Document();
doc.setDocument_date(resultSet.getString("youRowName"));
doc.setDocument_RevisedName(resultSet.getString("youRowName"));
documentList.add(doc);
}
}
catch(SQLException sqlE){
System.out.println(sqlE.toString());
}
finally {
try {
resultSet.close();
preparedStatement.close();
conn.close();
} catch (SQLException e) {
System.out.println(e.toString());
}
}
return documentList;
}
}
Finally: Call above DAO from your Controller/Servlet.
ArrayList<Document> docList = new ArrayList<>();
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
Document_DAO dao = new Document_DAO_Imp();
docList = dao.getAllDocuments();
JsonObject jsonObject = new JsonObject();
JsonElement jsonElement = null;
if (classList.size()!=0) {
jsonElement = new Gson().toJsonTree(docList);
jsonObject.add("docList", jsonElement);
jsonObject.addProperty("success", "true");
}
else{
jsonObject.addProperty("success", "true");
}
response.getWriter().println(jsonObject);
}
}
Till Now You have successfully,Grabed data from DB and Send to you JSP/HTML Page. Make Sure to add GSON a google library to add to you built path and WEB-INF/lib folder.
Now Using jquery we can parse result to page and can also download them in XLS format.
To Fetch Data using Jquery Ajax you can either provide a event or you can load data on page load. $(document).load(function(){ });
$.ajax({
type:"get",
url : "DocumentServlet",
dataType: 'json',
// if received a response from the server
success : function(data, textStatus, jqXHR) {
console.log("Getting Document Information!");
if (data.success == 'true') {
data = data.docList;
console.log(data);
$('.table').html("");
$.each(data, function(index, value) {
var eachrow = "<tr><td>"+value.documentName
+"</td><td>"+value.documentRevisedName
+"</td></tr>";
$('.table').append(eachrow);
});
}else{
console.log("failure");
}
},
error : function(jqXHR, textStatus, errorThrown) {
}
});
One you successfully populate JSON array to tables.
Then you can use any external Library to convert data to XLS and download. First use this Structure to create a page,,Then i'll provide code to download
