In this blog, How to generate the sample data on SQL Server is shown. the key point is to use sqlcmd to run queries in Java.
▼1. Generating the sample data using Java
In the pas, how to generate the sample data using python was shown in this blog.
Python – Creating sample data in SQL Server on Ubuntu No.62
In the past case, the file which extension is .sql was created and then run this file by sqlcmd. this blog show the way to run query without such .sql file.
▼2. Prerequisites
2-1. Installing SQL Server on Ubuntu
Quickstart: Install SQL Server and create a database on Ubuntu
2-2. Installing Microsoft JDBC Driver 8.2 for SQL Server
Install Java 8 openjdk x64 of zulu
mkdir -p /usr/lib/jvm/
cd /usr/lib/jvm/
sudo wget https://cdn.azul.com/zulu/bin/zulu8.66.0.15-ca-jdk8.0.352-linux_x64.tar.gz
sudo tar -xzvf zulu8.66.0.15-ca-jdk8.0.352-linux_x64.tar.gz
sudo mv zulu8.66.0.15-ca-jdk8.0.352-linux_x64 java-8-openjdk-linux_x64Set environment variables for the installed JDK
Adding below at the end of ~/.bashrc (vi ~/.bashrc)
export JAVA_HOME=/usr/lib/jvm/java-8-openjdk-x64/
Checking if $JAVA_HOME is correctly updated.
echo $JAVA_HOME2-3. Install Visual Studio Code
https://code.visualstudio.com/docs/setup/linux
sudo snap install --classic code2-4. Install Maven
Apache Kafka Word Count – Java No.44 “2-3. Installing Apache Maven”
2-5. Create a query to create a database and a table
Create “createdbtable.sql” file to create a new database and a table.
// 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)
);This sql script can create a database “sampleDB” and then create a “sampletable”. the schema of this table is below.
Column_name Type ----------- -------- id int num int cdatetime datetime note nvarchar
▼3. Generate a sample data using java
3-1. Create an Apache Maven project
mvn archetype:generate -DinteractiveMode=false -DgroupId=org.example.sqlsrv -DartifactId=sqlSrvOutput -DarchetypeArtifactId=maven-archetype-quickstart -DarchetypeVersion=1.43-2. Remove the existed both App.java and AppTest.java
rm ./sqlSrvOutput/src/main/java/org/example/sqlsrv/App.java
rm ./sqlSrvOutput/src/test/java/org/example/sqlsrv/AppTest.java3-3. Start Visual Studio Code
cd ./sqlSrvOutput
code .3-4. Update pom.xml as below 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. Connect to MySQL and execute select query
Run the file “createdbtable.sql” that was created above 2-5 by sqlcmd to create a database and a table.
After that, 10 records as the sample data are inserted in this code. we can change the number of records. please change the path of your “createdbtable.sql” and username and password for the connection to SQL Server.
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);
}
}
}3-6. Results of this code
Changed database context to 'sampleDB'. (11 rows affected)
3-7, Check the generated data
Connect to SQL Server and execute a select query to check the generated data.
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▼4. Reference
- Quickstart: Install SQL Server and create a database on Ubuntu
- Python – Creating sample data in SQL Server on Ubuntu No.62
That’s all. Have a nice day ahead !!!