In this article, we will use JDBC API to fetch records using where clause condition from newly created table in MySQL database from Java application i.e.; using Statement Interface
1. Pre-requisite:
- Java JDK 1.8.0_77
- MySQL database 5.5.16
- Eclipse Luna IDE 4.4.0
- mysql-connector-java-5.1.38.jar file
2. Database parameters:
Let us move on and code an example to connect MySQL database from Java application to fetch records using where clause condition using JDBC API. But before that, we will list down required things to connect database
- database server IP or address (localhost)
- sever port (3306)
- database name (PLAYER_INFO)
- username (root)
- password (root@123)
Note: All bold are database values to connect MySQL database
3. Where clause condition using JDBC Statement Interface
- As we are ready with required things to connect MySQL database from Java application
- We can use one of the method from Statement Interface to execute “where clause condition to fetch selected records” SQL query
- execute(String sqlQuery);
- executeUpdate(String sqlQuery);
- executeQuery(String sqlQuery);
- Let us code a simple example using Statement Interface
WhereClauseConditionUsingJDBCStatement.java
package in.bench.resources.mysql.db.clause; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class WhereClauseConditionUsingJDBCStatement { public static void main(String[] args) { // variables Connection connection = null; Statement statement = null; ResultSet resultSet = null; // Step 1: Loading or registering MySQL JDBC driver class try { Class.forName("com.mysql.jdbc.Driver"); } catch(ClassNotFoundException cnfex) { System.out.println("Problem in loading MySQL JDBC driver"); cnfex.printStackTrace(); } // Step 2: Opening database connection try { // Step 2.A: Create and get connection using DriverManager connection = DriverManager.getConnection( "jdbc:mysql://localhost:3306/PLAYER_INFO", "root", "root@123"); // Step 2.B: Creating JDBC Statement statement = connection.createStatement(); // to get all players with more than 150 matches String sqlSelectQuery ="SELECT * FROM PLAYER" + " WHERE MATCHES > 150"; // Step 2.C: Executing SQL & retrieve data into ResultSet resultSet = statement.executeQuery(sqlSelectQuery); System.out.println("ID\tName\t\t\tAge\tMatches"); System.out.println("==\t================\t===\t======="); // processing returned data and printing into console while(resultSet.next()) { System.out.println(resultSet.getInt(1) + "\t" + resultSet.getString(2) + "\t" + resultSet.getInt(3) + "\t" + resultSet.getInt(4)); } } catch(SQLException sqlex){ sqlex.printStackTrace(); } finally { // Step 3: Closing database connection try { if(null != connection) { // cleanup resources, once after processing statement.close(); // and then finally close connection connection.close(); } } catch (SQLException sqlex) { sqlex.printStackTrace(); } } } }
Output:
ID Name Age Matches == ================ === ======= 1 Sachin Tendulkar 43 200 8 Jacques Kallis 40 166 11 Steve Waugh 50 168
4. Download:
Read Also:
- JDBC Tutorial index
- Introduction to JDBC
- Different types of JDBC drivers
- JDBC Driver list for all leading databases
- Basic components of JDBC
- JDBC connection steps
- MySQL database connection steps
- Oracle database connection steps
References:
- https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-usagenotes-connect-drivermanager.html
- http://www.benchresources.net/spring-jdbc-introduction-and-jdbc-example-without-spring/
- https://docs.oracle.com/javase/tutorial/jdbc/basics/gettingstarted.html
- https://docs.oracle.com/cd/E11882_01/java.112/e16548/overvw.htm#JJDBC28025
- http://docs.oracle.com/javase/tutorial/jdbc/basics/index.html
- https://en.wikipedia.org/wiki/JDBC_driver
- http://www.devx.com/tips/Tip/28818
- https://docs.oracle.com/javase/7/docs/api/java/lang/Class.html#forName(java.lang.String)
Happy Coding !!
Happy Learning !!