AF
HomeTagSubmit NotesAsk AnythingLoginSubscribe Us
AF
1. Feel Free to ask and submit anything on Anyforum.in and get satisfactory answer
2. Registration is not compulsory, you can directly login via google or facebook
3. Our Experts are looking for yours ?.



java-jsp: Create web form using java with mysql database

Hello Team,
I´m new to java and given a below task to complete, Please provide the java code/logic to achive it.
We have two tables dept and emp in mysql database and need to create login page and pass the control to next page then select the value and click Ok to show the result.

Ex: first page should have Dep Name which is drop down list from mysql database table dept and password text box and need to validate with dept table coloumn password.
when user click on OK button if password valid then nagivage to 2nd page and show employee coloum with drop down list associated with page 1 dept value and when user select value click OK button then employee details should show in the same page as result.

Please help me with the above requirement to achive in java/jsp/ajax.
Thank you!
Pankaj

java x 210
jsp x 32
Posted On : 2016-10-15 22:18:34.0
profile Mallik M - anyforum.in Mallik M
400
up-rate
4
down-rate

Answers


Just consider below are the tables as per given requirement:
Department:
----------------------------------------
CREATE TABLE "DEPARTMENT"
( "ID" NUMBER,
"NAME" VARCHAR2(1000),
"PASSWORD" VARCHAR2(4000),
CONSTRAINT "DEPARTMENT_PK" PRIMARY KEY ("ID") ENABLE
)
/

CREATE OR REPLACE TRIGGER "BI_DEPARTMENT"
before insert on "DEPARTMENT"
for each row
begin
select "DEPARTMENT_SEQ".nextval into :NEW.ID from dual;
end;

/
ALTER TRIGGER "BI_DEPARTMENT" ENABLE
/

and

Employee:
------------------------------
CREATE TABLE "EMPLOYEE"
( "ID" NUMBER NOT NULL ENABLE,
"NAME" VARCHAR2(1000),
"PHONE" NUMBER,
"CITY" VARCHAR2(1000),
"DEPT" NUMBER,
CONSTRAINT "EMPLOYEE_PK" PRIMARY KEY ("ID") ENABLE
)
/

CREATE OR REPLACE TRIGGER "BI_EMPLOYEE"
before insert on "EMPLOYEE"
for each row
begin
select "EMPLOYEE_SEQ".nextval into :NEW.ID from dual;
end;

/
ALTER TRIGGER "BI_EMPLOYEE" ENABLE
/

Note:
----------------------------
I have used Oracle database, you can create the same table with same structure in MySQL. below example is just to give a hint, it´s not a standard coding, no framework used:

just create a web project and copy paste below 2 jsps.

index.jsp:
--------------------------------------
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.ResultSet"%><%
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection connection=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
PreparedStatement ps =connection.prepareStatement("select id, name from department");
ResultSet rs=ps.executeQuery();
if(null!=request.getAttribute("err")){
out.print(request.getAttribute("err"));
}
%>
<html>
<form action="login.jsp" method="post">
<select name="dept">
<option value="">Select Department</option>
<%
while(rs.next()){
%>
<option value="<%=rs.getInt(1)%>"><%=rs.getString(2)%></option>
<%
}
connection.close();
%>
</select>
<input type="password" name="password"/>
<input type="submit"/>
</form>
</html>


login.jsp:
--------------------------------
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.ResultSet"%>
<%
if(!request.getParameter("password").trim().equals("") && !request.getParameter("dept").trim().equals("")){
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection connection=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
PreparedStatement ps =connection.prepareStatement("select * from department where id=? and password=?");
ps.setInt(1,Integer.parseInt(request.getParameter("dept").trim()));
ps.setString(2,request.getParameter("password").trim());
ResultSet rs=ps.executeQuery();
if(rs.next()){
ps=connection.prepareStatement("select * from employee where dept=?");
ps.setInt(1,Integer.parseInt(request.getParameter("dept").trim()));
rs=ps.executeQuery();
%>
<select name="employee" onchange="getDetail(this.value)">
<option value="">Select Employee</option>
<%
while(rs.next()){%>
<option value="<%=rs.getInt(1)%>"><%=rs.getString(2) %></option>
<%}%>
</select><div id="detail"></div>
<%
}else{
request.setAttribute("err","Inavlid Department or password");
%>
<jsp:forward page="index.jsp"></jsp:forward>
<%
}
connection.close();
}else{
request.setAttribute("err","Inavlid Department or password");
%>
<jsp:forward page="index.jsp"></jsp:forward>
<%
}
%>
<script type="text/javascript">
function getDetail(a){
var url="detail.jsp?val="+a;

if(window.XMLHttpRequest){
request=new XMLHttpRequest();
}
else if(window.ActiveXObject){
request=new ActiveXObject("Microsoft.XMLHTTP");
}

try{
request.onreadystatechange=getInfo;
request.open("GET",url,true);
request.send();
}catch(e){alert("Unable to connect to server");}
}
function getInfo(){
if(request.readyState==4){
var val=request.responseText;
document.getElementById(´detail´).innerHTML=val;
} else{
document.getElementById(´detail´).innerHTML=´Processing´;
}
}
</script>

detail.jsp:
----------------------------------
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.ResultSet"%>
<%
if(!request.getParameter("val").trim().equals("")){
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection connection=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
PreparedStatement ps =connection.prepareStatement("select * from employee where id=?");
ps.setInt(1,Integer.parseInt(request.getParameter("val").trim()));
ResultSet rs=ps.executeQuery();
%>
<table><tr><th>Name</th><th>Phone</th><th>City</th></tr><%
while(rs.next()){%>
<tr><td><%=rs.getString(2) %></td><td><%=rs.getString(3) %></td><td><%=rs.getString(4) %></td></tr>
<%}%>
</table>
<%
connection.close();
}
%>


Note:
---------------------------
change the driver class name and connection string accordingly in all jsps and don´t forget to add mysql jar file in project.

Click here to download this project

Posted On : 2016-10-17 17:10:43
Satisfied : 1 Yes  0 No
profile Rishi Kumar - anyforum.in Rishi Kumar
523188221961
Reply This Thread
up-rate
1
down-rate

Thanskk Raj for the quick response!
Let me execute and post you the outcome, in my scenario for the ID data type defined as Varchar. Considering that do i need to change any code not to convert from number to string ?

Thanks,
Mallik

Posted On : 2016-10-17 17:36:14
Satisfied : 0 Yes  0 No
profile Mallik M - anyforum.in Mallik M
400
Reply This Thread
up-rate
0
down-rate
Comments
It´s not necessary but it´s better to define id as number. If you use varchar, you have to change getInt and setInt method to getString and setString respectively on all the jsp pages.
profile Rishi Kumar - anyforum.in Rishi Kumar
523  1882  21961
Posted On :2016-10-17 22:44:37.0
Leave a Comment



Post Answer
Please Login First to Post Answer: Login login with facebook - anyforum.in