{"id":2897,"date":"2012-12-24T13:25:35","date_gmt":"2012-12-24T12:25:35","guid":{"rendered":"http:\/\/www.gamlor.info\/wordpress\/?p=2897"},"modified":"2022-01-23T22:24:06","modified_gmt":"2022-01-23T21:24:06","slug":"state-of-asynchronous-database-access-for-java","status":"publish","type":"post","link":"https:\/\/www.gamlor.info\/wordpress\/2012\/12\/state-of-asynchronous-database-access-for-java\/","title":{"rendered":"State of Asynchronous Database Access for Java"},"content":{"rendered":"<p>It has been a while since I wrote any serious technical stuff. Previously I&#8217;ve already written a bit about <a href=\"https:\/\/www.gamlor.info\/wordpress\/2012\/05\/async-sql-and-akka\/\">asynchronous database<\/a> access.<\/p>\n<p>I&#8217;ve been continuing work on this, mainly on the basics, the <a href=\"https:\/\/github.com\/gamlerhart\/adbcj\">ADBCJ<\/a> driver implementation . Here&#8217;s a overview of the updates:<\/p>\n<ul>\n<li><a href=\"#connection-pool\">Simple connection pool<\/a>: This helps to avoid the costs of creating connections. It also gives a nice control how many connections are used.<\/li>\n<li><a href=\"#h2-support\">H2-Support<\/a>: Implemented a driver for the <a href=\"http:\/\/www.h2database.com\/html\/main.html\">H2<\/a> database<\/li>\n<li><a href=\"#mysql-synchronisation\">MySQL Driver Restructuring<\/a>: The driver has been massively simplified, mainly in term of synchronization. This makes it easier to debug and reason about it.<\/li>\n<li>DBFuture implementation moved to CAS operations instead of locks: Less overhead, simplifies implementation and avoids potential dead locks<\/li>\n<li>Dropped PostgreSQL driver for now: I never maintained that driver, so I dropped it for now. If someone has interrest in a PostgreSQL driver, talk to me.<\/li>\n<li>Countless bugfixes<\/li>\n<\/ul>\n<div id=\"attachment_2923\" style=\"width: 310px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/www.gamlor.info\/wordpress\/wp-content\/uploads\/2012\/12\/h2-database.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-2923\" class=\"size-medium wp-image-2923\" alt=\"H2? What? Ah, the database.\" src=\"https:\/\/www.gamlor.info\/wordpress\/wp-content\/uploads\/2012\/12\/h2-database-300x158.png\" width=\"300\" height=\"158\" srcset=\"https:\/\/www.gamlor.info\/wordpress\/wp-content\/uploads\/2012\/12\/h2-database-300x158.png 300w, https:\/\/www.gamlor.info\/wordpress\/wp-content\/uploads\/2012\/12\/h2-database.png 800w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/a><p id=\"caption-attachment-2923\" class=\"wp-caption-text\">H2? What? Ah, the database.<\/p><\/div>\n<h2><a name=\"connection-pool\"><\/a>Connection Pool<\/h2>\n<p>In the JDBC world connection pools are the standard. And this has it&#8217;s reasons. Connection to a database needs some initial work. With a connection pool this overhead is avoided. Plus it gives you control how many connections are used. ADBCJ has now a connection-pool too.<\/p>\n<p>First you need to add the pool implementation to your project:<br \/>\n<script src=\"https:\/\/gist.github.com\/4349067.js?file=build.sbt\"><\/script><noscript><pre><code class=\"language-scala scala\">libraryDependencies += &quot;org.adbcj&quot; % &quot;adbcj-connection-pool&quot; % &quot;0.5-SNAPSHOT&quot;<\/code><\/pre><\/noscript><br \/>\n<script src=\"https:\/\/gist.github.com\/4349067.js?file=pom.xml\"><\/script><noscript><pre><code class=\"language-maven pom maven pom\">&lt;dependency&gt;\n\t&lt;groupId&gt;org.adbcj&lt;\/groupId&gt;\n\t&lt;artifactId&gt;adbcj-connection-pool&lt;\/artifactId&gt;\n\t&lt;version&gt;0.5-SNAPSHOT&lt;\/version&gt;\n&lt;\/dependency&gt;<\/code><\/pre><\/noscript><\/p>\n<p>After that you add the prefix <em>pooled<\/em> to the connection string. This will load the ADBCJ connection pool. For examples:<br \/>\n<script src=\"https:\/\/gist.github.com\/4349067.js?file=ConnectionStrings.java\"><\/script><noscript><pre><code class=\"language-java java\">ConnectionManager plainMysql \n      = ConnectionManagerProvider.createConnectionManager(\n            &quot;adbcj:mysql:\/\/localhost\/adbcjtck&quot;, &quot;root&quot;, &quot;&quot;);\n\nConnectionManager pooledMysql \n      = ConnectionManagerProvider.createConnectionManager(\n            &quot;adbcj:pooled:mysql:\/\/localhost\/adbcjtck&quot;, &quot;root&quot;, &quot;&quot;);<\/code><\/pre><\/noscript><\/p>\n<p>The connection pool can be configured by providing properties for it. Here are the supported properties with the defaults:<br \/>\n<script src=\"https:\/\/gist.github.com\/4349067.js?file=ConfigProperties.java\"><\/script><noscript><pre><code class=\"language-java java\">Map&lt;String,String&gt; poolConfig = new HashMap&lt;String, String&gt;();\npoolConfig.put(&quot;pool.maxConnections&quot;,&quot;50&quot;);\npoolConfig.put(&quot;pool.maxWaitForConnection&quot;,&quot;500&quot;);\n\nConnectionManager pooledMysql\n  \t= ConnectionManagerProvider.createConnectionManager(\n  \t\t&quot;adbcj:pooled:mysql:\/\/localhost\/adbcjtck&quot;,\n\t\t&quot;root&quot;,\n\t\t&quot;&quot;,\n\t\tpoolConfig);<\/code><\/pre><\/noscript><\/p>\n<h2><a name=\"h2-support\"><\/a>H2-Support<\/h2>\n<p>I really like the H2 database. It&#8217;s simply to handle, embed, run and operate. I often use it for smaller projects, testing, default embedded database and so on. So no wonder, I&#8217;ve added support for it. You can add it via Maven\/sbt.<\/p>\n<p>First you need to add the driver to your project:<br \/>\n<script src=\"https:\/\/gist.github.com\/4349067.js?file=buildH2.sbt\"><\/script><noscript><pre><code class=\"language-scala scala\">libraryDependencies += &quot;org.adbcj&quot; % &quot;h2-async-driver&quot; % &quot;0.5-SNAPSHOT&quot;<\/code><\/pre><\/noscript><br \/>\n<script src=\"https:\/\/gist.github.com\/4349067.js?file=pomH2.xml\"><\/script><noscript><pre><code class=\"language-xml xml\">&lt;dependency&gt;\n  &lt;groupId&gt;org.adbcj&lt;\/groupId&gt;\n\t&lt;artifactId&gt;h2-async-driver&lt;\/artifactId&gt;\n\t&lt;version&gt;0.5-SNAPSHOT&lt;\/version&gt;\n&lt;\/dependency&gt;<\/code><\/pre><\/noscript><\/p>\n<p>And then you can use the driver:<br \/>\n<script src=\"https:\/\/gist.github.com\/4349067.js?file=H2.java\"><\/script><noscript><pre><code class=\"language-java java\">\/\/ Plain connection\nConnectionManager h2 \n       = ConnectionManagerProvider.createConnectionManager(\n       \t  &quot;adbcj:h2:\/\/localhost:14242\/adbcjtck;MVCC=TRUE&quot;,\n  \t  &quot;sa&quot;,\n\t  &quot;sa&quot;);\n\n\/\/ With connection pool, recommended\nConnectionManager h2Pooled \n       = ConnectionManagerProvider.createConnectionManager(\n       \t   &quot;adbcj:pooled:h2:\/\/localhost:14242\/adbcjtck;MVCC=TRUE&quot;,\n\t   &quot;sa&quot;,\n\t   &quot;sa&quot;);<\/code><\/pre><\/noscript><\/p>\n<h2><a name=\"mysql-synchronisation\"><\/a>Restructuring Synchronization of MySQL driver<\/h2>\n<p>The synchronization of the requests and running operations in the MySQL driver was quite complex. This caused a lot of head aches fixing race conditions and finding errors. I&#8217;ve rewritten the internal synchronization of the MySQL driver to be simpler and more understandable.<br \/>\nAnother side effect is, that the driver now pipelines request much more aggressively. This should improve performance in some scenario drastically. The cost of this is that most requests cannot be cancelled anymore for now.<\/p>\n<h2>Source, etc.<\/h2>\n<p>The source is still on GitHub (<a href=\"https:\/\/github.com\/gamlerhart\/adbcj\">https:\/\/github.com\/gamlerhart\/adbcj<\/a>). Also remember that I&#8217;ve a improved <a href=\"https:\/\/www.gamlor.info\/wordpress\/2012\/05\/async-sql-and-akka\/\">Scala-API here<\/a>.<\/p>\n<p>The stuff is on my Maven-Repo:<br \/>\n<a href=\"https:\/\/github.com\/gamlerhart\/gamlor-mvn\/raw\/master\/snapshots\">https:\/\/github.com\/gamlerhart\/gamlor-mvn\/raw\/master\/snapshots<\/a><\/p>\n<p>Group-ID and Version for all ADBCJ projects:<br \/>\n<code>GroupID: org.adbcj<br \/>\nVersion: 0.5.0-SNAPSHOT<\/code><\/p>\n<p>API:<br \/>\n<code>ArtifactID: adbcj-api<\/code><\/p>\n<p>Mysql:<br \/>\n<code>ArtifactID: mysql-async-driver<\/code><\/p>\n<p>H2:<br \/>\n<code>ArtifactID: h2-async-driver<\/code><\/p>\n<p>Connection-Pool:<br \/>\n<code>ArtifactID: adbcj-connection-pool<\/code><\/p>\n<p>That&#8217;s it for now =)<\/p>\n","protected":false},"excerpt":{"rendered":"<p>It has been a while since I wrote any serious technical stuff. Previously I&#8217;ve already written a bit about asynchronous database access. I&#8217;ve been continuing work on this, mainly on&hellip; <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","_links_to":"","_links_to_target":""},"categories":[268,15,187],"tags":[291,200,295],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.gamlor.info\/wordpress\/wp-json\/wp\/v2\/posts\/2897"}],"collection":[{"href":"https:\/\/www.gamlor.info\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.gamlor.info\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.gamlor.info\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.gamlor.info\/wordpress\/wp-json\/wp\/v2\/comments?post=2897"}],"version-history":[{"count":23,"href":"https:\/\/www.gamlor.info\/wordpress\/wp-json\/wp\/v2\/posts\/2897\/revisions"}],"predecessor-version":[{"id":3921,"href":"https:\/\/www.gamlor.info\/wordpress\/wp-json\/wp\/v2\/posts\/2897\/revisions\/3921"}],"wp:attachment":[{"href":"https:\/\/www.gamlor.info\/wordpress\/wp-json\/wp\/v2\/media?parent=2897"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.gamlor.info\/wordpress\/wp-json\/wp\/v2\/categories?post=2897"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.gamlor.info\/wordpress\/wp-json\/wp\/v2\/tags?post=2897"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}