search
HomeDatabaseMysql Tutorialjava自学之路-----jdbc_分页查询,大数据,批处理,存储过程

分页查询{ 实际上就是每次查询一部分记录,并显示: select * from table_name limit StartIndex, PageSize;StartIndex:从第几个记录开始查。PageSize:每次查多少记录 分页查询在javaweb中的案例: //该对象封装了分页查询的配置信息,用于dao层public cla

分页查询{

实际上就是每次查询一部分记录,并显示:

select * from table_name limit StartIndex, PageSize;——>StartIndex:从第几个记录开始查。PageSize:每次查多少记录

分页查询在javaweb中的案例:

//该对象封装了分页查询的配置信息,用于dao层
public class QueryInfo {
	private int startindex;//查询的每页在数据库的起始位置,该值可以根据其他参数计算出,不需要set属性
	private int pagesize = 5;//查询的每页记录数,赋一个初始值
	private int querypage = 1;//查询第几页,赋一个初始值
	//提供get,set方法,便于设置,获取属性值
	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;
	}
}
 
//该类封装了分页查询的结果信息,作为中间值,用于给QueryBean提供信息
public class QueryResult {
	private List list;//封装了查询后某页的所有记录
	private int totalrecord;//总记录数,用于给用户提供交互信息
//	提供属性的get,set方法,
	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;
	}
}
 
//该类封装了分页查询的所有信息,用于与用户交互,用于jsp页面,web层
public class QueryBean {
	private List list;//封装查询的所有记录信息
	private int totalrecord;//告诉用户总共有多少记录,可以从QueryResult对象获取
	private int pagesize;//告诉用户每页有多少记录,从QueryInfo对象获取
	private int totalpage;//告诉用户一共有多少页,可以通过totalrecord和pagesize计算
	private int currentpage;//告诉用户当前查看的是第几页,从QueryInfo对象获取
	private int previouspage;//上一页是第几页,通过当前页计算出
	private int nextpage;//下一页是第几页,通过当前页计算出
	private int[] pagebar;//记录页码条,通过currentpage和pagesize计算出的一个数组
//	提供所有属性的get,set方法
	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() {
//		计算总页数,21条,每页5条,有5页。20条,每页5条,有4页。
		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;
//		如果当前页小于等于0,就指向第一页
		if(this.previouspage<=0){
			this.previouspage = 1;
		}
		return previouspage;
	}
	public int getNextpage() {
		this.previouspage = this.currentpage - 1;
//		如果当前页小于等于0,就指向最后一页
		if(this.nextpage<=0){
			this.nextpage = 1;
		}
		return nextpage;
	}
	public int[] getPagebar() {
		int startindex;
		int endindex;
//		如果总页数小于10
		if(this.pagesize <= 10){
			startindex = 1;
			endindex = this.pagesize;
		}else{
			startindex = this.currentpage - 4;
			endindex = this.currentpage + 5;
//			如果startindex小于1,就从0开始,如果endindex大于总页数,就用总页数作为结束
			if(startindex < 1){
				startindex = 1;
				endindex = 10;
			}
			if(endindex > this.pagesize){
				startindex = this.pagesize - 9;
				endindex = this.pagesize;
			}
		}
//		更具两个索引值定义数组pagebar
		this.pagebar = new int[endindex-startindex+1];
//		为数组赋值
		for (int i = 0; i < pagebar.length; i++) {
			this.pagebar[i] = startindex++;
		}		
		return pagebar;
	}
}
//	在dao层提供一个分页查询的方法,将索引的初始位置和每页记录数传入
	public QueryResult pageQuery(int startIndex, int pageSize){
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
//			获取数据库连接
			conn = JdbcUtils.getConnection();
//			提供分页查询的sql语句
			String sql = "select * from customer limit ?,?";
//			预编译sql语句,获得preparedstatement对象
			st = conn.prepareStatement(sql);
//			为占位符&#39;?&#39; 赋值
			st.setInt(1, startIndex);
			st.setInt(2, pageSize);
//			执行sql语句,获得结果集
			rs = st.executeQuery();
//			将结果封装到QueryResult对象中
				//在service层,将该对象封装到QueryBean中,传给jsp用于显示
			...
		} catch (Exception e) {
		}finally{
			JdbcUtils.release(conn, st, rs);
		}
	}

}

