LOG4J

1. Log4j simply inserts a log statement in the application code and manage them externally without going back to its application source code.
2. Programmer's can control the logging message with the help of external configuration file e.g. log4.xml or log4j.properties file.
3. The external properties file can be used to set the log format as well as the level of logging.
4. Three main component of Log4J are :-
        Logger
        Appender
        Layout
5. LOGGER:- It is responsible for capturing the logging information.
   There are few levels of logger:
        DEBUG : Most useful to debug an application.
        INFO  : It provides informational messages.
        WARN  : It provides that application may have harmful events.
        ERROR : It provides that application having error events but that might allow it to continue running.
        FATAL : It denotes the severe error events which lead the application to abort.
        ALL   : It is intended to turn on all logging.
        OFF   : It is intended to turn off all logging.
6. APPENDERS:- It is responsible for publishing the log to a destination. It controls how the logging provides the output.
   There are few list of appenders listed below:
        ConsoleAppender
        DailyRollingFileAppender
        FileAppender
        RollingFileAppender
        WriterAppender
        SMTPAppender
        SocketAppender
        SocketHubAppender
        SyslogAppendersends
        TelnetAppender
7. LAYOUT:- It is responsible for formatting the log output in different layouts. User can control the output format by modifying the Log4J configuration file.
   There are basically three types of Layout:
        HTMLLayout : It formats the output in the HTML table
        PatternLayout : It formats the output in the conversion pattern
        SimpleLayout : It formats the output in a simple manner, it prints the level then place a dash and then the user specified log message.
8. CONFIGURING LOG4J:- Put log4j.properties and log4j.xml in classpath i.e. directly under src folder of the application.
    Log4j looks for log4j.xml file first and then go for log4j.properties hence you must place both the files in your folder. We use log4j.xml since properties file does not provide some advanced configuration options such as Filter, some of ErrorHandlers, and few advanced Appenders.
  

STORED PROCEDURES IN MYSQL

http://www.mysqltutorial.org/mysql-stored-procedure-tutorial.aspx

A stored procedure is a segment of declarative SQL code, which is stored in the database catalog. A stored procedure can be invoked by a program, a trigger or even another stored procedure.

Declarative programming is where you say what you want without having to say how to do it. With procedural programming, you have to specify exact steps to get the result.

ADVANTAGES:-
    Stored procedure increases performance of application. Once created, stored procedure is compiled and stored in the database catalog. It runs faster than uncompiled SQL commands which are sent from application.
    Stored procedure reduces the traffic between application and database server because instead of sending multiple uncompiled lengthy SQL commands statements, the application only has to send the stored procedure's name and get the data back.
    Stored procedure is reusable and transparent to any application which wants to use it. Stored procedure exposes the database interface to all applications so developers don't have to program the functions which are already supported in stored procedure in all external applications.
    Stored procedure is secured. Database administrator can grant the access right to application which wants to access stored procedures in database catalog without granting any permission on the underlying database tables.

SIMPLE STORED PROCEDURE:-
    CREATE PROCEDURE getALL()
    BEGIN
    select * from update_commands;
    END//
    DELIMITER ;

PARAMETERS IN STORED PROCEDURES:-
    In MySQL, a parameter has one of three modes IN, OUT and INOUT.
   
IN :-
    send an input parameter only

    DELIMITER //
    CREATE PROCEDURE getAll(IN commandName varchar(50))
    BEGIN
    select * from update_commands where command_name = commandName;
    END//
    DELIMITER;
   
    call getAll('send_me')
   
OUT:-

    send an input parameter AND get back another output parameter.
   
    DELIMITER //
    CREATE PROCEDURE getAll(IN commandName varchar(50), OUT total int)
    BEGIN
    select count(*) into total from update_commands where command_name like orderStatus ;
    END//
    DELIMITER;

    call getAll(1,@set);
    select @set as total


INOUT:-

    send an input parameter AND get back the same as output parameter after modification.
   
    DELIMITER //
    CREATE PROCEDURE getAll(INOUT a varchar(50))
    BEGIN
    select password into a from login where username = a;
    END//
    DELIMITER ;
   
    set @username = 'a';
    call getAll(@username);
    select @username as passsword
   
DECLARING A VARIABLE:-

    DECLARE variable_name datatype(size) DEFAULT default_value;
    e.g. DECLARE total_sale INT DEFAULT 0

VARIABLE SCOPE:-

    A variable has its own scope. If you declare a variable inside a stored procedure, it will be out of scope when the END of stored procedure reached. If you defined a variable inside block BEGIN/END inside a stored procedure it will be out of scope if the END reached. You can declare two variables or more variables with the same name in different scopes; the variable only is effective in its scope.

    A variable with the ‘@’ at the beginning is session variable. It exists until the session end.
   
CONDITIONAL CONTROL:-

    The IF Statement:-
        IF expression THEN commands
            ELSEIF expression THEN commands
            ELSE commands
        END IF;

    The CASE Statement:-
        CASE case_expression
            WHEN when_expression THEN commands
            WHEN when_expression THEN commands
            ELSE commands
        END CASE;
       
    The WHILE Statement:-
        WHILE expression DO
           Statements
        END WHILE
       
    The REPEAT Statement:-
        REPEAT
            Statements; UNTIL expression
        END REPEAT
   
    The LOOP loop, LEAVE and ITERATE Statment:-