java導出MS sqlserver的某表數據 – JAVA編程語言程序開發技術文章

java導出MS sqlserver的某表數據


遇到一Linux系統的Jboss的java環境連MS sqlserver的數據庫,想要導出某表數據存為csv文件,用java實現如下:


import java.io.BufferedOutputStream;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class SqlServerDataDump {


        public static void  main(String[] arg){
           SqlServerDataDump sqlDump = new SqlServerDataDump();


           sqlDump.dataDump();          
        }


    public void dataDump() {
        final String databaseName = “dbname”;
        final String userName = “sa”;
        final String passWord = “passwd”;
        final String serverHost = “192.168.1.10”;
        final int serverPort = 1433;


        final String fileName = “/home/test/test.csv”;


        // Create a variable for the connection string.
        final String connectionUrl = “jdbc:sqlserver://” + serverHost + “:”
                + serverPort + “;database=” + databaseName + “;user=”
                + userName + “;password=” + passWord+”;selectMethod=cursor”;


        // Declare the JDBC objects.
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;


        final String querySql = “select * from user”;


        File outputFile = new File(fileName);


        PrintStream printStream = null;


//        if (!outputFile.isFile() || !outputFile.canWrite()) {
//            // is dir or readonly
//            return;
//        }


        try {


            printStream = new PrintStream(outputFile);


            Class.forName(“com.microsoft.sqlserver.jdbc.SQLServerDriver”);
            con = DriverManager.getConnection(connectionUrl);
                        System.out.println(“get connection success.”);
            stmt = con.createStatement();
            rs = stmt.executeQuery(querySql);
                        System.out.println(“dump data begin.”);


            while (rs.next()) {
                String col1 = rs.getString(1);
                String col2 = rs.getString(2);
                String col3 = rs.getString(3);
                String col4 = rs.getString(4);
                String col5 = rs.getString(5);
                String col6 = rs.getString(6);
                String col7 = rs.getString(7);
                String col8 = rs.getString(8);
                String col9 = rs.getString(9);
                String col10 = rs.getString(10);
                String col11 = rs.getString(11);
                String col12 = rs.getString(12);
                String col13 = rs.getString(13);
                String col14 = rs.getString(14);
                String col15 = rs.getString(15);
                String col16 = rs.getString(16);
                String col17 = rs.getString(17);
                String col18 = rs.getString(18);
                String col19 = rs.getString(19);
                String col20 = rs.getString(20);
                …………………………………..


                printStream.println(col1 + “,” + col2 + “,” + col3 + “,” + col4 + “,” + col5 + “,” + col6 + “,” + col7 + “,” + col8 + “,” + col9 + “,” + col10 + “,” + col11 + “,” + col12 + “,” + col13 + “,” + col14 + “,” + col15 + “,” + col16 + “,” + col17 + “,” + col18 + “,” + col19 + “,” + col20 + “,” + ………..);
            }


                        System.out.println(“end dump data.”);


        } catch (Exception e) {
                     e.printStackTrace();


        } finally {
            if (null != rs) {
                try {
                    rs.close();
           &n

發佈留言