JSP/ Oracle) Members list

2022. 8. 31. 14:16

Create a table first to store data in Oracle or Eclipse.

myoracle.sql

select * from tab;
select * from member1;

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

insertTestForm.jsp

<%@ page contentType="text/html; charset=euc-kr"%>

<html>
<head>
<title>Insert record</title>
<script src="http://code.jquery.com/jquery-1.11.1.js"></script>
<script>
	$(document).ready(function() {
		$("form").submit(function() {
			if ($("#id").val() == "") {
				alert("Insert your ID.");
				$("#id").focus();
				return false;
			}
			if ($("#passwd").val() == "") {
				alert("Insert your password.");
				$("#passwd").focus();
				return false;
			}
			if ($("#name").val() == "") {
				alert("Insert your name.");
				$("#name").focus();
				return false;
			}
		});

	});
</script>
</head>

<body>
	<h2>Insert record on member1 table</h2>

	<FORM METHOD="post" ACTION="insertTest.jsp">
		ID : <INPUT TYPE="text" NAME="id" id="id">
		<p>
			Password : <INPUT TYPE="password" NAME="passwd" id="passwd">
		<p>
			Name :<INPUT TYPE="text" NAME="name" id="name">
		<p>
			<INPUT TYPE="submit" VALUE="Submit">
	</FORM>

</body>
</html>

insertTest.jsp

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

<%
  request.setCharacterEncoding("euc-kr");

  String id= request.getParameter("id");
  String passwd= request.getParameter("passwd");
  String name= request.getParameter("name");
  Timestamp register=new Timestamp(System.currentTimeMillis());

  Connection conn=null;
  PreparedStatement pstmt=null;
  int result = 0;
  
  try{
	String jdbcUrl="jdbc:oracle:thin:@localhost:1521:xe";
    String dbId="scott";
    String dbPass="tiger";
	 
	// JDBC
	Class.forName("oracle.jdbc.driver.OracleDriver");
	conn=DriverManager.getConnection(jdbcUrl,dbId ,dbPass );
	
	String sql= "insert into member1 values (?,?,?,sysdate)";
	pstmt=conn.prepareStatement(sql);
	pstmt.setString(1,id);
    pstmt.setString(2,passwd);
	pstmt.setString(3,name);
//	pstmt.setTimestamp(4,register);
	result = pstmt.executeUpdate();

	}catch(Exception e){ 
		e.printStackTrace();
	}finally{
		if(pstmt != null) try{pstmt.close();}catch(SQLException sqle){}
		if(conn != null) try{conn.close();}catch(SQLException sqle){}
	}
%>

<html>
<head><title>Member List</title></head>
<body>
  
<%
	if(result == 1){
%>    
  		<script>
  			alert("Successfully joined.");
  			location.href="selectTest.jsp";
  		</script>
<% 	}else{%>
   		<script>
			alert("Failed to join.");
			history.go(-1);
   		</script>
<% 	} %>   
</body>
</html>

seleteTest.jsp

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

<html>
<head><title>Display records</title></head>
<body>
  <h2>Member1 Table</h2>
  
  <a href="insertTestForm.jsp">Sign Up</a>
  <TABLE width="550" border="1">
  <TR>
  	<TD width="100">ID</TD>
  	<TD width="100">Password</TD>
  	<TD width="100">Name</TD>
  	<TD width="250">Joined Date</TD>
  	<TD width="100">Edit</TD>
  	<TD width="100">Delete</TD>
  </TR>

