Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Creating a JDBC connection in a jsp

Posted on 2004-08-19
16
Medium Priority
?
312 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

An overview of cyber security, cyber crime, and personal protection against hackers. Includes a brief summary of the Equifax breach and why everyone should be aware of it. Other subjects include: how cyber security has failed to advance with technol…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

609 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