Forum:

JDBC and Relational Databases

Retrieve data from database using jdbc

Ranch Hand

Posts: 146

posted 8 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

i am trying to retrieve the data from the database based on form input .there is no error when compile,debug and run .i shows me a textfield if i type the text and enter submit.,the url changes but the page is empty..there is no error in the console..can anyone tell me the place where it goes wrong

index.jsp

main.jsp

<%@ page language="java" import="java.sql.*" %>
 
 <jsp:include page="index.jsp"/>  
  
<% String searchCondition = request.getParameter("cond"); 
   if (searchCondition != null) { %>
    
      <H3> Search results for  <I> <%= searchCondition %> </I> </H3>
      <B> <%= runQuery(searchCondition) %> </B> <HR><BR>
<% } 
 
 
 
%>
            
      <%! private String runQuery(String cond) throws SQLException,ClassNotFoundException{
     Connection conn = null; 
     Statement stmt = null; 
     ResultSet rset = null; 
 
      
     try {
 Class.forName("com.mysql.jdbc.Driver");
       // DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/database name","root","admin321"); 
        stmt = conn.createStatement();
        // dynamic query
        rset = stmt.executeQuery ("SELECT *  FROM table "+ 
                           (cond.equals("") ? "" : "WHERE " + cond ));
       return (formatResult(rset));
     } catch (SQLException e) { 
         return ("<P> SQL error: <PRE> " + e + " </PRE> </P>\n");
     } finally {
         if (rset!= null) rset.close(); 
         if (stmt!= null) stmt.close();
         if (conn!= null) conn.close();
     }
  }
  private String formatResult(ResultSet rset) throws SQLException {
    StringBuffer sb = new StringBuffer();
    if (!rset.next())
      sb.append("<P> No matching rows.<P>\n");
    else {  sb.append("data goes here"); 
            do {  sb.append(rset.getString("name") + 
                           rset.getString("child name") );
            } while (rset.next());
         
    }
    return sb.toString();
  }
%>

Dave Tolls

Rancher

Posts: 4801

posted 8 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

First off I would suggest removing all that code from the JSP and placing it in a servlet.
Have the servlet forward the result to a JSP page to display it.
That way you at least have a chance of testing this.
I would also suggest logging some things on the server, including the sql statement you have built.
That SQL is also open to SQL injection, but that's not really solvable since you are sending an entire WHERE clause as a parameter.

In any case, as is, are you sure that the correct value is being sent to the server?

I don't see any html element on that first page that has an id of 'cond'.
I see a field on the form that has a name of 'cond', but that's not the same thing.

surya preethaaa

Ranch Hand

Posts: 146

posted 8 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

yes you are right. i have to use getElementByName
here..i have to get the database what i have typed in text field

var id=document.getElementById("cond").value;

Dave Tolls

Rancher

Posts: 4801

posted 8 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

There's too much going on with this as it is at the moment.

The first thing I would do is forget the Javascript bit for now, and simply submit the form.
Give the form the URL as its action.

That will mean you can avoid any Javascript errors and see if the code on your server works.

I would still move all the Java code out of the JSP page and into a servlet. There should be no Java code in your JSP page.
It will make it a lot easier to debug.

surya preethaaa

Ranch Hand

Posts: 146

posted 8 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

thank you...let me try your idea

surya preethaaa

Ranch Hand

Posts: 146

posted 8 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Dave Tolls thank you ..i got ..but i want to select all rows value containing same column name ..i used WHERE IN but getting only one row related to the INPUT.

surya preethaaa

Ranch Hand

Posts: 146

posted 8 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

ResultSet resultset =
statement.executeQuery("select * from t where name LIKE %"+id+"%"); is this correct?

Knute Snortum

Sheriff

Posts: 7126

185

I like...

posted 8 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

The above SELECT will return rows with the id anywhere in the name. Is this what you want? Does it return the rows you're looking for?

All things are lawful, but not all things are profitable.

surya preethaaa

Ranch Hand

Posts: 146

posted 8 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

actually i used id there that represent
String id=request.getParameter("input")
i want to get the data which is typed in the text box named "input".
i want to select all rows containing the same NAME

EG)

TEAM LEADER. TEAM MEMBERS
jon Hari
Jon. deva
Jon. Rahul
Suresh Raina
Suresh. Raksha

