Im using h2 database for my application, and my task is to create and read queries as practice. The connection works fine, but the table is not found no matter what I try to do. My classes look like the following:
DB connection:
package database;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class DatabaseConnectionManager {
private final String url;
private final Properties properties;
public DatabaseConnectionManager(String host, String databaseName, String username,String password){
this.url = "jdbc:h2:mem://"+host+"/"+databaseName;
this.properties = new Properties();
this.properties.setProperty("username",username);
this.properties.setProperty("password",password);
}
public Connection getConnection() throws SQLException{
return DriverManager.getConnection(this.url,this.properties);
}
}
JDBCExecutor:
package database;
import dataaccess.SporteventDAO;
import domain.FootballSportEvent;
import java.sql.*;
import java.time.LocalDateTime;
public class JDBCExecutor {
public static void main(String[] args) {
DatabaseConnectionManager databaseConnectionManager= new DatabaseConnectionManager("localhost","jdbc:h2:mem:sports_betting","sa","");
try{
Connection connection = databaseConnectionManager.getConnection();
SporteventDAO sporteventDAO = new SporteventDAO(connection);
FootballSportEvent footballSportEvent = new FootballSportEvent();
footballSportEvent.setType("FootballSportEvent");
footballSportEvent.setId(2);
Date start = new Date(2022-07-06);
Date end = new Date(2022-07-07);
footballSportEvent.setStart(start);
footballSportEvent.setEnd(end);
footballSportEvent.setResultId(2);
sporteventDAO.create(footballSportEvent);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
SporteventDAO:
package dataaccess;
import domain.FootballSportEvent;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
public class SporteventDAO extends DataAccessObject<FootballSportEvent> {
private static final String INSERT = "INSERT INTO SPORTEVENT (DTYPE, ID, START, END, TITLE, RESULT_ID) VALUES (?,?,?,?,?,?)";
public SporteventDAO(Connection connection){
super(connection);
}
@Override
public FootballSportEvent findById(long id) {
return null;
}
@Override
public List<FootballSportEvent> findAll() {
return null;
}
@Override
public FootballSportEvent update(FootballSportEvent dto) {
return null;
}
@Override
public FootballSportEvent create(FootballSportEvent dto) throws SQLException {
try(PreparedStatement statement = this.connection.prepareStatement(INSERT);){
statement.setString(1,dto.getType());
statement.setInt(2,dto.getId());
statement.setDate(3, (Date) dto.getStart());
statement.setDate(4, (Date) dto.getEnd());
statement.setInt(5, dto.getResultId());
statement.execute();
}catch (SQLException e){
e.printStackTrace();
throw new RuntimeException(e);
}
return null;
}
@Override
public void delete(long id) {
}
}
DataTransferObject:
package dataaccess;
public interface DataTransferObject {
int getId();
}
DataAccessObject:
package dataaccess;
import org.h2.jdbc.JdbcConnectionBackwardsCompat;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
public abstract class DataAccessObject <T extends DataTransferObject>{
protected final Connection connection;
protected final static String LAST_VAL = "SELECT last_value FROM";
protected final static String CUSTOMER_SEQUENCE = "hp_customer_seq";
public DataAccessObject(Connection connection){
super();
this.connection =connection;
}
public abstract T findById(long id);
public abstract List<T> findAll();
public abstract T update(T dto);
public abstract T create(T dto) throws SQLException;
public abstract void delete(long id);
protected int getLastVal(String sequence){
int key = 0;
String sql = LAST_VAL + sequence;
try(Statement statement = connection.createStatement()) {
ResultSet rs = statement.executeQuery(sql);
while (rs.next()) {
key = rs.getInt(1);
}
return key;
}catch (SQLException e){
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
FootballSportEventClass:
package domain;
import dataaccess.DataTransferObject;
import java.util.Date;
public class FootballSportEvent implements DataTransferObject
{
private String type;
private String name;
private int id;
private Date end;
private Date start;
private String title;
private int resultId;
public FootballSportEvent(){
}
public FootballSportEvent(String type, String name, int id, Date end, Date start, String title, int resultId) {
this.type = type;
this.name = name;
this.id = id;
this.end = end;
this.start = start;
this.title = title;
this.resultId = resultId;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public Date getEnd() {
return end;
}
public void setEnd(Date end) {
this.end = end;
}
public Date getStart() {
return start;
}
public void setStart(Date start) {
this.start = start;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public int getResultId() {
return resultId;
}
public void setResultId(int resultId) {
this.resultId = resultId;
}
@Override
public String toString() {
return "FootballSportEvent{" +
"type='" + type + '\'' +
", name='" + name + '\'' +
", id=" + id +
", end=" + end +
", start=" + start +
", title='" + title + '\'' +
", resultId=" + resultId +
'}';
}
}
And the following exception it thrown:
org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "SPORTEVENT" not found; SQL statement:
INSERT INTO SPORTEVENT (DTYPE, ID, START, END, TITLE, RESULT_ID) VALUES (?,?,?,?,?,?)
Edit:
I also tried using:
this.url = "jdbc:h2:mem://"+host+"/"+databaseName+";DB_CLOSE_DELAY=-1";
but it doesn't work.