大数据{

用处:程序需要将大文本或二进制数据保存到数据库中,一般在实际开发中不直接使用数据库存储大文本大数据,因为在存取操作的时候,会占用连接大量的时间。
大数据(Large Objects):
clob:存储大文本,text文件
blob:存储二进制数据,如图像,音频、二进制文

mysql中用Text代替clob:

Text:Tinytext,text,mediumtext,longtext

blob:tinyblob,blob,mediumblob,longblob

大文本(Text){

存:

PreparedStatement的setCharacterStream( 1, reader, length)方法,填充占位符

 

//		定义sql语句
		String sql = "insert into testclob(id,resum) values(?,?)";
//		预编译sql
		PreparedStatement st = conn.prepareStatement(sql);
//		赋值占位符
		st.setString(1, "1");
//		不建议用类装载器读取文件,因为文本内容可能太大,不适合读取至内存中
		File file = new File("src/1.txt");
		Reader reader = new FileReader(file);
		st.setCharacterStream(2, reader, file.length());//reader:一个读取文本的流 length:文本的大小
//										 这是long型的,使用1.6一下的jdk必须将其转成int

读:

reader = resultSet.getCharacterStream(i);

reader = resultSet.get Clob(i).getCharacterStream();

String s = resultSet.getSting(i);

 

//		定义sql语句
		String sql = "select * from testclob where id= &#39;1&#39;";
//		预编译sql
		PreparedStatement st = conn.prepareStatement(sql);
//		无占位符直接执行sql
		ResultSet rs = st.executeQuery();
//		通过结果集获取大数据文件
		while(rs.next()){
			String id = rs.getString(1);
//			将数据库的文本数据放在一个流中,供用户使用
			Reader reader = rs.getCharacterStream(2);
//			读取数据
			Writer writer = new FileWriter("src/2.txt");
			...
		}

}

二进制(Blob){

 

存:

 

PreparedStatement的setBinaryStream(i, inputStream, length)方法,填充占位符

 

//		定义sql语句
		String sql = "insert into testblob(id,image) values(?,?)";
//		预编译sql
		PreparedStatement st = conn.prepareStatement(sql);
//		赋值占位符
		st.setString(1, "1");
//		不建议用类装载器读取文件,因为文本内容可能太大,不适合读取至内存中
		File file = new File("src/1.jpg");
		InputStream inputStream = new FileInputStream(file); 
		st.setBinaryStream(2, inputStream, file.length());//inputStream:一个读取文本的流 length:文本的大小
//										 这是long型的,使用1.6一下的jdk必须将其转成int

读:查看数据不能直接通过命令行程序读取

inputStream = resultSet.getBinaryStream(i);

inputStream = resultSet.getBlob(i).getBinaryStream();

 

//		定义sql语句
		String sql = "select * from testblob where id= &#39;1&#39;";
//		预编译sql
		PreparedStatement st = conn.prepareStatement(sql);
//		无占位符直接执行sql
		ResultSet rs = st.executeQuery();
//		通过结果集获取大数据文件
		while(rs.next()){
			String id = rs.getString(1);
//			将数据库的文本数据放在一个流中,供用户使用
			InputStream in = rs.getBinaryStream(2);
//			存储数据
			OutputStream out = new FileOutputStream("c:\\2.jpg");
			...

}

}

批处理{

用处:当需要向数据库发送一批SQL语句执行时,应避免向数据库中一条条的发送执行,应该采用批处理机制,提升效率

第一种实现方式{

存储sql语句:Statement.addBatch(sql);

执行批处理语句:executeBatch()-->执行批处理命令 clearBatch();-->清除批处理命令

 

//		定义sql语句
		String sql1 = "insert into testBatch(id,name) values(&#39;1&#39;, &#39;aaa&#39;)";
		String sql2 = "update testBatch set name = &#39;bbb&#39; where id = &#39;1&#39;";
//		获取Statement对象
		Statement st = conn.createStatement();
//		将sql语句存到对象中,实际是将sql语句存放在内部的list对象中
		st.addBatch(sql1);
		st.addBatch(sql2);
//		执行sql
		int result[] = st.executeBatch();//该数组一次存放着每一条sql语句影响数据库数据的行数
//		清除
		st.clearBatch();
1.可以发送不同的sql语句

2.性能不够

}

第二种实现方式{

//		定义sql语句
		String sql = "insert into testBatch(id,name) values(?,?)";
//		获取PreparedStatement对象
		PreparedStatement st = conn.prepareStatement(sql);
////		替换占位符,第一个sql,存到list中
//		st.setString(1, "1");
//		st.setString(2, "aaa");
//		st.addBatch();
////		替换占位符,第二个sql,存到list中
//		st.setString(1, "2");
//		st.setString(2, "bbb");
//		st.addBatch();
		//例:
		for(int i=0; i<10; i++){
			st.setString(1, ""+i);
			st.setString(2, "aa"+i);
			st.addBatch();
		}
//		执行
		st.executeBatch();
//		清除
		st.clearBatch();
1.只能发送相同sql语句,比较适合作为批量插入或批量更新

2.预先编译,提升性能

}

 

		//如果批处理的语句过多,可能造成内存溢出:使用下列处理方法
		for(int i=0; i<100000; i++){
			st.setString(1, ""+i);
			st.setString(2, "aa"+i);
			st.addBatch();
//			每次处理1000条sql语句
			if(i%1000==0){
				st.executeBatch();
				st.clearBatch();
			}
		}
//		以防sql语句未执行
		st.executeBatch();
//		清除
		st.clearBatch();

}

存储过程{

该对象作用:

1.相当于数据库的一个方法,在数据库中处理数据,然后再java程序中直接获取数据

2.没有该对象就需要使用jdbc调用来处理数据

3.一般都用在金融证券领域,都内部写了存储过程,程序员直接调用。

4.存储过程中一般在参数中指定了输入数据,和输出数据

用法:

1.先在数据库中注册一个存储过程

\

2.在java程序中使用

 

//		获取连接,通过数据库的方法,先得到一个数据,处理后再输出
		Connection conn = JdbcUtils.getConnection();
//		用存储过程名来获取存储过程的对象
		CallableStatement st = conn.prepareCall("{call demoSp(?,?)}");//通过demoSp这个名字调用存储过程
//		替换占位符
			//输入的数据
		st.setString(1, "aaa");
			//输出的数据,提供输出的类型
		st.registerOutParameter(2, Types.VARCHAR);//Types封装了数据库中常用类型
		st.execute();
//		通过第二个?获得数据,并且它已经指定了数据类型
		st.getString(2);

问题:1.只知道存储过程在java程序中的输入,输出关系。不知道会对数据库造成怎样的影响 2.不知道怎么编写存储过程

}

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
How do you handle database upgrades in MySQL?How do you handle database upgrades in MySQL?Apr 30, 2025 am 12:28 AM

The steps for upgrading MySQL database include: 1. Backup the database, 2. Stop the current MySQL service, 3. Install the new version of MySQL, 4. Start the new version of MySQL service, 5. Recover the database. Compatibility issues are required during the upgrade process, and advanced tools such as PerconaToolkit can be used for testing and optimization.

What are the different backup strategies you can use for MySQL?What are the different backup strategies you can use for MySQL?Apr 30, 2025 am 12:28 AM

MySQL backup policies include logical backup, physical backup, incremental backup, replication-based backup, and cloud backup. 1. Logical backup uses mysqldump to export database structure and data, which is suitable for small databases and version migrations. 2. Physical backups are fast and comprehensive by copying data files, but require database consistency. 3. Incremental backup uses binary logging to record changes, which is suitable for large databases. 4. Replication-based backup reduces the impact on the production system by backing up from the server. 5. Cloud backups such as AmazonRDS provide automation solutions, but costs and control need to be considered. When selecting a policy, database size, downtime tolerance, recovery time, and recovery point goals should be considered.

What is MySQL clustering?What is MySQL clustering?Apr 30, 2025 am 12:28 AM

MySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe

How do you optimize database schema design for performance in MySQL?How do you optimize database schema design for performance in MySQL?Apr 30, 2025 am 12:27 AM

Optimizing database schema design in MySQL can improve performance through the following steps: 1. Index optimization: Create indexes on common query columns, balancing the overhead of query and inserting updates. 2. Table structure optimization: Reduce data redundancy through normalization or anti-normalization and improve access efficiency. 3. Data type selection: Use appropriate data types, such as INT instead of VARCHAR, to reduce storage space. 4. Partitioning and sub-table: For large data volumes, use partitioning and sub-table to disperse data to improve query and maintenance efficiency.

How can you optimize MySQL performance?How can you optimize MySQL performance?Apr 30, 2025 am 12:26 AM

TooptimizeMySQLperformance,followthesesteps:1)Implementproperindexingtospeedupqueries,2)UseEXPLAINtoanalyzeandoptimizequeryperformance,3)Adjustserverconfigurationsettingslikeinnodb_buffer_pool_sizeandmax_connections,4)Usepartitioningforlargetablestoi

