Forum:

JDBC and Relational Databases

jdbc resultset to xml

Greenhorn

Posts: 5

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hi,
I have to insert data into a database table using jsp and jdbc, query the table data and convert resultset into an xml file. I am using NetBeans IDE. I'm really stuck and i have a project due. Please do help.

Thanks in advance.

Paul Sturrock

Bartender

Posts: 10336

I like...

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

What have you written so far? What are you stuck on?

JavaRanch FAQ HowToAskQuestionsOnJavaRanch

shru win

Greenhorn

Posts: 5

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

I have to convert resultset into xml file. Inserting into database table and querying the contents of the table is done. But unable to generate xml file.

. Please help.

Paul Sturrock

Bartender

Posts: 10336

I like...

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

XML is just well formatted text data. Are you familiar with the java.io package and writing to files? And are you comfortable going through a ResultSet and accessing its contents?

JavaRanch FAQ HowToAskQuestionsOnJavaRanch

amit punekar

Ranch Hand

Posts: 544

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hello,
I think Oracle gives you ability to fetch the data as XML itself. Might be helpful if you are fetching the resultset from Oracle.

Regards,
Amit

shru win

Greenhorn

Posts: 5

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Thanks for your reply.

I am using postgresql. I'm pretty comfortable with resultset. I'm using netbeans IDE. This can be easily done using PHP.But in netbeans i'm unable to use php in java projects. So stuck.

Paul Sturrock

Bartender

Posts: 10336

I like...

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

OK. So the steps will be:

move through your ResultSet and build up your formatted XML as a String

write it out to a file

Have you written the first bit? Or is that where you are stuck?

JavaRanch FAQ HowToAskQuestionsOnJavaRanch

shru win

Greenhorn

Posts: 5

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Actually that is where am stuck.

Paul Sturrock

Bartender

Posts: 10336

I like...

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

OK, well the JDBC tutorial will show you how to access things from your ResutlSet, and the basic io second of the Java tutorial will show you how to do the other other part.

JavaRanch FAQ HowToAskQuestionsOnJavaRanch

shru win

Greenhorn

Posts: 5

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

I'v attached my code along with this. It's not working. This is the just a sample code i craetes to learn how it works. Inserting into database is perfectly working.
Converting to xml is not. Please do tel me what m doing wrong.

<%@page contentType="text/html"%>
<%@page pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<%@page import="java.sql.*"%>
<%@page import="java.util.*"%>
<%@page import="java.io.*"%>
<%@page import="javax.xml.parsers.*"%>
<%@page import ="javax.xml.transform.*"%>
<%@page import=" org.w3c.dom.Document"%>
<%@page import=" org.w3c.dom.Element"%>
<%@page import=" javax.xml.transform.dom.DOMSource"%>
<%@page import="javax.xml.transform.stream.StreamResult"%>
<html xmlns="http://www.w3.org/1999/xhtml">
    <body>
       <% try {
                Class.forName("org.postgresql.Driver");
            }catch (ClassNotFoundException e) {
                out.println("<h1>Driver not found:" + e + e.getMessage() + "</h1>" );
            }
       try {
Connection conn = DriverManager.getConnection("jdbc:postgresql://192.20.14.15/masdata","lilavati", "lilavati" );
Statement stmt = conn.createStatement();
String snamee  = request.getParameter("sname");
String sidd = request.getParameter("sid");
String marks = request.getParameter("marks");
stmt.executeQuery("insert into stud values('"+snamee+"','"+sidd+"','"+marks+"')");
ResultSet rs = stmt.executeQuery("select *from stud");

DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
   DocumentBuilder builder        = factory.newDocumentBuilder();
   Document doc                   = builder.newDocument();
Element results = doc.createElement("Results");
   doc.appendChild(results);

ResultSetMetaData rsmd = rs.getMetaData();
   int colCount           = rsmd.getColumnCount();

while (rs.next())
   {
      Element row = doc.createElement("Row");
      results.appendChild(row);

for (int i = 1; i <= colCount; i++)
      {
         String columnName = rsmd.getColumnName(i);
         Object value      = rs.getObject(i);

Element node      = doc.createElement(columnName);
         node.appendChild(doc.createTextNode(value.toString()));
         row.appendChild(node);
      }
   }

stmt.close();
conn.close();
return doc;
            }catch (Exception e) {
                System.out.println( "<h1>exception: "+e+e.getMessage()+"</h1>" );
            }%>
        <form id="stud" action ="" method="post">
                 <table id="pass">
                    <tr align="left">
                        <td><b><h3> Name</h3></b></td>
                        <td><input type="text" name="sname"  size="20"/></td>
                    </tr>
                    <tr align="left">
                        <td><b><h3>id</h3></b></td>
                        <td><input type="text" name="sid"  size="20"/></td>
                    </tr>
                    <tr align="left">
                        <td><b><h3>Marks</h3></b></td>
                        <td><input type="text" name="marks"  size="20"/></td>
                    </tr>
                    <tr align="left">
                        <td><input type="submit" name="ok"/></td>
                    </tr>
                </table>
        </form>
    </body>
</html>

Paul Sturrock

Bartender

Posts: 10336

I like...

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

A couple of points: saying "its not working" makes it difficult to help. You need to tell us what is going wrong, especially if there is a stack trace. The other is code in a JSP is bad. I know this is just to learn, but it actually makes things harder to develop.

That aside, I don't see any code that saves or streams your Document?

JavaRanch FAQ HowToAskQuestionsOnJavaRanch

My name is Inigo Montoya, you killed my father, prepare to read a tiny ad:

We need your help - Coderanch server fundraiser

https://coderanch.com/wiki/782867/Coderanch-server-fundraiser