add_student
For an example I added username ,first name and last name to student table and get auto incremented primay key id and save email and telephone to student_contact table.DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `add_student`(p_username varchar(20),p_firstname varchar(20),p_lastname varchar(20),p_email varchar(20),p_telephone varchar(20)) BEGIN DECLARE stu_id int(11); START TRANSACTION; INSERT INTO student(username,first_name,last_name) VALUES(p_username,p_firstname,p_lastname); SELECT id FROM student WHERE username=p_username INTO stu_id; INSERT INTO student_contact(stu_id,email,telephone) VALUES(stu_id,p_email,p_telephone); COMMIT; END
search_students
This sp returns student records.
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `search_students`(p_first_name varchar(20))
BEGIN
SELECT * FROM student WHERE first_name LIKE concat(p_first_name,'%');
END
get_firstname_by_username
This sp uses input parameter and out put parameterDELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `get_firstname_by_username`(p_username varchar(20),OUT p_firstname varchar(20)) SELECT first_name INTO p_firstname FROM student WHERE username = p_username
StudentDAO.java
call stored procedures.
package codesstore.dao;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import codesstore.db.JDBCConnector;
import codesstore.domain.Student;
public class StudentDAO {
public void addStudent(Student student) {
try {
Connection con = JDBCConnector.getConnection();
CallableStatement proc = con
.prepareCall("{ call add_student(?, ?, ?, ?, ?) }");
proc.setString(1, student.getUsername());
proc.setString(2, student.getFirstName());
proc.setString(3, student.getLastName());
proc.setString(4, student.getEmail());
proc.setString(5, student.getTelephone());
proc.execute();
} catch (SQLException e) {
e.printStackTrace();
}
}
public List<Student> searchStudents(String firstName) {
List<Student> students = new ArrayList<Student>();
try {
Connection con = JDBCConnector.getConnection();
CallableStatement proc = con
.prepareCall("{ call search_students(?) }");
proc.setString(1, firstName);
proc.execute();
ResultSet rs = proc.getResultSet();
while (rs.next()) {
Student s = new Student();
s.setFirstName(rs.getString("first_name"));
s.setLastName(rs.getString("last_name"));
s.setUsername(rs.getString("username"));
students.add(s);
}
} catch (SQLException e) {
e.printStackTrace();
}
return students;
}
public String getFirstNameByUsername(String username) {
String firstName = null;
try {
Connection con = JDBCConnector.getConnection();
CallableStatement proc = con
.prepareCall("{ call get_firstname_by_username(?,?) }");
proc.setString(1, username);
proc.registerOutParameter(2, java.sql.Types.VARCHAR);
proc.execute();
firstName = proc.getString(2);
} catch (SQLException e) {
e.printStackTrace();
}
return firstName;
}
}
Other files
web.xml
<?xml version="1.0" encoding="UTF-8"?> <web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5"> <display-name>SPtesting</display-name> <welcome-file-list> <welcome-file>index.jsp</welcome-file> </welcome-file-list> <servlet> <description></description> <display-name>StudentServlet</display-name> <servlet-name>StudentServlet</servlet-name> <servlet-class>codesstore.controller.StudentServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>StudentServlet</servlet-name> <url-pattern>/StudentServlet</url-pattern> </servlet-mapping> </web-app>
index.php
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Stored Procedure - codesstore.blogspot.com</title>
</head>
<body>
<form action="StudentServlet" method="post">
<table>
<tr>
<td colspan="2">Personal details </td>
</tr>
<tr>
<td>Username</td>
<td><input name="username" type="text"/></td>
</tr>
<tr>
<td>First Name</td>
<td><input name="fname" type="text"/></td>
</tr>
<tr>
<td>Last Name</td>
<td><input name="lname" type="text"/></td>
</tr>
<tr>
<td colspan="2">Contact details </td>
</tr>
<tr>
<td>Email</td>
<td><input name="email" type="text"/></td>
</tr>
<tr>
<td>Telephone</td>
<td><input name="telephone" type="text"/></td>
</tr>
<tr>
<td></td>
<td><input type="submit" value="Add" name="action"> </td>
</tr>
</table>
</form>
Search by First name
<form action="StudentServlet" method="post">
<input name="fname" type="text"/>
<input type="submit" value="Search" name="action">
</form>
Get first name by Username
<form action="StudentServlet" method="post">
<input name="username" type="text"/>
<input type="submit" value="GetFirstName" name="action" >
</form>
<c:forEach items="${students}" var="student">
${student.username}
${student.firstName}
${student.lastName}
<br/>
</c:forEach>
<c:if test="${firstName != null}">
${firstName}
</c:if>
</body>
</html>
StudentServlet.java
package codesstore.controller;
import java.io.IOException;
import java.util.List;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import codesstore.dao.StudentDAO;
import codesstore.domain.Student;
public class StudentServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public StudentServlet() {
super();
}
protected void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
}
protected void doPost(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
String action = request.getParameter("action");
if (action != null && action.equals("Search")) {
String firstName = request.getParameter("fname");
StudentDAO studentDAO = new StudentDAO();
List<Student> students = studentDAO.searchStudents(firstName);
request.setAttribute("students", students);
RequestDispatcher rd = request.getRequestDispatcher("index.jsp");
rd.forward(request, response);
} else if (action != null && action.equals("GetFirstName")) {
String username = request.getParameter("username");
StudentDAO studentDAO = new StudentDAO();
String firstName = studentDAO.getFirstNameByUsername(username);
request.setAttribute("firstName", firstName);
RequestDispatcher rd = request.getRequestDispatcher("index.jsp");
rd.forward(request, response);
} else if (action != null && action.equals("Add")) {
String userName = request.getParameter("username");
String firstName = request.getParameter("fname");
String lastName = request.getParameter("lname");
String email = request.getParameter("email");
String telephone = request.getParameter("telephone");
Student student = new Student();
student.setUsername(userName);
student.setFirstName(firstName);
student.setLastName(lastName);
student.setEmail(email);
student.setTelephone(telephone);
StudentDAO studentDAO = new StudentDAO();
studentDAO.addStudent(student);
RequestDispatcher rd = request.getRequestDispatcher("index.jsp");
rd.forward(request, response);
}
}
}
Student.java
package codesstore.domain;
public class Student {
String username;
String firstName;
String lastName;
String email;
String telephone;
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getTelephone() {
return telephone;
}
public void setTelephone(String telephone) {
this.telephone = telephone;
}
}
JDBCConnector.java
package codesstore.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.logging.Level;
import java.util.logging.Logger;
public class JDBCConnector {
private static Connection con;
public static Connection getConnection() {
if (con == null) {
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.
getConnection("jdbc:mysql://localhost:3306/mydbsp", "root", "123");
} catch (Exception ex) {
Logger.getLogger(JDBCConnector.class.getName()).log(Level.SEVERE, null, ex);
}
}
return con;
}
}
sql tables
CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `first_name` varchar(45) DEFAULT NULL, `last_name` varchar(45) DEFAULT NULL, `username` varchar(45) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `username` (`username`) ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;
CREATE TABLE `student_contact` ( `stu_id` int(11) NOT NULL, `email` varchar(20) DEFAULT NULL, `telephone` varchar(20) DEFAULT NULL, PRIMARY KEY (`stu_id`), CONSTRAINT `FK_student_personal_1` FOREIGN KEY (`stu_id`) REFERENCES `student` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
File structure in eclipse.
No comments:
Post a Comment