How to retrieve data from table by using JDBC with example
To retrieve data from mysql data table by using jdbc, we need to confirm weather which table data we need to retrieve. To confirm open your mysql and view all database names by using the 'show databases' commands in mysql. Then select which is your database name from showing list of database names, by using the 'use <database name>' command in mysql.
Then we need to see all your existing tables by executing the 'show tables' command. This command gives you the list of existing tables in your selected database. You need to use existing table only you can follow with that. But, here we will see with entirely new which is not existing in mysql database. Now, We will create simple mysql table with minimal columns.
| create table emp(eno integer(10), ename varchar(30), sal float); |
The above command creates a new table, we will move further to insert records into emp table by executing the insert which we mentioned in the below.
| insert into emp values(1,'javatbrains',50000.0); |
If you want to insert multiple records into the same table, you need to execute the same query by entering the different value to insert into the emp table. Now, we will check weather the records are inserted into the emp table or not by executing the select query which will mentioned in the below.
| select * from emp; |
The above query gives you the list of existing records in the emp table. In our emp table we have inserted two records, so output will be like,
+------+---------------+--------+
| eno | ename | sal |
+------+---------------+--------+
| 1 | javatbrains | 50000 |
| 2 | nallamachu | 50000 |
+------+---------------+--------+
2 rows in set (0.00 sec)
Now our aim is to get the same output by retrieving data from database by using jdbc program. There are several statements are exists in jdbc with minimal differences for doing any operations on database tables.
In this example, we will use normal Statement interface in jdbc to retrieve data from mysql database. The below is the jdbc program to retrieve records from database table.
package com.javatbrains.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class RetriveRecords {
static Connection con = null;
static Statement st = null;
static ResultSet rs = null;
public static void main(String[] args) {
try {
// Register Driver
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
// Establish Connection
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/test?user=root&password=root");
// Create a statement
st = con.createStatement();
// Execute the Statement
rs = st.executeQuery("select * from emp");
// Retrieve from ResultSet and display column data
while (rs.next()) {
System.out.println(rs.getInt(1) + " " + rs.getString(2) + " "+ rs.getFloat(3));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
con.close();
st.close();
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
OutPut:
1 javatbrains 50000.0
2 nallamachu 50000.0
|
In the above JDBC program differently I have written compare to previous programs. In previous programs we are using Class.forName() method for loading the driver. But, here we will see the way of registering the Driver class by using the DriverManager class. When we will register a Driver there no need to load the Driver again. We will follow any of one way.
Even though while while creating the Connection here different approach I am following in this example. In previous example we use to see the syntax of creating the connection is like,
| GetConnection("jdbc:mysql://localhost:3306","root","root"); |
But, in the current example is following the different approach. But, the both use is same only for making connection with database.
getConnection("jdbc:mysql://localhost:3306/test?user=root&password=root")
|
Then remaining steps will be the same as the common JDBC steps as we are following in all the examples like, create Statement and execute query and fetch records by using ResultSet.
| Related Posts: |
Comments
Post a Comment