Stored Procedures with JAVA

Stored Procedures with JAVA using MySQL

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.
view plaincopy to clipboardprint?
  1. DELIMITER $$  
  2.   
  3. 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))  
  4. BEGIN  
  5.     DECLARE stu_id int(11);  
  6.     START TRANSACTION;  
  7.         INSERT INTO student(username,first_name,last_name)  
  8.         VALUES(p_username,p_firstname,p_lastname);  
  9.         SELECT id FROM student WHERE username=p_username INTO stu_id;  
  10.         INSERT INTO student_contact(stu_id,email,telephone) VALUES(stu_id,p_email,p_telephone);  
  11.     COMMIT;  
  12.   
  13. END  

search_students

This sp returns student records.
view plaincopy to clipboardprint?
  1. DELIMITER $$  
  2.   
  3. CREATE DEFINER=`root`@`localhost` PROCEDURE `search_students`(p_first_name varchar(20))  
  4. BEGIN  
  5.      SELECT * FROM student WHERE first_name LIKE concat(p_first_name,'%');  
  6. END  

get_firstname_by_username

This sp uses input parameter and out put parameter
view plaincopy to clipboardprint?
  1. DELIMITER $$  
  2.   
  3. CREATE DEFINER=`root`@`localhost` PROCEDURE `get_firstname_by_username`(p_username varchar(20),OUT p_firstname varchar(20))  
  4. SELECT first_name INTO p_firstname  
  5. FROM student  
  6. WHERE username = p_username  

StudentDAO.java

call stored procedures.
view plaincopy to clipboardprint?
  1. package codesstore.dao;  
  2.   
  3. import java.sql.CallableStatement;  
  4. import java.sql.Connection;  
  5. import java.sql.ResultSet;  
  6. import java.sql.SQLException;  
  7. import java.util.ArrayList;  
  8. import java.util.List;  
  9.   
  10. import codesstore.db.JDBCConnector;  
  11. import codesstore.domain.Student;  
  12.   
  13. public class StudentDAO {  
  14.     public void addStudent(Student student) {  
  15.         try {  
  16.             Connection con = JDBCConnector.getConnection();  
  17.             CallableStatement proc = con  
  18.                     .prepareCall("{ call add_student(?, ?, ?, ?, ?) }");  
  19.             proc.setString(1, student.getUsername());  
  20.             proc.setString(2, student.getFirstName());  
  21.             proc.setString(3, student.getLastName());  
  22.             proc.setString(4, student.getEmail());  
  23.             proc.setString(5, student.getTelephone());  
  24.             proc.execute();  
  25.         } catch (SQLException e) {  
  26.             e.printStackTrace();  
  27.         }  
  28.     }  
  29.   
  30.     public List<Student> searchStudents(String firstName) {  
  31.         List<Student> students = new ArrayList<Student>();  
  32.         try {  
  33.             Connection con = JDBCConnector.getConnection();  
  34.             CallableStatement proc = con  
  35.                     .prepareCall("{ call search_students(?) }");  
  36.   
  37.             proc.setString(1, firstName);  
  38.             proc.execute();  
  39.             ResultSet rs = proc.getResultSet();  
  40.             while (rs.next()) {  
  41.                 Student s = new Student();  
  42.                 s.setFirstName(rs.getString("first_name"));  
  43.                 s.setLastName(rs.getString("last_name"));  
  44.                 s.setUsername(rs.getString("username"));  
  45.                 students.add(s);  
  46.             }  
  47.   
  48.         } catch (SQLException e) {  
  49.             e.printStackTrace();  
  50.         }  
  51.         return students;  
  52.     }  
  53.   
  54.     public String getFirstNameByUsername(String username) {  
  55.         String firstName = null;  
  56.         try {  
  57.             Connection con = JDBCConnector.getConnection();  
  58.             CallableStatement proc = con  
  59.                     .prepareCall("{ call get_firstname_by_username(?,?) }");  
  60.   
  61.             proc.setString(1, username);  
  62.             proc.registerOutParameter(2, java.sql.Types.VARCHAR);  
  63.             proc.execute();  
  64.             firstName = proc.getString(2);  
  65.   
  66.         } catch (SQLException e) {  
  67.             e.printStackTrace();  
  68.         }  
  69.   
  70.         return firstName;  
  71.     }  
  72.   
  73. }   

Other files

web.xml

