Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <h2>Intro</h2> <p>As I've promised I've hacked up a complete example. I've used MySQL and created three tables like the following:</p> <pre><code>CREATE TABLE `test{1,2,3}` ( `id` int(11) NOT NULL AUTO_INCREMENT, `data` varchar(255) NOT NULL UNIQUE, PRIMARY KEY (`id`) ); </code></pre> <p><code>test2</code> contains a single row initially.</p> <pre><code>INSERT INTO `test2` (`data`) VALUES ('a'); </code></pre> <p>(<a href="http://pastebin.com/Jh3ngvN3" rel="nofollow noreferrer">I've posted the full code to http://pastebin.com</a>.)</p> <p>The following example does several things.</p> <ol> <li>Sets <code>threads</code> to <code>3</code> which determines how many jobs are going to be run in parallel.</li> <li>Creates <code>threads</code> number of connections.</li> <li>Spouts out some sample data for every table (by default the data is <code>a</code> for every table).</li> <li>Creates <code>threads</code> number of jobs to be run and loads them with data.</li> <li>Runs the jobs in <code>threads</code> number of <em>threads</em> and waits for their completion (successful or not).</li> <li>If no exceptions occurred commits every connection; otherwise it rolls back each of them.</li> <li>Closes the connections (however these can be reused).</li> </ol> <p><sub>(Note, that I've used Java 7's automatic resource management feature in <code>SQLTask.call()</code>.)</sub></p> <h2>Logic</h2> <pre><code>public static void main(String[] args) throws SQLException, InterruptedException { int threads = 3; List&lt;Connection&gt; connections = getConnections(threads); Map&lt;String, String&gt; tableData = getTableData(threads); List&lt;SQLTask&gt; tasks = getTasks(threads, connections); setData(tableData, tasks); try { runTasks(tasks); commitConnections(connections); } catch (ExecutionException ex) { rollbackConnections(connections); } finally { closeConnections(connections); } } </code></pre> <h2>Data</h2> <pre><code>private static Map&lt;String, String&gt; getTableData(int threads) { Map&lt;String, String&gt; tableData = new HashMap&lt;&gt;(); for (int i = 1; i &lt;= threads; i++) tableData.put("test" + i, "a"); return tableData; } </code></pre> <h2>Tasks</h2> <pre><code>private static final class SQLTask implements Callable&lt;Void&gt; { private final Connection connection; private String data; private String table; public SQLTask(Connection connection) { this.connection = connection; } public void setTable(String table) { this.table = table; } public void setData(String data) { this.data = data; } @Override public Void call() throws SQLException { try (Statement statement = connection.createStatement()) { statement.executeUpdate(String.format( "INSERT INTO `%s` (data) VALUES ('%s');", table, data)); } return null; } } private static List&lt;SQLTask&gt; getTasks(int threads, List&lt;Connection&gt; connections) { List&lt;SQLTask&gt; tasks = new ArrayList&lt;&gt;(); for (int i = 0; i &lt; threads; i++) tasks.add(new SQLTask(connections.get(i))); return tasks; } private static void setData(Map&lt;String, String&gt; tableData, List&lt;SQLTask&gt; tasks) { Iterator&lt;Entry&lt;String, String&gt;&gt; i = tableData.entrySet().iterator(); Iterator&lt;SQLTask&gt; j = tasks.iterator(); while (i.hasNext()) { Entry&lt;String, String&gt; entry = i.next(); SQLTask task = j.next(); task.setTable(entry.getKey()); task.setData(entry.getValue()); } } </code></pre> <h2>Run</h2> <pre><code>private static void runTasks(List&lt;SQLTask&gt; tasks) throws ExecutionException, InterruptedException { ExecutorService executorService = Executors.newFixedThreadPool(tasks.size()); List&lt;Future&lt;Void&gt;&gt; futures = executorService.invokeAll(tasks); executorService.shutdown(); for (Future&lt;Void&gt; future : futures) future.get(); } </code></pre> <h2>Result</h2> <p>Given the default data returned by <code>getTableData(...)</code></p> <pre><code>test1 -&gt; `a` test2 -&gt; `a` test3 -&gt; `a` </code></pre> <p>and the fact that <code>test2</code> already contains <code>a</code> (and the <code>data</code> column is <em>unique</em>) the second job will fail and throw an exception, thus every connection will be rolled back.</p> <p>If instead of <code>a</code>s you return <code>b</code>s, then the connections will be committed safely.</p> <p>This can be done similarly with <code>LOAD DATA</code>.</p> <hr> <p>After OP's response on my answer I realized that what she/he wants to do isn't possible to do in a simple and clear manner.</p> <p>Basically the problem is that after a successful commit the data that was committed can't be rolled-back, because the operation is atomic. Given multiple commits are needed in the case given, rolling-back everything isn't possible unless one tracks <em>all</em> data (in all of the transactions) and if somethings happens deletes everything that was successfully committed.</p> <p>There is a <a href="https://stackoverflow.com/questions/4766667/can-i-roll-back-a-jta-transcation-after-i-commit-it">nice answer</a> relating to the issue of commits and rollbacks.</p>
    singulars
    1. This table or related slice is empty.
    1. POSingle transaction using multiple connections. (MYSQL/JDBC)
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USKohányi Róbert
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COSorry, i didn't get a chance to read this until now. This is very similar to what i am currently doing, and it does cover situations like SQL errors nicely.
      singulars
      1. PO
      1. USFilip
    2. COBut, it doesn't cover the situation where the ACTUAL COMMIT FAILS (unlikely, but possible). Try this: add a System.in.read() in the "for" in the commitConnections method (before doing the actual commit). Press enter for the first 2 commits, then stop the mysql server and press enter for the 3rd. The 3rd commit will fail, but the first 2 will have already been executed. This is what i'm trying to avoid.
      singulars
      1. PO
      1. USFilip
    3. CO@MasterF After a *commit*, what was committed can't be rolled-back. Unless you track what was committed and delete those rows after the commit. If you try to commit to totally separate tables then you will never receive errors like *duplicate id*, etc., so this isn't an issue. If you're connecting to a remote database there is the possibility that the link between you and the database breaks while the third `commit()` executes and thus will prevent data to propagate to the database from that *batch*. Basically, you want to break ACID rules in your transaction. That's not going to go smoothly.
      singulars
      1. PO
      1. USKohányi Róbert
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload