Java

MySQL Database Connection in Java

Tools and Technologies Used:

1. Java 8

2. MySQL 8

3. Eclipse IDE

4. mysql-connector-java-8.0.23.jar

MySQL table structure and insert statement

	-- Table create command
	CREATE TABLE `student` (
	  `id` int NOT NULL AUTO_INCREMENT,
	  `first_name` varchar(250) DEFAULT NULL,
	  `last_name` varchar(250) DEFAULT NULL,
	  PRIMARY KEY (`id`)
	);
	
	-- Insert command
	INSERT INTO `student` (`first_name`, `last_name`) VALUES ('Hareesh', 'Soni');
	
	-- Commit command
	commit;

MySQLDatabaseConnection.java

	package com.hareeshsoni.dbconnection;
	
	import java.sql.Connection;
	import java.sql.DriverManager;
	import java.sql.ResultSet;
	import java.sql.SQLException;
	import java.sql.Statement;
	
	/**
	 * @author HAREESH
	 *
	 */
	public class MySQLDatabaseConnection {
	
		// JDBC driver name
		private static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
	
		// Database URL
		private static final String DATABASE_URL = "jdbc:mysql://localhost/development";
	
		// Database user name
		private static final String DATABASE_USER = "root";
	
		// Database password
		private static final String DATABASE_PASS = "########";
	
		public static void main(String[] args) {
	
			Connection conn = null;
			Statement stmt = null;
			try {
				// Register JDBC driver
				Class.forName(JDBC_DRIVER);
	
				// Open a connection
				System.out.println("Connecting to database...");
				conn = DriverManager.getConnection(DATABASE_URL, DATABASE_USER, DATABASE_PASS);
	
				// Execute a query
				System.out.println("Creating statement...");
				stmt = conn.createStatement();
				String sql = "SELECT id, first_name, last_name FROM student";
	
				ResultSet rs = stmt.executeQuery(sql);
	
				// Extract data from result set
				while (rs.next()) {
					// Retrieve values using column name
					int id = rs.getInt("id");
					String firstName = rs.getString("first_name");
					String lastName = rs.getString("last_name");
	
					// Display values
					System.out.println("id: " + id);
					System.out.println("First Name: " + firstName);
					System.out.println("Last Name: " + lastName);
				}
				// Close Result Set
				rs.close();
			} catch (SQLException | ClassNotFoundException exception) {
				// Handle JDBC errors 
				exception.printStackTrace();
			} finally {
				// finally block used to close resources
				try {
					if (stmt != null)
						stmt.close();
					if (conn != null)
						conn.close();
				} catch (SQLException sqlException) {
					sqlException.printStackTrace();
				} 
			} // end finally
		}// end main
	}

Oracle Database Connection in Java

Tools and Technologies Used:

1. Java 8

2. Oracle Database 18c Express Edition

3. Eclipse IDE

4. ojdbc8.jar

ORACLE table structure and insert statement

	-- Table create command
	CREATE TABLE student(
	    id NUMBER GENERATED BY DEFAULT AS IDENTITY,
	    first_name VARCHAR2(250) NOT NULL,
	    last_name VARCHAR2(250) NOT NULL,
	    PRIMARY KEY(id)
	);
	
	-- Insert command
	INSERT INTO student (first_name, last_name) VALUES ('Hareesh', 'Soni');
	
	-- Commit command
	commit;

OracleDatabaseConnection.java

	package com.hareeshsoni.dbconnection;
	
	import java.sql.Connection;
	import java.sql.DriverManager;
	import java.sql.ResultSet;
	import java.sql.SQLException;
	import java.sql.Statement;
	
	/**
	 * @author HAREESH
	 *
	 */
	public class OracleDatabaseConnection {
	
		// JDBC driver name
		private static final String JDBC_DRIVER = "oracle.jdbc.driver.OracleDriver";
	
		// Database URL
		private static final String DATABASE_URL = "jdbc:oracle:thin:@localhost:1521:XE";
	
		// Database user name
		private static final String DATABASE_USER = "c##blog_user1";
	
		// Database password
		private static final String DATABASE_PASS = "########";
	
		public static void main(String[] args) {
	
			Connection conn = null;
			Statement stmt = null;
			try {
				// Register JDBC driver
				Class.forName(JDBC_DRIVER);
	
				// Open a connection
				System.out.println("Connecting to database...");
				conn = DriverManager.getConnection(DATABASE_URL, DATABASE_USER, DATABASE_PASS);
				
				// Execute a query
				System.out.println("Creating statement...");
				stmt = conn.createStatement();
				String sql = "SELECT id, first_name, last_name FROM student";
				
				ResultSet rs = stmt.executeQuery(sql);
				
				// Extract data from result set
				while (rs.next()) {
					// Retrieve values using column name
					int id = rs.getInt("id");
					String firstName = rs.getString("first_name");
					String lastName = rs.getString("last_name");
	
					// Display values
					System.out.println("id: " + id);
					System.out.println("First Name: " + firstName);
					System.out.println("Last Name: " + lastName);
				}
				// Close Result Set
				rs.close();
			} catch (SQLException | ClassNotFoundException exception) {
				// Handle JDBC errors
				exception.printStackTrace();
			} finally {
				// finally block used to close resources
				try {
					if (stmt != null)
						stmt.close();
					if (conn != null)
						conn.close();
				} catch (SQLException sqlException) {
					sqlException.printStackTrace();
				}
			} // end finally
		}// end main
	}