Home Code Connect Microsoft SQL Server with Property File using Java

Connect Microsoft SQL Server with Property File using Java

This code is to connect Micrososft SQL database with Callable statement using Java. Callable statement, parameter and SQL Driver URL are calling from properties file. Once it is connected to the database, it is populating all values in a particular column based on query. You can printout SQL database output to a file.

Connect MS SQL with property file and Callable Statement


import java.util.Properties;
import java.util.Random;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DBConnect {
private String dbValue;
public String getDbValue() {
return dbValue;
}
public void setDbValue(String dbValue) {
this.dbValue = dbValue;
}
public static String callStoredProc() {
InputStream input = null;
ResultSet rs = null;
PreparedStatement ps = null;
Connection con = null;
String result = null;
try {
String propPath = “.\src\props\db.properties”; // property file
input = new FileInputStream(propPath);
Properties prop = new Properties();
prop.load(input); // load property file

// get the property value
String dbDriver = (prop.getProperty(“dbDriver”)); // driver from db.properties
String dbURL = (prop.getProperty(“dbURL”));

Class.forName(dbDriver);
con = DriverManager.getConnection(dbURL);

// Stored procedure call
String stPro = (prop.getProperty(“SPSql1”));
ps = con.prepareStatement(stPro);
ps.setEscapeProcessing(true);
ps.setQueryTimeout(90); // timeout value
ps.setString(1, prop.getProperty(“VALUE1”));
ps.setString(2, prop.getProperty(“VALUE2”));
ps.setString(3, prop.getProperty(“VALUE3”));
rs = ps.executeQuery();
rs.next();

// Pick random value from DB range of 1-100
int random = (int)(Math.random() * 100 + 1);
int i=0;
while (i < random) {
rs.next();
i++;
}
result = (rs.getString(“row_name”)); //prop.getProperty(“dbRow”)
prop.getProperty(“dbRow”);
prop.getProperty(“value2”);

} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null)
input.close();
if (rs != null)
rs.close();
if (ps != null)
ps.close();
if (con != null)
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return result;
}
}
//end of class

//property file
/*db.properties */
dbDriver=com.microsoft.sqlserver.jdbc.SQLServerDriver
dbURL=jdbc:sqlserver:SERVER_URL;database=DB_NAME;user=USERNAME;password=PASSWORD
VALUE1=aaa
VALUE2=bbb
VALUE3=ccc
SPSql1={call storeprecdurecall (?,?,?)}
dbRow=row_name
DB_USERNAME=uname
DB_PASSWORD=password

This particular example, the data is iterating from the table and picking a random value from the result. In this particular example we set to a value 100 and the output will pick a random value between 1-100 by from the table result.

Disclaimer: MashTips is supported by its audience. When you purchase through links on our site, we may earn an affiliate commission at no extra cost to you.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Free Newsletter

Read More...

How to turn on private browsing in Mozilla Firefox?

Private browsing is a good security measure you can take to protect your personal data, online sensitive information from hackers and intruders. If you...

19 Hidden WhatsApp Tricks for iPhone & Android Users

Are you using WhatsApp messenger on your iPhone or Android? There are a bunch of shortcuts and hidden WhatsApp tricks that can make your...

How to Watch Apple TV+ on Roku & Fire Stick

Ever since Apple had launched its own streaming service Apple TV+, it has been gaining popularity among non-Apple users as well. Because Apple has...

How to Save a Clean PDF Version of a Webpage By Removing Ads

Internet bring you all the information you want into your screen with a simple mouse click. You can get plenty of information on any subject...

Top 10 Free Operating Systems For Laptop or Netbook for 2019.

Tired of Windows, Mac OS X, and other Operating Systems paid up gradation and support? Do you feel like trying out something new? Or...

How to Mirror and Control Android from Ubuntu

Connectivity between an Android phone and a Linux OS such as Ubuntu is definitely not a problem. With utilities like KDE Connect, you get...