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
H2 Database - JDBC Connection tutorialspoint
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/
}