Database setup MySQL or OracleXE
JDBC (Java Database Connectivity) is a Java platform library that provides API to connect to a database and operate with it. Nowadays, there are a lot of frameworks that are built for easier work with databases. But all of them contain the JDBC under the hood.
MySQL
MySQL is a very popular free and open-source relational databases, relational database management system (RDBMS).
Nowadays, the MySQL solutions have become the leading database choice for web-based applications with its proven performance, reliability and ease-of-use.
The creator and the first sponsor of MySQL was the Swedish company MySQL AB. After the Sun Microsystems bought MySQL, then Oracle Corporation.
Oracle drives MySQL innovation, delivering new capabilities to power next-generation web, cloud, mobile, and embedded applications.
Setup MySQL database
-
Installing the driver.
MySQL Connector/J is a tool that provides connectivity to MySQL for client applications developed in the Java programming language.
Connector/J implements the Java Database Connectivity API (JDBC 4.2).
Currently, there are two MySQL Connector/J versions available:
Connector/J 8.0 for JRE 8 or higher – recommended version.
Connector/J 5.1 for JRE 5 or higher.
We will use Connector/J 8.0 for the Java 8 platform. For Java 7 or earlier, use Connector/J 5.1 instead.
There are several ways to install the Connector/J package: installing from a binary distribution, installing from source, installing using Maven.
The easiest method for installation is the binary distribution. If you use the source distribution you can customize your installation. You can download both types of distributions from the Connector/J Download page.
The source code for Connector/J is also available on GitHub at https://github.com/mysql/mysql-connector-j.
If you use Maven, you can find Connector/J is available in the central repository:
<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>x.y.z</version> </dependency>
-
Obtaining the Connection.
Let’s get connection from DriverManager:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class ConnectionExample {
private final static String DB_NAME= "testdb";
private final static String USERNAME= "root";
private final static String PASSWORD= "root";
public static void main(String[] args) {
// load the driver class
Class.forName("com.mysql.jdbc.Driver");
Connection connection = null;
try {
connection =
DriverManager.getConnection("jdbc:mysql://localhost/"
+ DB_NAME + "?
serverTimezone=UTC&useSSL=false", USERNAME, PASSWORD);
System.out.println("We got the connection!");
// work with connection
} catch (SQLException ex) {
System.out.println("SQLException: " + ex.getMessage());
}
}
}
As you can see from imports, we are using classes from the package java.sql.
The DriverManager method getConnection attempts to establish a connection to the given database URL. It tries to select an appropriate driver from the set of registered JDBC drivers. The basic form of URL is jdbc:subprotocol:subname. Our connection URL is:
jdbc:mysql://localhost/mysql?serverTimezone=UTC&useSSL=false, where
“jdbc” points to the API,
“mysql” is the database,
“localhost” is the mysql server name (it may be also an IP address),
“3306” is the default port number(we haven’t changed it),
“testdb” is the name of our database name (it can be any name, for example, “mydatabase”).
We also used two configuration properties:
“serverTimezone=UTC” we set the default time zone. Without this property, we will get the warning that our time zone is unrecognized.
“ useSSL=false” we disabled SSL. Without this property, we will get the warning that establishing an SSL connection without the server identity verification is not recommended. Another way to fix this warning is to provide a trust store for server certificate verification.
Also, you have to send a username and password. The default username for the MySQL database is root. The password is given by the user during the installation of the MySQL database. We are going to use root as the password.
After getting the connection to the database, you can use it to create Statement and PreparedStatement objects, or retrieve metadata about the database.
Basic database operations
There are query and update statements.
The update statements return an update count that indicates how many rows were affected in the database. They are as INSERT, UPDATE and DELETE.
The query statements return a row result set(ResultSet class) and we can use it walk over the result set. We can retrieve an individual column in a row by name or by column number. The ResultSet class has metadata with the names of the columns and their types.
Let’s create a simple database table to demonstrate simple operations on the database with JDBC. You can use the MySQL Workbench for this purpose. We have created table Persons with columns ID, LastName, FirstName, Address, City. After, we added one row to this table. Look at the screenshot below:
Here is our simple code to get the first row from our table and print the last name, first name, address and city of the person:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class ConnectionExample {
public static void main(String[] args) {
Connection connection = null;
try {
connection =
DriverManager.getConnection("jdbc:mysql://localhost/testdb" +
"?serverTimezone=UTC&useSSL=false", "root", "root");
System.out.println("We got the connection!");
} catch (SQLException ex) {
System.out.println("SQLException: " + ex.getMessage());
}
Statement statement = null;
ResultSet resultSet = null;
try {
statement = connection.createStatement();
resultSet = statement.executeQuery("SELECT * FROM persons");
if (statement.execute("SELECT * FROM persons")) {
System.out.println("The query was executed!");
resultSet = statement.getResultSet();
}
while (resultSet.next()) {
System.out.println(resultSet.getString("LastName"));
System.out.println(resultSet.getString("FirstName"));
System.out.println(resultSet.getString("Address"));
System.out.println(resultSet.getString("City"));
}
}
catch (SQLException ex){
System.out.println("SQLException: " + ex.getMessage());
}
finally {
//let's release resources in a finally{} block
// in reverse-order of their creation
// if they are no-longer needed
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException sqlEx) { } // ignore
resultSet = null;
}
if (statement != null) {
try {
statement.close();
} catch (SQLException sqlEx) { } // ignore
statement = null;
}
System.out.println("All resources are released!");
}
}
}
In this example, we have used Statement and ResultSet objects. The Statement object used for executing a static SQL statement and returning the results it produces. The ResultSet object is a table of data representing a database result set. For more information, you can look at Java documentation.
Oracle XE
Oracle XE (eXpress Edition) is another free database edition, developed by Oracle Corporation. It offers almost all features of the Oracle database, but have some limitations for resources, such as the size of user data, database RAM, amount of CPU threads and pluggable databases.
Setup Oracle XE database
1. Installing the driver
You can upload the driver from the Oracle site. The latest version is Oracle Database 19c (19.3) JDBC Driver & UCP. The archive contains the latest 19.3 JDBC Thin driver (ojdbc10.jar), the Universal Connection Pool (ucp.jar), their Readme(s) and companion jars. If you use Maven, you can add the dependency:
<dependency> <groupId>com.oracle.jdbc</groupId> <artifactId>ojdbc8</artifactId> <version>19.3.0.0</version> <dependency>
2. Obtaining the Connection.
The way to connect to the Oracle XE and to work with it is very similar to the MySQL. The main difference is the driver and the connection URL.
The driver for Oracle XE is oracle.jdbc.driver.OracleDriver.
Let’s look at the code example:
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class ConnectionExample { private final static String user = "system"; private final static String password = "oracle"; public static void main(String args[]) { try { // load the driver class Class.forName("oracle.jdbc.driver.OracleDriver"); System.out.println("The driver for Oracle database is loaded"); Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", user , password); System.out.println("We are connected to Oracle database!"); //create the statement object Statement stmt = connection.createStatement(); //execute query ResultSet rs = stmt.executeQuery("select * from persons"); System.out.println("The query was executed!"); while (rs.next()) System.out.println(rs.getInt(1)); //close the connection object connection.close(); } catch (Exception e) { System.out.println(e); } } }
The connection URL for the oracle database is
jdbc:oracle:thin:@localhost:1521:xe, where
jdbc is the using API, oracle is the database name,
thin is the driver,
localhost is the server name with the running Oracle (we may also use IP address),
1521 is the port number,
XE is the Oracle service name.
You may get this information from the tnsnames.ora file.
We are using the default username for the oracle database (system).
The password is given by the user during the installation of the Oracle database.
After getting connection, we can also create tables:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class ConnectionExample {
public static void main(String args[]) {
try {
// load the driver class
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
System.out.println("Creating new table!");
Statement stmt = connection.createStatement();
String sql = "CREATE TEST_TABLE EMP ( ID NUMBER(5) PRIMARY KEY, NAME VARCHAR2(50))";
stmt.execute(sql);
System.out.println("Table created successfully!");
stmt.close();
} catch (Exception e) {
System.out.println(e);
}
}
}
We can also use the PreparedStatement class. This statement is cached and has a pre-determined execution path on the database server. So, it’s allowing us to execute it multiple times in an efficient manner.
The JDBC also contains the CallableStatement class. This class is used for executing stored procedures on the database.
Summary
The JDBC is a database access protocol that allows Java developers to connect to a database and run SQL statements and queries on the database. The core Java class libraries java.sql and javax.sql provide the JDBC APIs. The JDBC was created with the possibility to allow vendors to create drivers that offer the specialization for a particular database.
7 Comments