【JAVA】JAVAで各DBに接続する方法(JDBC)の纏め（未完結）

■目録

■ソース

①SQLite3

 package cn.com.sy;

 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.ResultSet;
 import java.sql.Statement;

 public class SQLiteTest {

     Connection con = null;
     Statement stmt = null;

     public SQLiteTest() {

         try {
             this.getConnection();
             this.dropTbl();
             this.createTbl();
             this.insertData();
             this.selectData();
             this.updateData();
             this.deleteData();
         } catch (Exception e) {
             System.err.println(e.getClass().getName() + ": " + e.getMessage());
             System.exit(0);
         } finally {
             try {
                 if (con != null && !con.isClosed()) {
                     con.close();
                     System.out.println("DB Connection is Closed.");
                 }
             } catch (Exception e2) {
                 System.out.println(e2.getMessage());
             }
         }
     }

     public void getConnection() throws Exception {
         Class.forName("org.sqlite.JDBC");
         con = DriverManager.getConnection("jdbc:sqlite:./db_data/sqlite/test.db");
         System.out.println("DB Connectioning.....");
     }

     public void dropTbl() {
         System.out.println("---------------DROP TABLE---------------------");
         try {
             stmt = con.createStatement();
             String sql = "DROP TABLE COMPANY;";
             stmt.executeUpdate(sql);
             stmt.close();
         } catch (Exception e) {

         }
     }

     public void createTbl() throws Exception {
         System.out.println("---------------Create TABLE---------------------");
         stmt = con.createStatement();
         String sql = "";
                 sql += "CREATE TABLE COMPANY ";
                 sql += "(ID INT PRIMARY KEY     NOT NULL,";
                 sql += " NAME           TEXT    NOT NULL, ";
                 sql += " AGE            INT     NOT NULL, ";
                 sql += " ADDRESS        CHAR(50), ";
                 sql += " SALARY         REAL)";

         stmt.executeUpdate(sql);
         stmt.close();
     }

     public void insertData() throws Exception {
         System.out.println("---------------INSERT DATA---------------------");
         con.setAutoCommit(false);
         stmt = con.createStatement();
         String sql = "";
         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " + "VALUES (1, 'Paul', 32, 'California', 20000.00 );";
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " + "VALUES (2, 'Allen', 25, 'Texas', 15000.00 );";
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " + "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );";
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " + "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";
         stmt.executeUpdate(sql);

         stmt.close();
         con.commit();
     }

     public void selectData() throws Exception {
         System.out.println("---------------SELECT DATA---------------------");
         //con.setAutoCommit(false);
         stmt = con.createStatement();
         ResultSet rs = stmt.executeQuery("SELECT * FROM COMPANY;");
         while (rs.next()) {
             int id = rs.getInt("id");
             String name = rs.getString("name");
             int age = rs.getInt("age");
             String address = rs.getString("address");
             float salary = rs.getFloat("salary");
             System.out.println("ID = " + id);
             System.out.println("NAME = " + name);
             System.out.println("AGE = " + age);
             System.out.println("ADDRESS = " + address);
             System.out.println("SALARY = " + salary);
             System.out.println();
         }

         stmt.close();
         con.commit();
     }

     public void updateData() throws Exception {
         System.out.println("---------------UPDATE DATA---------------------");
         con.setAutoCommit(false);
         stmt = con.createStatement();
         String sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1;";
         stmt.executeUpdate(sql);
         con.commit();
         ResultSet rs = stmt.executeQuery("SELECT * FROM COMPANY;");
         while (rs.next()) {
             int id = rs.getInt("id");
             String name = rs.getString("name");
             int age = rs.getInt("age");
             String address = rs.getString("address");
             float salary = rs.getFloat("salary");
             System.out.println("ID = " + id);
             System.out.println("NAME = " + name);
             System.out.println("AGE = " + age);
             System.out.println("ADDRESS = " + address);
             System.out.println("SALARY = " + salary);
             System.out.println();
         }
         rs.close();
         stmt.close();
     }

     public void deleteData() throws Exception {
         System.out.println("---------------DELETE DATA---------------------");
         stmt = con.createStatement();
         String sql = "DELETE from COMPANY where ID=2;";
         stmt.executeUpdate(sql);
         con.commit();

         ResultSet rs = stmt.executeQuery("SELECT * FROM COMPANY;");
         while (rs.next()) {
             int id = rs.getInt("id");
             String name = rs.getString("name");
             int age = rs.getInt("age");
             String address = rs.getString("address");
             float salary = rs.getFloat("salary");
             System.out.println("ID = " + id);
             System.out.println("NAME = " + name);
             System.out.println("AGE = " + age);
             System.out.println("ADDRESS = " + address);
             System.out.println("SALARY = " + salary);
             System.out.println();
         }
         rs.close();
         stmt.close();
     }

     public static void main(String[] args) {
         new SQLiteTest();
     }

 }

SQLiteTest.java

