Mac OSX setup MySQL server and MySQLWorkBench

Environment:
OS X: 10.11.6
MySQL: V5.7.17
MySQLWorkBench: V6.2.3

The installation of MySQL is simple, just follow this guidance,
http://dev.mysql.com/doc/refman/5.7/en/osx-installation-pkg.html

At the end of installation, it will pop up the below window to tell you the temporary password. Take note of this password. It will be used later.

This password can be used only one time. This is the difficulty, we need to take this chance to reset the password.

Supposing when you install MySQL server, you also installed the Preference Pane and Launched Support. After you installation, MySQL server should start automatically. Use OSX "Activity Monitor" to check if there is a process "mysqld". If it's runing, means the server is started. Otherwise, start the MySQL server manually.

After the server is running, we can connect to MySQL server now. Go to the installation path of MySQL, default location is at "/usr/local/mysql/bin". Use the following command to connect the server:

mysql -u root -p

After press return button, it will ask the password, key in the password at the end of installation.
Don't try any command like "sudo mysqld_safe --skip-grant-tables" that suggested online, it will start another MySQL server. You will lose connection of current one.

If the connection is succeeded, you should see the welcome message.

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 224

Server version: 5.5.13 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Now you can reset the password by this command.

SET PASSWORD = PASSWORD('your_new_password');

If it works, you will see.

Query OK, 0 rows affected, 1 warning (0.00 sec)

Otherwise, you will see.

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Don't close the current Terminal window, otherwise you will lose connect with current server, and you will lose the only one chance to reset the password.

If you cannot make it, you may need to uninstall the MySQL server by following commands and install again.

