Example usage for java.sql Connection createArrayOf

List of usage examples for java.sql Connection createArrayOf

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Connection
  6. createArrayOf

Introduction

In this page you can find the example usage for java.sql Connection createArrayOf.

Prototype

Array createArrayOf(String typeName, Object[] elements) throws SQLException;

Source Link

Document

Factory method for creating Array objects.

Usage

From source file:org.apache.phoenix.schema.stats.NoOpStatsCollectorIT.java

private void upsertValues(Connection conn, String tableName) throws SQLException {
    PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES(?,?,?)");
    stmt.setString(1, "a");
    String[] s = new String[] { "abc", "def", "ghi", "jkll", null, null, "xxx" };
    Array array = conn.createArrayOf("VARCHAR", s);
    stmt.setArray(2, array);//from  w w w .ja va 2 s. c o  m
    s = new String[] { "abc", "def", "ghi", "jkll", null, null, null, "xxx" };
    array = conn.createArrayOf("VARCHAR", s);
    stmt.setArray(3, array);
    stmt.execute();
    conn.commit();
}

From source file:org.kawanfw.test.api.client.ArrayTest.java

/**
 * @param connection/*w w  w.j  av a 2 s. c  om*/
 *            the AceQL Connection
 * 
 * @throws SQLException
 * @throws Exception
 */
public void test(Connection connection) throws SQLException, Exception {

    MessageDisplayer.initClassDisplay(this.getClass().getSimpleName());

    DatabaseMetaData databaseMetaData = connection.getMetaData();
    MessageDisplayer.display(
            "databaseMetaData.getDatabaseProductName()   : " + databaseMetaData.getDatabaseProductName());

    if (connection instanceof RemoteConnection) {
        RemoteConnection connectionHttp = (RemoteConnection) connection;
        if (connectionHttp.isStatelessMode()) {
            MessageDisplayer.display("Arrays are not supported in stateless mode");
            return;
        }
    }

    if (!new SqlUtil(connection).isPostgreSQL() && !new SqlUtil(connection).isHSQLDB()
            && !new SqlUtil(connection).isOracle()) {
        MessageDisplayer.display("Arrays tests are only supported in HSQLDB, Oracle Database & PostgreSQL");
        return;
    }

    Object[] northEastRegion = { "10022", "02110", "07399" };

    String sql = null;
    boolean doInsert = true;

    if (doInsert) {

        // Array aArray = connection.createArrayOf("varchar",
        // northEastRegion);

        Array aArray = null;

        if (new SqlUtil(connection).isOracle()) {
            aArray = connection.createArrayOf("VCARRAY", northEastRegion);
        } else {
            aArray = connection.createArrayOf("varchar", northEastRegion);
        }

        PreparedStatement pstmt = connection.prepareStatement("delete from REGIONS where region_name = ?");
        pstmt.setString(1, "NorthEast");
        pstmt.executeUpdate();
        pstmt.close();

        PreparedStatement pstmt2 = connection
                .prepareStatement("insert into REGIONS (region_name, zips) " + "VALUES (?, ?)");
        pstmt2.setString(1, "NorthEast");
        pstmt2.setArray(2, aArray);
        pstmt2.executeUpdate();
        pstmt2.close();

    }

    sql = "select * from REGIONS";
    PreparedStatement prepStatement = connection.prepareStatement(sql);
    ResultSet rs = prepStatement.executeQuery();

    while (rs.next()) {
        String region_name = rs.getString(1);
        MessageDisplayer.display(region_name + "  ");

        Array z = rs.getArray("ZIPS");

        Object[] zips = (Object[]) z.getArray();

        for (int i = 0; i < zips.length; i++) {
            System.out.print(zips[i] + " ");
        }
        MessageDisplayer.display("");

        if (region_name.equals("NorthEast")) {
            MessageDisplayer.display("Region is NorthEast. Test arrays equality");

            Assert.assertArrayEquals("Region is NorthEast. Test arrays equality", northEastRegion, zips);
        }
    }

    rs.close();

    MessageDisplayer.display("Done!");

}

From source file:org.nuxeo.ecm.core.storage.sql.jdbc.dialect.DialectPostgreSQL.java

@Override
public Array createArrayOf(int type, Object[] elements, Connection connection) throws SQLException {
    if (elements == null || elements.length == 0) {
        return null;
    }//from   w w w .j a v a 2 s  . com
    String typeName;
    switch (type) {
    case Types.VARCHAR:
        typeName = "varchar";
        break;
    case Types.CLOB:
        typeName = "text";
        break;
    case Types.BIT:
        typeName = "bool";
        break;
    case Types.BIGINT:
        typeName = "int8";
        break;
    case Types.DOUBLE:
        typeName = "float8";
        break;
    case Types.TIMESTAMP:
        typeName = "timestamp";
        break;
    case Types.SMALLINT:
        typeName = "int2";
        break;
    case Types.INTEGER:
        typeName = "int4";
        break;
    case Types.OTHER: // id
        switch (idType) {
        case VARCHAR:
            typeName = "varchar";
            break;
        case UUID:
            typeName = "uuid";
            break;
        case SEQUENCE:
            typeName = "int8";
            break;
        default:
            throw new AssertionError("Unknown id type: " + idType);
        }
        break;
    default:
        throw new AssertionError("Unknown type: " + type);
    }
    return connection.createArrayOf(typeName, elements);
}

