How to insert a record into a table in a database using JDBC API?

JDBCJava 8Object Oriented ProgrammingProgramming

A. You can insert records in to a table using the INSERT query.

Syntax

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);
Or,
INSERT INTO TABLE_NAME VALUES (value1, value2, value3,...valueN);

To insert a record into a table in a database using JDBC API you need to:

  • Register the driver: Register the driver class using the registerDriver() method of the DriverManager class. Pass the driver class name to it, as parameter.

  • Establish a connection: Connect ot the database using the getConnection() method of the DriverManager class. Passing URL (String), username (String), password (String) as parameters to it.

  • Create Statement: Create a Statement object using the createStatement() method of the Connection interface.

  • Execute the Query: Execute the query using the executeUpdate() method of the Statement interface.

Example

Assume we have a table named customers in the database with description as shown below:

+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| ID      | int(11)       | NO   | PRI | NULL    |       |
| NAME    | varchar(20)   | NO   |     | NULL    |       |
| AGE     | int(11)       | NO   |     | NULL    |       |
| SALARY  | decimal(18,2) | YES  |     | NULL    |       |
| ADDRESS | char(25)      | YES  |     | NULL    |       |
+---------+---------------+------+-----+---------+-------+

Following JDBC program establishes connection with MySQL and inserts 12 records in the customers table:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class InsertRecordsExample {
   public static void main(String args[]) throws SQLException {
      //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 the Statement
      Statement stmt = con.createStatement();
      //Query to insert records
      String query = "INSERT INTO CUSTOMERS(" + "ID, Name, AGE, SALARY, ADDRESS) VALUES "
         + "(1, 'Amit', 25, 3000, 'Hyderabad'), "
         + "(2, 'Kalyan', 27, 4000, 'Vishakhapatnam'), "
         + "(3, 'Renuka', 30, 5000, 'Delhi'), "
         + "(4, 'Archana', 24, 1500, 'Mumbai'),"
         + "(5, 'Koushik', 30, 9000, 'Kota'),"
         + "(6, 'Hardik', 45, 6400, 'Bhopal'),"
         + "(7, 'Trupthi', 33, 4360, 'Ahmedabad'),"
         + "(8, 'Mithili', 26, 4100, 'Vijayawada'),"
         + "(9, 'Maneesh', 39, 4000, 'Hyderabad'),"
         + "(10, 'Rajaneesh', 30, 6400, 'Delhi'),"
         + "(11, 'Komal', 29, 8000, 'Ahmedabad'),"
         + "(12, 'Manyata', 25, 5000, 'Vijayawada')";
      int i = stmt.executeUpdate(query);
      System.out.println("Rows inserted: "+i);
   }
}

Output

Connection established......
Rows inserted: 12

If you verify the contents of the customers table using select statement, you can find the inserted records in it as:

mysql> select * from customers;
+----+-----------+------+---------+----------------+
| ID | NAME      | AGE  | SALARY | ADDRESS         |
+----+-----------+------+---------+----------------+
| 1  | Amit      | 25   | 3000.00 | Hyderabad      |
| 2  | Kalyan    | 27   | 4000.00 | Vishakhapatnam |
| 3  | Renuka    | 30   | 5000.00 | Delhi          |
| 4  | Archana   | 24   | 1500.00 | Mumbai         |
| 5  | Koushik   | 30   | 9000.00 | Kota           |
| 6  | Hardik    | 45   | 6400.00 | Bhopal         |
| 7  | Trupthi   | 33   | 4360.00 | Ahmedabad      |
| 8  | Mithili   | 26   | 4100.00 | Vijayawada     |
| 9  | Maneesh   | 39   | 4000.00 | Hyderabad      |
| 10 | Rajaneesh | 30   | 6400.00 | Delhi          |
| 11 | Komal     | 29   | 8000.00 | Ahmedabad      |
| 12 | Manyata   | 25   | 5000.00 | Vijayawada     |
+----+-----------+------+---------+----------------+
12 rows in set (0.06 sec)
raja
Published on 02-Apr-2019 07:19:56
Advertisements