In this tutorial, we use the MySQL Connector/J driver. It is the official JDBC driver for MySQL. The examples were created and tested on Ubuntu Linux. You might also want to check Java tutorial, PostgreSQL Java tutorial, Apache Derby tutorial, MySQL tutorial, or Spring JdbcTemplate tutorial on ZetCode. Aug 23, 2013 Oracle Java JDBC: Get Primary Key of Inserted record by Viral Patel August 23, 2013 Here is a small write-up which should help those who still write plain Java JDBC code. Retrieves any auto-generated keys that are created as a result of running this SQLServerStatement object. Syntax public final java.sql.ResultSet getGeneratedKeys Return Value. A ResultSet object. This getGeneratedKeys method is specified by the getGeneratedKeys method in the java.sql.Statement interface.

1. Get Generated Keys From Mysql Java Download
2. Get Generated Keys From Mysql Java Free
3. Get Generated Keys From Mysql Java Tutorial
4. Get Generated Keys From Mysql Java 1

The driver will ignore the array if the SQL statement is not an INSERT statement, or an SQL statement able to return auto-generated keys (the list of such statements is vendor-specific). This method should be used when the returned row count may exceed Integer.MAXVALUE. It shows that you have successfully inserted a new candidate into the candidates table with id 134. In this tutorial, we have shown you how to use PreparedStatement object to insert a new record into a MySQL table and get the inserted ID back for further processing.

This is a Java tutorial for the MySQL database. It covers the basics of MySQL programming in Java with JDBC. ZetCode has a complete e-book for MySQL Java:MySQL Java programming e-book.

In this tutorial, we use the MySQL Connector/J driver. It is the official JDBC driver for MySQL.The examples were created and tested on Ubuntu Linux. You might also wantto check Java tutorial, PostgreSQL Java tutorial,Apache Derby tutorial,MySQL tutorial, or Spring JdbcTemplate tutorial on ZetCode.

JDBC

JDBC is an API for the Java programming language that defines how a client may access a database. It provides methods for querying and updating data in a database. JDBC is oriented towards relational databases. From a technical point of view, the API is as a set of classes in the java.sql package.To use JDBC with a particular database, we need a JDBC driver for that database.

JDBC is a cornerstone for database programming in Java. Today, itis considered to be very low-level and prone to errors. Solutionssuch as MyBatis or JdbcTemplate were created to ease the burden ofJDBC programming. However, under the hood, these solutions still use JDBC.JDBC is part of the Java Standard Edition platform.

JDBC manages these three main programming activities:

• connecting to a database;
• sending queries and update statements to the database;
• retrieving and processing the results received from the database in answer to the query.

MySQL Connector/J

To connect to MySQL in Java, MySQL provides MySQL Connector/J, a driver that implements the JDBC API. MySQL Connector/J is a JDBC Type 4 driver. The Type 4 designation means that the driver is a pure Java implementation of the MySQL protocol and does not rely on the MySQL client libraries. In this tutorial, we use MySQL Connector/J 5.1.41, which is a maintenance release of the 5.1 production branch.

Connection string

A database connection is defined with a connection string. It contains information such as database type, database name, servername, and port number. It also may contain additional key/valuepairs for configuration. Each database has its own connection stringformat.

The following is a syntax of a MySQL connection string:

It is possible to specify multiple hosts for a server failover setup.The items in square brackets are optional. If no host is specified, the host name defaults to localhost. If the port for a host is not specified, it defaults to 3306, the default port number for MySQL servers.

This is an example of a MySQL connection string. The jdbc:mysql:// is knownas a sub-protocol and is constant for MySQL. We connect to the localhost on MySQL standard port 3306. The database nameis testdb. The additional key/value pairs follow the question mark character (?). The useSSL=false tells MySQL that there will be no secure connection.

About MySQL database

