I implemented the JDBC driver for the memory database

I implemented the JDBC driver for the memory database

I made a memory database last year and thought it was very powerful. The memory database is a program that runs independently. The client accesses the database through socket, transmits SQL statements, and obtains the results. Therefore, I provide a client API for the C interface.

But to achieve better universality, we must take care of the two most common types of it: java programmers and c # programmers. C # My colleagues encapsulate the ado.net driver. I have implemented the jdbc driver over the past few days. This article records some experiences in the implementation process.

I am very good at C/C ++, and I am not very familiar with java's syntax. I used java to write a small-sized android program a while ago, to challenge writing a jdbc driver task.

My development environment is a relatively small number of mac OS x, just to test the so-called cross-platform java.

Step 1: 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); public native void Close (long conn ); public native int Exec (long conn, String SQL, String [] dbInfo); // execute an SQL statement public native int Rows (long conn ); // total number of rows public native int Cols (long conn); // total number of columns // obtain the column information public native int GetColInfoByIndex (long conn, int col, String [] retName, int [] lenTypePos); public native int GetColInfoByName (long conn, String name, int [] lenTypePos); public native int Next (long conn); // to the Next record, 1 is returned successfully. When the record set ends, 0 is returned. Negative public native int GotoRec (long conn, int recNo) is returned ); // record number starts from 1 // obtain the field value: 1. always Returns string results, 2. the String variable must be pre-allocated with at least 2048 length public native int LxjDbGetValByName (long conn, String fieldName, String [] retVal ); // The column number starts from 0. public native int GetValByIndex (long conn, int col, String [] retVal );}

Then, use javah to generate the header file of c language to compile jni, store the c file in the jni directory, and use the command line in the terminal.

Javah-classpath bin-d jni com. lxjdb. jdbc. LxjDbApi

The following is the cpp Code that calls our C language api:

/* Do not edit this file-it is machine generated */# include "com_lxjdb_jdbc_LxjDbApi.h"/* Header for class com_lxjdb_jdbc_LxjDbApi */# include "LxjDbApi. h "/** Class: com_lxjdb_jdbc_LxjDbApi * Method: Open * Signature: (Ljava/lang/String; 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 p Ort, jstring user, 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 jint JNICALL inline (JNIEnv * env, jobject, 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] = '\ 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 trim (JNIEnv * env, jobject obj, jlong conn) {void * pConn = (void *) conn; return (LxjDbRows (pConn); // total number of rows}/** Class: com_lxjdb_jdbc_LxjDbApi * Method: Cols * Signature: (J) I */JNIEXPORT jint JNICALL inline (JNIEnv * env, jobject obj, jlong 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 inline (JNIEnv * env, jobject obj, jlong conn, jint col, jobjectArray retName, jintArray lenTypePos) {void * pConn = (void *) conn; char name [200]; name [0] = '\ 0'; int len = 0; int type = 0; int pos = 0; int ret = LxjDbGetColInfoByIndex (pConn, col, name, len, type, pos); // locate the field information based on the column index (starting from 0). env-> SetObjectArrayElement (retName, 0, env-> NewStringUTF (name )); jint * pArr = env-> GetIntArrayElements (lenTypePos, NULL); pArr [0] = len; pArr [1] = type; pArr [2] = pos; env-> ReleaseIntArrayElements (lenTypePos, pArr, NULL); return (ret);}/** Class: Using * Method: GetColInfoByName * Signature: (JLjava/lang/String; [I) I */JNIEXPORT jint JNICALL inline (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: pipeline * Method: Next * Signature: (J) I */JNIEXPORT jint JNICALL struct (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 inline (JNIEnv * env, jobject obj, jlong conn, jint recNo) {void * pConn = (void *) conn; return (LxjDbGotoRec (pConn, recNo); // to the specified row}/** Class: com_lxjdb_jdbc_LxjDbApi * Method: LxjDbGetValByName * Signature: (JLjava/lang/String; [Ljava/lang/String ;) I */JNIEXPORT jint JNICALL inline (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] = '\ 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 JNICALL inline (JNIEnv * env, jobject, jlong conn, jint col, jobjectArray retVal) {void * pConn = (void *) conn; char val [2048]; val [0] = '\ 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: Because java's jni uses a shared library on mac, you need to rename the file libLxjDbJdbcApi. jnilib and copy it to the/usr/lib/java directory.

Step 2: implement various interface classes:

1. Driver Class

Note that you must override the jdbcCompliant () method and return false, indicating that we do not need to be fully compatible with all jdbc interfaces, as long as necessary.

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;} // you need to break down the url to get the host address and port number, and get the username and password from info 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

The createStatement () method is mainly implemented:

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

Two methods are implemented: Execute the executeUpdate () method that is not returned by the result set, such as executing SQL statements such as update, delete, and insert;

The second is the ResultSet executeQuery (String SQL) method that executes the select query statement. Obviously, this method returns the result set.

package com.lxjdb.jdbc;import java.sql.*;public 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

Processing result set. Our memory database is relatively simplified, and the returned data is of the string type. Therefore, we only need to implement getString (), other getInt (), getLong (), getDouble () and other data types will not be implemented.

Of course, we also need to implement some methods to move the result set cursor row pointer.

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 SQLEx Ception {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 the return retVal [0];} public String getString (String columnLabel) from 1) throws SQLException {String [] retVal = new String [1]; lxjdb. lxjDbGetValByName (conn, columnLabel, retVal); return retVal [0];} public int getFetchSize () throws SQLException {return lxjdb. rows (conn );}}

Step 3: Compile and export it as a jar file:

Right-click the Eclipse project, select "Export...", and Export LxjDbJdbc. jar.

In this way, the jdbc driver is successfully implemented.

Next we can perform the test.

Step 4: test:

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

Import java. SQL. *; public 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 = DriverM Anager. getConnection (url, userName, passwrod); Statement stmt = conn. createStatement (); int ret = stmt.exe cuteUpdate ("insert into OnlineUser (UserId, DevId, Addr, LastTime, Expires) values ('20140901', 'mac', '123. 168.0.106: 888 ', getdate (), 2000) "); System. out. println ("executeUpdate:" + ret); ResultSet rs = stmt.exe cuteQuery (SQL); while (rs. next () {System. out. println ("userId:" + rs. getString (1) + "dev: "+ Rs. getString (2) + "addr:" + rs. getString ("Addr") + "time:" + rs. getString (4);} // close the record set 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: You must set LxjDbJdbc correctly in the project. jar location, right-click the "Build Path" menu in the project, select the "Configure Build Path" Librares tag, and click "Add Externd JARs... "button to change LxjDbJdbc. jar is added to the project to run it. The result is completely correct.