How to get primary key value (auto-generated keys) from inserted queries using JDBC? Description: When we are inserting a record into the database table and the primary key is an auto-increment or auto-generated key, then the insert query will generate it dynamically. In the general case, the keys are returned as a List containing one Map for each row of keys. Most applications only use on key per row and process only one row at a time in an insert statement. In these cases, just call getKey to retrieve the key. The returned value is a Number here, which is the usual type for auto-generated keys.

1. Spring Mysql Return Generated Keys 2016
2. Spring Mysql Return Generated Keys Download
3. Spring Mysql Return Generated Keys For Windows

-->

The Microsoft JDBC Driver for SQL Server supports the optional JDBC 3.0 APIs to retrieve automatically generated row identifiers. The main value of this feature is to provide a way to make IDENTITY values available to an application that is updating a database table without a requiring a query and a second round-trip to the server.

Because SQL Server doesn't support pseudo columns for identifiers, updates that have to use the auto-generated key feature must operate against a table that contains an IDENTITY column. SQL Server allows only a single IDENTITY column per table. The result set that is returned by getGeneratedKeys method of the SQLServerStatement class will have only one column, with the returned column name of GENERATED_KEYS. If generated keys are requested on a table that has no IDENTITY column, the JDBC driver will return a null result set.

As an example, create the following table in the sample database:

In the following example, an open connection to the sample database is passed in to the function, an SQL statement is constructed that will add data to the table, and then the statement is run and the IDENTITY column value is displayed.

See also

In this article, You’ll learn how to map a composite primary key in Hibernate using JPA’s @Embeddable and @EmbeddedId annotations.

Let’s say that We have an application that manages Employees of various companies. Every employee has a unique employeeId within his company. But the same employeeId can be present in other companies as well, So we can not uniquely identity an employee just by his employeeId. Auto generated key in db2.

To identify an employee uniquely, we need to know his employeeId and companyId both. Check out the following Employees table that contains a composite primary key which includes both the employeeId and companyId columns -

Let’s create a project from scratch and learn how to map such composite primary key using JPA and Hibernate.

Creating the Project

You can generate the project quickly using Spring Boot CLI by typing the following command in the terminal - Download keylogger free for mac.

Alternatively, You can also use Spring Initializr web app to generate the project. Follow the instructions below to generate the app using Spring Initializr web app -

1. Open http://start.spring.io
2. Enter Artifact as “jpa-composite-primary-key-demo”
3. Click Options dropdown to see all the options related to project metadata.
4. Change Package Name to “com.example.jpa”
5. Select Web, JPA and Mysql dependencies.
6. Click Generate to generate and download the project.

Following is the directory structure of the complete application for your reference -

(Your bootstrapped project won’t have model and repository packages and all the other classes. We’ll create them as we proceed to next sections) Key generator online office 2010.

Configuring the Database and Hibernate Log Levels

Let’s add the MySQL database URL, username and password configurations in src/main/resources/application.properties file -

Apart from MySQL database configurations, I’ve also specified hibernate log levels and other properties.

The property spring.jpa.hibernate.ddl-auto = update keeps the Entity types in your application and the mapped database tables in sync. Whenever you update a domain entity, the corresponding mapped table in the database will also get updated when you restart the application next time.

This is great for development because you don’t need to manually create or update the tables. They will automatically be created/updated based on the Entity classes in your application.

Before proceeding to the next section, Please make sure that you create a MySQL database named jpa_composite_pk_demo and change spring.datasource.username and spring.datasource.password properties as per your MySQL installation.

Spring Mysql Return Generated Keys 2016

Defining the Domain model

A composite primary key is mapped using an Embeddable type in hibernate. We’ll first create an Embeddable type called EmployeeIdentity containing the employeeId and companyId fields, and then create the Employee entity which will embed the EmployeeIdentity type.

Create a new package named model inside com.example.jpa package and then add the following classes inside the model package -

1. EmployeeIdentity - Embeddable Type

2. Employee - Domain model

In the Employee class, We use @EmbeddedId annotation to embed the EmployeeIdentity type and mark it as a primary key.

Creating the Repository

Next, Let’s create the repository for accessing the Employee data from the database. First, Create a new package named repository inside com.example.jpa package, then add the following interface inside the repository package -

Code to test the Composite Primary Key Mapping

Finally, Let’s write some code to test the composite primary key mapping. Open the main class JpaCompositePrimaryKeyDemoApplication.java and replace it with the following code -

We first clean up the Employee table and then insert a new Employee record with an employeeId and a companyId to test the setup.

You can run the application by typing mvn spring-boot:run from the root directory of the project. The Employee record will be inserted in the database once the application is successfully started.

Querying using the Composite Primary Key

Let’s now see some query examples using the composite primary key -

1. Retrieving an Employee using the composite primary key - (employeeId and companyId)

2. Retrieving all employees of a particular company

Let’s say that you want to retrieve all the employees of a company by companyId. For doing this, just add the following method in the EmployeeRepository interface.

That’s all! You don’t need to implement anything. Spring Data JPA will dynamically generate a query using the method name. You can use the above method in the main class to retrieve all the employees of a company like this -

Conclusion

Spring Mysql Return Generated Keys Download

Congratulations guys! In this article, you learned how to implement a composite primary key in hibernate using @Embeddable and @EmbeddedId annotations.

You can find the entire source code for the sample project that we built in this article in my jpa-hibernate-tutorials github repository.

Spring Mysql Return Generated Keys For Windows

Thanks for reading. See you in the next post.