JDBC tests : [PostgreSQL / MariaDB : working] and [Firebird / SQLite : error]_问答_开发者

I want to compare times of execution of a SQL script. Here it's my code :

import java.sql.*;
import java.io.BufferedReader;
import java.io.FileReader;

public class TempsRequete {
  public static void main(String[] argv) {

  String adresseScript = new String("C:/PTUT/ScriptsSQL/test.sql");

  // Enregistrement driver PostgreSQL
  System.out.println("Test driver PostgreSQL");
  try {
    Class.forName("org.postgresql.Driver");
  } catch (ClassNotFoundException cnfe) {
    System.out.println("Driver introuvable !");
    cnfe.printStackTrace();
    System.exit(1);
  }
  System.out.println("-> Driver PostgreSQL : OK");

  // Enregistrement driver Firebird
  System.out.println("Test driver Firebird");
  try {
    Class.forName("org.firebirdsql.jdbc.FBDriver");
  } catch (ClassNotFoundException cnfe) {
    System.out.println("Driver introuvable !");
    cnfe.printStackTrace();
    System.exit(1);
  }
  System.out.println("-> Driver Firebird : OK");

  // Enregistrement driver MariaDB
  System.out.println("Test driver MariaDB");
  try {
    Class.forName("com.mysql.jdbc.Driver");
  } catch (ClassNotFoundException cnfe) {
    System.out.println("Driver introuvable !");
    cnfe.printStackTrace();
    System.exit(1);
  }
  System.out.println("-> Driver MariaDB : OK");

  // Enregistrement driver SQLite
  System.out.println("Test driver SQLite");
  try {
    Class.forName("org.sqlite.JDBC");
  } catch (ClassNotFoundException cnfe) {
    System.out.println("Driver introuvable !");
    cnfe.printStackTrace();
    System.exit(1);
  }
  System.out.println("-> Driver SQLite : OK");



  // Connexion PostgreSQL
  System.out.println("Connexion PostgreSQL");
  Connection c1 = null;
  try {
    c1 = DriverManager.getConnection("jdbc:postgresql://localhost:5432/bdd_postgresql", "ptut_postgresql", "PassPTUT");
  } catch (SQLException se) {
    System.out.println("Connexion impossible.");
    se.printStackTrace();
    System.exit(1);
  }
  System.out.println("-> Connexion PostgreSQL réussie !");

  // Connexion MariaDB
  System.out.println("Connexion MariaDB");
  Connection c2 = null;
  try {
    c2 = DriverManager.getConnection("jdbc:mysql://localhost:3306/BDD_MariaDB", "PTUT_MariaDB", "PassPTUT");
  } catch (SQLException se) {
    System.out.println("Connexion impossible.");
    se.printStackTrace();
    System.exit(1);
  }
  System.out.println("-> Connexion MariaDB réussie !");

  // Connexion Firebird
  System.out.println("Connexion Firebird");
  Connection c3 = null;
  try {
    c3 = DriverManager.getConnection("jdbc:firebirdsql:localhost/3050:C:/PTUT/BDD/Firebird/BDD_Firebird.db", "PTUT_Firebird", "PassPTUT");
  } catch (SQLException se) {
    System.out.println("Connexion impossible.");
    se.printStackTrace();
    System.exit(1);
  }
  System.out.println("-> Connexion Firebird réussie !");

  // Connexion SQLite
  System.out.println("Connexion SQLite");
  Connection c4 = null;
  try {
    c4 = DriverManager.getConnection("jdbc:sqlite:C:/PTUT/BDD/SQLite/BDD_SQLite.db");
  } catch (SQLException se) {
    System.out.println("Connexion impossible.");
    se.printStackTrace();
    System.exit(1);
  }
  System.out.println("-> Connexion SQLite réussie !");



  // Execution requête PostgreSQL
  System.out.println("\n### Exécution requête PostgreSQL ###");
  ScriptRunner runner1 = new ScriptRunner(c1, false, false);
  long begin1 = System.currentTimeMillis(); //System.out.println("==> Temps de départ : " + begin1);
  try {
    runner1.runScript(new BufferedReader(new FileReader(adresseScript)));
  } catch(Exception e) {
    e.printStackTrace();
  }
  long end1 = System.currentTimeMillis(); //System.out.println("==> Temps d'arrivée : " + end1);

  // Execution requête MariaDB
  System.out.println("\n### Exécution requête MariaDB ###");
  ScriptRunner runner2 = new ScriptRunner(c2, false, false);
  long begin2 = System.currentTimeMillis(); //System.out.println("==> Temps de départ : " + begin2);
  try {
    runner2.runScript(new BufferedReader(new FileReader(adresseScript)));
  } catch(Exception e) {
    e.printStackTrace();
  }
  long end2 = System.currentTimeMillis(); //System.out.println("==> Temps d'arrivée : " + end2);

  // Execution requête Firebird
  System.out.println("\n### Exécution requête Firebird ###");
  ScriptRunner runner3 = new ScriptRunner(c3, false, false);
  long begin3 = System.currentTimeMillis(); //System.out.println("==> Temps de départ : " + begin3);
  try {
    runner3.runScript(new BufferedReader(new FileReader(adresseScript)));
  } catch(Exception e) {
    e.printStackTrace();
  }
  long end3 = System.currentTimeMillis(); //System.out.println("==> Temps d'arrivée : " + end3);

  // Execution requête SQLite
  System.out.println("\n### Exécution requête SQLite ###");
  ScriptRunner runner4 = new ScriptRunner(c4, false, false);
  long begin4 = System.currentTimeMillis(); //System.out.println("==> Temps de départ : " + begin4);
  try {
    runner4.runScript(new BufferedReader(new FileReader(adresseScript)));
  } catch(Exception e) {
    e.printStackTrace();
  }
  long end4 = System.currentTimeMillis(); //System.out.println("==> Temps d'arrivée : " + end4);



  // Temps PostgreSQL
  float time1 = ((float) (end1-begin1)) / 1000f;
  System.out.println("\nTemps PostgreSQL :");
  System.out.println("/------------------------------\\");
  System.out.println("| Temps d'exécution : " + time1 + " sec |");
  System.out.println("\\------------------------------/");

  // Temps MariaDB
  float time2 = ((float) (end2-begin2)) / 1000f;
  System.out.println("\nTemps MariaDB :");
  System.out.println("/------------------------------\\");
  System.out.println("| Temps d'exécution : " + time2 + " sec |");
  System.out.println("\\------------------------------/");

  // Temps Firebird
  float time3 = ((float) (end3-begin3)) / 1000f;
  System.out.println("\nTemps Firebird :");
  System.out.println("/------------------------------\\");
  System.out.println("| Temps d'exécution : " + time3 + " sec |");
  System.out.println("\\------------------------------/");

  // Temps SQLite
  float time4 = ((float) (end4-begin4)) / 1000f;
  System.out.println("\nTemps SQLite :");
  System.out.println("/------------------------------\\");
  System.out.println("| Temps d'exécution : " + time4 + " sec |");
  System.out.println("\\------------------------------/");
  }
}

