Java self-learning path ----- jdbc _ paging query, big data, batch processing, stored procedure

Paging query {is actually a part of the record each time, and displays: select * fromtable_namelimitStartIndex, PageSize; StartIndex: query from the first few records. PageSize: the number of records per query in the javaweb case: This object encapsulates the configuration information of the paging query, used at the dao layer publicl4

Paging query {is actually a part of the record each time, and displays: select * from table_name limit StartIndex, PageSize; StartIndex: Query starting from the first few records. PageSize: the number of records per query in the javaweb case: // This object encapsulates the configuration information of the paging query, used for the dao layer public L4

Paging query {

In fact, a part of records are queried each time and displayed:

Select * from table_name limit StartIndex, PageSize; --> StartIndex: query the number of records. PageSize: How many records are queried each time

Case study of paging query in javaweb:

// This object encapsulates the configuration information of the paging query, used for the public class QueryInfo {private int startindex at the dao layer; // each page of the query is at the starting position of the database, this value can be calculated based on other parameters. The set attribute private int pagesize = 5 is not required; // The number of records per page queried. An initial value private int querypage = 1 is assigned; // query the page number and assign an initial value // provides the get and set methods for ease of setting. get the attribute value public int getStartindex () {this. startindex = (this. querypage-1) + 1; return startindex;} public int getPagesize () {return pagesize;} public void setPagesize (int pagesize) {this. pagesize = pagesize;} public int getQuerypage () {return querypage;} public void setQuerypage (int querypage) {this. querypage = querypage ;}}

// This class encapsulates the results of the paging query and serves as the median value. It is used to provide the QueryBean with public class QueryResult {private List list; // encapsulate the private int totalrecord of all records on a page after the query; // The total number of records, used to provide users with interactive information // get and set methods for providing properties, public List getList () {return list;} public void setList (List list) {this. list = list;} public int getTotalrecord () {return totalrecord;} public void setTotalrecord (int totalrecord) {this. totalrecord = totalrecord ;}}

// This class encapsulates all information queried by page for interaction with users. It is used for jsp pages and the web layer public class QueryBean {private List list; // encapsulate all query records. private int totalrecord indicates the total number of records. You can obtain the private int pagesize from the QueryResult object. // you can obtain the number of records on each page, get private int totalpage from QueryInfo object; // tell the user the total number of pages. You can use totalrecord and pagesize to calculate private int currentpage; // tell the user which page is viewed currently, obtain the private int previouspage from the QueryInfo object; // The page on which the previous page is displayed, and calculate the private int nextpage on the current page; // The page on which the next page is displayed. Int [] pagebar; // record the page number. An array calculated by currentpage and pagesize // provides get for all attributes. set Method public int getTotalrecord () {return totalrecord ;} public void setTotalrecord (int totalrecord) {this. totalrecord = totalrecord;} public int getPagesize () {return pagesize;} public void setPagesize (int pagesize) {this. pagesize = pagesize;} public int getTotalpage () {// calculate the total number of pages, 21, 5 per page, with 5 pages. 20, 5 per page, 4 pages. If (this. totalrecord % this. pagesize = 0) {this. totalpage = this. totalrecord/this. pagesize;} else {this. totalpage = this. totalrecord/this. pagesize + 1;} return totalpage;} public int getCurrentpage () {return currentpage;} public void setCurrentpage (int currentpage) {this. currentpage = currentpage;} public int getPreviouspage () {this. previouspage = this. currentpage-1; // if the current page is smaller than or equal to 0, it points to the first page if (this. previouspage <= 0) {this. previouspage = 1;} return previouspage;} public int getNextpage () {this. previouspage = this. currentpage-1; // if the current page is smaller than or equal to 0, it points to the last page if (this. nextpage <= 0) {this. nextpage = 1;} return nextpage;} public int [] getPagebar () {int startindex; int endindex; // if the total number of pages is less than 10if (this. pagesize <= 10) {startindex = 1; endindex = this. pagesize;} else {startindex = this. currentpage-4; endindex = this. currentpage + 5; // if startindex is smaller than 1, it starts from 0. if endindex is greater than the total number of pages, the total number of pages is used as the end if (startindex <1) {startindex = 1; endindex = 10;} if (endindex> this. pagesize) {startindex = this. pagesize-9; endindex = this. pagesize ;}// two more index values define the array pagebarthis. pagebar = new int [endindex-startindex + 1]; // assign an array value for (int I = 0; I <pagebar. length; I ++) {this. pagebar [I] = startindex ++;} return pagebar ;}}

// A paging query method is provided at the dao layer to transmit the initial index position and records on each page to public QueryResult pageQuery (int startIndex, int pageSize) {Connection conn = null; preparedStatement st = null; ResultSet rs = null; try {// get database connection conn = JdbcUtils. getConnection (); // The SQL statement String SQL = "select * from customer limit ?,? "; // Pre-compile the SQL statement to obtain the preparedstatement object st = conn. prepareStatement (SQL); // is the placeholder '? 'Assign st. setInt (1, startIndex); st. setInt (2, pageSize); // execute an SQL statement to obtain the result set rs = st.exe cuteQuery (); // encapsulate the result to the QueryResult object // At the service layer, encapsulate this object in QueryBean and pass it to jsp for displaying ...} catch (Exception e) {} finally {JdbcUtils. release (conn, st, rs );}}

}

Big Data {

Usage:The program needs to save big text or binary data to the database. Generally, in actual development, it does not directly use the database to store big text data, because during access operations, it takes a lot of time to connect.

Big Data (Large Objects ):

Clob: stores large text and text files.

Blob: stores binary data, such as audio and binary files.

Replace clob with Text in mysql:

Text: Tinytext, text, mediumtext, longtext

Blob: tinyblob, blob, mediumblob, longblob

Large Text (Text ){

Storage:

PreparedStatement's setCharacterStream (1, reader, length) method, fill in placeholders

// Define the SQL statement String SQL = "insert into testclob (id, resum) values (?,?) "; // Pre-compile sqlPreparedStatement st = conn. prepareStatement (SQL); // value the placeholder st. setString (1, "1"); // It is not recommended to use the class loader to read files, because the text content may be too large, not suitable for reading to the memory File = new file ("src/1.txt"); Reader reader = new FileReader (File); st. setCharacterStream (2, reader, file. length (); // reader: the length of the stream for reading text: the size of the text. // This is a long type. jdk 1.6 must convert it to an int type.

Read:

Reader = resultSet. getCharacterStream (I );

Reader = resultSet. get Clob (I). getCharacterStream ();

String s = resultSet. getSting (I );

// Define the SQL statement String SQL = "select * from testclob where id = '1'"; // pre-compile sqlPreparedStatement st = conn. prepareStatement (SQL); // execute sqlResultSet rs = st.exe cuteQuery () without placeholders; // obtain the big data file while (rs) through the result set. next () {String id = rs. getString (1); // put the text data of the database in a stream for the user to use Reader = rs. getCharacterStream (2); // read data Writer writer = new FileWriter ("src/2.txt ");...}

}

Binary (Blob ){

Storage:

PreparedStatement's setBinaryStream (I, inputStream, length) method, fill in placeholders

// Define the SQL statement String SQL = "insert into testblob (id, image) values (?,?) "; // Pre-compile sqlPreparedStatement st = conn. prepareStatement (SQL); // value the placeholder st. setString (1, "1"); // It is not recommended to use the class loader to read files, because the text content may be too large, not suitable for reading to the memory File = new file ("src/1.jpg"); InputStream inputStream = new FileInputStream (File); st. setBinaryStream (2, inputStream, file. length (); // inputStream: the length of a stream that reads text: the size of the text. // This is a long type. jdk 1.6 must convert it to an int type.

Read: The data cannot be read directly through the command line program.

InputStream = resultSet. getBinaryStream (I );

InputStream = resultSet. getBlob (I). getBinaryStream ();

// Define the SQL statement String SQL = "select * from testblob where id = '1'"; // pre-compile sqlPreparedStatement st = conn. prepareStatement (SQL); // execute sqlResultSet rs = st.exe cuteQuery () without placeholders; // obtain the big data file while (rs) through the result set. next () {String id = rs. getString (1); // put the text data of the database in a stream for the user to use InputStream in = rs. getBinaryStream (2); // store data OutputStream out = new FileOutputStream ("c: \ 2.jpg ");...

}

}

Batch Processing {

Usage:When sending a batch of SQL statements to the database for execution, you should avoid sending and executing a batch of SQL statements to the database. The batch processing mechanism should be adopted to improve efficiency.

The first implementation method {

Store SQL statements: Statement. addBatch (SQL );

Execute batch statement: ExecuteBatch () --> execute the batch processing command clearBatch (); --> clear the batch processing command

// Define the SQL statement String sql1 = "insert into testBatch (id, name) values ('1', 'aaa ')"; string sql2 = "update testBatch set name = 'bbb 'where id = '1'"; // obtain the Statement object Statement st = conn. createStatement (); // Save the SQL statement to an object. The actual SQL statement is stored in the internal list object st. addBatch (sql1); st. addBatch (sql2); // execute sqlint result [] = st.exe cuteBatch (); // This array stores each SQL statement at a time and affects the number of rows of database data // clear st. clearBatch ();

1. Different SQL statements can be sent

2. Insufficient Performance

}

Method 2 {

// Define the SQL statement String SQL = "insert into testBatch (id, name) values (?,?) "; // Get the PreparedStatement object PreparedStatement st = conn. prepareStatement (SQL); // Replace the placeholder. The first SQL statement is saved to the list. // st. setString (1, "1"); // st. setString (2, "aaa"); // st. addBatch (); // Replace the placeholder. The second SQL statement is saved to the list. // st. setString (1, "2"); // st. setString (2, "bbb"); // st. addBatch (); // example: for (int I = 0; I <10; I ++) {st. setString (1, "" + I); st. setString (2, "aa" + I); st. addBatch () ;}// execute st.exe cuteBatch (); // clear st. clearBatch ();

1. Only the same SQL statement can be sent, which is suitable for batch insertion or batch update.

2. Pre-compile to improve performance

}

// If too many batch processing statements may cause memory overflow: Use the following processing method for (int I = 0; I <100000; I ++) {st. setString (1, "" + I); st. setString (2, "aa" + I); st. addBatch (); // each time SQL sentences are processed, if(i1_10001_01_1_st.exe cuteBatch (); st. clearBatch () ;}}// st.exe cuteBatch () is not executed to prevent SQL sentences; // clear st. clearBatch ();

}

Stored Procedure {

Role of this object:

1. It is equivalent to a database method. It processes data in the database and then directly obtains data in the java program.

2. Without this object, you need to use jdbc to process data.

3. It is generally used in the financial and securities field, where stored procedures are written internally and called directly by programmers.

4. input data and output data are usually specified in parameters during the stored procedure.

Usage:

1. Register a stored procedure in the database first

2. Use

// Obtain the Connection. first obtain a data through the database method, and then output Connection conn = JdbcUtils. getConnection (); // use the stored procedure name to obtain the CallableStatement st = conn. prepareCall ("{call demoSp (?,?)} "); // Call the stored procedure using the demoSp name // Replace the placeholder // input data st. setString (1, "aaa"); // output data, which provides the output type st. registerOutParameter (2, Types. VARCHAR); // typesban is often used in the data warehouse st.exe cute (); // through the second? Obtain the data, and it has specified the data type st. getString (2 );

Question: 1. Only know the input and output relationships of stored procedures in java programs. I don't know how it will affect the database. 2. I don't know how to write a stored procedure.

}