How can I connect to embedded database in Java Class?

Camunda 7 Topics Discussion & Questions
1

Hello, I’m new ,I try to connect at the database but when I execute a query I have the following error:

Blockquote
Connecting to database…
Connected database successfully…
org.h2.jdbc.JdbcSQLException: Table “ACT_RU_VARIABLES” not found; SQL statement:
SELECT * FROM ACT_RU_VARIABLES; [42102-176]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:344)
at org.h2.message.DbException.get(DbException.java:178)
at org.h2.message.DbException.get(DbException.java:154)
at org.h2.command.Parser.readTableOrView(Parser.java:5213)
at org.h2.command.Parser.readTableFilter(Parser.java:1220)
at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1859)
at org.h2.command.Parser.parseSelectSimple(Parser.java:1968)
at org.h2.command.Parser.parseSelectSub(Parser.java:1853)
at org.h2.command.Parser.parseSelectUnion(Parser.java:1674)
at org.h2.command.Parser.parseSelect(Parser.java:1662)
at org.h2.command.Parser.parsePrepared(Parser.java:434)
at org.h2.command.Parser.parse(Parser.java:306)
at org.h2.command.Parser.parse(Parser.java:278)
at org.h2.command.Parser.prepareCommand(Parser.java:243)
at org.h2.engine.Session.prepareLocal(Session.java:442)
at org.h2.engine.Session.prepareCommand(Session.java:384)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1188)
at org.h2.jdbc.JdbcStatement.executeQuery(JdbcStatement.java:75)
at org.camunda.bpm.getstarted.incidentreview.basetest.main(basetest.java:37)

I don’t understand.

My Java class:
import java.sql.Connection;
import org.h2.jdbcx.JdbcDataSource;
import javax.naming.Context;
import javax.naming.InitialContext;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class basetest {

   // JDBC driver name and database URL 
   static final String JDBC_DRIVER = "org.h2.Driver";   
   static final String DB_URL = "jdbc:h2:mem:./camunda-h2-dbs/process-engine;DB_CLOSE_DELAY=-1";
   //  Database credentials 
   static final String USER = "sa"; 
   static final String PASS = "sa"; 
   
   public static void main(String[] args) { 
      Connection conn = null; 
      Statement stmt = null; 
      try { 
         // STEP 1: Register JDBC driver 
         Class.forName(JDBC_DRIVER); 
         
         // STEP 2: Open a connection 
         System.out.println("Connecting to database..."); 
         conn = DriverManager.getConnection(DB_URL,USER,PASS);  
         
         // STEP 3: Execute a query 
         System.out.println("Connected database successfully..."); 
         stmt = conn.createStatement(); 
        
        
         ResultSet rs = stmt.executeQuery("SELECT ID_ FROM ACT_RU_VARIABLES;"); 
        System.out.println(rs);
         // STEP 4: Extract data from result set 
         while(rs.next()) { 
            // Retrieve by column name 
       
            String first = rs.getString("ID_"); 
        
            
            // Display values 
        
            System.out.print(", ID_: " +  first); 
         } 
         // STEP 5: Clean-up environment 
         rs.close(); 
      } catch(SQLException se) { 
         // Handle errors for JDBC 
         se.printStackTrace(); 
      } catch(Exception e) { 
         // Handle errors for Class.forName 
         e.printStackTrace(); 
      } finally { 
         // finally block used to close resources 
         try { 
            if(stmt!=null) stmt.close();  
         } catch(SQLException se2) { 
         } // nothing we can do 
         try { 
            if(conn!=null) conn.close(); 
         } catch(SQLException se) { 
            se.printStackTrace(); 
         } // end finally try 
      } // end try 
   }

}

Blockquote

2

Hi,
what are you trying to achieve?
Why dont you use the normal camunda public java api?

1 Like
3

@Configuration(value = “dataSourceConfig”)
public class DataSourceConfig {

@Value(“${ds.dbuser}”)
private String dbUserName;

@Value(“${ds.dbpass}”)
private String dbPassword;

@Value(“${ds.dburl}”)
private String dbUrl;

@Value(“${ds.dbtype}”)
private String dbType;

@Bean(name = “dataSource”)
public DataSource dataSource() {
SimpleDriverDataSource dataSource = new SimpleDriverDataSource();
try {
if (dbType.equals(“MYSQL”)) {
dataSource.setDriver(com.mysql.jdbc.Driver.class.newInstance());
} else if(dbType.equals(“H2”)){
dataSource.setDriver(org.h2.Driver.class.newInstance());
}
dataSource.setUrl(dbUrl);
dataSource.setUsername(dbUserName);
dataSource.setPassword(dbPassword);
} catch (InstantiationException | IllegalAccessException e) {
LOGGER.info("Exception: {} ", e);
}
return dataSource;
}

@Bean(name = “transactionManager”)
public DataSourceTransactionManager transactionManager() {
return new DataSourceTransactionManager(dataSource());
}

}

Note: Leave the password field as empty

JVM ARGS:
-Dds.dbtype=H2 -Dds.dbuser=root -Dds.dbpass=
-D ds.dburl =“jdbc:h2:file:~/usr/target/db/camundadb;DB_CLOSE_ON_EXIT=FALSE;DB_CLOSE_DELAY=-1;”

4

Make sure to get the table names right.

5

The query errors because you have connected to an in-memory database that gets created when you first connect to it (in STEP 2). At that point, you have an empty database, into which you first have to load the table definitions for Camunda (using the scripts shipped with the distribution). Look at the H2 documentation (or Stackoverflow etc) to find out how to run scripts against the H2 db

6

If you have configured database settings properly, camunda will create all tables by default