Databases

Using H2 in IntelliJ with SQL, no Java

  • One time only - Download and install H2 from www.h2database.com/html/main.html

      • If you don't have admin rights, like on the classroom computers, you can change the install location to C:\

  • Create and connect to database - video

    • Create a new project (if necessary)

    • Right click the project name -> New -> Directory -> res

    • IntelliJ Database tab (must be Ultimate)

    • +

    • Data Source from Path (more information)

      • C:/Users/username/OneDrive - Florida Gulf Coast University/IDEAProjects/ProjectName/res/databasename

      • Driver: H2

    • One time only - Download missing driver files

    • Test Connection, green check == good

    • You can run SQL in the console window using the green arrow, like MySQL_Schema_HR or Oracle_Schema_HR

Using H2 in a Java Project in IntelliJ IDEA without Gradle

  • Windows: File -> Project Structure -> Modules -> Dependencies -> + Jars... -> browse to h2 jar file, for Windows likely in C:\Program Files (x86)\H2\bin folder for MacOS the folder where you downloaded -> OK

Using H2 in a Java Project in IntelliJ IDEA with Gradle

  • Add compile group: 'com.h2database', name: 'h2', version: '1.4.199' in dependencies section in build.gradle

Troubleshooting

  • If you get a table not found error even after running the SQL to create the tables there is likely a problem with your DB_URL resulting from not typing the database name after res when creating the data source. IntelliJ probably made a folder and database in a different location but the database has no tables. You could either delete the existing database from the Database tab and connect to the newly created one in the project and rerun the SQL to create the tables, or delete both and redo the database creation properly.

  • If H2 auto increments by 32 it is because the program is not closing the connection properly.

Resources

Using H2 in IntelliJ with Java - Basic Non-GUI Database Program

Requirements:

    • Instructions from Using H2 in IntelliJ IDEA

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;


public class Main {


public static void main(String[] args) {


final String JDBC_DRIVER = "org.h2.Driver";

final String DB_URL = "jdbc:h2:./res/HR";


// Database credentials

final String USER = "";

final String PASS = "";

Connection conn = null;

Statement stmt = null;

try {

// STEP 1: Register JDBC driver

Class.forName(JDBC_DRIVER);


//STEP 2: Open a connection

conn = DriverManager.getConnection(DB_URL, USER, PASS);


//STEP 3: Execute a query

stmt = conn.createStatement();

String sql = "SELECT * FROM JOBS";


ResultSet rs = stmt.executeQuery(sql);

while (rs.next()) {

System.out.println(rs.getString(1));

}


// STEP 4: Clean-up environment

stmt.close();

conn.close();

} catch (ClassNotFoundException e) {

e.printStackTrace();


} catch (SQLException e) {

e.printStackTrace();

}

}

}

Using Gradle and MySQL

dependencies {

testCompile group: 'junit', name: 'junit', version: '4.12'


// Use MySQL Connector-J

compile group: 'mysql', name: 'mysql-connector-java', version: '8.0.17'

// Code from https://www.w3resource.com/mysql/mysql-java-connection.php

// Version from https://dev.mysql.com/downloads/connector/j/


}