• sudo rm /usr/local/mysql
• sudo rm -rf /usr/local/mysql*
• sudo rm -rf /Library/StartupItems/MySQLCOM
• sudo rm -rf /Library/PreferencePanes/My*
• edit /etc/hostconfig and remove the line MYSQLCOM=-YES-
• rm -rf ~/Library/PreferencePanes/My*
• sudo rm -rf /Library/Receipts/mysql*
• sudo rm -rf /Library/Receipts/MySQL*
• sudo rm -rf /private/var/db/receipts/*mysql*

After resetting the password, you can start to install MySQLWorkBench, and connect the MySQL server.

香港新聞

該新聞客戶端通過優化界面設計，具有方便用戶使用，快速加載和省電等優點。

該客戶端不是簡單的加載新聞網頁而已，它會解析和顯示新聞內容，並且存到服務器。這樣您既可以看到前幾天的新聞，也可以加快新聞加載的速度。

該客戶端目前包括了香港主要的新聞報紙：
    - 蘋果日報
    - 東方日報
    - South China Morning Post (南華早報)
    - 頭條日報
    - 太陽報
    - 明報
    - 大榴蓮網
    - BBC
    - CNN
    - CNET
    - Engadget 中文版
    - 大公報
    - Yahoo香港新聞
    - 香港電台網站
    - am730
    - Google新聞
    - 東網
將有更多的新聞報刊陸續推出，敬請期待！

優點：
* 多元：包括最新的香港，亞洲，世界，政治，生活，體驗，商業，娛樂，科技，財經等新聞。用戶通過滑動界面就可以切換分類。

* 字體：可切換字體大小。

* 收藏：沒有網絡的時候，可以收藏當前新聞，供下次閱讀。

* 分享：分享新聞，分享PSI抓屏。

* 夜晚模式：可以切換白天模式和夜晚模式。

* 離線下載：可緩存讀過的新聞，離線也可以閱讀。

歡迎反饋問題到topnewssg@gmail.com。

iOS:    https://itunes.apple.com/sg/app/hk-top-news/id1180403718?mt=8

Android: https://play.google.com/store/apps/details?id=com.topnewshk

台灣新聞

該新聞客戶端通過優化界面設計，具有方便用戶使用，快速加載和省電等優點。

該客戶端不是簡單的加載新聞網頁而已，它會解析和顯示新聞內容，並且存到服務器。這樣您既可以看到前幾天的新聞，也可以加快新聞加載的速度。

該客戶端目前包括了香港主要的新聞報紙：
     中央通訊社
     東森新聞雲
     NOWnews 今日新聞
     鉅亨網
     Engadget 中文版
     蘋果日報
     中國時報
     中國時報 (即時新聞)
     自由時報
     聯合報
     大榴蓮網
     BBC
     CNN
     CNET
     Yahoo台灣新聞
     台視新聞
     華視新聞
     三立新聞網
     民視新聞網
     番薯藤新聞
     旺報
     經濟日報
將有更多的新聞報刊陸續推出，敬請期待！

優點：
* 多元：包括最新的台灣，亞洲，世界，政治，生活，體驗，商業，娛樂，科技，財經等新聞。用戶通過滑動界面就可以切換分類。

* 字體：可切換字體大小。

* 收藏：沒有網絡的時候，可以收藏當前新聞，供下次閱讀。

* 分享：分享新聞，分享PSI抓屏。

* 夜晚模式：可以切換白天模式和夜晚模式。

* 離線下載：可緩存讀過的新聞，離線也可以閱讀。


歡迎反饋問題到topnewssg@gmail.com。

iOS:    https://itunes.apple.com/sg/app/taiwan-top-news/id1180403714?mt=8
Android: https://play.google.com/store/apps/details?id=com.topnews.twnews

DynamoDB High Level API-DynamoDBMapper (Java)

DynamoDB provides a high level API DynamoDBMapper. This approach can reduce the amount of code comparing with low level APIs(putItem, getItemOutcome, updateItem, query and scan).

AWS has detailed document to explain how to use it, this article is focus on how to use it for custom object and list of custom object.

Before going to deeper, let's have a simple example first (From https://aws.amazon.com/blogs/developer/using-the-savebehavior-configuration-for-the-dynamodbmapper/ ).

Map<String, String> expressionAttributeNames = new HashMap<String, String>();
expressionAttributeNames.put("#P", "Price");

@DynamoDBTable(tableName="TestTable")
public class TestTableItem {

   private int key;
   private String modeledScalar;
   private Set<String> modeledSet;

   @DynamoDBHashKey(attributeName="key")
   public int getKey() { return key; }
   public void setKey(int key) { this.key = key; }

   @DynamoDBAttribute(attributeName="modeled_scalar")
   public String getModeledScalar() { return modeledScalar; }
   public void setModeledScalar(String modeledScalar) { this.modeledScalar = modeledScalar; }
    
   @DynamoDBAttribute(attributeName="modeled_set")
   public Set<String> getModeledSet() { return modeledSet; }
   public void setModeledSet(Set<String> modeledSet) { this.modeledSet = modeledSet; }

}

The approach is simple, you just need to put the annotation to specify the property methods is DynamoDBHashKey, DynamoDBRangeKey or DynamoDBAttribute. If you don't need to save some property into database, you should put annotation DynamoDBIngore on top of property methods.

One thing to remember is the naming of your property methods, you must use the standard name start with "get" and "set". Don't use other prefix, otherwise DynamoDB mapper cannot "reflect" your attribute name to your methods.

To store a custom object of complex class, you need to use DynamoDBTypeConverter(DynamoDBMarshaller is deprecated already).
For example, we have a complex class PhoneNumber

public class PhoneNumber {
    private String areaCode;
    private String exchange;
    private String subscriberLineIdentifier;
 
    public String getAreaCode() { return areaCode; }  
    public void setAreaCode(String areaCode) { this.areaCode = areaCode; }
 
    public String getExchange() { return exchange; }
    public void setExchange(String exchange) { this.exchange = exchange; }
 
    public String getSubscriberLineIdentifier() { return subscriberLineIdentifier; }  
    public void setSubscriberLineIdentifier(String subscriberLineIdentifier) { this.subscriberLineIdentifier = subscriberLineIdentifier; }    
}

And we add a custom object to the above example.

@DynamoDBTable(tableName="TestTable")
public class TestTableItem {

   private int key;
   private String modeledScalar;
   private Set<String> modeledSet;
   private PhoneNumber phoneNumber;

   @DynamoDBHashKey(attributeName="key")
   public int getKey() { return key; }
   public void setKey(int key) { this.key = key; }

   @DynamoDBAttribute(attributeName="modeled_scalar")
   public String getModeledScalar() { return modeledScalar; }
   public void setModeledScalar(String modeledScalar) { this.modeledScalar = modeledScalar; }
    
   @DynamoDBAttribute(attributeName="modeled_set")
   public Set<String> getModeledSet() { return modeledSet; }
   public void setModeledSet(Set<String> modeledSet) { this.modeledSet = modeledSet; }

    @DynamoDBTypeConverted(converter = PhoneNumberConverter.class)
    public PhoneNumber getPhoneNumber() { return phoneNumber; }  
    public void setPhoneNumber(PhoneNumber phoneNumber) { this.phoneNumber = phoneNumber; }

}

Instead of using standard annotation DynamoDBAttribute, now we need to use @DynamoDBTypeConverted(converter = PhoneNumberConverter.class), and PhoneNumberConverter here is a class implementing DynamoDBTypeConverter interface.

Here is how the interface looks like. It just a interface to convert the object to a string and convert it back.

public class PhoneNumberConverterimplements DynamoDBTypeConverter<String, PhoneNumber>
{
    @Override
    public String convert(PhoneNumber number) {
        return "(" + number.getAreaCode() + ") " + number.getExchange() + "-" + number.getSubscriberLineIdentifier();
    }

    @Override
    public PhoneNumber unconvert(String s) {
        String[] areaCodeAndNumber = s.split(" ");
        String areaCode = areaCodeAndNumber[0].substring(1,4);
        String[] exchangeAndSlid = areaCodeAndNumber[1].split("-");
        PhoneNumber number = new PhoneNumber();
        number.setAreaCode(areaCode);
        number.setExchange(exchangeAndSlid[0]);
        number.setSubscriberLineIdentifier(exchangeAndSlid[1]);
        return number;
    }  
}

Having the above basic knowledge, now let's see how to do it for a List of custom object.
For example, we have a list of phone numbers.

@DynamoDBTable(tableName="TestTable")
public class TestTableItem {

   private int key;
   private String modeledScalar;
   private Set<String> modeledSet;
   private List<PhoneNumber> phoneNumbers;
 
   ......

   @DynamoDBTypeConverted(converter = PhoneNumberConverter.class)
   public List<PhoneNumber> getPhoneNumbers() { return phoneNumbers; }  
   public void setPhoneNumbers(List<PhoneNumber> phoneNumbers) { this.phoneNumbers = phoneNumbers; }

}

What we need to do is to convert the list of object to string and convert it back.

public class PhoneNumberConverterimplements DynamoDBTypeConverter<String, PhoneNumber>
{
    private static final ObjectMapper mapper = new ObjectMapper();
    private static final ObjectWriter writer = mapper.writerWithType(new TypeReference<List<PhoneNumber>>(){});
    @Override
    public String convert(List<PhoneNumber> numbers) {
               try {
            return writer.writeValueAsString(numbers);
        } catch (JsonProcessingException e) {
            System.out.println(
                    "Unable to marshall the instance of " + numbers.getClass()
                    + "into a string");
            return null;
        }
    }

    @Override
    public List<PhoneNumber> unconvert(String s) {
        TypeReference<List<PhoneNumber>> type = new TypeReference<List<PhoneNumber>>() {};
        try {
            List<PhoneNumber> list = mapper.readValue(s, type);
            return list;
        } catch (Exception e) {
            System.out.println("Unable to unmarshall the string " + s
                             + "into " + s);
            return null;
        }
    }  
}

DynamoDB Low Level API-updateItem() (Java)

If you don't want to use high-level API DynamoDBMapper to update the DynamoDB table. You can use the low level APIs to update the table items.

AWS don't provide detail document for item update in Java, this document is going to introduce several item update methods systematically.

1. UpdateItemOutcome updateItem(PrimaryKey primaryKey, AttributeUpdate... attributeUpdates)

This is most simple one, you are using AttributeUpdate directly. By using AttributeUpdate, you can update the item by add objects by using addElements()or add number by using addNumeric().

1 2 3 4 5 6 7

final String phoneNumber = "9872-6530"; table.updateItem(HASH_KEY, ITEM_HASH_KEY, RANGE_KEY, ITEM_RANGE_KEY, new AttributeUpdate("phone").addElements(phoneNumber)); outcome = table.getItemOutcome(new GetItemSpec() .withPrimaryKey(HASH_KEY, ITEM_HASH_KEY, RANGE_KEY, ITEM_RANGE_KEY) .withConsistentRead(true)); item = outcome.getItem();

2. UpdateItemOutcome updateItem(UpdateItemSpec updateItemSpec)

This is most powerful one, UpdateItemSpec() has all the features you need to update a item. There is also a benefit of using UpdateItemSpec(), you can specify the return value and expected results. If the ReturnValue is UPDATED_NEW, then it only returns the attributes you updated.
If the ReturnValue is ALL_NEW, then it will returns the whole item with all new attributes.

1 2 3 4 5 6 7 8 9 10 11

UpdateItemOutcome outcome = table.updateItem(new UpdateItemSpec() .withReturnValues(ReturnValue.ALL_NEW) .withPrimaryKey(HASH_KEY, ITEM_HASH_KEY, RANGE_KEY, ITEM_RANGE_KEY) .withAttributeUpdate( new AttributeUpdate("Player-Position").addNumeric(1), new AttributeUpdate("Status").put("SUSPENDED")) .withExpected( new Expected("Player1-Position").lt(20), new Expected("Player2-Position").lt(20), new Expected("Status").eq("IN_PROGRESS")) );

3. UpdateItemOutcome updateItem(PrimaryKey primaryKey,
            String updateExpression, String conditionExpression,
            Map nameMap, Map valueMap)

This is another method to update item. You can specify the update expression directly.

1 2 3 4 5 6 7 8 9 10 11 12 13

Map<String, String> expressionAttributeNames = new HashMap<String, String>(); expressionAttributeNames.put("#P", "Price"); Map<String, Object> expressionAttributeValues = new HashMap<String, Object>(); expressionAttributeValues.put(":val1", 25); // update Price to 25... expressionAttributeValues.put(":val2", 20); //...but only if existing Price is 20 UpdateItemOutcome outcome = table.updateItem( new PrimaryKey("Id",101), "set #P = :val1", // UpdateExpression "#P = :val2", // ConditionExpression expressionAttributeNames, expressionAttributeValues);

You can choose the method you need to update a DynamoDB table item.