我实现的内存数据库JDBC驱动
我去年做了个内存数据库,自以为功能很强大。内存数据库是独立运行的程序,客户端通过socket访问,传送SQL语句并得到结果,为此我提供了一个C接口的客户端API。
但如果要做到更好的通用性,必须照顾IT世界数量最多的两类人:java程序员和c#程序员。c#我的同事封装了ado.net驱动。这几天我实现了jdbc的驱动,本文记录实现过程的一些心得。
本人较为擅长的是C/C++,对java的啰嗦语法实在不太感冒,前阵子用java写过一个规模不大的android程序,才敢挑战写一个jdbc驱动的任务。
我的开发环境是比较小众的mac os x,正好考验一下java所谓的跨平台。
第一步,先将C接口的客户端API封装成一个java类: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); // 执行一个sql语句 public native int Rows(long conn); // 总行数 public native int Cols(long conn); // 总列数 // 得到列信息 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); // 到下一条记录,成功返回1,到记录集结束,则为0,错误返回负数 public native int GotoRec(long conn, int recNo); // 记录号从1开始 // 取字段值:1.总是返回字符串结果,2.字符串变量要预先分配至少2048大小的长度 public native int LxjDbGetValByName(long conn, String fieldName, String[] retVal); // 列编号从0开始 public native int GetValByIndex(long conn, int col, String[] retVal); }
然后用javah生成c语言的头文件来编译jni,将c文件存放在jni目录下,在终端下使用命令行。
javah -classpath bin -d jni com.lxjdb.jdbc.LxjDbApi
下面是cpp代码,是调用我们的c语言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 port, 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 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] = '\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_LxjDbApi_Rows (JNIEnv * env, jobject obj, jlong conn) { void* pConn = (void*)conn; return( LxjDbRows(pConn) ); // 总行数 } /* * Class: com_lxjdb_jdbc_LxjDbApi * Method: Cols * Signature: (J)I */ JNIEXPORT jint JNICALL Java_com_lxjdb_jdbc_LxjDbApi_Cols (JNIEnv * env, jobject obj, jlong conn) { void* pConn = (void*)conn; return(LxjDbCols(pConn)); // 总列数 } /* * 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 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); // 根据列索引(从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: 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_LxjDbApi_Next (JNIEnv * env, jobject obj, jlong conn) { void* pConn = (void*)conn; return(LxjDbNext(pConn)); // 下一行 } /* * 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)); // 到指定行 } /* * 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] = '\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 Java_com_lxjdb_jdbc_LxjDbApi_GetValByIndex (JNIEnv * env, jobject obj, 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); }
再构造一个makefile:
LIBFLAG= -lstdc++ -lpthread -ldl CPPFLAGS = -c -fPIC -I /Library/Java/JavaVirtualMachines/jdk1.8.0_05.jdk/Contents/Home/include -I /System/Library/Frameworks/JavaVM.framework/Versions/A/Headers COMMON_OBJs = com_lxjdb_jdbc_LxjDbApi.o SHARELIB = -lLxjDbApi libLxjDbJdbcApi: $(COMMON_OBJs) gcc $(COMMON_OBJs) $(LIBFLAG) $(SHARELIB) -rdynamic -dynamiclib -fPIC -install_name /usr/lib/libLxjDbJdbcApi.dylib -o libLxjDbJdbcApi.dylib
make后将生成一个os x下的共享库:
libLxjDbJdbcApi.dylib注意,因为java的jni在mac下使用共享库比较特别,需要将该文件改名为libLxjDbJdbcApi.jnilib并拷贝到/usr/lib/java目录下。
第二步,实现各接口类:
1、Driver类
注意务必要重写jdbcCompliant()方法并返回false,表示我们不需要全面兼容实现所有的jdbc接口,只要实现必须的部分。
主要实现connect()方法,代码如下:
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; } // 要分解url得到主机地址和端口号,并从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 stub return new DriverPropertyInfo[0]; } public int getMajorVersion() { // TODO Auto-generated method stub return 1; } public int getMinorVersion() { // TODO Auto-generated method stub return 0; } public Logger getParentLogger() throws SQLFeatureNotSupportedException { // TODO Auto-generated method stub return null; } }
2、Connection类
主要是实现createStatement()方法:
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); } }
主要实现两个方法,一是执行没有结果集返回的executeUpdate()方法,如执行update, delete, insert一类的sql语句;
二是执行select查询语句的ResultSet executeQuery(String sql)方法,显然,这个方法将返回结果集。
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; } }
处理结果集。我们的内存数据库比较简化,返回的数据都是字符串类型,因此只要实现getString(),其它什么getInt(), getLong(), getDouble()之类的数据类型就不予实现了。
当然,还要实现移动结果集游标行指针的一些方法。
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是从1开始的 return 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); } }
第三步,编译后导出为jar文件:
在Eclipse下对准项目右键,选择“Export...”,导出LxjDbJdbc.jar。
这样就成功地实现了jdbc驱动。
下面再就可以进行测试了。
第四步,测试:
测试代码比较简单,不用过多解释:
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 = 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)); } // 关闭记录集 if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } // 关闭链接对象 if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } catch (Exception e) { e.printStackTrace(); } } }
注意,在项目中要正确设置LxjDbJdbc.jar的位置,项目中右键菜单“Build Path”,再选择“Configure Build Path”之Librares标签,点“Add Externd JARs...”按钮,将LxjDbJdbc.jar加入到项目中,即可运行,结果完全正确。
(蓝星际内存数据库是免费的,欢迎索取试用。蓝星际数据库速度极快,插入速度更快;非常稳定;支持快照功能,断电重启不会丢失数据。支持各种操作系统windows,Mac OS X,Linux等,不需要安装,直接运行。)
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。