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.

NO COMMENTS

Leave a Reply

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