欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

Java如何導出數(shù)據(jù)庫中的所有數(shù)據(jù)表到指定文件夾

 更新時間：2023年06月01日 15:25:46   作者：_不吃貓的魚_  
這篇文章主要介紹了Java導出數(shù)據(jù)庫中的所有數(shù)據(jù)表到指定文件夾,本文通過實例代碼給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下

Java導出數(shù)據(jù)庫中的所有數(shù)據(jù)表到指定文件夾

package com.test.util;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
public class ExportData {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "password";
        String outputFolder = "D:\\backups\\";
        String query = "SHOW TABLES";
        try (Connection con = DriverManager.getConnection(url, user, password);
             Statement stmt = con.createStatement();
             ResultSet rs = stmt.executeQuery(query)) {
            while (rs.next()) {
                String tableName = rs.getString(1);
                System.out.println(tableName);
                String sql = generateTableSQL(con, tableName);
                writeToFile(outputFolder + tableName + ".sql", sql);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    private static String generateTableSQL(Connection conn, String tableName) throws SQLException {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName);
        StringBuilder sb = new StringBuilder();
        sb.append("DROP TABLE IF EXISTS " + tableName + ";\n");
        sb.append("CREATE TABLE " + tableName + " (\n");
        int columnCount = rs.getMetaData().getColumnCount();
        for (int i = 1; i <= columnCount; i++) {
            String columnName = rs.getMetaData().getColumnName(i);
            String columnType = rs.getMetaData().getColumnTypeName(i);
            sb.append("  " + columnName + " " + columnType);
            if (i < columnCount) {
                sb.append(",");
            }
            sb.append("\n");
        }
        sb.append(");\n");
        while (rs.next()) {
            sb.append("INSERT INTO " + tableName + " VALUES (");
            for (int i = 1; i <= columnCount; i++) {
                Object value = rs.getObject(i);
                if (value == null) {
                    sb.append("NULL");
                } else if (value instanceof Number) {
                    sb.append(value);
                } else {
                    sb.append("'" + value.toString().replaceAll("'", "''") + "'");
                }
                if (i < columnCount) {
                    sb.append(",");
                }
            }
            sb.append(");\n");
        }
        rs.close();
        stmt.close();
        return sb.toString();
    }
    private static void writeToFile(String fileName, String content) {
        try {
            FileWriter writer = new FileWriter(new File(fileName));
            writer.write(content);
            writer.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

Java實現(xiàn)從數(shù)據(jù)庫導出大量數(shù)據(jù)記錄并保存到文件的方法

數(shù)據(jù)庫腳本：

-- Table "t_test" DDL
CREATE TABLE `t_test` (?
`id` int(11) NOT NULL AUTO_INCREMENT,?
`title` varchar(255) DEFAULT NULL,?
`createTime` bigint(20) DEFAULT NULL,?
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

代碼：

package com.yanek.test;?
import java.io.BufferedReader;?
import java.io.File;?
import java.io.FileOutputStream;?
import java.io.FileReader;?
import java.io.IOException;?
import java.io.OutputStreamWriter;?
import java.sql.Connection;?
import java.sql.DriverManager;?
import java.sql.PreparedStatement;?
import java.sql.ResultSet;?
import java.sql.SQLException;?
import java.sql.Statement;?
public class TestDB {?
?public static void main(String[] args) {?
? Test(); // 生成測試數(shù)據(jù)?
? //Exp();?
? //Exp(0);?
? //System.out.println(readText("/opt/id.txt"));?
?}?
?/**?
? * 導出數(shù)據(jù)?
? */?
? public static void Exp() {?
? ?Connection Conn=null;?
? ?try {?
? ? Class.forName("com.mysql.jdbc.Driver").newInstance();?
? ? String jdbcUrl = "jdbc:mysql://127.0.0.1:3306/testcms?characterEncoding=GBK";?
? ? String jdbcUsername = "root";?
? ? String jdbcPassword = "root";?
? ? Conn = DriverManager.getConnection(jdbcUrl, jdbcUsername, jdbcPassword);?
? ? System.out.println("conn"+Conn);?
? ? Exp(Conn);?
? ?} catch (SQLException e) {?
? ? e.printStackTrace();?
? ?}?
? ?catch (InstantiationException e) {?
? ? // TODO Auto-generated catch block?
? ? e.printStackTrace();?
? ?} catch (IllegalAccessException e) {?
? ? // TODO Auto-generated catch block?
? ? e.printStackTrace();?
? ?} catch (ClassNotFoundException e) {?
? ? // TODO Auto-generated catch block?
? ? e.printStackTrace();?
? ?}?
? ?finally?
? ?{?
? ? try {?
? ? ?Conn.close();?
? ? } catch (SQLException e) {?
? ? ?// TODO Auto-generated catch block?
? ? ?e.printStackTrace();?
? ? }?
? ?}?
? }?
? public static void Exp(int startid) {?
? ?Connection Conn=null;?
? ?try {?
? ? Class.forName("com.mysql.jdbc.Driver").newInstance();?
? ? String jdbcUrl = "jdbc:mysql://127.0.0.1:3306/testcms?characterEncoding=GBK";?
? ? String jdbcUsername = "root";?
? ? String jdbcPassword = "root";?
? ? Conn = DriverManager.getConnection(jdbcUrl, jdbcUsername, jdbcPassword);?
? ? System.out.println("conn"+Conn);?
? ? Exp(Conn,startid);?
? ?} catch (SQLException e) {?
? ? e.printStackTrace();?
? ?}?
? ?catch (InstantiationException e) {?
? ? // TODO Auto-generated catch block?
? ? e.printStackTrace();?
? ?} catch (IllegalAccessException e) {?
? ? // TODO Auto-generated catch block?
? ? e.printStackTrace();?
? ?} catch (ClassNotFoundException e) {?
? ? // TODO Auto-generated catch block?
? ? e.printStackTrace();?
? ?}?
? ?finally?
? ?{?
? ? try {?
? ? ?Conn.close();?
? ? } catch (SQLException e) {?
? ? ?// TODO Auto-generated catch block?
? ? ?e.printStackTrace();?
? ? }?
? ?}?
? }?
? /**?
? * 導出從startid開始的數(shù)據(jù)?
? * @param conn?
? * @param start_id?
? */?
? public static void Exp(Connection conn,int start_id) {?
? ?int counter = 0;?
? ?int startid=start_id;?
? ?boolean flag = true;?
? ?while (flag) {?
? ? flag = false;?
? ? String Sql = "SELECT * FROM t_test WHERE id>"?
? ? ? + startid + " order by id asc LIMIT 50";?
? ? System.out.println("sql===" + Sql);?
? ? try {?
? ? ?Statement stmt = conn.createStatement();?
? ? ?ResultSet rs = stmt.executeQuery(Sql);?
? ? ? while (rs.next()) {?
? ? ? ?flag = true;?
? ? ? ?int id = rs.getInt("id");?
? ? ? ?String title = rs.getString("title");?
? ? ? ?startid = id ;?
? ? ? ?counter++;?
? ? ? ?writeContent(counter+"--id--"+id+"--title-"+title+"\r\n", "/opt/","log.txt",true);?
? ? ? ?System.out.println("i="+counter+"--id--"+id+"--title-"+title);?
? ? ? }?
? ? ?rs.close();?
? ? ?stmt.close();?
? ? } catch (SQLException e) {?
? ? ?e.printStackTrace();?
? ? }?
? ?}?
? ?writeContent(""+startid, "/opt/","id.txt",false);?
? }?
? /**?
? * 導出一小時內(nèi)的數(shù)據(jù)?
? * @param conn?
? */?
? public static void Exp(Connection conn) {?
? ?int counter = 0;?
? ?//一小時內(nèi)的數(shù)據(jù)?
? ?Long timestamp = System.currentTimeMillis() - (60 * 60 * 1000);?
? ?boolean flag = true;?
? ?while (flag) {?
? ? flag = false;?
? ? String Sql = "SELECT * FROM t_test WHERE createTime>"?
? ? ? + timestamp + " LIMIT 50";?
? ? System.out.println("sql===" + Sql);?
? ? try {?
? ? ?Statement stmt = conn.createStatement();?
? ? ?ResultSet rs = stmt.executeQuery(Sql);?
? ? ?while (rs.next()) {?
? ? ? flag = true;?
? ? ? int id = rs.getInt("id");?
? ? ? String title = rs.getString("title");?
? ? ? Long lastmodifytime = rs.getLong("createTime");?
? ? ? timestamp = lastmodifytime;?
? ? ? counter++;?
? ? ? System.out.println("i="+counter+"--id--"+id+"--title-"+title);?
? ? ?}?
? ? ?rs.close();?
? ? ?stmt.close();?
? ? } catch (SQLException e) {?
? ? ?e.printStackTrace();?
? ? }?
? ?}?
? }?
? public static void Test() {?
? ?Connection Conn=null;?
? ?try {?
? ? Class.forName("com.mysql.jdbc.Driver").newInstance();?
? ? String jdbcUrl = "jdbc:mysql://127.0.0.1:3306/testcms?characterEncoding=GBK";?
? ? String jdbcUsername = "root";?
? ? String jdbcPassword = "root";?
? ? Conn = DriverManager.getConnection(jdbcUrl, jdbcUsername, jdbcPassword);?
? ? System.out.println("conn"+Conn);?
? ? for(int i=1;i<=10000;i++)?
? ? {?
? ? ?add(Conn,"testTitle"+i+"-"+System.currentTimeMillis());?
? ? }?
? ?} catch (SQLException e) {?
? ? e.printStackTrace();?
? ?}?
? ?catch (InstantiationException e) {?
? ? // TODO Auto-generated catch block?
? ? e.printStackTrace();?
? ?} catch (IllegalAccessException e) {?
? ? // TODO Auto-generated catch block?
? ? e.printStackTrace();?
? ?} catch (ClassNotFoundException e) {?
? ? // TODO Auto-generated catch block?
? ? e.printStackTrace();?
? ?}?
? ?finally?
? ?{?
? ? try {?
? ? ?Conn.close();?
? ? } catch (SQLException e) {?
? ? ?// TODO Auto-generated catch block?
? ? ?e.printStackTrace();?
? ? }?
? ?}?
? }?
? public static void add(Connection conn,String title)?
? {?
? ?PreparedStatement pstmt = null;?
? ?String insert_sql = "insert into t_test(title,createTime) values (?,?)";?
? ?System.out.println("sql="+insert_sql);?
? ?try {?
? ? pstmt = conn.prepareStatement(insert_sql);?
? ? pstmt.setString(1,title);?
? ? pstmt.setLong(2,System.currentTimeMillis());?
? ? int ret = pstmt.executeUpdate();?
? ?} catch (SQLException e) {?
? ? // TODO Auto-generated catch block?
? ? e.printStackTrace();?
? ?}?
? ?finally{?
? ? try {?
? ? ?pstmt.close();?
? ? } catch (SQLException e) {?
? ? ?// TODO Auto-generated catch block?
? ? ?e.printStackTrace();?
? ? } ?
? ?}?
? }?
? /**?
? ?* 寫入內(nèi)容到文件?
? ?*?
? ?* @param number?
? ?* @param filename?
? ?* @return?
? ?*/?
? public static boolean writeContent(String c, String dirname,String filename,boolean isAppend) {?
? ?File f=new File(dirname);?
? ?if (!f.exists())?
? ?{?
? ? ?f.mkdirs();?
? ?}?
? ?try {?
? ? FileOutputStream fos = new FileOutputStream( dirname+File.separator+filename,isAppend);?
? ? OutputStreamWriter writer = new OutputStreamWriter(fos);?
? ? writer.write(c);?
? ? writer.close();?
? ? fos.close();?
? ?} catch (IOException e) {?
? ? e.printStackTrace();?
? ? return false;?
? ?}?
? ?return true;?
? }?
? /**?
? ?* 從文件讀取內(nèi)容?
? ?*?
? ?* @param filename?
? ?* @return?
? ?*/?
? public static String readText(String filename) {?
? ?String content = "";?
? ?try {?
? ? File file = new File(filename);?
? ? if (file.exists()) {?
? ? ?FileReader fr = new FileReader(file);?
? ? ?BufferedReader br = new BufferedReader(fr);?
? ? ?String str = "";?
? ? ?String newline = "";?
? ? ?while ((str = br.readLine()) != null) {?
? ? ? content += newline + str;?
? ? ? newline = "\n";?
? ? ?}?
? ? ?br.close();?
? ? ?fr.close();?
? ? }?
? ?} catch (IOException e) {?
? ? e.printStackTrace();?
? ?}?
? ?return content;?
? }?
}

基本思想: 就是通過記錄開始記錄id,執(zhí)行多次sql來處理. 由于大數(shù)據(jù)量所以不能使用一條sql語句來輸出.否則會內(nèi)存不足導致錯誤.

主要用途: 可以使用在做接口開發(fā)時,給第三方提供數(shù)據(jù)增量輸出的場景使用.

到此這篇關(guān)于Java導出數(shù)據(jù)庫中的所有數(shù)據(jù)表到指定文件夾的文章就介紹到這了,更多相關(guān)java導出數(shù)據(jù)庫數(shù)據(jù)表內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

相關(guān)文章

最新評論