MYSQL - JOIN conjunction

Where you have two and more tables, you can made conjunction between table.

You can use this kind of conjunction:

• INNER JOIN
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL JOIN
• CROSS JOIN

---

Source table

mysql> select* from EMPLOYEE;
+----+--------+---------------+
| ID | NAME   | ID_DEPARTMENT |
+----+--------+---------------+
|  1 | Tom    |             1 | 
|  2 | Petr   |             1 | 
|  3 | Jan    |             2 | 
|  4 | Michal |             2 | 
|  5 | Pepa   |             3 | 
|  6 | Zdenek |             0 | 
+----+--------+---------------+

mysql> select* from DEPARTMENT;
+----+----------+
| ID | TITLE    |
+----+----------+
|  1 | economy  | 
|  2 | it       | 
|  3 | research | 
|  4 | sales    | 
+----+----------+

---

INNER JOIN

Return only corresponding records. You use inner join or join or empty clause.

This selects are the same:

select * from EMPLOYEE inner join DEPARTMENT on EMPLOYEE.ID_DEPARTMENT = DEPARTMENT.ID

select * from EMPLOYEE join DEPARTMENT on EMPLOYEE.ID_DEPARTMENT = DEPARTMENT.ID

select * from EMPLOYEE, DEPARTMENT where EMPLOYEE.ID_DEPARTMENT = DEPARTMENT.ID

+----+--------+---------------+----+----------+
| ID | NAME   | ID_DEPARTMENT | ID | TITLE    |
+----+--------+---------------+----+----------+
|  1 | Tom    |             1 |  1 | economy  | 
|  2 | Petr   |             1 |  1 | economy  | 
|  3 | Jan    |             2 |  2 | it       | 
|  4 | Michal |             2 |  2 | it       | 
|  5 | Pepa   |             3 |  3 | research | 
+----+--------+---------------+----+----------+

---

LEFT OUTER JOIN

Return all records from left table. You can use left outer join or
left join clause only.

mysql> SELECT *                         
    -> FROM EMPLOYEE
    -> LEFT OUTER JOIN DEPARTMENT ON EMPLOYEE.ID_DEPARTMENT = DEPARTMENT.ID;
+----+--------+---------------+------+----------+
| ID | NAME   | ID_DEPARTMENT | ID   | TITLE    |
+----+--------+---------------+------+----------+
|  1 | Tom    |             1 |    1 | economy  | 
|  2 | Petr   |             1 |    1 | economy  | 
|  3 | Jan    |             2 |    2 | it       | 
|  4 | Michal |             2 |    2 | it       | 
|  5 | Pepa   |             3 |    3 | research | 
|  6 | Zdenek |             0 | NULL | NULL     | 
+----+--------+---------------+------+----------+

---

RIGHT OUTER JOIN

Return all records from right table. You can use right outer join or
right join clause only.

mysql> SELECT *
    -> FROM EMPLOYEE
    -> RIGHT OUTER JOIN DEPARTMENT ON EMPLOYEE.ID_DEPARTMENT = DEPARTMENT.ID;
+------+--------+---------------+----+----------+
| ID   | NAME   | ID_DEPARTMENT | ID | TITLE    |
+------+--------+---------------+----+----------+
|    1 | Tom    |             1 |  1 | economy  | 
|    2 | Petr   |             1 |  1 | economy  | 
|    3 | Jan    |             2 |  2 | it       | 
|    4 | Michal |             2 |  2 | it       | 
|    5 | Pepa   |             3 |  3 | research | 
| NULL | NULL   |          NULL |  4 | sales    | 
+------+--------+---------------+----+----------+

CROSS JOIN

Create cartesian product, You can use cross join or empty.

This selects are the same:

SELECT * FROM EMPLOYEE CROSS JOIN DEPARTMENT

SELECT * FROM EMPLOYEE, DEPARTMENT

mysql> SELECT *
    -> FROM EMPLOYEE
    -> CROSS JOIN DEPARTMENT;
