聊聊jdbc socketTimeout的设置_java教程

上一篇: 九天学会Java，第二天，算术运算下一篇:Java程序员进阶成为架构师，该掌...

聊聊jdbc socketTimeout的设置

发布时间：2019-11-19 发布网站：脚本宝典

脚本宝典收集整理的这篇文章主要介绍了聊聊jdbc socketTimeout的设置，脚本宝典觉得挺不错的，现在分享给大家，也给大家做个参考。

序
 本文主要介绍下jdbc的socket timeout的设置
 jdbc timeout类别
 主要有如下几个类别
 transaction timeout
 设置的是一个事务的执行时间，里头可能包含多个statement
 statement timeout(也相当于result set fetch timeout)
 设置的是一个statement的执行超时时间，即driver等待statement执行完成，接收到数据的超时时间(注意statement的timeout不是整个查询的timeout，只是statement执行完成并拉取fetchSize数据返回的超时，之后resultSet的next在必要的时候还会触发fetch数据，每次fetch的超时时间是单独算的，默认也是以statement设置的timeout为准)
 jdbc socket timeout
 设置的是jdbc I/O socket read and wrITe operations的超时时间，防止因网络问题或数据库问题，导致driver一直阻塞等待。(建议比statement timeout的时间长)
 os socket timeout
 这个是操作系统级别的socket设置(如果jdbc socket timeout没有设置，而os级别的socket timeout有设置，则使用系统的socket timeout值)。上面的不同级别的timeout越往下优先级越高，也就是说如果下面的配置比上面的配置值小的话，则会优先触发timeout，那么相当于上面的配置值就"失效"了。
 
 jdbc socket timeout
 这个不同数据的jdbc driver实现不一样
 MySQL
                             
       
jdbc:mySQL://localhost:3306/ag_admin?useUnicode=true&amp;amp;characterEncoding=UTF8&connectTimeout=60000&socketTimeout=60000
 通过url参数传递即可
 pg
                             
       
jdbc:postgresql://localhost/test?user=fred&password=secret&&connectTimeout=60&socketTimeout=60
 pg也是通过url传递，不过它的单位与mysql不同，mysql是毫秒，而pg是秒
 oracle
 oracle需要通过oracle.jdbc.ReadTimeout参数来设置，连接超时参数是oracle.net.CONNECT_TIMEOUT
 通过PRoPErties设置
                             
       
            Class.forName("oracle.jdbc.driver.OracleDriver");             Properties props = new Properties() ;             props.put( "user" , "test_schema") ;             props.put( "password" , "pwd") ;             props.put( "oracle.net.CONNECT_TIMEOUT" , "10000000") ;             props.put( "oracle.jdbc.ReadTimeout" , "2000" ) ;             Connection conn = DriverManager.getConnection( "jdbc:oracle:thin:@10.0.1.9:1521:orcl" , props ) ;
 通过环境变量设置
                             
       
String readTimeout = "10000"; // ms System.setProperty("oracle.jdbc.ReadTimeout", readTimeout); Class.forName("oracle.jdbc.OracleDriver"); Connection conn = DriverManager.getConnection(jdbcUrl, user, pwd);
 注意需要在connection连接之前设置环境变量
 tomcat jdbc pool
 一般我们不直接使用jdbc connection，而是使用连接池。由于tomcat jdbc pool是springboot默认使用的数据库连接池，这里就讲述一下如何在tomcat jdbc pool下设置。
                             
       
spring.datasource.tomcat.connectionProperties=oracle.net.CONNECT_TIMEOUT=10000;oracle.jdbc.ReadTimeout=60000
 注意，这里是分号分隔，单位是毫秒，这里可以根据各自的情况配置前缀(tomcat jdbc连接池的话，默认是spring.datasource.tomcat)，可以自定义，比如
                             
       
    @Bean     @Qualifier("writeDataSource")     @configurationProperties(prefix = "spring.datasource.write")     public DataSource writeDataSource() {         return DataSourceBuilder.create().build();     }
 假设你这里是自定义了prefix为spring.datasource.write，那么上述配置就变为
                             
       