How to use MySQL functions for data processing and calculationHow to use MySQL functions for data processing and calculationApr 29, 2025 pm 04:21 PM

MySQL functions can be used for data processing and calculation. 1. Basic usage includes string processing, date calculation and mathematical operations. 2. Advanced usage involves combining multiple functions to implement complex operations. 3. Performance optimization requires avoiding the use of functions in the WHERE clause and using GROUPBY and temporary tables.

An efficient way to batch insert data in MySQLAn efficient way to batch insert data in MySQLApr 29, 2025 pm 04:18 PM

Efficient methods for batch inserting data in MySQL include: 1. Using INSERTINTO...VALUES syntax, 2. Using LOADDATAINFILE command, 3. Using transaction processing, 4. Adjust batch size, 5. Disable indexing, 6. Using INSERTIGNORE or INSERT...ONDUPLICATEKEYUPDATE, these methods can significantly improve database operation efficiency.

Steps to add and delete fields to MySQL tablesSteps to add and delete fields to MySQL tablesApr 29, 2025 pm 04:15 PM

In MySQL, add fields using ALTERTABLEtable_nameADDCOLUMNnew_columnVARCHAR(255)AFTERexisting_column, delete fields using ALTERTABLEtable_nameDROPCOLUMNcolumn_to_drop. When adding fields, you need to specify a location to optimize query performance and data structure; before deleting fields, you need to confirm that the operation is irreversible; modifying table structure using online DDL, backup data, test environment, and low-load time periods is performance optimization and best practice.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools