当前位置：Gxlcms > 数据库问题 > jdbc基础 (三) 大文本、二进制数据处理

jdbc基础 (三) 大文本、二进制数据处理

时间：2021-07-01 10:21:17 帮助过：4人阅读

   分为：CLOB和BLOB，即大文本和大二进制数据

CLOB：用于存储大文本

BLOB：用于存储二进制数据，例如图像、声音、二进制文件

在mysql中,只有BLOB,没有CLOB，mysql存储大文本用TEXT

 

TEXT  分为：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT

BLOB 分为：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB

取值范围如下图：

技术分享

 

下面来看具体的代码实现：

  1. <span style="color: #008080;"> 1</span> <span style="color: #0000ff;">package</span><span style="color: #000000;"> com.cream.ice.jdbc;
  2. </span><span style="color: #008080;"> 2</span> 
  3. <span style="color: #008080;"> 3</span> <span style="color: #0000ff;">import</span><span style="color: #000000;"> java.io.File;
  4. </span><span style="color: #008080;"> 4</span> <span style="color: #0000ff;">import</span><span style="color: #000000;"> java.io.FileNotFoundException;
  5. </span><span style="color: #008080;"> 5</span> <span style="color: #0000ff;">import</span><span style="color: #000000;"> java.io.FileReader;
  6. </span><span style="color: #008080;"> 6</span> <span style="color: #0000ff;">import</span><span style="color: #000000;"> java.io.FileWriter;
  7. </span><span style="color: #008080;"> 7</span> <span style="color: #0000ff;">import</span><span style="color: #000000;"> java.io.Reader;
  8. </span><span style="color: #008080;"> 8</span> <span style="color: #0000ff;">import</span><span style="color: #000000;"> java.io.Writer;
  9. </span><span style="color: #008080;"> 9</span> <span style="color: #0000ff;">import</span><span style="color: #000000;"> java.sql.Connection;
  10. </span><span style="color: #008080;">10</span> <span style="color: #0000ff;">import</span><span style="color: #000000;"> java.sql.PreparedStatement;
  11. </span><span style="color: #008080;">11</span> <span style="color: #0000ff;">import</span><span style="color: #000000;"> java.sql.ResultSet;
  12. </span><span style="color: #008080;">12</span> <span style="color: #0000ff;">import</span><span style="color: #000000;"> java.sql.SQLException;
  13. </span><span style="color: #008080;">13</span> 
  14. <span style="color: #008080;">14</span> <span style="color: #0000ff;">import</span><span style="color: #000000;"> org.junit.Test;
  15. </span><span style="color: #008080;">15</span> 
  16. <span style="color: #008080;">16</span> <span style="color: #008000;">/**</span>
  17. <span style="color: #008080;">17</span> <span style="color: #008000;"> * 大文本数据操作
  18. </span><span style="color: #008080;">18</span> <span style="color: #008000;"> * 
  19. </span><span style="color: #008080;">19</span> <span style="color: #008000;"> * 假设数据库中已存在表test: 
  20. </span><span style="color: #008080;">20</span> <span style="color: #008000;"> * create table test(
  21. </span><span style="color: #008080;">21</span> <span style="color: #008000;"> *        id int primary key,
  22. </span><span style="color: #008080;">22</span> <span style="color: #008000;"> *        content longtext
  23. </span><span style="color: #008080;">23</span> <span style="color: #008000;"> * );
  24. </span><span style="color: #008080;">24</span> <span style="color: #008000;"> * 
  25. </span><span style="color: #008080;">25</span> <span style="color: #008000;"> * </span><span style="color: #808080;">@author</span><span style="color: #008000;"> ice
  26. </span><span style="color: #008080;">26</span> <span style="color: #008000;"> *
  27. </span><span style="color: #008080;">27</span>  <span style="color: #008000;">*/</span>
  28. <span style="color: #008080;">28</span> <span style="color: #0000ff;">public</span> <span style="color: #0000ff;">class</span><span style="color: #000000;"> ClobDemo {
  29. </span><span style="color: #008080;">29</span>     
  30. <span style="color: #008080;">30</span>     Connection connection = <span style="color: #0000ff;">null</span><span style="color: #000000;">;
  31. </span><span style="color: #008080;">31</span>     PreparedStatement statement = <span style="color: #0000ff;">null</span><span style="color: #000000;">;
  32. </span><span style="color: #008080;">32</span>     ResultSet resultSet=<span style="color: #0000ff;">null</span><span style="color: #000000;">;
  33. </span><span style="color: #008080;">33</span>     
  34. <span style="color: #008080;">34</span> <span style="color: #000000;">    @Test
  35. </span><span style="color: #008080;">35</span>     <span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> add(){
  36. </span><span style="color: #008080;">36</span>         <span style="color: #0000ff;">try</span><span style="color: #000000;"> {
  37. </span><span style="color: #008080;">37</span>             connection=<span style="color: #000000;">JdbcUtils.getConnection();
  38. </span><span style="color: #008080;">38</span>             statement=connection.prepareStatement("insert into test (id,content) values (?,?)"<span style="color: #000000;">);
  39. </span><span style="color: #008080;">39</span>             statement.setInt(1, 1<span style="color: #000000;">);
  40. </span><span style="color: #008080;">40</span>             
  41. <span style="color: #008080;">41</span>             <span style="color: #008000;">//</span><span style="color: #008000;">大文本要使用流的形式。将d:/test.txt内容添加至该记录的content字段</span>
  42. <span style="color: #008080;">42</span>             File file = <span style="color: #0000ff;">new</span> File("d:/test.txt"<span style="color: #000000;">);
  43. </span><span style="color: #008080;">43</span>             Reader reader = <span style="color: #0000ff;">new</span><span style="color: #000000;"> FileReader(file);
  44. </span><span style="color: #008080;">44</span>             <span style="color: #008000;">//</span><span style="color: #008000;">不能使用long的参数，因为mysql根本支持不到那么大的数据，所以没有实现</span>
  45. <span style="color: #008080;">45</span>             statement.setCharacterStream(2, reader, (<span style="color: #0000ff;">int</span><span style="color: #000000;">)file.length());
  46. </span><span style="color: #008080;">46</span>             
  47. <span style="color: #008080;">47</span>             <span style="color: #0000ff;">int</span> i =<span style="color: #000000;"> statement.executeUpdate();
  48. </span><span style="color: #008080;">48</span>             <span style="color: #0000ff;">if</span>(i>0<span style="color: #000000;">)
  49. </span><span style="color: #008080;">49</span>                 System.out.println("插入成功"<span style="color: #000000;">);
  50. </span><span style="color: #008080;">50</span>             
  51. <span style="color: #008080;">51</span>         } <span style="color: #0000ff;">catch</span><span style="color: #000000;"> (SQLException e) {
  52. </span><span style="color: #008080;">52</span> <span style="color: #000000;">            e.printStackTrace();
  53. </span><span style="color: #008080;">53</span>         } <span style="color: #0000ff;">catch</span><span style="color: #000000;"> (FileNotFoundException e) {
  54. </span><span style="color: #008080;">54</span> <span style="color: #000000;">            e.printStackTrace();
  55. </span><span style="color: #008080;">55</span>         } <span style="color: #0000ff;">finally</span><span style="color: #000000;">{
  56. </span><span style="color: #008080;">56</span>             JdbcUtils.releaseResources(<span style="color: #0000ff;">null</span><span style="color: #000000;">, statement, connection);
  57. </span><span style="color: #008080;">57</span> <span style="color: #000000;">        }
  58. </span><span style="color: #008080;">58</span> <span style="color: #000000;">    }
  59. </span><span style="color: #008080;">59</span>     
  60. <span style="color: #008080;">60</span> <span style="color: #000000;">    @Test
  61. </span><span style="color: #008080;">61</span>     <span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> read(){
  62. </span><span style="color: #008080;">62</span>         <span style="color: #0000ff;">try</span><span style="color: #000000;"> {
  63. </span><span style="color: #008080;">63</span>             connection =<span style="color: #000000;"> JdbcUtils.getConnection();
  64. </span><span style="color: #008080;">64</span>             statement = connection.prepareStatement("select * from test where id=?"<span style="color: #000000;">);
  65. </span><span style="color: #008080;">65</span>             statement.setInt(1, 1<span style="color: #000000;">);
  66. </span><span style="color: #008080;">66</span> 
  67. <span style="color: #008080;">67</span>             <span style="color: #008000;">//</span><span style="color: #008000;">将读取内容保存到E盘上</span>
  68. <span style="color: #008080;">68</span>             resultSet =<span style="color: #000000;"> statement.executeQuery();
  69. </span><span style="color: #008080;">69</span>             <span style="color: #0000ff;">while</span><span style="color: #000000;">(resultSet.next()){
  70. </span><span style="color: #008080;">70</span>                 Reader reader = resultSet.getCharacterStream("content"<span style="color: #000000;">);
  71. </span><span style="color: #008080;">71</span>                 Writer writer = <span style="color: #0000ff;">new</span> FileWriter("e:/test.txt"<span style="color: #000000;">);
  72. </span><span style="color: #008080;">72</span>                 <span style="color: #0000ff;">char</span> buffer[] = <span style="color: #0000ff;">new</span> <span style="color: #0000ff;">char</span>[1024<span style="color: #000000;">];
  73. </span><span style="color: #008080;">73</span>                 <span style="color: #0000ff;">int</span> len = -1<span style="color: #000000;">;
  74. </span><span style="color: #008080;">74</span>                 <span style="color: #0000ff;">while</span>((len=reader.read(buffer))!=-1<span style="color: #000000;">){
  75. </span><span style="color: #008080;">75</span>                     writer.write(buffer, 0<span style="color: #000000;">, len);
  76. </span><span style="color: #008080;">76</span> <span style="color: #000000;">                }
  77. </span><span style="color: #008080;">77</span> <span style="color: #000000;">                reader.close();
  78. </span><span style="color: #008080;">78</span> <span style="color: #000000;">                writer.close();
  79. </span><span style="color: #008080;">79</span> <span style="color: #000000;">            }
  80. </span><span style="color: #008080;">80</span>         } <span style="color: #0000ff;">catch</span><span style="color: #000000;"> (Exception e) {
  81. </span><span style="color: #008080;">81</span> <span style="color: #000000;">            e.printStackTrace();
  82. </span><span style="color: #008080;">82</span>         } <span style="color: #0000ff;">finally</span><span style="color: #000000;">{
  83. </span><span style="color: #008080;">83</span> <span style="color: #000000;">            JdbcUtils.releaseResources(resultSet, statement, connection);
  84. </span><span style="color: #008080;">84</span> <span style="color: #000000;">        }
  85. </span><span style="color: #008080;">85</span> <span style="color: #000000;">    }
  86. </span><span style="color: #008080;">86</span> }
  1. <span style="color: #008080;"> 1</span> <span style="color: #0000ff;">package</span><span style="color: #000000;"> com.cream.ice.jdbc;
  2. </span><span style="color: #008080;"> 2</span> 
  3. <span style="color: #008080;"> 3</span> <span style="color: #0000ff;">import</span><span style="color: #000000;"> java.io.FileInputStream;
  4. </span><span style="color: #008080;"> 4</span> <span style="color: #0000ff;">import</span><span style="color: #000000;"> java.io.FileOutputStream;
  5. </span><span style="color: #008080;"> 5</span> <span style="color: #0000ff;">import</span><span style="color: #000000;"> java.io.InputStream;
  6. </span><span style="color: #008080;"> 6</span> <span style="color: #0000ff;">import</span><span style="color: #000000;"> java.io.OutputStream;
  7. </span><span style="color: #008080;"> 7</span> <span style="color: #0000ff;">import</span><span style="color: #000000;"> java.sql.Connection;
  8. </span><span style="color: #008080;"> 8</span> <span style="color: #0000ff;">import</span><span style="color: #000000;"> java.sql.PreparedStatement;
  9. </span><span style="color: #008080;"> 9</span> <span style="color: #0000ff;">import</span><span style="color: #000000;"> java.sql.ResultSet;
  10. </span><span style="color: #008080;">10</span> 
  11. <span style="color: #008080;">11</span> <span style="color: #0000ff;">import</span><span style="color: #000000;"> org.junit.Test;
  12. </span><span style="color: #008080;">12</span> 
  13. <span style="color: #008080;">13</span> <span style="color: #008000;">/**</span>
  14. <span style="color: #008080;">14</span> <span style="color: #008000;"> * 大二进制数据操作
  15. </span><span style="color: #008080;">15</span> <span style="color: #008000;"> * 
  16. </span><span style="color: #008080;">16</span> <span style="color: #008000;"> * 假设数据库中已存在表test: 
  17. </span><span style="color: #008080;">17</span> <span style="color: #008000;"> * create table test(
  18. </span><span style="color: #008080;">18</span> <span style="color: #008000;"> *        id int primary key,
  19. </span><span style="color: #008080;">19</span> <span style="color: #008000;"> *        content longblob
  20. </span><span style="color: #008080;">20</span> <span style="color: #008000;"> * );
  21. </span><span style="color: #008080;">21</span> <span style="color: #008000;"> * 
  22. </span><span style="color: #008080;">22</span> <span style="color: #008000;"> * </span><span style="color: #808080;">@author</span><span style="color: #008000;"> ice
  23. </span><span style="color: #008080;">23</span> <span style="color: #008000;"> *
  24. </span><span style="color: #008080;">24</span>  <span style="color: #008000;">*/</span>
  25. <span style="color: #008080;">25</span> <span style="color: #0000ff;">public</span> <span style="color: #0000ff;">class</span><span style="color: #000000;"> BlobDemo {
  26. </span><span style="color: #008080;">26</span>     Connection connection = <span style="color: #0000ff;">null</span><span style="color: #000000;">;
  27. </span><span style="color: #008080;">27</span>     PreparedStatement statement = <span style="color: #0000ff;">null</span><span style="color: #000000;">;
  28. </span><span style="color: #008080;">28</span>     ResultSet resultSet=<span style="color: #0000ff;">null</span><span style="color: #000000;">;
  29. </span><span style="color: #008080;">29</span>     
  30. <span style="color: #008080;">30</span> <span style="color: #000000;">    @Test
  31. </span><span style="color: #008080;">31</span>     <span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> add(){
  32. </span><span style="color: #008080;">32</span>         <span style="color: #0000ff;">try</span><span style="color: #000000;"> {
  33. </span><span style="color: #008080;">33</span>             connection=<span style="color: #000000;">JdbcUtils.getConnection();
  34. </span><span style="color: #008080;">34</span>             statement=connection.prepareStatement("insert into test (id,content) values (?,?)"<span style="color: #000000;">);
  35. </span><span style="color: #008080;">35</span>             statement.setInt(1, 1<span style="color: #000000;">);
  36. </span><span style="color: #008080;">36</span>             
  37. <span style="color: #008080;">37</span>             InputStream in = <span style="color: #0000ff;">new</span> FileInputStream("d:/test.jpg"<span style="color: #000000;">);
  38. </span><span style="color: #008080;">38</span>             statement.setBinaryStream(2<span style="color: #000000;">, in, in.available());
  39. </span><span style="color: #008080;">39</span>             
  40. <span style="color: #008080;">40</span>             <span style="color: #0000ff;">int</span> i =<span style="color: #000000;"> statement.executeUpdate();
  41. </span><span style="color: #008080;">41</span>             <span style="color: #0000ff;">if</span>(i>0<span style="color: #000000;">)
  42. </span><span style="color: #008080;">42</span>                 System.out.println("插入成功"<span style="color: #000000;">);
  43. </span><span style="color: #008080;">43</span>             
  44. <span style="color: #008080;">44</span>         } <span style="color: #0000ff;">catch</span><span style="color: #000000;"> (Exception e) {
  45. </span><span style="color: #008080;">45</span> <span style="color: #000000;">            e.printStackTrace();
  46. </span><span style="color: #008080;">46</span>         } <span style="color: #0000ff;">finally</span><span style="color: #000000;">{
  47. </span><span style="color: #008080;">47</span>             JdbcUtils.releaseResources(<span style="color: #0000ff;">null</span><span style="color: #000000;">, statement, connection);
  48. </span><span style="color: #008080;">48</span> <span style="color: #000000;">        }
  49. </span><span style="color: #008080;">49</span> <span style="color: #000000;">    }
  50. </span><span style="color: #008080;">50</span>     
  51. <span style="color: #008080;">51</span> <span style="color: #000000;">    @Test
  52. </span><span style="color: #008080;">52</span>     <span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> read(){
  53. </span><span style="color: #008080;">53</span>         <span style="color: #0000ff;">try</span><span style="color: #000000;"> {
  54. </span><span style="color: #008080;">54</span>             connection =<span style="color: #000000;"> JdbcUtils.getConnection();
  55. </span><span style="color: #008080;">55</span>             statement = connection.prepareStatement("select * from test where id=?"<span style="color: #000000;">);
  56. </span><span style="color: #008080;">56</span>             statement.setInt(1, 1<span style="color: #000000;">);
  57. </span><span style="color: #008080;">57</span> 
  58. <span style="color: #008080;">58</span>             <span style="color: #008000;">//</span><span style="color: #008000;">保存到E盘上</span>
  59. <span style="color: #008080;">59</span>             resultSet =<span style="color: #000000;"> statement.executeQuery();
  60. </span><span style="color: #008080;">60</span>             <span style="color: #0000ff;">while</span><span style="color: #000000;">(resultSet.next()){
  61. </span><span style="color: #008080;">61</span>                 InputStream in = resultSet.getBinaryStream("content"<span style="color: #000000;">);
  62. </span><span style="color: #008080;">62</span>                 OutputStream out = <span style="color: #0000ff;">new</span> FileOutputStream("e:/test.jpg"<span style="color: #000000;">);
  63. </span><span style="color: #008080;">63</span>                 <span style="color: #0000ff;">byte</span> b[] = <span style="color: #0000ff;">new</span> <span style="color: #0000ff;">byte</span>[1024<span style="color: #000000;">];
  64. </span><span style="color: #008080;">64</span>                 <span style="color: #0000ff;">int</span> len = -1<span style="color: #000000;">;
  65. </span><span style="color: #008080;">65</span>                 <span style="color: #0000ff;">while</span>((len=in.read(b))!=-1<span style="color: #000000;">){
  66. </span><span style="color: #008080;">66</span>                     out.write(b, 0<span style="color: #000000;">, len);
  67. </span><span style="color: #008080;">67</span> <span style="color: #000000;">                }
  68. </span><span style="color: #008080;">68</span> <span style="color: #000000;">                out.close();
  69. </span><span style="color: #008080;">69</span> <span style="color: #000000;">                in.close();
  70. </span><span style="color: #008080;">70</span> <span style="color: #000000;">            }
  71. </span><span style="color: #008080;">71</span>         } <span style="color: #0000ff;">catch</span><span style="color: #000000;"> (Exception e) {
  72. </span><span style="color: #008080;">72</span> <span style="color: #000000;">            e.printStackTrace();
  73. </span><span style="color: #008080;">73</span>         } <span style="color: #0000ff;">finally</span><span style="color: #000000;">{
  74. </span><span style="color: #008080;">74</span> <span style="color: #000000;">            JdbcUtils.releaseResources(resultSet, statement, connection);
  75. </span><span style="color: #008080;">75</span> <span style="color: #000000;">        }
  76. </span><span style="color: #008080;">76</span> <span style="color: #000000;">    }
  77. </span><span style="color: #008080;">77</span> }

 

这里使用了我上一篇jdbc基础中的JdbcUtils工具类，同时也使用了单元测试来测试两个成员方法，代码已亲测可运行。

 

jdbc基础 (三) 大文本、二进制数据处理

标签：

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！