②H2

 package cn.com.sy;

 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.ResultSet;
 import java.sql.Statement;

 public class H2Test {

     Connection con = null;
     Statement stmt = null;
     public H2Test() {

         try {
             this.getConnection();
             this.dropTbl();
             this.createTbl();
             this.insertData();
             this.selectData();
             this.updateData();
             this.deleteData();
         } catch (Exception e) {
             System.err.println(e.getClass().getName() + ": " + e.getMessage());
             System.exit(0);
         } finally {
             try {
                 if (con != null && !con.isClosed()) {
                     con.close();
                     System.out.println("DB Connection is Closed.");
                 }
             } catch (Exception e2) {
                 System.out.println(e2.getMessage());
             }
         }
     }

     public void getConnection() throws Exception {
         Class.forName("org.h2.Driver");
         con = DriverManager.getConnection("jdbc:h2:./db_data/h2/test.db","SA","PASS");
         System.out.println("DB Connectioning.....");
     }

     public void dropTbl() {
         System.out.println("---------------DROP TABLE---------------------");
         try {
             stmt = con.createStatement();
             String sql = "DROP TABLE IF EXISTS COMPANY;";
             stmt.executeUpdate(sql);
             stmt.close();
         } catch (Exception e) {

         }
     }

     public void createTbl() throws Exception {
         System.out.println("---------------Create TABLE---------------------");
         stmt = con.createStatement();
         String sql = "";
                 sql += "CREATE TABLE COMPANY ";
                 sql += "(ID INT PRIMARY KEY     NOT NULL,";
                 sql += " NAME           VARCHAR(50)    NOT NULL, ";
                 sql += " AGE             INT     NOT NULL, ";
                 sql += " ADDRESS      VARCHAR(50), ";
                 sql += " SALARY        REAL)";

         stmt.executeUpdate(sql);
         stmt.close();
     }

     public void insertData() throws Exception {
         System.out.println("---------------INSERT DATA---------------------");
         con.setAutoCommit(false);
         stmt = con.createStatement();
         String sql = "";
         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " + "VALUES (1, 'Paul', 32, 'California', 20000.00 );";
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " + "VALUES (2, 'Allen', 25, 'Texas', 15000.00 );";
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " + "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );";
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " + "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";
         stmt.executeUpdate(sql);

         stmt.close();
         con.commit();
     }

     public void selectData() throws Exception {
         System.out.println("---------------SELECT DATA---------------------");
         //con.setAutoCommit(false);
         stmt = con.createStatement();
         ResultSet rs = stmt.executeQuery("SELECT * FROM COMPANY;");
         while (rs.next()) {
             int id = rs.getInt("id");
             String name = rs.getString("name");
             int age = rs.getInt("age");
             String address = rs.getString("address");
             float salary = rs.getFloat("salary");
             System.out.println("ID = " + id);
             System.out.println("NAME = " + name);
             System.out.println("AGE = " + age);
             System.out.println("ADDRESS = " + address);
             System.out.println("SALARY = " + salary);
             System.out.println();
         }

         stmt.close();
         con.commit();
     }

     public void updateData() throws Exception {
         System.out.println("---------------UPDATE DATA---------------------");
         con.setAutoCommit(false);
         stmt = con.createStatement();
         String sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1;";
         stmt.executeUpdate(sql);
         con.commit();
         ResultSet rs = stmt.executeQuery("SELECT * FROM COMPANY;");
         while (rs.next()) {
             int id = rs.getInt("id");
             String name = rs.getString("name");
             int age = rs.getInt("age");
             String address = rs.getString("address");
             float salary = rs.getFloat("salary");
             System.out.println("ID = " + id);
             System.out.println("NAME = " + name);
             System.out.println("AGE = " + age);
             System.out.println("ADDRESS = " + address);
             System.out.println("SALARY = " + salary);
             System.out.println();
         }
         rs.close();
         stmt.close();
     }

     public void deleteData() throws Exception {
         System.out.println("---------------DELETE DATA---------------------");
         stmt = con.createStatement();
         String sql = "DELETE from COMPANY where ID=2;";
         stmt.executeUpdate(sql);
         con.commit();

         ResultSet rs = stmt.executeQuery("SELECT * FROM COMPANY;");
         while (rs.next()) {
             int id = rs.getInt("id");
             String name = rs.getString("name");
             int age = rs.getInt("age");
             String address = rs.getString("address");
             float salary = rs.getFloat("salary");
             System.out.println("ID = " + id);
             System.out.println("NAME = " + name);
             System.out.println("AGE = " + age);
             System.out.println("ADDRESS = " + address);
             System.out.println("SALARY = " + salary);
             System.out.println();
         }
         rs.close();
         stmt.close();
     }

     public static void main(String[] args) {
         new H2Test();
     }

 }

H2Test.java

