Objective and Prerequisites

Objective: This segment introduces implementation of how to insert (records using CSV file) and retrieve the records from a table in MySQL using Java.

Prerequisites: MySQL setup is installed and running in the background, and the database DB and table holders are created in MySQL. If not, then refer to the Java Database Connectivity (JDBC) segment in sequence. Also, the project has been developed and executed in Eclipse IDE (Integrated Development Environment).

To run the following program, you need to download the following jar files:
1. Download JDBC driver
2. Download OpenCSV
3. Download Apache Commons Lang
Once downloaded, you can import the library by right-clicking on your project in Eclipse -> Properties -> Java Build Path -> Libraries -> Add External JARs...

Start MySQL
kodingwindow@kw:~$ sudo mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| db                 |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> USE DB;
Database changed

mysql> SHOW TABLES;
+--------------+
| Tables_in_db |
+--------------+
| holders      |
+--------------+
1 row in set (0.00 sec)

mysql> DESC holders;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| account_no | bigint      | NO   | PRI | NULL    |       |
| name       | varchar(30) | NO   |     | NULL    |       |
| bank       | varchar(10) | YES  |     | NULL    |       |
| amount     | bigint      | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.20 sec)
Java MySQL to insert and retrieve the records from a table
In the following program, we will be inserting records into a table using CSV (comma-separated values) file. Once all the records inserted, we will retrieve those records using MySQL SELECT statement and WHERE clause.
kw.csv
account_no,name,bank,amount
25622348989,James Moore,Barclays,5000
25622348990,Donald Taylor,Citi,7000
25622348991,Edward Parkar,ICICI,95000
25622348992,Ryan Bakshi,Citi,50000
25622348993,Marie Peters,DZBank,12250
KW.java
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import com.opencsv.CSVReader;
import com.opencsv.CSVReaderBuilder;

class KW
{
    public static void main(String[] args)
    {
        try
        {
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/DB","root", "kodingwindow");
            PreparedStatement statement = con.prepareStatement("INSERT INTO holders(account_no,name,bank,amount) VALUES(?,?,?,?)");
                    
            CSVReader reader = new CSVReaderBuilder(new FileReader("kw.csv")).withSkipLines(1).build();
            String[] column;
            while((column = reader.readNext()) != null) 
            {
                String account_no=column[0];
                String name=column[1];
                String bank=column[2];
                String amount=column[3];
                
                statement.setString(1,account_no);
                statement.setString(2,name);
                statement.setString(3,bank);
                statement.setString(4,amount);
                statement.addBatch();
            }
            statement.executeBatch();
            System.out.println("Records inserted successfully");
            
            System.out.println("List of all the records");
            ResultSet rs=statement.executeQuery("SELECT * FROM holders");  
            while(rs.next())
            {
                System.out.println(rs.getLong("account_no")+", "+rs.getString("name")+", "+rs.getString("bank")+", "+rs.getDouble("amount"));
            }
            
            System.out.println("\nList of the records after applying WHERE clause");
            rs=statement.executeQuery("SELECT * FROM holders WHERE amount >= 10000");  
            while(rs.next())
            {
                System.out.println(rs.getLong("account_no")+", "+rs.getString("name")+", "+rs.getString("bank")+", "+rs.getDouble("amount"));
            }
        }
        catch(Exception e)
        {
            System.out.println(e.getMessage());
        }
    }
}
Output
kodingwindow@kw:~$ javac KW.java
kodingwindow@kw:~$ java KW
Records inserted successfully
List of all the records
25622348989, James Moore, Barclays, 5000.0
25622348990, Donald Taylor, Citi, 7000.0
25622348991, Edward Parkar, ICICI, 95000.0
25622348992, Ryan Bakshi, Citi, 50000.0
25622348993, Marie Peters, DZBank, 12250.0

List of the records after applying WHERE clause
25622348991, Edward Parkar, ICICI, 95000.0
25622348992, Ryan Bakshi, Citi, 50000.0
25622348993, Marie Peters, DZBank, 12250.0
kodingwindow@kw:~$
MySQL Instance
mysql> SELECT * FROM holders;
+-------------+---------------+----------+--------+
| account_no  | name          | bank     | amount |
+-------------+---------------+----------+--------+
| 25622348989 | James Moore   | Barclays |   5000 |
| 25622348990 | Donald Taylor | Citi     |   7000 |
| 25622348991 | Edward Parkar | ICICI    |  95000 |
| 25622348992 | Ryan Bakshi   | Citi     |  50000 |
| 25622348993 | Marie Peters  | DZBank   |  12250 |
+-------------+---------------+----------+--------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM holders WHERE amount >= 10000;
+-------------+---------------+--------+--------+
| account_no  | name          | bank   | amount |
+-------------+---------------+--------+--------+
| 25622348991 | Edward Parkar | ICICI  |  95000 |
| 25622348992 | Ryan Bakshi   | Citi   |  50000 |
| 25622348993 | Marie Peters  | DZBank |  12250 |
+-------------+---------------+--------+--------+
3 rows in set (0.00 sec)
What Next?
Java MySQL to update the table records Java MySQL to perform JDBC CRUD operations Java MongoDB to create database and collection
Advertisement