Hi all
I have a problem to export my SQL query is resulty into an XML file I had fixed my servlet and JSP so that i can display all the records into my database and that the goal .Now I want to get the result set into JSP so that i can create an XML file from that result set from the jsp code.
thisis my servlet which will call the jsp page and the jsp just behind it.
//this is the servlet
*
import java.io.*;
**
import java.lang.reflect.Array;
*
*
import java.sql.*;
**
import java.util.ArrayList;
**
import java.util.logging.Level;
**
import java.util.logging.Logger;
*
*
import javax.servlet.*;
**
import javax.servlet.http.*;
**
import javax.naming.*;
**
import javax.sql.*;
*
*
public *class *Campaign *extends *HttpServlet
*
{
*private* *final* *static* Logger +log+ = Logger.+getLogger+(Campaign.*class*.getName());
*private* *final* *static* String +DATASOURCE_NAME+ = "jdbc/SampleDB";
*private* DataSource _dataSource;
*public* *void* setDataSource(DataSource dataSource)
{
_dataSource = dataSource;
}
*public* DataSource getDataSource()
{
*return* _dataSource;
}
*public* *void* init()
*throws* ServletException
{
*if* (_dataSource == *null*) {
*try* {
Context env = (Context) *new* InitialContext().lookup("java:comp/env");
_dataSource = (DataSource) env.lookup(+DATASOURCE_NAME+);
*if* (_dataSource == *null*)
*throw* *new* ServletException("`" + +DATASOURCE_NAME+ + "' is an unknown DataSource");
} *catch* (NamingException e) {
*throw* *new* ServletException(e);
}
}
}
*
protected *void *doGet(HttpServletRequest request, HttpServletResponse response)
**
throws IOException, ServletException
*
{
Connection conn = *null*;
*try* {
conn = getDataSource().getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select post_id,comments,postname from app.posts");
// out.println("Le résultat :<br>");
ArrayList <String> Lescomments= *new* ArrayList<String>();
ArrayList <String> Lesidentifiant = *new* ArrayList<String>();
ArrayList <String> Lesnoms = *new* ArrayList <String>();
*while* (rs.next()) {
Lescomments.add(rs.getString("comments"));
request.setAttribute("comments",Lescomments);
Lesidentifiant.add(rs.getString("post_id"));
request.setAttribute("id",Lesidentifiant);
Lesnoms.add(rs.getString("postname"));
request.setAttribute("nom",Lesnoms);
}
rs.close();
stmt.close();
}
*catch* (SQLException e) {
}
*finally* {
*try* {
*if* (conn != *null*)
conn.close();
}
*catch* (SQLException e) {
}
// les paramètres sont corrects - on envoie la page réponse
getServletContext().getRequestDispatcher("/Campaign.jsp").forward(request,response);
}
}
}///end of servlet
}///this is the jsp page called
<%@ page import="java.util.ArrayList" %>
<%
// on récupère les données
ArrayList nom=(ArrayList)request.getAttribute("nom");
ArrayList id=(ArrayList)request.getAttribute("id");
ArrayList comments=(ArrayList) request.getAttribute("comments");
%>
<html>
<head>
<title></title>
</head>
<body>
Liste des campagnes here i will create the xml file the problem is to display all rows
<hr>
<table>
<tr>
</tr>
<tr>
<td>Comment</td>
<td>
<%
for( int i=0;i<comments.size();i++){
out.print("<li>" + (String) comments.get(i) + "</li>\n");
}//for
%>
</tr>
<tr>
<td>nom</td>
<td>
<%
for( int i=0;i<nom.size();i++){
out.print("<li>" + (String) nom.get(i) + "</li>\n");
}//for
%>
</tr>
<tr>
<td>id</td>
<td>
<%
for( int i=0;i<id.size();i++){
out.print("<li>" + (String) id.get(i) + "</li>\n");
}//for
%>
</tr>
</table>
</body>
</html>
This is how i used to create an XML file in a JSP page only without JSP/SERVLET concept:
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<%
// Identify a carriage return character for each output line
*
int iLf = 10;
**
char cLf = (*char*)iLf;
*
// Create a new empty binary file, which will content XML output
File outputFile = *new* File("C:\\Users\\user\\workspace1\\demo\\WebContent\\YourFileName.xml");
//outputFile.createNewFile();
FileWriter outfile = *new* FileWriter(outputFile);
// the header for XML file
outfile.write("<?xml version='1.0' encoding='ISO-8859-1'?>"+cLf);
*
try {
*
// Define connection string and make a connection to database
Connection conn = DriverManager.getConnection("jdbc:derby://localhost:1527/SAMPLE","app","app");
Statement stat = conn.createStatement();
// Create a recordset
ResultSet rset = stat.executeQuery("Select * From posts");
// Expecting at least one record
*if*( !rset.next() ) {
*throw* *new* IllegalArgumentException("No data found for the posts table");
}
outfile.write("<Table>"+cLf);
// Parse our recordset
// Parse our recordset
*while*(rset.next()) {
outfile.write("<posts>"+cLf);
outfile.write("<postname>" + rset.getString("postname") +"</postname>"+cLf);
outfile.write("<comments>" + rset.getString("comments") +"</comments>"+cLf);
outfile.write("</posts>"+cLf);
}
outfile.write("</Table>"+cLf);
// Everything must be closed
rset.close();
stat.close();
conn.close();
outfile.close();
}
*
catch( Exception er ) {
*
}
%>