MySQL is a leading open source database management system. It is a multi user, multithreaded database management system. MySQL is especially popular on the web. It is one part of the very popular LAMP platform consisting of Linux, Apache, MySQL, and PHP. Currently MySQL is owned by Oracle. MySQL database is available on most important OS platforms. It runs on BSD Unix, Linux, Windows, or Mac OS.Wikipedia and YouTube use MySQL. These sites manage millions of querieseach day. MySQL comes in two versions: MySQL server system and MySQLembedded system.

Setting up MySQL

In this section, we are going to install MySQL server, create a testdbdatabase, and a test user.

This command installs the MySQL server and various other packages. While installing the package, we are prompted to enter a password for the MySQL root account.

Next, we are going to create a new database user and a new database. We use the mysql client.

We check if the MySQL server is running. If not, we needto start the server. On Ubuntu Linux, this can be donewith the sudo service mysql start command.

We use the mysql monitor client application to connect to the server. We connect to the database using the root account. We show all availabledatabases with the SHOW DATABASES statement.

We create a new testdb database. We will use this database throughout the tutorial.

We create a new database user. We grant all privileges to this userfor all tables of the testdb database.

Maven file

We use the following Maven file:

The POM file has a dependency for the MySQL driver. We also include the exec-maven-pluginfor executing Java programs from Maven. Between the <mainClass></mainClass> tagswe provide the full name of the application.

Java MySQL version

If the following program runs OK, then we have everythinginstalled OK. We check the version of the MySQL server.

We connect to the database and get some info about the MySQL server.

This is the connection URL for the MySQL database. Each driverhas a different syntax for the URL. In our case, we provide a host, a port, and a database name.

We establish a connection to the database, using the connection URL,user name, and password. The connection is established with the getConnection() method.

The createStatement() method of the connectionobject creates a Statement object for sending SQL statements to the database.

The executeQuery() method of the connectionobject executes the given SQL statement, which returns a single ResultSet object.The ResultSet is a table of data returned by a specific SQL statement.

The try-with-resources syntax ensures that the resources are cleaned upin the end.

A ResultSet object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The next()method moves the cursor to the next row. If there are no rows left, the methodreturns false. The getString() method retrieves the value of a specified column. The first column has index 1.

In case of an exception, we log the error message. For this consoleexample, the message is displayed in the terminal.

We run the program from the command line. The Manen's -q option runs Mavenin quiet mode; i.e. we only see error messages.

Creating and populating tables

Next we are going to create database tables and fill themwith data. These tables will be used throughout this tutorial.

Get Generated Keys From Mysql Java Download

The SQL commands create four database tables: Authors, Books, Testing, and Images. The tables are of InnoDB type. InnoDB databases support foreign key constraints andtransactions. We place a foreign key constraint on the AuthorId column of the Books table. We fill the Authors and Books tables with initial data.

We use the source command to execute the tables.sql script.

Java MySQL prepared statements

Now we will concern ourselves with prepared statements. When we write prepared statements, we use placeholders instead of directly writing the values into the statements. Prepared statements increase security and performance.

In Java a PreparedStatement is an objectwhich represents a precompiled SQL statement.

We add a new author to the Authors table.

Here we create a prepared statement. When we write prepared statements, we use placeholders instead of directly writing the values into the statements. Prepared statements are faster and guard against SQL injection attacks. The ? is a placeholder which is going to be filled later.

A value is bound to the placeholder.

The prepared statement is executed. We use the executeUpdate()method of the statement object when we don't expect any data to be returned. This is when we create databases or execute INSERT, UPDATE, DELETE statements.

We have a new author inserted into the table.

Testing MySQL prepared and not prepared statements

For the following two examples, we will use the Testingtable. We will execute a normal statement and a preparedstatement 5000 times. We check if there is some differencein execution time.

The first example uses the normal Statement object.

Get a google maps api key. We build the query and execute it 5000 times.

It 4.14 minutes to finish the 5000 inserts.

Now we use the PreparedStatement to do the same task.

We create the prepared statement using the prepareStatement()method.

We bind a value to the prepared statement, execute it in a loopthousand times.

