mysql数据库---批处理与大文本/图片类型

要在java对数据库做任何操作，第一件事当然是获取数据库连接，笔者是通过配置文件的形式加载数据库信息的，配置文件名为db.properties，内容如下 driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/customermanage username=root password=root

要在java对数据库做任何操作，第一件事当然是获取数据库连接，笔者是通过配置文件的形式加载数据库信息的，配置文件名为db.properties，内容如下

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/customermanage
username=root
password=root

ps:properties中的内容均为键值对形式，不需要用到引号

接下来是DBUtil类，获取配置，得到连接

<code class=" hljs java"><span class="hljs-keyword">package</span> com.cherry.utils;

<span class="hljs-keyword">import</span> java.sql.Connection;
<span class="hljs-keyword">import</span> java.sql.DriverManager;
<span class="hljs-keyword">import</span> java.sql.PreparedStatement;
<span class="hljs-keyword">import</span> java.sql.ResultSet;
<span class="hljs-keyword">import</span> java.sql.SQLException;
<span class="hljs-keyword">import</span> java.util.Properties;
<span class="hljs-keyword">import</span> java.util.ResourceBundle;

<span class="hljs-keyword">public</span> <span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">DBUtils</span> {</span>
    <span class="hljs-keyword">static</span> String driver;
    <span class="hljs-keyword">static</span> String url;
    <span class="hljs-keyword">static</span> String username;
    <span class="hljs-keyword">static</span> String password;
    <span class="hljs-keyword">static</span> {
        <span class="hljs-keyword">try</span> {
            <span class="hljs-comment">// Class.forName("com.mysql.jdbc.Driverr");</span>
            <span class="hljs-comment">// DriverManager.getConnection("jdbc:mysql://localhost:3306/customermanage",</span>
            <span class="hljs-comment">// "root", "root");</span>
            ResourceBundle rb = ResourceBundle.getBundle(<span class="hljs-string">"db"</span>);
            driver = rb.getString(<span class="hljs-string">"driver"</span>);

            Class.forName(driver);

            url = rb.getString(<span class="hljs-string">"url"</span>);
            username = rb.getString(<span class="hljs-string">"username"</span>);
            password = rb.getString(<span class="hljs-string">"password"</span>);

        } <span class="hljs-keyword">catch</span> (ClassNotFoundException e) {
            e.printStackTrace();
        }

    }

    <span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> Connection <span class="hljs-title">getConn</span>() <span class="hljs-keyword">throws</span> SQLException {
        <span class="hljs-keyword">return</span> DriverManager.getConnection(url, username, password);

    }

    <span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> <span class="hljs-keyword">void</span> <span class="hljs-title">releaseRes</span>(ResultSet rs, PreparedStatement ps,
            Connection conn) <span class="hljs-keyword">throws</span> SQLException {
        <span class="hljs-keyword">if</span> (rs != <span class="hljs-keyword">null</span>) {
            rs.close();
        }
        <span class="hljs-keyword">if</span> (ps != <span class="hljs-keyword">null</span>) {
            ps.close();
        }
        <span class="hljs-keyword">if</span> (conn != <span class="hljs-keyword">null</span>) {
            conn.close();
        }
    }
}
</code>

先说说批处理操作类

<code class=" hljs avrasm">package <span class="hljs-keyword">com</span><span class="hljs-preprocessor">.cherry</span><span class="hljs-preprocessor">.batch</span><span class="hljs-comment">;</span>

import java<span class="hljs-preprocessor">.sql</span><span class="hljs-preprocessor">.Connection</span><span class="hljs-comment">;</span>
import java<span class="hljs-preprocessor">.sql</span><span class="hljs-preprocessor">.PreparedStatement</span><span class="hljs-comment">;</span>
import java<span class="hljs-preprocessor">.sql</span><span class="hljs-preprocessor">.SQLException</span><span class="hljs-comment">;</span>

import <span class="hljs-keyword">com</span><span class="hljs-preprocessor">.cherry</span><span class="hljs-preprocessor">.utils</span><span class="hljs-preprocessor">.DBUtils</span><span class="hljs-comment">;</span>

