SPRING Roo commands

// open eclipse with spring roo installation
// springsource -> sts-2.9.2.RELEASE -> STS.sh
// script --file /home/meena/Desktop/meena/my_spring_roo/spring_roo_commands.roo


// setting up database provider
jpa setup --provider HIBERNATE --database HYPERSONIC_IN_MEMORY

jpa setup --provider HIBERNATE --database MYSQL --databaseName myDb--hostName 123.1.1.9 --userName user1--password pass@123


// creating class in roo proj
entity jpa --class ~.MyClass

entity jpa --class ~.master.domain.Lov --identifierColumn LOV_PK --identifierField LovPk --table LM_LOV

// declare variables in class
field string --fieldName message --notNull
field boolean --fieldName ActiveYN --column LOV_ACTIVE_YN

// creates .war file of proj
web mvc setup

// building all source files in mypkg
web mvc all --package mypkg


// create class with roo annotations to include toString() automatically for all variables
class --class in.mydomain.Employee --rooAnnotations
field number --fieldName empID --type int --notNull --unique
field string --fieldName empName --notNull

// to give custom name to toString method of a class
// @RooToString(toStringMethod="myTostring")

// to prevent a field from displaying through toString()
// @RooToString(excludeFields={"empName"})

class --class ~.domain.Address --rooAnnotations
field string --fieldName streetName --notNull
field string --fieldName city --notNull
field string --fieldName country --notNull

// to modify instead of recreating or overriding the class definition
focus --class ~.Employee
field other --fieldName empAdd --type in.traccion.Address --notNull
field date --fieldName dateOfBirth --type java.util.Date

// To remove or modify field in class, make changes in IDE

// to create interface
interface --class in.mydomain.MyInterface

// To set up Hibernate as the JPA provider for your application
persistence setup --provider HIBERNATE --database MYSQL --databaseName roodb --username=root password=root

class --class in.traccion.ParentClass1
class --class in.traccion.ChildClass1 --extends ParentClass1

focus --class ~.ParentClass1
field number --fieldName parentid --type int --min 0 --max 1000 --unique
field string --fieldName parentName

focus --class ~.ChildClass1

// roo> database properties list
// database.driverClassName = com.mysql.jdbc.Driver
// database.password = root
// database.url = jdbc:mysql://localhost:3306/roodb
// database.username = root

// to set username of a db
database properties set --key database.username --value root
database properties set --key database.password --value root
database properties set --key database.initialPoolSize --value 10
database properties remove --key database.url
database properties set --key database.modified.url --value jdbc:mysql://localhost:3406/roodb

// Right-click on project in project explorer & select Run as Server

Linux commands

ls -> to list files & directories in a directory
ls -l -> to list files/folders in a directory with permission sets

mkdir <directory_name> -> creates a directory
rmdir <dir> -> removes directory

chmod -R 777 * -> gives all permissions to all files & files within subfolders in a directory

vi <file_name> -> editor to open, edit files in terminal
exit -> to close terminal
rm <file_name> -> removes a file
rm -rf <folder_name> to remove directories that are not empty

mv <file_name> <destination_directory> ->  moves a file from a folder to another
mv <directory> <destination_directory> ->  moves a folder from a folder to another
mv <file1> <file2> <destination_directory> -> moves two files in a folder siultaneously

cd <path> -> changes directory path to a particular location
cd .. -> changes directory path to one folder above

cat <file_name> -> show contents of file

Ctrl + L -> to clear terminal screen

ls -u <user_name> -> lists all processes started by a particular user
kill -9 <pid> -> stops the process with a particular pid

./<app> -> to execute an application

locate <folder_name> -> To find a folder in entire PC

MySQL concatenation

If you want to generate multiple statements for all the tables in MySQL database to insert data imported by Oracle, you can do so by using MySQL concat() with the required information from information_schema database:-

use information_schema;
SHOW TABLES;
desc columns
DESC TABLES;

Say while importing data from .csv files containing data from oracle has some blank values which has to be entered as null value in MySQL database tables, then you have got to check which columns are nullable & accordingly declare set command for those columns for each table in following manner.

mysql> LOAD DATA LOCAL INFILE '/home/../mytbldata.csv' INTO TABLE <db_name>.<tbl_name>
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n' IGNORE 1 LINES
(<col1_name>,<@var1>, <col3_name>,<@var2>)
SET col2_name = IF(@var1=0, NULL, @var1),
col3_name = IF(@var2=0, NULL, @var2)

1. You can check the nullable columns for a particular table by using foll command:-

select column_name, is_nullable from information_Schema.columns
where is_nullable = 'yes' and table_Schema='<db_name>' and table_name='<table_name>';


