In this article, we will use JDBC API to fetch single record from newly created table in MySQL database from Java application i.e.; using PreparedStatement 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 a single record 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. Getting single record using JDBC PreparedStatement Interface
- As we are ready with required things to connect MySQL database from Java application
- We can use one of the methods from PreparedStatement Interface to execute “select a single record” SQL query
- execute();
- executeUpdate();
- executeQuery();
- Let us code a simple example using PreparedStatement Interface
GetSingleRecordUsingJDBCPreparedStatement.java
package in.bench.resources.mysql.db.example; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class GetSingleRecordUsingJDBCPreparedStatement { public static void main(String[] args) { // variables Connection connection = null; PreparedStatement preparedStatement = 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"); // create SQL query to fetch particular record String sqlSelectQuery = "SELECT * FROM PLAYER WHERE PLAYER_ID = ?"; // Step 2.B: Creating JDBC Statement preparedStatement = connection.prepareStatement(sqlSelectQuery); // set values for PreparedStatement for respective ? preparedStatement.setInt(1, 7); // PLAYER_ID // Step 2.C: Executing SQL & retrieve data into ResultSet resultSet = preparedStatement.executeQuery(); 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 resultSet.close(); preparedStatement.close(); // and then finally close connection connection.close(); } } catch (SQLException sqlex) { sqlex.printStackTrace(); } } } }
Output:
ID Name Age Matches == ================ === ======= 7 Mahela Jayawardene 37 145
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 !!