org.springframework.jdbc.datasource.init.ScriptUtils Java Examples

/**
 * 通过sql脚本初始化数据库表
 *
 * @param conn
 * @throws SQLException
 */
private static void doInitWork(Connection conn) throws SQLException {
    try {
        // 生成临时sql文件加载数据库sql执行脚本,
        File sqlFile = new File(WINDCHAT_SQLITE_SQL);
        if (!sqlFile.exists()) {
            FileUtils.writeResourceToFile("/" + WINDCHAT_SQLITE_SQL, sqlFile);
        }

        // 初始化数据库表
        File file = new File(WINDCHAT_SQLITE_SQL);
        if (!file.exists()) {
            throw new FileNotFoundException("init mysql with sql script file is not exists");
        }

        FileSystemResource rc = new FileSystemResource(file);
        EncodedResource encodeRes = new EncodedResource(rc, "GBK");
        ScriptUtils.executeSqlScript(conn, encodeRes);
        SqlLog.info("windchat init sqlite with sql-script finish");

        file.delete();
    } catch (Exception e) {
        throw new SQLException(e);
    }
}
 

private static void initDatabaseTable(Connection conn) throws SQLException {
    try {
        // 生成临时sql文件加载数据库sql执行脚本,
        File sqlFile = new File(WINDCHAT_MYSQL_SQL);
        if (!sqlFile.exists()) {
            FileUtils.writeResourceToFile("/" + WINDCHAT_MYSQL_SQL, sqlFile);
        }

        // 初始化数据库表
        File file = new File(WINDCHAT_MYSQL_SQL);
        if (!file.exists()) {
            throw new FileNotFoundException("init mysql with sql script file is not exists");
        }

        FileSystemResource rc = new FileSystemResource(file);
        EncodedResource encodeRes = new EncodedResource(rc, "GBK");
        ScriptUtils.executeSqlScript(conn, encodeRes);
        SqlLog.info("windchat init mysql database with sql-script finish");

        file.delete();
    } catch (Exception e) {
        throw new SQLException(e);
    }
}
 

private static void initDatabaseTable(Connection conn) throws SQLException {
	try {
		// 生成临时sql文件加载数据库sql执行脚本,
		File sqlFile = new File(OPENZALY_MYSQL_SQL);
		if (!sqlFile.exists()) {
			FileUtils.writeResourceToFile("/" + OPENZALY_MYSQL_SQL, sqlFile);
		}

		// 初始化数据库表
		File file = new File(OPENZALY_MYSQL_SQL);
		if (!file.exists()) {
			throw new FileNotFoundException("init mysql with sql script file is not exists");
		}

		FileSystemResource rc = new FileSystemResource(file);
		EncodedResource encodeRes = new EncodedResource(rc, "GBK");
		ScriptUtils.executeSqlScript(conn, encodeRes);
		SqlLog.info("openzaly init mysql database with sql-script finish");

		file.delete();
	} catch (Exception e) {
		throw new SQLException(e);
	}
}
 

/**
 * 通过sql脚本初始化数据库表
 * 
 * @param conn
 * @throws SQLException
 */
private static void doInitWork(Connection conn) throws SQLException {
	try {
		// 生成临时sql文件加载数据库sql执行脚本,
		File sqlFile = new File(OPENZALY_SQLITE_SQL);
		if (!sqlFile.exists()) {
			FileUtils.writeResourceToFile("/" + OPENZALY_SQLITE_SQL, sqlFile);
		}

		// 初始化数据库表
		File file = new File(OPENZALY_SQLITE_SQL);
		if (!file.exists()) {
			throw new FileNotFoundException("init mysql with sql script file is not exists");
		}

		FileSystemResource rc = new FileSystemResource(file);
		EncodedResource encodeRes = new EncodedResource(rc, "GBK");
		ScriptUtils.executeSqlScript(conn, encodeRes);
		SqlLog.info("openzaly init sqlite with sql-script finish");

		file.delete();
	} catch (Exception e) {
		throw new SQLException(e);
	}
}
 

private static void initDatabaseTable(Connection conn) throws SQLException {
	try {
		// 生成临时sql文件加载数据库sql执行脚本,
		File sqlFile = new File(OPENZALY_MYSQL_SQL);
		if (!sqlFile.exists()) {
			FileUtils.writeResourceToFile("/" + OPENZALY_MYSQL_SQL, sqlFile);
		}

		// 初始化数据库表
		File file = new File(OPENZALY_MYSQL_SQL);
		if (!file.exists()) {
			throw new FileNotFoundException("init mysql with sql script file is not exists");
		}

		FileSystemResource rc = new FileSystemResource(file);
		EncodedResource encodeRes = new EncodedResource(rc, "GBK");
		ScriptUtils.executeSqlScript(conn, encodeRes);
		SqlLog.info("openzaly init mysql database with sql-script finish");

		file.delete();
	} catch (Exception e) {
		throw new SQLException(e);
	}
}
 

@Test
public void test() {
    if (drop) {
        try {
            transactionTemplate.execute((TransactionCallback<Void>) transactionStatus -> {
                Session session = entityManager.unwrap(Session.class);
                session.doWork(connection -> {
                    ScriptUtils.executeSqlScript(connection,
                        new EncodedResource(
                            new ClassPathResource(
                                String.format("flyway/scripts/%1$s/drop/drop.sql", databaseType)
                            )
                        ),
                        true, true,
                        ScriptUtils.DEFAULT_COMMENT_PREFIX,
                        ScriptUtils.DEFAULT_BLOCK_COMMENT_START_DELIMITER,
                        ScriptUtils.DEFAULT_BLOCK_COMMENT_END_DELIMITER,
                        ScriptUtils.DEFAULT_COMMENT_PREFIX);
                });
                return null;
            });
        } catch (TransactionException e) {
            LOGGER.error("Failure", e);
        }
    }
}
 

@Before
public void before() throws Exception
{
    bindSession();
    executeStartupRoutines();

    IntegrationTestData annotation = this.getClass().getAnnotation( IntegrationTestData.class );

    if ( annotation != null && !dataInit )
    {
        ScriptUtils.executeSqlScript( jdbcTemplate.getDataSource().getConnection(),
            new EncodedResource( new ClassPathResource( annotation.path() ), StandardCharsets.UTF_8 ) );
        // only executes once per Unit Test
        dataInit = true;
    }

    // method that can be overridden by subclasses
    setUpTest();
}
 

private void runScript(String scriptName, DataSource dataSouorce) throws SQLException {
    DefaultResourceLoader resourceLoader = new DefaultResourceLoader();
    Resource script = resourceLoader.getResource(scriptName);
    try (Connection con = dataSouorce.getConnection()) {
        ScriptUtils.executeSqlScript(con, script);
    }
}
 

@Before
public void setUp() throws ScriptException {
	jdbcTemplate = new JdbcTemplate(dataSource);
	try {
		ScriptUtils.executeSqlScript(dataSource.getConnection(),
				new ClassPathResource("metrics/create-schema.sql"));
	} catch (Exception e) {
		// if table exist, error is okay.
	}
}
 

public static void main(String[] args) throws Exception {
    String url = "jdbc:postgresql://localhost:5432/quartz";

    Class.forName("org.postgresql.Driver");
    Connection db = DriverManager.getConnection(url, "quartz", "quartz");

    System.out.println("Initializing database and creating tables");
    db.setAutoCommit(false);
    ScriptUtils.executeSqlScript(db, new FileSystemResource("tables_postgres.sql"));
    db.setAutoCommit(true);

    db.close();

    System.out.println("Database initialized");
}
 

/**
 * Construct a {@code MergedSqlConfig} instance by merging the configuration
 * from the supplied local (potentially method-level) {@code @SqlConfig} annotation
 * with class-level configuration discovered on the supplied {@code testClass}.
 * <p>Local configuration overrides class-level configuration.
 * <p>If the test class is not annotated with {@code @SqlConfig}, no merging
 * takes place and the local configuration is used "as is".
 */
