Add a column in Mysql FIRST Or AFTER

MySQL tables are easy to extend with additional columns.

Add the email column to the end of the table. To insert the new column after a specific column, such as name, use this statement:

ALTER TABLE contacts ADD email VARCHAR(60) AFTER name;

If you want the new column to be first, use this statement:

ALTER TABLE contacts ADD email VARCHAR(60) FIRST;

Coding guidelines for Method creation in Java

• The scope of the method should be very clear (public / private / protected based on the requirement). This will be easy to identify if any changes in future for assessment. 
• The method name and variable name should completely be relevant. Names representing methods must be verbs and written in mixed case starting with lower case (ex : getName, computeTotal)
• Method should not exceed 50 lines. If so, create a private method and have the logic there.
• Every method should have JavaDoc (comments) clearly with Javadoc conventions (ctrl + shift + j in eclipse will add comments. You need add some description about methods)
• Try..Catch (Catch exception should throw to the class to caller to capture the error and provide proper error message in the UI. Catch exception should not printStackTrace.
• Don't hardcode static number / strings. Use constants. Names representing constants (final variables) must be all uppercase using underscore to separate words. (ex : THIRD_PARTY_SOURCE => 'DFP')
• Names representing methods must be verbs and written in mixed case starting with lower case
• The name of the object is implicit, and should be avoided in a method name. (line.getLength(); // NOT: line.getLineLength();)
• is prefix should be used for boolean variables and methods (isFound, isDeleted, isSet)
•  The term find can be used in methods where something is looked up

Basic things to learn for Relational DB Table Creation

•  Table name should be relevant. Not necessary to add project name with table name
• First column should be integer, not null, primary key and auto increment. Have (TABLENAME_ID). It will help for the foreign key reference. Rather just saying "ID".
• When you have NOT NULL try to have Default Value (ex : double(10,2) DEFAULT 0.00)
• If the column is foreign key you should have a constraint and the table should be created as InnoDB Engine
• Try to have indexing if you know the table size is going to be huge
• Try to have constraint in db level based on the project requirement to avoid unwanted corrupted data (unique key to avoid duplicate, composite key....)
• Have last_modified and created_on with timestamp data type column (NOT NULL) always for auditing and support. Mysql will automatically make last_modified column with Default 'Current_TimeStamp' and Extra 'on update CURRENT_TIMESTAMP'
• Never have definer on table, view, stored procs, trigger creation. When other user take dump and restore they may face issues.
• Foreign key (FK) name cannot have the same name in a single database. 

mysqldump Couldn’t execute references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

3 cases

• The view is “corrupted” (this could happen if the table the view is of has been updated in a way so that the view is no longer correct – for instance if the table structure is updated)
• The user you are using are lacking permissions (to the view/table (that the view is referencing) ) (Refer : http://gubendran.blogspot.com/2012/02/create-user-in-mysql-and-provide.html for user creation and permission)
• Somecases view must have created based on the user definer. In this case drop the view and create the view without having definer. This is safe. Always avoid using definer on table, view, trigger and procs creation.

Create a user in mysql and provide complete permission

1. Login as root in mysql
2. create user `username`
Or Create user with password

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password123'

Set password for the mysql user using grant

GRANT USAGE ON *.* TO 'bob'@'%.loc.gov' IDENTIFIED BY 'newpass';

or

SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass');

Give full permission to the user

GRANT ALL ON *.* TO 'user'@'localhost' IDENTIFIED BY 'pwd'

If you use grant you need flush the privileges

FLUSH PRIVILEGES;

Run below command from root. If other users need to connect to server.
 GRANT ALL ON *.* TO ''@'%' IDENTIFIED BY '';
FLUSH PRIVILEGES;

REVOKE INSERT, DELETE, UPDATE ON db_name.table_name FROM 'user'

In case of AWS RDS to provide permission for any machine to connect, create user and provide grant permission

CREATE USER 'springbot'@'%' IDENTIFIED BY 'SpringBot';

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'springbot'@'%' WITH GRANT OPTION;

conversion of datatype from MySQL to JAVA

MySQL Types to Java Types for ResultSet.getObject(). 

MySQL Type Name	Return value ofGetColumnClassName	Returned as Java Class
BIT(1) (new in MySQL-5.0)	BIT	java.lang.Boolean
BIT( > 1) (new in MySQL-5.0)	BIT	byte[]
TINYINT	TINYINT	java.lang.Boolean if the configuration property tinyInt1isBit is set to true (the default) and the storage size is 1, or java.lang.Integer if not.
BOOL, BOOLEAN	TINYINT	See TINYINT, above as these are aliases for TINYINT(1), currently.
SMALLINT[(M)] [UNSIGNED]	SMALLINT [UNSIGNED]	java.lang.Integer (regardless if UNSIGNED or not)
MEDIUMINT[(M)] [UNSIGNED]	MEDIUMINT [UNSIGNED]	java.lang.Integer, if UNSIGNED java.lang.Long (C/J 3.1 and earlier), or java.lang.Integer for C/J 5.0 and later
INT,INTEGER[(M)] [UNSIGNED]	INTEGER [UNSIGNED]	java.lang.Integer, if UNSIGNED java.lang.Long
BIGINT[(M)] [UNSIGNED]	BIGINT [UNSIGNED]	java.lang.Long, if UNSIGNED java.math.BigInteger
FLOAT[(M,D)]	FLOAT	java.lang.Float
DOUBLE[(M,B)]	DOUBLE	java.lang.Double
DECIMAL[(M[,D])]	DECIMAL	java.math.BigDecimal
DATE	DATE	java.sql.Date
DATETIME	DATETIME	java.sql.Timestamp
TIMESTAMP[(M)]	TIMESTAMP	java.sql.Timestamp
TIME	TIME	java.sql.Time
YEAR[(2|4)]	YEAR	If yearIsDateType configuration property is set to false, then the returned object type is java.sql.Short. If set to true (the default) then an object of type java.sql.Date (with the date set to January 1st, at midnight).
CHAR(M)	CHAR	java.lang.String (unless the character set for the column is BINARY, then byte[] is returned.
VARCHAR(M) [BINARY]	VARCHAR	java.lang.String (unless the character set for the column is BINARY, then byte[] is returned.
BINARY(M)	BINARY	byte[]
VARBINARY(M)	VARBINARY	byte[]
TINYBLOB	TINYBLOB	byte[]
TINYTEXT	VARCHAR	java.lang.String
BLOB	BLOB	byte[]
TEXT	VARCHAR	java.lang.String
MEDIUMBLOB	MEDIUMBLOB	byte[]
MEDIUMTEXT	VARCHAR	java.lang.String
LONGBLOB	LONGBLOB	byte[]
LONGTEXT	VARCHAR	java.lang.String
ENUM('value1','value2',...)	CHAR	java.lang.String
SET('value1','value2',...)	CHAR	java.lang.String

Connection Properties - Miscellaneous. 

These MySQL Data Types	Can always be converted to these Java types
CHAR, VARCHAR, BLOB, TEXT, ENUM, and SET	java.lang.String, java.io.InputStream, java.io.Reader, java.sql.Blob, java.sql.Clob
FLOAT, REAL, DOUBLE PRECISION, NUMERIC, DECIMAL, TINYINT, SMALLINT, MEDIUMINT, INTEGER, BIGINT	java.lang.String, java.lang.Short, java.lang.Integer, java.lang.Long, java.lang.Double, java.math.BigDecimal
DATE, TIME, DATETIME, TIMESTAMP	java.lang.String, java.sql.Date, java.sql.Timestamp

Referred : http://dev.mysql.com/doc/refman/4.1/en/connector-j-reference-type-conversions.html