Java supports many databases and for each database, we need to have their respective jar files to be placed in the build path to proceed for JDBC connectivity. For different databases, different jar files are imported to make a connection given below or their built path is supposed to be added for specific databases.
- Types of Database
Illustration: SQL and Oracle databases are mostly used for illustration. Here SQL database is taken into consideration. Here Table_Name is Table name. Here it will take all columns and count the rows.
Input: Existing data in the table is shown in the below image
- SQL server used: sqljdbc4.jar
- SQL table used
CREATE TABLE `studentsdetails` ( `id` int(6) unsigned NOT NULL, `Name` varchar(50) NOT NULL, `caste` varchar(10) NOT NULL, `NeetMarks` int(11) NOT NULL, `gender` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Approaches:
A less efficient way of creating a query
select count(*) from Table_Name;
A more efficient way of creating a query
select count(1) from Table_Name;
This query will take the first column and count the rows. As mostly, the Primary key is the first column, it is ideal enough as the Primary key is always unique and not null.
| Example 1 | Example 2 |
|---|---|
| It will give only one row as output containing a number of rows. Hence, ‘resultset’ will be kept as next itself. | select * will bring the whole resultset and the cursor is forced to move last and finally ‘resultset.getRow()’ method will give the number of rows. |
| It is more efficient | It is comparatively less efficient |
Example 1: To get the number of rows in a table in JDBC by selecting count(1) from ‘studentsdetails’ will provide the result as 5.
Java
/* Java Program to use different row methodsto get no of rows in a table in JDBC */// Step 1: Importing database librariesimport java.sql.*;// Only main class- GFG is shown// connection class object is usedpublic class GFG { // Main driver method public static void main(String[] args) { // Initially connection is assigned Null valued Connection con = null; ResultSet res = null; // Try block to check exceptions try { /* Step 2: Load and register drivers or relevant jars in build path of project */ // Here- 'mysql-connector-java-8.0.22' // is used using Class.forNmae() method Class.forName("com.mysql.cj.jdbc.Driver"); /* Step 3: Establish a connection using DriverManager method */ con = DriverManager.getConnection( "root", ""); // Try block to check exceptions try { Statement st = con.createStatement(); /* This query will take first column and count the rows. As mostly, Primary key is the first column, it is ideal enough as Primary key is always unique and not null */ /* Step 4: Create a statement */ /* Alias name is used as NumberOfRows for COUNT(1) Moving the cursor to the last row */ res = st.executeQuery( "SELECT COUNT(1) as NumberOfRows FROM " + "studentsdetails"); /* Step 5: Execute the query */ res.next(); /* Step 6: Process the results */ System.out.println( "MySQL Table - studentsdetails contains " + res.getInt("NumberOfRows") + " rows"); } // Catch block to handle exceptions catch (SQLException s) { // Message to be displayed if SQLException // occurs System.out.println( "SQL statement is not executed!"); } } catch (Exception e) { /* Displaying line where exception occurred using method returning line number in code */ e.printStackTrace(); } finally { // Step 7: Closing the connection res = null; con = null; } }} |
Output:
Example 2: To get the number of rows in a table in JDBC
Java
/* Step 1: Importing Database libraries */import java.sql.*;/* Only main class-GFG is shownConnection class of JDBC is not shown.Object of connection class is used */public class GFG { // Main driver method public static void main(String[] args) { /* Objects are assigned null before any execution */ // Connection class objects Connection con = null; ResultSet res = null; // Try block to check exceptions try { /* Step 2: Load and register drivers or relevant jars in build path of project */ // Driver used- 'mysql-connector-java-8.0.22' // Loading and register drivers // using Class.forname() method Class.forName("com.mysql.cj.jdbc.Driver"); /* Step 3: Create a connection */ // using DriverManager con = DriverManager.getConnection( "root", ""); // Display message when connection // is successfully established System.out.println( "Connection is established"); // Try block to check exceptions try { /* In order to avoid Result set type is TYPE_FORWARD_ONLY */ Statement st = con.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); /* Query takes first column and count the rows. As mostly, Primary key is the first column, it is ideal enough as Primary key is always unique & not null. */ /* Step 4: Creating the statement */ res = st.executeQuery("SELECT * FROM " + "studentsdetails"); /* Step 5: Execute the statements */ // Moving the cursor to the last row res.last(); /* Step 6: Process the results */ System.out.println( "MySQL Table - studentsdetails contains " + res.getRow() + " rows"); } // Catch block to handle exceptions catch (SQLException s) { // Exception handled if it is SQL based System.out.println( "SQL statement is not executed!" + s.getMessage()); } } catch (Exception e) { // Exception handled here if it is generic // program based e.printStackTrace(); } finally { // Step 7: Closing the connection res = null; con = null; } }} |
Output:
