手撸 SQL to mongodb jdbc driver 玩具

优点闲余时间，于是手撸了一个 Sql to mongodb 的jdbc 驱动，实现简单sql语句查询删除修改mongodb。就是个玩具。

这里用到 jsqlparser 1.2 ， bson 3.11， mongodb-driver 3.11 。

这个具备简单增删改查功能的jdbc驱动需要建立4个类分别实现 Driver， Connection， Statement， ResultSet 这四个接口。

具体的实现原理不多说了，反正有代码，斟杯茶，慢慢看，贴出的代码是需要增加的，不需要改动的就不贴了，俺不是写小说的，不做靠字数骗钱事，下面贴代码：

Driver：

public class MongoDBDriver implements Driver {
	
    private final static String connectStrPrefix = "jdbc:mongo:";

	@Override
	public Connection connect(String url, Properties info) throws SQLException {
		if (!acceptsURL(url)) {
		    return null;
		}
		String mongoUrlStr = url.replace(connectStrPrefix, "mongodb:");
		
		
		return new MongoDBConnection(mongoUrlStr);
	}

	private boolean checkConnetionUrl(String url){
		String tempUrlStr = url.replace(connectStrPrefix, "http:");
		URI tempUrl = null;
		try {
			tempUrl = new URI(tempUrlStr);
		} catch (URISyntaxException e) {
			return false;
		}
		return tempUrl==null?false:true;
	}
	
	@Override
	public boolean acceptsURL(String url) throws SQLException {
		if(url.startsWith(connectStrPrefix) && checkConnetionUrl(url) ){
			return true;
		}
		return false;
	}

	@Override
	public DriverPropertyInfo[] getPropertyInfo(String url, Properties info) throws SQLException {

		DriverPropertyInfo propInfos[] = new DriverPropertyInfo[0];

		return propInfos;
	}


    static {
		try {
		    DriverManager.registerDriver(new MongoDBDriver());
		} catch (Exception e) {
		}
    }
    


}

Connection

public class MongoDBConnection implements Connection {
	
	private String mongoDBConnString = null;
	private MongoClient mongoClient = null;
	private MongoClientURI mongoClientURI = null;
	private MongoDatabase mongoDatabase = null;
	
	public MongoDBConnection(String connstr){
		mongoDBConnString = connstr;
		mongoClientURI = new MongoClientURI(connstr);
		mongoClient = new MongoClient(mongoClientURI);
		mongoDatabase = mongoClient.getDatabase(mongoClientURI.getDatabase());
	}

	@Override
	public Statement createStatement() throws SQLException {
		
		return new MongoDBStatement(mongoDatabase);
	}


}

Statement

public class MongoDBStatement implements Statement {
	
	private MongoDatabase mongoDatabase = null;
		
	public MongoDBStatement(MongoDatabase database){
		mongoDatabase = database;
	}


	@Override
	public ResultSet executeQuery(String sql) throws SQLException {
		SearchRelateDatas searchRelateDatas = new SearchRelateDatas();
		net.sf.jsqlparser.statement.Statement statement = parseSql(sql);
		if(statement==null){
			throw new SQLException("Parse sql error");
		}
		if(statement instanceof Select){
			PlainSelect selectBody = (PlainSelect)((Select) statement).getSelectBody();
			
			Table fromItem = (Table) selectBody.getFromItem();
			
			searchRelateDatas.collection = mongoDatabase.getCollection(fromItem.getName());
			searchRelateDatas.columns = new ArrayList<String>();
			
			final List<SelectItem> list = selectBody.getSelectItems();
			for (int i = 0; i < list.size(); i++) {
				if (list.get(i) instanceof AllColumns) {
					AllColumns allColumns = (AllColumns) list.get(i);
					searchRelateDatas.columns.add(allColumns.toString());
				} else if (list.get(i) instanceof SelectExpressionItem) {
					SelectExpressionItem sei = (SelectExpressionItem) list.get(i);
					Column c = (Column) sei.getExpression();
					//System.out.println(c.getColumnName());
					searchRelateDatas.columns.add( c.getColumnName() );
				}
			}
			
			Expression where = selectBody.getWhere();
			if(where!=null){
				searchRelateDatas.searchCondition = ParseSqlText.parseWhereStament(where);
			}
			if(searchRelateDatas.searchCondition!=null){
				searchRelateDatas.founds = searchRelateDatas.collection.find(searchRelateDatas.searchCondition);
			}else{
				searchRelateDatas.founds = searchRelateDatas.collection.find();
			}
			return new MongoDBResultSet(searchRelateDatas);
		}
		return null;
	}

