{"id":2614,"date":"2020-04-21T00:57:04","date_gmt":"2020-04-20T19:27:04","guid":{"rendered":"https:\/\/www.h2kinfosys.com\/blog\/?p=2614"},"modified":"2020-04-21T00:57:04","modified_gmt":"2020-04-20T19:27:04","slug":"database-setup-mysql-oraclexe","status":"publish","type":"post","link":"https:\/\/www.h2kinfosys.com\/blog\/database-setup-mysql-oraclexe\/","title":{"rendered":"Database setup MySQL or OracleXE"},"content":{"rendered":"

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.<\/span><\/p>\n

MySQL<\/b><\/h2>\n

MySQL is a very popular free and open-source relational databases, relational database management system (RDBMS).
\n<\/span>Nowadays, the MySQL <\/a>solutions have become the leading database choice for web-based applications with its proven performance, reliability and ease-of-use.
\n<\/span>The creator and the first sponsor of MySQL was the Swedish company MySQL AB. After the Sun Microsystems bought MySQL, then Oracle Corporation.
\n<\/span>Oracle drives MySQL innovation, delivering new capabilities to power next-generation web, cloud, mobile, and embedded applications.<\/span><\/p>\n

Setup MySQL database<\/b><\/h2>\n
    \n
  1. \n

    Installing the driver.<\/span><\/h4>\n<\/li>\n<\/ol>\n

    MySQL Connector\/J is a tool that provides connectivity to MySQL for client applications developed in the Java programming language<\/a>.\u00a0<\/span><\/p>\n

    Connector\/J implements the Java Database Connectivity API (JDBC 4.2).
    \n<\/span>Currently, there are two MySQL Connector\/J versions available:
    \n<\/span>Connector\/J 8.0 for JRE 8 or higher – recommended version.
    \n<\/span>Connector\/J 5.1 for JRE 5 or higher.<\/span><\/p>\n

    We will use Connector\/J 8.0 for the Java 8 platform. For Java 7 or earlier, use Connector\/J 5.1 instead.
    \nThere are several ways to install the Connector\/J package: installing from a binary distribution, installing from source, installing using Maven.
    \nThe 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.
    \nThe source code for Connector\/J is also available on GitHub at\u00a0https:\/\/github.com\/mysql\/mysql-connector-j.<\/p>\n

    If you use Maven, you can find Connector\/J is available in the central repository:<\/span><\/p>\n

    <dependency>\r\n <groupId>mysql<\/groupId>\r\n <artifactId>mysql-connector-java<\/artifactId>\r\n <version>x.y.z<\/version>\r\n<\/dependency><\/pre>\n
      \n
    1. \n
       Obtaining the Connection.<\/span><\/h4>\n<\/li>\n<\/ol>\n
      Let’s get connection from <\/span>DriverManager<\/span>:<\/span><\/p>\n
      import<\/strong> java.sql.Connection;\r\n\r\nimport<\/strong> java.sql.DriverManager;\r\n\r\nimport<\/strong> java.sql.SQLException;\r\n\r\npublic<\/strong> class<\/strong> ConnectionExample {\r\n\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 private<\/strong> final<\/strong> static<\/strong> String DB_NAME<\/em><\/strong>= \"testdb\";\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 private<\/strong> final<\/strong> static<\/strong> String USERNAME<\/em><\/strong>= \"root\";\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 private<\/strong> final<\/strong> static<\/strong> String PASSWORD<\/em><\/strong>= \"root\";\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public<\/strong> static<\/strong> void<\/strong> main(String[] args) {\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ load the driver class\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Class.forName<\/em>(\"com.mysql.jdbc.Driver\");\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Connection connection<\/u> = null<\/strong>;\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 try<\/strong> {\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 connection =\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0 \u00a0\u00a0 DriverManager.getConnection<\/em>(\"jdbc:mysql:\/\/localhost\/\"\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + DB_NAME<\/em><\/strong> + \"?\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n                             serverTimezone=UTC&useSSL=false\", USERNAME<\/em><\/strong>, PASSWORD<\/em><\/strong>);\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 System.out<\/em><\/strong>.println(\"We got the connection!\");\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0 \/\/ work with connection\r\n\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 } catch<\/strong> (SQLException ex) {\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0 System.out<\/em><\/strong>.println(\"SQLException: \" + ex.getMessage());\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\r\n}<\/pre>\n
       <\/p>\n
      As you can see from imports, we are using classes from the package java.sql.
      \nThe 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:<\/p>\n
      jdbc:mysql:\/\/localhost\/mysql?serverTimezone=UTC&useSSL=false, where<\/p>\n
      \u201cjdbc\u201d points to the API,
      \n\u201cmysql\u201d is the database,
      \n\u201clocalhost\u201d is the mysql server name (it may be also an IP address),
      \n\u201c3306\u201d is the default port number(we haven’t changed it),
      \n\u201ctestdb\u201d is the name of our database name (it can be any name, for example, \u201cmydatabase\u201d).
      \nWe also used two configuration properties:
      \n\u201cserverTimezone=UTC\u201d we set the default time zone. Without this property, we will get the warning that our time zone is unrecognized.
      \n\u201c useSSL=false\u201d 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.
      \nAlso, 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.
      \nAfter getting the connection to the database, you can use it to create Statement and PreparedStatement objects, or retrieve metadata about the database.<\/p>\n
      Basic database operations<\/strong><\/h3>\n
      There are query and update statements.
      \nThe       update statements<\/a> return an update count that indicates how many rows were affected in the database. They are as INSERT, UPDATE and DELETE.
      \nThe 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.
      \nLet’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:<\/p>\n
      \"\"<\/p>\n
      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:<\/span><\/p>\n
      import<\/strong> java.sql.Connection;\r\nimport<\/strong> java.sql.DriverManager;\r\nimport<\/strong> java.sql.ResultSet;\r\nimport<\/strong> java.sql.SQLException;\r\nimport<\/strong> java.sql.Statement;\r\n\r\npublic<\/strong> class<\/strong> ConnectionExample {\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public<\/strong> static<\/strong> void<\/strong> main(String[] args) {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Connection connection = null<\/strong>;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 try<\/strong> {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 connection =\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0 \u00a0\u00a0 DriverManager.getConnection<\/em>(\"jdbc:mysql:\/\/localhost\/testdb\" +\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"?serverTimezone=UTC&useSSL=false\", \"root\", \"root\");\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 System.out<\/em><\/strong>.println(\"We got the connection!\");\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 } catch<\/strong> (SQLException ex) {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0 System.out<\/em><\/strong>.println(\"SQLException: \" + ex.getMessage());\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Statement statement = null<\/strong>;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ResultSet resultSet = null<\/strong>;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 try<\/strong> {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0 statement = connection.createStatement();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0 resultSet = statement.executeQuery(\"SELECT * FROM persons\");\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0 if<\/strong> (statement.execute(\"SELECT * FROM persons\")) {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 System.out<\/em><\/strong>.println(\"The query was executed!\");\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 resultSet = statement.getResultSet();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0 while<\/strong> (resultSet.next()) {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0 \u00a0\u00a0 System.out<\/em><\/strong>.println(resultSet.getString(\"LastName\"));\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 System.out<\/em><\/strong>.println(resultSet.getString(\"FirstName\"));\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 System.out<\/em><\/strong>.println(resultSet.getString(\"Address\"));\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 System.out<\/em><\/strong>.println(resultSet.getString(\"City\"));\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 catch<\/strong> (SQLException ex){\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0 System.out<\/em><\/strong>.println(\"SQLException: \" + ex.getMessage());\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 finally<\/strong> {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0 \/\/let's release resources in a finally{} block\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0 \/\/ in reverse-order of their creation\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0 \/\/ if they are no-longer needed\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0 if<\/strong> (resultSet != null<\/strong>) {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 try<\/strong> {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 resultSet.close();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 } catch<\/strong> (SQLException sqlEx) { } \/\/ ignore\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 resultSet = null<\/strong>;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0 if<\/strong> (statement != null<\/strong>) {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 try<\/strong> {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 statement.close();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 } catch<\/strong> (SQLException sqlEx) { } \/\/ ignore\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 statement = null<\/strong>;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0 System.out<\/em><\/strong>.println(\"All resources are released!\");\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n}<\/pre>\n
      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.<\/span><\/p>\n
      Oracle XE<\/h3>\n
      Oracle XE (eXpress Edition) is another free database edition, developed by Oracle Corporation<\/a>. It offers almost all features of the Oracle database<\/a>, but have some limitations for resources, such as the size of user data, database RAM, amount of CPU threads and pluggable databases.<\/p>\n
      Setup Oracle XE database<\/h3>\n
      1. Installing the driver
      \nYou 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:<\/p>\n
      <dependency><\/span>\r\n<groupId>com.oracle.jdbc<\/groupId><\/span>\r\n<artifactId>ojdbc8<\/artifactId><\/span>\r\n<version>19.3.0.0<\/version><\/span>\r\n<dependency><\/span><\/pre>\n
      2. Obtaining the Connection.
      \nThe 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.
      \nThe driver for Oracle XE is oracle.jdbc.driver.OracleDriver.
      \nLet’s look at the code example:<\/p>\n
      import<\/b> java.sql.Connection;<\/span>\r\nimport<\/b> java.sql.DriverManager;<\/span>\r\nimport<\/b> java.sql.ResultSet;<\/span>\r\nimport<\/b> java.sql.Statement;<\/span>\r\n\r\npublic<\/b> class<\/b> ConnectionExample {<\/span>\r\n           private<\/b> final<\/b> static<\/b> String <\/span>user<\/i><\/b> = <\/span>\"system\"<\/span>;<\/span>\r\n           private<\/b> final<\/b> static<\/b> String <\/span>password<\/i><\/b> = <\/span>\"oracle\"<\/span>;<\/span>\r\n\r\n            public<\/b> static<\/b> void<\/b> main(String <\/span>args<\/span>[]) {<\/span>\r\n            try<\/b> {<\/span>\r\n\r\n           \/\/ load the driver class<\/span>\r\n           Class.<\/span>forName<\/span><\/i>(<\/span>\"oracle.jdbc.driver.OracleDriver\"<\/span>);<\/span>\r\n \u00a0        <\/span> System.<\/span>out<\/i><\/b>.println(<\/span>\"The driver for Oracle database is loaded\"<\/span>);<\/span>\r\n           Connection <\/span>connection<\/span> = <\/span>DriverManager.<\/span>getConnection<\/span><\/i>(<\/span>\"jdbc:oracle:thin:@localhost:1521:xe\"<\/span>,\u00a0<\/span>\r\n                user<\/i><\/b> , <\/span>password<\/i><\/b>);<\/span>\r\n \u00a0 <\/span>          System.<\/span>out<\/i><\/b>.println(<\/span>\"We are connected to Oracle database!\"<\/span>);<\/span>\r\n            \/\/create the statement object<\/span>\r\n             Statement <\/span>stmt<\/span> = <\/span>connection<\/span>.createStatement();<\/span>\r\n            \/\/execute query<\/span>\r\n\r\n                ResultSet <\/span>rs<\/span> = <\/span>stmt<\/span>.executeQuery(<\/span>\"select * from persons\"<\/span>);<\/span>\r\n                   System.<\/span>out<\/i><\/b>.println(<\/span>\"The query was executed!\"<\/span>);<\/span>\r\n                while<\/b> (<\/span>rs<\/span>.next())<\/span>\r\n                  System.<\/span>out<\/i><\/b>.println(<\/span>rs<\/span>.getInt(1));<\/span>\r\n\r\n                \/\/close the connection object<\/span>\r\n                   connection<\/span>.close();<\/span>\r\n                     } <\/span>catch<\/b> (Exception <\/span>e<\/span>) {<\/span>\r\n\r\n                   System.<\/span>out<\/i><\/b>.println(<\/span>e<\/span>);<\/span>\r\n\r\n                   }<\/span>\r\n            }<\/span>\r\n        }<\/span><\/pre>\n
      The connection URL for the oracle database is
      \njdbc:oracle:thin:@localhost:1521:xe, where
      \njdbc is the using API, oracle is the database name,
      \nthin is the driver,
      \nlocalhost is the server name with the running Oracle (we may also use IP address),
      \n1521 is the port number,
      \nXE is the Oracle service name.
      \nYou may get this information from the tnsnames.ora file.
      \nWe are using the default username for the oracle database (system).
      \nThe password is given by the user during the installation of the Oracle database.
      \nAfter getting connection, we can also create tables:<\/p>\n
      import<\/strong> java.sql.Connection;\r\nimport<\/strong> java.sql.DriverManager;\r\nimport<\/strong> java.sql.Statement;\r\n\r\npublic<\/strong> class<\/strong> ConnectionExample {\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public<\/strong> static<\/strong> void<\/strong> main(String args[]) {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 try<\/strong> {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ load the driver class\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Class.forName<\/em>(\"oracle.jdbc.driver.OracleDriver\");\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Connection connection = DriverManager.getConnection<\/em>(\"jdbc:oracle:thin:@localhost:1521:xe\",\"system\",\"oracle\");\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 System.out<\/em><\/strong>.println(\"Creating new table!\");\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Statement stmt = connection.createStatement();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 String sql = \"CREATE TEST_TABLE EMP ( ID NUMBER(5) PRIMARY KEY, NAME\u00a0VARCHAR2(50))\";\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 stmt.execute(sql);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 System.out<\/em><\/strong>.println(\"Table created successfully!\");\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 stmt.close();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 } catch<\/strong> (Exception e) {\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 System.out<\/em><\/strong>.println(e);\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\r\n}<\/pre>\n
      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.<\/span><\/p>\n
      The JDBC also contains the CallableStatement class. This class is used for executing stored procedures on the database.<\/span><\/p>\n
      Summary<\/b><\/p>\n
      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<\/a> 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.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"
      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 […]<\/p>\n","protected":false},"author":1,"featured_media":2657,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[42],"tags":[537,540,541,539,538],"class_list":["post-2614","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-java-tutorials","tag-database-setup-mysql","tag-java-database-connectivity","tag-jdbc","tag-mysql-database","tag-oraclexe"],"_links":{"self":[{"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/posts\/2614","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/comments?post=2614"}],"version-history":[{"count":0,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/posts\/2614\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/media\/2657"}],"wp:attachment":[{"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/media?parent=2614"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/categories?post=2614"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/tags?post=2614"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}