第十五天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 {@Testpublic 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);}}@Testpublic 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 {@Testpublic 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);}}@Testpublic 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語句@Testpublic 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@Testpublic 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萬條@Testpublic 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 {@Testpublic 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);}}