1。概述相信很多同学在学习JDBC的时候都会遇到PreparedStatement和Statement。我应该使用哪一个？到头来很可能是看各种总结发呆，用PreparedStatement。所以在这篇文章中，通过MyCAT对PreparedStatement的实现，我们可以重新认识它。本文主要分为两部分：JDBCClient如何实现PreparedStatement。MyCAT服务器如何处理PreparedStatements。😈我们走吧。2.JDBCClient实现首先我们来看一段大家都喜欢复制粘贴的代码。JDBCPreparedStatement查询MySQL数据库：publicclassPreparedStatementDemo{publicstaticvoidmain(String[]args)throwsClassNotFoundException,SQLException{//1。获取数据库连接Class.forName("com.mysql.jdbc.Driver");Connectionconn=DriverManager.getConnection("jdbc:mysql://127.0.0.1:8066/dbtest?useServerPrepStmts=true","root","123456");//PreparedStatementPreparedStatementps=conn.prepareStatement("SELECTid,username,passwordFROMt_userWHEREid=?");ps.setLong(1,Math.abs(newRandom().nextLong()));//executeps.executeQuery();}}在获取MySQL连接时，useServerPrepStmts=true是一个非常非常非常重要的参数。如果不配置，PreparedStatement其实是一个假的PreparedStatement（新版本默认为FALSE，据说有的老版本默认为TRUE），不开启server级别的SQL预编译。为什么？看看它在JDBC中是如何实现的。//com.mysql.jdbc.ConnectionImpl.javapublicPreparedStatementprepareStatement(Stringsql,intresultSetType,intresultSetConcurrency)throwsSQLException{同步(getConnectionMutex()){checkClosed();PreparedStatementpStmt=null;booleancanServerPrepare=true;StringnativeSqlPre=getProcessForEscapes(Stsql)sql;if(this...this.serverSideStatementCache.remove(makePreparedStatementCacheKey(this.database,sql));if(pStmt!=null){((com.mysql.jdbc.ServerPreparedStatement)pStmt).setClosed(false);pStmt.clearParameters();//清除上次留下的参数}if(pStmt==null){//....省略代码：提交SQL预编译到服务器。}}}else{try{//提交SQL预编译到服务器。pStmt=ServerPreparedStatement.getInstance(getMultiHostSafeProxy(),nativeSql,this.database,resultSetType,resultSetConcurrency);pStmt.setResultSetType(resultSetType);pStmt.setResultSetConcurrency(resultSetConcurrency);}catch(SQLExceptionsqlEx){//平底锅，如果需要的话Utsaryif(){pStmt=(PreparedStatement)clientPrepareStatement(nativeSql,resultSetType,resultSetConcurrency,false);}else{throwsqlEx;}}}}else{pStmt=(PreparedStatement)clientPrepareStatement(nativeSql,resultSetType,resultSetConcurrency,false);}returnpStmt;}}[前]当Client启用useServerPreparedStmts且Server支持ServerPrepare时，Client会向Server提交SQL预编译请求。if(this.useServerPreparedStmts&&canServerPrepare){pStmt=ServerPreparedStatement.getInstance(getMultiHostSafeProxy(),nativeSql,this.database,resultSetType,resultSetConcurrency);}【稍后】当Client没有启用useServerPreparedStmts或者Server不支持ServerPrepared时，Client会不创建Prepared向Server提交SQL预编译请求。pStmt=(PreparedStatement)clientPrepareStatement(nativeSql,resultSetType,resultSetConcurrency,false);即便如此，为什么性能更好呢？【前者】返回的PreparedStatement对象类为JDBC42ServerPreparedStatement。bit对应的值？可以提交到服务器，减少网络传输和SQL解析的开销。【后】返回的PreparedStatement对象类为JDBC42PreparedStatement.java，后续每次执行SQL都需要将完整的SQL提交给服务器，增加了网络传输和SQL解析的开销。🌚：[前者]一定比[后者]有更好的表现？我相信你已经有了正确的答案。3、MyCATServer实现3.1CreatePreparedStatement该操作对应Clientconn.prepareStatement(....)。MyCAT收到请求后，创建一个PreparedStatement，返回statementId等信息。Client在发起SQL执行时，需要带上statementId给MyCAT。核心代码如下：//ServerPrepareHandler.java@Overridepublicvoidprepare(Stringsql){LOGGER.debug("useserverprepare,sql:"+sql);PreparedStatementpstmt=pstmtForSql.get(sql);if(pstmt==null){//在缓存中获取//解析并获取字段和参数的个数.getStatement(),pstmt);pstmtForId.put(pstmt.getId(),pstmt);}PreparedStmtResponse.response(pstmt,source);}//PreparedStmtResponse.javapublicstaticvoidresponse(PreparedStatementpstmt,FrontendConnectionc){bytepacketId=0;//writePrepreparedOkpacket(newPrepreparedOkpacket);preparedOk.packetId=++packetId;preparedOk.statementId=pstmt.getId();preparedOk.columnsNumber=pstmt.getColumnsNumber();preparedOk.parametersNumber=pstmt.getParametersNumber();ByteBufferbuffer=writepreparedOk..allocate(),c,true);//写入参数字段packetintparametersNumber=preparedOk.parametersNumber;if(pa内存etersNumber>0){for(inti=0;i
