How to Connect MySQL from JSP Page

[Dhruv Shah]

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

[peter_budo]

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

[jwenting]

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

[peter_budo]

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

[jwenting]

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>

[peter_budo]

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.

[jwenting]

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

[A Yasir]

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

[jwenting]

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.

[jatin29]

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

[masijade]

Use a bean, as it is suppossed to be done.

You shouldn't have any scriptlets in your code anyway, so having to duplicate the code over and over again shouldn't be a problem in the first place.

[jatin29]

code to upload a image in mysql by jsp

[peter_budo]

code to upload a image in mysql by jsp

What we are suppost to make out of this word collection as it is not a sentence?
Secondly what relevance it has to original topic discussed here previously?

[jatin29]

I want to insert image in mysql by using jsp I have error in my jsp code so if u know how to insert it help me and if possible send me code.

[peter_budo]

Well I need to point you toward Announcements, that clearly state how this community work. So please read it befor next post

Don't mix Servlet and JSP. Add the following code into a JSP page. Don't put it inside a function. JSP pages don't use function. Servlets do.

By the way. Database connections and business logic does not belong in the JSP pages. Move them down to Servlet or other specialized classes for the task.

<html>
<head>
    <title></title>
</head>
<body>
    
   <% 
        try{
            java.sql.Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:8009/test", "root", "asma");
            java.sql.PreparedStatement stmt = conn.prepareStatement("SELECT * FROM test");
            ResultSet rs = stmt.executeQuery();
            while(rs.next()){

            }
        }
        catch(Exception e){
            e.printStackTrace();
        }
   %>
</body>
</html>

[praveen56]

hi,
u hve to copy a file fom jdk\lib\tools.jar file and paste it into tomcat 5.0\common\lib
after doing this u can compile your jsp file..

[Kusno]

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

How do I implement this class in JSP page ?

Thanks,

Kusno.

This class is the JSP. JSP is an extension of Servlet. Simply told. If you see a few pages up on this article you see how to create a connection in JSP. But as i already had sead. Don't use connection or other business related code inside JSP. It makes it more difficult to read Tags and Java code. Move the Connection and Query code down in a DAO layer and use Servlet class as controler. Put the data you want into a JavaBean and put it into Request or session. Get it back in the JSP page and view it. If you need help in how to implement it give me a reply

[peter_budo]

This is a wrong approach!
JSP => presentation purpose
Servlet => logic execution
That mean servlet is in charge of database connection. Get your self up to date...

Sorry. Peter_budo has absolutly right. JSP has the presentation and Servlet has the logic.

[Kusno]

This class is the JSP. JSP is an extension of Servlet. Simply told. If you see a few pages up on this article you see how to create a connection in JSP. But as i already had sead. Don't use connection or other business related code inside JSP. It makes it more difficult to read Tags and Java code. Move the Connection and Query code down in a DAO layer and use Servlet class as controler. Put the data you want into a JavaBean and put it into Request or session. Get it back in the JSP page and view it. If you need help in how to implement it give me a reply

Dear Mr. Electron33,

Yes, I want to know how to implement it.

Please...:$

Here is an example of how I have done it so far. It is build in Netbeans 5.5

[Kusno]

Dear Mr. Electron33,

Thanks a lot for your help.

Kusno.

[guymanbuddude]

Here is an example of how I have done it so far. It is build in Netbeans 5.5

Hi electron33, thank you for taking the time out to guide us newbies, but could you possibly explain to me how to run this?

The exception it keeps relaying to me is this:
"org.apache.jasper.JasperException: File "/WEB-INF/tld/c.tld" not found"
And I've no idea why, I can see the file myself, it is 2 directories deeper (ie. /WEB-INF/tld) than the current directory index.jsp is in.

I realise that to many this may seem like a very stupid question, but I have been trying in vein for quite a while at this!

Thanks in advance.
If it is at all useful, I'm hosting my MySQL database using Apache2Triad, and I'm using Tomcat.

[jwenting]

Kiddo, we're not here to do your homework for you.
You quite obviously have no clue as to what you're doing, so start out with some beginner's tutorials.

Hi electron33, thank you for taking the time out to guide us newbies, but could you possibly explain to me how to run this?

The exception it keeps relaying to me is this:
"org.apache.jasper.JasperException: File "/WEB-INF/tld/c.tld" not found"
And I've no idea why, I can see the file myself, it is 2 directories deeper (ie. /WEB-INF/tld) than the current directory index.jsp is in.

I realise that to many this may seem like a very stupid question, but I have been trying in vein for quite a while at this!

Thanks in advance.
If it is at all useful, I'm hosting my MySQL database using Apache2Triad, and I'm using Tomcat.

Hi again.

I understand that you are not familiar with Java and Jsp. If you are using Netbeans to develop your application, you have the possibility to add libraries to the Web application class path. What you need is a Jar library named JSTL (java standard tag library). Inside the Jar file you will find a folder named META-INF. Inside this folder you will find a file named core.tld. copy this file from the jar file and put it inside the tld folder under WEB-INF. If there is no folder create one and call it tld.

Start the application and it sould work fine.

By the way. If you are newbie I will recommend starting with some simpler examples and work your way up. I will recommend this site for all. http://www.java2s.com.

[peter_budo]

http://www.java2s.com.

I like that website and use it sometimes, however there are some problems

1. 80% examples are just copy and paste cases from various books missing any explanation
2. because of that simply useless to beginner that have to understand code to know what it does
3. there are many cases of not working examples and admin is lazy to sort them (I actualy reported problem but no change)
4. some code examples are out of date

All-in-all good quick resources if you know what you looking for, you better of with something like The Java EE 5 Tutorial

[jwenting]

hmm, if the examples are all copied from books they're most likely guilty of plagiarism...

[guymanbuddude]

Cheers Electron33 & Peter_Budo.

Got the interaction working so I can finally get on with my original intentions!

You're right about java2s though, it doesn't explain the code it has at all, which is quite annoying when you want to learn. But it is still useful.