第十五天3月10日之JDBC案例、分页技术、大数据（文本、图片）存储、批处理和存储过程的调用
一、大结果集的分页
MySQL：
limit M,N;
M:每页开始记录的索引。第一页的第一条记录的索引为0
N：每次取多少条
每页显示10条记录
第一页：M=0 N=10
第二页：M=10 N=10
第三页：M=20 N=10
第X页： M=(X-1)*N N=10
----------------------------------
总共需要多少页？
总页数=总记录条数%10==0?总记录条数/10:(总记录条数/10+1)
二、分页代码：
1、DAO：
/**
* 查询分页记录
* @param startIndex 开始的索引
* @param pageSize 每次取到的条数
* @return
*/
List<Customer> findPageRecords(int startIndex,int pageSize);
/**
* 查询总记录的条数
* @return
*/
int findTotalRecords();
2、Page对象设计：
public class Page {
private int pageSize = 10;//每页显示多少条记录
private int currentPageNum;//当前查看的页码
private int totalPage;//总页数
private List records;//分页数据
private int startIndex;//每页开始记录的索引号
private int totalRecords;//总记录条数
public Page(int currentPageNum,int totalRecords){
this.currentPageNum = currentPageNum;
this.totalRecords = totalRecords;
//计算总页数
totalPage = totalRecords%pageSize==0?totalRecords/pageSize:(totalRecords/pageSize+1);
//计算每页开始记录的索引号
startIndex = (currentPageNum-1)*pageSize;
}
}
2、SERVICE：
/**
* 查询封装了分页信息的Page对象
* @param pageNum 用户要看的页码。如果为null或者“”，默认值为1
* @return
*/
Page findPage(String pageNum);
参考实现：
public Page findPage(String pageNum) {
int num = 1;//用户要看的页码
if(pageNum!=null&&!pageNum.equals("")){
num = Integer.parseInt(pageNum);
}
int totalRecords = dao.findTotalRecords();
Page page = new Page(num,totalRecords);
List<Customer> cs = dao.findPageRecords(page.getStartIndex(), page.getPageSize());
page.setRecords(cs);
return page;
}
3、Servlet
String pageNum = request.getParameter("pageNum");
Page page = s.findPage(pageNum);
request.setAttribute("page", page);
request.getRequestDispatcher("/listCustomer.jsp").forward(request, response);
4.显示数据的jsp:
<c:forEach items="${page.records}" var="c" varStatus="vs">
<tr class="${vs.index%2==0?'odd':'even' }">
<td nowrap="nowrap">
<input type="checkbox" name="ids" value="${c.id}">
</td>
<td nowrap="nowrap">${c.name}</td>
<td nowrap="nowrap">${c.gender=='male'?'男':'女' }</td>
<td nowrap="nowrap">${c.birthday}</td>
<td nowrap="nowrap">${c.cellphone}</td>
<td nowrap="nowrap">${c.email}</td>
<td nowrap="nowrap">${c.hobby}</td>
<td nowrap="nowrap">${c.type}</td>
<td nowrap="nowrap">${fn:substring(c.description,0,3)}...</td>
<td nowrap="nowrap">[<a
href="${pageContext.request.contextPath}/servlet/CenterController?op=editUI&id=${c.id}">修改</a>]
[<a href="javascript:delOne('${c.id}')">删除</a>]</td>
</tr>
</c:forEach>
package com.itheima;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.Reader;
import java.io.Writer;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.junit.Test;
import com.itheima.util.JdbcUtil;
/*
use day15;
create table t2(
id int primary key,
content longblob
);
*/
//练一遍。实际开发用得少
public class BlobDemo {
@Test
public void add(){
Connection conn = null;
PreparedStatement stmt = null;
try{
conn = JdbcUtil.getConnection();
stmt = conn.prepareStatement("insert into t2(id,content) values(?,?)");
stmt.setInt(1, 1);
File file = new File("src/22.jpg");
InputStream in = new FileInputStream(file);
stmt.setBinaryStream(2, in, (int)file.length());
stmt.executeUpdate();
}catch(Exception e){
e.printStackTrace();
}finally{
JdbcUtil.release(null, stmt, conn);
}
}
@Test
public void read(){
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try{
conn = JdbcUtil.getConnection();
stmt = conn.prepareStatement("select * from t2 where id=1");
rs = stmt.executeQuery();
if(rs.next()){
InputStream in = rs.getBinaryStream("content");
//存到D盘上
OutputStream out = new FileOutputStream("d:/22.jpg");
int len = -1;
byte c[] = new byte[1024];
while((len=in.read(c))!=-1){
out.write(c, 0, len);
}
in.close();
out.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
JdbcUtil.release(null, stmt, conn);
}
}
}
package com.itheima;
import java.io.File;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.Reader;
import java.io.Writer;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.junit.Test;
import com.itheima.util.JdbcUtil;
/*
use day15;
create table t1(
id int primary key,
content longtext
);
*/
//练一遍。实际开发用得少
public class ClobDemo {
@Test
public void add(){
Connection conn = null;
PreparedStatement stmt = null;
try{
conn = JdbcUtil.getConnection();
stmt = conn.prepareStatement("insert into t1(id,content) values(?,?)");
stmt.setInt(1, 1);
File file = new File("src/jpm.txt");
Reader reader = new FileReader(file);
stmt.setCharacterStream(2, reader, (int)file.length());//long 参数的，MySQL驱动根本木有实现。
stmt.executeUpdate();
}catch(Exception e){
e.printStackTrace();
}finally{
JdbcUtil.release(null, stmt, conn);
}
}
@Test
public void read(){
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try{
conn = JdbcUtil.getConnection();
stmt = conn.prepareStatement("select * from t1 where id=1");
rs = stmt.executeQuery();
if(rs.next()){
Reader r = rs.getCharacterStream("content");
//存到D盘上
Writer out = new FileWriter("d:/jpm.txt");
int len = -1;
char c[] = new char[1024];
while((len=r.read(c))!=-1){
out.write(c, 0, len);
}
r.close();
out.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
JdbcUtil.release(null, stmt, conn);
}
}
}
package com.itheima;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import org.junit.Test;
import com.itheima.util.JdbcUtil;
/*
use day15;
create table t3(
id int primary key,
name varchar(100)
);
*/
public class BatchDemo {
//插入2条记录，删掉第1条
//statment:批处理不同的sql语句
@Test
public void batch1() throws Exception{
Connection conn = JdbcUtil.getConnection();
Statement stmt = conn.createStatement();
String sql1 = "insert into t3 (id,name) value(1,'aa')";
String sql2 = "insert into t3 (id,name) value(2,'bb')";
String sql3 = "delete from t3 where id=1";
stmt.addBatch(sql1);
stmt.addBatch(sql2);
stmt.addBatch(sql3);
int i[] = stmt.executeBatch();//元素，每条语句影响的行数
JdbcUtil.release(null, stmt, conn);
}
//语句相同，只是参数不同。使用PreparedStatement
@Test
public void batch2() throws Exception{
Connection conn = JdbcUtil.getConnection();
PreparedStatement stmt = conn.prepareStatement("insert into t3 (id,name) values(?,?)");
//插入100条有规律的数据
for(int i=0;i<100;i++){
stmt.setInt(1, i+1);
stmt.setString(2,"aa"+(i+1));
stmt.addBatch();
}
stmt.executeBatch();
JdbcUtil.release(null, stmt, conn);
}
//插入10万条
@Test
public void batch3() throws Exception{
Connection conn = JdbcUtil.getConnection();
PreparedStatement stmt = conn.prepareStatement("insert into t3 (id,name) values(?,?)");
long time = System.currentTimeMillis();
//插入100条有规律的数据
for(int i=0;i<100001;i++){
stmt.setInt(1, i+1);
stmt.setString(2,"aa"+(i+1));
stmt.addBatch();
if(i%1000==0){
stmt.executeBatch();
stmt.clearBatch();//清空List缓存
}
}
stmt.executeBatch();
JdbcUtil.release(null, stmt, conn);
System.out.println("用时："+(System.currentTimeMillis()-time));
}
}
package com.itheima;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Types;
import org.junit.Test;
import com.itheima.util.JdbcUtil;
/*
delimiter $$ 不一定是$$,可以是其他符号
CREATE PROCEDURE demoSp(IN inputParam VARCHAR(255), INOUT inOutParam varchar(255))
BEGIN
SELECT CONCAT('zyxw---', inputParam) into inOutParam;
END $$
delimiter ; 改回来把分号
*/
//如何调用存储过程
public class CallableStatementDemo {
@Test
public void test() throws Exception{
Connection conn = JdbcUtil.getConnection();
CallableStatement stmt = conn.prepareCall("{call demoSp(?,?)}");
stmt.setString(1, "晚上好");
//第2个不需要传参，因为是输出参数。但需要注册类型
stmt.registerOutParameter(2, Types.VARCHAR);
stmt.execute();
//获取输出参数的值
String value = stmt.getString(2);
System.out.println(value);
JdbcUtil.release(null, stmt, conn);
}
}