How To Connect JSP with MySQL

[P Arunkumar]

Hi

I have created some jsp files without database its working perfectly, now i want to connect that project with data base with Mysql.


I don't have any idea about MySql, I tried from out side but till now i didn't get any idea about connection.


Please any one help me from where i want to download mysql , how i want to install and how to connect with jsp page, because iam new to developing field no one is not there to guide me in my office.

Please explain with simple addition program code for connection, then i will develop my self.


Thanks in advance.

Thank & Regards,
Jenifer Rajkumar.

[Swastik Dey]

Create a java class that manages your database related things, for e.g. making connection, retrieving result set etc. Under META-INF folder create a file called context.xml where you define your database connection settings.
From your jsp, use JSTL/EL to retrieve results and other things.

[P Arunkumar]

Hi

I can't understand how to connect my jsp page with my sql please guide with step by step process.


Thanks in advance,
Jenifer Rajkumar.

[Swastik Dey]

As I said create a java class that manages your connection, that can be somewhat like this

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class ConnectionFactory {

	public static Connection Get(String name) throws SQLException {
		try {
			Context initContext = new InitialContext();
			Context envContext  = (Context)initContext.lookup("java:/comp/env");
			DataSource ds = (DataSource)envContext.lookup(name==null?"jdbc/default":name);	
			
			return ds.getConnection();

		} catch (NamingException e) {
			throw new SQLException(e);
			
		} 
	}

	public static ResultSet GetResultSet(String sql, String connectionName) throws SQLException {
		CallableStatement statement = null;
		try {
			statement = Get(connectionName).prepareCall(sql);
			return statement.executeQuery();
			
		} catch (SQLException e) {
			Close(statement);
			throw e;
			
		}
	}

}

create a file context.xml and store it under META-INF

<Context path="/MYApp" reloadable="true">
			<Resource name="jdbc/default" 
				auth="Container" 
				type="javax.sql.DataSource"
				maxActive="100" maxIdle="30" maxWait="10000"
				removeAbandoned="true" logAbandoned="true" removeAbandonedTimeout="300"
				username="userid" password="password" driverClassName="com.mysql.jdbc.Driver"
				url="jdbc:mysql://localhost:3306/datbasename"/>

</Context>

a sample jsp code to retrieve data

<%@taglib prefix="sql" uri="http://java.sun.com/jstl/sql"%>
<%@taglib prefix="c" uri="http://java.sun.com/jstl/core"%>
<sql:query var="rs" dataSource="jdbc/default">
  select roll,name from student
</sql:query>
<c:forEach var="row" items="${rs.rows}">
  Roll No<c:out value="${row.roll}"/>name<c:out value="${row.name}"/><br>
</c:forEach>

And don't forget to include the required library files in your project build path.