Now it took 3.53 minutes to finish the 5000 inserts. We saved 20 seconds.

Java MySQL retrieving data

Next we will show how to retrieve data from a database table.We get all data from the Authors table.

We get all authors from the Authors table and print them to the console.

We execute a query that selects all columns from the Authors table.We use the executeQuery() method. The method executes the given SQL statement, which returns a single ResultSet object. The ResultSet is the data table returned by the SQL query.

The next() method advances the cursor to the next record.It returns false when there are no more rows in the result set. The getInt() and getString() methods retrieve the value of the designated column in the current row of this ResultSet object as an int and String of the Java programming language.

We execute the program; we have IDs and names of authors printed to the console.

Properties

It is a common practice to put the configuration data outside theprogram in a separate file. This way the programmers are more flexible. We can change the user, a password or a connection urlwithout needing to recompile the program. It is especially usefulin a dynamic environment, where is a need for a lot of testing,debugging, securing data etc.

In Java, the Properties is a class usedoften for this. The class is used for easy reading and savingof key/value properties.

We have a db.properties file in which we have threekey/value pairs. These are dynamically loaded during the executionof the program.

We connect to the testdb database and print the contents of the Authors table to the console. This time, we load the connection properties from a file. They are not hard coded in the proram.

The Properties class is created. The data is loadedfrom the file called db.properties, where we have our configurationdata.

The values are retrieved with the getProperty()method.

Java MySQL datasource

In this example, we connect to the database using a data source. The usage of a data source improves application's performance and scalability. Using a datasource has several advantages over the DriverManager: increased portability, connection pooling, and distributed transactions.

The MysqlDataSource is a class for creating datasources.

The are the properties for the MySQL database.

In this example, we connect to the database using a datasource.

The database properties are read from the db.properties file.

A MysqlDataSource is created and the datasource properties are set.

A connection object is created from the datasource.

Java MySQL multiple statements

It is possible to execute multiple SQL statements in one query.The allowMultiQueries must be set to enable multiplestatements in MySQL.

In the code example, we retrieve three rows from the Authors table. We use three SELECT statements to get three rows.

We enable multiple statements queries in the database URL by settingthe allowMultiQueries parameter to true.

Here we have a query with multiple statements. The statements are separatedby a semicolon.

We call the execute() method of the prepared statementobject. The method returns a boolean value indicating if the first resultis a ResultSet object. Subsequent results are called usingthe getMoreResults() method.

The processing of the results is done inside the do while loop. The ResultSet is retrieved with the getResultSet()method call. To find out if there are other results, we call the getMoreResults() method.

This is the output of the example. The first three rows were retrieved from theAuthors table.

Java MySQL column headers

The following example shows how to print column headers with the datafrom the database table. We refer to column names as MetaData. MetaData is data about the core data in the database.

In this program, we select authors from the Authors tableand their books from the Books table. We print the names of the columns returned in the result set. The output is formatted.

This is the SQL statement which joins authors with theirbooks.

To get the column names we need to get the ResultSetMetaData.It is an object that can be used to get information about the types and properties of the columns in a ResultSet object.

From the obtained metadata, we get the column names.

We print the column names to the console.

We print the data to the console. The first column is 21 characters wide and is aligned to the left.

This is the output of the program.

MySQL Java auto-generated keys

MySQL's AUTO_INCREMENT attribute generates a unique ID for new rows. The following example shows how we can use JDBC to retrieve an auto-generated key value.

In the example, we add a new author to a table that has its primary keyauto-incremented by MySQL. We retrieve the generated ID.

As the first step, we have to pass the Statement.RETURN_GENERATED_KEYSto the prepareStatement() method.

Then we retrieve the generated key(s) with the getGeneratedKeys() method.

Since we have only one insert statement, we use first()to navigate to the value.

This is a sample output.

MySQL Java writing images

