1
\$\begingroup\$

When a user input employee_id and book_title check its existence before insertion employee_id from student_table and book_title from book_table.

  • If employee_id is available but book_title is not, display an error message(book title not available);
  • If book_title is available and employee_id is not, display a message(employee_id not available);

  • If both are not available display an error message(both employee_id and book_title are not available);

issue_book_controller.jsp

 <%@ page language="java" contentType="text/html; charset=ISO-8859-1"
 pageEncoding="ISO-8859-1"%>
 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" 
 "http://www.w3.org/TR/html4/loose.dtd">


   <%@page import="common.Common_Things"%>
   <%@page import="Issue_Table_Model.Issue_Table_Model"%><html>
   <head>
   <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">

   </head>
   <body>
   <jsp:useBeanid="obj_Issue_Use_Bean"
   class="common_use_bean.Issue_Use_Bean"> 
   </jsp:useBean>
   <jsp:setProperty property="*" name="obj_Issue_Use_Bean"/>

  <%

    Issue_Table_Model obj_Issue_Table_Model=new Issue_Table_Model();

    int flag=obj_Issue_Table_Model.issue_book(obj_Issue_Use_Bean);

    if(flag==1){

      session.setAttribute("issue_book_message","Book Issued 
         Successfully");

       }

       else if(flag==20){       

       session.setAttribute("issue_book_message","Employee with Id Number 
       "+obj_Issue_Use_Bean.getEmployee_id()+" not available");

        }

       else if(flag==18){       

       session.setAttribute("issue_book_message","Book with Title 
       "+obj_Issue_Use_Bean.getBook_title()+" not available");

        }
       else if(flag==17){

           session.setAttribute("issue_book_message","Failed");
       } 

   %>

     <script type="text/javascript">
        window.location.href="<%=Common_Things.url%>/issue-book";
     </script>

   </body>
  </html>

issue_table_model.java

    public int issue_book(Issue_Use_Bean obj_Issue_Use_Bean){
    Connection connection=null;
    DBConnection_LMS_Portal obj_DBConnection_SMS_Portal=new 
    DBConnection_LMS_Portal();
    connection=obj_DBConnection_SMS_Portal.getConnection();
    PreparedStatement ps=null;
    ResultSet rs=null;
    int flag=0;
                   TimeZone.setDefault(TimeZone.getTimeZone("USA"));
    SimpleDateFormat format = new SimpleDateFormat("YYYY-MM-DD hh:mm:ss ");

      Date today4 = new Date();
      String date4 = format.format(today4);
  try { 

     String query="select * from student_table, book_table where 
     student_table.employee_id=? and book_table.book_title=?";
     ps=connection.prepareStatement(query);
     ps.setString(1, obj_Issue_Use_Bean.getEmployee_id());
     ps.setString(2, obj_Issue_Use_Bean.getBook_title()); 
     System.out.println(ps);
     rs=ps.executeQuery();

         if(obj_Issue_Use_Bean.getEmployee_id()==null) {        
                           flag=20; 
          }

         else if(obj_Issue_Use_Bean.getBook_title()==null) {
                    flag=18;
          }

         else if(rs.next()){

            query="insert into issue_book(" +
                        "employee_id," +
                        "book_title," +
                        "barcode," +
                        "issue_date," +
                        "return_date," +
                        "status," +
                        "issue_sl_no," +
                        "description) " +
                        "values(?,?,?,?,?,?,?,?)";

                ps=connection.prepareStatement(query);
                ps.setString(1, obj_Issue_Use_Bean.getEmployee_id());
                ps.setString(2, obj_Issue_Use_Bean.getBook_title());
                ps.setString(3, obj_Issue_Use_Bean.getBarcode());
                ps.setString(4, date4);
                ps.setString(5, obj_Issue_Use_Bean.getReturn_date());
                ps.setString(6, obj_Issue_Use_Bean.getStatus());

                Sequence_Issue_Id  obj_Sequence_Issue_Id=new 
                Sequence_Issue_Id();
                String issue_sl_no=obj_Sequence_Issue_Id.get_issue_id();
                ps.setString(7,issue_sl_no);
                ps.setString(8, obj_Issue_Use_Bean.getDescription());
                System.out.println(ps);
                flag=ps.executeUpdate();
                if(obj_Issue_Use_Bean.getStatus()!=null){
                  if(obj_Issue_Use_Bean.getStatus().contains("borrowed")){     
                    query="update book_table set 
                    total_book_available=total_book_available-1  where 
                    book_title=?";
                ps=connection.prepareStatement(query);
                ps.setString(1, obj_Issue_Use_Bean.getBook_title());
                System.out.println(ps);
                ps.executeUpdate();
                  } 
                }
                query="update serial_number set book_status='Not Available' 
                where bar_code=?";
                ps=connection.prepareStatement(query);
                ps.setString(1, obj_Issue_Use_Bean.getBarcode());
                System.out.println(ps);
                ps.executeUpdate();


         }  
         else{
                flag=17;
            }

     }catch(Exception e){
      e.printStackTrace();
     }
     finally{
     if(connection!=null){
        try {
                connection.close();
            }
            catch (Exception e2) {
            // TODO: handle exception
            }
     }
     if(ps!=null){
        try {

            ps.close();
            }
        catch (Exception e2) {
            // TODO: handle exception
            }
     }
     if(rs!=null){
        try {

            rs.close();
            }
        catch (Exception e2) {
            // TODO: handle exception
            }
     }

    }

    return flag;

    }   
\$\endgroup\$
1
  • 4
    \$\begingroup\$ The code formatting here makes it really hard to read. Can you first paste it into your favorite editor (IntelliJ/Eclipse/Netbeans/...), press the autoformat button in there. Then paste the resulting code back here. After pasting select the entire code block and click the " { } " button at the top of the editor field (or press ctrl+K) to fix the indentation if needed. \$\endgroup\$
    – Imus
    Commented Dec 18, 2019 at 9:52

1 Answer 1

2
\$\begingroup\$

Format

So first up, as has been said in the comments, code formatting is important. Consistent formatting makes your code a lot easier to read. If it really looks the same in your editor as in the question, then you should try reformatting it in a good Java editor:

public int issue_book(Issue_Use_Bean obj_Issue_Use_Bean) {
    Connection connection = null;
    DBConnection_LMS_Portal obj_DBConnection_SMS_Portal = new DBConnection_LMS_Portal();
    connection = obj_DBConnection_SMS_Portal.getConnection();
    PreparedStatement ps = null;
    ResultSet rs = null;
    int flag = 0;
    TimeZone.setDefault(TimeZone.getTimeZone("USA"));
    SimpleDateFormat format = new SimpleDateFormat("YYYY-MM-DD hh:mm:ss ");

    Date today4 = new Date();
    String date4 = format.format(today4);
    try {
        String query = "select * from student_table, book_table where student_table.employee_id=? and book_table.book_title=?";
        ps = connection.prepareStatement(query);
        ps.setString(1, obj_Issue_Use_Bean.getEmployee_id());
        ps.setString(2, obj_Issue_Use_Bean.getBook_title());
        System.out.println(ps);
        rs = ps.executeQuery();

        if (obj_Issue_Use_Bean.getEmployee_id() == null) {
            flag = 20;
        } else if (obj_Issue_Use_Bean.getBook_title() == null) {
            flag = 18;
        } else if (rs.next()) {
            query = "insert into issue_book(" +
                    "employee_id," +
                    "book_title," +
                    "barcode," +
                    "issue_date," +
                    "return_date," +
                    "status," +
                    "issue_sl_no," +
                    "description) " +
                    "values(?,?,?,?,?,?,?,?)";

            ps = connection.prepareStatement(query);
            ps.setString(1, obj_Issue_Use_Bean.getEmployee_id());
            ps.setString(2, obj_Issue_Use_Bean.getBook_title());
            ps.setString(3, obj_Issue_Use_Bean.getBarcode());
            ps.setString(4, date4);
            ps.setString(5, obj_Issue_Use_Bean.getReturn_date());
            ps.setString(6, obj_Issue_Use_Bean.getStatus());

            Sequence_Issue_Id obj_Sequence_Issue_Id = new Sequence_Issue_Id();
            String issue_sl_no = obj_Sequence_Issue_Id.get_issue_id();
            ps.setString(7, issue_sl_no);
            ps.setString(8, obj_Issue_Use_Bean.getDescription());
            System.out.println(ps);
            flag = ps.executeUpdate();
            if (obj_Issue_Use_Bean.getStatus() != null) {
                if (obj_Issue_Use_Bean.getStatus().contains("borrowed")) {
                    query = "update book_table set total_book_available=total_book_available-1  where book_title=?";
                    ps = connection.prepareStatement(query);
                    ps.setString(1, obj_Issue_Use_Bean.getBook_title());
                    System.out.println(ps);
                    ps.executeUpdate();
                }
            }
            query = "update serial_number set book_status='Not Available' where bar_code=?";
            ps = connection.prepareStatement(query);
            ps.setString(1, obj_Issue_Use_Bean.getBarcode());
            System.out.println(ps);
            ps.executeUpdate();
        } else {
            flag = 17;
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (connection != null) {
            try {
                connection.close();
            } catch (Exception e2) {
                // TODO: handle exception
            }
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (Exception e2) {
                // TODO: handle exception
            }
        }
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e2) {
                // TODO: handle exception
            }
        }
    }
    return flag;
}

Cleanup

The code is littered with TODO and System.out.println statements. These can both be useful during development, however by the time your code gets merged into source control / ready for review all of these should be cleaned up / resolved.

Naming

There's some standard naming conventions for java, which include things like public method names. To comply with the convention, I'd expect your method to be named issueBook.

Variable names should also be descriptive. Appending numbers onto them doesn't make the code easier to read. date4 for example might be better named formattedDate.

TimeZone

Your setting the default timezone. This seems a bit odd. There's probably a better way to achieve what you're after.

Break up your code

There's an awful lot going on in one method. You want to create layers of abstraction within your code. This will make more approachable and easier to understand. A good start might be to break each of the SQL queries out into their own method. The top level method then becomes a higher level orchestra-tor of these calls.

Magic Strings

There's a lot of strings in your code (table names, column names, values to insert into columns etc). You should consider putting some of them into constants or enums. This can help prevent problems caused by future typos.

Flag

Flag's not a great name for a variable. You're also setting it to not particularly obvious magic numbers (0, 17, 20...). It would be clearer if these numbers were named and in constants. It would also make it easier to ensure that the front and back end stay in sync if they're using the same constants, rather than relying on both sides remembering to use 18. Looking at the front end code, you're essentially mapping this 'flag' code to a String that's displayed. Rather than the flag being returned, one option might be for issueBook to return the success/failure message to be displayed.

Exceptions / Missing Condition

What's supposed to happen if the first execute throws an exception? At the moment, it returns a flag of '0', which the front-end doesn't seem to handle. It's generally a bad idea to swallow exceptions, without taking some kind of action to make sure that they are being handled correctly (in this instance it could be as simple as setting flag=17 which is your failed message).

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.