List of usage examples for java.sql PreparedStatement setArray
void setArray(int parameterIndex, Array x) throws SQLException;
java.sql.Array object. From source file:Main.java
public static void main(String[] args) throws Exception { Connection conn = null;/*w ww. j a v a 2 s .c o m*/ PreparedStatement pstmt = null; java.sql.Array sqlArray = null; conn = getOracleConnection(); // ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("CHAR_ARRAY", conn); String[] content = { "v1", "v2", "v3", "v4" }; // sqlArray = new oracle.sql.ARRAY(arrayDescriptor, conn, content); String query = "insert into CHAR_ARRAY_TABLE(id, array) values(?, ?)"; pstmt = conn.prepareStatement(query); pstmt.setString(1, "0001"); pstmt.setArray(2, sqlArray); int rowCount = pstmt.executeUpdate(); System.out.println("rowCount=" + rowCount); System.out.println("--Demo_PreparedStatement_SetArray end--"); pstmt.close(); conn.close(); }
From source file:Main.java
public static void main(String[] args) throws Exception { Connection conn = null;/*w w w .ja va2 s. c o m*/ PreparedStatement pstmt = null; java.sql.Array sqlArray = null; conn = getOracleConnection(); // For oracle you need an array descriptor specifying // the type of the array and a connection to the database // the first parameter must match with the SQL ARRAY type created ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("CHAR_ARRAY", conn); // then obtain an Array filled with the content below String[] content = { "v1", "v2", "v3", "v4" }; sqlArray = new oracle.sql.ARRAY(arrayDescriptor, conn, content); String query = "insert into CHAR_ARRAY_TABLE(id, array) values(?, ?)"; pstmt = conn.prepareStatement(query); pstmt.setString(1, "0001"); pstmt.setArray(2, sqlArray); int rowCount = pstmt.executeUpdate(); System.out.println("rowCount=" + rowCount); System.out.println("--Demo_PreparedStatement_SetArray end--"); pstmt.close(); conn.close(); }
From source file:ru.org.linux.user.ProfileDao.java
public void writeProfile(@Nonnull final User user, @Nonnull final Profile profile) { String boxlets[] = null;//from w ww . j a v a 2s . c o m List<String> customBoxlets = profile.getCustomBoxlets(); if (customBoxlets != null) { boxlets = customBoxlets.toArray(new String[customBoxlets.size()]); } final String[] finalBoxlets = boxlets; if (jdbcTemplate.update(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection con) throws SQLException { PreparedStatement st = con .prepareStatement("UPDATE user_settings SET settings=?, main=? WHERE id=?"); st.setObject(1, profile.getSettings()); if (finalBoxlets != null) { st.setArray(2, con.createArrayOf("text", finalBoxlets)); } else { st.setNull(2, Types.ARRAY); } st.setInt(3, user.getId()); return st; } }) == 0) { jdbcTemplate.update(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection con) throws SQLException { PreparedStatement st = con .prepareStatement("INSERT INTO user_settings (id, settings, main) VALUES (?,?,?)"); st.setInt(1, user.getId()); st.setObject(2, profile.getSettings()); if (finalBoxlets != null) { st.setArray(3, con.createArrayOf("text", finalBoxlets)); } else { st.setNull(3, Types.ARRAY); } return st; } }); } }
From source file:de.whs.poodle.repositories.McWorksheetRepository.java
public int createMcWorksheet(CreateMcWorksheetForm form, int studentId, int courseTermId) { return jdbc.query(con -> { PreparedStatement ps = con.prepareStatement("SELECT * FROM generate_student_mc_worksheet(?,?,?,?,?)"); ps.setInt(1, courseTermId);//from w ww.j a va 2 s . c o m ps.setInt(2, studentId); Array tagsArray = con.createArrayOf("int4", ObjectUtils.toObjectArray(form.getTags())); ps.setArray(3, tagsArray); ps.setInt(4, form.getMaximum()); ps.setBoolean(5, form.isIgnoreAlreadyAnswered()); return ps; }, new ResultSetExtractor<Integer>() { @Override public Integer extractData(ResultSet rs) throws SQLException, DataAccessException { if (!rs.next()) // no results -> generated worksheet had no questions return 0; return rs.getInt("id"); } }); }
From source file:de.whs.poodle.repositories.McWorksheetRepository.java
public int getCountForMcWorksheet(CreateMcWorksheetForm form, int studentId, int courseTermId) { return jdbc.query(con -> { PreparedStatement ps = con .prepareStatement("SELECT COUNT(*) AS count FROM get_mc_questions_for_worksheet(?,?,?,?,?)"); ps.setInt(1, courseTermId);//from www . j a v a2s .c o m ps.setInt(2, studentId); Array tagsArray = con.createArrayOf("int4", ObjectUtils.toObjectArray(form.getTags())); ps.setArray(3, tagsArray); ps.setInt(4, form.getMaximum()); ps.setBoolean(5, form.isIgnoreAlreadyAnswered()); return ps; }, new ResultSetExtractor<Integer>() { @Override public Integer extractData(ResultSet rs) throws SQLException, DataAccessException { rs.next(); return rs.getInt("count"); } }); }
From source file:de.whs.poodle.repositories.CourseRepository.java
public void create(Course course, String firstTermName) { try {/*from w w w.j a v a 2s .c o m*/ int id = jdbc.query(con -> { // the function creates the course and the first term (firstTermId) PreparedStatement ps = con.prepareStatement("SELECT * FROM create_course(?,?,?,?,?,?,?)"); ps.setInt(1, course.getInstructor().getId()); ps.setString(2, course.getName()); ps.setBoolean(3, course.getVisible()); if (course.getPassword().trim().isEmpty()) ps.setNull(4, Types.VARCHAR); else ps.setString(4, course.getPassword()); Array otherInstructors = con.createArrayOf("int4", course.getOtherInstructorsIds().toArray()); ps.setArray(5, otherInstructors); Array linkedCourses = con.createArrayOf("int4", course.getLinkedCoursesIds().toArray()); ps.setArray(6, linkedCourses); ps.setString(7, firstTermName); return ps; }, new ResultSetExtractor<Integer>() { @Override public Integer extractData(ResultSet rs) throws SQLException, DataAccessException { rs.next(); return rs.getInt(1); } }); course.setId(id); } catch (DuplicateKeyException e) { throw new BadRequestException(); } }
From source file:com.streamsets.pipeline.lib.jdbc.JdbcMultiRowRecordWriter.java
@SuppressWarnings("unchecked") private void processPartition(Connection connection, Multimap<Long, Record> partitions, Long partitionKey, List<OnRecordErrorException> errorRecords) throws SQLException, OnRecordErrorException { Collection<Record> partition = partitions.get(partitionKey); // Fetch the base insert query for this partition. SortedMap<String, String> columnsToParameters = getFilteredColumnsToParameters(getColumnsToParameters(), partition.iterator().next()); // put all the records in a queue for consumption LinkedList<Record> queue = new LinkedList<>(partition); // compute number of rows per batch if (columnsToParameters.isEmpty()) { throw new OnRecordErrorException(Errors.JDBCDEST_22); }//from www . j a v a 2 s . c o m int maxRowsPerBatch = maxPrepStmtParameters / columnsToParameters.size(); PreparedStatement statement = null; // parameters are indexed starting with 1 int paramIdx = 1; int rowCount = 0; while (!queue.isEmpty()) { // we're at the start of a batch. if (statement == null) { // instantiate the new statement statement = generatePreparedStatement(columnsToParameters, // the next batch will have either the max number of records, or however many are left. Math.min(maxRowsPerBatch, queue.size()), getTableName(), connection); } // process the next record into the current statement Record record = queue.removeFirst(); for (String column : columnsToParameters.keySet()) { Field field = record.get(getColumnsToFields().get(column)); Field.Type fieldType = field.getType(); Object value = field.getValue(); try { switch (fieldType) { case LIST: List<Object> unpackedList = unpackList((List<Field>) value); Array array = connection.createArrayOf(getSQLTypeName(fieldType), unpackedList.toArray()); statement.setArray(paramIdx, array); break; case DATE: case DATETIME: // Java Date types are not accepted by JDBC drivers, so we need to convert to java.sql.Date java.util.Date date = field.getValueAsDatetime(); statement.setObject(paramIdx, new java.sql.Date(date.getTime())); break; default: statement.setObject(paramIdx, value, getColumnType(column)); break; } } catch (SQLException e) { LOG.error(Errors.JDBCDEST_23.getMessage(), column, fieldType.toString(), e); throw new OnRecordErrorException(record, Errors.JDBCDEST_23, column, fieldType.toString()); } ++paramIdx; } rowCount++; // check if we've filled up the current batch if (rowCount == maxRowsPerBatch) { // time to execute the current batch statement.addBatch(); statement.executeBatch(); statement.close(); statement = null; // reset our counters rowCount = 0; paramIdx = 1; } } // check if there are any records left. this should occur whenever there isn't *exactly* maxRowsPerBatch records in // this partition. if (statement != null) { statement.addBatch(); statement.executeBatch(); statement.close(); } }
From source file:edu.harvard.i2b2.crc.dao.pdo.PdoQueryModifierDao.java
/** * Get concepts detail from concept code list * /*from ww w.ja va 2 s . co m*/ * @param conceptCdList * @param detailFlag * @param blobFlag * @param statusFlag * @return {@link PatientDataType.ConceptDimensionSet} * @throws I2B2DAOException */ public ModifierSet getModifierByModifierCd(List<String> modifierCdList, boolean detailFlag, boolean blobFlag, boolean statusFlag) throws I2B2DAOException { ModifierSet modifierDimensionSet = new ModifierSet(); log.debug("Size of input modifier cd list " + modifierCdList.size()); Connection conn = null; PreparedStatement query = null; String tempTableName = ""; try { conn = getDataSource().getConnection(); ModifierFactRelated modifierFactRelated = new ModifierFactRelated( buildOutputOptionType(detailFlag, blobFlag, statusFlag)); String selectClause = modifierFactRelated.getSelectClause(); String serverType = dataSourceLookup.getServerType(); if (serverType.equalsIgnoreCase(DAOFactoryHelper.ORACLE)) { // get oracle connection from jboss wrapped connection // Otherwise Jboss wrapped connection fails when using oracle // Arrays oracle.jdbc.driver.OracleConnection conn1 = null;// (oracle.jdbc.driver.OracleConnection) ((WrappedConnection) conn) // .getUnderlyingConnection(); String finalSql = "SELECT " + selectClause + " FROM " + getDbSchemaName() + "modifier_dimension modifier WHERE modifier.modifier_cd IN (SELECT * FROM TABLE (?))"; log.debug("Pdo modifier sql [" + finalSql + "]"); query = conn1.prepareStatement(finalSql); ArrayDescriptor desc = ArrayDescriptor.createDescriptor("QT_PDO_QRY_STRING_ARRAY", conn1); oracle.sql.ARRAY paramArray = new oracle.sql.ARRAY(desc, conn1, modifierCdList.toArray(new String[] {})); query.setArray(1, paramArray); } else if (serverType.equalsIgnoreCase(DAOFactoryHelper.SQLSERVER) || serverType.equalsIgnoreCase(DAOFactoryHelper.POSTGRESQL)) { log.debug("creating temp table"); java.sql.Statement tempStmt = conn.createStatement(); tempTableName = SQLServerFactRelatedQueryHandler.TEMP_PDO_INPUTLIST_TABLE; try { tempStmt.executeUpdate("drop table " + tempTableName); } catch (SQLException sqlex) { ; } uploadTempTable(tempStmt, tempTableName, modifierCdList); String finalSql = "SELECT " + selectClause + " FROM " + getDbSchemaName() + "modifier_dimension modifier WHERE modifier.modifier_cd IN (select distinct char_param1 FROM " + tempTableName + ") order by modifier_path"; log.debug("Executing [" + finalSql + "]"); query = conn.prepareStatement(finalSql); } ResultSet resultSet = query.executeQuery(); I2B2PdoFactory.ModifierBuilder modifierBuilder = new I2B2PdoFactory().new ModifierBuilder(detailFlag, blobFlag, statusFlag, dataSourceLookup.getServerType()); while (resultSet.next()) { ModifierType modifierDimensionType = modifierBuilder.buildModifierSet(resultSet); modifierDimensionSet.getModifier().add(modifierDimensionType); } } catch (SQLException sqlEx) { log.error("", sqlEx); throw new I2B2DAOException("", sqlEx); } catch (IOException ioEx) { log.error("", ioEx); throw new I2B2DAOException("", ioEx); } finally { if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.SQLSERVER)) { PdoTempTableUtil tempUtil = new PdoTempTableUtil(); tempUtil.deleteTempTableSqlServer(conn, tempTableName); } try { JDBCUtil.closeJdbcResource(null, query, conn); } catch (SQLException sqlEx) { sqlEx.printStackTrace(); } } return modifierDimensionSet; }
From source file:edu.harvard.i2b2.crc.dao.pdo.PdoQueryConceptDao.java
/** * Get concepts detail from concept code list * //from www. j av a 2 s.c o m * @param conceptCdList * @param detailFlag * @param blobFlag * @param statusFlag * @return {@link PatientDataType.ConceptDimensionSet} * @throws I2B2DAOException */ public ConceptSet getConceptByConceptCd(List<String> conceptCdList, boolean detailFlag, boolean blobFlag, boolean statusFlag) throws I2B2DAOException { ConceptSet conceptDimensionSet = new ConceptSet(); log.debug("Size of input concept cd list " + conceptCdList.size()); Connection conn = null; PreparedStatement query = null; String tempTableName = ""; try { conn = getDataSource().getConnection(); ConceptFactRelated conceptFactRelated = new ConceptFactRelated( buildOutputOptionType(detailFlag, blobFlag, statusFlag)); String selectClause = conceptFactRelated.getSelectClause(); String serverType = dataSourceLookup.getServerType(); if (serverType.equalsIgnoreCase(DAOFactoryHelper.ORACLE)) { // get oracle connection from jboss wrapped connection // Otherwise Jboss wrapped connection fails when using oracle // Arrays oracle.jdbc.driver.OracleConnection conn1 = null; //(oracle.jdbc.driver.OracleConnection) ((WrappedConnection) conn) // .getUnderlyingConnection(); String finalSql = "SELECT " + selectClause + " FROM " + getDbSchemaName() + "concept_dimension concept WHERE concept.concept_cd IN (SELECT * FROM TABLE (?))"; log.debug("Pdo Concept sql [" + finalSql + "]"); query = conn1.prepareStatement(finalSql); ArrayDescriptor desc = ArrayDescriptor.createDescriptor("QT_PDO_QRY_STRING_ARRAY", conn1); oracle.sql.ARRAY paramArray = new oracle.sql.ARRAY(desc, conn1, conceptCdList.toArray(new String[] {})); query.setArray(1, paramArray); } else if (serverType.equalsIgnoreCase(DAOFactoryHelper.SQLSERVER) || serverType.equalsIgnoreCase(DAOFactoryHelper.POSTGRESQL)) { log.debug("creating temp table"); java.sql.Statement tempStmt = conn.createStatement(); tempTableName = SQLServerFactRelatedQueryHandler.TEMP_PDO_INPUTLIST_TABLE; try { tempStmt.executeUpdate("drop table " + tempTableName); } catch (SQLException sqlex) { ; } uploadTempTable(tempStmt, tempTableName, conceptCdList); String finalSql = "SELECT " + selectClause + " FROM " + getDbSchemaName() + "concept_dimension concept WHERE concept.concept_cd IN (select distinct char_param1 FROM " + tempTableName + ") order by concept_path"; log.debug("Executing [" + finalSql + "]"); query = conn.prepareStatement(finalSql); } ResultSet resultSet = query.executeQuery(); I2B2PdoFactory.ConceptBuilder conceptBuilder = new I2B2PdoFactory().new ConceptBuilder(detailFlag, blobFlag, statusFlag, dataSourceLookup.getServerType()); while (resultSet.next()) { ConceptType conceptDimensionType = conceptBuilder.buildConceptSet(resultSet); conceptDimensionSet.getConcept().add(conceptDimensionType); } } catch (SQLException sqlEx) { log.error("", sqlEx); throw new I2B2DAOException("", sqlEx); } catch (IOException ioEx) { log.error("", ioEx); throw new I2B2DAOException("", ioEx); } finally { if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.SQLSERVER)) { PdoTempTableUtil tempUtil = new PdoTempTableUtil(); tempUtil.deleteTempTableSqlServer(conn, tempTableName); } try { JDBCUtil.closeJdbcResource(null, query, conn); } catch (SQLException sqlEx) { sqlEx.printStackTrace(); } } return conceptDimensionSet; }
From source file:com.viettel.ttbankplus.servicegw.hibernate.dao.transaction.TransCPDAO.java
public List<Transaction> getTrans(String where, HashMap param) { List lst = new ArrayList(); try {/*from w ww .j ava 2s. c o m*/ String sql = "select cp.trans_id transId, cp.content_provider_id contentproviderid, to_char(cp.request_date, 'dd/MM/yyyy hh24:mi:ss') requestDate, " + "(case nvl(bp.error_code,'null')\n" + "when '00' then TO_CHAR(2)\n" + "when '32' then \n" + "(case nvl(bp.correct_code,'null')\n" + "when '00' then TO_CHAR(2)\n" + "when 'null' then TO_CHAR(1)\n" + "else TO_CHAR(4) end)\n" + "when 'null' then\n" + "(case nvl(bp.correct_code,'null')\n" + "when '00' then TO_CHAR(2)\n" + "when 'null' then TO_CHAR(1)\n" + "else TO_CHAR(4) end\n" + ")\n" + "when 'null' then\n" + "(case nvl(bp.correct_code,'null')\n" + "when '00' then TO_CHAR(2)\n" + "when 'null' then TO_CHAR(1)\n" + "else TO_CHAR(4) end\n" + ")\n" + "else TO_CHAR(4)\n" + "end\n" + ")" + " transStatus, " + "cp.confirm_status confirmStatus, cp.billing_code billingCode, cp.order_id orderId, cp.amount, " + "cp.order_info orderInfo, cp.trans_type transType, bp.bank_code bankCode, bp.cp_code cpCode, bp.msisdn, p.cp_name cpName, bp.customer_name customerName, bp.error_code || bp.correct_code errorCode from trans_cp cp join trans_bankplus bp on cp.trans_cp_id = bp.trans_cp_id " + " join content_provider p on p.content_provider_id = cp.content_provider_id "; // sql += where; // sql += " order by cp.request_date desc"; // log.debug(sql); //Rebuild where param int numparam = StringUtils.countMatches(where, ":"); // log.info("NUM PARAM: " + numparam); ArrayList<Object> arrParam = new ArrayList<Object>(); arrParam.add(0, ""); for (int i = 0; i < numparam; i++) { for (Object object : param.keySet()) { String key = object.toString(); Object val = param.get(key); int index = where.indexOf(":"); int indexCheck = where.indexOf(":" + key); if (index == indexCheck) { if (val instanceof ArrayList) { ArrayList arr = (ArrayList) val; String add = ""; for (int j = 0; j < arr.size(); j++) { arrParam.add(arr.get(j)); add += ",?"; } add = add.substring(1); where = where.substring(0, index) + add + where.substring(index + (":" + key).length()); } else if (val instanceof Date) { Date d = (Date) val; String date = new SimpleDateFormat("dd/MM/yyyy HH-mm-ss").format(d); arrParam.add(date); where = where.substring(0, index) + "to_date(?,'dd/MM/yyyy hh24-mi-ss')" + where.substring(index + (":" + key).length()); } else { arrParam.add(val); where = where.substring(0, index) + "?" + where.substring(index + (":" + key).length()); } // arrParam.add(val); // where = where.substring(0, index) + "?" + where.substring(index + (":" + key).length()); break; } } } numparam = arrParam.size() - 1; sql += where; sql += " order by cp.request_date desc"; // log.debug(sql); log.info("WHERE CLAUSE: " + where); log.info("LIST PARAM VALUE: " + arrParam); Session sess = DAOFactory.getNewSession(); // SQLQuery query = sess.createSQLQuery(sql); Connection conn = sess.connection(); PreparedStatement pstm = null; ResultSet rs = null; try { pstm = conn.prepareStatement(sql); for (int i = 0; i < numparam; i++) { Object objVal = arrParam.get(i + 1); if (objVal instanceof ArrayList) { ArrayList arrlist = (ArrayList) objVal; java.sql.Array sqlArray = null; // oracle.jdbc.OracleConnection oracleConnection = conn.unwrap(OracleConnection.class); if (arrlist.get(0) instanceof String) { // sqlArray = oracleConnection.createArrayOf("VARCHAR", arrlist.toArray()); // sqlArray = conn.createArrayOf("VARCHAR", arrlist.toArray()); } else { // sqlArray = conn.createArrayOf("NUMERIC", arrlist.toArray()); // sqlArray = oracleConnection.createArrayOf("INTEGER", arrlist.toArray()); } pstm.setArray(i + 1, sqlArray); } else if (objVal instanceof String) { pstm.setString(i + 1, objVal.toString()); // java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime()); // log.info("set string: " + (i + 1) + " - " + objVal.toString()); } else if (objVal instanceof Date) { Date d = (Date) objVal; // String date = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss").format(d); java.sql.Timestamp sqlDate = new java.sql.Timestamp(d.getTime()); // log.info("set date: " + (i + 1) + " - " + sqlDate); pstm.setTimestamp(i + 1, sqlDate); // pstm.setString(i + 1, date); } else { pstm.setLong(i + 1, Long.parseLong(objVal.toString())); // log.info("set long: " + (i + 1) + " - " + Long.parseLong(objVal.toString())); } } // log.info("PREP: " + pstm.toString()); rs = pstm.executeQuery(); ResultSetMetaData rsmd = rs.getMetaData(); int numCol = rsmd.getColumnCount(); String[] arrCol = new String[numCol]; String coltemp = ""; for (int i = 0; i < numCol; i++) { arrCol[i] = rsmd.getColumnName(i + 1); coltemp += rsmd.getColumnName(i + 1) + "#"; } // log.info("CCCCCC:" + coltemp); while (rs.next()) { // log.info("AAAAAAAAAAAAA:" + rs.getString(1)); Transaction trans = new Transaction(); for (int i = 0; i < numCol; i++) { // String data = rs.getString(arrCol[i]); Object data = rs.getObject(arrCol[i]); if (data != null) { callSetFunction(trans, "set" + arrCol[i], data); } } lst.add(trans); } } catch (Exception ex) { log.error("", ex); } finally { // log.info("================>finally"); if (rs != null) { rs.close(); } if (pstm != null) { pstm.close(); } if (sess != null) { sess.close(); } } // query.setResultTransformer(Transformers.aliasToBean(Transaction.class)); // for (Object object : param.keySet()) { // String key = object.toString(); // Object val = param.get(key); // if (val instanceof ArrayList) { //For select in // query.setParameterList(key, (ArrayList) val); // } else { // query.setParameter(key, param.get(key)); // } // } // log.info(query.toString()); // lst = query.list(); // ScrollableResults resultset = query.scroll(ScrollMode.FORWARD_ONLY); // resultset.beforeFirst(); // while (resultset.next()) { // Object[] objres = resultset.get(); // log.info(objres); // } // resultset.close(); } catch (Exception ex) { log.error("getTrans: ", ex); } finally { // DAOFactory.commitCurrentSessions(); } return lst; }