Error while parsing and storing xml data in databa

2019-08-27 03:50发布

站内问答 / 后端开发
1393 2 5

I have to read an xml file, display its content in the form of a table and need to store the data in database table. Firstly I tried to only display the data using jstl1.2 tags in my jsp. It is working fine. But when I tried to implement some code to insert the records I am getting this error..

 Can't infer the SQL type to use for an instance of org.apache.taglibs.standard.tag.common.xml.JSTLNodeList. Use setObject() with an explicit Types value to specify the type to use.

My Jsp code is as:

<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/xml" prefix="x" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>

<sql:setDataSource var="dataSource" driver="org.postgresql.Driver"
     url="jdbc:postgresql://localhost:5432/postgres" user="postgres" password="shail"
     scope="session" />

<html>
  <head>
    <title>cust_xml</title>
  </head>
  <body>

    <h1>Customer Information</h1>

    <c:import var="xmlFile" url="cust.xml"  charEncoding="UTF-8"/>
    <x:parse var="myDoc" xml="${xmlFile}" />

    <table border="1">
      <tr>
        <th>Customer Number</th>
        <th>First Name</th>
        <th>Last Name</th>
        <th>Street</th>
        <th>City</th>
        <th>State</th>
        <th>Zipcode</th>
        <th>Balance</th>
      </tr>
      <x:forEach select="$myDoc/CUSTOMERS/Customer" var="cust">
        <tr>
             <td>
              <x:out select="$cust/CustNo" />
              <x:set var="custno" select="$cust/CustNo" scope="session"/>
             </td>
             <td>
              <x:out select="$cust/CustFirstName" />
              <x:set var="custfn" select="$cust/CustFirstName" scope="session"/>
             </td>
             <td>
              <x:out select="$cust/CustLastName" />
              <x:set var="custln" select="$cust/CustLastName" scope="session"/>
             </td>
             <td>
              <x:out select="$cust/CustStreet" />
              <x:set var="custst" select="$cust/CustStreet" scope="session"/>
             </td>
             <td>
              <x:out select="$cust/CustCity" />
              <x:set var="custcity" select="$cust/CustCity" scope="session"/>
             </td>
             <td>
              <x:out select="$cust/CustState" />
              <x:set var="custstate" select="$cust/CustState" scope="session"/>
             </td>
             <td>
              <x:out select="$cust/CustZip" />
              <x:set var="custz" select="$cust/CustZip" scope="session"/>
             </td>
             <td>
              <x:out select="$cust/CustBal" />
              <x:set var="custbal" select="$cust/CustBal" scope="session"/>
             </td>
        </tr>
      </x:forEach>
    </table>

    <sql:update dataSource="${dataSource}" var="updatedTable">
        INSERT INTO data  VALUES (?,?,?,?,?,?,?,?);
        <sql:param value="${custno}" />
        <sql:param value="${custfn}" />
        <sql:param value="${custln}" />
        <sql:param value="${custst}" />
        <sql:param value="${custcity}" />
        <sql:param value="${custstate}" />
        <sql:param value="${custz}" />
        <sql:param value="${custbal}" />
    </sql:update>
    <c:if test="${updatedTable>=1}">
     <font size="5" color='green'> Congratulations ! Data inserted successfully.</font>
    </c:if>

  </body>
</html>

Please provide your suggestion which tag shall I use to set the values to insert query parameters? How to get the xml elements data to set in query parameters? Thanks & Regards

2条回答
Luminary・发光体
2楼-- · 2019-08-27 04:23

I tried hard to get the solution of my problem but unfortunately couldn't get any help. While googling I got some basic idea to cope with this. I tried this

<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/xml" prefix="x" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>

<sql:setDataSource var="dataSource" driver="org.postgresql.Driver"
     url="jdbc:postgresql://localhost:5432/postgres" user="postgres" password="shail"
     scope="session" />

