我用jdbc做了个定时更新数据库的功能，当插入1w条数据时候没点问题，当插入2w条数据时候，就报连接超时的错误接着，但是程序还是没有挂掉，不知道是什么原因就解？

Java代码 复制代码 收藏代码
  1. package com.jgre.org;
  2.  
  3. import java.sql.Connection;
  4.  
  5. import java.sql.Date;
  6. import java.sql.DriverManager;
  7. import java.sql.PreparedStatement;
  8. import java.sql.ResultSet;
  9. import java.sql.SQLException;
  10. import java.text.ParseException;
  11. import java.text.SimpleDateFormat;
  12. import java.util.List;
  13.  
  14.  
  15. public class DB {
  16. //设置
  17. static int precount=1000;
  18.  
  19. public static Connection getConnect() throws Exception{
  20. Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
  21. //?useUnicode=true&characterEncoding=utf8
  22. Connection conn= DriverManager.getConnection("jdbc:sqlserver://146.12.62.208:1433;DatabaseName=LHJGXN", "sa", "flying-321");
  23. return conn;
  24. }
  25.  
  26. public static void free(ResultSet rs,PreparedStatement ps,Connection conn){
  27. try {
  28. if(rs!=null) rs.close();
  29. if(ps!=null) ps.close();
  30. if(conn!=null) conn.close();
  31. } catch (SQLException e) {
  32. e.printStackTrace();
  33. }
  34. }
  35. public static void free(PreparedStatement ps,Connection conn){
  36. try {
  37. if(ps!=null) ps.close();
  38. if(conn!=null) conn.close();
  39. } catch (SQLException e) {
  40. e.printStackTrace();
  41. }
  42. }
  43. //插入数据
  44. public synchronized int insertObject(List<AJXX> list){
  45. list=DB.initArray(list);
  46. Connection conn=null;
  47. PreparedStatement ps=null;
  48. try {
  49. conn=DB.getConnect();
  50. //关闭自动提交
  51. conn.setAutoCommit(false);
  52. //清空数据库
  53. ps=conn.prepareStatement("truncate table LHJGXN.dbo.TB_AJXX");
  54. ps.executeUpdate();
  55.  
  56. long startTime=System.currentTimeMillis();
  57. String sql="insert into LHJGXN.dbo.TB_AJXX( " +
  58. "AH,NH,ZH,SAXH,AY,LARQ,LAR,CBT,SJCBT, " +
  59. "CBR,AJLB,SPCX,SYCX,SFDA,SFYA,AJSJ,ZZRQ," +
  60. "JARQ, JAFS,GDRQ,DTXP,FDSXDQR,KCSXTS,SXDQR," +
  61. "AJZT,XLABZ)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
  62. ps=conn.prepareStatement(sql);
  63. for(int i=0;i<list.size();i++){
  64. System.out.println(i);
  65. AJXX a=list.get(i);
  66. if(a.getAH()!=null&&!a.getAH().equals("")){
  67. ps.setString(1, a.getAH());
  68. ps.setInt(2, a.getNH());
  69. ps.setString(3, a.getZH());
  70. ps.setInt(4, a.getSAXH());
  71. ps.setInt(5, a.getAY());
  72. // Date d=new Date();
  73. if(tDate(a.getLARQ())==0){
  74. ps.setDate(6, null);
  75. }else{
  76. ps.setDate(6, new Date(tDate(a.getLARQ())));
  77. }
  78. ps.setString(7, a.getLAR());
  79. ps.setString(8, a.getCBT());
  80. ps.setString(9, a.getSJCBT());
  81. ps.setString(10, a.getCBR());
  82. ps.setString(11, a.getAJLB());
  83. ps.setString(12, a.getSPCX());
  84. ps.setString(13, a.getSYCX());
  85. ps.setString(14, a.getSFDA());
  86. ps.setString(15, a.getSFYA());
  87. ps.setString(16, a.getAJSJ());
  88. if(tDate(a.getZZRQ())==0){
  89. ps.setDate(17, null);
  90. }else{
  91. ps.setDate(17, new Date(tDate(a.getZZRQ())));
  92. }
  93. if(tDate(a.getJARQ())==0){
  94. ps.setDate(18, null);
  95. }else{
  96. ps.setDate(18, new Date(tDate(a.getJARQ())));
  97. }
  98. ps.setString(19, getJAFS(a.getJAFS(),conn));
  99. if(tDate(a.getGDRQ())==0){
  100. ps.setDate(20, null);
  101. }else{
  102. ps.setDate(20, new Date(tDate(a.getGDRQ())));
  103. }
  104. ps.setInt(21, a.getDTXP());
  105. ps.setString(22, a.getFDSXDQR());
  106. ps.setInt(23, a.getKCSXTS());
  107. ps.setString(24, a.getSXDQR());
  108. ps.setString(25, a.getAJZT());
  109. ps.setString(26, a.getXLABZ());
  110. // ps.executeUpdate();
  111. ps.addBatch();
  112. }
  113. //每precount次就批量处理一次
  114. if((i+1)%precount==0){
  115. ps.executeBatch();
  116. //因为不确定总数所以没有去分很多批去执行，我整体做了个批量处理
  117. }
  118. }
  119. //提交
  120. //ps.executeBatch();
  121.  
  122. conn.commit();
  123. ps.clearBatch();
  124. long endTime=System.currentTimeMillis();
  125. System.out.println("插入数据时间为："+(endTime-startTime)+"ms");
  126. return (int)(endTime-startTime);
  127. } catch (Exception e) {
  128. try {
  129. //如果出现问题就回滚
  130. conn.rollback();
  131. } catch (SQLException e1) {
  132. e1.printStackTrace();
  133. }
  134. e.printStackTrace();
  135. PrintLog.writeLog("error:"+e.toString());
  136. }finally{
  137. DB.free(ps, conn);
  138. }
  139. return -1;
  140. }
  141. //根据案由名称获取案由编号
  142. public static int getAYBH(String AYMC){
  143. Connection conn=null;
  144. PreparedStatement ps=null;
  145. ResultSet rs=null;
  146. try {
  147. conn=DB.getConnect();
  148. ps=conn.prepareStatement("select AYBH from LHJGXN.dbo.TB_AY where AYMC=?");
  149. ps.setString(1, AYMC);
  150. rs=ps.executeQuery();
  151. if(rs.next()){
  152. return rs.getInt(1);
  153. }
  154. } catch (Exception e) {
  155. e.printStackTrace();
  156. }finally{
  157. DB.free(rs, ps, conn);
  158. }
  159. return -1;
  160. }
  161. //得到字号
  162. public static String getZH(String ZHMC){
  163. Connection conn=null;
  164. PreparedStatement ps=null;
  165. ResultSet rs=null;
  166. try {
  167. conn=DB.getConnect();
  168. ps=conn.prepareStatement("select ZH from LHJGXN.dbo.TB_ZHXX where ZHMC=?");
  169. ps.setString(1, ZHMC);
  170. rs=ps.executeQuery();
  171. if(rs.next()){
  172. return rs.getString(1);
  173. }
  174. } catch (Exception e) {
  175. e.printStackTrace();
  176. }finally{
  177. DB.free(rs, ps, conn);
  178. }
  179. return null;
  180. }
  181. //跟根据结案名称获取JAFS、AJLB、SPCX
  182. public static int getInfo(String cmd,String MC,Connection conn){
  183. //Connection conn=null;
  184. PreparedStatement ps=null;
  185. ResultSet rs=null;
  186. String sql=null;
  187. if(cmd.equalsIgnoreCase("JAFS")){
  188. sql="select JAFS from LHJGXN.dbo.TB_JAFS where MC=?";
  189. }else if(cmd.equalsIgnoreCase("AJLB")){
  190. sql="select AJLB from LHJGXN.dbo.TB_JAFS where MC=?";
  191. }else if(cmd.equalsIgnoreCase("SPCX")){
  192. sql="select SPCX from LHJGXN.dbo.TB_JAFS where MC=?";
  193. }
  194. try {
  195. conn=DB.getConnect();
  196. ps=conn.prepareStatement(sql);
  197. ps.setString(1, MC);
  198. rs=ps.executeQuery();
  199. if(rs.next()){
  200. return rs.getInt(1);
  201. }
  202. } catch (Exception e) {
  203. e.printStackTrace();
  204. }finally{
  205. DB.free(rs, ps, conn);
  206. }
  207. return -1;
  208. }
  209. public static long tDate(String d){
  210. SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
  211. try {
  212. if(d!=null&&!d.equals("")){
  213. java.util.Date date=sdf.parse(d);
  214. return date.getTime();}
  215. } catch (ParseException e) {
  216. e.printStackTrace();
  217. }
  218. return 0;
  219. }
  220. public static String getJAFS(String jafs,Connection conn){
  221. if(jafs!=null&&!jafs.trim().equals("")){
  222. // Connection conn=null;
  223. PreparedStatement ps=null;
  224. ResultSet rs=null;
  225. try {
  226. conn=DB.getConnect();
  227. ps=conn.prepareStatement("select JAFS from LHJGXN.dbo.TB_JAFS where MC=?");
  228. ps.setString(1, jafs);
  229. rs=ps.executeQuery();
  230. if(rs.next()){
  231. return rs.getString(1);
  232. }
  233. } catch (Exception e) {
  234. e.printStackTrace();
  235. }finally{
  236. DB.free(rs,ps, conn);
  237. }
  238. }
  239. return null;
  240. }
  241. public static List<AJXX> initArray(List<AJXX> d){
  242. while(d!=null&d.size()>0&d.size()%precount!=0){
  243. AJXX a=new AJXX();
  244. d.add(a);
  245. }
  246. System.out.println("格式化后的数据长度："+d.size());
  247. return d;
  248. }
  249.  
  250. }
  251.  
  252.  
  253.