+----+--------+---------------+----+----------+
| ID | NAME   | ID_DEPARTMENT | ID | TITLE    |
+----+--------+---------------+----+----------+
|  1 | Tom    |             1 |  1 | economy  | 
|  1 | Tom    |             1 |  2 | it       | 
|  1 | Tom    |             1 |  3 | research | 
|  1 | Tom    |             1 |  4 | sales    | 
|  2 | Petr   |             1 |  1 | economy  | 
|  2 | Petr   |             1 |  2 | it       | 
|  2 | Petr   |             1 |  3 | research | 
|  2 | Petr   |             1 |  4 | sales    | 
|  3 | Jan    |             2 |  1 | economy  | 
|  3 | Jan    |             2 |  2 | it       | 
|  3 | Jan    |             2 |  3 | research | 
|  3 | Jan    |             2 |  4 | sales    | 
|  4 | Michal |             2 |  1 | economy  | 
|  4 | Michal |             2 |  2 | it       | 
|  4 | Michal |             2 |  3 | research | 
|  4 | Michal |             2 |  4 | sales    | 
|  5 | Pepa   |             3 |  1 | economy  | 
|  5 | Pepa   |             3 |  2 | it       | 
|  5 | Pepa   |             3 |  3 | research | 
|  5 | Pepa   |             3 |  4 | sales    | 
|  6 | Zdenek |             0 |  1 | economy  | 
|  6 | Zdenek |             0 |  2 | it       | 
|  6 | Zdenek |             0 |  3 | research | 
|  6 | Zdenek |             0 |  4 | sales    | 
+----+--------+---------------+----+----------+

---

FULL JOIN

You need to use two JOINs and UNION. More example in article How to simulate FULL OUTER JOIN in MySQL

mysql> SELECT *
    -> FROM EMPLOYEE
    -> LEFT OUTER JOIN DEPARTMENT ON EMPLOYEE.ID_DEPARTMENT = DEPARTMENT.ID
    -> UNION
    -> SELECT *
    -> FROM EMPLOYEE
    -> RIGHT OUTER JOIN DEPARTMENT ON EMPLOYEE.ID_DEPARTMENT = DEPARTMENT.ID;
+------+--------+---------------+------+----------+
| ID   | NAME   | ID_DEPARTMENT | ID   | TITLE    |
+------+--------+---------------+------+----------+
|    1 | Tom    |             1 |    1 | economy  | 
|    2 | Petr   |             1 |    1 | economy  | 
|    3 | Jan    |             2 |    2 | it       | 
|    4 | Michal |             2 |    2 | it       | 
|    5 | Pepa   |             3 |    3 | research | 
|    6 | Zdenek |             0 | NULL | NULL     | 
| NULL | NULL   |          NULL |    4 | sales    | 
+------+--------+---------------+------+----------+

COALESCE

In most of DB engine you can find COALESCE function. It is helpful where you need to work with NULL values.

source table

mysql> select * from INFO;
+----+-------------+--------------+
| ID | GALLERY     | PHOTOGRAPHER |
+----+-------------+--------------+
|  1 | Cars        | Tom          | 
|  2 | Countryside | Petr         | 
|  3 | City        | Jan          | 
|  4 | Sport       | NULL         | 
+----+-------------+--------------+

When you need to return not NULL report, you can use COALESCE

COALESCE function

mysql> select GALLERY, coalesce(PHOTOGRAPHER,"unknown") as PHOTOGRAPHER from coalesce;
+-------------+--------------+
| GALLERY     | PHOTOGRAPHER |
+-------------+--------------+
| Cars        | Tom          | 
| Countryside | Petr         | 
| City        | Jan          | 
| Sport       | unknown      | 
+-------------+--------------+

Enumeration in Java5

package enumjava5;

public enum Color {
    RED(255, 0, 0){
        public String getMood(){
            return "live";
        }
    },
    GREEN(0, 255, 0){
        public String getMood(){
            return "rest";
        }  
    
    },
    BLUE(0, 0, 255){
        public String getMood(){
            return "sleep";
        }
    };

    Integer red;
    Integer green;
    Integer blue;

    private Color(Integer red, Integer green, Integer blue){
        this.red = red;
        this.green = green;
        this.blue = blue;
    }

    public String getCode(){
        return red + ", " + green + ", " + blue;
    }

    public abstract String getMood();

    @Override
    public String toString(){
        return "Color #" + this.ordinal() +  ", " +  this.name();
    }
}

• Use keyword enum - public enum Color
• Constuctor is private.
• You can implements yours method: String getCode()
• You can implements yours abstract method: abstract String getMood()
• You can use heritage method: String name(), int ordinal(), <T>[] values(), <T> valueOf(String)

package enumjava5;

public class Main {

   public static void main(String[] args) {

       for (Color color : Color.values()) {

            System.out.println("name: " + color.name() + ", code: " + color.getCode() + ", mood: " + color.getMood() + ", asString: " + color.toString());
       }

    }
}

output:

