Java tutorial
/* * Copyright (C) 2015 coastland * * Licensed under the Apache License, Version 2.0 (the "License"); * You may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package jp.co.tis.gsp.tools.dba.dialect; import java.io.BufferedInputStream; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Timestamp; import java.sql.Types; import java.util.Map; import org.apache.commons.io.IOUtils; import org.apache.maven.plugin.MojoExecutionException; import org.seasar.extension.jdbc.gen.dialect.GenDialectRegistry; import org.seasar.extension.jdbc.gen.meta.DbTableMeta; import org.seasar.extension.jdbc.util.ConnectionUtil; import org.seasar.framework.util.FileOutputStreamUtil; import org.seasar.framework.util.ResultSetUtil; import org.seasar.framework.util.StatementUtil; import org.seasar.framework.util.StringUtil; import org.seasar.framework.util.tiger.Maps; import jp.co.tis.gsp.tools.db.TypeMapper; import jp.co.tis.gsp.tools.dba.dialect.param.ExportParams; import jp.co.tis.gsp.tools.dba.dialect.param.ImportParams; import jp.co.tis.gsp.tools.dba.util.ProcessUtil; public class MysqlDialect extends Dialect { public MysqlDialect() { GenDialectRegistry.deregister(org.seasar.extension.jdbc.dialect.MysqlDialect.class); GenDialectRegistry.register(org.seasar.extension.jdbc.dialect.MysqlDialect.class, new ExtendedMysqlGenDialect()); } private Map<Integer, String> typeToNameMap = Maps.map(Types.BIGINT, "BIGINT").$(Types.BLOB, "BLOB") .$(Types.BOOLEAN, "BOOLEAN").$(Types.CHAR, "CHAR").$(Types.CLOB, "TEXT").$(Types.DATE, "DATE") .$(Types.DECIMAL, "NUMBER").$(Types.DOUBLE, "DOUBLE").$(Types.FLOAT, "FLOAT").$(Types.INTEGER, "INT") .$(Types.TIMESTAMP, "TIMESTAMP").$(Types.VARCHAR, "VARCHAR").$(); @Override public void exportSchema(ExportParams params) throws MojoExecutionException { BufferedInputStream in = null; FileOutputStream out = null; try { File dumpFile = params.getDumpFile(); String user = params.getUser(); String password = params.getPassword(); String schema = params.getSchema(); ProcessBuilder pb = new ProcessBuilder("mysqldump", schema, "-u", user, "--password=" + password, "--default-character-set=utf8", "--hex-blob", "-R"); Process process = pb.start(); in = new BufferedInputStream(process.getInputStream()); out = FileOutputStreamUtil.create(dumpFile); byte[] buf = new byte[4096]; while (true) { int res = in.read(buf); if (res <= 0) break; out.write(buf, 0, res); } } catch (IOException e) { throw new MojoExecutionException("mysqldump", e); } finally { IOUtils.closeQuietly(in); IOUtils.closeQuietly(out); } } /** * ??????? * * MySQL?????????????????????? * * @param user ?? * @param password * @param adminUser ? * @param adminPassword ? * @param schema * @throws MojoExecutionException */ public void dropAll(String user, String password, String adminUser, String adminPassword, String schema) throws MojoExecutionException { Connection conn = null; Statement stmt = null; try { conn = DriverManager.getConnection(url, adminUser, adminPassword); stmt = conn.createStatement(); // ?? String nmzschema = schema; String dropListSql = "SELECT TABLE_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='" + nmzschema + "'"; dropObjectsInSchema(conn, dropListSql, nmzschema, OBJECT_TYPE.FK); dropListSql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA='" + nmzschema + "'"; dropObjectsInSchema(conn, dropListSql, nmzschema, OBJECT_TYPE.VIEW); dropListSql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='" + nmzschema + "'"; dropObjectsInSchema(conn, dropListSql, nmzschema, OBJECT_TYPE.TABLE); } catch (SQLException e) { throw new MojoExecutionException("?", e); } finally { StatementUtil.close(stmt); ConnectionUtil.close(conn); } } @Override protected void dropObjectsInSchema(Connection conn, String dropListSql, String schema, OBJECT_TYPE objType) throws SQLException { Statement stmt = null; ResultSet rs = null; try { stmt = conn.createStatement(); rs = stmt.executeQuery(dropListSql); String dropSql = ""; while (rs.next()) { switch (objType) { case FK: dropSql = "ALTER TABLE " + rs.getString(1) + " DROP FOREIGN KEY " + rs.getString(2); break; case TABLE: dropSql = "DROP TABLE " + rs.getString(1); break; case VIEW: dropSql = "DROP VIEW " + rs.getString(1); break; default: // ?? break; } stmt = conn.createStatement(); System.err.println(dropSql); stmt.execute(dropSql); } } finally { rs.close(); StatementUtil.close(stmt); } } @Override public void createUser(String user, String password, String adminUser, String adminPassword) throws MojoExecutionException { Connection conn = null; Statement stmt = null; try { conn = DriverManager.getConnection(url, adminUser, adminPassword); stmt = conn.createStatement(); String db = normalizeSchemaName(conn.getCatalog()); if (existsUser(conn, user)) { // ??????????????? stmt.execute("GRANT ALL ON " + db + ".* TO '" + user + "'"); return; } stmt.execute("CREATE USER '" + user + "' IDENTIFIED BY '" + password + "'"); stmt.execute("GRANT ALL ON " + db + ".* TO '" + user + "'"); } catch (SQLException e) { throw new MojoExecutionException("CREATE USER?", e); } finally { StatementUtil.close(stmt); ConnectionUtil.close(conn); } } private boolean existsUser(Connection conn, String user) throws SQLException { PreparedStatement stmt = null; try { stmt = conn.prepareStatement("SELECT count(*) AS num FROM mysql.user WHERE User=?"); stmt.setString(1, user); ResultSet rs = stmt.executeQuery(); rs.next(); return (rs.getInt("num") > 0); } finally { StatementUtil.close(stmt); } } @Override public void importSchema(ImportParams params) throws MojoExecutionException { try { File dumpFile = params.getDumpFile(); if (!dumpFile.exists()) throw new MojoExecutionException(dumpFile.getName() + " is not found?"); String user = params.getAdminUser(); String password = params.getAdminPassword(); String schema = params.getSchema(); String[] args = new String[] { "mysql", "--default-character-set=utf8", "-u", user, "--password=" + password, "-D", schema, "-e", "\"source " + dumpFile.getAbsolutePath().replaceAll("\\\\", "/") + "\"" }; ProcessUtil.exec(args); } catch (Exception e) { throw new MojoExecutionException("??", e); } } @Override public TypeMapper getTypeMapper() { return new TypeMapper(typeToNameMap); } /** * ?SQL?? * @return ?SQL */ @Override public String getViewDefinitionSql() { return "SELECT view_definition FROM information_schema.views WHERE table_name=? AND table_schema=?"; } /** * View?DDL??MySQL. * * <p> * MySQL?JDBC??DbTableMeta???????????? * </p> * * @param conn {@inheritDoc} * @param viewName {@inheritDoc} * @param tableMeta {@inheritDoc} * @return {@inheritDoc} * @throws SQLException {@inheritDoc} */ public String getViewDefinition(Connection conn, String viewName, DbTableMeta tableMeta) throws SQLException { String sql = getViewDefinitionSql(); if (sql == null) { return null; } PreparedStatement stmt = null; ResultSet rs = null; int idx = 1; try { stmt = conn.prepareStatement(sql); stmt.setString(idx++, viewName); stmt.setString(idx++, tableMeta.getCatalogName()); rs = stmt.executeQuery(); while (rs.next()) { return rs.getString(1); } } finally { ResultSetUtil.close(rs); StatementUtil.close(stmt); } return null; } @Override public void setObjectInStmt(PreparedStatement stmt, int parameterIndex, String value, int sqlType) throws SQLException { if (sqlType == UN_USABLE_TYPE) { stmt.setNull(parameterIndex, Types.NULL); } else if (StringUtil.isBlank(value) || "".equals(value)) { stmt.setNull(parameterIndex, sqlType); } else if (sqlType == Types.TIMESTAMP) { stmt.setTimestamp(parameterIndex, Timestamp.valueOf(value)); } else { stmt.setObject(parameterIndex, value, sqlType); } } }