List of usage examples for java.sql PreparedStatement setArray
void setArray(int parameterIndex, Array x) throws SQLException;
java.sql.Array object. From source file:org.kawanfw.test.api.client.ArrayTest.java
/** * @param connection/*ww w . j a v a2 s. co m*/ * 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.db.dialect.DialectPostgreSQL.java
@Override public void setToPreparedStatement(PreparedStatement ps, int index, Serializable value, Column column) throws SQLException { switch (column.getJdbcType()) { case Types.VARCHAR: case Types.CLOB: String v;// w w w . ja va 2s .c o m if (column.getType() == ColumnType.BLOBID) { v = ((Binary) value).getDigest(); } else { v = (String) value; } ps.setString(index, v); break; case Types.BIT: ps.setBoolean(index, ((Boolean) value).booleanValue()); return; case Types.SMALLINT: ps.setInt(index, ((Long) value).intValue()); return; case Types.INTEGER: case Types.BIGINT: ps.setLong(index, ((Long) value).longValue()); return; case Types.DOUBLE: ps.setDouble(index, ((Double) value).doubleValue()); return; case Types.TIMESTAMP: Calendar cal = (Calendar) value; Timestamp ts = new Timestamp(cal.getTimeInMillis()); ps.setTimestamp(index, ts, cal); // cal passed for timezone return; case Types.ARRAY: Array array = createArrayOf(Types.VARCHAR, (Object[]) value, ps.getConnection()); ps.setArray(index, array); return; case Types.OTHER: if (column.getType() == ColumnType.FTSTORED) { ps.setString(index, (String) value); return; } throw new SQLException("Unhandled type: " + column.getType()); default: throw new SQLException("Unhandled JDBC type: " + column.getJdbcType()); } }
From source file:org.nuxeo.ecm.core.storage.sql.jdbc.dialect.DialectPostgreSQL.java
@Override public void setToPreparedStatement(PreparedStatement ps, int index, Serializable value, Column column) throws SQLException { switch (column.getJdbcType()) { case Types.VARCHAR: case Types.CLOB: setToPreparedStatementString(ps, index, value, column); return;/*from www . jav a 2s . c om*/ case Types.BIT: ps.setBoolean(index, ((Boolean) value).booleanValue()); return; case Types.SMALLINT: ps.setInt(index, ((Long) value).intValue()); return; case Types.INTEGER: case Types.BIGINT: ps.setLong(index, ((Number) value).longValue()); return; case Types.DOUBLE: ps.setDouble(index, ((Double) value).doubleValue()); return; case Types.TIMESTAMP: ps.setTimestamp(index, getTimestampFromCalendar((Calendar) value)); return; case Types.ARRAY: int jdbcBaseType = column.getJdbcBaseType(); String jdbcBaseTypeName = column.getSqlBaseTypeString(); if (jdbcBaseType == Types.TIMESTAMP) { value = getTimestampFromCalendar((Serializable[]) value); } Array array = ps.getConnection().createArrayOf(jdbcBaseTypeName, (Object[]) value); ps.setArray(index, array); return; case Types.OTHER: ColumnType type = column.getType(); if (type.isId()) { setId(ps, index, value); return; } else if (type == ColumnType.FTSTORED) { ps.setString(index, (String) value); return; } throw new SQLException("Unhandled type: " + column.getType()); default: throw new SQLException("Unhandled JDBC type: " + column.getJdbcType()); } }
From source file:org.nuxeo.ecm.core.storage.sql.jdbc.JDBCMapper.java
public int setToPreparedStatement(PreparedStatement ps, int i, Serializable object) throws SQLException { if (object instanceof Calendar) { Calendar cal = (Calendar) object; ps.setTimestamp(i, dialect.getTimestampFromCalendar(cal), cal); } else if (object instanceof java.sql.Date) { ps.setDate(i, (java.sql.Date) object); } else if (object instanceof Long) { ps.setLong(i, ((Long) object).longValue()); } else if (object instanceof WrappedId) { dialect.setId(ps, i, object.toString()); } else if (object instanceof Object[]) { int jdbcType; if (object instanceof String[]) { jdbcType = dialect.getJDBCTypeAndString(ColumnType.STRING).jdbcType; } else if (object instanceof Boolean[]) { jdbcType = dialect.getJDBCTypeAndString(ColumnType.BOOLEAN).jdbcType; } else if (object instanceof Long[]) { jdbcType = dialect.getJDBCTypeAndString(ColumnType.LONG).jdbcType; } else if (object instanceof Double[]) { jdbcType = dialect.getJDBCTypeAndString(ColumnType.DOUBLE).jdbcType; } else if (object instanceof java.sql.Date[]) { jdbcType = Types.DATE; } else if (object instanceof java.sql.Clob[]) { jdbcType = Types.CLOB; } else if (object instanceof Calendar[]) { jdbcType = dialect.getJDBCTypeAndString(ColumnType.TIMESTAMP).jdbcType; object = dialect.getTimestampFromCalendar((Calendar) object); } else if (object instanceof Integer[]) { jdbcType = dialect.getJDBCTypeAndString(ColumnType.INTEGER).jdbcType; } else {//w w w. j ava 2s. c o m jdbcType = dialect.getJDBCTypeAndString(ColumnType.CLOB).jdbcType; } Array array = dialect.createArrayOf(jdbcType, (Object[]) object, connection); ps.setArray(i, array); } else { ps.setObject(i, object); } return i; }
From source file:org.nuxeo.ecm.core.storage.sql.jdbc.JDBCRowMapper.java
protected void setToPreparedStatementIdArray(PreparedStatement ps, int index, Serializable idArray) throws SQLException { if (idArray instanceof String) { ps.setString(index, (String) idArray); } else {// w w w .j ava 2s . co m Array array = dialect.createArrayOf(Types.OTHER, (Object[]) idArray, connection); ps.setArray(index, array); } }
From source file:org.nuxeo.ecm.core.storage.sql.Mapper.java
/** * Makes a NXQL query to the database./*from w w w . j a v a 2 s . co m*/ * * @param query the query * @param queryFilter the query filter * @param countTotal if {@code true}, count the total size without * limit/offset * @param session the current session (to resolve paths) * @return the list of matching document ids * @throws StorageException * @throws SQLException */ public PartialList<Serializable> query(String query, QueryFilter queryFilter, boolean countTotal, Session session) throws StorageException { QueryMaker queryMaker = findQueryMaker(query); if (queryMaker == null) { throw new StorageException("No QueryMaker accepts query: " + query); } QueryMaker.Query q = queryMaker.buildQuery(sqlInfo, model, session, query, queryFilter); if (q == null) { log("Query cannot return anything due to conflicting clauses"); return new PartialList<Serializable>(Collections.<Serializable>emptyList(), 0); } long limit = queryFilter.getLimit(); long offset = queryFilter.getOffset(); if (isLogEnabled()) { String sql = q.selectInfo.sql; if (limit != 0) { sql += " -- LIMIT " + limit + " OFFSET " + offset; } if (countTotal) { sql += " -- COUNT TOTAL"; } logSQL(sql, q.selectParams); } PreparedStatement ps = null; try { ps = connection.prepareStatement(q.selectInfo.sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); int i = 1; for (Object object : q.selectParams) { if (object instanceof Calendar) { Calendar cal = (Calendar) object; Timestamp ts = new Timestamp(cal.getTimeInMillis()); ps.setTimestamp(i++, ts, cal); // cal passed for timezone } else if (object instanceof String[]) { Array array = sqlInfo.dialect.createArrayOf(Types.VARCHAR, (Object[]) object, connection); ps.setArray(i++, array); } else { ps.setObject(i++, object); } } ResultSet rs = ps.executeQuery(); // get all the returned data. name -> id Column column = q.selectInfo.whatColumns.get(0); Map<Serializable, String> id2Name = new LinkedHashMap<Serializable, String>(); while (rs.next()) { String id = (String) column.getFromResultSet(rs, 1); String name = rs.getString("name"); id2Name.put(id, name); } String uname = queryFilter.getPrincipal().getName(); Map<Serializable, CRMPermission> permissions = CRMPermissionRestService.filterHierarchyResult(uname, id2Name); long totalSize = permissions.size(); List<Serializable> allDocIds = new ArrayList<Serializable>(); allDocIds.addAll(permissions.keySet()); List<Serializable> pageDocIds = null; if (limit == 0) { limit = allDocIds.size(); } int startIdx = (offset > allDocIds.size()) ? allDocIds.size() : (int) offset; int endIdx = ((offset + limit) > allDocIds.size()) ? allDocIds.size() : (int) (offset + limit); pageDocIds = allDocIds.subList(startIdx, endIdx); return new PartialList<Serializable>(pageDocIds, totalSize); } catch (SQLException e) { checkConnectionReset(e); throw new StorageException("Invalid query: " + query, e); } finally { if (ps != null) { try { closePreparedStatement(ps); } catch (SQLException e) { log.error("Cannot close connection", e); } } } }
From source file:org.siphon.jssql.SqlExecutor.java
void setArg(PreparedStatement ps, int index, Object arg) throws SQLException, SqlExecutorException, UnsupportedDataTypeException, NoSuchMethodException, ScriptException { boolean output = false; int outputParameterType = 0; CallableStatement cs = null;/*from w w w . j a v a 2s . c om*/ if (ps instanceof CallableStatement) { cs = (CallableStatement) ps; if (arg instanceof ScriptObjectMirror && ((ScriptObjectMirror) arg).containsKey("OUT")) { ScriptObjectMirror jsarg = ((ScriptObjectMirror) arg); outputParameterType = (int) jsarg.get("JDBC_TYPE"); arg = jsarg.get("VALUE"); output = true; } } if (output) { cs.registerOutParameter(index + 1, outputParameterType); if (JsTypeUtil.isNull(arg) || (arg instanceof Double && Double.isNaN((Double) arg))) { return; } } if (JsTypeUtil.isNull(arg)) { ps.setObject(index + 1, null); } else if (arg instanceof CharSequence) { ps.setString(index + 1, arg.toString()); } else if (arg instanceof NativeString) { ps.setString(index + 1, arg.toString()); } else if (arg instanceof Double) { // js number always be // Doublebut if its came from // JSON.parse since JSON is jdk // given global object, it will // make Integer and ... double d = ((Double) arg).doubleValue(); if (d == (int) d) { ps.setInt(index + 1, (int) d); } else if (d == (long) d) { ps.setLong(index + 1, (long) d); } else { ps.setBigDecimal(index + 1, new BigDecimal(d)); } } else if (arg instanceof Integer) { ps.setInt(index + 1, (Integer) arg); } else if (arg instanceof Long) { ps.setLong(index + 1, (Long) arg); } else if (arg instanceof Float) { ps.setFloat(index + 1, (Float) arg); } else if (jsTypeUtil.isNativeDate(arg)) { ps.setTimestamp(index + 1, parseDate(arg)); } else if (arg instanceof ZonedDateTime) { ZonedDateTime zdt = (ZonedDateTime) arg; ps.setTimestamp(index + 1, new Timestamp(zdt.toInstant().toEpochMilli())); } else if (arg instanceof Boolean) { ps.setBoolean(index + 1, JsTypeUtil.isTrue(arg)); } else if (arg instanceof ScriptObjectMirror || arg instanceof ScriptObject) { String attr = null; Object value = null; if (arg instanceof ScriptObjectMirror) { ScriptObjectMirror atm = (ScriptObjectMirror) arg; if (atm.keySet().contains("toJavaObject")) { Object obj = atm.callMember("toJavaObject"); setArg(ps, index, obj); return; } attr = atm.keySet().iterator().next(); value = atm.get(attr); } else { ScriptObject obj = (ScriptObject) arg; if (obj.containsKey("toJavaObject")) { ScriptObjectMirror atm = (ScriptObjectMirror) jsTypeUtil.toScriptObjectMirror(obj); Object result = atm.callMember("toJavaObject"); setArg(ps, index, result); return; } String[] arr = obj.getOwnKeys(false); if (arr.length == 0) { throw new SqlExecutorException("js argument " + arg + " (" + arg.getClass() + ") at " + index + " is an empty js object"); } attr = arr[0]; value = obj.get(attr); } if ("STRING".equals(attr)) { ps.setString(index + 1, String.valueOf(value)); } else if ("DECIMAL".equals(attr)) { if (value instanceof Double) { ps.setBigDecimal(index + 1, new BigDecimal((Double) value)); } else { ps.setBigDecimal(index + 1, new BigDecimal(value + "")); } } else if ("INT".equals(attr)) { if (value instanceof Double) { if (((Double) value).isNaN()) { ps.setObject(index + 1, null); } else { ps.setInt(index + 1, ((Double) value).intValue()); } } else { ps.setInt(index + 1, new Integer(value + "")); } } else if ("BOOLEAN".equals(attr)) { ps.setBoolean(index + 1, JsTypeUtil.isTrue(arg)); } else if ("DOUBLE".equals(attr)) { if (value instanceof Double) { if (((Double) value).isNaN()) { ps.setObject(index + 1, null); } else { ps.setDouble(index + 1, (double) value); } } else { ps.setDouble(index + 1, new Double(value + "")); } } else if ("FLOAT".equals(attr)) { if (value instanceof Double) { if (((Double) value).isNaN()) { ps.setObject(index + 1, null); } else { ps.setFloat(index + 1, (float) (double) value); } } else { ps.setFloat(index + 1, new Float(value + "")); } } else if ("DATE".equals(attr)) { ps.setTimestamp(index + 1, parseDate(value)); } else if ("TIME".equals(attr)) { ps.setTimestamp(index + 1, parseTime(value)); } else if ("BINARY".equals(attr)) { ps.setBytes(index + 1, parseBinary(value)); } else if ("CLOB".equals(attr)) { Clob clob = ps.getConnection().createClob(); clob.setString(1, String.valueOf(value)); ps.setClob(index + 1, clob); } else if ("LONG".equals(attr)) { if (value instanceof Double) { if (((Double) value).isNaN()) { ps.setObject(index + 1, null); } else { ps.setLong(index + 1, ((Double) value).longValue()); } } else { ps.setLong(index + 1, new Long(value + "")); } } else if ("OUTCURSOR".equals(attr)) { // cs.registerOutParameter(i+1, OracleTypes.CURSOR); cs.registerOutParameter(index + 1, -10); } else if ("ARRAY".equals(attr)) { if (value instanceof NativeArray) { ps.setArray(index + 1, createSqlArray(ps.getConnection(), (NativeArray) value)); } else { setArg(ps, index, value); // value is {ARRAY : ['int', e1, e2, ...]} } // ps.setObject(i+1, createSqlArray(ps.getConnection(), // (NativeArray) value)); } else if ("JSON".equals(attr) || "JSONB".equals(attr)) { PGobject obj = new PGobject(); obj.setType(attr.toLowerCase()); obj.setValue(this.JSON.tryStringify(value)); ps.setObject(index + 1, obj); } else if ("UUID".equals(attr)) { if (value != null) { ps.setObject(index + 1, UUID.fromString(value.toString())); } else { ps.setObject(index + 1, null); } } else { if (this.defaultJsonDbType != null) { PGobject obj = new PGobject(); obj.setType(this.defaultJsonDbType); obj.setValue(this.JSON.tryStringify(arg)); ps.setObject(index + 1, obj); } else { throw new SqlExecutorException("js argument " + arg + " (" + arg.getClass() + ") not support"); } } } else { throw new SqlExecutorException( "js argument " + arg + " (" + arg.getClass() + ") at " + index + " not support"); } }
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 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; }/* w ww. j ava2 s . co 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; }//w ww.j ava2 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; }