name: RED, code: 255, 0, 0, mood: live, asString: Color #0, RED
name: GREEN, code: 0, 255, 0, mood: rest, asString: Color #1, GREEN
name: BLUE, code: 0, 0, 255, mood: sleep, asString: Color #2, BLUE

You can use inner enum:

package enumjava5;

public class Outter{
    
  public enum Color{
      RED, GREEN, BLUE;
  }

  public static void main(String[] args){
      for (Color m: Color.values()){
          System.out.println( m.name() );
      }
  }
}

IPv6

IPv6 is not compatible with IPv4.

Address is assign to interface.

Interface has more addresses: e.g. loopback, multicast, local link, global, ...

The length of header is constant. It can use extends header. Extend header have specified order.

Don´t use checksum.

Kinds of address:

unicast

for interface

multicast

for group of computer

anycast

for first computer in group

Don´t use broadcast. In place of broadcast it use multicast.

---

Notation of IPv6 address

IPv4

147.230.49.73

length 32b

4 x 8b

decimal

IPv6

fedc:ba98:7654:3210:fedc:ba98:7654:3210

length 128b

8 x 16b

hexadecimal

---

Shortening IPv6 address

• 0123:0000:0000:0000:fedc:ba98:7654:3210
• 123:0:0:0:fedc:ba98:7654:3210
• 123::fedc:ba98:7654:3210 (allow only one "::" in address)

---

Mapping IPv4 address to IPv6

147.230.49.73

::ffff:93e6:3149 (147=>93, 230=>e6, 49=>31, 73=>49)

::ffff:147.230.49.73

---

Range of IPv6

• ::1/128 loopback
• 2000::/3 Global Unicast
• FC00::/7 Unique Local Unicast
• FE80::/10 Link Local Unicast
• FF00::/8 Multicast

---

Global Unicast

|_ _ _|_|_ _ _ _|

48b - Public NET topology (from provider)

16b - Local SUBNET topology (your net)

64b - Interfaces (your interfaces)

---

Automatic IPv6 configuration

1. Create local link address from MAC address. (fe80::/10 + MAC)
2. Check collision.
3. Waiting for router info about networks or ask about router.
4. Set network parameters.

---

Security

Ipv6 mandatory use IPsec. It offers authentication, encryption, tunnelling.

Spring AOP with annotation

Aspect

Combination of advice and pointcuts.

Advice

The implementation of functionality that will be applied.

Pointcut

A rule for matching the parts of the object model that the functionality will be applied to.

---

Enable annotation

<aop:aspectj-autoproxy />

---

Aspect

package org.xyz;
import org.aspectj.lang.annotation.Aspect;

@Aspect
public class NotVeryUsefulAspect {

}

---

Pointcut

@Pointcut("execution(* transfer(..))")// the pointcut expression
private void anyOldTransfer() {}// the pointcut signature

Combining pointcut expressions

@Pointcut("execution(public * *(..))")
private void anyPublicOperation() {}
    
@Pointcut("within(com.xyz.someapp.trading..*)")
private void inTrading() {}
    
@Pointcut("anyPublicOperation() && inTrading()")
private void tradingOperation() {}

@Aspect
public class BeforeAspect {
                                                            
  @Pointcut("execution(* com.apress.prospring2.ch06.services.*.*(..))")
  private void serviceExecution() { }
  @Pointcut(
      "execution(* com.apress.prospring2.ch06.services.UserService.login(..))")
  private void loginExecution() { }
  @Before("serviceExecution() && !loginExecution()")
  public void beforeLogin() throws Throwable {
      if (SecurityContext.getCurrentUser() == null)
          throw new RuntimeException("Must login to call this method.");
  }
}

Sharing common pointcut definitions

package com.xyz.someapp;

import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;

@Aspect
public class SystemArchitecture {

  /**
   * A join point is in the web layer if the method is defined
   * in a type in the com.xyz.someapp.web package or any sub-package
   * under that.
   */
  @Pointcut("within(com.xyz.someapp.web..*)")
  public void inWebLayer() {}

  /**
   * A join point is in the service layer if the method is defined
   * in a type in the com.xyz.someapp.service package or any sub-package
   * under that.
   */
  @Pointcut("within(com.xyz.someapp.service..*)")
  public void inServiceLayer() {}

  /**
   * A join point is in the data access layer if the method is defined
   * in a type in the com.xyz.someapp.dao package or any sub-package
   * under that.
   */
  @Pointcut("within(com.xyz.someapp.dao..*)")
  public void inDataAccessLayer() {}

  /**
   * A business service is the execution of any method defined on a service
   * interface. This definition assumes that interfaces are placed in the
   * "service" package, and that implementation types are in sub-packages.
   * 
   * If you group service interfaces by functional area (for example, 
   * in packages com.xyz.someapp.abc.service and com.xyz.def.service) then
   * the pointcut expression "execution(* com.xyz.someapp..service.*.*(..))"
   * could be used instead.
   *
   * Alternatively, you can write the expression using the 'bean'
   * PCD, like so "bean(*Service)". (This assumes that you have
   * named your Spring service beans in a consistent fashion.)
   */
  @Pointcut("execution(* com.xyz.someapp.service.*.*(..))")
  public void businessService() {}
  
  /**
   * A data access operation is the execution of any method defined on a 
   * dao interface. This definition assumes that interfaces are placed in the
   * "dao" package, and that implementation types are in sub-packages.
   */
  @Pointcut("execution(* com.xyz.someapp.dao.*.*(..))")
  public void dataAccessOperation() {}

}

Examples of common pointcut expressions

execution(public * *(..))

the execution of any public method

execution(* set*(..))

the execution of any method with a name beginning with "set"

execution(* com.xyz.service.AccountService.*(..))

the execution of any method defined by the AccountService interface

execution(* com.xyz.service.*.*(..))

he execution of any method defined in the service package

execution(* com.xyz.service..*.*(..))

the execution of any method defined in the service package or a sub-package

within(com.xyz.service.*)

any join point (method execution only in Spring AOP) within the service package

within(com.xyz.service.*)

any join point (method execution only in Spring AOP) within the service package

within(com.xyz.service..*)

any join point (method execution only in Spring AOP) within the service package or a sub-package

this(com.xyz.service.AccountService)

any join point (method execution only in Spring AOP) where the proxy implements the AccountService interface

target(com.xyz.service.AccountService)

any join point (method execution only in Spring AOP) where the target object implements the AccountService interface

args(java.io.Serializable)

any join point (method execution only in Spring AOP) which takes a single parameter, and where the argument passed at runtime is Serializable

@target(org.springframework.transaction.annotation.Transactional)

any join point (method execution only in Spring AOP) where the target object has an @Transactional annotation

@annotation(org.springframework.transaction.annotation.Transactional)

any join point (method execution only in Spring AOP) where the executing method has an @Transactional annotation

@args(com.xyz.security.Classified)

any join point (method execution only in Spring AOP) which takes a single parameter, and where the runtime type of the argument passed has the @Classified annotation