view plaincopy to clipboardprint?
  1. <?xml version="1.0" encoding="UTF-8"?>  
  2. <web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
  3.     xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"  
  4.     xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"  
  5.     id="WebApp_ID" version="2.5">  
  6.     <display-name>SPtesting</display-name>  
  7.     <welcome-file-list>  
  8.         <welcome-file>index.jsp</welcome-file>  
  9.     </welcome-file-list>  
  10.     <servlet>  
  11.         <description></description>  
  12.         <display-name>StudentServlet</display-name>  
  13.         <servlet-name>StudentServlet</servlet-name>  
  14.         <servlet-class>codesstore.controller.StudentServlet</servlet-class>  
  15.     </servlet>  
  16.     <servlet-mapping>  
  17.         <servlet-name>StudentServlet</servlet-name>  
  18.         <url-pattern>/StudentServlet</url-pattern>  
  19.     </servlet-mapping>  
  20. </web-app>   

index.php

view plaincopy to clipboardprint?
  1. <%@ page language="java" contentType="text/html; charset=ISO-8859-1"  
  2.     pageEncoding="ISO-8859-1"%>  
  3. <%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>      
  4. <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">  
  5. <html>  
  6. <head>  
  7. <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">  
  8. <title>Stored Procedure - codesstore.blogspot.com</title>  
  9. </head>  
  10. <body>  
  11.     <form action="StudentServlet" method="post">  
  12.         <table>  
  13.             <tr>  
  14.                 <td colspan="2">Personal details    </td>  
  15.             </tr>  
  16.             <tr>  
  17.                 <td>Username</td>  
  18.                 <td><input name="username" type="text"/></td>  
  19.             </tr>  
  20.             <tr>  
  21.                 <td>First Name</td>  
  22.                 <td><input name="fname" type="text"/></td>  
  23.             </tr>  
  24.             <tr>  
  25.                 <td>Last Name</td>  
  26.                 <td><input name="lname" type="text"/></td>  
  27.             </tr>  
  28.             <tr>  
  29.                 <td colspan="2">Contact details    </td>  
  30.             </tr>  
  31.             <tr>  
  32.                 <td>Email</td>  
  33.                 <td><input name="email" type="text"/></td>  
  34.             </tr>  
  35.             <tr>  
  36.                 <td>Telephone</td>  
  37.                 <td><input name="telephone" type="text"/></td>  
  38.             </tr>  
  39.             <tr>  
  40.                 <td></td>  
  41.                 <td><input type="submit" value="Add" name="action"> </td>  
  42.             </tr>  
  43.         </table>  
  44.     </form>  
  45.     Search by First name  
  46.     <form action="StudentServlet" method="post">  
  47.         <input name="fname" type="text"/>  
  48.         <input type="submit" value="Search" name="action">   
  49.     </form>  
  50.     Get first name by Username  
  51.     <form action="StudentServlet" method="post">  
  52.         <input name="username" type="text"/>  
  53.         <input type="submit" value="GetFirstName" name="action" >   
  54.     </form>  
  55.       
  56.     <c:forEach items="${students}" var="student">  
  57.         ${student.username}  
  58.         ${student.firstName}  
  59.         ${student.lastName}  
  60.         <br/>  
  61.     </c:forEach>  
  62.   
  63.     <c:if test="${firstName != null}">  
  64.         ${firstName}  
  65.     </c:if>  
  66.       
  67.       
  68. </body>  
  69. </html>  

StudentServlet.java

view plaincopy to clipboardprint?
  1. package codesstore.controller;  
  2.   
  3. import java.io.IOException;  
  4. import java.util.List;  
  5.   
  6. import javax.servlet.RequestDispatcher;  
  7. import javax.servlet.ServletException;  
  8. import javax.servlet.http.HttpServlet;  
  9. import javax.servlet.http.HttpServletRequest;  
  10. import javax.servlet.http.HttpServletResponse;  
  11.   
  12. import codesstore.dao.StudentDAO;  
  13. import codesstore.domain.Student;  
  14.   
  15. public class StudentServlet extends HttpServlet {  
  16.     private static final long serialVersionUID = 1L;  
  17.   
  18.     public StudentServlet() {  
  19.         super();  
  20.   
  21.     }  
  22.   
  23.     protected void doGet(HttpServletRequest request,  
  24.             HttpServletResponse response) throws ServletException, IOException {  
  25.   
  26.     }  
  27.   
  28.     protected void doPost(HttpServletRequest request,  
  29.             HttpServletResponse response) throws ServletException, IOException {  
  30.         String action = request.getParameter("action");  
  31.         if (action != null && action.equals("Search")) {  
  32.               
  33.             String firstName = request.getParameter("fname");  
  34.             StudentDAO studentDAO = new StudentDAO();  
  35.             List<Student> students = studentDAO.searchStudents(firstName);  
  36.             request.setAttribute("students", students);  
  37.             RequestDispatcher rd = request.getRequestDispatcher("index.jsp");  
  38.             rd.forward(request, response);  
  39.           
  40.         } else if (action != null && action.equals("GetFirstName")) {  
  41.               
  42.             String username = request.getParameter("username");  
  43.             StudentDAO studentDAO = new StudentDAO();  
  44.             String firstName = studentDAO.getFirstNameByUsername(username);  
  45.               
  46.             request.setAttribute("firstName", firstName);  
  47.             RequestDispatcher rd = request.getRequestDispatcher("index.jsp");  
  48.             rd.forward(request, response);  
  49.   
  50.         } else if (action != null && action.equals("Add")) {  
  51.   
  52.             String userName = request.getParameter("username");  
  53.             String firstName = request.getParameter("fname");  
  54.             String lastName = request.getParameter("lname");  
  55.             String email = request.getParameter("email");  
  56.             String telephone = request.getParameter("telephone");  
  57.   
  58.             Student student = new Student();  
  59.             student.setUsername(userName);  
  60.             student.setFirstName(firstName);  
  61.             student.setLastName(lastName);  
  62.             student.setEmail(email);  
  63.             student.setTelephone(telephone);  
  64.   
  65.             StudentDAO studentDAO = new StudentDAO();  
  66.             studentDAO.addStudent(student);  
  67.               
  68.             RequestDispatcher rd = request.getRequestDispatcher("index.jsp");  
  69.             rd.forward(request, response);  
  70.               
  71.         }  
  72.   
  73.     }  
  74.   
  75. }  

