• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 336
  • Last Modified:

Sample JSP code w/ MYSQL

Can someone pass along some sample code connecting to a MYSQL DB w/ Java Server Pages?
0
AndySulz
Asked:
AndySulz
  • 13
  • 13
1 Solution
 
kotanCommented:
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
 
cheekycjCommented:
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
 
AndySulzAuthor Commented:
do you know where i can download the jdbc drivers for win32 platform?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
cheekycjCommented:
0
 
AndySulzAuthor Commented:
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
 
AndySulzAuthor Commented:
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
 
AndySulzAuthor Commented:
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
 
AndySulzAuthor Commented:
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
 
AndySulzAuthor Commented:
sorry about that, i was hitting refresh and it posted a few more...
0
 
cheekycjCommented:
which JDBC driver did you use?
0
 
AndySulzAuthor Commented:
2.0.4
0
 
cheekycjCommented:
The jar file, entitled mysql_comp.jar should be placed in the Tomcat lib folder
0
 
AndySulzAuthor Commented:
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
 
AndySulzAuthor Commented:
i got it to work, but nothing prints on the screen...
0
 
cheekycjCommented:
what is the current code that you are using?

CJ
0
 
cheekycjCommented:
And when you run the same query directly against MySQL DB what gets returned?
0
 
AndySulzAuthor Commented:
<%@ 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
 
cheekycjCommented:
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
 
AndySulzAuthor Commented:
there is data in the tblPeople table.  this is what i get.

Got Connection
Got Resultset
Printing Resultset:
Done with Resultset
Closing up
0
 
AndySulzAuthor Commented:
I don't think it executes the while statement.
0
 
cheekycjCommented:
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
 
cheekycjCommented:
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
 
cheekycjCommented:
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
 
AndySulzAuthor Commented:
cheekyci,

I changed rs.getString(1); to

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

looks like everything works now.

Thanks.
0
 
cheekycjCommented:
Glad you got it working.  Thanx for the "A".

CJ
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 13
  • 13
Tackle projects and never again get stuck behind a technical roadblock.
Join Now