JDBC基础知识1_保存大文本_分页_批处理

 

0 保存大文本数据:

 

使用类PreparedStatement方法，将文本通过流方式读取/写入数据库

a) setCharacterStream(parameterIndex, reader, length): 保存数据

b) getCharacterStream(columnname);查询数据

 

public class JdbcClob {

	/**
	
	create table testclob
	(
		id int primary key auto_increment,
		resume text
	);
	 * @throws FileNotFoundException 
	
	
	 */
	public static void main(String[] args) throws Exception {
		find();
		//insert();
	}
	
	public static void find() throws Exception{
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try{
			conn = JdbcUtils.getConnection();
			String sql = "select resume from testclob where id=? ";
			st = conn.prepareStatement(sql);
			st.setInt(1, 3); // 第二个参数表示ID
			
			rs = st.executeQuery();
			if(rs.next()){
				Reader reader = rs.getCharacterStream("resume");
				char buffer[] = new char[1024];
				int len = 0;
				while((len=reader.read(buffer))>0){
					System.out.println(new String(buffer,0,len));
				}
			}
		}finally{
			JdbcUtils.release(rs, st, conn);
		}
	}
	
	public static void insert() throws SQLException, FileNotFoundException{
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try{
			conn = JdbcUtils.getConnection();
			String sql = "insert into testclob(resume) values(?)";
			st = conn.prepareStatement(sql);
			
			URL url = JdbcClob.class.getClassLoader().getResource("resume.txt");
			File file = new File(url.getPath());
			FileReader reader = new FileReader(url.getPath());
			st.setCharacterStream(1, reader, (int)file.length());
			
			st.executeUpdate();
		}finally{
			JdbcUtils.release(rs, st, conn);
		}
	}

}

 

 

1 保存二进制 eg 图片: 项目中常把图片上传到服务器中，而在对应表中保存图片访问路径

 

使用类PreparedStatement方法，将二进制文件通过流方式读取/写入数据库

a) setBinaryStream(parameterIndex, inputstream, length)： 保存数据

b) getBinaryStream(column);查询数据

 

 

public class JdbcImage {

	/**保存二进制数据到数据库中
		 CREATE TABLE testblob
	 (
	 	id INT PRIMARY KEY AUTO_INCREMENT,
	 	image LONGBLOB
	 );
	 
	 * @param args
	 * @throws Exception 
	 */
	public static void main(String[] args) throws Exception {
		
		//insert();
		find();

	}
	
	public static void find() throws Exception{
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try{
			conn = JdbcUtils.getConnection();
			String sql = "select image from testblob where id=1";
			st = conn.prepareStatement(sql);
			rs = st.executeQuery();
			
			if(rs.next()){
				InputStream in = rs.getBinaryStream("image");
				byte buffer[] = new byte[1024];
				int len = 0;
				
				FileOutputStream out = new FileOutputStream("D:\\1.jpg");
				while((len=in.read(buffer))>0){
					out.write(buffer, 0, len);
				}
				in.close();
				out.close();
			}
			
			
		}finally{
			JdbcUtils.release(rs, st, conn);
		}
	}
	
	public static  void insert() throws Exception{
		
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try{
			conn = JdbcUtils.getConnection();
			String sql = "insert into testblob(image) values(?)";
			st = conn.prepareStatement(sql);
			URL url = JdbcImage.class.getClassLoader().getResource("resume.jpg");
			File file = new File(url.getPath());
			FileInputStream in = new FileInputStream(url.getPath());
			
			st.setBinaryStream(1, in, (int) file.length());
			st.executeUpdate();
			
			
		}finally{
			JdbcUtils.release(rs, st, conn);
		}
	}

}

 

2 JDBC批处理:

 

 第一种方式：
Statement.addBatch(sql)
执行批处理SQL语句
executeBatch()方法：执行批处理命令
clearBatch()方法：清除批处理命令

 

 

Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtil.getConnection();
String sql1 = "insert into user(name,password,email,birthday) 
	values('kkk','123','abc@sina.com','1978-08-08')";
String sql2 = "update user set password='123456' where id=3";
st = conn.createStatement();
st.addBatch(sql1);  //把SQL语句加入到批命令中
st.addBatch(sql2);  //把SQL语句加入到批命令中
st.executeBatch();
} finally{
	JdbcUtil.free(conn, st, rs);
}

 

 采用Statement.addBatch(sql)方式实现批处理：