2.  Generating LOAD Statements without SET for all tables of a database:-
select concat
('load data local infile \'/home/meena/Desktop/meena/', UPPER(table_name), '_DATA.csv\'
into table <db_name>.', table_name,
' fields terminated by \',\' enclosed by \'"\'
lines terminated by \'\\n\' ignore 1 lines;'  )
AS Load_statement
from information_schema.TABLES
where table_schema='<db_name>' ;

3. Now, the Load statements for all tables in database with set for nullable columns in each table.

select concat
("load data local infile
'/home/meena/Desktop/meena/", upper(table_name),
"_DATA.csv' into table ",table_name, "fields terminated by ','
enclosed by '""' lines terminated by '\\n' ignore 1 lines (")
as my_stmt
from information_schema.tables
where table_name = '<table_name>' and table_schema = '<db_name>'
union select concat(if(is_nullable='YES', '@', ''),column_name)
from columns
where table_name = '<table_name>' and table_schema = '<db_name>' and ordinal_position = 1
union
select concat(',',if(is_nullable='YES', '@', ''),column_name)
from columns
where table_name = '<table_name>' and table_schema = '<db_name>' and ordinal_position > 1
union
select ') set '
from dual
union
select concat(column_name, '= if(@', column_name, '=0, null, @', column_name, ')')
from columns
where table_name = '<table_name>' and table_schema = '<db_name>' and is_nullable = 'YES';

#----------------------------------------------------------------------------------------------------------------
We can use group_concat() function also to generate load data statement with set condition for all tables in a database.


select 
concat("load data local infile '/home/meena/Desktop/meena/dummy_file_format/", UPPER(c.table_name)
, "_DATA.csv.txt' into table ", c.table_name, " fields terminated by ','
enclosed by '\"' lines terminated by '\\n' ignore 1 lines ("
      ) as s1, 

group_concat( if(is_nullable="YES", "@", ""),column_name) as s2, 

concat(") set ") as s3,

group_concat(if(is_nullable="YES", 
           concat(column_name,"= if(@",column_name, "=0, null, @", column_name, ") \n") ,
            ''
         )
       ) as s4

from information_Schema.columns c 
where c.table_schema='dummy_db' group by c.table_name;

Master to Master Replication in MySQL server

 *********** MASTER SERVER 1 SETTINGS ************

1. Login to master server 1 machine.
 Open terminal. Login as super user.

2.  Edit mysql configuration file (my.cnf) using vi editor. It is in /etc directory. Set server-id = 1. Basically it should be a unique number in both master servers.

3.  root> service mysqld start;

4.  root> mysql -u<username> -p<password>

5.  mysql> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@ 'ip_addr' IDENTIFIED BY ‘slavepass’;
eg. grant replication slave on *.* to 'm1'@  '%' identified by 'p1';

6.  mysql> show master status\G;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
Note the bin log file name & position. This will be used in master 2 settings.

7.   root> CHANGE MASTER TO MASTER_HOST='IP_ADDR', MASTER_USER='<repl_user>', MASTER_PASSWORD='<password>', MASTER_LOG_FILE='<log_file_name>', MASTER_LOG_POS=<position>;
eg: change master to master_host='129.1.1.19', master_user = 'm2', master_password='p2', master_log_file='mysql-bin.000004', master_log_pos=107;

8.  mysql> SHOW SLAVE STATUS\G;
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 129.1.1.19
                  Master_User: m2
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 200
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 200
              Relay_Log_Space: 410
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 2
1 row in set (0.00 sec)

9. In above step, the value of foll should be as it is shown here for replication to work :-
 Slave_IO_State: Waiting for master to send event
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes

10. The value of foll should be of another master server:-
            Master_Host: 129.1.1.19   (IP of master 2)
            Master_User: m2     (repl user name of master 2)
            Master_Port: 3306    (port no of master 2)
            Master_Log_File: mysql-bin.000006 (log file of master 2)
            Read_Master_Log_Pos: 200   (log file position of master 2)

 *********** MASTER SERVER 2 SETTINGS ************

1. Login to master server 2 machine.
 Open terminal. Login as super user.

2.  Edit mysql configuration file (my.cnf) using vi editor. It is in /etc directory. Set server-id = 2. Basically it should be a unique number in both master servers.

3.  root> service mysqld start;

4.  root> mysql -u<username> -p<password>

5.  mysql> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@ 'ip_addr' IDENTIFIED BY ‘slavepass’;
eg. grant replication slave on *.* to 'm2'@ '%' identified by 'p2';

6.  mysql> show master status\G;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 |      200 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
Note the bin log file name & position. This will be used in master 1 settings.

7.   root> CHANGE MASTER TO MASTER_HOST='IP_ADDR', MASTER_USER='<repl_user>', MASTER_PASSWORD='<password>', MASTER_LOG_FILE='<log_file_name>', MASTER_LOG_POS=<position>;
eg: change master to master_host='129.1.1.42', master_user = 'm1', master_password='p1', master_log_file='mysql-bin.000001', master_log_pos=107;

8.  mysql> SHOW SLAVE STATUS\G;
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 129.1.1.42
                  Master_User: m1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 107
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 200
              Relay_Log_Space: 410
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)

10. In above step, the value of foll should be as it is shown here for replication to work :-
Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

10. The value of foll should be of another master server:-
 Master_Host: 129.1.1.42   (IP of master 1)
 Master_User: m1     (repl user name of master 1)
 Master_Port: 3306    (port no of master 1)
 Master_Log_File: mysql-bin.000001 (log file of master 1)
 Read_Master_Log_Pos: 107   (log file position of master 1)

Creating .jar file

Create the necessary .java program files in a directory.

Employee.java

class Employee
{
 public int EmpID;
 public String EmpName;
 public Employee(int id, String name)
 {
  EmpID = id;
  EmpName = name;
 }
 public String toString()
 {
  return " Employee ID: "+EmpID + "\n Employee Name: "+EmpName;
 }
}

MyClass.java

import java.util.Scanner;
public class MyClass
{
 public static void main(String[] args)
 {
  Scanner sc=new Scanner(System.in);
  System.out.println("\n Enter Emp ID: ");
  int id = sc.nextInt();
  System.out.println("\n Enter Emp Name: ");
  String name = sc.next();
  Employee e = new Employee(id, name);
  System.out.println(e);
 }
}

Compile them using command prompt:-

javac *.java

The .class files are created.

Create manifest file to store the name of class containing main(). The line should end with new line. It can also contain version information.

manifest.txt

Main-Class: MyClass

To create jar file:-

jar cvfm MyJarFile.jar manifest.txt *.class

Jar file is created in same folder.

D:\CORE_JAVA\myjardemo>jar cvfm MyJarFile.jar manifest.txt *.class
added manifest
adding: Employee.class(in = 618) (out= 369)(deflated 40%)
adding: MyClass.class(in = 768) (out= 470)(deflated 38%)


D:\CORE_JAVA\myjardemo>dir
 Volume in drive D is Data
 Volume Serial Number is 1E89-AEA1
 Directory of D:\CORE_JAVA\myjardemo
08/12/2012  02:45 PM    <DIR>          .
08/12/2012  02:45 PM    <DIR>          ..
08/12/2012  02:45 PM               618 Employee.class
08/12/2012  02:24 PM               251 Employee.java
08/12/2012  02:22 PM                21 manifest.txt
08/12/2012  02:45 PM               768 MyClass.class
08/12/2012  02:27 PM               358 MyClass.java
08/12/2012  02:45 PM             1,439 MyJarFile.jar
               6 File(s)          3,455 bytes
               2 Dir(s)  130,187,784,192 bytes free

D:\CORE_JAVA\myjardemo>java -jar MyClass.jar

 Enter Emp ID:
420
 Enter Emp Name:
Gabbar
 Employee ID: 420
 Employee Name: Gabbar
 

IMPORTING DATA FROM MULTIPLE .DSV FILES TO MULTIPLE TABLES IN MySQL db

COUNT OF FILES IN A DIRECTORY:-

root> # ls -l |grep '^-' | wc -l

--------------------------------------------------------------------
TRANSFERING ALL FILENAMES IN A DIRECTORY TO A FILE:-

root> # ls -1 > /home/meena/Desktop/meena/DSV_FILENAMES.txt
--------------------------------------------------------------------

AWK COMMAND TO CREATE STATEMENTS LIKE BELOW FOR EACH TABLE:-

LOAD DATA LOCAL INFILE /home/meena/Desktop/meena/TABLE1.dsv
INTO TABLE TABLE1
FIELDS ENCLOSED BY '"' SEPERATED BY '\t'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

root> #
awk -F: -v  sq="'" -F: -v dq="\"" -F: -v t="\\t" -F: -v n="\\n"
'BEGIN{FS=" ";RS="\n";ORS=";\n\n"}
{print "LOAD DATA LOCAL INFILE " sq "/home/meena/Desktop/meena/" $1 sq " INTO TABLE " $1 " FIELDS ENCLOSED BY " sq dq sq " TERMINATED BY " sq t sq " LINES TERMINATED BY " sq n sq " IGNORE 1 LINES"}'
 /home/meena/Desktop/meena/DSV_FILENAMES.txt
  > /home/meena/Desktop/meena/DSV_IMPORT_STMT.sql

REPLACE ALL
_DATA.dsv FIELDS
WITH
 FIELDS

root> # mysql -uroot -proot  set_foreign_key_checks=0;
root> # mysql -uroot -proot  db_name > /home/meena/Desktop/meena/DSV_IMPORT_STMT.sql
root> # mysql -uroot -proot  set_foreign_key_checks=1;