[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 316
  • Last Modified:

Creating a JDBC connection in a jsp

when accessing a mysql db directly from a jsp are try and catch loops required within the jsp?

Heres my code:

****************************
dbUpdateQuery.jsp
****************************



<%@ page import="java.sql.*" %>
<%@ page import="dbAddQuery.*" %>


<html>

<%    

      Class.forName("com.mysql.jdbc.Driver");
      String url = "jdbc:mysql://csserver.ucd.ie/imcdonnell";    
      String userName = "imcdonnell";    
      String passWord = "tuesday";    
      Connection con;    
      con = DriverManager.getConnection(url, userName, passWord);
      String sql = "select * from queries";  
      Statement stmt = con.createStatement();    
      ResultSet results = stmt.executeQuery(sql);
      
%>


<head><title>Please enter search criteria</title>

<script type="text/javascript">
<!--



// -->
</script>
<link rel="stylesheet" href="styles.css" type="text/css">
</head>
<body>


<FORM  action=" dbAddQuery.jsp" method="post">

<table width="240" border="0" cellpadding="5" cellspacing="0" background="backdrop.jpg">
<tr>
<td>
<br>



<table width="81%" border="0" cellpadding="0" cellspacing="2" align="center">
<tr><td colspan="2" height="20" align="center"><div align="left"><font size="3" color="#0000aa"><b> Enter Search Criteria</b></font></div></td></tr>


<tr><td>





</td></tr></table>



<br>

<table width="100%" border="0" cellpadding="0" cellspacing="2" align="center">
  <tr>
    <td><b>Title:</b></td>
    <td width="632" align="center"><div align="left">
        <input type="text" name="title" value = "hello" size="20" maxlength="50">
    </div></td>
  </tr>
  <tr>
    <td><b>Description:</b></td>
    <td align="center"><div align="left">
        <input type="text" name="description" size="20" maxlength="50">
    </div></td>
  </tr>
  <tr>
    <td width="127"><b>Location: </b> </td>
    <td align="center"><div align="left">
        <input type="text" name="location" size="20" maxlength="50">
    </div></td>
  </tr>
  <tr>
    <td><b>Weather: </b> </td>
    <td align="center"><div align="left">
        <input type="text" name="weather" size="20" maxlength="20" class="input">
    </div></td>
  </tr>
  <tr>
    <td><b>Mood: </b> </td>
    <td align="center"><div align="left">
        <input type =" text" name="mood" size="20" maxlength="20">
    </div></td>
  </tr>
  <tr>
    <td><b>Date: </b> </td>
    <td align="center"><div align="left">
        <input type =" text" name="date" size="20" maxlength="20">
    </div></td>
  </tr>
  <tr>
    <td><b>Time: </b> </td>
    <td align="center"><div align="left">
        <input type =" text" name="time" size="20" maxlength="10">
    </div></td>
  </tr>
  <tr valign="middle">
    <td><b>InOut:</b></td>
    <td><select name="inout">
        <option selected> Indoors
          <option> Outdoors
          <option> Both
          </select></td>
  </tr>
</table>
<tr><td colspan="2">
<table width="222">

<tr>
  <td width="252" colspan="2" align="center"><input type=submit  value="Submit Details!"></td>
</tr>
<tr><td colspan="2" align="center"><input type=reset value=" Reset Form "></td></tr>

</table> </FORM><br><br>
</body>

</html>
0
override_y2k
Asked:
override_y2k
  • 8
  • 5
  • 2
  • +1
1 Solution
 
override_y2kAuthor Commented:
That works fine, but when i add this line it throws an error:

<% while(results.next()){ %>

***************************
File that isn't working.jsp
***************************



<%@ page import="java.sql.*" %>
<%@ page import="dbAddQuery.*" %>


<html>

<%    

      Class.forName("com.mysql.jdbc.Driver");
      String url = "jdbc:mysql://csserver.ucd.ie/imcdonnell";    
      String userName = "imcdonnell";    
      String passWord = "tuesday";    
      Connection con;    
      con = DriverManager.getConnection(url, userName, passWord);
      String sql = "select * from queries";  
      Statement stmt = con.createStatement();    
      ResultSet results = stmt.executeQuery(sql);
      
%>

<%    while(results.next()){ %>


<head><title>Please enter search criteria</title>

<script type="text/javascript">
<!--



// -->
</script>
<link rel="stylesheet" href="styles.css" type="text/css">
</head>
<body>


<FORM  action=" dbAddQuery.jsp" method="post">

<table width="240" border="0" cellpadding="5" cellspacing="0" background="backdrop.jpg">
<tr>
<td>
<br>



<table width="81%" border="0" cellpadding="0" cellspacing="2" align="center">
<tr><td colspan="2" height="20" align="center"><div align="left"><font size="3" color="#0000aa"><b> Enter Search Criteria</b></font></div></td></tr>


<tr><td>





</td></tr></table>



<br>

<table width="100%" border="0" cellpadding="0" cellspacing="2" align="center">
  <tr>
    <td><b>Title:</b></td>
    <td width="632" align="center"><div align="left">
        <input type="text" name="title" value = "hello" size="20" maxlength="50">
    </div></td>
  </tr>
  <tr>
    <td><b>Description:</b></td>
    <td align="center"><div align="left">
        <input type="text" name="description" size="20" maxlength="50">
    </div></td>
  </tr>
  <tr>
    <td width="127"><b>Location: </b> </td>
    <td align="center"><div align="left">
        <input type="text" name="location" size="20" maxlength="50">
    </div></td>
  </tr>
  <tr>
    <td><b>Weather: </b> </td>
    <td align="center"><div align="left">
        <input type="text" name="weather" size="20" maxlength="20" class="input">
    </div></td>
  </tr>
  <tr>
    <td><b>Mood: </b> </td>
    <td align="center"><div align="left">
        <input type =" text" name="mood" size="20" maxlength="20">
    </div></td>
  </tr>
  <tr>
    <td><b>Date: </b> </td>
    <td align="center"><div align="left">
        <input type =" text" name="date" size="20" maxlength="20">
    </div></td>
  </tr>
  <tr>
    <td><b>Time: </b> </td>
    <td align="center"><div align="left">
        <input type =" text" name="time" size="20" maxlength="10">
    </div></td>
  </tr>
  <tr valign="middle">
    <td><b>InOut:</b></td>
    <td><select name="inout">
        <option selected> Indoors
          <option> Outdoors
          <option> Both
          </select></td>
  </tr>
</table>
<tr><td colspan="2">
<table width="222">

<tr>
  <td width="252" colspan="2" align="center"><input type=submit  value="Submit Details!"></td>
</tr>
<tr><td colspan="2" align="center"><input type=reset value=" Reset Form "></td></tr>

</table> </FORM><br><br>
</body>

</html>
0
 
override_y2kAuthor Commented:
These are the errors it throws:


Generated servlet error:
    [javac] Compiling 1 source file
    [javac] /usr/local/jakarta-tomcat-4.1.29/work/Standalone/localhost/~imcdonnell/dbAddQuery/dbUpdateQuery_jsp.java:205: 'catch' without 'try'
    [javac]     } catch (Throwable t) {
    [javac]       ^
    [javac] /usr/local/jakarta-tomcat-4.1.29/work/Standalone/localhost/~imcdonnell/dbAddQuery/dbUpdateQuery_jsp.java:32: 'try' without 'catch' or 'finally'
    [javac]     try {
    [javac]     ^
    [javac] /usr/local/jakarta-tomcat-4.1.29/work/Standalone/localhost/~imcdonnell/dbAddQuery/dbUpdateQuery_jsp.java:215: '}' expected
0
 
girionisCommented:
Hi override_y2k,

> <% while(results.next()){ %>

you need to close the while loop. You will also need to try catch any SQL excpetions that might occur.

Regards
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
sompol_kiatkamolchaiCommented:
Yes girionis is right.

No matching } with while loop and I found that your source code does not make sense in business logic. you make a query from database but you didn't use the resultset to render data.

you may do

<table>
<tr>
  <td>column_name1</td>
  <td>column_name2</td>
</tr>
<%
while(results.next()) {
%>
<tr>
  <td><%=results.getString("column_name1")%></td>
  <td><%=results.getString("column_name2")%></td>
</tr>
<%
}
%>
</table>
0
 
override_y2kAuthor Commented:
Sorry typo in my question, i had the while loop closed.

I do have functionality with the result set.

Heres a snippet for ease of use:

*********************

<%@ page import="java.sql.*" %>
<%@ page import="dbAddQuery.*" %>


<html>




<head><title>Please enter search criteria</title>

<script type="text/javascript">
<!--



// -->
</script>
<link rel="stylesheet" href="styles.css" type="text/css">
</head>
<body>


<FORM  action=" dbAddQuery.jsp" method="post">

<table width="240" border="0" cellpadding="5" cellspacing="0" background="backdrop.jpg">
<tr>
<td>
<br>



<table width="81%" border="0" cellpadding="0" cellspacing="2" align="center">
<tr><td colspan="2" height="20" align="center"><div align="left"><font size="3" color="#0000aa"><b> Enter Search Criteria</b></font></div></td></tr>


<tr><td>





</td></tr></table>



<br>

<table width="100%" border="0" cellpadding="0" cellspacing="2" align="center">

<%    

      Class.forName("com.mysql.jdbc.Driver");
      String url = "jdbc:mysql://csserver.ucd.ie/imcdonnell";    
      String userName = "imcdonnell";    
      String passWord = "tuesday";    
      Connection con;    
      con = DriverManager.getConnection(url, userName, passWord);
      String sql = "select * from queries";  
      Statement stmt = con.createStatement();    
      ResultSet results = stmt.executeQuery(sql);
      
%>

<%    while(results.next()){ %>


  <tr>
    <td><b>Title:</b></td>
    <td width="632" align="center"><div align="left">
        <%input type="text" name="title" value = results.getString(1) size="20" maxlength="50"%>
    </div></td>
  </tr>
<% } %>








************************************************

The problem seems to be with this line:

<%input type="text" name="title" value = results.getString(1) size="20" maxlength="50"%>
0
 
override_y2kAuthor Commented:
And this is the error report:

Generated servlet error:    [javac] Compiling 1 source file    [javac] /usr/local/jakarta-tomcat-4.1.29/work/Standalone/localhost/~imcdonnell/dbAddQuery/dbUpdateQuery_jsp.java:102: ';' expected    [javac] input type="text" name="title" value = results.getString(1) size="20" maxlength="50"    [javac]                   ^    [javac] 1 error
0
 
girionisCommented:
> <%input type="text" name="title" value = results.getString(1) size="20"
> maxlength="50"%>

change it with

<input type="text" name="title" value = results.getString(1) size="20" maxlength="50">

it is HTML code not Java. Yo do not need the scriplet tags <% and %>
0
 
copyPasteGhostCommented:
<%input type="text" name="title" value = results.getString(1) size="20" maxlength="50"%>

should this be..

<input type="text" name="title" value = <%=results.getString(1)%> size="20" maxlength="50" />
Ghost
0
 
override_y2kAuthor Commented:
Excellent thx a mill. Thats where i was goin wrong alright!

Cheers for that, much appreciated!
0
 
copyPasteGhostCommented:
NO problem.
Glad I could help.
Ghost
0
 
override_y2kAuthor Commented:
1 quick small problem, when it displays result.getString in the field, it only shows the 1st word, where as many of the fields should have several words.

Any ideas?
0
 
copyPasteGhostCommented:
umm are all of the words in the same field in the results resultset? you code is saying to pull only the first coloum.
does that help?
Ghost
0
 
override_y2kAuthor Commented:
well i have results.getString(2) etc all the way up to the number of fields that i have in my db table/html form.

Like say for example my db field called title has value blah blah.

When i display the details in the html form, it has title : blah
rather than title blah blah

0
 
copyPasteGhostCommented:
maybe something to do with your size or length?
Ghost
0
 
override_y2kAuthor Commented:
what are you trying to say :)

I dont think its anything to do with that, its only minor prob anyway, so i should be able to sort it.

Thx again for the help.
0
 
copyPasteGhostCommented:
sure. no problem
Ghost
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now