DaniWeb Forum Index > Web Development > JSP

How to Connect MySQL from JSP Page

Please support our JSP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

Last »

•

Join Date: Sep 2006

Posts: 17

Reputation: Dhruv Shah is an unknown quantity at this point

Solved Threads: 0

Dhruv Shah Dhruv Shah is offline

Offline

Newbie Poster

How to Connect MySQL from JSP Page

Feb 17th, 2007

As I am new to MySQL, I dont know how to connect my jsp page to the MySQL Database.
So please help me by giving some code on how to connect.
I have downloaded driver for Java named as Connector/J.
MySQL version is 5.0.
In this line
con = DriverManager.getConnection("jdbc:mysql://localhost/JSP_MYSQL?user=root&password=admin");

what is wrong where i have to give MySQL username and password and the name of the Database.

So please help me out.

It's Urgent.

Thanks..

 Help with Code Tags 
 JSP Syntax (Toggle Plain Text) 
<%! 
  Connection con = null;
  Statement state = null;
  ResultSet rs = null;
%>
<%
 try
 {
  Class.forName("com.mysql.jdbc.Driver").newInstance();
  con = DriverManager.getConnection("jdbc:mysql://localhost/JSP_MYSQL?user=root&password=admin");
  state = con.createStatement();
  String s = "Select * from user.login";
  rs = state.executeQuery(s);
 }
 
 catch(SQLException e)
 {
  e.printStackTrace();
 }
 
%>
<%! 
  Connection con = null;
  Statement state = null;
  ResultSet rs = null;
%>
<%
 try
 {
  Class.forName("com.mysql.jdbc.Driver").newInstance();
  con = DriverManager.getConnection("jdbc:mysql://localhost/JSP_MYSQL?user=root&password=admin");
  state = con.createStatement();
  String s = "Select * from user.login";
  rs = state.executeQuery(s);
 }
 
 catch(SQLException e)
 {
  e.printStackTrace();
 }
 
%>

Last edited by Dhruv Shah; Feb 17th, 2007 at 4:20 am.

•

Join Date: Dec 2004

Posts: 4,171

Reputation: peter_budo has much to be proud of

Solved Threads: 479

peter_budo peter_budo is offline

Offline

Code tags enforcer

Re: How to Connect MySQL from JSP Page

Feb 17th, 2007

First thing, please do not use JSP to connect to database. Pass data to servlet for validation, or use JavaScript to valildate them in JSP, and setup connection inside servlet and then by the needs go back to previous page to pick up correct/new data or move to nex page
this is usual peace of code which I re-use often

 Help with Code Tags 
 JSP Syntax (Toggle Plain Text) 
