Below is the code for JDBC MySQl connection and reusable method which return 2 D array.
GitHub location:
Code:
DB Connection Singleton Pattern:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
package bddcucumber.managers.dbmanager; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; /* * Author: Akash * Date: 23Jun2019 */ public class DBConnectionManager { private static DBConnectionManager instance; private Connection conn; private DBConnectionManager(String url) { try { Class.forName("com.mysql.cj.jdbc.Driver"); this.conn = DriverManager.getConnection(url); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } public Connection getConnection() { return this.conn; } public static DBConnectionManager getInstance(String url) throws SQLException { if (instance==null) { instance= new DBConnectionManager(url); }else if (instance.getConnection().isClosed()) { instance= new DBConnectionManager(url); } return instance; } } |
DB Manager
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
package bddcucumber.managers.dbmanager; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import bddcucumber.managers.dbmanager.DBConnectionManager; /* * Author: Akash * Date: 23Jun2019 * Using free MY SQl database for sampling details for the same is as below */ /* * Database: db_autofrat Username: akashdktyagi Email: akashdktyagi@gmail.com The host name to access the server is db4free.net and the port is 3306. You can use phpMyAdmin on our website to log in to the server. https://www.db4free.net/ */ public class DBManager { //for testing only Should be removed public static void main(String[] args) throws SQLException { String q = "Select * from Products"; String [][] result = FetchDataFromDB(q); System.out.print(result); } public static String[][] FetchDataFromDB(String query) throws SQLException { //String url = "jdbc:ucanaccess://E:\\_AkashStuff\\Automation\\EclipseWorkspace\\AutomationPoCCucumber\\src\\test\\resources\\dbtable\\mySQL.accdb"; String url = "jdbc:mysql://db4free.net:3306/db_autofrat?user=akashdktyagi&password=akashdktyagi"; DBConnectionManager DBInstance = DBConnectionManager.getInstance(url); Connection conn = DBInstance.getConnection(); Statement stat = conn.createStatement(); ResultSet rs = stat.executeQuery(query); //To get total number of Column returned int clmCount = rs.getMetaData().getColumnCount(); //To get total number of rows returned rs.last();// this will move the rs to last row int rowcount = rs.getRow(); // this will give the index of last row rs.beforeFirst(); // this will bring it back to first record //Create a Object String[][] result = new String[rowcount][clmCount]; int i=0; while(rs.next()) { for(int j=0;j<clmCount;j++) { result[i][j]=rs.getString(j+1); } i=i+1; } return result; } } |
Dependencies for MYSQL JDBC impl:
|
1 2 3 4 5 6 7 |
<!-- FOR DB Connections to My SQL --> <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.13</version> </dependency> |
Video Explanation: