How to sql insert items from a list or collection in to table using JDBC?


To insert the contents of a database to a collection,

Connect to the database and retrieve the contents of the table into a ResultSet object using the SELECT Query.

DriverManager.registerDriver(new com.mysql.jdbc.Driver());
String mysqlUrl = "jdbc:mysql://localhost/mydatabase";
Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select * from MyPlayers");

Create a Java class to hold the contents of each record, with a variable and, setter and getter methods for each column (with suitable datatype).

For example, if the table sample in the database has two fields with the details −

  • Column name: ID, datatype: INT(11)
  • Column name: Name, datatype VARCHAR(255)

Then, the variable declaration and the setter and getter methods in the class would be like −

import java.sql.Date;
public class Sample {
   private int ID;
   private String Name;
   public int getID() {
      return ID;
   }
   public void setID(int id) {
      ID = id;
   }
   public int getName() {
      return Name;
   }
   public void setName(int name) {
      Name = name;
   }
}

For each record, create and object of the above class (Sample), retrieve the values of each column from the ResultSet object and, set them to this object using its setter methods as −

Player player = new Player();
player.setID(rs.getInt("ID"));
player.setName(rs.getString("Name"));

Add the object to the required collection.

ArrayList<Player> arrayList = new ArrayList<>(); arrayList.add(player);

Let us create a table with name MyPlayers in MySQL database using CREATE statement as shown below −

CREATE TABLE MyPlayers(
   ID INT,
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Date_Of_Birth date,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255),
   PRIMARY KEY (ID)
);

Now, we will insert 7 records in MyPlayers table using INSERT statements −

insert into MyPlayers values(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India');
insert into MyPlayers values(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica');
insert into MyPlayers values(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka');
insert into MyPlayers values(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India');
insert into MyPlayers values(5, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India');
insert into MyPlayers values(6, 'Ravindra', 'Jadeja', DATE('1988-12-06'), 'Nagpur', 'India');
insert into MyPlayers values(7, 'James', 'Anderson', DATE('1982-06-30'), 'Burnley', 'England');

Java class used hold the data of each of this table −

import java.sql.Date;
public class Player {
   private int ID;
   private String First_Name;
   private String Last_Name;
   private Date Date_Of_Birth;
   private String Place_Of_Birth;
   private String Country;
   public int getID() {
      return ID;
   }
   public void setID(int id) {
      ID = id;
   }
   public String getFirst_Name() {
      return First_Name;
   }
   public void setFirst_Name(String first_Name) {
      First_Name = first_Name;
   }
   public String getLast_Name() {
      return Last_Name;
   }
   public void setLast_Name(String last_Name) {
      Last_Name = last_Name;
   }
   public Date getDate_Of_Birth() {
      return Date_Of_Birth;
   }
   public void setDate_Of_Birth(Date date_Of_Birth) {
      Date_Of_Birth = date_Of_Birth;
   }
   public String getPlace_Of_Birth() {
      return Place_Of_Birth;
   }
   public void setPlace_Of_Birth(String place_Of_Birth) {
      Place_Of_Birth = place_Of_Birth;
   }
   public String getCountry() {
      return Country;
   }
   public void setCountry(String country) {
      Country = country;
   }
}

Following JDBC program that retrieves the contents of the MyPlayers table, adds them to an ArrayList.

Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
public class TableToArrayList {
   public static void main(String args[]) throws Exception {
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());
      //Getting the connection
      String mysqlUrl = "jdbc:mysql://localhost/mydatabase";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      //Creating a Statement object
      Statement stmt = con.createStatement();
      //Retrieving the data
      ResultSet rs = stmt.executeQuery("select * from MyPlayers");
      //Creating an ArrayList object
      ArrayList<Player> arrayList = new ArrayList<>();
      //Adding the Records of the table to the Array List
      while(rs.next()) {
         Player player = new Player();
         player.setID(rs.getInt("ID"));
         player.setFirst_Name(rs.getString("First_Name"));
         player.setLast_Name(rs.getString("Last_Name"));
         player.setDate_Of_Birth(rs.getDate("Date_Of_Birth"));
         player.setPlace_Of_Birth(rs.getString("Place_Of_Birth"));
         player.setCountry(rs.getString("Country"));
         arrayList.add(player);
      }
      for (Player obj : arrayList) {
         System.out.print("ID: "+obj.getID()+", ");
         System.out.print("Name: "+obj.getFirst_Name()+", ");
         System.out.print("Age: "+obj.getLast_Name()+", ");
         System.out.print("Salary: "+obj.getDate_Of_Birth()+", ");
         System.out.print("Country: "+obj.getPlace_Of_Birth()+", ");
         System.out.print("Address: "+obj.getCountry());
         System.out.println();
      }
   }
}

Output

Connection established......
Records added to the array list.....
Contents of the array list:
ID: 1, Name: Shikhar, Age: Dhawan, Salary: 1981-12-05, Country: Delhi, Address: India
ID: 2, Name: Jonathan, Age: Trott, Salary: 1981-04-22, Country: CapeTown, Address: SouthAfrica
ID: 3, Name: Kumara, Age: Sangakkara, Salary: 1977-10-27, Country: Matale, Address: Srilanka
ID: 4, Name: Virat, Age: Kohli, Salary: 1988-11-05, Country: Mumbai, Address: India
ID: 5, Name: Rohit, Age: Sharma, Salary: 1987-04-30, Country: Nagpur, Address: India
ID: 6, Name: Ravindra, Age: Jadeja, Salary: 1988-12-06, Country: Nagpur, Address: India
ID: 7, Name: James, Age: Anderson, Salary: 1982-06-30, Country: Burnely, Address: England
raja
Published on 16-May-2019 12:34:37
Advertisements