Student.java

view plaincopy to clipboardprint?
  1. package codesstore.domain;  
  2.   
  3. public class Student {  
  4.     String username;  
  5.     String firstName;  
  6.     String lastName;  
  7.     String email;  
  8.     String telephone;  
  9.   
  10.     public String getUsername() {  
  11.         return username;  
  12.     }  
  13.   
  14.     public void setUsername(String username) {  
  15.         this.username = username;  
  16.     }  
  17.   
  18.     public String getFirstName() {  
  19.         return firstName;  
  20.     }  
  21.   
  22.     public void setFirstName(String firstName) {  
  23.         this.firstName = firstName;  
  24.     }  
  25.   
  26.     public String getLastName() {  
  27.         return lastName;  
  28.     }  
  29.   
  30.     public void setLastName(String lastName) {  
  31.         this.lastName = lastName;  
  32.     }  
  33.   
  34.     public String getEmail() {  
  35.         return email;  
  36.     }  
  37.   
  38.     public void setEmail(String email) {  
  39.         this.email = email;  
  40.     }  
  41.   
  42.     public String getTelephone() {  
  43.         return telephone;  
  44.     }  
  45.   
  46.     public void setTelephone(String telephone) {  
  47.         this.telephone = telephone;  
  48.     }  
  49.   
  50. }  

JDBCConnector.java

view plaincopy to clipboardprint?
  1. package codesstore.db;  
  2.   
  3. import java.sql.Connection;  
  4. import java.sql.DriverManager;  
  5. import java.util.logging.Level;  
  6. import java.util.logging.Logger;  
  7.   
  8. public class JDBCConnector {  
  9.     private static Connection con;  
  10.     public static Connection getConnection() {  
  11.         if (con == null) {  
  12.             try {  
  13.                 Class.forName("com.mysql.jdbc.Driver");  
  14.                 con = DriverManager.  
  15.                         getConnection("jdbc:mysql://localhost:3306/mydbsp", "root", "123");  
  16.             } catch (Exception ex) {  
  17.                 Logger.getLogger(JDBCConnector.class.getName()).log(Level.SEVERE, null, ex);  
  18.             }  
  19.         }  
  20.         return con;  
  21.     }  
  22. }  

sql tables
view plaincopy to clipboardprint?
  1. CREATE TABLE `student` (  
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,  
  3.   `first_name` varchar(45) DEFAULT NULL,  
  4.   `last_name` varchar(45) DEFAULT NULL,  
  5.   `username` varchar(45) NOT NULL,  
  6.   PRIMARY KEY (`id`),  
  7.   UNIQUE KEY `username` (`username`)  
  8. ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;  

view plaincopy to clipboardprint?
  1. CREATE TABLE `student_contact` (  
  2.   `stu_id` int(11) NOT NULL,  
  3.   `email` varchar(20) DEFAULT NULL,  
  4.   `telephone` varchar(20) DEFAULT NULL,  
  5.   PRIMARY KEY (`stu_id`),  
  6.   CONSTRAINT `FK_student_personal_1` FOREIGN KEY (`stu_id`) REFERENCES `student` (`id`) ON DELETE CASCADE ON UPDATE CASCADE  
  7. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;  

File structure in eclipse.
  Saturday, February 04, 2012
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)