Solved

Sample JSP code w/ MYSQL

Posted on 2002-04-04
27
327 Views
Last Modified: 2010-04-01
Can someone pass along some sample code connecting to a MYSQL DB w/ Java Server Pages?
0
Comment
Question by:AndySulz
  • 13
  • 13
27 Comments
 
LVL 6

Expert Comment

by:kotan
ID: 6920049
Sample code...

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

<%
Class.forName("org.gjt.mm.mysql.Driver");
           
Connection conn = DriverManager.getConnection("jdbc:mysql://host:3306/databasename", "userid", "password");
               
PreparedStatement stmt1 = conn.prepareStatement("select field1, field2 from table");

ResultSet rs = stmt1.executeQuery();

while (rs.next())
{
  rs.getString(1);
  rs.getString(2);
}

conn.close();
%>
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6920753
one small change :-)

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

<%
Class.forName("org.gjt.mm.mysql.Driver");
           
Connection conn = DriverManager.getConnection("jdbc:mysql://host:3306/databasename", "userid", "password");
               
PreparedStatement stmt1 = conn.prepareStatement("select field1, field2 from table");

ResultSet rs = stmt1.executeQuery();

while (rs.next())
{
 rs.getString(1);
 rs.getString(2);
}
rs.close();
conn.close();
%>

CJ
0
 
LVL 2

Author Comment

by:AndySulz
ID: 6921120
do you know where i can download the jdbc drivers for win32 platform?
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 19

Expert Comment

by:cheekycj
ID: 6921152
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6921156
0
 
LVL 2

Author Comment

by:AndySulz
ID: 6921228
I downloaded all the classes, I extracted them to the classes folder in the Apache TomCat dir...i'm still getting the same error...i don't think i put the classes in the right place.

Andy
0
 
LVL 2

Author Comment

by:AndySulz
ID: 6921234
I downloaded all the classes, I extracted them to the classes folder in the Apache TomCat dir...i'm still getting the same error...i don't think i put the classes in the right place.

Andy
0
 
LVL 2

Author Comment

by:AndySulz
ID: 6921235
I downloaded all the classes, I extracted them to the classes folder in the Apache TomCat dir...i'm still getting the same error...i don't think i put the classes in the right place.

Andy
0
 
LVL 2

Author Comment

by:AndySulz
ID: 6921243
I downloaded all the classes, I extracted them to the classes folder in the Apache TomCat dir...i'm still getting the same error...i don't think i put the classes in the right place.

Andy
0
 
LVL 2

Author Comment

by:AndySulz
ID: 6921244
sorry about that, i was hitting refresh and it posted a few more...
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6921408
which JDBC driver did you use?
0
 
LVL 2

Author Comment

by:AndySulz
ID: 6921413
2.0.4
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6921417
The jar file, entitled mysql_comp.jar should be placed in the Tomcat lib folder
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6921424
0
 
LVL 2

Author Comment

by:AndySulz
ID: 6921433
ok, i got an access denied error which is on my end...the code you sent before was to connect to a PW protected DB, how would I need to modify this to connect with no password?
0
 
LVL 2

Author Comment

by:AndySulz
ID: 6921456
i got it to work, but nothing prints on the screen...
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6921475
what is the current code that you are using?

CJ
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6921480
And when you run the same query directly against MySQL DB what gets returned?
0
 
LVL 2

Author Comment

by:AndySulz
ID: 6921536
<%@ page import="java.sql.*" %>

<%
Class.forName("org.gjt.mm.mysql.Driver");
         
Connection conn = DriverManager.getConnection("jdbc:mysql://SERVER1:3306/DB", "UN", "PW");
             
PreparedStatement stmt1 = conn.prepareStatement("select FirstName, LastName from tblPeople");

ResultSet rs = stmt1.executeQuery();

while (rs.next())
{
rs.getString(1);
rs.getString(2);
}
rs.close();
conn.close();
%>
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6921549
try modifying it to this.. and see what gets printed:

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

<%
Class.forName("org.gjt.mm.mysql.Driver");
         
Connection conn = DriverManager.getConnection("jdbc:mysql://SERVER1:3306/DB", "UN", "PW");
%>
Got Connection<br>
<%          
PreparedStatement stmt1 = conn.prepareStatement("select FirstName, LastName from tblPeople");
ResultSet rs = stmt1.executeQuery();
%>
Got Resultset<br>
Printing Resultset:<br>
<%
while (rs.next())
{
rs.getString(1);
rs.getString(2);
}
%>
Done with Resultset<br>
Closing up<br>
<%
rs.close();
conn.close();
%>
Done.


Could you try that and tell me what gets displayed.

Also make sure that tblPeople has data in it.

CJ
0
 
LVL 2

Author Comment

by:AndySulz
ID: 6921586
there is data in the tblPeople table.  this is what i get.

Got Connection
Got Resultset
Printing Resultset:
Done with Resultset
Closing up
0
 
LVL 2

Author Comment

by:AndySulz
ID: 6921598
I don't think it executes the while statement.
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6921611
I wonder if you can use prepared statement at all with 2.0.4

replace:
<%          
PreparedStatement stmt1 = conn.prepareStatement("select FirstName, LastName from tblPeople");
ResultSet rs = stmt1.executeQuery();
%>
with:
<%          
Statement stmt = conn.createStatement();
Resultset rs = stmt.executeQuery("select FirstName, LastName from tblPeople");
%>

See if that changes anything.

CJ
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6921614
I think if you want to use Prepared Statements you may have to get the latest mysql jdbc driver from here:
http://sourceforge.net/project/showfiles.php?group_id=15923&release_id=72107

version 2.0.11 supports a lot more (including prepared statements)
0
 
LVL 19

Accepted Solution

by:
cheekycj earned 50 total points
ID: 6921625
Scratch all that.. the reason why its not printing is b/c we are not making it print :-)

This will work :-)


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

<%
Class.forName("org.gjt.mm.mysql.Driver");
       
Connection conn = DriverManager.getConnection("jdbc:mysql://SERVER1:3306/DB", "UN", "PW");
%>
Got Connection<br>
<%          
PreparedStatement stmt1 = conn.prepareStatement("select FirstName, LastName from tblPeople");
ResultSet rs = stmt1.executeQuery();
%>
Got Resultset<br>
Printing Resultset:<br>
<%
while (rs.next()) {
%>
<%=rs.getString(1);%>
<%=rs.getString(2);%>
<%
}
%>
Done with Resultset<br>
Closing up<br>
<%
rs.close();
conn.close();
%>
Done.
0
 
LVL 2

Author Comment

by:AndySulz
ID: 6921685
cheekyci,

I changed rs.getString(1); to

out.println(rs.getString("FirstName"));

looks like everything works now.

Thanks.
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6921710
Glad you got it working.  Thanx for the "A".

CJ
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
java.util.concurrent.locks in java 3 203
sortaSum challenge java 17 174
eclipse luna javeEE perspecive missing 5 118
How  can  i  resolve  HTTP Status 404 -? 8 58
While it may be true that the internet is a place of possibilities, it is also a hostile environment lurking with many dangers. By clicking on the wrong link, trusting the wrong person or using a weak password, you are virtually inviting hackers to …
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

685 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