From source file:org.siphon.jssql.SqlExecutor.java

private Array createSqlArray(Connection connection, NativeArray arr)
        throws SQLException, UnsupportedDataTypeException, SqlExecutorException {
    Object[] objs = new Object[JsTypeUtil.getArrayLength(arr)];
    for (int i = 0; i < JsTypeUtil.getArrayLength(arr); i++) {
        objs[i] = arr.get(i);/*from   w ww.  j av  a2 s.c o  m*/
    }
    String type = (String) objs[0];

    objs = Arrays.copyOfRange(objs, 1, objs.length);
    for (int i = 0; i < objs.length; i++) {
        objs[i] = convertJsObjToJavaType(objs[i]);
    }
    Array result = connection.createArrayOf(type, objs);
    return result;
}

From source file:org.smallmind.persistence.orm.hibernate.LongArrayUserType.java

@Override
public void nullSafeSet(final PreparedStatement statement, final Object object, final int i,
        final SessionImplementor sessionImplementor) throws HibernateException, SQLException {

    Connection connection = statement.getConnection();

    long[] castObject = (long[]) object;
    Long[] longs = ArrayUtils.toObject(castObject);
    Array array = connection.createArrayOf("long", longs);

    statement.setArray(i, array);//from ww w . j a  v  a2s .  c om
}

From source file:org.wso2.carbon.device.mgt.core.search.mgt.dao.impl.SearchDAOImpl.java

private List<Device> fillPropertiesOfDevices(List<Device> devices) throws SearchDAOException {
    if (devices.isEmpty()) {
        return null;
    }//from w w w  .j a  v  a 2 s  .c o m

    Connection conn;
    PreparedStatement stmt;
    ResultSet rs;

    try {
        conn = this.getConnection();
        String query = "SELECT * FROM DM_DEVICE_INFO WHERE DEVICE_ID IN (?) ORDER BY DEVICE_ID ;";
        stmt = conn.prepareStatement(query);
        if (conn.getMetaData().getDatabaseProductName().contains("H2")
                || conn.getMetaData().getDatabaseProductName().contains("MySQL")) {
            String inData = Utils.getDeviceIdsAsString(devices);
            stmt.setString(1, inData);
        } else {
            Array array = conn.createArrayOf("INT", Utils.getArrayOfDeviceIds(devices));
            stmt.setArray(1, array);
        }
        rs = stmt.executeQuery();

        DeviceInfo dInfo;
        while (rs.next()) {
            dInfo = this.getDeviceInfo(devices, rs.getInt("DEVICE_ID"));
            dInfo.getDeviceDetailsMap().put(rs.getString("KEY_FIELD"), rs.getString("VALUE_FIELD"));
        }
    } catch (SQLException e) {
        throw new SearchDAOException("Error occurred while retrieving the device properties.", e);
    }
    return devices;
}

From source file:org.wso2.carbon.device.mgt.core.search.mgt.impl.ProcessorImpl.java

private List<Device> fillPropertiesOfDevices(List<Device> devices) throws SearchDAOException {
    if (devices.isEmpty()) {
        return null;
    }//from   w ww .java 2 s  .c o  m
    Connection conn;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        conn = this.getConnection();
        String query = "SELECT * FROM DM_DEVICE_INFO WHERE DEVICE_ID IN (";
        if (conn.getMetaData().getDatabaseProductName()
                .contains(DeviceManagementConstants.DataBaseTypes.DB_TYPE_H2)
                || conn.getMetaData().getDatabaseProductName()
                        .contains(DeviceManagementConstants.DataBaseTypes.DB_TYPE_MYSQL)
                || conn.getMetaData().getDatabaseProductName()
                        .contains(DeviceManagementConstants.DataBaseTypes.DB_TYPE_ORACLE)
                || conn.getMetaData().getDatabaseProductName()
                        .contains(DeviceManagementConstants.DataBaseTypes.DB_TYPE_MSSQL)) {
            StringBuilder builder = new StringBuilder();
            for (int i = 0; i < devices.size(); i++) {
                builder.append("?,");
            }
            query += builder.deleteCharAt(builder.length() - 1).toString() + ") ORDER BY DEVICE_ID";
            stmt = conn.prepareStatement(query);
            for (int i = 0; i < devices.size(); i++) {
                stmt.setInt(i + 1, devices.get(i).getId());
            }
        } else {
            query += "?) ORDER BY DEVICE_ID";
            stmt = conn.prepareStatement(query);
            Array array = conn.createArrayOf("INT", Utils.getArrayOfDeviceIds(devices));
            stmt.setArray(1, array);
        }
        rs = stmt.executeQuery();

        DeviceInfo dInfo;
        while (rs.next()) {
            dInfo = this.getDeviceInfo(devices, rs.getInt("DEVICE_ID"));
            dInfo.getDeviceDetailsMap().put(rs.getString("KEY_FIELD"), rs.getString("VALUE_FIELD"));
        }
    } catch (SQLException e) {
        throw new SearchDAOException("Error occurred while retrieving the device properties.", e);
    } finally {
        DeviceManagementDAOUtil.cleanupResources(stmt, rs);
    }
    return devices;
}