spring.datasource.write.connectionProperties=oracle.net.CONNECT_TIMEOUT=10000;oracle.jdbc.ReadTimeout=60000
 oracle.jdbc.ReadTimeout如果没有设置的话，driver里头默认是0
 oracle.jdbc.ReadTimeout
 driver内部将该值设置到oracle.net.READ_TIMEOUT变量上
 oracle.net.nt.TcpNTAdapter
                      button" class="copyCode code-tool" data-toggle="tooltip" data-placement="top" data-clipboard-text="    @override     public void setReadTimeoutIfRequired(final Properties properties) throws IOException, NetException {         String s = ((Hashtable)properties).get("oracle.net.READ_TIMEOUT");         if (s == null) {             s = "0";         }         this.setOption(3, s);     }          public void setOption(int VAR1, Object var2) throws IOException, NetException {         String var3;         switch(var1) {         case 0:             var3 = (String)var2;             this.socket.setTcpNoDelay(var3.equals("YES"));             break;         case 1:             var3 = (String)var2;             if(var3.equals("YES")) {                 this.socket.setKeepAlive(true);             }         case 2:         default:             break;         case 3:             this.sockTimeout = Integer.parseint((String)var2);             this.socket.setSoTimeout(this.sockTimeout);         }      }" title="" data-original-title="复制">       
       
    @Override     public void setReadTimeoutIfRequired(final Properties properties) throws IOException, NetException {         String s = ((Hashtable<K, String>)properties).get("oracle.net.READ_TIMEOUT");         if (s == null) {             s = "0";         }         this.setOption(3, s);     }          public void setOption(int var1, Object var2) throws IOException, NetException {         String var3;         switch(var1) {         case 0:             var3 = (String)var2;             this.socket.setTcpNoDelay(var3.equals("YES"));             break;         case 1:             var3 = (String)var2;             if(var3.equals("YES")) {                 this.socket.setKeepAlive(true);             }         case 2:         default:             break;         case 3:             this.sockTimeout = Integer.parseInt((String)var2);             this.socket.setSoTimeout(this.sockTimeout);         }      }
 可用看到最后设置的是socket的soTimeout
 实例
                      select * From demo_table";         PreparedStatement pstmt;         try {             pstmt = (Preparedstatement)connection.prepareStatement(sql);             ResultSet rs = pstmt.executeQuery();             int col = rs.getMetaData().getColumnCount();             System.out.println("============================");             while (rs.next()) {                 for (int i = 1; i <= col; i++) {                     System.out.print(rs.getObject(i));                 }                 System.out.println("");             }             System.out.println("============================");         } catch (SQLException e) {             e.printStackTrace();         } finally {             //close resources         }     }" title="" data-original-title="复制">       
       
    @Test     public void testReadTimeout() throws SQLException {         Connection connection = dataSource.getConnection();         String sql = "select * from demo_table";         PreparedStatement pstmt;         try {             pstmt = (PreparedStatement)connection.prepareStatement(sql);             ResultSet rs = pstmt.executeQuery();             int col = rs.getMetaData().getColumnCount();             System.out.println("============================");             while (rs.next()) {                 for (int i = 1; i <= col; i++) {                     System.out.print(rs.getObject(i));                 }                 System.out.println("");             }             System.out.println("============================");         } catch (SQLException e) {             e.printStackTrace();         } finally {             //close resources         }     }
 超时错误输出
                             
       
//部分数据输出...... java.sql.SQLRecoverableException: IO 错误: Socket read timed out     at oracle.jdbc.driver.T4CPreparedStatement.fetch(T4CPreparedStatement.java:1128)     at oracle.jdbc.driver.OracleResultSetImpl.close_or_fetch_from_next(OracleResultSetImpl.java:373)     at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:277)     at com.example.demo.DemoApplicationTests.testReadTimeout(DemoApplicationTests.java:68)     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)     at java.lang.reflect.Method.invoke(Method.java:497)     at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)     at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)     at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)     at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)     at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)     at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)     at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)     at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)     at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:252)     at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:94)     at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)     at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)     at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)     at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)     at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)     at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)     at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)     at org.junit.runners.ParentRunner.run(ParentRunner.java:363)     at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:191)     at org.junit.runner.JUnitCore.run(JUnitCore.java:137)     at com.Intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithargs(JUnit4IdeaTestRunner.java:69)     at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:234)     at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:74)     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)     at java.lang.reflect.Method.invoke(Method.java:497)     at com.intellij.rt.execution.application.AppMain.main(AppMain.java:144) Caused by: oracle.net.ns.NetException: Socket read timed out     at oracle.net.ns.Packet.receive(Packet.java:339)     at oracle.net.ns.DataPacket.receive(DataPacket.java:106)     at oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:315)     at oracle.net.ns.NetInputStream.read(NetInputStream.java:260)     at oracle.net.ns.NetInputStream.read(NetInputStream.java:185)     at oracle.net.ns.NetInputStream.read(NetInputStream.java:102)     at oracle.jdbc.driver.T4CSocketInputStreamWrapper.readNextPacket(T4CSocketInputStreamWrapper.java:124)     at oracle.jdbc.driver.T4CSocketInputStreamWrapper.read(T4CSocketInputStreamWrapper.java:80)     at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1137)     at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:290)     at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)     at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)     at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)     at oracle.jdbc.driver.T4CPreparedStatement.fetch(T4CPreparedStatement.java:1119)     ... 35 more
 刚开始会有数据输出，但是到了某个resultSet的next的时候，报了超时(close_or_fetch_from_next)，这个超时指定的是当result.next方法触发新的一批数据的拉取(当一个fetchSize的数据消费完之后，接下来的next会触发新一批数据的fetch)之后在timeout时间返回内没有收到数据库返回的数据。oracle的jdbc默认的fetchSize为10，也就是每个fetch，如果超过指定时间没接收到数据，则抛出timeout异常。
 
 @H_360_641@小结 jdbc的socketTimeout值的设置要非常小心，不同数据库的jdbc driver设置不一样，特别是使用不同连接池的话，设置也可能不尽相同。对于严重依赖数据库操作的服务来说，非常有必要设置这个值，否则万一网络或数据库异常，会导致服务线程一直阻塞在java.net.SocketInputStream.socketRead0。
  如果查询数据多，则会导致该线程持有的data list不能释放，相当于内存泄露，最后导致OOM
 如果请求数据库操作很多且阻塞住了，会导致服务器可用的woker线程变少，严重则会导致服务不可用，nginx报504 Gateway Timeout
 
 doc
  oracle.jdbc.ReadTimeout
 深入理解JDBC的超时设置
 在Spring中基于JDBC进行数据访问时如何控制超时
 BugFix－HttpURLConnection