jPMdbc - Java to PHP to MySQL DataBase Connector

... a jdbc driver for using a regular mysql-database behind a webserver/webspace

Today, every webhoster provides you a (mysql) database for running a content-management-system or somethings else db-realated. In most cases, the database is only accessible via localhost. So using it outside the webserver is not possible. Really? No. With the jPMdbc jdbc driver you are now able to use the database for any java program that has web-access.

The only thing you need to do: place the jpmdbc.php on your webserver which has access to the database and use the jPMdbc jdbc driver. That's all.

Features

  • the most needed jdbc-interface-methods are implemented
  • works with proxy (and with proxy auth)
  • uses the standard webserverport 80 (or 443 for using https), so there's no firewall-problem at all
  • works with mysql 4 and 5
  • Java 6 compatible (sorry for the java 1.5 users out there)
  • db-host and port configurable
  • interface for a progress-listener, i.e. if you have large query-results and want to have a progress-bar showing the current download-process.
  • gzip data compression for faster communication (if supported by webserver)
  • secure communication due to HTTPS support (see: Howto use SSL with jPMdbc)

Things to be done (in random order)

  • implement more jdbc-interface-methods
  • more exception handling
  • make it more stable
  • add some encryption for people who cannot use HTTPS

Sample code

Here is a small example on how to use jPMdbc. You see, it's quite simple. Just like using any other jdbc driver...

Speedtests

Okay, here's something about the speed of some example-querys (without data compression!):

Database used MySQL Server 4.1.11
Serverside MySQL client for PHP MySQL Client 4.0.24
Tables in Database 93
Internetconnection DSL: 6000kbit down / 512kbit up
Webserver Apache 2.0.54
PHP PHP 4.3.10-19

Testtable #1:

used tablestorage engine InnoDB
rows in table 2699
columns in table 30
size 13.7 MB
query SELECT * FROM XYZ
query duration ~17 seconds @ ~690kByte/sec

Testtable #2:

used tablestorage engine MyISAM
rows in table 85624
columns in table 19
size 6.7 MB
query SELECT * FROM ABC
query duration ~14 seconds @ ~690kByte/sec

The query duration depends on two things:

  1. the time that mysql needs for executing the query (mostly very fast, < 1sec)
  2. the output of the resultset via php and read with java.

You will see that 1.) doesn't matter at all. The faster your download-rate of your internet connection is, the faster you get the query-result. Same of course for updates like INSERT, UPDATE or DELETE ...

Try it ....

Download the source and run Maven pom.xml. Move the created JAR to your library-path of your project and add it to you build-path. Then just use it like in the example shown above. And, of course, leave me a message if you find it useful or not :-)

Or you just download the latest build from here: http://jenkins.root1.de/job/jPMdbc%20-%20trunk/lastStableBuild/de.root1$jpmdbc/

Sample Code

jPMdbc uses almost only plain jdbc. There are only a few things which are different/in addition to jdbc. Have a look at a full sample here: http://svn.root1.de/svn/jpmdbc/branches/0.1/src/de/root1/jpmdbc/testing/

Maven

To use jPMdbc "the maven way", adapt your pom.xml to the following:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    ...

    <dependencies>
        ...  
        <dependency>
            <groupId>de.root1</groupId>
            <artifactId>jpmdbc</artifactId>
            <version>1.0.0-SNAPSHOT</version>
        </dependency>
    </dependencies>

    <repositories>      
        ...  
       <repository>
            <id>root1-releases</id>
            <name>root1.de Releases Repository</name>
            <layout>default</layout>
            <url>http://nexus.root1.de/content/repositories/releases/</url>
            <snapshots>
                <enabled>false</enabled>
            </snapshots>
        </repository>
       <repository>
            <id>root1-snapshots</id>
            <name>root1.de Snapshots Repository</name>
            <layout>default</layout>
            <url>http://nexus.root1.de/content/repositories/snapshots/</url>
            <snapshots>
                <enabled>true</enabled>
            </snapshots>
        </repository>
    </repositories>

</project>

The jpmdbc.php file is available in the distribution-archive and must be downloaded separately:

http://nexus.root1.de/content/repositories/snapshots/de/root1/jpmdbc/1.0.0-SNAPSHOT/ --> Download latest "jpmdbc-1.0.0-{yyyymmdd}.{hhmmss}-{buildnr}-distribution.zip"