bean(tradeService

any join point (method execution only in Spring AOP) on a Spring bean named 'tradeService'

bean(*Service)

any join point (method execution only in Spring AOP) on Spring beans having names that match the wildcard expression '*Service'

---

Advice

As parameter of advice method you can use org.aspectj.lang.JoinPoint. Class which implements this interface have method: getTarget(), getArgs(), getSignature(), getThis(). You can use this method in advice. For example see @After advice.

Advice with reference to pointcut

import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;

@Aspect
public class BeforeExample {

  @Before("com.xyz.myapp.SystemArchitecture.dataAccessOperation()")
  public void doAccessCheck() {
    // ...
  }

}

Advice with in-place pointcut

import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;

@Aspect
public class BeforeExample {

  @Before("execution(* com.xyz.myapp.dao.*.*(..))")
  public void doAccessCheck() {
    // ...
  }

}

After returning advice

import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.AfterReturning;

@Aspect
public class AfterReturningExample {

  @AfterReturning(
    pointcut="com.xyz.myapp.SystemArchitecture.dataAccessOperation()",
    returning="retVal")
  public void doAccessCheck(Object retVal) {
    // ...
  }
  
}

After throwing advice

import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.AfterThrowing;

@Aspect
public class AfterThrowingExample {

  @AfterThrowing(
    pointcut="com.xyz.myapp.SystemArchitecture.dataAccessOperation()",
    throwing="ex")
  public void doRecoveryActions(DataAccessException ex) {
    // ...
  }

}

After (finally) advice

import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.After;

@Aspect
public class AfterFinallyExample {

  @After("com.xyz.myapp.SystemArchitecture.dataAccessOperation()")
  public void doReleaseLock( JoinPoint jp ) {
    // ...
    Object[] targetMethodArgs = jp.getArgs();
    //...
  }

}

Around advice

import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.ProceedingJoinPoint;

@Aspect
public class AroundExample {

  @Around("com.xyz.myapp.SystemArchitecture.businessService()")
  public Object doBasicProfiling(ProceedingJoinPoint pjp) throws Throwable {
    // start stopwatch
    Object retVal = pjp.proceed();
    // stop stopwatch
    return retVal;
  }

}

Passing parameters to advice

@Before("com.xyz.myapp.SystemArchitecture.dataAccessOperation() &&" + 
        "args(account,..)")
public void validateAccount(Account account) {
  // ...
}

@Pointcut("com.xyz.myapp.SystemArchitecture.dataAccessOperation() &&" + 
          "args(account,..)")
private void accountDataAccessOperation(Account account) {}

@Before("accountDataAccessOperation(account)")
public void validateAccount(Account account) {
  // ...
}

@Before(
   value="com.xyz.lib.Pointcuts.anyPublicMethod() && target(bean) && @annotation(auditable)",
   argNames="bean,auditable")
public void audit(JoinPoint jp, Object bean, Auditable auditable) {
  AuditCode code = auditable.value();
  // ... use code, bean, and jp
}

---

Exception handling in Spring MVC

SimpleMappingExceptionResolver

---

Specific Exception handler

Class SimpleleMappingExceptionResolver implements interface HandlerExceptionResolver. You can implement own specific class or extends class SimpleleMappingExceptionResolver.

---

MailErrorHandlerException

If occure some exception it send mail.

public class ErrorHandlerController implements HandlerExceptionResolver{

    public static final String DEFAULT_EXCEPTION_ATTRIBUTE = "exception";
    public static final String DEFAULT_ERROR_WIEW = "error";
   
    private MailManager mailManager;
    private String from;
    private String to;
    private String subject;
    private String defaultErrorView = DEFAULT_ERROR_WIEW;
    private String exceptionAttribute = DEFAULT_EXCEPTION_ATTRIBUTE;

    @Override
    public ModelAndView resolveException(HttpServletRequest request, HttpServletResponse response, Object handler, Exception ex) {

        if (mailManager != null && from != null && to != null && subject != null){
            sendMailToWebmaster(ex);
        }

        return getModelAndView(defaultErrorView, ex);
    }

    private void sendMailToWebmaster(Exception ex) {
        mailManager.setFrom(  from );
        mailManager.setTo( to );
        mailManager.setSubject( subject );
        mailManager.setText( stack2string( ex ));
        mailManager.sendMail();
    }

    protected ModelAndView getModelAndView(String viewName, Exception ex) {
         ModelAndView mv = new ModelAndView(viewName);       
         mv.addObject(this.exceptionAttribute, ex);      
         return mv;
    }

    public static String stack2string(Exception e) {
            StringWriter sw = new StringWriter();
            PrintWriter pw = new PrintWriter(sw);
            e.printStackTrace(pw);
            return "------\r\n" + sw.toString() + "------\r\n";
    }
    
    public void setDefaultErrorView(String defaultErrorView) {
        this.defaultErrorView = defaultErrorView;
    }

    public void setExceptionAttribute(String exceptionAttribute) {
        this.exceptionAttribute = exceptionAttribute;
    }

    public void setMailManager(MailManager mailManager) {
        this.mailManager = mailManager;
    }

    public void setFrom(String from) {
        this.from = from;
    }

    public void setSubject(String subject) {
        this.subject = subject;
    }

    public void setTo(String to) {
        this.to = to;
    }
}

Exception handling

Exception - special conditions that change the normal flow of program execution.

---

Exception hierarchy

Your exception extend Exception.

---

Type of exception

• Checked exception
• Unchecked exception

---

Standart checked exception

• IllegalArgumentException
• IllegalStateException
• NullPointerException
• IndexOutOfBoundsException
• ConcurentModificationException
• UnsupportedOperationException
• AritmeticException
• NumberFormatException

---

Exception handle

try{
    //protected block
}catch(Datovy typ výjimky){
    //exception handling
}finally{
    //this code it execute every time
} 

Block finally it will execute always.

---

Exception chaining

public void configure() throws ConfigurationException{
    try{
        ...
        ...
    }catch(FileNotFoundException e){
        throw new ConfigurationException("Config file not exists.",e);
    }catch(PropertyMissingException e){
        throw new ConfigurationException("Missing argument.",e);
    }catch(InvalidFormatException e){
        throw new ConfigurationException("Unknown argument",e);
    }
} 

---

Exception mesage

public IndexOutOfBoundException( int lowerBound, int upperBound, int index){      
    super ("Lower bound:" +  lowerBound +
                ", Upper bound: " + upperBound +
                ", Index: " + index);              
    )
}

---