MergedSqlConfig(SqlConfig localSqlConfig, Class<?> testClass) {
	Assert.notNull(localSqlConfig, "Local @SqlConfig must not be null");
	Assert.notNull(testClass, "testClass must not be null");

	// Get global attributes, if any.
	AnnotationAttributes attributes = AnnotatedElementUtils.findMergedAnnotationAttributes(testClass,
		SqlConfig.class.getName(), false, false);

	// Override global attributes with local attributes.
	if (attributes != null) {
		for (String key : attributes.keySet()) {
			Object value = AnnotationUtils.getValue(localSqlConfig, key);
			if (value != null) {
				// Is the value explicit (i.e., not a 'default')?
				if (!value.equals("") && (value != TransactionMode.DEFAULT) && (value != ErrorMode.DEFAULT)) {
					attributes.put(key, value);
				}
			}
		}
	}
	else {
		// Otherwise, use local attributes only.
		attributes = AnnotationUtils.getAnnotationAttributes(localSqlConfig, false, false);
	}

	this.dataSource = attributes.getString("dataSource");
	this.transactionManager = attributes.getString("transactionManager");
	this.transactionMode = getEnum(attributes, "transactionMode", TransactionMode.DEFAULT, TransactionMode.INFERRED);
	this.encoding = attributes.getString("encoding");
	this.separator = getString(attributes, "separator", ScriptUtils.DEFAULT_STATEMENT_SEPARATOR);
	this.commentPrefix = getString(attributes, "commentPrefix", ScriptUtils.DEFAULT_COMMENT_PREFIX);
	this.blockCommentStartDelimiter = getString(attributes, "blockCommentStartDelimiter",
		ScriptUtils.DEFAULT_BLOCK_COMMENT_START_DELIMITER);
	this.blockCommentEndDelimiter = getString(attributes, "blockCommentEndDelimiter",
		ScriptUtils.DEFAULT_BLOCK_COMMENT_END_DELIMITER);
	this.errorMode = getEnum(attributes, "errorMode", ErrorMode.DEFAULT, ErrorMode.FAIL_ON_ERROR);
}
 

@RequestMapping(value = "/executeSql", method = RequestMethod.POST)
public void executeSql(MultipartFile file, HttpServletResponse response) throws SQLException, IOException {
    Connection connection = DriverManager.getConnection(url + "mysql", username, password);
    String db = ("SC" + UUID.randomUUID().toString().substring(0, 5)).toLowerCase();
    List<String> sqls = Arrays.asList("CREATE DATABASE IF NOT EXISTS " + db + " DEFAULT CHARSET utf8",
            "CREATE USER '" + db + "'@'%' IDENTIFIED BY '" + db + "'",
            "grant all privileges on " + db + ".* to '" + db + "'@'%' identified by '" + db + "'");
    for (String sql : sqls) {
        Statement statement = connection.createStatement();
        statement.execute(sql);
        statement.close();
    }

    connection.close();

    String jdbcUrl = url + db;
    connection = DriverManager.getConnection(url, db, db);
    ScriptUtils.executeSqlScript(connection, new InputStreamResource(file.getInputStream()));
    connection.close();

    datasourceService.save(new Datasource()
            .setJdbcUrl(jdbcUrl)
            .setName(file.getOriginalFilename().substring(0, file.getOriginalFilename().lastIndexOf(".")))
            .setUsername(db)
            .setPassword(db)
            .setDriver("com.mysql.jdbc.Driver"));
    response.sendRedirect("/");
}
 

/**
 * Construct a {@code MergedSqlConfig} instance by merging the configuration
 * from the supplied local (potentially method-level) {@code @SqlConfig} annotation
 * with class-level configuration discovered on the supplied {@code testClass}.
 * <p>Local configuration overrides class-level configuration.
 * <p>If the test class is not annotated with {@code @SqlConfig}, no merging
 * takes place and the local configuration is used "as is".
 */
MergedSqlConfig(SqlConfig localSqlConfig, Class<?> testClass) {
	Assert.notNull(localSqlConfig, "Local @SqlConfig must not be null");
	Assert.notNull(testClass, "testClass must not be null");

	// Get global attributes, if any.
	AnnotationAttributes attributes = AnnotatedElementUtils.findMergedAnnotationAttributes(
			testClass, SqlConfig.class.getName(), false, false);

	// Override global attributes with local attributes.
	if (attributes != null) {
		for (String key : attributes.keySet()) {
			Object value = AnnotationUtils.getValue(localSqlConfig, key);
			if (value != null) {
				// Is the value explicit (i.e., not a 'default')?
				if (!value.equals("") && value != TransactionMode.DEFAULT && value != ErrorMode.DEFAULT) {
					attributes.put(key, value);
				}
			}
		}
	}
	else {
		// Otherwise, use local attributes only.
		attributes = AnnotationUtils.getAnnotationAttributes(localSqlConfig, false, false);
	}

	this.dataSource = attributes.getString("dataSource");
	this.transactionManager = attributes.getString("transactionManager");
	this.transactionMode = getEnum(attributes, "transactionMode", TransactionMode.DEFAULT, TransactionMode.INFERRED);
	this.encoding = attributes.getString("encoding");
	this.separator = getString(attributes, "separator", ScriptUtils.DEFAULT_STATEMENT_SEPARATOR);
	this.commentPrefix = getString(attributes, "commentPrefix", ScriptUtils.DEFAULT_COMMENT_PREFIX);
	this.blockCommentStartDelimiter = getString(attributes, "blockCommentStartDelimiter",
			ScriptUtils.DEFAULT_BLOCK_COMMENT_START_DELIMITER);
	this.blockCommentEndDelimiter = getString(attributes, "blockCommentEndDelimiter",
			ScriptUtils.DEFAULT_BLOCK_COMMENT_END_DELIMITER);
	this.errorMode = getEnum(attributes, "errorMode", ErrorMode.DEFAULT, ErrorMode.FAIL_ON_ERROR);
}
 

/**
 * Construct a {@code MergedSqlConfig} instance by merging the configuration
 * from the supplied local (potentially method-level) {@code @SqlConfig} annotation
 * with class-level configuration discovered on the supplied {@code testClass}.
 * <p>Local configuration overrides class-level configuration.
 * <p>If the test class is not annotated with {@code @SqlConfig}, no merging
 * takes place and the local configuration is used "as is".
 */
MergedSqlConfig(SqlConfig localSqlConfig, Class<?> testClass) {
	Assert.notNull(localSqlConfig, "Local @SqlConfig must not be null");
	Assert.notNull(testClass, "testClass must not be null");

	// Get global attributes, if any.
	AnnotationAttributes attributes = AnnotatedElementUtils.findMergedAnnotationAttributes(
			testClass, SqlConfig.class.getName(), false, false);

	// Override global attributes with local attributes.
	if (attributes != null) {
		for (String key : attributes.keySet()) {
			Object value = AnnotationUtils.getValue(localSqlConfig, key);
			if (value != null) {
				// Is the value explicit (i.e., not a 'default')?
				if (!value.equals("") && value != TransactionMode.DEFAULT && value != ErrorMode.DEFAULT) {
					attributes.put(key, value);
				}
			}
		}
	}
	else {
		// Otherwise, use local attributes only.
		attributes = AnnotationUtils.getAnnotationAttributes(localSqlConfig, false, false);
	}

	this.dataSource = attributes.getString("dataSource");
	this.transactionManager = attributes.getString("transactionManager");
	this.transactionMode = getEnum(attributes, "transactionMode", TransactionMode.DEFAULT, TransactionMode.INFERRED);
	this.encoding = attributes.getString("encoding");
	this.separator = getString(attributes, "separator", ScriptUtils.DEFAULT_STATEMENT_SEPARATOR);
	this.commentPrefix = getString(attributes, "commentPrefix", ScriptUtils.DEFAULT_COMMENT_PREFIX);
	this.blockCommentStartDelimiter = getString(attributes, "blockCommentStartDelimiter",
			ScriptUtils.DEFAULT_BLOCK_COMMENT_START_DELIMITER);
	this.blockCommentEndDelimiter = getString(attributes, "blockCommentEndDelimiter",
			ScriptUtils.DEFAULT_BLOCK_COMMENT_END_DELIMITER);
	this.errorMode = getEnum(attributes, "errorMode", ErrorMode.DEFAULT, ErrorMode.FAIL_ON_ERROR);
}
 

@Override
@Transactional
public void initSettings(Resource resource) {
	Session session = entityManager.unwrap(Session.class);
	session.doWork(connection -> ScriptUtils.executeSqlScript(connection, resource));
}
 

/**
 * Split an SQL script into separate statements delimited by the provided
 * delimiter character. Each individual statement will be added to the
 * provided {@code List}.
 * <p>Within a statement, "{@code --}" will be used as the comment prefix;
 * any text beginning with the comment prefix and extending to the end of
 * the line will be omitted from the statement. In addition, multiple adjacent
 * whitespace characters will be collapsed into a single space.
 * @param script the SQL script
 * @param delim character delimiting each statement &mdash; typically a ';' character
 * @param statements the list that will contain the individual statements
 * @deprecated as of Spring 4.0.3, in favor of using
 * {@link org.springframework.jdbc.datasource.init.ScriptUtils#splitSqlScript(String, char, List)}
 */
@Deprecated
public static void splitSqlScript(String script, char delim, List<String> statements) {
	ScriptUtils.splitSqlScript(script, delim, statements);
}
 

/**
 * Determine if the provided SQL script contains the specified delimiter.
 * @param script the SQL script
 * @param delim character delimiting each statement — typically a ';' character
 * @return {@code true} if the script contains the delimiter; {@code false} otherwise
 * @deprecated as of Spring 4.0.3, in favor of using
 * {@link org.springframework.jdbc.datasource.init.ScriptUtils#containsSqlScriptDelimiters}
 */
@Deprecated
public static boolean containsSqlScriptDelimiters(String script, char delim) {
	return ScriptUtils.containsSqlScriptDelimiters(script, String.valueOf(delim));
}
 

/**
 * Read a script from the provided {@code LineNumberReader}, using the supplied
 * comment prefix, and build a {@code String} containing the lines.
 * <p>Lines <em>beginning</em> with the comment prefix are excluded from the
 * results; however, line comments anywhere else &mdash; for example, within
 * a statement &mdash; will be included in the results.
 * @param lineNumberReader the {@code LineNumberReader} containing the script
 * to be processed
 * @param commentPrefix the prefix that identifies comments in the SQL script &mdash; typically "--"
 * @return a {@code String} containing the script lines
 * @deprecated as of Spring 4.0.3, in favor of using
 * {@link org.springframework.jdbc.datasource.init.ScriptUtils#readScript(LineNumberReader, String, String)}
 */
@Deprecated
public static String readScript(LineNumberReader lineNumberReader, String commentPrefix) throws IOException {
	return ScriptUtils.readScript(lineNumberReader, commentPrefix, ScriptUtils.DEFAULT_STATEMENT_SEPARATOR);
}
 

/**
 * Read a script from the provided {@code LineNumberReader}, using
 * "{@code --}" as the comment prefix, and build a {@code String} containing
 * the lines.
 * @param lineNumberReader the {@code LineNumberReader} containing the script
 * to be processed
 * @return a {@code String} containing the script lines
 * @see #readScript(LineNumberReader, String)
 * @deprecated as of Spring 4.0.3, in favor of using
 * {@link org.springframework.jdbc.datasource.init.ScriptUtils#readScript(LineNumberReader, String, String)}
 */
@Deprecated
public static String readScript(LineNumberReader lineNumberReader) throws IOException {
	return readScript(lineNumberReader, ScriptUtils.DEFAULT_COMMENT_PREFIX);
}