?
Solved

Creating a JDBC connection in a jsp

Posted on 2004-08-19
16
Medium Priority
?
310 Views
Last Modified: 2010-04-01
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
Comment
Question by:override_y2k
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 2
  • +1
16 Comments
 
LVL 2

Author Comment

by:override_y2k
ID: 11842087
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
 
LVL 2

Author Comment

by:override_y2k
ID: 11842091
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
 
LVL 35

Expert Comment

by:girionis
ID: 11842348
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.

 
LVL 11

Expert Comment

by:sompol_kiatkamolchai
ID: 11842489
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
 
LVL 2

Author Comment

by:override_y2k
ID: 11842969
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
 
LVL 2

Author Comment

by:override_y2k
ID: 11842977
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
 
LVL 35

Expert Comment

by:girionis
ID: 11843038
> <%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
 
LVL 13

Accepted Solution

by:
copyPasteGhost earned 500 total points
ID: 11843131
<%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
 
LVL 2

Author Comment

by:override_y2k
ID: 11843152
Excellent thx a mill. Thats where i was goin wrong alright!

Cheers for that, much appreciated!
0
 
LVL 13

Expert Comment

by:copyPasteGhost
ID: 11843202
NO problem.
Glad I could help.
Ghost
0
 
LVL 2

Author Comment

by:override_y2k
ID: 11843265
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
 
LVL 13

Expert Comment

by:copyPasteGhost
ID: 11843302
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
 
LVL 2

Author Comment

by:override_y2k
ID: 11843519
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
 
LVL 13

Expert Comment

by:copyPasteGhost
ID: 11843549
maybe something to do with your size or length?
Ghost
0
 
LVL 2

Author Comment

by:override_y2k
ID: 11843685
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
 
LVL 13

Expert Comment

by:copyPasteGhost
ID: 11843975
sure. no problem
Ghost
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I don't pretend to be an expert at this, but I have found a few things that are useful. I hope that sharing them here will help others, so they will not have to face some rather hard choices. Since I felt this to be a topic of enough importance and…
I originally wrote this article to compare SARDU and YUMI, but have now added Easy2Boot, since that is the one I currently use and find the easiest to create and alter.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses
Course of the Month8 days, 1 hour left to enroll

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question