Java – Put SQL Server select results into a csv file No.70

sudo snap install --classic code

(e.g)
wget https://downloads.apache.org/maven/maven-3/3.8.3/binaries/apache-maven-3.8.3-bin.tar.gz  -P /tmp/
sudo tar xzvf /tmp/apache-maven-*.tar.gz -C /opt/
/opt/apache-maven-3.8.3/bin/mvn -version

	Apache Maven 3.8.3 (xxxx)
	Maven home: /opt/apache-maven-3.8.3
	Java version: 1.8.0_312, vendor: Azul Systems, Inc., runtime: /usr/lib/jvm/zulu-8-azure-amd64/jre
	Default locale: en_US, platform encoding: UTF-8
	OS name: "linux", version: "5.11.0-40-generic", arch: "amd64", family: "unix"

#Set the directory path of /bin/ in Apache-maven-3.8.3 to environment variable
例)
PATH=$PATH:/opt/apache-maven-3.8.3/bin/

mkidr sqlsrvtest
cd sqlsrvtest

mvn archetype:generate -DinteractiveMode=false -DgroupId=org.example.sqlsrv -DartifactId=sqlSrvOutput -DarchetypeArtifactId=maven-archetype-quickstart -DarchetypeVersion=1.4

（A part of output）
xxxx
[INFO] Generating project in Batch mode
[INFO] Archetype repository not defined. Using the one from [org.apache.maven.archetypes:maven-archetype-quickstart:1.4] found in catalog remote
[INFO] -------------------------------------------------------------------
[INFO] Using following parameters for creating project from Archetype: maven-archetype-quickstart:1.4
[INFO] --------------------------------------------------------------------
[INFO] Parameter: groupId, Value: org.example.sqlsrv
[INFO] Parameter: artifactId, Value: sqlSrvOutput
[INFO] Parameter: version, Value: 1.0-SNAPSHOT
[INFO] Parameter: package, Value: org.example.sqlsrv
[INFO] Parameter: packageInPathFormat, Value: org/example/sqlsrv
[INFO] Parameter: version, Value: 1.0-SNAPSHOT
[INFO] Parameter: package, Value: org.example.sqlsrv
[INFO] Parameter: groupId, Value: org.example.sqlsrv
[INFO] Parameter: artifactId, Value: sqlSrvOutput
[INFO] Project created from Archetype in dir: /home/xxx/sqlSrvOutput
[INFO] --------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] --------------------------------------------------------------------
[INFO] Total time:  3.063 s
[INFO] Finished at: 2022-11-08T10:51:27+09:00
[INFO] --------------------------------------------------------------------

rm ./src/main/java/org/example/sqlsrv/App.java
rm ./src/test/java/org/example/sqlsrv/AppTest.java

code .

(A part of pom.xml)

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.8</maven.compiler.source>
    <maven.compiler.target>1.8</maven.compiler.target>
  </properties>

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>com.microsoft.sqlserver</groupId>
      <artifactId>mssql-jdbc</artifactId>
      <version>11.2.1.jre8</version>
    </dependency>    
  </dependencies>

package org.example.sqlsrv;

import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class sqlsrvlocaloutput {
    // select data from SQL Server
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:sqlserver://localhost:1433;databaseName=sampledb;user=sa;password=yourpassowrd;encrypt=false;";
        Connection conn = DriverManager.getConnection(url);
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select * from dbo.sampletable");
        List<String> list = new ArrayList<String>();
        while (rs.next()) {
            String ID = rs.getString("id");
            String NUM = rs.getString("num");
            String cdatetime = rs.getString("cdatetime");
            String note = rs.getString("note");
            String str = ID + "," + NUM + "," + cdatetime + "," + note;
            list.add(str);
        }
        rs.close();
        stmt.close();
        conn.close();

        // save local file
        String filename = "/home/xxx/sqlSrvOutput.csv";
        try {
            java.io.PrintWriter output = new java.io.PrintWriter(filename);
            for (int i = 0; i < list.size(); i++) {
                output.println(list.get(i));
            }
            output.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
    }
}

1,0,2022-10-20 16:13:13.477,init
2,1,2022-10-20 16:12:11.777,a
3,2,2022-10-20 16:12:11.777,ah
4,3,2022-10-20 16:12:11.777,lam
5,4,2022-10-20 16:12:11.777,nddv
6,5,2022-10-20 16:12:11.777,vdhpw
7,6,2022-10-20 16:12:11.777,fdfszy
8,7,2022-10-20 16:12:11.777,rgofcgt
9,8,2022-10-20 16:12:11.777,hezwdqae
10,9,2022-10-20 16:12:11.777,bywqcunva