Solved

Creating a JDBC connection in a jsp

Posted on 2004-08-19
16
308 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
Technology Partners: 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 125 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

Technology Partners: 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

Suggested Solutions

A hard and fast method for reducing Active Directory Administrators members.
Check out this step-by-step guide for asking an anonymous question on Experts Exchange.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

734 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