Solved

Sample JSP code w/ MYSQL

Posted on 2002-04-04
27
324 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Google Drive is extremely cheap offsite storage, and it's even possible to get extra storage for free for two years.  You can use the free account 15GB, and if you have an Android device..when you install Google Drive for the first time it will give…
Binary Differential Replication, What it is, how it works and how it differs from standard delta file replication
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

816 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now