0

Day 36 – Record Locking


Solution

  • If database monitors locking process too frequent, leads to inefficiency.


Optimistic Locking – UPDATE, COMMIT

  • Lock when there is a change or updates
  • Both users can retrieve the record
  • When the user updates the record, the database will lock user A to disallow user A to update.

  • Problems

    • User A (value=1000, rcdversion=1)
    • User B (value=1000, rcdversion=1) – If update, rcdversion+1
    • After the update:User A: rcdversion=1User B: rcdversion=2


    Solution

    UPDATE <table>

    SET …..

    rcdversion=rcdversion+1

    WHERE primarykey=”…..”

    AND rcdversion=original value

    Commitment Control

    • Maintains integrity of database where each ‘logical’ transaction consist of multiple updates or inserts or deletes. (Receiver and Sender)
    • Logical transactions happened, updates three tables.
    • If one of them not updates, the transaction is rollback.
      conn.setAutoCommit(true);
      conn.setAutoCommit(false);
    • take control
    • will update until execute

    update statements

    conn.commit();
    conn.rollback();

    If master file splits, more secure because master file being copied by others is meaningless.

    E.g. code tables

    • country codes
    • currency codes

    Standalone and Dependent

    • Master file
    • Code file
    • Transaction file
    • Audit log file

    • Triggers: Automatic
    • Stored Procedures: functions to be called.
    • Oracle/PostgreSQL: PL/SQL
    • Microsoft SQL Server: Transact/SQL

    • Do not display for the first time until field entered.
    • AssetMasterBean(Collection) – FaultsBean
    0

    Day 32 – Fault Report

    iReport – To use the grouping feature

    1. Under the ‘data’ sub-menu under Report Query
      • the SQL statement must have ‘ORDER BY’ clause.
    2. Click on group icon
    3. Click on ‘add’ button to add on or more groups.
      • Add 2 groups (companyid and assetid)
      • group name = any descriptive name (e.g. By company, By assetid)
      • group expression = the control field (e.g. $F{companyid}
    4. After the groups have been defined
    • The appropriate bands will be created
    0

    Day 30 – Lookup (Open a new window to search)


    Basically codes in jsp

    1. On the field to be looked-upAdd a link to execute a javascript function
      &lt;a href=”javascript.lookupAssettype();”&gt;Lookup&lt;/a&gt;
      
    2. Add a javascript to open window to do lookup
      function lookupAssettype(){
      window.open(“assettypeLookup.jsp”,”popup”,”toolbar=no,width=400,status=no,
      menubar=no,scrollbars=yes”);
      }
      
    3. Add on other javascript function to allow jsp in popup window to return value.
      function setLookupAssettype(parm) {
      window.document.forms[0].assettype.value=parm;
      }
      function setSelectKey(parm)
      {
      window.opener.setLookupAssettype(parm);
      window.close();
      
    0

    Day 29 – Reading and Writing Dates

    • get system date and time
      long time = System.current TimeMillis();
      java.sql.Timestamp now = new java.sql.Timestamp(time);
      …
      …
      
    • Insert/Update
      <statement object>.setString(<fieldno>, …)
      setDate(today);
      setTime(now);
      

      Retrieve Date

      String adate = rs.getDate(...).toString(), rs.getTime(...).toString();
      

      Update date

      	ps = conn.preparedStatement();
      	ps.setDate(<fieldno>, Date.valueOf(<string>));
      

      → yyyy-mm-dd

    • If date is null
      if(<string>.trim().equals(“”))
      ps.setNull(<fieldno>,java.sql.Types.DATE);
      else
      ps.setDate(<fieldno>,Date.valueOf(<string>));
      

    Numeric → Double

    • setDouble()
    • getDouble().toString()
      yyyy = Double.toString(xxx);
      xxx=Double.parseDouble(yyy);
      catch → NumberFormatException
    public static double strToDouble(String num){
    double x;
    if(num.trim().equals(“”))
    return 0;
    try{
    x=Double.parseDouble(num);
    return x;
    }
    catch(NumberFormatException e) {
    return 0;
    }
    
    public static String fmtNumber(float num, String fmt){
    DecimalFormat df = new DecimalFormat(fmt);
    return df.format(num);
    }
    
    • Forms store all the Strings.


    Additional Notes

    • Delete

    equal name=(struts-config Form-Bean name(assettypebean))
    formname=AssetBean

      <logic:equal name=”   “ property=”formname” value=”update”>
      <html:submit …....>
      </logic:equal>
      
      <logic:equal name=”assettypeForm” property=”formname” value=”new”>
      <html:text …..>
      </logic:equal>
      
      <logic:notEqual name=” “ property=” “ value=”new”>
      <html:text ….. readonly=”true”>
      </logic:notEqual>