I use the ScriptRunner class too.

C:/PTUT/ScriptsSQL/test.sql:

CREATE TABLE TEST123(a INTEGER, b VARCHAR(50), c VARCHAR(50));
INSERT INTO TEST123 VALUES(1, 'Riri', 'Tic');
INSERT INTO TEST123 VALUES(2, 'Fifi', 'Tac');
INSERT INTO TEST123 VALUES(3, 'Loulou', 'Toc');
SELECT * FROM TEST123;
SELECT * FROM TEST123;

The output :

Test driver PostgreSQL
-> Driver PostgreSQL : OK
Test driver Firebird
->开发者_如何学编程 Driver Firebird : OK
Test driver MariaDB
-> Driver MariaDB : OK
Test driver SQLite
-> Driver SQLite : OK
Connexion PostgreSQL
-> Connexion PostgreSQL réussie !
Connexion MariaDB
-> Connexion MariaDB réussie !
Connexion Firebird
-> Connexion Firebird réussie !
Connexion SQLite
-> Connexion SQLite réussie !

### Exécution requête PostgreSQL ###
CREATE TABLE TEST123(a INTEGER, b VARCHAR(50), c VARCHAR(50)) 
INSERT INTO TEST123 VALUES(1, 'Riri', 'Tic') 
INSERT INTO TEST123 VALUES(2, 'Fifi', 'Tac') 
INSERT INTO TEST123 VALUES(3, 'Loulou', 'Toc') 
SELECT * FROM TEST123 
SELECT * FROM TEST123 

