Java – Creating sample data for SQL Server on Ubuntu No.89

In this blog, how to create sample records for SQL Server on Ubuntu using Java is shown.

Contents

▼1. Creating sample data using Java
▼2. Prerequisites
- 2-1. Checking Java – Generating the sample data on SQL Server No.87 to execute below.
▼3. Creating sample data for SQL Server by Java
▼4. Reference

▼1. Creating sample data using Java

In the past blog, these blogs are shared. they use .sql file to create data by sqlcmd.

In this blog, there is the simple way to use Java without sqlcmd to create sample data.

▼2. Prerequisites

2-1. Checking Java – Generating the sample data on SQL Server No.87 to execute below.

▼3. Creating sample data for SQL Server by Java

3-1. Create an Apache Maven project

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

3-2. Remove the existed both App.java and AppTest.java

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

3-3. Start Visual Studio Code

cd ./sqlSrvOutput2
code .

3-4. Update pom.xml as below to run query on SQL Server and save it (Ctrl+S)

  <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>

3-5. Creating Java code sqlsample2.java

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 sqlsample2 {

        // create an insert query and save it to a string
        public static String insertQuery() {
            String query = "INSERT INTO sampledb.dbo.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 = 1; 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) throws SQLException {

        String url = "jdbc:sqlserver://localhost:1433;databaseName=master;user=sa;password=yourpassword;encrypt=false";
        Connection conn = DriverManager.getConnection(url);
        
        // Create databas
        String createdb = "IF DB_ID (N'sampledb') IS NULL CREATE DATABASE sampledb";
        Statement cd = conn.createStatement();
        cd.execute(createdb);
        System.out.println("Database was created.");
        cd.close();

        // Create table
        String createtable = "IF NOT EXISTS (select * from sampledb.dbo.sysobjects where name like 'sampletable') CREATE TABLE sampledb.dbo.sampletable (id int, num int, cdatetime datetime, note varchar(50))";
        Statement ct = conn.createStatement();
        ct.execute(createtable);
        System.out.println("Table was created.");
        ct.close();

        // Insert data
        String insertdata = insertQuery();
        Statement id = conn.createStatement();
        id.execute(insertdata);
        System.out.println("Data was inserted.");
        id.close();

        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select * from sampledb.dbo.sampletable");
        List<String> list = new ArrayList<String>();
        while (rs.next()) {
            String NUM = rs.getString("num");
            String cdatetime = rs.getString("cdatetime");
            String note = rs.getString("note");
            String str = NUM + "," + cdatetime + "," + note;
            list.add(str);
        }
        rs.close();
        stmt.close();
        conn.close();

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

3-6. Results of this code

Database was created.
Table was created.
Data was inserted.

Output file “sqlSrvOutput2.csv” contains below.

0,2022-12-01 18:12:56.953,EmlFGESx1
1,2022-12-01 18:12:56.953,GzHCThChH
2,2022-12-01 18:12:56.953,GKcBm7tdj
3,2022-12-01 18:12:56.953,UN3SXZB50
4,2022-12-01 18:12:56.953,jq0sqmdB7
5,2022-12-01 18:12:56.953,gdqQX5zq9
6,2022-12-01 18:12:56.953,JfkqIQjjM
7,2022-12-01 18:12:56.953,59js3TsaP
8,2022-12-01 18:12:56.953,TQsBNmsCe
9,2022-12-01 18:12:56.953,epQjRYxuV

▼4. Reference

That’s all. Have a nice day ahead !!!