I want to get all information from the rows related to jon
So I used LIKE in MYSQL command
But it gives error..

Paul Clapham

Sheriff

Posts: 28395

100

I like...

posted 8 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

surya preethaaa wrote:But it gives error..

That's great! Now you're getting somewhere.

Java 8 (verified skill)
Skill verified by Paul Clapham

Knute Snortum

Sheriff

Posts: 7126

185

I like...

posted 8 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Could you post the error and all relevant messages?

All things are lawful, but not all things are profitable.

surya preethaaa

Ranch Hand

Posts: 146

posted 8 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

this is what the page shows

HTTP Status 500 - An exception occurred processing JSP page /main.jsp at line 25

type Exception report

message An exception occurred processing JSP page /main.jsp at line 25

description The server encountered an internal error that prevented it from fulfilling this request.

exception

org.apache.jasper.JasperException: An exception occurred processing JSP page /main.jsp at line 25

22: String id = request.getParameter("name");
23:
24: ResultSet resultset =
25: statement.executeQuery("select * from t where LIKE %" + id + "%'");
26:
27: if(!resultset.next()) {
28: out.println("Sorry, could not find . ");

Stacktrace:
org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:584)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:466)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:385)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:329)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
root cause

javax.servlet.ServletException: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIKE %à®…à®•à®²à¯�%'' at line 1
org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:909)
org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:838)
org.apache.jsp.main_jsp._jspService(main_jsp.java:188)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:443)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:385)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:329)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
root cause

com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIKE %à®…à®•à®²à¯�%'' at line 1
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
com.mysql.jdbc.Connection.execSQL(Connection.java:3277)
com.mysql.jdbc.Connection.execSQL(Connection.java:3206)
com.mysql.jdbc.Statement.executeQuery(Statement.java:1232)
org.apache.jsp.main_jsp._jspService(main_jsp.java:136)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:443)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:385)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:329)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
note The full stack trace of the root cause is available in the Apache Tomcat/8.5.9 logs.

Paul Clapham

Sheriff

Posts: 28395

100

I like...

posted 8 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

At least two problems.

First of all you're missing some quotes in your SQL. And you're missing other stuff too. You want your query to look like

But your query looks more like

(Do you really have a table named "t"? That doesn't seem like a very useful name to me.)

And second, your "id" value seems to have a whole bunch of garbage in it, although you seem to be expecting ordinary text. I have no idea where that's coming from, so perhaps examining your code (both front-end and back-end) to make sure no unnecessary transformations are taking place would be a good idea.

surya preethaaa

Ranch Hand

Posts: 146

posted 8 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

thanks for your reply..

surya preethaaa

Ranch Hand

Posts: 146

posted 8 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

on the other side ,

when i call jsp output in the d3
this is how i call jsp in D3
myJSON = [ (
<% while (resultset.next()) { %>
<%= resultset.getString("name") %> ,
<%= resultset.getString("child name") %>,
<%= resultset.getString("child color") %> ,
<%= resultset.getString("children t") %> ,
<%= resultset.getString("child l") %> ,
<%= resultset.getString("child child name") %> ,
<%= resultset.getString("child child color") %>

<% } %>
) ];

BUT dont know why it shows error

அகல் ,
நீங்கு,
red ,
நீங்குதல், பாயிருளகல (புறநா.25). , ---> in this line as Uncaught SyntaxError: missing ) after argument list
red ,
விலகு ,
red
any suggestions over here?

Paul Clapham

Sheriff

Posts: 28395

100

I like...

posted 8 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

You're using that antique version of JSP which has been obsolete for well over 10 years now -- why are you doing that? You ought to be using JSTL and EL, which support SQL a lot better than what you have to do.

But anyway it appears that your JSP is trying to generate a JSON expression. But remember that JSP generates Java, so you're going to have to generate Java code which generates that JSON expression. This bit which I can see at the beginning:

isn't correct Java. Is myJSON a String variable? If so then your Java needs to start like this:

This is typical of the problems that we used to have when generating Java from the antique JSP with scriptlets. Quoting things was a nightmare (and clearly it still is). It's way easier with JSTL, I strongly recommend you rewrite your code to use that.

surya preethaaa

