Java – SQL Server サンプルデータの作成 VS Code No.87

// createdbtable.sql の中身
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'sampleDB')
Begin
	Create database sampleDB
End;
use sampleDB;
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'sampletable')
Begin
	drop table sampletable
END;
Create table sampletable(
	id int identity(1,1) not null,
	num int,
	cdatetime datetime,
	note nvarchar(max)
);

sampletable の Schema

Column_name Type    
----------- --------
id          int     
num         int     
cdatetime   datetime
note        nvarchar

package org.example.sqlsrv;

import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStreamReader;

public class sqlgensample {

    //create table
    public static String sqlgentable() {
        // read the file and put it into a string
        String filename = "/home/xxx/sqlsrvtest/sqlsample/createtable.sql";
        String line = null;
        String sql = "";
        
        try {
            FileReader fileReader = new FileReader(filename);
            BufferedReader bufferedReader = new BufferedReader(fileReader);
            while((line = bufferedReader.readLine()) != null) {
                sql += line;
            }
            bufferedReader.close();
        }
        catch(FileNotFoundException ex1) {
            System.out.println("Unable to open file '" + filename + "'");
        }
        catch(IOException ex2) {
            System.out.println("Error reading file '" + filename + "'");
        }

        return sql;
    }


    // create an insert query and save it to a string
    public static String insertQuery() {
        String query = "INSERT INTO sampletable (num, cdatetime, note) VALUES (0,CURRENT_TIMESTAMP,'init')";     
        
        for (int num=0; num<10; num++) {
            String AlphaNumericString ="ABCDEFGHIJKLMNOPQRSTUVWXYZ" + "0123456789" + "abcdefghijklmnopqrstuvxyz";
            StringBuilder sb = new StringBuilder(10);

            for (int i = 0; i < 10; i++) {
                int index = (int)(AlphaNumericString.length() * Math.random());
                sb.append(AlphaNumericString.charAt(index));
            }
            String randomString = sb.toString();
            query += ",(" + num + ",CURRENT_TIMESTAMP,'" + randomString + "')";
        }
        query += ";";

        return query;
    }

    public static void main(String[] args) {
        //System.out.println(insertQuery());
        //System.out.println(sqlgentable());

        try{
            String[] sqlcommand1= {"sqlcmd", "-S", "localhost", "-U", "sa", "-P", "Password", "-d", "sampledb", "-Q", sqlgentable()};
            Process p1 = Runtime.getRuntime().exec(sqlcommand1);
            p1.waitFor();
            BufferedReader reader1 = new BufferedReader(new InputStreamReader(p1.getInputStream()));
            String line1 = "";
            while ((line1 = reader1.readLine())!= null) {
                System.out.println(line1);
            }
        }catch(Exception e){
            System.out.println(e);
        }

        try{
            String[] sqlcommand2 = {"sqlcmd", "-S", "localhost", "-U", "sa", "-P", "Password", "-d", "sampledb", "-Q", insertQuery()};
            Process p2 = Runtime.getRuntime().exec(sqlcommand2);
            p2.waitFor();
            BufferedReader reader2 = new BufferedReader(new InputStreamReader(p2.getInputStream()));
            String line2 = "";
            while ((line2 = reader2.readLine())!= null) {
                System.out.println(line2);
            }
        }catch(Exception e){
            System.out.println(e);
        }
    }
}

Changed database context to 'sampleDB'.

(11 rows affected)

3> select * from sampletable;
4> go
id          num         cdatetime               note
----------- ----------- ----------------------- ---------------
          1           0 2022-11-29 12:51:13.930 init
          2           0 2022-11-29 12:51:13.930 oUu5REuOBD
          3           1 2022-11-29 12:51:13.930 JUrBJkXeLx
          4           2 2022-11-29 12:51:13.930 TT1JBlRQCU
          5           3 2022-11-29 12:51:13.930 moXq78miPl
          6           4 2022-11-29 12:51:13.930 gLOeDj0gjx
          7           5 2022-11-29 12:51:13.930 NSFOy1ZlAa
          8           6 2022-11-29 12:51:13.930 D9Kyo4XFSd
          9           7 2022-11-29 12:51:13.930 eWFF74pf6H
         10           8 2022-11-29 12:51:13.930 JH65hfBYBs
         11           9 2022-11-29 12:51:13.930 BMa6AZ8Zuc