### Exécution requête MariaDB ###
CREATE TABLE TEST123(a INTEGER, b VARCHAR(50), c VARCHAR(50)) 
INSERT INTO TEST123 VALUES(1, 'Riri', 'Tic') 
INSERT INTO TEST123 VALUES(2, 'Fifi', 'Tac') 
INSERT INTO TEST123 VALUES(3, 'Loulou', 'Toc') 
SELECT * FROM TEST123 
SELECT * FROM TEST123 

### Exécution requête Firebird ###
CREATE TABLE TEST123(a INTEGER, b VARCHAR(50), c VARCHAR(50)) 
INSERT INTO TEST123 VALUES(1, 'Riri', 'Tic') 
INSERT INTO TEST123 VALUES(2, 'Fifi', 'Tac') 
INSERT INTO TEST123 VALUES(3, 'Loulou', 'Toc') 
SELECT * FROM TEST123 
SELECT * FROM TEST123 
Error executing: INSERT INTO TEST123 VALUES(1, 'Riri', 'Tic') 
org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -204
Table unknown
TEST123
At line 1, column 13
Error executing: INSERT INTO TEST123 VALUES(2, 'Fifi', 'Tac') 
org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -204
Table unknown
TEST123
At line 1, column 13
Error executing: INSERT INTO TEST123 VALUES(3, 'Loulou', 'Toc') 
org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -204
Table unknown
TEST123
At line 1, column 13
Error executing: SELECT * FROM TEST123 
org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -204
Table unknown
TEST123
At line 1, column 15
Error executing: SELECT * FROM TEST123 
org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -204
Table unknown
TEST123
At line 1, column 15

### Exécution requête SQLite ###
CREATE TABLE TEST123(a INTEGER, b VARCHAR(50), c VARCHAR(50)) 
Error executing: CREATE TABLE TEST123(a INTEGER, b VARCHAR(50), c VARCHAR(50)) 
java.sql.SQLException: no ResultSet available
java.sql.SQLException: no ResultSet available
    at ScriptRunner.runScript(ScriptRunner.java:242)
    at ScriptRunner.runScript(ScriptRunner.java:110)
    at TempsRequete.main(TempsRequete.java:142)

Temps PostgreSQL :
/-------------------------------\
| Temps d'exécution : 0.2 sec |
\-------------------------------/

Temps MariaDB :
/-------------------------------\
| Temps d'exécution : 0.09 sec |
\-------------------------------/

Temps Firebird :
/-------------------------------\
| Temps d'exécution : 0.16 sec |
\-------------------------------/

Temps SQLite :
/-------------------------------\
| Temps d'exécution : 0.05 sec |
\-------------------------------/

Process completed.

I don't get why Firebird returns a "Table unknown". I typed the same lines in isql (Firebird command line) and it works without problem.

And I don't find how to solve the "java.sql.SQLException: no ResultSet available" SQLite problem.

Thanks if you can help me !

You could cut the size of this test down a great deal by writing the code once and simply changing the drivers and connection URLs. Isn't that the point of JDBC?

Sounds like the two failing databases aren't set up like the ones that succeed. I'd check your assumptions.

I think you need to commit the CREATE TABLE for Firebird before you can use it in an INSERT.