Ranch Hand

Posts: 146

posted 8 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

thanks for your reply..i will try with JSTL..I am going to use this for the first time .....i will show the result with jstl

surya preethaaa

Ranch Hand

Posts: 146

posted 8 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

hi,i tried with JSTL ..

index.jsp

<%@page pageEncoding="UTF-8" contentType="text/html; charset=UTF-8"%>
<!DOCTYPE html>
   <html>

<head>
   <title>Radial Cluster Demo</title>
       <script src="http://localhost:8080/nav/dd.js"></script>

</style>

</head>

<body>
        <FORM ACTION="main.jsp" METHOD="GET">
         
            <BR>
            <INPUT TYPE="TEXT" NAME="name">
            <BR>
            <INPUT TYPE="SUBMIT" value="Submit">
        </FORM>
      
 <div id="tooltip" style="display:none"></div>
<div id="tree-container"class="full"></div>
 <script type="text/javascript">
    treeData = "${text}";

var selectedNode = null;
var draggingNode = null;

var panSpeed = 200;
var panBoundary = 0;

var i = 0;
var duration = 750;
var root;

var width =5000;
var height = 5000;

var diameter = 750;

var tree = d3.layout.tree().size([360, diameter / 2 - 120])

.separation(function (a, b) {
    return (a.parent === b.parent ? 1 : 5) / a.depth;
});

var diagonal = d3.svg.diagonal()

.projection(function (d) {
    return [d.y, d.x / 180 * Math.PI];
});

root = treeData;
root.x0 = height / 2;
root.y0 = 0;

function sortTree() {
    tree.sort(function (a, b) {
        return b.name.toLowerCase() < a.name.toLowerCase() ? 1 : -1;
    });
}
sortTree();
var baseSvg = d3.select("#tree-container").append("svg")
    .attr("width", width)
    .attr("height", height)
    .attr("class", "overlay")

.attr("transform", "translate(" + 1000 + "," + 1000 + ")");
function collapse(d) {

if (d.children) {
        d._children = d.children;
        d._children.forEach(collapse);
        d.children = null;
    }

update(d);
}

function expand(d) {

if (d._children) {
        d.children = d._children;
        d.children.forEach(expand);
        d._children = null;
    }
}
function toggleChildren(d) {

if (d.children) {
        d._children = d.children;
        d.children = null;
    } else if (d._children) {
        d.children = d._children;
        d._children = null;
    }
    return d;
}

function click(d) {
if(!d.parent){
return;
}

if (!d.children) 
	 treeData.children.forEach(collapse);

if (d3.event.defaultPrevented) return; 
	
    d = toggleChildren(d);
	
    update(d);

}

