第十五天3月10日之JDBC案例、分页技术、大数据（文本、图片）存储、批处理和存储过程的调用

浏览数：46 / 时间：2015年06月12日


一、大结果集的分页
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);
	}
}



第十五天3月10日之JDBC案例、分页技术、大数据（文本、图片）存储、批处理和存储过程的调用,古老的榕树,5-wow.com

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

标签： des it class java string 类 代码 lu top blog oc des it class java string 类 代码 lu top blog oc

相关文章

随机文章

您可能还喜欢

您可能还喜欢