I implemented the in-memory database JDBC Driver

I made an in-memory database last year and I thought it was a powerful feature. The In-memory database is a stand-alone program that is accessed by the client through the socket, delivering the SQL statement and getting the results, so I have a client API for the C interface.

But if you want to be more versatile, you have to take care of the two largest groups of people in the IT world: Java Programmers and C # programmers. My colleague in C # encapsulates the ADO drive. These days I have implemented the JDBC driver, this article records some of the implementation process of the experience.

I am more good at the C + +, Java's long-winded grammar is not too cold, before the use of Java to write a small Android program, dare to challenge write a JDBC-driven task.

My development environment is a relatively small Mac OS X, just testing Java's so-called cross-platform.

The first step is to encapsulate the client API of the C interface into a Java class: Lxjdbapi.java

package Com.lxjdb.jdbc;public class Lxjdbapi {public Lxjdbapi () {system.loadlibrary ("Lxjdbjdbcapi");} Public native long Open (string host, int port, string user, string pwd);p ublic native void Close (Long conn);p Ublic native  int Exec (Long conn, String SQL, string[] dbInfo);  Executes an SQL statement public native int Rows (long conn);  Total rows Public native int Cols (long conn); Total columns//Get column information public native int Getcolinfobyindex (Long conn, int. Col, string[] retname, int[] lentypepos);p ublic native in  T Getcolinfobyname (Long conn, String name, int[] lentypepos);p ublic native int Next (long conn);  To the next record, the successful return 1, to the end of the recordset, 0, the error returns a negative number public native int Gotorec (long conn, int recNo); Record number starting from 1//Fetch field value: 1. Always returns string result, 2. String variable to be pre-allocated at least 2048 size of length public native int Lxjdbgetvalbyname (Long conn, string fieldName, String[] retVal);//column numbering starting from 0 public native int Getvalbyindex (long conn, int col, string[] retVal);} 

Then use Javah to generate the C language header file to compile JNI, the C file is stored in the JNI directory, under the terminal using the command line.

Javah-classpath bin-d JNI Com.lxjdb.jdbc.LxjDbApi

Here is the CPP code, which is called our C language API:

/* Don't EDIT this file-it are machine generated */#include "com_lxjdb_jdbc_lxjdbapi.h"/* Header for Class Com_lxjdb_jdb C_LXJDBAPI */#include "LxjDbApi.h"/* * CLASS:COM_LXJDB_JDBC_LXJDBAPI * Method:open * Signature: (Ljava/lang/strin g;iljava/lang/string; ljava/lang/string;) Ljava/lang/long; */jniexport jlong jnicall java_com_lxjdb_jdbc_lxjdbapi_open (jnienv * env, Jobject obj, jstring host, Jint Port, jstring us   Er, jstring pwd) {lxjdbinit ();   Const char* Phost = env->getstringutfchars (host, 0);   Const char* Puser = env->getstringutfchars (user, 0);   Const char* PPWD = env->getstringutfchars (pwd, 0);   void* conn = Lxjdbopen (Phost, Port, "", Puser, ppwd);   Env->releasestringutfchars (host, phost);   Env->releasestringutfchars (pwd, ppwd);   Env->releasestringutfchars (user, puser); Return ((JLONG) conn);} /* * CLASS:COM_LXJDB_JDBC_LXJDBAPI * method:close * Signature: (ljava/lang/long;) Ljava/lang/long; */jniexport Jin T Jnicall java_com_lxjdb_jdbc_lxjdbApi_close (JNIENV * env, Jobject obj, Jlong conn) {void* Pconn = (void*) conn;   int ret = Lxjdbclose (pconn); return (ret);} /** class:com_lxjdb_jdbc_lxjdbapi* method:exec* Signature: (jljava/lang/string;[ ljava/lang/string;) I*/jniexport jint jnicall java_com_lxjdb_jdbc_lxjdbapi_exec (jnienv * env, Jobject obj, Jlong conn,   Jstring sql, Jobjectarray dbInfo) {void* pconn = (void*) conn;   Const char* PSQL = env->getstringutfchars (sql, 0);   int retCode = 0;   Char pdbinfo[512];   Pdbinfo[0] = ' + ';   int ret = lxjdbexec (Pconn, PSQL, RetCode, Pdbinfo);   Env->releasestringutfchars (SQL, PSQL);   Env->setobjectarrayelement (dbInfo, 0, Env->newstringutf (pdbinfo)); Return (ret>0 retcode:ret);} /** class:com_lxjdb_jdbc_lxjdbapi* method:rows* Signature: (J) i*/jniexport jint jnicall java_com_lxjdb_jdbc_lxjdb   Api_rows (JNIENV * env, Jobject obj, Jlong conn) {void* Pconn = (void*) conn;  Return (Lxjdbrows (pconn)); Total Rows}/** class:com_lxjdb_jdbc_lxjdbapi*method:cols* Signature: (J) I*/jniexport jint jnicall java_com_lxjdb_jdbc_lxjdbapi_cols (jnienv * env, jobject obj, Jlon   G conn) {void* Pconn = (void*) conn;  Return (Lxjdbcols (pconn)); Total number of columns}/** class:com_lxjdb_jdbc_lxjdbapi* method:getcolinfobyindex* Signature: (ji[ljava/lang/string;[ I) I*/jniexport jint jnicall java_com_lxjdb_jdbc_lxjdbapi_getcolinfobyindex (jnienv * env, Jobject obj, Jlong Conn, Jint Co   L, Jobjectarray Retname, Jintarray lentypepos) {void* pconn = (void*) conn;   Char name[200];   Name[0] = ' + ';   int len=0;   int type=0;   int pos=0;  int ret = Lxjdbgetcolinfobyindex (pconn, col, name, Len, type, POS);   Find field Information env->setobjectarrayelement (retname, 0, Env->newstringutf (name)) based on the column index (starting at 0);   Jint *parr = env->getintarrayelements (Lentypepos, NULL);   Parr[0] = len;   PARR[1] = type;   PARR[2] = pos;   Env->releaseintarrayelements (Lentypepos, PARR, NULL); return (ret);} /** class:com_lxjdb_jdbc_lxjdbapi* method:getcolinfobyname* SignAture: (jljava/lang/string;[ I) I*/jniexport jint jnicall java_com_lxjdb_jdbc_lxjdbapi_getcolinfobyname (jnienv * env, Jobject obj, Jlong conn, jstring   Name, Jintarray lentypepos) {void* pconn = (void*) conn;   Const char* PName = env->getstringutfchars (name, 0);   int len = 0;   int type = 0;   int pos = 0;   int ret = Lxjdbgetcolinfobyname (Pconn, PName, Len, type, POS);   Env->releasestringutfchars (name, pName);   Jint *parr = env->getintarrayelements (Lentypepos, NULL);   Parr[0] = len;   PARR[1] = type;   PARR[2] = pos;   Env->releaseintarrayelements (Lentypepos, PARR, NULL); return (ret);} /** class:com_lxjdb_jdbc_lxjdbapi* method:next* Signature: (J) i*/jniexport jint jnicall java_com_lxjdb_jdbc_lxjdb   Api_next (JNIENV * env, Jobject obj, Jlong conn) {void* Pconn = (void*) conn;  Return (Lxjdbnext (pconn)); Next line}/** class:com_lxjdb_jdbc_lxjdbapi* method:gotorec* Signature: (JI) i*/jniexport jint jnicall java_com_lxjdb _jdbc_lxjdbapi_gotorec (JNIENV * env, JoBject obj, Jlong conn, jint recNo) {void* pconn = (void*) conn;  Return (Lxjdbgotorec (Pconn, recNo)); To the specified line}/** class:com_lxjdb_jdbc_lxjdbapi* method:lxjdbgetvalbyname* Signature: (jljava/lang/string;[ ljava/lang/string;) I*/jniexport jint jnicall java_com_lxjdb_jdbc_lxjdbapi_lxjdbgetvalbyname (JNIENV * env, Jobject obj   , Jlong Conn, jstring name, Jobjectarray retVal) {void* pconn = (void*) conn;   Const char* PName = env->getstringutfchars (name, 0);   Char val[2048];   Val[0] = ' + ';   int ret = Lxjdbgetvalbyname (Pconn, PName, Val);   Env->releasestringutfchars (name, pName);   Env->setobjectarrayelement (retVal, 0, Env->newstringutf (val)); return (ret);} /** class:com_lxjdb_jdbc_lxjdbapi* method:getvalbyindex* Signature: (ji[ljava/lang/string;) I*/jniexport Jint JNIC All Java_com_lxjdb_jdbc_lxjdbapi_getvalbyindex (JNIENV * env, Jobject obj, Jlong conn, Jint Col, Jobjectarray retVal) {VO   id* pconn = (void*) conn;   Char val[2048];   Val[0] = ' + '; int ret = Lxjdbgetvalbyindex (Pconn, Col, Val);   Env->setobjectarrayelement (retVal, 0, Env->newstringutf (val)); return (ret);}

Then construct a makefile:

libflag=-lstdc++-lpthread-ldlcppflags =-c-fpic-i/library/java/javavirtualmachines/jdk1.8.0_05.jdk/contents/home /include-i/SYSTEM/LIBRARY/FRAMEWORKS/JAVAVM.FRAMEWORK/VERSIONS/A/HEADERSCOMMON_OBJS = com_lxjdb_jdbc_ Lxjdbapi.osharelib =-llxjdbapi      liblxjdbjdbcapi: $ (COMMON_OBJS) gcc $ (COMMON_OBJS) $ (libflag) $ (sharelib)  - Rdynamic-dynamiclib-fpic-install_name/usr/lib/liblxjdbjdbcapi.dylib-o Liblxjdbjdbcapi.dylib

Make will generate a shared library under OS X:

Liblxjdbjdbcapi.dylib

Note that because of the special use of the shared library by the Java JNI under the Mac, you need to rename the file to Liblxjdbjdbcapi.jnilib and copy it to the/usr/lib/java directory.

The second step is to implement each interface class:

1, Driver class

Note Be sure to override the Jdbccompliant () method and return False to indicate that we do not need full compatibility to implement all JDBC interfaces, as long as the necessary parts are implemented.

The main implementation of the Connect () method, the code is as follows:

Package Com.lxjdb.jdbc;import Java.sql.*;import Java.util.*;import java.util.logging.logger;public class Driver Implements Java.sql.driver{public Lxjdbapi Lxjdb;static{try{java.sql.drivermanager.registerdriver (New Driver ());} catch (SQLException e) {//System.err.println (e); throw new RuntimeException ("Can ' t Register driver!");}}  Public Driver () throws SQLException {//Required for Class.forName (). newinstance () Lxjdb = new Lxjdbapi ();}  public boolean acceptsurl (String url) throws Sqlexception{return url.startswith ("jdbc:lxjdb://");}  Public Connection connect (String URL, Properties info) throws sqlexception{if (!acceptsurl (URL)) {return null; }//To decompose the URL to get the host address and port number, and from info get username and password try {string[] Arr=url.split ("//"); String url2 = arr[1]; String[] Arr2=url2.split (":"); String host = Arr2[0];int port = integer.parseint (arr2[1]); String user = Info.getproperty ("user"); String pwd = info.getproperty ("password"); return new Lxjdbconnection (LXJDB, host, port, user, PWD);} catch (Exception e) {THrow New SQLException (E.getmessage ());}} public Boolean jdbccompliant () {return false;} Public driverpropertyinfo[] getpropertyinfo (String URL, Properties info) throws SQLException {//TODO auto-generated Method Stubreturn new driverpropertyinfo[0];} public int getmajorversion () {//TODO auto-generated method Stubreturn 1;} public int getminorversion () {//TODO auto-generated method Stubreturn 0;} Public Logger Getparentlogger () throws Sqlfeaturenotsupportedexception {//TODO auto-generated method Stubreturn null;}}

2, Connection Class

is mainly to achieve createstatement () method:

Package Com.lxjdb.jdbc;import Java.sql.*;import Java.util.map;import java.util.properties;import Java.util.concurrent.executor;public class Lxjdbconnection implements Java.sql.connection{public long Conn=0;public Lxjdbapi lxjdb;public lxjdbconnection (Lxjdbapi plxjdb, string host, int port, string user, string pwd) throws SQLException {lxjdb = Plxjdb;conn = Lxjdb. Open (host, port, user, PWD), if (conn==0) {String info = "Open Err:" +host+ ":" +port+ ", user=" +user;throw New SQLException ( info); }}public Statement createstatement () throws SQLException {return new lxjdbstatement (this);}}

3.Statement Class
The main implementation of two methods, one is to execute no result set returned executeupdate () method, such as Execute update, delete, insert a class of SQL statements;

the second is to execute the SELECT query statement ResultSet executeQuery (String sql) method, it is clear that this method will return the result set.

Package Com.lxjdb.jdbc;import java.sql.*;p ublic class Lxjdbstatement implements Java.sql.Statement {public Lxjdbconnection conntion;public lxjdbapi lxjdb;public long conn;public lxjdbstatement (LxjDbConnection pconn) {conntion = Pconn;lxjdb = Pconn.lxjdb;conn = Pconn.conn;} Public ResultSet executeQuery (String sql) throws SQLException {string[] dbInfo = new String[1];int ret = lxjdb. EXEC (conn, SQL, dbInfo); if (ret<0) {throw new SQLException (Dbinfo[0]);} return new Lxjdbresultset (conntion);} public int executeupdate (String sql) throws SQLException {string[] dbInfo = new String[1];int ret = lxjdb. EXEC (conn, SQL, dbInfo); if (ret<0) {throw new SQLException (Dbinfo[0]);} return ret;}}

4.ResultSet class
Process the result set. Our in-memory database is simplified and the returned data are string types, so as long as the implementation of GetString (), what other Getint (), Getlong (), getdouble () and other data types are not implemented.

Of course, there are some ways to implement moving result set cursor row pointers.

Package Com.lxjdb.jdbc;import Java.io.inputstream;import Java.io.reader;import java.math.bigdecimal;import Java.net.url;import java.sql.*;import Java.util.calendar;import Java.util.map;public class LxjDbResultSet implements Java.sql.ResultSet {public Lxjdbapi lxjdb;public long conn;public lxjdbresultset (lxjdbconnection pconn) {lxjdb = Pconn.lxjdb;conn = Pconn.conn;} public Boolean Next () throws SQLException {int ret = lxjdb. NEXT (conn); return ret>0? True:false;} public Boolean first () throws SQLException {if (lxjdb. Rows (conn) <1) return false;int ret = lxjdb. GOTOREC (conn, 1); return ret>0? True:false;} public Boolean last () throws SQLException {int r = lxjdb. Rows (conn); if (r<1) return false;int ret = lxjdb. GOTOREC (conn, R); return ret>0? True:false;} Public String getString (int columnindex) throws SQLException {string[] retVal = new string[1];lxjdb.  GETVALBYINDEX (conn, columnIndex-1, RetVal); ColumnIndex is a return from 1 retval[0];} public string getString (string ColumnLabel) tHrows SQLException {string[] retVal = new string[1];lxjdb. Lxjdbgetvalbyname (conn, ColumnLabel, RetVal); return retval[0];} public int getfetchsize () throws SQLException {return lxjdb. Rows (conn);}}

The third step is to export the jar file after compiling:

in the under Eclipse, right-click on the project and select "Export ..." to export the Lxjdbjdbc.jar.

This enables the JDBC driver to be implemented successfully.

You can test it again below.

Fourth step, test:

The test code is relatively simple and does not have to be explained too much:

Import java.sql.*;p Ublic class Jdbctest {public static void main (string[] args) {String driver = "Com.lxjdb.jdbc.Driver";        String userName = "sa";        String Passwrod = "********";        String url = "jdbc:lxjdb://192.168.0.106:2013";         String sql = "SELECT * from Onlineuser";                    try {System.out.println ("path:[" +system.getproperties (). Get ("Java.library.path") + "]");            Class.forName (driver);            Connection conn = drivermanager.getconnection (URL, userName, passwrod);                          Statement stmt = Conn.createstatement (); int ret = stmt.executeupdate ("INSERT into Onlineuser (UserId, DevId, Addr, Lasttime, Expires) VALUES (' 9999 ', ' mac ', '            192.168.0.106:888 ', GETDATE (), 2000) ");            System.out.println ("executeupdate:" +ret);            ResultSet rs = stmt.executequery (SQL);                     while (Rs.next ()) {System.out.println ("userId:" + rs.getstring (1) + "Dev:"   + rs.getstring (2) + "addr:" + rs.getstring ("addr") + "time:" + rs.getstring (4));                }//Close Recordset if (rs! = null) {try {rs.close ();                } catch (SQLException e) {e.printstacktrace ();                }}//Close the link object if (conn! = null) {try {conn.close ();                } catch (SQLException e) {e.printstacktrace ();        }}} catch (Exception e) {e.printstacktrace (); }}}

Note that in the project to correctly set the location of the Lxjdbjdbc.jar, right-click the project in the "Build path", and then select "Configure build Path" of the librares tag, point "Add externd JARs ..." button, willThe Lxjdbjdbc.jar is added to the project to run and the results are completely correct.

(The Blue Star Memory database is free, welcome to the trial.) The Blu-Star database is extremely fast, has faster insertion speed, is very stable, and supports the snapshot feature, which does not lose data when power is restarted. Support various operating systems WINDOWS,MAC OS X,linux, etc., do not need to install, run directly. ）

I implemented the in-memory database JDBC Driver