Jan 03, 2005 tement.java:538) java at com.ace.db.DbHelper.insertObject(DbHelper.java:351) If someone can tell me how to correct this, or if there is a fix for it somewhere. Import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Main.
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 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:
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.
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.
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.
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.
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.
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.
Next we are going to create database tables and fill themwith data. These tables will be used throughout this tutorial.
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.
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.
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.
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.
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.
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.
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.
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.
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'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.
Netgear stora software for mac. 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.
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.
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. Generating equivalent expressions answer key. 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.
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.
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.
The next example exports data into a CSV file.
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.
We verify the data.
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.