jdbc操作大段文本和二进制数据类型

使用jdbc操作大段文本和二进制数据类型

1 clob类型 用以存储大容量的文本
mysql中对应的字段类型是mediumText ,text 和longTetxt

向数据库中插入大段的文本

public static void insert(){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try
		{
			String sql = "insert into clob(content) values(?)";

			conn = JdbcUtils.getInstance().getConnection();
			pstmt = conn.prepareStatement(sql);

			File file = new File("src/jdbc/CRUD.java");

			BufferedReader br = new BufferedReader(new FileReader(file));
			pstmt.setCharacterStream(1, br, file.length());
			
			int result = pstmt.executeUpdate();

			System.out.println(result);
			br.close();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		catch (FileNotFoundException e)
		{
			e.printStackTrace();
		}
		catch (IOException e)
		{
			e.printStackTrace();
		}
		finally
		{
			JdbcUtils.classAll(conn, pstmt, rs);
		}
	}

读取clob类型的数据

public static void read(){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try
		{
			conn = JdbcUtils.getInstance().getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select content from clob where id = 2");
			while (rs.next())
			{
				Clob clob = rs.getClob("content");
				Reader read = clob.getCharacterStream();
				
				//Reader read = rs.getCharacterStream("content");
				File file = new File("CRUD_bak.java");
				
				BufferedWriter bw = new BufferedWriter(new FileWriter(file));
				char[] buffer = new char[1024];
				/*for(int i; (i=read.read(buffer)) >0;){
					bw.write(buffer, 0, i);
				}*/
    int c = -1;
				while((c = read.read(buffer)) != -1){
					bw.write(buffer, 0, c);
				}

				bw.close();
				read.close();
				//System.out.println(rs.getString("content"));
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		catch (IOException e)
		{
			e.printStackTrace();
		}
		finally
		{
			JdbcUtils.classAll(conn, stmt, rs);
		}
	}

2 blob类型用以存储二进制的数据
mysql对应的字段类型: mediumBlob,Bolb,LongBlob

插入数据

public static void insertBlob()
	{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try
		{
			String sql = "insert into blob_(content) values(?)";

			conn = JdbcUtils.getInstance().getConnection();
			pstmt = conn.prepareStatement(sql);

			File file = new File("Ext2.2英文API.CHM");

			BufferedInputStream fis = new BufferedInputStream(
					new FileInputStream(file));
			pstmt.setBinaryStream(1, fis, fis.available());

			int result = pstmt.executeUpdate();

			System.out.println(result);
			fis.close();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		catch (FileNotFoundException e)
		{
			e.printStackTrace();
		}
		catch (IOException e)
		{
			e.printStackTrace();
		}
		finally
		{
			JdbcUtils.classAll(conn, pstmt, rs);
		}
	}

读取bolb的数据

public static void readBlob()
	{
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try
		{
			conn = JdbcUtils.getInstance().getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select content from blob_ where id = 3");
			while (rs.next())
			{
				//Blob blob = rs.getBlob("content");
				//InputStream is = blob.getBinaryStream();
				
				InputStream is = rs.getBinaryStream("content");
				File file = new File("Ext2.2英文API_bak.CHM");
				FileOutputStream os = new FileOutputStream(file);
				int i =0;
				while((i = is.read()) != -1){
					os.write(i);
				}
				os.close();
				is.close();
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		catch (IOException e)
		{
			e.printStackTrace();
		}
		finally
		{
			JdbcUtils.classAll(conn, stmt, rs);
		}
	}