	@Override
	public int executeUpdate(String sql) throws SQLException {
		net.sf.jsqlparser.statement.Statement statement = parseSql(sql);
		if(statement==null){
			throw new SQLException("Parse sql error");
		}
		if(statement instanceof Drop){
			Drop drop = (Drop) statement;
			mongoDatabase.getCollection(drop.getName().getName()).drop();
		}else if (statement instanceof Truncate) {
			Truncate truncate = (Truncate) statement;
			truncate.getTable().getName();
			mongoDatabase.getCollection(truncate.getTable().getName()).deleteMany(new Document());
		}else if (statement instanceof Update) {
			Update updateStament = (Update) statement;
			Table table = (Table) updateStament.getTables().get(0);
			MongoCollection<Document> collection = mongoDatabase.getCollection(table.getName());
			List<Column> columnList = updateStament.getColumns();
			List<Expression> exprList = updateStament.getExpressions();
			Document updateDoc = new Document();
			for(int i=0; i<columnList.size(); ++i){
				Column updateColumn = columnList.get(i);
				Expression expr = exprList.get(i);
				ParseSqlText.addNameValueToDoc(updateDoc, updateColumn.getColumnName(), expr);
			}
			Expression where = updateStament.getWhere();
			Document searchCondition = null;
			if(where!=null){
				searchCondition = ParseSqlText.parseWhereStament(where);
			}else{
				searchCondition = new Document();
			}
			collection.updateMany(searchCondition, new Document("$set", updateDoc));
		} else if (statement instanceof Insert) {
			Insert insertStament = (Insert) statement;
			MongoCollection<Document> collection = mongoDatabase.getCollection(insertStament.getTable().getName());
			List<Column> columnList = insertStament.getColumns();
			List<Expression> exprList = ((ExpressionList)(insertStament.getItemsList())).getExpressions();
			Document insertDoc = new Document();
			for(int i=0; i<columnList.size(); ++i){
				Column setColumn = columnList.get(i);
				Expression expr = exprList.get(i);
				ParseSqlText.addNameValueToDoc(insertDoc, setColumn.getColumnName(), expr);
			}
			collection.insertOne(insertDoc);
		} else if (statement instanceof Delete) {
			Delete deleteStament = (Delete) statement;
			MongoCollection<Document> collection = mongoDatabase.getCollection(deleteStament.getTable().getName());
			Expression where = deleteStament.getWhere();
			Document searchCondition = null;
			if(where!=null){
				searchCondition = ParseSqlText.parseWhereStament(where);
			}else{
				searchCondition = new Document();
			}
			collection.deleteMany(searchCondition);
		}
		return 0;
	}

	private net.sf.jsqlparser.statement.Statement parseSql(String sql){
		try {
			return CCJSqlParserUtil.parse(sql);
		} catch (JSQLParserException e) {
		}
		return null;
	}


}

ResultSet

public class MongoDBResultSet implements ResultSet, ResultSetMetaData {
	
	private SearchRelateDatas founds = null;
	private MongoCursor<Document> cursor = null;
	private Document currRecord = null;
	
	public MongoDBResultSet(SearchRelateDatas f){
		founds = f;
		cursor = f.founds.cursor();
	}

	@Override
	public int getColumnCount() throws SQLException {
		int totalColumn = 0;
		for(int i=0; i<founds.columns.size(); ++i){
			if("*".equals(founds.columns.get(i))){
				totalColumn += founds.founds.first().size();
			}else{
				++totalColumn;
			}
		}
		return totalColumn;
	}


	@Override
	public String getColumnName(int column) throws SQLException {
		return (String)(currRecord.keySet().toArray()[column]);
	}


	@Override
	public boolean next() throws SQLException {
		
		currRecord = cursor.hasNext()?cursor.next():null;
		return currRecord==null?false:true;
	}

	@Override
	public void close() throws SQLException {
		cursor.close();
	}


	@Override
	public String getString(int columnIndex) throws SQLException {
		return currRecord.getString(currRecord.keySet().toArray()[columnIndex]);
	}

	@Override
	public boolean getBoolean(int columnIndex) throws SQLException {
		return currRecord.getBoolean(currRecord.keySet().toArray()[columnIndex]);
	}

	@Override
	public int getInt(int columnIndex) throws SQLException {
		return currRecord.getInteger(currRecord.keySet().toArray()[columnIndex]);
	}

	@Override
	public long getLong(int columnIndex) throws SQLException {
		return currRecord.getLong(currRecord.keySet().toArray()[columnIndex]);
	}

	@Override
	public double getDouble(int columnIndex) throws SQLException {
		return currRecord.getDouble(currRecord.keySet().toArray()[columnIndex]);
	}


	@Override
	public Date getDate(int columnIndex) throws SQLException {
		return (Date)currRecord.getDate(currRecord.keySet().toArray()[columnIndex]);
	}