<%
  Connection conn=null;
  PreparedStatement pstmt=null;
  ResultSet rs=null;
  ResultSet rs01=null;
  int cnt=0;
  
  try{
	String jdbcUrl="jdbc:oracle:thin:@localhost:1521:xe";
    String dbId="scott";
    String dbPass="tiger";
	 
	Class.forName("oracle.jdbc.driver.OracleDriver");
	conn=DriverManager.getConnection(jdbcUrl,dbId ,dbPass );

	pstmt=conn.prepareStatement("select count(*) from member1");
	rs01=pstmt.executeQuery();
	if(rs01.next()){
		cnt = rs01.getInt(1);
//		cnt = rs01.getInt("count(*)");
	}	
	
	String sql= "select * from member1";
	pstmt=conn.prepareStatement(sql);
	rs=pstmt.executeQuery();

	while(rs.next()){
	  String id= rs.getString("id");
      String passwd= rs.getString("passwd");
      String name= rs.getString("name");
      Timestamp register=rs.getTimestamp("reg_date");

%>
     <TR>
  	   <TD width="100"><%=id%></TD>
  	   <TD width="100"><%=passwd%></TD>
  	   <TD width="100"><%=name%></TD>
  	   <TD width="250"><%=register.toString()%></TD>
  	   <TD width="100">
  	   		<a href="updateTestForm.jsp?id=<%=id%>">
  	  		 Edit
  	   		</a>  	   
  	   </TD>
  	   <TD width="100">
  	   		<a href="deleteTestForm.jsp?id=<%=id %>">
  	   		 Delete
  	   		</a>
  	   </TD>
    </TR>
<%  } 
  }catch(Exception e){ 
		e.printStackTrace();
  }finally{
	    if(rs != null) try{rs.close();}catch(SQLException sqle){}
		if(pstmt != null) try{pstmt.close();}catch(SQLException sqle){}
		if(conn != null) try{conn.close();}catch(SQLException sqle){}
  }
%>
</TABLE>

Total members :<%=cnt %>


</body>
</html>

updateTestForm.jsp

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

<%
	String uid = request.getParameter("id");

Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;

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

	Class.forName("oracle.jdbc.driver.OracleDriver");
	conn = DriverManager.getConnection(jdbcUrl, dbId, dbPass);

	String sql = "select * from member1 where id=?";
	pstmt = conn.prepareStatement(sql);
	pstmt.setString(1, uid);
	rs = pstmt.executeQuery();

	if (rs.next()) {
		String id = rs.getString("id");
		String passwd = rs.getString("passwd");
		String name = rs.getString("name");
		Timestamp register = rs.getTimestamp("reg_date");
%>
<html>
<head>
<title>Update record</title>
</head>

<body>
	<h2>Update record on member1 table</h2>

	<FORM METHOD="post" ACTION="updateTest.jsp">
		<input type=hidden name="id" value="<%=id%>"> ID :
		<%=id%><p>
			Password : <INPUT TYPE="password" NAME="passwd">
		<p>
			Name to edit:<INPUT TYPE="text" NAME="name" value="<%=name%>">
		<p>
			<INPUT TYPE="submit" VALUE="Submit">
	</FORM>

</body>
</html>

<%
	} // if end
} catch (Exception e) {
e.printStackTrace();
} finally {
if (rs != null)
try {
	rs.close();
} catch (SQLException sqle) {
}
if (pstmt != null)
try {
	pstmt.close();
} catch (SQLException sqle) {
}
if (conn != null)
try {
	conn.close();
} catch (SQLException sqle) {
}
}
%>

updateTest.jsp

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

