Java ScriptUtils.executeSqlScript方法代码示例

import org.springframework.jdbc.datasource.init.ScriptUtils; //导入方法依赖的package包/类
/**
 * Initialize data base with 'MDA' JIRA project.
 */
@BeforeClass
public static void initializeJiraDataBaseForSla() throws SQLException {
	final DataSource datasource = new SimpleDriverDataSource(new JDBCDriver(), "jdbc:hsqldb:mem:dataSource", null, null);
	final Connection connection = datasource.getConnection();
	try {
		ScriptUtils.executeSqlScript(connection, new EncodedResource(new ClassPathResource("sql/sla/jira-create.sql"), StandardCharsets.UTF_8));
		ScriptUtils.executeSqlScript(connection, new EncodedResource(new ClassPathResource("sql/sla/jira.sql"), StandardCharsets.UTF_8));
	} finally {
		connection.close();
	}
}
 

import org.springframework.jdbc.datasource.init.ScriptUtils; //导入方法依赖的package包/类
/**
 * Initialize data base with 'MDA' JIRA project.
 */
@BeforeClass
public static void initializeJiraDataBase() throws SQLException {
	datasource = new SimpleDriverDataSource(new JDBCDriver(), "jdbc:hsqldb:mem:dataSource", null, null);
	final Connection connection = datasource.getConnection();
	final JdbcTemplate jdbcTemplate = new JdbcTemplate(datasource);
	try {
		ScriptUtils.executeSqlScript(connection,
				new EncodedResource(new ClassPathResource("sql/base-1/jira-create.sql"), StandardCharsets.UTF_8));
		ScriptUtils.executeSqlScript(connection,
				new EncodedResource(new ClassPathResource("sql/base-1/jira.sql"), StandardCharsets.UTF_8));
		jdbcTemplate.queryForList("SELECT * FROM pluginversion WHERE ID = 10075");
	} finally {
		connection.close();
	}
}
 

import org.springframework.jdbc.datasource.init.ScriptUtils; //导入方法依赖的package包/类
/**
 * Initialize data base with 'MDA' JIRA project.
 */
@BeforeClass
public static void initializeJiraDataBaseForImport() throws SQLException {
	datasource = new SimpleDriverDataSource(new JDBCDriver(), "jdbc:hsqldb:mem:dataSource", null, null);
	final Connection connection = datasource.getConnection();
	try {
		ScriptUtils.executeSqlScript(connection,
				new EncodedResource(new ClassPathResource("sql/base-1/jira-create.sql"), StandardCharsets.UTF_8));
		ScriptUtils.executeSqlScript(connection,
				new EncodedResource(new ClassPathResource("sql/base-2/jira-create.sql"), StandardCharsets.UTF_8));
		ScriptUtils.executeSqlScript(connection,
				new EncodedResource(new ClassPathResource("sql/upload/jira-create.sql"), StandardCharsets.UTF_8));

		ScriptUtils.executeSqlScript(connection, new EncodedResource(new ClassPathResource("sql/base-1/jira.sql"), StandardCharsets.UTF_8));
		ScriptUtils.executeSqlScript(connection, new EncodedResource(new ClassPathResource("sql/base-2/jira.sql"), StandardCharsets.UTF_8));
		ScriptUtils.executeSqlScript(connection, new EncodedResource(new ClassPathResource("sql/upload/jira.sql"), StandardCharsets.UTF_8));
	} finally {
		connection.close();
	}
}
 

import org.springframework.jdbc.datasource.init.ScriptUtils; //导入方法依赖的package包/类
@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("/");
}
 

import org.springframework.jdbc.datasource.init.ScriptUtils; //导入方法依赖的package包/类
/**
 * Clean data base with 'MDA' JIRA project.
 */
@AfterClass
public static void cleanJiraDataBaseForSla() throws SQLException {
	final DataSource datasource = new SimpleDriverDataSource(new JDBCDriver(), "jdbc:hsqldb:mem:dataSource", null, null);
	final Connection connection = datasource.getConnection();

	try {
		ScriptUtils.executeSqlScript(connection, new EncodedResource(new ClassPathResource("sql/sla/jira-drop.sql"), StandardCharsets.UTF_8));
	} finally {
		connection.close();
	}
}
 

import org.springframework.jdbc.datasource.init.ScriptUtils; //导入方法依赖的package包/类
/**
 * Clean data base with 'MDA' JIRA project.
 */
@AfterClass
public static void cleanJiraDataBase() throws SQLException {
	final Connection connection = datasource.getConnection();

	try {
		ScriptUtils.executeSqlScript(connection,
				new EncodedResource(new ClassPathResource("sql/base-1/jira-drop.sql"), StandardCharsets.UTF_8));
	} finally {
		connection.close();
	}
}
 

import org.springframework.jdbc.datasource.init.ScriptUtils; //导入方法依赖的package包/类
/**
 * Initialize data base with 'MDA' JIRA project.
 */
@BeforeClass
public static void initializeJiraDataBaseForImport() throws SQLException {
	final DataSource datasource = new SimpleDriverDataSource(new JDBCDriver(), "jdbc:hsqldb:mem:dataSource", null, null);
	final Connection connection = datasource.getConnection();
	try {
		ScriptUtils.executeSqlScript(connection,
				new EncodedResource(new ClassPathResource("sql/upload/jira-create.sql"), StandardCharsets.UTF_8));
		ScriptUtils.executeSqlScript(connection, new EncodedResource(new ClassPathResource("sql/upload/jira.sql"), StandardCharsets.UTF_8));
	} finally {
		connection.close();
	}
}
 

import org.springframework.jdbc.datasource.init.ScriptUtils; //导入方法依赖的package包/类
/**
 * Clean data base with 'MDA' JIRA project.
 */
@AfterClass
public static void cleanJiraDataBaseForImport() throws SQLException {
	final DataSource datasource = new SimpleDriverDataSource(new JDBCDriver(), "jdbc:hsqldb:mem:dataSource", null, null);
	final Connection connection = datasource.getConnection();

	try {
		ScriptUtils.executeSqlScript(connection, new EncodedResource(new ClassPathResource("sql/upload/jira-drop.sql"), StandardCharsets.UTF_8));
	} finally {
		connection.close();
	}
}
 

import org.springframework.jdbc.datasource.init.ScriptUtils; //导入方法依赖的package包/类
/**
 * Initialize data base with 'MDA' JIRA project.
 */
@BeforeClass
public static void initializeJiraDataBase3() throws SQLException {
	final Connection connection = datasource.getConnection();
	try {
		ScriptUtils.executeSqlScript(connection,
				new EncodedResource(new ClassPathResource("sql/base-3/jira-create.sql"), StandardCharsets.UTF_8));
		ScriptUtils.executeSqlScript(connection, new EncodedResource(new ClassPathResource("sql/base-3/jira.sql"), StandardCharsets.UTF_8));
	} finally {
		connection.close();
	}
}
 

import org.springframework.jdbc.datasource.init.ScriptUtils; //导入方法依赖的package包/类
/**
 * Clean data base with 'MDA' JIRA project.
 */
@AfterClass
public static void cleanJiraDataBase3() throws SQLException {
	final Connection connection = datasource.getConnection();

	try {
		ScriptUtils.executeSqlScript(connection, new EncodedResource(new ClassPathResource("sql/base-3/jira-drop.sql"), StandardCharsets.UTF_8));
	} finally {
		connection.close();
	}
}
 

import org.springframework.jdbc.datasource.init.ScriptUtils; //导入方法依赖的package包/类
/**
 * Clean data base with 'MDA' JIRA project.
 */
@AfterClass
public static void cleanJiraDataBaseForImport() throws SQLException {
	final Connection connection = datasource.getConnection();

	try {
		ScriptUtils.executeSqlScript(connection, new EncodedResource(new ClassPathResource("sql/base-1/jira-drop.sql"), StandardCharsets.UTF_8));
		ScriptUtils.executeSqlScript(connection, new EncodedResource(new ClassPathResource("sql/base-2/jira-drop.sql"), StandardCharsets.UTF_8));
		ScriptUtils.executeSqlScript(connection, new EncodedResource(new ClassPathResource("sql/upload/jira-drop.sql"), StandardCharsets.UTF_8));
	} finally {
		connection.close();
	}
}
 

import org.springframework.jdbc.datasource.init.ScriptUtils; //导入方法依赖的package包/类
/**
 * Initialize data base with 'MDA' JIRA project.
 */
@BeforeClass
public static void initializeJiraDataBase2() throws SQLException {
	final Connection connection = datasource.getConnection();
	try {
		ScriptUtils.executeSqlScript(connection,
				new EncodedResource(new ClassPathResource("sql/base-2/jira-create.sql"), StandardCharsets.UTF_8));
		ScriptUtils.executeSqlScript(connection, new EncodedResource(new ClassPathResource("sql/base-2/jira.sql"), StandardCharsets.UTF_8));
	} finally {
		connection.close();
	}
}
 

import org.springframework.jdbc.datasource.init.ScriptUtils; //导入方法依赖的package包/类
/**
 * Clean data base with 'MDA' JIRA project.
 */
@AfterClass
public static void cleanJiraDataBase2() throws SQLException {
	final Connection connection = datasource.getConnection();

	try {
		ScriptUtils.executeSqlScript(connection, new EncodedResource(new ClassPathResource("sql/base-2/jira-drop.sql"), StandardCharsets.UTF_8));
	} finally {
		connection.close();
	}
}
 

import org.springframework.jdbc.datasource.init.ScriptUtils; //导入方法依赖的package包/类
public static void executeScript(DataSource dataSource, String... sqlResourcePaths) throws DataAccessException,
		SQLException {
	for (String sqlResourcePath : sqlResourcePaths) {
		Resource resource = resourceLoader.getResource(sqlResourcePath);
		ScriptUtils.executeSqlScript(dataSource.getConnection(), new EncodedResource(resource, DEFAULT_ENCODING));
	}
}
 

import org.springframework.jdbc.datasource.init.ScriptUtils; //导入方法依赖的package包/类
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");
}