15th-March 10 JDBC case, paging technology, big data (text, image) Storage, batch processing, and stored procedure calls

Zookeeper

1. pagination of large result sets
MySQL:
Limit M, N;
M: Index of records starting from each page. The index of the first record on the first page is 0.
N: How many records are retrieved each time?

10 records per page
Page 1: M = 0 N = 10
Page 2: M = 10 N = 10
Page 3: M = 20 N = 10

Page X: M = (X-1) * N = 10

----------------------------------
How many pages are required in total?
Total number of pages = Total number of records % 10 = 0? Total records/10: (total records/10 + 1)

Ii. Paging code:
1. DAO:
/**
* Querying paging records
* @ Param startIndex start Index
* @ Param pageSize the number of items obtained each time
* @ Return
*/
List <Customer> findPageRecords (int startIndex, int pageSize );
/**
* Query the total number of records
* @ Return
*/
Int findTotalRecords ();
2. Page Object Design:
Public class Page {
Private int pageSize = 10; // how many records are displayed on each page
Private int currentPageNum; // the current page number.
Private int totalPage; // the total number of pages.
Private List records; // paging data
Private int startIndex; // index number recorded at the beginning of each page
Private int totalRecords; // The total number of records

Public Page (int currentPageNum, int totalRecords ){
This. currentPageNum = currentPageNum;
This. totalRecords = totalRecords;
// Calculate the total number of pages
TotalPage = totalRecords % pageSize = 0? TotalRecords/pageSize :( totalRecords/pageSize + 1 );
// Calculate the index number recorded at the beginning of each page
StartIndex = (currentPageNum-1) * pageSize;
}

}
2. SERVICE:

/**
* Query the Page object that encapsulates the Page information
* @ Param pageNum the page number to be viewed by the user. If it is null or "", the default value is 1.
* @ Return
*/
Page findPage (String pageNum );
 
Reference implementation:
Public Page findPage (String pageNum ){
Int num = 1; // the page number to be viewed by the user
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 for displaying data:

<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 '? Male: female} </td>
<Td nowrap = "nowrap" >$ {c. birthday} </td>
<Td nowrap = "nowrap" >$ {c. cellphone} </td>
<Td nowrap = "nowrap" >$ {c. email} </td>
<Td nowrap = "nowrap" >$ {c. holobby} </td>
<Td nowrap = "nowrap" >$ {c. type} </td>
<Td nowrap = "nowrap" >$ {fn: substring (c. description, 0, 3)}... </td>
<Td nowrap = "nowrap"> [<
Href = "$ {pageContext. request. contextPath}/servlet/CenterController? Op = editUI & id =$ {c. id} "> modify </a>]
[<A href = "javascript: delOne ('$ {c. id}')"> Delete </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); * // practice it again. 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, (intw.file.length({{stmt.exe cuteUpdate ();} 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. pr EpareStatement ("select * from t2 where id = 1"); rs = stmt.exe cuteQuery (); if (rs. next () {InputStream in = rs. getBinaryStream ("content"); // save it to disk 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); * // practice it again. 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 (); // For the long parameter, the MySQL driver is basically implemented. Stmt.exe cuteUpdate ();} 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.exe cuteQuery (); if (rs. next () {Reader r = rs. getCharacterStream ("content"); // save it to drive D for 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 {// insert 2 records, delete 1st rows // statment: Batch Processing of different SQL statements @ Testpublic void battings () throws Exception {Connection conn = JdbcUtil. getConnection (); Statement stmt = conn. cr EateStatement (); String sql1 = "insert into t3 (id, name) value (1, 'A')"; 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.exe cuteBatch (); // element, number of lines affected by each statement JdbcUtil. release (null, stmt, conn);} // The statement is the same, but the parameters are different. Use PreparedStatement @ Testpublic void batch2 () throws Exception {Connection conn = JdbcUtil. getConnection (); PreparedStatement stmt = conn. prepareStatement ("insert into t3 (id, name) values (?,?) "); // Insert 100 regular data records for (int I = 0; I <100; I ++) {stmt. setInt (1, I + 1); stmt. setString (2, "aa" (I %1%%%%stmt.addbatch(%%%%stmt.exe cuteBatch (); JdbcUtil. release (null, stmt, conn);} // insert 0.1 million @ Testpublic void batch3 () throws Exception {Connection conn = JdbcUtil. getConnection (); PreparedStatement stmt = conn. prepareStatement ("insert into t3 (id, name) values (?,?) "); Long time = System. currentTimeMillis (); // insert 100 regular data records for (int I = 0; I <100001; I ++) {stmt. setInt (1, I + 1); stmt. setString (2, "aa" Certificate (I %1%%%%stmt.addbatch(%%%if( I %1000%%0%%stmt.exe cuteBatch (); stmt. clearBatch (); // empty list stmt.exe cuteBatch (); JdbcUtil. release (null, stmt, conn); System. out. println ("time used:" + (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 $ is not necessarily $. It can be other symbols: create procedure demoSp (IN inputParam VARCHAR (255), INOUT inOutParam varchar (255 )) begin select concat ('zyxw --- ', inputParam) into inOutParam; END $ delimiter; change back to semicolon * // how to call the Stored Procedure public class CallableStatementDemo { @ Testpublic void test () throws Exception {Connection conn = JdbcUtil. getConnection (); CallableStatement stmt = conn. prepareCall ("{call demoSp (?,?)} "); Stmt. setString (1," Good evening "); // 2nd parameters are not required because they are output parameters. However, you must register the stmt type. registerOutParameter (2, types.varchareter ;stmt.exe cute (); // obtain the value of the output parameter String value = stmt. getString (2); System. out. println (value); JdbcUtil. release (null, stmt, conn );}}