JDBC对大数据的处理

在实际开发中，程序一般不把大文本或二进制数据直接保存到数据库。但当需要时则应采用以下方式

大数据也称之为LOB(Large Objects)。

         LOB分为：clob和blob

clob用于存储大文本。mysql中为Text

blob用于存储二进制数据，例如图像、声音、二进制文等。

对MySQL而言只有blob，而没有clob，mysql存储大文本采用的是Text。

Text和blob分为：

         TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT

         TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB

处理大文本

         存入方法：PreparedStatement.setCharacterStream(index,reader, length);

                                                                 注：length长度须设置，并且设置为int型 ，jdk1.6以后才有long型的length   获取方法：（对MySQL中的Text类型）

                              reader = resultSet. getCharacterStream(i);

            reader = resultSet.getClob(i).getCharacterStream();

            string s = resultSet.getString(i); 不推荐，文本太大，返回字符串会导致内存溢出

例：存储	
	public void insert() throws SQLException, FileNotFoundException{
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try{
			conn = JdbcUtils.getConnection();
			String sql = "insert into testclob(id,resume) values(?,?)";
			st = conn.prepareStatement(sql);
			st.setString(1, "1");
			//创建文件对象，为了获取文件的长度大小
			File file = new File("src/1.txt");
			//由于是普通工程，就用传统读取了
			FileReader reader = new FileReader(file);
			st.setCharacterStream(2, reader, (int) file.length());
			int num = st.executeUpdate();
			if(num>0){
				System.out.println("插入成功！！");
			}
		}finally{
			JdbcUtils.release(conn, st, rs);
		}
	}
	取出
	public void read() throws SQLException, IOException{
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try{
			conn = JdbcUtils.getConnection();
			String sql = "select id,resume from testclob where id='1'";
			st = conn.prepareStatement(sql);
			rs = st.executeQuery();
			if(rs.next()){
				// 获取流的方法有两种
				// InputStream in  = resultSet.getBlob(i).getBinaryStream();
				Reader reader = rs.getCharacterStream("resume");
				FileWriter writer = new FileWriter("c:\\1.txt");
				//读取流
				try {
					int len = 0;
					char buffer[] = new char[1024];
					while ((len = reader.read(buffer)) > 0) {
						writer.write(buffer, 0, len);
					}
				} finally {
					if (reader != null) {
						reader.close();
					}
					writer.close();
				}
			}
		}finally{
			JdbcUtils.release(conn, st, rs);
		}
	}

处理二进制数据

    存入方法：PreparedStatement. setBinaryStream(i, inputStream, length);

    获取方法：InputStreamin  = resultSet.getBinaryStream(i);

             InputStream in  = resultSet.getBlob(i).getBinaryStream(); 

例：存储
	public void insert() throws SQLException, FileNotFoundException{
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try{
			conn = JdbcUtils.getConnection();
			String sql = "insert into testblob(id,image) values(?,?)";
			st = conn.prepareStatement(sql);
			st.setString(1, "1");
			File file = new File("src/1.jpg");
			FileInputStream in = new FileInputStream(file);
			st.setBinaryStream(2, in, (int) file.length());
			st.executeUpdate();
		}finally{
			JdbcUtils.release(conn, st, rs);
		}
	}
	读取
	// 读取不能直接在DOS中读取，否则读出的全是乱码
	public void read() throws SQLException, IOException{
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try{
			conn = JdbcUtils.getConnection();
			String sql = "select id,image from testblob where id='1'";
			rs = conn.prepareStatement(sql).executeQuery();
			if(rs.next()){
				InputStream in = rs.getBinaryStream("image");
				OutputStream out = new FileOutputStream("c:\\1.jpg");;
				try {
					int len = 0;
					byte buffer[] = new byte[1024];
					while ((len = in.read(buffer)) > 0) {
						out.write(buffer, 0, len);
					}
				} finally {
					if (in != null)
						in.close();
					if (out != null)
						out.close();
				}
			}
		}finally{
			JdbcUtils.release(conn, st, rs);
		}
	}