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.
Δ
