1. Introduction
In this tutorial, we’ll review exactly how to run a SQL manuscript from Java. As component of this, we’ll check out 2 collections, MyBatis and also Springtime JDBC. MyBatis gives the ScriptRunner course, and also Springtime JDBC gives ScriptUtils to review SQL manuscript documents straight from disks and also run them on target data sources.
We’ll additionally carry out a customized DB energy to review the SQL declarations from a documents and afterwards implement them in sets.
To maintain points easy and also make the code up and also running quickly, allow’s utilize the extensively made use of in-memory H2 ingrained data source for screening objectives. Allow’s see them done in activity.
2. Perform SQL Manuscript Making Use Of MyBatis ScriptRunner
Initially, allow’s include the Expert reliance for mybatis by consisting of the complying with in the pom.xml:
<< reliance>>.
<< groupId>> org.mybatis<.
<< artifactId>> mybatis<.
<< variation>> 3.5.7<.
<Currently, allow's have a look at the course MyBatisScriptUtility:
public course MyBatisScriptUtility {
public fixed space runScript(.
String course,.
Link link.
) tosses Exemption {
ScriptRunner scriptRunner = brand-new ScriptRunner( link);
scriptRunner.setSendFullScript( incorrect);
scriptRunner.setStopOnError( real);
scriptRunner.runScript( brand-new java.io.FileReader( course));
}
} As obvious in the above code, ScriptRunner offers the alternative to carry out the manuscript line by line along with the complete manuscript in one go
Prior to performing the SQL data, allow's have a look at it:
-- Develop the staff members table if it does not exist.
PRODUCE TABLE staff members (.
id INT PRIMARY SECRET,.
first_name VARCHAR( 50 ),.
last_name VARCHAR( 50 ),.
division VARCHAR( 50 ),.
wage DECIMAL( 10, 2).
);.
-- Place worker documents.
PLACE INTO staff members (id, first_name, last_name, division, wage).
WORTHS (1, 'John', 'Doe', 'HUMAN RESOURCES', 50000.00);.
PLACE INTO staff members (id, first_name, last_name, division, wage).
WORTHS (2, 'Jane', 'Smith', 'IT', 60000.00);.
-- Even more SQL declarations ...As we can see, the above data contains a mix of block remarks, single-line remarks, empty lines, produce table declaration and also insert declarations. This allows us to examine the parsing capacities of the collections talked about in this post.
The execution for performing a complete manuscript data is simple. For this, the whole data reads from the disk and also handed down as a string debate to the approach java.sql.Statement.execute() For this reason, we'll like to run it line by line:
@Test.
public space givenConnectionObject_whenSQLFile_thenExecute() tosses Exemption {
String course = brand-new Documents( ClassLoader.getSystemClassLoader(). getResource(" employee.sql"). getFile()). toPath(). toString();.
MyBatisScriptUtility.runScript( course, link);.
Declaration declaration = connection.createStatement();.
ResultSet resultSet = statement.executeQuery(" SELECT COUNT( 1) FROM staff members");.
if (resultSet.next()) {
int matter = resultSet.getInt( 1 );.
Assert.assertEquals(" Inaccurate variety of documents placed", 20, matter);.
}
} In the above instance, we have actually made use of a SQL data that develops an staff members table and afterwards inserts 20 documents right into it.
The even more interested viewers can additionally experience the resource code of ScriptRunner
3. Perform SQL Manuscript Making Use Of Springtime JDBC ScriptUtils
Carrying On, it's time to take a look at the ScriptUtils course. Allow's initial deal with the Expert reliance:
<< reliance>>.
<< groupId>> org.springframework<.
<< artifactId>> spring-jdbc<.
<< variation>> 5.3.29<.
<Hereafter, allow's have a look at the course SpringScriptUtility:
public course SpringScriptUtility {
public fixed space runScript( String course, Link link) {
boolean continueOrError = incorrect;.
boolean ignoreFailedDrops = incorrect;.
String commentPrefix="--";.
String separator=";";.
String blockCommentStartDelimiter="/ *";.
String blockCommentEndDelimiter="*/";.
ScriptUtils.executeSqlScript(.
link,.
brand-new EncodedResource( brand-new PathResource( course)),.
continueOrError,.
ignoreFailedDrops,.
commentPrefix,.
separator,.
blockCommentStartDelimiter,.
blockCommentEndDelimiter.
);.
}
} As we see above, ScriptUtils gives several alternatives to review the SQL data. For this reason it sustains several data source engines that use various delimiters for remark recognition past the regular "--," "/ *" and also "*/". Additionally, there are 2 even more disagreements continueOnError, and also ignoreFailedDrops which offer self-evident objectives.
Unlike MyBatis collection, ScriptUtils does not provide the alternative to run the complete manuscript yet instead chooses to run the SQL declarations one at a time This can be verified by taking a look at its resource code
Allow's have a look at the implementation:
@Test.
public space givenConnectionObject_whenSQLFile_thenExecute() tosses Exemption {
String course = brand-new Documents( ClassLoader.getSystemClassLoader()
. getResource(" employee.sql"). getFile()). toPath(). toString();.
SpringScriptUtility.runScript( course, link);.
Declaration declaration = connection.createStatement();.
ResultSet resultSet = statement.executeQuery(" SELECT COUNT( 1) FROM staff members");.
if (resultSet.next()) {
int matter = resultSet.getInt( 1 );.
Assert.assertEquals(" Inaccurate variety of documents placed", 20, matter);.
}
} In the above approach, we are merely conjuring up SpringScriptUtility.runScript() with the course and also link things.
4. Implementing SQL Statements in Batches Making Use Of JDBC
Until now, we have actually seen that both collections practically sustain performing SQL documents. Yet neither of them offers the alternative to run the SQL declarations in sets This is a vital attribute for performing big SQL documents.
For this reason, allow's generate our very own SqlScriptBatchExecutor:
fixed space executeBatchedSQL( String scriptFilePath, Link link, int batchSize) tosses Exemption {
Checklist<< String> > sqlStatements = parseSQLScript( scriptFilePath);.
executeSQLBatches( link, sqlStatements, batchSize);.
} Over execution can be summarized in 2 lines: the approach parseSQLScript() brings the SQL declarations from the data, and also executeSQLBatches() implements them in sets
Allow's have a look at the approach parseSQLScript():
fixed Checklist<< String> > parseSQLScript( String scriptFilePath) tosses IOException {
Checklist<< String> > sqlStatements = brand-new ArrayList<>< >();.
attempt (BufferedReader viewers = brand-new BufferedReader( brand-new FileReader( scriptFilePath))) {
StringBuilder currentStatement = brand-new StringBuilder();.
String line;.
while (( line = reader.readLine())!= null) {
Matcher commentMatcher = COMMENT_PATTERN. matcher( line);.
line = commentMatcher.replaceAll("");.
line = line.trim();.
if (line.isEmpty()) {
proceed;.
}
currentStatement.append( line). append(" ");.
if (line.endsWith(";")) {
sqlStatements.add( currentStatement.toString());.
logger.info( currentStatement.toString());.
currentStatement.setLength( 0 );.
}
}
} catch (IOException e) {
toss e;.
}
return sqlStatements;.
} We utilize COMMENT_PATTERN = Pattern.compile("--. * |/ *(.|[rn]) *? */") to determine the remarks and also the empty lines, and afterwards eliminate them from the SQL data. Like MyBatis, we additionally sustain just the default remark delimiters.
Currently, we can have a look at the approach executeSQLBatches():
fixed space executeSQLBatches( Link link, Checklist<< String> > sqlStatements, int batchSize).
tosses SQLException {
int matter = 0;.
Declaration declaration = connection.createStatement();.
for (String sql: sqlStatements) {
statement.addBatch( sql);.
matter++;.
if (matter % batchSize == 0) {
logger.info(" Implementing set");.
statement.executeBatch();.
statement.clearBatch();.
}
}
if (matter % batchSize!= 0) {
statement.executeBatch();.
}
connnection.commit();.
} The over approach takes the checklist of SQL declarations, repeats via it, and afterwards implements it when the set dimension expands to the worth of the debate batchSize
Allow's see the customized program at work:
@Test.
public space givenConnectionObject_whenSQLFile_thenExecute() tosses Exemption {
String course = brand-new Documents(.
ClassLoader.getSystemClassLoader(). getResource(" employee.sql"). getFile()). toPath(). toString();.
SqlScriptBatchExecutor.executeBatchedSQL( course, link, 10);.
Declaration declaration = connection.createStatement();.
ResultSet resultSet = statement.executeQuery(" SELECT COUNT( 1) FROM staff members");.
if (resultSet.next()) {
int matter = resultSet.getInt( 1 );.
Assert.assertEquals(" Inaccurate variety of documents placed", 20, matter);.
}
} It carries out the SQL declarations in 2 sets, 10 declarations in each. Especially, the set dimension below is parameterized to ensure that it can be readjusted according to the variety of SQL declarations in the data.
5. Verdict
In this post, we found out about the data source energies supplied by MyBatis and also Springtime JDBC to carry out SQL documents. We located that Springtime JDBC is extra versatile in analyzing the SQL documents. In addition, we created a customized energy that sustains set implementations of the SQL declarations.
Customarily, the code for this tutorial can be located over on GitHub