<%
  request.setCharacterEncoding("euc-kr");

  String id= request.getParameter("id");
  String passwd= request.getParameter("passwd");
  String name= request.getParameter("name");

  Connection conn=null;
  PreparedStatement pstmt=null;
  ResultSet rs=null;

  try{
	String jdbcUrl="jdbc:oracle:thin:@localhost:1521:xe";
    String dbId="scott";
    String dbPass="tiger";
	 
	Class.forName("oracle.jdbc.driver.OracleDriver");
	conn=DriverManager.getConnection(jdbcUrl,dbId ,dbPass );
	
	String sql= "select id, passwd from member1 where id= ?";
	pstmt=conn.prepareStatement(sql);
    pstmt.setString(1,id);
	rs=pstmt.executeQuery();
    
	if(rs.next()){ // if the ID is already occupied
		String rId=rs.getString("id");
		String rPasswd=rs.getString("passwd");
      if(id.equals(rId) && passwd.equals(rPasswd)){
	    sql= "update member1 set name= ?, reg_date=sysdate  where id= ? ";
	    pstmt=conn.prepareStatement(sql);
	    pstmt.setString(1,name);
	    pstmt.setString(2,id);
	    pstmt.executeUpdate();
	   
%>
		<script>
			alert("Successfully updated.");
			location.href="selectTest.jsp";
		</script>	
<%
       }else{
 %>
	 <script>
	 	alert("Wrong password.");
	 	history.go(-1);
	 </script>
	 	
<% 	 
		}
	 }else{
%>
		<script>
			alert("Wrong ID.");
			history.go(-1);
		</script>
<%
	 }

	}catch(Exception e){ 
		e.printStackTrace();
	}finally{
		if(rs != null) try{rs.close();}catch(SQLException sqle){}
		if(pstmt != null) try{pstmt.close();}catch(SQLException sqle){}
		if(conn != null) try{conn.close();}catch(SQLException sqle){}
	}
%>

deleteTestForm.jsp

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

<%
  String uid=request.getParameter("id");

  Connection conn=null;
  PreparedStatement pstmt=null;
  ResultSet rs=null; 
  
  try{
	String jdbcUrl="jdbc:oracle:thin:@localhost:1521:xe";
    String dbId="scott";
    String dbPass="tiger";
	 
	Class.forName("oracle.jdbc.driver.OracleDriver");
	conn=DriverManager.getConnection(jdbcUrl,dbId ,dbPass );
	
	String sql= "select * from member1 where id=?";
	pstmt=conn.prepareStatement(sql);
	pstmt.setString(1, uid);
	rs=pstmt.executeQuery();

	if(rs.next()){
	  String id= rs.getString("id");
      String passwd= rs.getString("passwd");
      String name= rs.getString("name");
      Timestamp register=rs.getTimestamp("reg_date");

%>
<html>
<head><title>Delete record</title></head>

<body>
  <h2>Delete record on member1 table</h2>

  <FORM METHOD="post" ACTION="deleteTest.jsp">
  <input type=hidden name="id" value="<%=id %>">
  <%--   ID : <%=id %><p> --%>
    Password: <INPUT TYPE="password" NAME="passwd"><p>
   <%--  Name:<%=name%><p> --%>
    <INPUT TYPE="submit" VALUE="Delete">
  </FORM>

</body>
</html>

<%  } // if end
  }catch(Exception e){ 
		e.printStackTrace();
  }finally{
	    if(rs != null) try{rs.close();}catch(SQLException sqle){}
		if(pstmt != null) try{pstmt.close();}catch(SQLException sqle){}
		if(conn != null) try{conn.close();}catch(SQLException sqle){}
  }
%>

deleteTest.jsp

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

<%
  request.setCharacterEncoding("euc-kr");

  String id= request.getParameter("id");
  String passwd= request.getParameter("passwd");

  Connection conn=null;
  PreparedStatement pstmt=null;
  ResultSet rs=null;

  try{
	String jdbcUrl="jdbc:oracle:thin:@localhost:1521:xe";
    String dbId="scott";
    String dbPass="tiger";
	 
	Class.forName("oracle.jdbc.driver.OracleDriver");
	conn=DriverManager.getConnection(jdbcUrl,dbId ,dbPass );
	
	String sql= "select id, passwd from member1 where id= ?";
	pstmt=conn.prepareStatement(sql);
    pstmt.setString(1,id);
	rs=pstmt.executeQuery();
    
	if(rs.next()){ 
		String rId=rs.getString("id");
		String rPasswd=rs.getString("passwd");
      if(id.equals(rId) && passwd.equals(rPasswd)){
	    sql= "delete from member1 where id= ? ";
	    pstmt=conn.prepareStatement(sql);
	    pstmt.setString(1,id);
	    pstmt.executeUpdate();
%>
			<script>
				alert("Successfully deleted.");
				location.href="selectTest.jsp";
	    	</script>
<%	    
	   }else{
%>
		  <script>
		  	alert("Wrong password.");
		  	history.go(-1);
		  </script>
<%	   }%>


<% }else{ %>

		<script>
			alert("Wrong ID.");
			history.go(-1);
		</script>
		
<%	
	 } 

	}catch(Exception e){ 
		e.printStackTrace();
	}finally{
		if(rs != null) try{rs.close();}catch(SQLException sqle){}
		if(pstmt != null) try{pstmt.close();}catch(SQLException sqle){}
		if(conn != null) try{conn.close();}catch(SQLException sqle){}
	}
