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.