Solved

Sample JSP code w/ MYSQL

Posted on 2002-04-04
27
326 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Jquery - parsing the results returned from webmethod 1 118
rebasing and merging used in SCM 1 78
main method of string class 3 117
collection output issue 9 36
This article describes my battle tested process for setting up delegation. I use this process anywhere that I need to setup delegation. In the article I will show how it applies to Active Directory
Starting your own business is always a daunting process, and for most people it is brand new experience. Avoid the common pitfalls by following these tips to start on the road to success.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

840 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