Here is a program to to check:
1) the connection with sql server database
2) print the database drivername, driver version, databaseproductname and databaseproduct version
3) get data from sql server
4) print the database result into excel file
First add the following dependencies into maven project as we are using the testNG framework and maven project or if you are not using maven project then add respective JAR files into your project.
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.testng</groupId>
<artifactId>testng</artifactId>
<version>7.4.0</version>
<scope>test</scope>
</dependency>
below is the complete code:
package Mypackage;
import java.io.File;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;
import java.util.Date;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.testng.annotations.AfterClass;
import org.testng.annotations.BeforeClass;
import org.testng.annotations.Test;
public class GetSQLServerDBdataandWritetoExcel_testCase {
Connection conn = null;
String dbURL = "jdbc:sqlserver:metion ur database ip here";
String user = "username";
String pass = "password***";
String databaseName=";databaseName=TestDB";
//String excelFilePath = "Reviews-export.xlsx";
Connection dbConn = null;
Statement stmt = null;
ResultSet rs = null;
public Date yesterday() {
final Calendar cal = Calendar.getInstance();
cal.add(Calendar.DATE, -1);
return cal.getTime();
}
@Test (priority = 1)
public void GetDataFromSikkaDBForRefreshDetails()
{
try {
conn = DriverManager.getConnection(dbURL, user, pass);
if (conn != null) {
DatabaseMetaData dm = (DatabaseMetaData) conn.getMetaData();
System.out.println("Connected");
System.out.println("Driver name: " + dm.getDriverName());
System.out.println("Driver version: " + dm.getDriverVersion());
System.out.println("Product name: " + dm.getDatabaseProductName());
System.out.println("Product version: " + dm.getDatabaseProductVersion());
}
else if (conn == null)
{
System.out.println("NotConnected");
}
} catch (SQLException ex) {
ex.printStackTrace();
} finally {
try {
if (conn != null && !conn.isClosed()) {
conn.close();
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}
try {
// Load jdbc driver class.
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
// Get connection.
dbConn = DriverManager.getConnection(dbURL, user, pass);
// Execute sql and return data result.
String SQL = "write your select query here to fetch data from the database through java program";
stmt = dbConn.createStatement();
rs = stmt.executeQuery(SQL);
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet spreadsheet1 = workbook.createSheet("excel file sheet name");
XSSFRow row = spreadsheet1.createRow(0);
XSSFCell cell;
cell = row.createCell(0);
cell.setCellValue("mscid");
int i = 1;
while (rs.next()) {
row = spreadsheet1.createRow(i);
cell = row.createCell(0);
cell.setCellValue(rs.getString("database cloumn name to mention to write data to excel"));
i++;
}
File f=new File("mention file path to write database data to the excel file");
if (f.createNewFile()) {
System.out.println("File created: " + f.getName());
}
FileOutputStream fos=new FileOutputStream(f);
workbook.write(fos);
// Step 7: Close the connection
fos.close();
// Display message for successful compilation of
// program
System.out.println(
"exceldatabase.xlsx written successfully");
// Loop the data result and display the data.
while (rs.next()) {
System.out.println(rs.getString(1));
}
}catch (Exception ex) {
ex.printStackTrace();
}finally {
if (rs != null)
{
try {
rs.close();
}catch(Exception ex) {
ex.printStackTrace();
}
}
if (stmt != null)
{
try {
stmt.close();
}catch(Exception ex)
{
ex.printStackTrace();
}
}
if (dbConn != null)
{
try {
dbConn.close();
} catch(Exception ex) {
ex.printStackTrace();
}
}
}
}
@AfterClass
public void tearDown()
{
System.out.println("Program Existed successfully");
}
}
Comments
Post a Comment