Some people prefer to put their images into the database, some prefer to keep them on the file system for their applications. Technical difficulties arise when we work with lots of images. Images are binary data. MySQL database has a special data type to store binary data called BLOB (Binary Large Object).

For this example, we use the Images table.

In the preceding example, we read a PNG image from the currentworking directory and insert in into the Images table.

This is the SQL to insert an image.

We create a File object for the image file. To read bytes from this file, we create a FileInputStreamobject.

The binary stream is set to the prepared statement. The parameters ofthe setBinaryStream() method are the parameterindex to bind, the input stream, and the number of bytes in the stream.

We execute the statement.

MySQL Java reading images

In the previous example, we have inserted an image into the database table. Now we are going to read the image back from the table.

We read one image from the Images table.

We select one record from the table.

The FileOutputStream object is createdto write to a file. It is meant for writing streams of raw bytes such as image data.

We get the image data from the Data column by callingthe getBlob() method.

We figure out the length of the blob data. In other words,we get the number of bytes.

The getBytes() method retrieves all bytes of the Blob object, as an array of bytes.

The bytes are written to the output stream. The image is createdon the filesystem.

Transaction support

A transaction is an atomic unit of database operations against the data in one or more databases. The effects of all the SQL statementsin a transaction can be either all committed to the database or all rolled back.

The MySQL database has different types of storage engines. The most common are the MyISAMand the InnoDB engines. There is a trade-off between data security anddatabase speed. The MyISAM tables are faster to process and they do not support transactions. On the other hand, the InnoDB tables are more safe against the data loss. They support transactions and are slower to process.

In this program, we want to change the name of the authoron the first row of the Authors table. We must also change thebooks associated with this author. This is a good example where a transaction is necessary. If we change the author and do notchange the author's books, the data is corrupted.

To work with transactions, we must set the autocommit mode to false.By default, a database connection is in autocommit mode. In this mode each statement is committed to the database as soon as itis executed. A statement cannot be undone. When the autocommit isturned off, we commit the changes by calling the commit() or roll it back by calling therollback() method.

The third SQL statement has an error. There is no Titl column in thetable.

If there is no exception, the transaction is committed.

In case of an exception, the transaction is rolled back. No changes are committed to the database.

The application ends with an exception.

The transaction was rolled back and no changes took place.

However, without a transaction, the data is not safe.

We have the same example. This time, without the transaction support.

An exception is thrown again. Leo Tolstoy did not write Martin Eden; the data is corrupted.

Batch updates

When we need to update data with multiple statements, we can usebatch updates. Batch updates are available for INSERT, UPDATE, DELETE, statements as well as for CREATE TABLE and DROP TABLE statements.

This is an example program for a batch update. We delete all data from theAuthors table and insert new data. We add one new author, Umberto Eco to see the changes.

We use teh addBatch() method to add a new command to the statement.

After adding all commands, we call the executeBatch() to perform abatch update. The method returns an array of committed changes.

Batch updates are committed in a transaction.

We call rollback() in case the batch updates failed.

We execute the BatchUpdate program. The SELECT statement shows that the Authors2 table was successfully updated. It has a new author, Umerto Eco.

Exporting data to a CSV file

The next example exports data into a CSV file.

Get Generated Keys From Mysql Java Free

We need to have proper file permissions for our testuser; otherwise, we get access denied error message.

We set the FILE permission.

For security reasons, MySQL starts with --secure-file-priv option enabled, which only allows to work with files in a certain directory.The directory is specified in the secure_file_priv variable. On Windows, thepath is something like 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads'.

We export the authors and their corresponding books to the /var/lib/mysql-files/authors_books.csvfile.

To export data into a file, we use the SELECT INTO OUTFILE SQL statement.

Get Generated Keys From Mysql Java Tutorial

We verify the data.

Get Generated Keys From Mysql Java 1

This was the MySQL Java tutorial. You might be also interested in JDBI tutorial,Java H2 tutorial,PostgreSQL Java tutorial,MongoDB Java tutorial, orMySQL tutorial.