Java tutorial
/* * Serposcope - SEO rank checker https://serposcope.serphacker.com/ * * Copyright (c) 2016 SERP Hacker * @author Pierre Nogues <support@serphacker.com> * @license https://opensource.org/licenses/MIT MIT License */ package com.serphacker.serposcope.db.base; import com.google.common.io.ByteStreams; import com.serphacker.serposcope.db.AbstractDB; import static com.serphacker.serposcope.db.base.MigrationDB.TABLES; import java.io.BufferedReader; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.io.OutputStreamWriter; import java.io.PrintWriter; import java.io.Reader; import java.io.Writer; import java.nio.charset.Charset; import java.sql.Blob; import java.sql.Clob; import java.sql.Connection; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.util.zip.GZIPOutputStream; import javax.inject.Inject; import org.apache.commons.lang3.StringEscapeUtils; import org.apache.commons.lang3.time.DurationFormatUtils; public class ExportDB extends AbstractDB { @Inject MigrationDB migrationDB; public final static int DEFAULT_MAX_ALLOWED_PACKET = 4194304 / 2; // public final static int DEFAULT_MAX_ALLOWED_PACKET = 4194304; public boolean export(String path) throws Exception { OutputStream os = new FileOutputStream(path, false); if (path.endsWith(".gz")) { os = new GZIPOutputStream(os); } try (OutputStreamWriter osw = new OutputStreamWriter(os, Charset.forName("UTF-8")); PrintWriter writer = new PrintWriter(osw);) { return export(writer); } } public boolean importStream(BufferedReader reader) throws SQLException, IOException, Exception { String line = null; try (Connection con = ds.getConnection()) { while ((line = reader.readLine()) != null) { try (Statement stmt = con.createStatement()) { if (line.isEmpty() || line.startsWith("--") || line.startsWith("#")) { continue; } stmt.executeUpdate(line); } } } migrationDB.migrateIfNeeded(); return true; } // default max_allowed_packet = 4194304 public boolean export(Writer writer) throws IOException { for (String resource : MigrationDB.DB_SCHEMA_FILES) { String sql = new String(ByteStreams.toByteArray(MigrationDB.class.getResourceAsStream(resource))); sql = sql.replaceAll("--.*\n", "\n"); sql = sql.replaceAll("\\s+", " "); sql = sql.replaceAll(";\\s*", ";\n"); writer.append(sql); writer.append("\n"); } writer.append("\nSET FOREIGN_KEY_CHECKS=0;\n"); try (Connection con = ds.getConnection()) { for (String table : TABLES) { writer.flush(); try (Statement stmt = con.createStatement()) { LOG.info("exporting table {}", table); long _start = System.currentTimeMillis(); stmt.setQueryTimeout(3600 * 24); ResultSet rs = stmt.executeQuery("SELECT * FROM `" + table + "`"); ResultSetMetaData metaData = rs.getMetaData(); int columns = metaData.getColumnCount(); String insertStatement = "INSERT INTO `" + table + "` VALUES "; StringBuilder stmtBuilder = new StringBuilder(insertStatement); while (rs.next()) { StringBuilder entryBuilder = new StringBuilder("("); for (int colIndex = 1; colIndex <= columns; colIndex++) { Object object = rs.getObject(colIndex); String colName = metaData.getColumnName(colIndex); String colClassName = metaData.getColumnClassName(colIndex); String escaped = escape(object, colClassName, colName); entryBuilder.append(escaped); if (colIndex < columns) { entryBuilder.append(','); } } entryBuilder.append("),"); if (stmtBuilder.length() != insertStatement.length() && stmtBuilder.length() + entryBuilder.length() > DEFAULT_MAX_ALLOWED_PACKET) { stmtBuilder.setCharAt(stmtBuilder.length() - 1, ';'); writer.append(stmtBuilder).append('\n'); stmtBuilder = new StringBuilder(insertStatement); } stmtBuilder.append(entryBuilder); } if (stmtBuilder.length() != insertStatement.length()) { stmtBuilder.setCharAt(stmtBuilder.length() - 1, ';'); writer.append(stmtBuilder).append('\n'); } LOG.info("exported table {} in {}", table, DurationFormatUtils.formatDurationHMS(System.currentTimeMillis() - _start)); } } writer.append("SET FOREIGN_KEY_CHECKS=1;\n"); } catch (Exception ex) { LOG.error("SQL error", ex); return false; } return true; } protected String escape(Object colVal, String className, String colName) throws Exception { if (colVal == null) { return "NULL"; } switch (className) { case "java.lang.String": return "'" + escapeString((String) colVal) + "'"; case "java.sql.Clob": return "'" + clobToStringEscaped((Clob) colVal) + "'"; case "java.sql.Blob": return blobToString((Blob) colVal); case "[B": return blobToString((byte[]) colVal); case "java.lang.Boolean": return (Boolean) colVal ? "1" : "0"; case "java.lang.Integer": case "java.lang.Short": case "java.lang.Long": case "java.lang.Byte": return colVal.toString(); case "java.sql.Date": case "java.sql.Timestamp": return "'" + colVal.toString() + "'"; default: throw new UnsupportedOperationException( "escaping not implemented for class " + className + " of column " + colName); } } protected String escapeString(String str) { StringBuilder builder = new StringBuilder(); for (int i = 0; i < str.length(); i++) { sbEscape(builder, str.charAt(i)); } return builder.toString(); } protected String clobToStringEscaped(java.sql.Clob data) throws Exception { final StringBuilder builder = new StringBuilder(); try (final Reader reader = data.getCharacterStream(); final BufferedReader br = new BufferedReader(reader);) { int b; while (-1 != (b = br.read())) { sbEscape(builder, (char) b); } } return builder.toString(); } protected StringBuilder sbEscape(StringBuilder builder, char c) { switch (c) { case '\'': builder.append('\''); builder.append(c); break; case '\0': case '\r': case '\n': case '\t': case '\b': builder.append(' '); break; default: builder.append(c); } return builder; } protected String blobToString(Blob data) throws Exception { final StringBuilder sb = new StringBuilder("X'"); int b; try (InputStream br = data.getBinaryStream()) { while (-1 != (b = br.read())) { if (b < 0x10) { sb.append('0'); } sb.append(Integer.toString(b, 16)); } sb.append("'"); } return sb.toString(); } protected String blobToString(byte[] blob) { final StringBuilder sb = new StringBuilder("X'"); for (int i = 0; i < blob.length; i++) { int b = (blob[i] & 0xFF); if (b < 0x10) { sb.append('0'); } sb.append(Integer.toString(b, 16)); } sb.append("'"); return sb.toString(); } }