Datatype exception thrown in jsp

Question

I'm writing a webapp where in there is a table that is to be generated using Ajax. This data is actually pulled from the database. And below are my codes.

Index.jsp

<html>
<head>
</head>
<body>
    <marquee>
        <h1>This is an example of ajax</h1>
    </marquee>
    <form name="vinform">
        Enter id:<input type="button" id="somebutton" value="Click Me">
    </form>

    <span id="somediv"> </span>
    <script src="//code.jquery.com/jquery-1.10.2.js"></script>
    <script src="//code.jquery.com/ui/1.10.4/jquery-ui.js"></script>
    <script type="text/javascript" src="tableGenerator.js"></script>
</body>
</html>

tableGenerator.js

$('#somebutton').click(
        function() {
            $.getJSON('Controller', function(searchList) {
                var $table = $('<table>').appendTo($('#somediv'));
                $.each(searchList, function(index, userBean) {
                    $('<tr>').appendTo($table).append(
                            $('<td>').text(userBean.caseNumber)).append(
                            $('<td>').text(userBean.caseOwner)).append(
                            $('<td>').text(userBean.status)).append(
                            $('<td>').text(userBean.issue)).append(
                            $('<td>').text(userBean.reason)).append(
                            $('<td>').text(userBean.dateOpened));
                });
            });
        });

UserBean.java

package org.bean;

public class UserBean {
    private int age;
    private String caseOwner, status, issue, reason, dateOpened, caseNumber, resolution, finalStatus, startDate,
            endDate;
    private Double totalTimeTaken;

    public String getStartDate() {
        return startDate;
    }

    public void setStartDate(String startDate) {
        this.startDate = startDate;
    }

    public String getEndDate() {
        return endDate;
    }

    public void setEndDate(String endDate) {
        this.endDate = endDate;
    }

    public String getCaseNumber() {
        return caseNumber;
    }

    public void setCaseNumber(String caseNumber) {
        this.caseNumber = caseNumber;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getCaseOwner() {
        return caseOwner;
    }

    public void setCaseOwner(String caseOwner) {
        this.caseOwner = caseOwner;
    }

    public String getStatus() {
        return status;
    }

    public String getReason() {
        return reason;
    }

    public void setReason(String reason) {
        this.reason = reason;
    }

    public void setStatus(String status) {
        this.status = status;
    }

    public String getIssue() {
        return issue;
    }

    public void setIssue(String issue) {
        this.issue = issue;
    }

    public String getDateOpened() {
        return dateOpened;
    }

    public void setDateOpened(String dateOpened) {
        this.dateOpened = dateOpened;
    }

    public String getResolution() {
        return resolution;
    }

    public void setResolution(String resolution) {
        this.resolution = resolution;
    }

    public String getFinalStatus() {
        return finalStatus;
    }

    public void setFinalStatus(String finalStatus) {
        this.finalStatus = finalStatus;
    }

    public double getTotalTimeTaken() {
        return totalTimeTaken;
    }

    public void setTotalTimeTaken(Double totalTimeTaken) {
        this.totalTimeTaken = totalTimeTaken;
    }

    public UserBean() {

    }

    public UserBean(String caseNumber, String caseOwner, String issue, int age, String reason, String dateOpened,
            String status, String finalStatus, String resolution, String startDate, String endDate,
            Double totalTimeTaken) {
        this.caseNumber = caseNumber;
        this.caseOwner = caseOwner;
        this.issue = issue;
        this.reason = reason;
        this.age = age;
        this.dateOpened = dateOpened;
        this.status = status;
        this.resolution = resolution;
        this.finalStatus = finalStatus;
        this.startDate = startDate;
        this.endDate = endDate;
        this.totalTimeTaken = totalTimeTaken;
    }
}

Controller.java(Servlet)

import java.io.IOException;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.bean.UserBean;

import com.dao.DataDao;
import com.google.gson.Gson;

@WebServlet("/Controller")
public class Controller extends HttpServlet {
    private static final long serialVersionUID = 1L;

    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        try {
            // String id = request.getParameter("val");

            DataDao dataDao = new DataDao();
            ArrayList<UserBean> list = dataDao.getFrameWork();
            String searchList = new Gson().toJson(list);
            response.setContentType("application/json");
            response.setCharacterEncoding("UTF-8");
            response.getWriter().write(searchList);
        } catch (Exception e) {
            System.err.println(e.getMessage());
        }
    }
}

DBUtility.java

package com.dao;

import java.sql.Connection;
import java.sql.DriverManager;

public class DBUtility {
    private static Connection connection = null;

    public static Connection getConnection() throws Exception {
        if (connection != null)
            return connection;
        else {
            // Store the database URL in a string
            String userName = "sa";
            String password = "T!ger123";
            String url = "jdbc:sqlserver:XXXXXX;DatabaseName=TEST";

            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

            // set the url, username and password for the databse
            connection = DriverManager.getConnection(url, userName, password);
            return connection;
        }
    }
}

DataDao.java

package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.bean.UserBean;

public class DataDao {
    private Connection connection;

    public DataDao() throws Exception {
        connection = DBUtility.getConnection();
    }

    public ArrayList<UserBean> getFrameWork() throws SQLException {
        ArrayList<UserBean> list = new ArrayList<UserBean>();
        PreparedStatement ps = null;
        try {

            ps = connection.prepareStatement("select * from statusTable");
            // ps.setString(1, frameWork);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                UserBean userBean = new UserBean();
                userBean.setCaseNumber(rs.getString(1));
                userBean.setCaseOwner(rs.getString(2));
                userBean.setStatus(rs.getString(3));
                userBean.setIssue(rs.getString(4));
                userBean.setReason(rs.getString(5));
                userBean.setDateOpened(rs.getString(6));
                userBean.setAge(rs.getInt(7));
                list.add(userBean);
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return list;
    }
}

I'm new to using this Ajax/Jquery/JSON technologies. And I've followed the tutorial How to use Servlets and Ajax? by @BalusC.

Here I've 2 queries.

When i place the below block in <head>, it doesn't work.

<script src="//code.jquery.com/jquery-1.10.2.js"></script>
<script src="//code.jquery.com/ui/1.10.4/jquery-ui.js"></script>
<script type="text/javascript" src="tableGenerator.js"></script>

When i place the same above code in <body>, it is throwing me the below exception.

An error occurred while converting the varchar value to JDBC data type INTEGER.

when i did a sp_help statusTable(this is sqlserver query equivalent to Oracle's desc statusTable). And i get the result as shown in the below screenshot.

Unable to understand where there is a datatype mismatch.

please let me know where am i going wrong and how to fix it.

Thanks

score 1 · Accepted Answer · answered Jan 07 '16 at 09:48

There seems to be a mismatch between the column order as it is listed by SQL server, and the one in your JDBC result set. In particular, it seems that column 7 does not correspond to the Age column.

To check which column name corresponds to which column number, you can use the result set's metadata:

 ResultSetMetaData meta = rs.getMetaData();
 String name = meta.getColumnName(7);

It's probably wiser to either:

list the column names explicitly in your query instead of using SELECT *; or
retrieve data by specifying the column's name instead of its index number.

As to your first issue, if you put your Javascript in the header, the #somebutton clickhandler is instantiated before the element is loaded in the DOM, hence it doesn't work.

Datatype exception thrown in jsp

1 Answers1