随时随地阅读更多技术实战干货,获取项目源码、学习资料,请关注源代码社区公众号(ydmsq666)
在JDBC开发中,操作数据库需要与数据库建立连接,然后执行SQL,最后关闭连接。如果按照这个流程执行多个sql,就会建立多个连接,这样时间就浪费在了数据库连接上。
JDBC中批处理提供了很好的解决方案。同样以前面的例子说明:
index.jsp:
<%@page import="java.net.URLDecoder"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>添加图书</title>
</head>
<body>
<jsp:useBean id="batch" class="com.home.web.manager.Batch"></jsp:useBean>
<%
int row = batch.saveBatch();
out.print("批量插入了【" + row + "】条数据!");
%>
<a href="FindServlet">查看所有图书</a>
</body>
</html>
Batch.java:
package com.home.web.manager;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class Batch {
public Connection getConnection() {
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/test";
conn = DriverManager.getConnection(url, "root", "123456");
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
/**
*
* @description 批量添加书籍信息
* @return
*/
public int saveBatch() {
int row = 0;
Connection conn = getConnection();
try {
String sql = "insert into t_books(name,price,bookCount,author) values(?,?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
for (int i = 0; i < 10; i++) {
ps.setString(1, "book" + (i + 1));
ps.setDouble(2, i + 1);
ps.setInt(3, 1);
ps.setString(4, "author" + (i + 1));
ps.addBatch();
}
int[] rows = ps.executeBatch();
row = rows.length;
ps.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
return row;
}
}
result.jsp:
<%@page import="java.sql.*"%>
<%@ page language="java" import="java.util.*" autoFlush="true"
import="com.home.web.dto.*" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<table align="center" width="500" border="1">
<tr>
<td colspan="6" align="center">
<h2>所有图书信息</h2>
</td>
</tr>
<tr align="center">
<td><b>ID</b></td>
<td><b>图书名称</b></td>
<td><b>价格</b></td>
<td><b>数量</b></td>
<td><b>作者</b></td>
<td><b>删除</b></td>
</tr>
<%
List<Book> list = (List<Book>) request.getAttribute("list");
if (list == null || list.size() < 1) {
out.println("没有数据!");
} else {
for (Book book : list) {
%>
<tr align="center">
<td><%=book.getId()%></td>
<td><%=book.getName()%></td>
<td><%=book.getPrice()%></td>
<td><%=book.getBookCount()%></td>
<td><%=book.getAuthor()%></td>
<td>
<a href="DeleteServlet?id=<%=book.getId() %>">删除</a>
</td>
</tr>
<%
}
}
%>
</table>
</body>
</html>