How to use JSON to exchange data between Flex and Java from MySQL

How to use JSON to exchange data between Flex and Java from MySQL

環境：
Windows XP SP3
Flex Builder 3.2
Eclipse 3.4
MySQL 5.1
Tomcat 6.0
LCDS(LifeCycle Data Service) 2.51

所需Package：

　Java：
　　commons-beanutils
　　commons-lang
　　commons-collections
　　commons-logging
　　Ezmorph
　　json-lib

　Flex：
　　as3corelib

1. 先在MySQL建一個table，就叫user好了

CREATE TABLE `test`.`user` (
  `user_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_name` VARCHAR(10) NOT NULL,
  `user_age` INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY (`user_id`)
);

2. 新增一些假的資料~

INSERT INTO test.user(user_name,user_age) VALUES('Flower2',22);
INSERT INTO test.user(user_name,user_age) VALUES('Ivy',23);
INSERT INTO test.user(user_name,user_age) VALUES('Mandy',18);
INSERT INTO test.user(user_name,user_age) VALUES('Peter',30);
INSERT INTO test.user(user_name,user_age) VALUES('Com',22);

3. 撰寫java程式碼

package com.lflower;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

public class SQLHandler {

    private Connection conn;

    private Connection connectMySQL() {
        try {
            conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/test", "root", "1233");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    public String getUsers() {

        Statement stmt;
        JSONArray users = new JSONArray();

        try {
            stmt = this.connectMySQL().createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM `test`.`user`");

            while (rs.next()) {
                JSONObject user = new JSONObject();
                user.put("UserId", rs.getString("user_id"));
                user.put("UserName", rs.getString("user_name"));
                user.put("UserAge", rs.getString("user_age"));
                users.add(user);
            }
            rs.close();
            stmt.close();
            connectMySQL().close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return users.toString();
    }

    public static void main(String[] args) {
        new SQLHandler().getUsers();
    }
}

在這邊會用到JSONArray和JSONObject
將每一筆資料以Key and Value的方式put到JSONObject
然後在被add到JSONArray裡
最後將JSONArray以String的型態回傳給Flex

4. 設定remoting-config.xml

    <destination id="SQL">
        <properties>
            <source>com.lflower.SQLHandler</source>
        </properties>
    </destination>

5. 寫一個介面來檢視我們取得的資料（將as3corelib裡的as3corelib.swc複製到Flex專案裡的libs）

<?xml version="1.0" encoding="utf-8"?>
<mx:Application xmlns:mx="http://www.adobe.com/2006/mxml"
                layout="vertical">
    <mx:Script>
        <![CDATA[
            import mx.collections.ArrayCollection;
            import mx.rpc.events.FaultEvent;
            import mx.rpc.events.ResultEvent;
            import mx.controls.Alert;
            import com.adobe.serialization.json.JSON;

            [Bindable]
            public var users:ArrayCollection;

            private function roResult(event:ResultEvent):void
            {
                var result:String=event.result.toString();
                var usersArray:Array=JSON.decode(result) as Array;
                users=new ArrayCollection(usersArray);
            }

            private function roFault(event:FaultEvent):void
            {
                Alert.show(event.message.toString(), "Error");
            }
        ]]>
    </mx:Script>
    <mx:RemoteObject id="ro"
                     destination="SQL"
                     result="roResult(event)"
                     fault="roFault(event)"/>

    <mx:DataGrid dataProvider="{users}"/>

    <mx:Button label="Get users"
               click="{ro.getUsers()}"/>
</mx:Application>
 

6. 執行結果