Ad:

JSP Discussion Thread
Marked Solved
Views: 39123

Page 1
2
3
4
Next

You are currently viewing page 1 of this multi-page discussion thread

Feb 17th, 2007

How to Connect MySQL from JSP Page

Expand Post »

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..

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.

Similar Threads

Dhruv Shah

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

17 posts
since Sep 2006

Permalink

Feb 17th, 2007

Re: How to Connect MySQL from JSP Page

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

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

peter_budo

Moderator

Featured Poster

Reputation Points: 2786

Solved Threads: 874

Code tags enforcer

Offline

6,659 posts
since Dec 2004

Permalink

Feb 17th, 2007

Re: How to Connect MySQL from JSP Page

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

jwenting

Team Colleague

Reputation Points: 1658

Solved Threads: 331

duckman

Offline

7,719 posts
since Nov 2004

Permalink

Feb 17th, 2007

Re: How to Connect MySQL from JSP Page

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.

peter_budo

Moderator

Featured Poster

Reputation Points: 2786

Solved Threads: 874

Code tags enforcer

Offline

6,659 posts
since Dec 2004

Permalink

Feb 18th, 2007

Re: How to Connect MySQL from JSP Page

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

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>

jwenting

Team Colleague

Reputation Points: 1658

Solved Threads: 331

duckman

Offline

7,719 posts
since Nov 2004

Permalink

Feb 18th, 2007

Re: How to Connect MySQL from JSP Page

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.

peter_budo

Moderator

Featured Poster

Reputation Points: 2786

Solved Threads: 874

Code tags enforcer

Offline

6,659 posts
since Dec 2004

Permalink

Feb 19th, 2007

Re: How to Connect MySQL from JSP Page

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).

jwenting

Team Colleague

Reputation Points: 1658

Solved Threads: 331

duckman

Offline

7,719 posts
since Nov 2004

Permalink

Mar 29th, 2007

Re: How to Connect MySQL from JSP Page

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

A Yasir

Reputation Points: 10

Solved Threads: 1

Newbie Poster

Offline

7 posts
since Mar 2007

Permalink

Mar 29th, 2007

Re: How to Connect MySQL from JSP Page

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.

jwenting

Team Colleague

Reputation Points: 1658

Solved Threads: 331

duckman

Offline

7,719 posts
since Nov 2004

Permalink

Jan 25th, 2008

Re: How to Connect MySQL from JSP Page

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

jatin29

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

5 posts
since Jan 2008

Page 1
2
3
4
Next

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in JSP Forum Timeline: Unable to connect to tomcat through browser

Next Thread in JSP Forum Timeline: MVC help

DaniWeb Message

Cancel Changes

User Name
Password