Java.sql and JDBC
Java Database Connectivity (JDBC) is a standard Java API for interacting with relational databases. The java.sql package, part of the Java Standard Edition (SE), provides classes and interfaces to facilitate database operations.
Example 1: The table contains an `id` column, which is of auto-increment type
Let us assume that we have a database named “GFG” in which we have a table named “users”. The schema for the table “users” is as follows:
CREATE TABLE users
(
id int AUTO_INCREMENT PRIMARY KEY,
name varchar(255) not null,
email varchar(255) not null,
age int not null
);
Now, we will insert two records into this table. The Java code below establishes a JDBC connection using connection parameters like username, password, database URL, and database name. The code contains an `InsertRecord()` method, which takes the JDBC connection object and the values for the insert query. The `InsertRecord()` method creates a PreparedStatement object and executes the query. To get the id of the last inserted record, it extracts the generatedKeys() from the executed query and returns the id.
Code:
Java
// importing required packages import java.io.*; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class GFG { // database connection paramaters private static final String DATABASE_URL = "localhost:3306" ; private static final String DATABASE = "gfg" ; private static final String USERNAME = "root" ; private static final String PASSWORD = "123" ; // a function to insert record into MySQL database // it returns the last inserted id for the record public static int InsertRecord(Connection conn, String name, String email, int age) throws SQLException { int id = 0 ; String query = "INSERT INTO users(name, email, age) values(?,?,?)" ; // create a PreparedStatement object PreparedStatement ps = conn.prepareStatement( query, Statement.RETURN_GENERATED_KEYS); // set the query paramaters/values ps.setString( 1 , name); ps.setString( 2 , email); ps.setString( 3 , Integer.toString(age)); // execute the insert query ps.executeUpdate(); // get the auto-generated keys ResultSet rs = ps.getGeneratedKeys(); if (rs.next()) { id = rs.getInt( 1 ); } return id; } public static void main(String[] args) { try { // create a connection object Connection conn = DriverManager.getConnection( "jdbc:mysql://" + DATABASE_URL + "/" + DATABASE, USERNAME, PASSWORD); // insert 1st record into table int lastInsertedId1 = InsertRecord( conn, "Girish" , "cs22mtech11005@iith.ac.in" , 24 ); // print the id of this 1st record inserted into // table System.out.println( "Last Inserted Record Id was : " + lastInsertedId1); // insert 2nd record into table int lastInsertedId2 = InsertRecord( conn, "Medha" , "cs22mtech11003@iith.ac.in" , 23 ); // print the id of this 2nd record inserted into // table System.out.println( "Last Inserted Record Id was : " + lastInsertedId2); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } |
Last Inserted Record Id was : 1
Last Inserted Record Id was : 2
Output:
Data inserted using JDBC:
Example 2: The table DOES NOT contain an `id` column, which is of auto-increment type
CREATE TABLE users
(
name varchar(255) not null,
email varchar(255) not null PRIMARY KEY,
age int not null
);
Again, we use the same code mentioned in the example above, but this time, we won’t get the last inserted id for any record inserted into the `users` table since, now there is no AUTOINCREMENT type column in `users` table.
Last Inserted Record Id was : 0
Last Inserted Record Id was : 0
Output:
Data inserted using JDBC:
How to Get Last Inserted ID from MySQL?
In the world of Java programming and MySQL database management systems, you might be querying your MySQL database using a JDBC connection. In this article, we will look at fetching the latest ID after an insertion. Using Java’s java.sql package, we will look at some examples of how to access the last inserted ID from MySQL.
In these examples, we’ll learn how to smoothly retrieve the last inserted ID in Java applications, improving data management. Using Java’s java.sql package, these techniques empower developers to optimize interactions with MySQL databases efficiently.
Contact Us