Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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
Updated on: 30-Jul-2019
5K+ Views
Advertisements