JSP) JSP and Oracle

It would help if you had some place to store your data on any website or webpage. Even if you made a perfect website with JSP, unless you connect it to DB, it wouldn't have any function to store the users' information. So, It is essential to know how to connect the two sources. 

 

There are three significant ways to connect JSP and Oracle: JDBC(Java DataBase Connectivity), DBCP(DataBase Connection Pool), and ORM(Object Relational Mapping) framework. iBatis, MyBatis, hibernate, and JPA are ORM frameworks. Among them, we will discuss JDBC first.

 

JDBC

 

JDBC Configuration

To connect to Oracle, you first need a JDBC Driver. Depending on the version set on your PC, I will download an ojdbc6.jar. 

After then, we will test if the connection is successfully run or not. Oracle Smith account must be connected if you want to connect them successfully. 

To connect Oracle

<%@ page contentType="text/html; charset=euc-kr" %>
<%@ page import="java.sql.*" %>

<%
  Connection con=null;

  try{

/**************** Oracle *****************************/
    String driver = "oracle.jdbc.driver.OracleDriver";  	

    String jdbcUrl = "jdbc:oracle:thin:@localhost:1521:xe";
    String dbId = "scott";
    String dbPass = "tiger";
/*****************************************************/


	Class.forName(driver);
	con=DriverManager.getConnection(jdbcUrl,dbId ,dbPass );
	
	out.println("Successfully connected.");

	} catch(Exception e){ 

		e.printStackTrace();

	}
%>

To connect MySQL

<%@ page contentType="text/html; charset=euc-kr" %>
<%@ page import="java.sql.*" %>

<%
  Connection con=null;

  try{

/***************** My-SQL *****************************/
    String driver = "com.mysql.jdbc.Driver";

    String jdbcUrl = "jdbc:mysql://localhost:3306/jsptest";
    String dbId = "jspid";
    String dbPass = "jsppass";	
/*******************************************************/	


	Class.forName(driver);
	con=DriverManager.getConnection(jdbcUrl,dbId ,dbPass );
	
	out.println("Successfully connected.");

	} catch(Exception e){ 

		e.printStackTrace();

	}
%>

If you get this message, it is all set! 

Now, to run the Oracle in Eclipse, we will make a folder named "sql" in the webcontent folder and create a new SQL file.

Open the Data Source Explorer and connect the Scott account to run the codes. 

 

Now, let us look at the overall procedure of connecting the database to JSP. 

1. Load JDBC Driver 

Create a class.forName() 

 

2. Connect Database

java.sql.Connection

 

3. Create SQLs

java.sql.Statement

java.sql.PreparedStatement - The most used

java.sql.CallableStatement

 

4. Send SQLs 

executeQuery()

executeUpdate()

 

5. Receive result

java.sql.ResultSet

 

6. Disconnect

Connection.close()

 

To start connecting, we need first to create a table.

create  table  member1( 
			 id varchar2(12) primary key,
             passwd varchar2(12) not null,
		     name varchar2(10) not null,
		     reg_date timestamp not null );
             
select * from tab;
select * from member1;