Solved

Sample JSP code w/ MYSQL

Posted on 2002-04-04
27
330 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
[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
  • 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
Industry Leaders: 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

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

Popular third-party chat platforms like Slack, Discord, and Telegram are just a few of the many new productivity applications that are being hijacked by cybercriminals to create command-and-control (C&C) communications infrastructures for their malw…
This article outlines some of the reasons why an email message gets flagged as spam on a recipient's end.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
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.…

705 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