优点：可以向数据库发送多条不同的ＳＱＬ语句。
缺点：
SQL语句没有预编译。
当向数据库发送多条语句相同，但仅参数不同的SQL语句时，需重复写上很多条SQL语句。例如：

  Insert into user(name,password) values(‘aa’,’111’);
  Insert into user(name,password) values(‘bb’,’222’);
  Insert into user(name,password) values(‘cc’,’333’);
  Insert into user(name,password) values(‘dd’,’444’);

 

 

 第二种方式：

PreparedStatement.addBatch()
采用PreparedStatement.addBatch()实现批处理
优点：发送的是预编译后的SQL语句，执行效率高。
缺点：只能应用在SQL语句相同，但参数不同的批处理中。因此此种形式的批处理经常用于在同一个表中批量插入数据，或批量更新表的数据。

 

写法:

conn = JdbcUtil.getConnection();
String sql = "insert into user(name,password,email,birthday) values(?,?,?,?)";
st = conn.prepareStatement(sql);
for(int i=0;i<50000;i++){ // 5000可以理解成list.size(),list是封装参数的集合eg 5000个人的信息
st.setString(1, "aaa" + i);
st.setString(2, "123" + i);
st.setString(3, "aaa" + i + "@sina.com");
st.setDate(4,new Date(1980, 10, 10));

st.addBatch();
if(i%1000==0){
st.executeBatch();
st.clearBatch();
}
}
st.executeBatch();

 

 

3 JDBC分页:

 

3.1 MySQL分页支持的实现写法：
Select * from table limit M,N
M：记录开始索引位置(索引的角标从0开始计数,表第一个记录的角标为0)
N：取多少条记录

3.2 Oracle分页语句：  细节见本人博客oracle分页查询 那篇文章

select * from (
   select rownum r_, row_.*  from (
     select * from student order by id
   ) row_ where rownum <=5
         ) where r_>=1

1位置：起始索引位置。
5位置：结束索引位置。

3.3 分页核心在于Page对象的设计，然后结合不同数据库分页查询具体sql写法，这两块实现分页，

page代码如下:

 

public class Page {

	private int totalrecord;  //总纪录数
	private int totalpage;  //总页数
	private int pagesize = 3;  //每页显示多少条
	
	private int startindex; //记住从数据库哪个位置开始取页面数据
	private List list;  //该页的数据
	
	private int foreachbegin;// 最左侧页码   
	private int foreachend;// 最右侧页码
	
	public Page(int totalrecord,int pagenum){
		
		if(totalrecord%pagesize==0){
			this.totalpage = totalrecord/pagesize;
		}else{
			this.totalpage = totalrecord/pagesize + 1;
		}
		
		startindex = (pagenum-1)*pagesize;
		
		
		if(this.totalpage<=10){
			foreachbegin = 1;
			foreachend = totalpage;
		}else{
			foreachbegin = pagenum-4;
			foreachend = pagenum + 5;
			
			if(foreachbegin<=0){
				foreachbegin = 1;
				foreachend = 10;
			}
			if(foreachend>this.totalpage){
				foreachend = totalpage;
				foreachbegin = this.totalpage-10+1;
			}
			
		}
		
		
	}

     params的 getter()  setter() 方法.... }

 

 

mysql数据库分页写法如下：

	public List getRecordByPage(int startindex,int pagesize){
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		
		try{
			
			conn = JdbcUtils.getConnection();
			String sql = "select * from student limit ?,?";
			st = conn.prepareStatement(sql);
			st.setInt(1, startindex);
			st.setInt(2, pagesize);
			
			rs = st.executeQuery();
			List list = new ArrayList();
			while(rs.next()){
				Student student = new Student();
				student.setChinese(rs.getDouble("chinese"));
				student.setEnglish(rs.getDouble("english"));
				student.setId(rs.getInt("id"));
				student.setMath(rs.getDouble("math"));
				student.setName(rs.getString("name"));
				
				list.add(student);
			}
			return list;
		}catch(Exception e){
			throw new RuntimeException(e);
			
		}finally{
			JdbcUtils.release(rs, st, conn);
		}
		
		
	}

 

 

3.4 连接mysql数据库下分页案例：

 

效果如下:

 

表如下:

CREATE TABLE student(
	id INT,
	NAME VARCHAR(20),
	chinese FLOAT,
	english FLOAT,
	math FLOAT
);

 

 

案例代码已打包，见 附件 jdbc_page.rar