Oracle的Array型態定義與存取－Jemmy Walker

　　有別於PostgreSQL可以直接把欄位定義為陣列型態，Oracle是透過自訂型態的方式達成目標，飯粒如下：

create or replace Type varray_phone as varray(3) of varchar2(20)

　　上述是定義一個名為varray_phone的Type，是維度為三個varchar2(20)的資料型態。所以varray是宣告陣列維度，of後接的不止Oracle的資料型態度，也可以接更複雜的Oracle Object型態，複雜的部份就不討論。而其Create、Insert的用法很簡單，如下：

create table employee (id number(10) primary key, name varchar2(10), phone varray_phone)

Insert into employee values (1, 'Jemmy', varray_phone('0911-222333', '0922-33344'))

　　這篇文章http://www.devx.com/tips/Tip/22034描述如何用JDBC存取Array型態，就不贅述，此處列出如何用Spring的JDBC Template存取Array型態飯粒。

import javax.sql.DataSource;

import oracle.sql.ARRAY;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.systex.rdf4.util.AnnotationConfigContextLoader;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(loader = AnnotationConfigContextLoader.class,
value = "com.foo.bar.DSConfig")
public class TestVArray {
private static Logger logger = LoggerFactory.getLogger(TestVArray.class);

private SimpleJdbcTemplate simpleJdbcTemplate;

    @Autowired
    public void init(DataSource datasource) {
        this.simpleJdbcTemplate = new SimpleJdbcTemplate(datasource);
    }

    @Test
    public void testVArray() {
        String sql = "insert into employee values (?, ?, varray_phone(?, ?, ?))";
        this.simpleJdbcTemplate.update(sql, 2, "Tom", "12345", "67890", "09876");
        sql = "select * from employee";
        List<Map<String, Object>> rsList = this.simpleJdbcTemplate.queryForList(sql);
        for (int i=0, n=rsList.size(); i<n; i++) {
            System.out.println("====== Line #" + i + " ======");
            Map<String, Object> map = rsList.get(i);
            System.out.println(map.get("NAME"));
            ARRAY array = (ARRAY) map.get("PHONE");
            try {
                String[] values = (String[]) array.getArray();
                if (values != null) {
                    for (String phone : values) {
                        System.out.println(phone + ", ");
                    }
                } else {
                    System.out.print("phone is null");
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}