How to Connect MySQL from JSP Page
 

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

<%! 

  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();

 }

 

%>

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

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

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.

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

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 ;)):

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

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.

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

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

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.

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