Solved

Sample JSP code w/ MYSQL

Posted on 2002-04-04
27
320 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
 
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Owning a franchise can be the dream of a lifetime. It provides a chance for economic growth. You can be as successful as you want.  To make your franchise successful, you need to market it successfully. Here are six of the best marketing strategies …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

747 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

11 Experts available now in Live!

Get 1:1 Help Now