③HSQLDB

 package cn.com.sy;

 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.ResultSet;
 import java.sql.Statement;

 public class HSQLTest2 {

     Connection con = null;
     Statement stmt = null;
     public HSQLTest2() {

         try {
             this.getConnection();
             this.dropTbl();
             this.createTbl();
             this.insertData();
             this.selectData();
             this.updateData();
             this.deleteData();
         } catch (Exception e) {
             System.err.println(e.getClass().getName() + ": " + e.getMessage());
             System.exit(0);
         } finally {
             try {
                 if (con != null && !con.isClosed()) {
                     con.close();
                     System.out.println("DB Connection is Closed.");
                 }
             } catch (Exception e2) {
                 System.out.println(e2.getMessage());
             }
         }
     }

     public void getConnection() throws Exception {
         Class.forName("org.hsqldb.jdbc.JDBCDriver");
         con = DriverManager.getConnection("jdbc:hsqldb:./db_data/hsql/test.db","SA","PASS");
         System.out.println("DB Connectioning.....");
     }

     public void dropTbl() {
         System.out.println("---------------DROP TABLE---------------------");
         try {
             stmt = con.createStatement();
             String sql = "DROP TABLE IF EXISTS COMPANY;";
             stmt.executeUpdate(sql);
             stmt.close();
         } catch (Exception e) {

         }
     }

     public void createTbl() throws Exception {
         System.out.println("---------------Create TABLE---------------------");
         stmt = con.createStatement();
         String sql = "";
                 sql += "CREATE TABLE COMPANY ";
                 sql += "(ID INT PRIMARY KEY     NOT NULL,";
                 sql += " NAME           VARCHAR(50)    NOT NULL, ";
                 sql += " AGE             INT     NOT NULL, ";
                 sql += " ADDRESS      VARCHAR(50), ";
                 sql += " SALARY        REAL)";

         stmt.executeUpdate(sql);
         stmt.close();
     }

     public void insertData() throws Exception {
         System.out.println("---------------INSERT DATA---------------------");
         con.setAutoCommit(false);
         stmt = con.createStatement();
         String sql = "";
         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " + "VALUES (1, 'Paul', 32, 'California', 20000.00 );";
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " + "VALUES (2, 'Allen', 25, 'Texas', 15000.00 );";
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " + "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );";
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " + "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";
         stmt.executeUpdate(sql);

         stmt.close();
         con.commit();
     }

     public void selectData() throws Exception {
         System.out.println("---------------SELECT DATA---------------------");
         //con.setAutoCommit(false);
         stmt = con.createStatement();
         ResultSet rs = stmt.executeQuery("SELECT * FROM COMPANY;");
         while (rs.next()) {
             int id = rs.getInt("id");
             String name = rs.getString("name");
             int age = rs.getInt("age");
             String address = rs.getString("address");
             float salary = rs.getFloat("salary");
             System.out.println("ID = " + id);
             System.out.println("NAME = " + name);
             System.out.println("AGE = " + age);
             System.out.println("ADDRESS = " + address);
             System.out.println("SALARY = " + salary);
             System.out.println();
         }

         stmt.close();
         con.commit();
     }

     public void updateData() throws Exception {
         System.out.println("---------------UPDATE DATA---------------------");
         con.setAutoCommit(false);
         stmt = con.createStatement();
         String sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1;";
         stmt.executeUpdate(sql);
         con.commit();
         ResultSet rs = stmt.executeQuery("SELECT * FROM COMPANY;");
         while (rs.next()) {
             int id = rs.getInt("id");
             String name = rs.getString("name");
             int age = rs.getInt("age");
             String address = rs.getString("address");
             float salary = rs.getFloat("salary");
             System.out.println("ID = " + id);
             System.out.println("NAME = " + name);
             System.out.println("AGE = " + age);
             System.out.println("ADDRESS = " + address);
             System.out.println("SALARY = " + salary);
             System.out.println();
         }
         rs.close();
         stmt.close();
     }

     public void deleteData() throws Exception {
         System.out.println("---------------DELETE DATA---------------------");
         stmt = con.createStatement();
         String sql = "DELETE from COMPANY where ID=2;";
         stmt.executeUpdate(sql);
         con.commit();

         ResultSet rs = stmt.executeQuery("SELECT * FROM COMPANY;");
         while (rs.next()) {
             int id = rs.getInt("id");
             String name = rs.getString("name");
             int age = rs.getInt("age");
             String address = rs.getString("address");
             float salary = rs.getFloat("salary");
             System.out.println("ID = " + id);
             System.out.println("NAME = " + name);
             System.out.println("AGE = " + age);
             System.out.println("ADDRESS = " + address);
             System.out.println("SALARY = " + salary);
             System.out.println();
         }
         rs.close();
         stmt.close();
     }

     public static void main(String[] args) {
         new HSQLTest2();
     }

 }

HSQLTest2

※補足（Maven Repository）※

参考URL：https://mvnrepository.com/

【H2】

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>1.4.197</version>
    <scope>test</scope>
</dependency>

【HSQLDB】

<dependency>
    <groupId>org.hsqldb</groupId>
    <artifactId>hsqldb</artifactId>
    <version>2.4.1</version>
    <scope>test</scope>
</dependency>

【SQLite】

<dependency>
    <groupId>org.xerial</groupId>
    <artifactId>sqlite-jdbc</artifactId>
    <version>3.25.2</version>
</dependency>