<html>
  <head>
    <title>cust_xml</title>
  </head>
  <body bgcolor="skyblue">

    <h1 align="center">Customer Information Stored In XMl File</h1>

    <c:import var="xmlFile" url="cust.xml"  charEncoding="UTF-8"/>
    <x:parse var="myDoc" xml="${xmlFile}" />

    <table border="1" cellspacing="1" cellpadding="1" bgcolor="pink" align="center">
      <tr>
        <th>Customer Number</th>
        <th>First Name</th>
        <th>Last Name</th>
        <th>Street</th>
        <th>City</th>
        <th>State</th>
        <th>Zipcode</th>
        <th>Balance</th>
      </tr>
      <x:forEach select="$myDoc/CUSTOMERS/Customer" var="cust">
        <tr>
             <td>
              <c:set var="custno" >
              <x:out select="$cust/CustNo" />
              </c:set>
              <x:out select="$cust/CustNo" />
             </td>
             <td>
              <c:set var="custfn" >
              <x:out select="$cust/CustFirstName" />
              </c:set>
              <x:out select="$cust/CustFirstName" />
             </td>
             <td>
              <c:set var="custln" >
              <x:out select="$cust/CustLastName" />
              </c:set>
              <x:out select="$cust/CustLastName" />
             </td>
             <td>
              <c:set var="custst" >
              <x:out select="$cust/CustStreet" />
              </c:set>
              <x:out select="$cust/CustStreet" />
             </td>
             <td>
              <c:set var="custcity" >
              <x:out select="$cust/CustCity" />
              </c:set>
              <x:out select="$cust/CustCity" />
             </td>
             <td>
              <c:set var="custstate" >
              <x:out select="$cust/CustState" />
              </c:set>
              <x:out select="$cust/CustState" />
             </td>
             <td>
              <c:set var="custz" >
              <x:out select="$cust/CustZip" />
              </c:set>
              <x:out select="$cust/CustZip" />
             </td>
             <td>
              <c:set var="custbal" >
              <x:out select="$cust/CustBal" />
              </c:set>
              <x:out select="$cust/CustBal" />
             </td>
        </tr>
        <sql:update dataSource="${dataSource}" var="updatedTable">
        INSERT INTO data  VALUES (?,?,?,?,?,?,?,?);
        <sql:param value="${custno}" />
        <sql:param value="${custfn}" />
        <sql:param value="${custln}" />
        <sql:param value="${custst}" />
        <sql:param value="${custcity}" />
        <sql:param value="${custstate}" />
        <sql:param value="${custz}" />
        <sql:param value="${custbal}" />

    </sql:update>
    <c:set var="rowcount" value="${updatedTable+rowcount}"/>
      </x:forEach>

    <c:if test="${rowcount>=1}">
      <tr>
       <td colspan="8" align="center">
     <font size="5" color='red' align="center">
     Congratulations ! <c:out value="${rowcount}" /> Rows inserted successfully  in DB Table.</font>
       </td>
      </tr>
    </c:if>
    </table>
  </body>
</html>

But I got this error:

org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: 
        INSERT INTO data  VALUES (?,?,?,?,?,?,?,?);
ERROR: column "balance" is of type integer but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.

As in my table balance(last column) is integer type. When I googled I got that jstl tags automatically take care of casting. But it is showing error as by default <c:set > tag places value for variable as string. Any idea? Any help? Any suggestion? Please let me know.

查看更多
0人赞 添加讨论(0) 举报
神经病院院长
3楼-- · 2019-08-27 04:38

You can enclose the ? with CAST function and still use setObject() method.
Let me give you an example While storing IP address in MySQL my query use to be like this

INSERT INTO log (ip,logged_on) VALUES(inet6_aton(?),no());

PS: inet6_aton() for MySQL 5.6+ else use inet4_aton() or just inet_aton()

Now I shifted to Postgres and it doesn't have inet_aton function but inet as a datatype. Unfortunately, there is not setInet() method so I'd to do this

INSERT INTO log (ip,looged_on) VALUES(CAST(? AS INET),now());

With this I can use setString() or setObject() while passing the parameters in JDBC or with JSTL

INSERT INTO data VALUES (?,?,?,?,?,?,?,?);

Since you haven't mentioned the column names like I did in my example I don't know which of the column needs to be typecast but you can try by writing it like this

 INSERT INTO data VALUES (CAST(? AS INTEGER),?,?,?,?,?,?,?);

I'm just assuming the first column of your table as balance please do this for the appropriate parameter depending upon the position of balance column in your table.

查看更多
0人赞 添加讨论(0) 举报
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(5)