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.getStrin<span style="color:transparent">本文来源gaodai#ma#com搞*!代#%^码$网!</span>g(<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这些都是实现类，用他们肯定不合适。

喜欢 (0)赏

[搞代码]

分享 (0)