public class SomeClassName extends HttpServlet
{
Connection conn;
Statement stmt;
 
public void init() throws ServletException
{
	try
	{
		Class.forName("com.mysql.jdbc.Driver").newInstance();
	}
	catch (Exception ex)
	{
		System.out.println("Exception is : " + ex.toString() );
	}
}
 
public void doPost( HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException
{
	try
	{
		conn = DriverManager.getConnection("jdbc:mysql://localhost/" 
													+ "database_name?user=my_username&password=my_password");
		stmt = conn.createStatement();
	}
	catch(SQLException ex)
	{
		System.out.println("SQLException : " + ex.getMessage() );
	}
public class SomeClassName extends HttpServlet
{
Connection conn;
Statement stmt;
	
public void init() throws ServletException
{
	try
	{
		Class.forName("com.mysql.jdbc.Driver").newInstance();
	}
	catch (Exception ex)
	{
		System.out.println("Exception is : " + ex.toString() );
	}
}
	
public void doPost( HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException
{
	try
	{
		conn = DriverManager.getConnection("jdbc:mysql://localhost/" 
													+ "database_name?user=my_username&password=my_password");
		stmt = conn.createStatement();
	}
	catch(SQLException ex)
	{
		System.out.println("SQLException : " + ex.getMessage() );
	}

PS: I will request this post to be move to JSP section, it is where it actualy belong and where you can find more similar questions and answers

Learn to see in another's calamity the ills which you should avoid.
Publilius Syrus
(~100 BC)
LJC - London Java Community, Graduate & Undergraduate Software Development Community, JAVAWUG (Java Web User Group), The London Android Group

•

Join Date: Nov 2004

Posts: 6,144

Reputation: jwenting is just really nice

Solved Threads: 212

jwenting

Offline

duckman

Re: How to Connect MySQL from JSP Page

Feb 17th, 2007

also, ALWAYS close database resources after use.
And your connect string looks really weird.
Normally you'd pass username and password separately.

As people are clearly allowed to attack me but I'm not allowed to defend myself, I no longer post to this site.

•

Join Date: Dec 2004

Posts: 4,171

Reputation: peter_budo has much to be proud of

Solved Threads: 479

peter_budo peter_budo is offline

Offline

Code tags enforcer

Re: How to Connect MySQL from JSP Page

Feb 17th, 2007

jwenting, can I ask why to pass username and password seperately? Is there any particular reason or it is your profesional recomodation? I'm interested as I usualy do it the way as the code above shows...

Last edited by peter_budo; Feb 17th, 2007 at 6:06 pm.

•

Join Date: Nov 2004

Posts: 6,144

Reputation: jwenting is just really nice

Solved Threads: 212

jwenting

Offline

duckman

Re: How to Connect MySQL from JSP Page

Feb 18th, 2007

In most environments username, password, and database url (as well as the actual driver) are stored separately in a configuration file that's not maintained (exclusively) by the people building the application.

It's far easier for a DBA or systems administrator to change a properties file that has a username, password, URL, and driverclass entry than a single very large string.

Most ORM frameworks and other abstraction layers (which you really should use when doing for real database access) also expect them to be separate for that reason.
For example, JBoss expects the following configuration file for a Firebird database (the username and password are the defaults Firebird gets installed with, so everyone who knows Firebird should know about them, thus I'm not divulging secrets here

 Help with Code Tags 
 JSP Syntax (Toggle Plain Text) 
<?xml version="1.0" encoding="UTF-8"?>
<datasources>
 
  <local-tx-datasource>
    <jndi-name>TitanDS</jndi-name>
    <connection-url>jdbc:firebirdsql:localhost/3050:titan</connection-url>
    <driver-class>org.firebirdsql.jdbc.FBDriver</driver-class>
    <user-name>SYSDBA</user-name>
    <password>masterkey</password>    
    <min-pool-size>5</min-pool-size>
    <max-pool-size>20</max-pool-size>    
      <track-statements/>
      <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
      <metadata>
         <type-mapping>Firebird</type-mapping>
      </metadata>
 
    </local-tx-datasource>
</datasources>
<?xml version="1.0" encoding="UTF-8"?>
<datasources>

  <local-tx-datasource>
    <jndi-name>TitanDS</jndi-name>
    <connection-url>jdbc:firebirdsql:localhost/3050:titan</connection-url>
    <driver-class>org.firebirdsql.jdbc.FBDriver</driver-class>
    <user-name>SYSDBA</user-name>
    <password>masterkey</password>    
    <min-pool-size>5</min-pool-size>
    <max-pool-size>20</max-pool-size>    
      <track-statements/>
      <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
      <metadata>
         <type-mapping>Firebird</type-mapping>
      </metadata>

    </local-tx-datasource>
</datasources>

As people are clearly allowed to attack me but I'm not allowed to defend myself, I no longer post to this site.

•

Join Date: Dec 2004

Posts: 4,171

Reputation: peter_budo has much to be proud of

Solved Threads: 479

peter_budo peter_budo is offline

Offline

Code tags enforcer

Re: How to Connect MySQL from JSP Page

Feb 18th, 2007

So how do we go about it in simple enviroment where most schools/universities use Tomcat and MySQL? I found (didn't know before) that my login data are stored in tomcat-users.xml under conf folder.

•

Join Date: Nov 2004

Posts: 6,144

Reputation: jwenting is just really nice

Solved Threads: 212

jwenting

Offline

duckman

Re: How to Connect MySQL from JSP Page

Feb 19th, 2007

You'd put the data in your web.xml or a properties file.
The username/password Tomcat stores is just for login to Tomcat itself (for sites that have authentication enabled) and then only if that authentication uses the default system (you can tell it to use different authentication systems like LDAP as well).

As people are clearly allowed to attack me but I'm not allowed to defend myself, I no longer post to this site.

•

Join Date: Mar 2007

Posts: 7

Reputation: A Yasir is an unknown quantity at this point

Solved Threads: 1

A Yasir

Offline

Newbie Poster

Re: How to Connect MySQL from JSP Page

Mar 29th, 2007

Hi I have used the same code to work at my end but it still giving me error, i just modified code to replace my database name and other specifications:

Here is teh code :

<%@ page import="java.sql.*" %>
<html><body>
<%
public class SomeClassName extends HttpServlet
{
Connection conn;
Statement stmt;

public void init() throws ServletException
{
try
{
Class.forName("com.mysql.jdbc.Driver").newInstance();
}
catch (Exception ex)
{
System.out.println("Exception is : " + ex.toString() );
}
}

public void doPost( HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException
{
try
{
conn = DriverManager.getConnection("jdbc:mysql://localhost:8009/" + "login?user=root&password=asma");
stmt = conn.createStatement();
}
catch(SQLException ex)
{
System.out.println("SQLException : " + ex.getMessage() );
}
%>
</body></html>

I m using:

For TOMCAT -- apache-tomcat-5.5.23
For JRE -- Java EE 5 SDK
For database -- MySQL 5.0.37
For JDBC -- mysql-connector-java-3.0.17

Here is the Error:

type Exception report
message
description The server encountered an internal error () that prevented it from fulfilling this request.

exception org.apache.jasper.JasperException: Unable to compile class for JSP: Stacktrace: org.apache.jasper.compiler.DefaultErrorHandler.javacError(DefaultErrorHandler.java:85) org.apache.jasper.compiler.ErrorDispatcher.javacError(ErrorDispatcher.java:330) org.apache.jasper.compiler.JDTCompiler.generateClass(JDTCompiler.java:435) org.apache.jasper.compiler.Compiler.compile(Compiler.java:298) org.apache.jasper.compiler.Compiler.compile(Compiler.java:277) org.apache.jasper.compiler.Compiler.compile(Compiler.java:265) org.apache.jasper.JspCompilationContext.compile(JspCompilationContext.java:564) org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:299) org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:315) org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265) javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
note The full stack trace of the root cause is available in the Apache Tomcat/5.5.23 logs.

Please help me i'll be grateful

•

Join Date: Nov 2004

Posts: 6,144

Reputation: jwenting is just really nice

Solved Threads: 212

jwenting

Offline

duckman

Re: How to Connect MySQL from JSP Page

Mar 29th, 2007

and?
Haven't you read you should NOT do things like that in a JSP?

I will NOT help anyone who tries to use scriptlets, as they should NEVER be used.

As people are clearly allowed to attack me but I'm not allowed to defend myself, I no longer post to this site.

•

Join Date: Jan 2008

Posts: 5

Reputation: jatin29 is an unknown quantity at this point

Solved Threads: 0

jatin29

Offline

Newbie Poster

Re: How to Connect MySQL from JSP Page

#10

Jan 25th, 2008

how can i use one jsp page in which user name & password are so that all the jsp page use that code to connect with mysql. I escape from writting connection code again & again in all jsp
page use only one page