public class BatchSQL {
public static void main(String[] args) throws SQLException {
    Connection conn= DBUtils<span class="hljs-preprocessor">.getConn</span>()<span class="hljs-comment">;</span>
    String sql=<span class="hljs-string">"insert into des value(?,?)"</span><span class="hljs-comment">;</span>
    PreparedStatement ps=conn<span class="hljs-preprocessor">.prepareStatement</span>(sql)<span class="hljs-comment">;</span>
    for ( int i=<span class="hljs-number">0</span><span class="hljs-comment">;i<10000 ;i++) {</span>
        ps<span class="hljs-preprocessor">.setString</span>(<span class="hljs-number">1</span>, <span class="hljs-string">"name"</span>+i)<span class="hljs-comment">;</span>
        ps<span class="hljs-preprocessor">.setString</span>(<span class="hljs-number">2</span>, <span class="hljs-string">"des"</span>+i)<span class="hljs-comment">;</span>
        ps<span class="hljs-preprocessor">.addBatch</span>()<span class="hljs-comment">;</span>
        if(i%<span class="hljs-number">100</span>==<span class="hljs-number">0</span>){
            ps<span class="hljs-preprocessor">.executeBatch</span>()<span class="hljs-comment">;</span>
            ps<span class="hljs-preprocessor">.clearBatch</span>()<span class="hljs-comment">;</span>
        }
    }
}
}
</code>

再看大文本或图片类型的存储

<code class=" hljs avrasm">package <span class="hljs-keyword">com</span><span class="hljs-preprocessor">.cherry</span><span class="hljs-preprocessor">.batch</span><span class="hljs-comment">;</span>

import java<span class="hljs-preprocessor">.io</span><span class="hljs-preprocessor">.File</span><span class="hljs-comment">;</span>
import java<span class="hljs-preprocessor">.io</span><span class="hljs-preprocessor">.FileInputStream</span><span class="hljs-comment">;</span>
import java<span class="hljs-preprocessor">.io</span><span class="hljs-preprocessor">.FileReader</span><span class="hljs-comment">;</span>
import java<span class="hljs-preprocessor">.io</span><span class="hljs-preprocessor">.Reader</span><span class="hljs-comment">;</span>
import java<span class="hljs-preprocessor">.sql</span><span class="hljs-preprocessor">.Connection</span><span class="hljs-comment">;</span>
import java<span class="hljs-preprocessor">.sql</span><span class="hljs-preprocessor">.PreparedStatement</span><span class="hljs-comment">;</span>
import java<span class="hljs-preprocessor">.util</span><span class="hljs-preprocessor">.UUID</span><span class="hljs-comment">;</span>


import <span class="hljs-keyword">com</span><span class="hljs-preprocessor">.cherry</span><span class="hljs-preprocessor">.utils</span><span class="hljs-preprocessor">.DBUtils</span><span class="hljs-comment">;</span>

public class TextSQL {

    public static void main(String[] args) {
        try {
            Connection connection = DBUtils<span class="hljs-preprocessor">.getConn</span>()<span class="hljs-comment">;</span>
            String  sql = <span class="hljs-string">"insert into des values(?,?)"</span><span class="hljs-comment">;</span>
            PreparedStatement ps = connection<span class="hljs-preprocessor">.prepareStatement</span>(sql)<span class="hljs-comment">;</span>
            ps<span class="hljs-preprocessor">.setString</span>(<span class="hljs-number">1</span>, UUID<span class="hljs-preprocessor">.randomUUID</span>()<span class="hljs-preprocessor">.toString</span>())<span class="hljs-comment">;</span>

            File file = new File(<span class="hljs-string">"f://test.txt"</span>)<span class="hljs-comment">;</span>

            Reader reader = new FileReader(file)<span class="hljs-comment">;</span>
            //这里不一定要强转，看你的mysql驱动版本，总之不转不行就转个试试
            ps<span class="hljs-preprocessor">.setCharacterStream</span>(<span class="hljs-number">2</span>, reader, (int)file<span class="hljs-preprocessor">.length</span>())<span class="hljs-comment">;</span>

            ps<span class="hljs-preprocessor">.executeUpdate</span>()<span class="hljs-comment">;</span>
        } catch (Exception e) {
            e<span class="hljs-preprocessor">.printStackTrace</span>()<span class="hljs-comment">;</span>
        }
    }

}
</code>

另外，注意导包，这里所有的数据库操作类所导的都是java.sql下的，想想也知道，面向接口编程的Java君，肯定是使用sql包的（普适性），因为mysql/sqlserver/orcale这些都是实现类，用他们肯定不合适。