function update(source) {
  
    var levelWidth = [1];
    var childCount = function (level, n) {
        if (n.children && n.children.length > 0) {
            if (levelWidth.length <= level + 1) levelWidth.push(0);

levelWidth[level + 1] += n.children.length;
            n.children.forEach(function (d) {
                childCount(level + 1, d);
            });
        }
    };
    childCount(0, root);
     var nodes = tree.nodes(root); 
    links = tree.links(nodes);
    node = svgGroup.selectAll("g.node")
        .data(nodes, function (d) {
        return d.id || (d.id = ++i);
    });

var nodeEnter = node.enter().append("g")
	
        .attr("class", "node")
		
    .on('click', click);

nodeEnter.append("circle")
        .attr("class", "smallcircle")
            .style("stroke", function(d) {
      return d.color;
    });

nodeEnter.append("text")
  
    .text(function (d) {
        return d.name;
    })
     //   .style("font", "12px serif")
        .style("opacity", 1)
        .style("fill-opacity", 0)
		
.on("mouseover", function (d) {
            var r = d3.select(this).node().getBoundingClientRect();
           d3.select("div#tooltip")
                .style("display", "inline")
               .style("top", (r.top-25) + "px")
			   .style("top", 10 + "px")
			.style("left", (d3.event.pageX) + "px")		
                .style("top", (d3.event.pageY - 40) + "px")	
              .style("left", r.left + 40+"px")
			  .style("left",  + "px")
                .style("position", "absolute")
               .text(d.t);
		 })
    .on("mouseout", function(){
       d3.select("div#tooltip").style("display", "none");
   });

node.select("circle.nodeCircle")
        .attr("r", 4.5)
        .style("fill", function (d) {
        return d._children ? "red" : "#fff";
    });

//     });
     var nodeUpdate = node.transition()
        .duration(duration)
        .attr("transform", function (d) {
        return "rotate(" + (d.x - 90) + ")translate(" + d.y + ")rotate(" + (-d.x + 90) + ")";
    });

nodeUpdate.select("circle")
        .attr("r", 4.5)
		
        .style("fill", function (d) {
        return d._children ? "lightsteelblue" : "#fff";
    });
	
    nodeUpdate.select("text")
	// .style("font-size", "13px")
        .style("fill-opacity", 2)
   .attr("fill",function(d){return (d.children?"red":"black");})
 //.attr("stroke-width",function(d){return (d.children?"1px":"1px");})
  //.attr("font-weight",function(d){return (d.children?"bolder":"normal");})
		
 
    .attr("dy", ".35em")
	
        .attr("text-anchor", function (d) {
        return d.x < 180 ? "start" : "end";
    })

.attr("transform", function (d) {
        return d.x < 180 ? "translate(8)" : "rotate(360)translate(-8)";
    });

var nodeExit = node.exit().transition()
        .duration(duration)
        .attr("transform", function (d) {
        return "translate(" + source.x + "," + source.y + ")";
    })
        .remove();

nodeExit.select("circle")
        .attr("r", 0);

nodeExit.select("text")
        .style("fill-opacity", 0);

link.transition()
        .duration(duration)
        .attr("d", diagonal);

link.exit().transition()
        .duration(duration)
        .attr("d", function (d) {
        var o = {
            x: source.x,
            y: source.y
        };
        return diagonal({
            source: o,
            target: o
        });
    })
        .remove();

nodes.forEach(function (d) {
        d.x0 = d.x;
        d.y0 = d.y;
    });
}

var svgGroup = baseSvg.append("g")
.attr("transform", "translate(" + 550 + "," + 300 + ")");

d3.selectAll("text").style("fill", function (d) { return d3.select(this).classed(d.cond, true); });

//root.children.forEach(function (child) {
 //   collapse(child);
//});

update(root);
d3.select(self.frameElement).style("height", width);
  </script>
              
              
              
              </script>
</body>

</html>

main.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>   
<c:import var="html" url="index.jsp"/>
<c:import var="js" url="http://localhost:8080/nav/v4.js"/>
<c:import var="js1" url="http://localhost:8080/nav/v44.js"/>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
    "http://www.w3.org/TR/html4/loose.dtd">

</head>

<sql:setDataSource
        var="database"
        driver="com.mysql.jdbc.Driver"
        url="jdbc:mysql://localhost:3306/database"
        user="root" password="admin321"
    />
     
    <sql:query var="text"   dataSource="${database}">     
      select * from newtab where name = +name;
    </sql:query>
     
    <div align="center">
     
            <caption><h2>List</h2></caption>
       
             
         
            <c:forEach var="list" items="${text.rows}">
   <c:out                value="${user.name}" />
                    <c:out value="${user.childname}" />
                     <c:out value="${user.childcolor}" />
                     <c:out value="${user.childtooltip}" />
                      <c:out value="${user.childlevel}" />
                       <c:out value="${user.childchildname}" />
                        <c:out value="${user.childchildcolor}" />
             
       
            
            </c:forEach>
        
    </div>
      
      <body>

</body>

when i run this with d3.v3.min js
in index console it shows Uncaught TypeError: Cannot read property 'diagonal' of undefined
where main.jsp console is blank..
Is there any wrong in getting jsp result into d3. (i.e) treeData = "${text}";

when i run this with d3.v4.min
in index console it shows

Uncaught TypeError: Cannot read property 'prelim' of undefined
at Function.n [as nodes] (dd.js:4)
at update (index1.jsp:166)
at index1.jsp:332

whereas main.jsp console is blank..

what should i do...???

surya preethaaa

Ranch Hand

Posts: 146

posted 8 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

in main.jsp i fail to the import file

actually it is

<script src="http://localhost:8080/nav/v4.js"></script>
<script src="http://localhost:8080/nav/v44.js"></script>

Please suggest me any idea to overcome this issue