	@Override
	public String getString(String columnLabel) throws SQLException {
		return currRecord.getString(columnLabel);
	}

	

	@Override
	public int getInt(String columnLabel) throws SQLException {
		return currRecord.getInteger(columnLabel);
	}

	@Override
	public long getLong(String columnLabel) throws SQLException {
		return currRecord.getLong(columnLabel);
	}

	@Override
	public double getDouble(String columnLabel) throws SQLException {
		return currRecord.getDouble(columnLabel);
	}

	
}

SearchRelateDatas

public class SearchRelateDatas {
	public List<String> columns;
	public Document searchCondition;
	public MongoCollection<Document> collection;
	public FindIterable<Document> founds;

}

ParseSqlText

public class ParseSqlText {
	
	public static Document buildBetWeenCondition(Between betweenExpr){
		Document betweenVal = null;
		Column column = (Column)betweenExpr.getLeftExpression();
		Expression startExpr = betweenExpr.getBetweenExpressionStart();
		Expression endExpr = betweenExpr.getBetweenExpressionEnd();
		if(startExpr instanceof LongValue){
			betweenVal = new Document(column.getColumnName(), 
					new Document("$gte", ((LongValue)startExpr).getBigIntegerValue())
					.append("$lte", ((LongValue)endExpr).getBigIntegerValue()));
		} else if(startExpr instanceof DoubleValue){
			betweenVal = new Document(column.getColumnName(), 
					new Document("$gte", ((DoubleValue)startExpr).getValue())
					.append("$lte", ((DoubleValue)endExpr).getValue()));
		}else if(startExpr instanceof StringValue){
			betweenVal = new Document(column.getColumnName(), 
					new Document("$gte", ((StringValue)startExpr).getValue())
					.append("$lte", ((StringValue)endExpr).getValue()));
		}else if(startExpr instanceof DateValue){
			betweenVal = new Document(column.getColumnName(), 
					new Document("$gte", ((DateValue)startExpr).getValue())
					.append("$lte", ((DateValue)endExpr).getValue()));
		}else if(startExpr instanceof TimeValue){
			betweenVal = new Document(column.getColumnName(), 
					new Document("$gte", ((TimeValue)startExpr).getValue())
					.append("$lte", ((TimeValue)endExpr).getValue()));
		}else if(startExpr instanceof TimestampValue){
			betweenVal = new Document(column.getColumnName(), 
					new Document("$gte", ((TimestampValue)startExpr).getValue())
					.append("$lte", ((TimestampValue)endExpr).getValue()));
		}
		return betweenVal;
	}
	
	public static Document buildCompareCondition(String name, String condition, Expression expr){
		Document cond = null;
		if(expr instanceof LongValue){
			cond = new Document(name, 
					new Document(condition, ((LongValue)expr).getBigIntegerValue()));
		} else if(expr instanceof DoubleValue){
			cond = new Document(name, 
					new Document(condition, ((DoubleValue)expr).getValue()));
		}else if(expr instanceof StringValue){
			cond = new Document(name, 
					new Document(condition, ((StringValue)expr).getValue()));
		}else if(expr instanceof DateValue){
			cond = new Document(name, 
					new Document(condition, ((DateValue)expr).getValue()));
		}else if(expr instanceof TimeValue){
			cond = new Document(name, 
					new Document(condition, ((TimeValue)expr).getValue()));
		}else if(expr instanceof TimestampValue){
			cond = new Document(name, 
					new Document(condition, ((TimestampValue)expr).getValue()));
		}
		return cond;
	}
	
