jdbc批量插入工具类

1.import java.sql.Connection;
2.import java.sql.DriverManager;
3.import java.sql.PreparedStatement;
4.import java.sql.ResultSet;
5.import java.sql.SQLException;
6.import java.sql.Statement;
7.import java.util.ArrayList;
8.import java.util.List;
9.
10.public class JDBCUtils {
11.
12. private static JDBCUtils jdbcUtils = null;
13.
14. private static String jdbc_driver; //jdbc驱动
15.
16. private static String jdbc_url; //jdbc连接Url
17.
18. private static String user_name; //jdbc连接用户名
19.
20. private static String user_password; //jdbc连接密码
21.
22. private static String batch_size; //批量提交数
23.
24.
25. private JDBCUtils() { }
26.
27. /**
28. * 创建JDBC工具类实例
29. * @return
30. */
31. public static synchronized JDBCUtils getInstance(){
32.
33. if(jdbcUtils == null){
34. jdbcUtils = new JDBCUtils();
35. }
36. return jdbcUtils;
37. }
38.
39. /**
40. * 获取 数据库连接
41. * @return
42. */
43. public Connection getConnection(){
44. try {
45. Class.forName(jdbc_driver);
46. Connection conn = DriverManager.getConnection(jdbc_url, user_name, user_password);
47. return conn;
48.
49. } catch (Exception e) {
50. e.printStackTrace();
51. }
52. return null;
53. }
54.
55.
56. /**
57. * 关闭数据库相关连接
58. * @param connection
59. */
60. public void close(ResultSet rs, Statement st, Connection conn) {
61. try {
62. if(rs != null)rs.close();rs=null;
63. } catch (SQLException e) {
64. e.printStackTrace();
65. }finally{
66. try {
67. if (st != null) st.close();st=null;
68. } catch (SQLException e) {
69. e.printStackTrace();
70. } finally {
71. try {
72. if (conn != null) conn.close();conn=null;
73. } catch (SQLException e) {
74. e.printStackTrace();
75. }
76. }
77. }
78. }
79.
80. /**
81. * 关闭数据库相关连接
82. * @param connection
83. */
84. private void close(PreparedStatement pstmt, Connection conn) {
85. try {
86. if(pstmt != null)pstmt.close();
87. } catch (SQLException e) {
88. e.printStackTrace();
89. }finally{
90. try {
91. if (conn != null) conn.close();
92. } catch (SQLException e) {
93. e.printStackTrace();
94. }
95. }
96. }
97.
98. /**
99. * 增加单条数据
100. * @param sql sql语句
101. * @param values 参数值
102. * @return 是否增加成功
103. * @throws SQLException
104. */
105. public boolean saveOrUpdate(String sql,Object ... values) throws SQLException{
106. Connection conn = getConnection(); //获取数据库连接
107. PreparedStatement pstmt = null;
108. try {
109. conn.setAutoCommit(false); //设置手动提交事务
110. pstmt = conn.prepareStatement(sql); //创建PreparedStatement对象
111. //赋值
112. for (int i = 0; i < values.length; i++) {
113. pstmt.setObject(i+1, values[i]);
114. }
115.
116. pstmt.execute(); //执行操作
117. conn.commit(); //提交事务
118. close(pstmt,conn); //关闭相关连接
119. } catch (SQLException e) {
120. e.printStackTrace();
121. }finally{
122. close(pstmt,conn); //关闭相关连接
123. }
124. return true;
125. }
126. /**
127. * 删除
128. * @param sql
129. * @return
130. */
131. public boolean batchDelete(String sql){
132. Connection conn = getConnection(); //获取数据库连接
133. PreparedStatement pstmt = null;
134. try {
135. conn.setAutoCommit(false); //设置手动提交事务
136. pstmt = conn.prepareStatement(sql); //创建PreparedStatement对象
137.
138. pstmt.execute(); //执行操作
139. conn.commit(); //提交事务
140. close(pstmt,conn); //关闭相关连接
141. } catch (SQLException e) {
142. e.printStackTrace();
143. }finally{
144. close(pstmt,conn); //关闭相关连接
145. }
146. return true;
147.
148. }
149. /**
150. * 批量增加与修改
151. * @param sql insert or update 语句
152. * @param params 参数集合
153. * @return
154. * @throws SQLException
155. */
156. public boolean batchSaveOrUpdate(String sql,List<Object[]> paramList) {
157. int count = Integer.parseInt(batch_size)-1;
158. Connection conn = getConnection(); //获取数据库连接
159. PreparedStatement pstmt = null;
160. try {
161. conn.setAutoCommit(false); //设置手动提交事务
162. pstmt = conn.prepareStatement(sql); //创建PreparedStatement对象
163. //赋值
164. for (int i = 0; i < paramList.size(); i++) {
165.
166. Object[] values = paramList.get(i);
167. for (int j = 0; j < values.length ; j++) {
168. pstmt.setObject(j+1, values[j]);
169. }
170. pstmt.addBatch();
171.
172. //批量数等于 batch_size 时 提交数据
173. if(i != 0 && (i%count == 0)){
174. int ids[] = pstmt.executeBatch(); //执行操作
175. if(ids.length == count+1 ){
176. conn.commit(); //提交事务
177. }else{
178. conn.rollback(); //事务回滚
179. }
180. pstmt.clearBatch();
181. }
182. }
183.
184. int ids[] = pstmt.executeBatch(); //执行操作
185. if(ids.length == paramList.size()%(count+1) ){
186. conn.commit(); //提交事务
187. }else{
188. conn.rollback(); //事务回滚
189. }
190.
191. } catch (SQLException e) {
192. e.printStackTrace();
193. }finally{
194. close(pstmt,conn); //关闭相关连接
195. }
196. return true;
197. }
198.
199.
200.
201. public static void main(String[] args) throws SQLException {
202.// JDBCUtils utils = JDBCUtils.getInstance();
203.//
204.//
205.// String sql = "insert into tbl_yitiansystem_systemlog (id,message) values(?,?);";
206.// List paramList = new ArrayList();
207.// for (int i = 0; i < 10; i++) {
208.// String [] param = new String[]{i+"",i+""};
209.// paramList.add(param);
210.// }
211.//
212.// boolean t = utils.batchSaveOrUpdate(sql, paramList);
213.// System.out.println(t);
214.//
215. }
216.
217.}