%>

++ SimpleDateFormat in selectTest.jsp

You must import the SimpleDateFormat.

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

<html>
<head><title>Display records</title></head>
<body>
  <h2>Member1 Table</h2>
  
  <a href="insertTestForm.jsp">Sign Up</a>
  <TABLE width="550" border="1">
  <TR>
  	<TD width="100">ID</TD>
  	<TD width="100">Password</TD>
  	<TD width="100">Name</TD>
  	<TD width="250">Joined Date</TD>
  	<TD width="100">Edit</TD>
  	<TD width="100">Delete</TD>
  </TR>

<%
  Connection conn=null;
  PreparedStatement pstmt=null;
  ResultSet rs=null;
  ResultSet rs01=null;
  int cnt=0;
  
  try{
	String jdbcUrl="jdbc:oracle:thin:@localhost:1521:xe";
    String dbId="scott";
    String dbPass="tiger";
	 
	Class.forName("oracle.jdbc.driver.OracleDriver");
	conn=DriverManager.getConnection(jdbcUrl,dbId ,dbPass );

	pstmt=conn.prepareStatement("select count(*) from member1");
	rs01=pstmt.executeQuery();
	if(rs01.next()){
		cnt = rs01.getInt(1);
//		cnt = rs01.getInt("count(*)");
	}	
	
	String sql= "select * from member1";
	pstmt=conn.prepareStatement(sql);
	rs=pstmt.executeQuery();
	
	SimpleDateFormat sd = new SimpleDateFormat("MM.dd.yyyy HH:mm:ss");

	while(rs.next()){
	  String id= rs.getString("id");
      String passwd= rs.getString("passwd");
      String name= rs.getString("name");
      Timestamp register=rs.getTimestamp("reg_date");

%>
     <TR>
  	   <TD width="100"><%=id%></TD>
  	   <TD width="100"><%=passwd%></TD>
  	   <TD width="100"><%=name%></TD>
  	   <TD width="250"><%=sd.format(register)%></TD>
  	   <TD width="100">
  	   		<a href="updateTestForm.jsp?id=<%=id%>">
  	  		 Edit
  	   		</a>  	   
  	   </TD>
  	   <TD width="100">
  	   		<a href="deleteTestForm.jsp?id=<%=id %>">
  	   		 Delete
  	   		</a>
  	   </TD>
    </TR>
<%  } 
  }catch(Exception e){ 
		e.printStackTrace();
  }finally{
	    if(rs != null) try{rs.close();}catch(SQLException sqle){}
		if(pstmt != null) try{pstmt.close();}catch(SQLException sqle){}
		if(conn != null) try{conn.close();}catch(SQLException sqle){}
  }
%>
</TABLE>

Total members :<%=cnt %>


</body>
</html>

저작자표시 비영리 변경금지

'Codes & Projects' 카테고리의 다른 글

Java) ATM - While loops / If statements (0)	2022.09.03
JSP / CSS) Log In - JavaBean, Action tags (0)	2022.09.02
JSP / CSS ) Sign up - JavaBean, Action tags (0)	2022.08.31
JSP) Forward /Include action tags - Web page templates (0)	2022.08.30
JSP) Log in / Shopping cart (0)	2022.08.29

agilemeadow Devlog

JSP/ Oracle) Members list

'Codes & Projects' 카테고리의 다른 글

+ Recent posts

티스토리툴바