List of usage examples for java.sql Blob getBinaryStream
java.io.InputStream getBinaryStream() throws SQLException;
From source file:org.openmrs.module.sync.api.db.hibernate.HibernateSyncDAO.java
public void exportChildDB(String uuidForChild, OutputStream os) throws DAOException { PrintStream out = new PrintStream(os); Set<String> tablesToSkip = new HashSet<String>(); {/*from w w w. j a v a 2 s . c o m*/ tablesToSkip.add("hl7_in_archive"); tablesToSkip.add("hl7_in_queue"); tablesToSkip.add("hl7_in_error"); tablesToSkip.add("formentry_archive"); tablesToSkip.add("formentry_queue"); tablesToSkip.add("formentry_error"); tablesToSkip.add("sync_class"); tablesToSkip.add("sync_import"); tablesToSkip.add("sync_record"); tablesToSkip.add("sync_server"); tablesToSkip.add("sync_server_class"); tablesToSkip.add("sync_server_record"); // TODO: figure out which other tables to skip // tablesToSkip.add("obs"); // tablesToSkip.add("concept"); // tablesToSkip.add("patient"); } List<String> tablesToDump = new ArrayList<String>(); Session session = sessionFactory.getCurrentSession(); String schema = (String) session.createSQLQuery("SELECT schema()").uniqueResult(); log.warn("schema: " + schema); // Get all tables that we'll need to dump { Query query = session.createSQLQuery( "SELECT tabs.table_name FROM INFORMATION_SCHEMA.TABLES tabs WHERE tabs.table_schema = '" + schema + "'"); for (Object tn : query.list()) { String tableName = (String) tn; if (!tablesToSkip.contains(tableName.toLowerCase())) tablesToDump.add(tableName); } } log.warn("tables to dump: " + tablesToDump); String thisServerGuid = getGlobalProperty(SyncConstants.PROPERTY_SERVER_UUID); // Write the DDL Header as mysqldump does { out.println("-- ------------------------------------------------------"); out.println("-- Database dump to create an openmrs child server"); out.println("-- Schema: " + schema); out.println("-- Parent GUID: " + thisServerGuid); out.println("-- Parent version: " + OpenmrsConstants.OPENMRS_VERSION); out.println("-- ------------------------------------------------------"); out.println(""); out.println("/*!40101 SET CHARACTER_SET_CLIENT=utf8 */;"); out.println("/*!40101 SET NAMES utf8 */;"); out.println("/*!40103 SET TIME_ZONE='+00:00' */;"); out.println("/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;"); out.println("/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;"); out.println("/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;"); out.println("/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;"); out.println("/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;"); out.println("/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;"); out.println("/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;"); out.println("/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;"); out.println(""); } try { // JDBC way of doing this // Connection conn = // DriverManager.getConnection("jdbc:mysql://localhost/" + schema, // "test", "test"); Connection conn = sessionFactory.getCurrentSession().connection(); try { Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); // Get the create database statement ResultSet rs = st.executeQuery("SHOW CREATE DATABASE " + schema); for (String tableName : tablesToDump) { out.println(); out.println("--"); out.println("-- Table structure for table `" + tableName + "`"); out.println("--"); out.println("DROP TABLE IF EXISTS `" + tableName + "`;"); out.println("SET @saved_cs_client = @@character_set_client;"); out.println("SET character_set_client = utf8;"); rs = st.executeQuery("SHOW CREATE TABLE " + tableName); while (rs.next()) { out.println(rs.getString("Create Table") + ";"); } out.println("SET character_set_client = @saved_cs_client;"); out.println(); { out.println("-- Dumping data for table `" + tableName + "`"); out.println("LOCK TABLES `" + tableName + "` WRITE;"); out.println("/*!40000 ALTER TABLE `" + tableName + "` DISABLE KEYS */;"); boolean first = true; rs = st.executeQuery("select * from " + tableName); ResultSetMetaData md = rs.getMetaData(); int numColumns = md.getColumnCount(); int rowNum = 0; boolean insert = false; while (rs.next()) { if (rowNum == 0) { insert = true; out.print("INSERT INTO `" + tableName + "` VALUES "); } ++rowNum; if (first) { first = false; } else { out.print(", "); } if (rowNum % 20 == 0) { out.println(); } out.print("("); for (int i = 1; i <= numColumns; ++i) { if (i != 1) { out.print(","); } if (rs.getObject(i) == null) { out.print("NULL"); } else { switch (md.getColumnType(i)) { case Types.VARCHAR: case Types.CHAR: case Types.LONGVARCHAR: out.print("'"); out.print( rs.getString(i).replaceAll("\n", "\\\\n").replaceAll("'", "\\\\'")); out.print("'"); break; case Types.BIGINT: case Types.DECIMAL: case Types.NUMERIC: out.print(rs.getBigDecimal(i)); break; case Types.BIT: out.print(rs.getBoolean(i)); break; case Types.INTEGER: case Types.SMALLINT: case Types.TINYINT: out.print(rs.getInt(i)); break; case Types.REAL: case Types.FLOAT: case Types.DOUBLE: out.print(rs.getDouble(i)); break; case Types.BLOB: case Types.VARBINARY: case Types.LONGVARBINARY: Blob blob = rs.getBlob(i); out.print("'"); InputStream in = blob.getBinaryStream(); while (true) { int b = in.read(); if (b < 0) { break; } char c = (char) b; if (c == '\'') { out.print("\'"); } else { out.print(c); } } out.print("'"); break; case Types.CLOB: out.print("'"); out.print( rs.getString(i).replaceAll("\n", "\\\\n").replaceAll("'", "\\\\'")); out.print("'"); break; case Types.DATE: out.print("'" + rs.getDate(i) + "'"); break; case Types.TIMESTAMP: out.print("'" + rs.getTimestamp(i) + "'"); break; default: throw new RuntimeException("TODO: handle type code " + md.getColumnType(i) + " (name " + md.getColumnTypeName(i) + ")"); } } } out.print(")"); } if (insert) { out.println(";"); insert = false; } out.println("/*!40000 ALTER TABLE `" + tableName + "` ENABLE KEYS */;"); out.println("UNLOCK TABLES;"); out.println(); } } } finally { conn.close(); } // Now we mark this as a child out.println("-- Now mark this as a child database"); if (uuidForChild == null) uuidForChild = SyncUtil.generateUuid(); out.println("update global_property set property_value = '" + uuidForChild + "' where property = '" + SyncConstants.PROPERTY_SERVER_UUID + "';"); // Write the footer of the DDL script { out.println("/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;"); out.println("/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;"); out.println("/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;"); out.println("/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;"); out.println("/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;"); out.println("/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;"); out.println("/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;"); out.println("/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;"); } out.flush(); out.close(); } catch (IOException ex) { log.error("IOException", ex); } catch (SQLException ex) { log.error("SQLException", ex); } }
From source file:com.turborep.turbotracker.banking.service.BankingServiceImpl.java
@Override public void printCheckDetails(HttpServletResponse theResponse, HttpServletRequest theRequest) throws BankingException { Session printBeanSession = itsSessionFactory.openSession(); SessionFactoryImplementor sessionFactoryImplementation = (SessionFactoryImplementor) printBeanSession .getSessionFactory();//from w w w .j a v a2 s .com ConnectionProvider connectionProvider = sessionFactoryImplementation.getConnectionProvider(); Map<String, Object> params = new HashMap<String, Object>(); //Rxaddress address = null; Connection connection = null; InputStream imageStream = null; try { Integer addressID = rxAddressID[0]; //address = (Rxaddress)printBeanSession.get(Rxaddress.class,addressID); params.put("Name", ""); params.put("Address2", ""); params.put("Address3", ""); itsLogger.info(billId.length); String count = billId.length + ""; params.put("BillCount", count); params.put("CheckDate", checkDate); TsUserSetting objtsusersettings = (TsUserSetting) printBeanSession.get(TsUserSetting.class, 1); Blob blob = objtsusersettings.getCompanyLogo(); imageStream = blob.getBinaryStream(); //params.put("companyLogo", imageStream); itsLogger.info("Check Date: " + checkDate); ServletOutputStream out = theResponse.getOutputStream(); String fileName = theRequest.getParameter("fileName"); theResponse.setHeader("Content-Disposition", "attachment; filename=" + fileName); theResponse.setContentType("application/pdf"); connection = connectionProvider.getConnection(); String path_JRXML = theRequest.getSession().getServletContext() .getRealPath("/resources/jasper_reports/PrintChecksfinal.jrxml"); //String path_JRXML = theRequest.getSession().getServletContext().getRealPath("/resources/jasper_reports/CheckMICR.jrxml"); JasperReport report = JasperCompileManager.compileReport(path_JRXML); JasperPrint print = JasperFillManager.fillReport(report, params, connection); ByteArrayOutputStream baos = new ByteArrayOutputStream(); JasperExportManager.exportReportToPdfStream(print, baos); out.write(baos.toByteArray()); out.flush(); out.close(); } catch (Exception e) { itsLogger.error(e.getMessage(), e); BankingException aBankingException = new BankingException(e.getMessage(), e); } finally { try { if (imageStream != null) { imageStream.close(); } if (connectionProvider != null) { connectionProvider.closeConnection(connection); connectionProvider = null; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } printBeanSession.flush(); printBeanSession.close(); } }
From source file:nl.nn.adapterframework.util.JdbcUtil.java
public static InputStream getBlobInputStream(Blob blob, String column) throws SQLException, JdbcException { if (blob == null) { throw new JdbcException("no blob found in column [" + column + "]"); }/*from ww w . ja v a 2 s .c om*/ return blob.getBinaryStream(); }
From source file:nl.nn.adapterframework.util.JdbcUtil.java
public static String getBlobAsString(Blob blob, String column, String charset, boolean xmlEncode, boolean blobIsCompressed, boolean blobSmartGet, boolean encodeBlobBase64) throws IOException, JdbcException, SQLException, JMSException { if (encodeBlobBase64) { InputStream blobStream = JdbcUtil.getBlobInputStream(blob, column, blobIsCompressed); return Misc.streamToString(new Base64InputStream(blobStream, true), null, false); }//from w w w . j a v a 2s . c om if (blobSmartGet) { if (blob == null) { log.debug("no blob found in column [" + column + "]"); return null; } int bl = (int) blob.length(); InputStream is = blob.getBinaryStream(); byte[] buf = new byte[bl]; int bl1 = is.read(buf); Inflater decompressor = new Inflater(); decompressor.setInput(buf); ByteArrayOutputStream bos = new ByteArrayOutputStream(buf.length); byte[] bufDecomp = new byte[1024]; boolean decompresOK = true; while (!decompressor.finished()) { try { int count = decompressor.inflate(bufDecomp); if (count == 0) { break; } bos.write(bufDecomp, 0, count); } catch (DataFormatException e) { log.debug("message in column [" + column + "] is not compressed"); decompresOK = false; break; } } bos.close(); if (decompresOK) buf = bos.toByteArray(); Object result = null; ObjectInputStream ois = null; boolean objectOK = true; try { ByteArrayInputStream bis = new ByteArrayInputStream(buf); ois = new ObjectInputStream(bis); result = ois.readObject(); } catch (Exception e) { log.debug("message in column [" + column + "] is probably not a serialized object: " + e.getClass().getName()); objectOK = false; } if (ois != null) ois.close(); String rawMessage; if (objectOK) { if (result instanceof IMessageWrapper) { rawMessage = ((IMessageWrapper) result).getText(); } else if (result instanceof TextMessage) { rawMessage = ((TextMessage) result).getText(); } else { rawMessage = (String) result; } } else { rawMessage = new String(buf, charset); } String message = XmlUtils.encodeCdataString(rawMessage); return message; } return Misc.readerToString(getBlobReader(blob, column, charset, blobIsCompressed), null, xmlEncode); }
From source file:nz.co.gregs.dbvolution.datatypes.DBByteArray.java
private byte[] getFromBLOB(ResultSet resultSet, String fullColumnName) throws SQLException { byte[] bytes = new byte[] {}; Blob blob = resultSet.getBlob(fullColumnName); if (resultSet.wasNull()) { blob = null;//from ww w. ja va 2 s. c o m } if (blob == null) { this.setToNull(); } else { InputStream inputStream = blob.getBinaryStream(); bytes = getBytesFromInputStream(inputStream); } return bytes; }
From source file:org.agnitas.util.AgnUtils.java
private static byte[] toByteArrayImpl(Blob fromBlob, ByteArrayOutputStream baos) throws SQLException, IOException { byte[] buf = new byte[4000]; InputStream is = fromBlob.getBinaryStream(); try {//from w ww .j a va 2 s. c om for (;;) { int dataSize = is.read(buf); if (dataSize == -1) break; baos.write(buf, 0, dataSize); } } finally { if (is != null) { try { is.close(); } catch (IOException ex) { logger.error("toByteArrayImpl", ex); } } } return baos.toByteArray(); }
From source file:org.apache.ambari.server.orm.DBAccessorImpl.java
private void convertUpdateData(ResultSet rs, DBColumnInfo columnNameFrom, int typeFrom, DBColumnInfo columnNameTo, int typeTo) throws SQLException { if (typeFrom == Types.BLOB && typeTo == Types.CLOB) { //BLOB-->CLOB Blob data = rs.getBlob(columnNameFrom.getName()); if (data != null) { rs.updateClob(columnNameTo.getName(), new BufferedReader(new InputStreamReader(data.getBinaryStream()))); }//from ww w. ja v a 2 s .c om } else { Object data = rs.getObject(columnNameFrom.getName()); rs.updateObject(columnNameTo.getName(), data); } }
From source file:org.apache.gora.sql.store.SqlStore.java
protected byte[] getBytes(ResultSet resultSet, int columnIndex, Schema schema, Column column) throws SQLException, IOException { switch (column.getJdbcType()) { case BLOB:// w w w . j a v a2 s .c o m Blob blob = resultSet.getBlob(columnIndex); return IOUtils.readFully(blob.getBinaryStream()); case BINARY: case VARBINARY: return resultSet.getBytes(columnIndex); case LONGVARBINARY: return IOUtils.readFully(resultSet.getBinaryStream(columnIndex)); } return null; }
From source file:org.apache.gora.sql.store.SqlStore.java
protected Object readField(ResultSet resultSet, int columnIndex, Object field, Schema schema, Column column) throws SQLException, IOException { InputStream is = null;//from www . j a v a 2 s. c o m byte[] bytes = null; JdbcType type = JdbcType.get(resultSet.getMetaData().getColumnType(columnIndex)); switch (type) { case BLOB: Blob blob = resultSet.getBlob(columnIndex); if (blob != null) is = blob.getBinaryStream(); break; case BINARY: case VARBINARY: bytes = resultSet.getBytes(columnIndex); break; case LONGVARBINARY: is = resultSet.getBinaryStream(columnIndex); break; } if (bytes != null) return IOUtils.deserialize(bytes, datumReader, schema, field); else if (is != null) return IOUtils.deserialize(is, datumReader, schema, field); return field; // field is empty }
From source file:org.apache.jackrabbit.core.persistence.bundle.BundleDbPersistenceManager.java
/** * {@inheritDoc}//from w ww . j a va 2 s . c o m */ public void checkConsistency(String[] uuids, boolean recursive, boolean fix) { log.info("{}: checking workspace consistency...", name); int count = 0; int total = 0; Collection modifications = new ArrayList(); if (uuids == null) { // get all node bundles in the database with a single sql statement, // which is (probably) faster than loading each bundle and traversing the tree ResultSet rs = null; DataInputStream din = null; try { String sql; if (getStorageModel() == SM_BINARY_KEYS) { sql = "select NODE_ID, BUNDLE_DATA from " + schemaObjectPrefix + "BUNDLE"; } else { sql = "select NODE_ID_HI, NODE_ID_LO, BUNDLE_DATA from " + schemaObjectPrefix + "BUNDLE"; } Statement stmt = connectionManager.executeStmt(sql, new Object[0]); rs = stmt.getResultSet(); // iterate over all nodebundles in the db while (rs.next()) { NodeId id; Blob blob; if (getStorageModel() == SM_BINARY_KEYS) { id = new NodeId(new UUID(rs.getBytes(1))); blob = rs.getBlob(2); } else { id = new NodeId(new UUID(rs.getLong(1), rs.getLong(2))); blob = rs.getBlob(3); } din = new DataInputStream(blob.getBinaryStream()); try { // parse and check bundle // check bundle will log any problems itself if (binding.checkBundle(din)) { // reset stream for readBundle() din = new DataInputStream(blob.getBinaryStream()); NodePropBundle bundle = binding.readBundle(din, id); checkBundleConsistency(id, bundle, fix, modifications); } else { log.error("invalid bundle '" + id + "', see previous BundleBinding error log entry"); } } catch (Exception e) { log.error("Error in bundle " + id + ": " + e); } count++; if (count % 1000 == 0) { log.info(name + ": checked " + count + " bundles..."); } } } catch (Exception e) { log.error("Error loading bundle", e); } finally { IOUtils.closeQuietly(din); closeResultSet(rs); total = count; } } else { // check only given uuids, handle recursive flag // 1) convert uuid array to modifiable list // 2) for each uuid do // a) load node bundle // b) check bundle, store any bundle-to-be-modified in collection // c) if recursive, add child uuids to list of uuids List uuidList = new ArrayList(uuids.length); // convert uuid string array to list of UUID objects for (int i = 0; i < uuids.length; i++) { try { uuidList.add(new UUID(uuids[i])); } catch (IllegalArgumentException e) { log.error("Invalid uuid for consistency check, skipping: '" + uuids[i] + "': " + e); } } // iterate over UUIDs (including ones that are newly added inside the loop!) for (int i = 0; i < uuidList.size(); i++) { final UUID uuid = (UUID) uuidList.get(i); try { // load the node from the database NodeId id = new NodeId(uuid); NodePropBundle bundle = loadBundle(id, true); if (bundle == null) { log.error("No bundle found for uuid '" + uuid + "'"); continue; } checkBundleConsistency(id, bundle, fix, modifications); if (recursive) { Iterator iter = bundle.getChildNodeEntries().iterator(); while (iter.hasNext()) { NodePropBundle.ChildNodeEntry entry = (NodePropBundle.ChildNodeEntry) iter.next(); uuidList.add(entry.getId().getUUID()); } } count++; if (count % 1000 == 0) { log.info(name + ": checked " + count + "/" + uuidList.size() + " bundles..."); } } catch (ItemStateException e) { // problem already logged (loadBundle called with logDetailedErrors=true) } } total = uuidList.size(); } // repair collected broken bundles if (consistencyFix && !modifications.isEmpty()) { log.info(name + ": Fixing " + modifications.size() + " inconsistent bundle(s)..."); Iterator iterator = modifications.iterator(); while (iterator.hasNext()) { NodePropBundle bundle = (NodePropBundle) iterator.next(); try { log.info(name + ": Fixing bundle '" + bundle.getId() + "'"); bundle.markOld(); // use UPDATE instead of INSERT storeBundle(bundle); } catch (ItemStateException e) { log.error(name + ": Error storing fixed bundle: " + e); } } } log.info(name + ": checked " + count + "/" + total + " bundles."); }