	public static Document parseWhereStament(Expression expr){
		Document query = new Document();
		if(expr instanceof AndExpression){
			AndExpression andExpression = (AndExpression)expr;
			Document left = parseWhereStament(andExpression.getLeftExpression());
			Document right = parseWhereStament(andExpression.getRightExpression());
			query.append("$and", Arrays.asList(left, right));
		} else if(expr instanceof OrExpression){
			OrExpression orExpression = (OrExpression)expr;
			Document left = parseWhereStament(orExpression.getLeftExpression());
			Document right = parseWhereStament(orExpression.getRightExpression());
			query.append("$or", Arrays.asList(left, right));
		} else if(expr instanceof Between){
			Between betweenExpr = (Between)expr;
			query = buildBetWeenCondition(betweenExpr); 
		} else if(expr instanceof EqualsTo){
			EqualsTo equalsTo = (EqualsTo)expr;
			boolean leftIsColumn = equalsTo.getLeftExpression() instanceof Column;
			Column column = leftIsColumn?(Column)(equalsTo.getLeftExpression()) : (Column)(equalsTo.getRightExpression());
			Expression valExpr = leftIsColumn? equalsTo.getRightExpression() : equalsTo.getLeftExpression();
			query = buildCompareCondition(column.getColumnName(), "$eq", valExpr);
		} else if(expr instanceof ExistsExpression){
		} else if(expr instanceof GreaterThan){
			GreaterThan greaterThan = (GreaterThan)expr;
			boolean leftIsColumn = greaterThan.getLeftExpression() instanceof Column;
			Column column = leftIsColumn?(Column)(greaterThan.getLeftExpression()) : (Column)(greaterThan.getRightExpression());
			Expression valExpr = leftIsColumn?greaterThan.getRightExpression() : greaterThan.getLeftExpression();
			query = buildCompareCondition(column.getColumnName(), "$gt", valExpr);
		} else if(expr instanceof GreaterThanEquals){
			GreaterThanEquals greaterThanEquals = (GreaterThanEquals)expr;
			boolean leftIsColumn = greaterThanEquals.getLeftExpression() instanceof Column;
			Column column = leftIsColumn?(Column)(greaterThanEquals.getLeftExpression()) : (Column)(greaterThanEquals.getRightExpression());
			Expression valExpr = leftIsColumn? greaterThanEquals.getRightExpression() : greaterThanEquals.getLeftExpression();
			query = buildCompareCondition(column.getColumnName(), "$gte", valExpr);
		} else if(expr instanceof InExpression){
		} else if(expr instanceof IsNullExpression){
		} else if(expr instanceof LikeExpression){
		} else if(expr instanceof MinorThan){
			MinorThan minorThan = (MinorThan)expr;
			boolean leftIsColumn = minorThan.getLeftExpression() instanceof Column;
			Column column = leftIsColumn?(Column)(minorThan.getLeftExpression()) : (Column)(minorThan.getRightExpression());
			Expression valExpr = leftIsColumn? minorThan.getRightExpression() : minorThan.getLeftExpression();
			query = buildCompareCondition(column.getColumnName(), "$lt", valExpr);
		} else if(expr instanceof MinorThanEquals){
			MinorThanEquals minorThanEquals = (MinorThanEquals)expr;
			boolean leftIsColumn = minorThanEquals.getLeftExpression() instanceof Column;
			Column column = leftIsColumn?(Column)(minorThanEquals.getLeftExpression()) : (Column)(minorThanEquals.getRightExpression());
			Expression valExpr = leftIsColumn? minorThanEquals.getRightExpression() : minorThanEquals.getLeftExpression();
			query = buildCompareCondition(column.getColumnName(), "$lte", valExpr);
		} else if(expr instanceof NotEqualsTo){
			NotEqualsTo notEqualsTo = (NotEqualsTo)expr;
			boolean leftIsColumn = notEqualsTo.getLeftExpression() instanceof Column;
			Column column = leftIsColumn?(Column)(notEqualsTo.getLeftExpression()) : (Column)(notEqualsTo.getRightExpression());
			Expression valExpr = leftIsColumn? notEqualsTo.getRightExpression() : notEqualsTo.getLeftExpression();
			query = buildCompareCondition(column.getColumnName(), "$ne", valExpr);
		}
		return query;
	}
	
	public static void addNameValueToDoc(Document doc, String name, Expression expr){
		if(expr instanceof LongValue){
			doc.append(name, ((LongValue)expr).getBigIntegerValue());
		} else if(expr instanceof DoubleValue){
			doc.append(name, ((DoubleValue)expr).getValue());
		}else if(expr instanceof StringValue){
			doc.append(name, ((StringValue)expr).getValue());
		}else if(expr instanceof DateValue){
			doc.append(name, ((DateValue)expr).getValue());
		}else if(expr instanceof TimeValue){
			doc.append(name, ((TimeValue)expr).getValue());
		}else if(expr instanceof TimestampValue){
			doc.append(name, ((TimestampValue)expr).getValue());
		}
	}
	
	
}

测试代码：

    
	public static void main(String[] args) throws SQLException {
		try {
			Class.forName("myjdbc.mongodb.MongoDBDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		Connection conn = DriverManager.getConnection("jdbc:mongo://127.0.0.1:27017/test", "", "");
		java.sql.Statement statement = conn.createStatement();
		statement.executeUpdate("insert into test01 (c1,c2,c3) values ('123','456','789')");
		statement.executeUpdate("update test01 set c2='ABC' where c1='123'");
		ResultSet rs = statement.executeQuery("select * from test01");
		while(rs.next()){
			System.out.println(String.format("%s %s %s", rs.getString(1), rs.getString(2), rs.getString(3)));
		}
		rs.close();
		rs = null;
		statement.executeUpdate("delete from test01");
	}

结果：