高级农民工(H.F.H)
我思，故我在！(僕なら、僕ができる！)

导航

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

■目録

 

■ソース

①SQLite3

  1 package cn.com.sy;
  2 
  3 import java.sql.Connection;
  4 import java.sql.DriverManager;
  5 import java.sql.ResultSet;
  6 import java.sql.Statement;
  7 
  8 public class SQLiteTest {
  9 
 10     Connection con = null;
 11     Statement stmt = null;
 12 
 13     public SQLiteTest() {
 14 
 15         try {
 16             this.getConnection();
 17             this.dropTbl();
 18             this.createTbl();
 19             this.insertData();
 20             this.selectData();
 21             this.updateData();
 22             this.deleteData();
 23         } catch (Exception e) {
 24             System.err.println(e.getClass().getName() + ": " + e.getMessage());
 25             System.exit(0);
 26         } finally {
 27             try {
 28                 if (con != null && !con.isClosed()) {
 29                     con.close();
 30                     System.out.println("DB Connection is Closed.");
 31                 }
 32             } catch (Exception e2) {
 33                 System.out.println(e2.getMessage());
 34             }
 35         }
 36     }
 37 
 38     public void getConnection() throws Exception {
 39         Class.forName("org.sqlite.JDBC");
 40         con = DriverManager.getConnection("jdbc:sqlite:./db_data/sqlite/test.db");
 41         System.out.println("DB Connectioning.....");
 42     }
 43 
 44     public void dropTbl() {
 45         System.out.println("---------------DROP TABLE---------------------");
 46         try {
 47             stmt = con.createStatement();
 48             String sql = "DROP TABLE COMPANY;";
 49             stmt.executeUpdate(sql);
 50             stmt.close();
 51         } catch (Exception e) {
 52             
 53         }
 54     }
 55 
 56     public void createTbl() throws Exception {
 57         System.out.println("---------------Create TABLE---------------------");
 58         stmt = con.createStatement();
 59         String sql = "";
 60                 sql += "CREATE TABLE COMPANY ";
 61                 sql += "(ID INT PRIMARY KEY     NOT NULL,";
 62                 sql += " NAME           TEXT    NOT NULL, ";
 63                 sql += " AGE            INT     NOT NULL, ";
 64                 sql += " ADDRESS        CHAR(50), ";
 65                 sql += " SALARY         REAL)";
 66 
 67         stmt.executeUpdate(sql);
 68         stmt.close();
 69     }
 70 
 71     public void insertData() throws Exception {
 72         System.out.println("---------------INSERT DATA---------------------");
 73         con.setAutoCommit(false);
 74         stmt = con.createStatement();
 75         String sql = "";
 76         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " + "VALUES (1, 'Paul', 32, 'California', 20000.00 );";
 77         stmt.executeUpdate(sql);
 78 
 79         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " + "VALUES (2, 'Allen', 25, 'Texas', 15000.00 );";
 80         stmt.executeUpdate(sql);
 81 
 82         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " + "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );";
 83         stmt.executeUpdate(sql);
 84 
 85         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " + "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";
 86         stmt.executeUpdate(sql);
 87 
 88         stmt.close();
 89         con.commit();
 90     }
 91 
 92     public void selectData() throws Exception {
 93         System.out.println("---------------SELECT DATA---------------------");
 94         //con.setAutoCommit(false);
 95         stmt = con.createStatement();
 96         ResultSet rs = stmt.executeQuery("SELECT * FROM COMPANY;");
 97         while (rs.next()) {
 98             int id = rs.getInt("id");
 99             String name = rs.getString("name");
100             int age = rs.getInt("age");
101             String address = rs.getString("address");
102             float salary = rs.getFloat("salary");
103             System.out.println("ID = " + id);
104             System.out.println("NAME = " + name);
105             System.out.println("AGE = " + age);
106             System.out.println("ADDRESS = " + address);
107             System.out.println("SALARY = " + salary);
108             System.out.println();
109         }
110 
111         stmt.close();
112         con.commit();
113     }
114 
115     public void updateData() throws Exception {
116         System.out.println("---------------UPDATE DATA---------------------");
117         con.setAutoCommit(false);
118         stmt = con.createStatement();
119         String sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1;";
120         stmt.executeUpdate(sql);
121         con.commit();
122         ResultSet rs = stmt.executeQuery("SELECT * FROM COMPANY;");
123         while (rs.next()) {
124             int id = rs.getInt("id");
125             String name = rs.getString("name");
126             int age = rs.getInt("age");
127             String address = rs.getString("address");
128             float salary = rs.getFloat("salary");
129             System.out.println("ID = " + id);
130             System.out.println("NAME = " + name);
131             System.out.println("AGE = " + age);
132             System.out.println("ADDRESS = " + address);
133             System.out.println("SALARY = " + salary);
134             System.out.println();
135         }
136         rs.close();
137         stmt.close();
138     }
139 
140     public void deleteData() throws Exception {
141         System.out.println("---------------DELETE DATA---------------------");
142         stmt = con.createStatement();
143         String sql = "DELETE from COMPANY where ID=2;";
144         stmt.executeUpdate(sql);
145         con.commit();
146 
147         ResultSet rs = stmt.executeQuery("SELECT * FROM COMPANY;");
148         while (rs.next()) {
149             int id = rs.getInt("id");
150             String name = rs.getString("name");
151             int age = rs.getInt("age");
152             String address = rs.getString("address");
153             float salary = rs.getFloat("salary");
154             System.out.println("ID = " + id);
155             System.out.println("NAME = " + name);
156             System.out.println("AGE = " + age);
157             System.out.println("ADDRESS = " + address);
158             System.out.println("SALARY = " + salary);
159             System.out.println();
160         }
161         rs.close();
162         stmt.close();
163     }
164 
165     public static void main(String[] args) {
166         new SQLiteTest();
167     }
168 
169 }
SQLiteTest.java

②H2

  1 package cn.com.sy;
  2 
  3 import java.sql.Connection;
  4 import java.sql.DriverManager;
  5 import java.sql.ResultSet;
  6 import java.sql.Statement;
  7 
  8 public class H2Test {
  9 
 10     Connection con = null;
 11     Statement stmt = null;
 12     public H2Test() {
 13 
 14         try {
 15             this.getConnection();
 16             this.dropTbl();
 17             this.createTbl();
 18             this.insertData();
 19             this.selectData();
 20             this.updateData();
 21             this.deleteData();
 22         } catch (Exception e) {
 23             System.err.println(e.getClass().getName() + ": " + e.getMessage());
 24             System.exit(0);
 25         } finally {
 26             try {
 27                 if (con != null && !con.isClosed()) {
 28                     con.close();
 29                     System.out.println("DB Connection is Closed.");
 30                 }
 31             } catch (Exception e2) {
 32                 System.out.println(e2.getMessage());
 33             }
 34         }
 35     }
 36 
 37     public void getConnection() throws Exception {
 38         Class.forName("org.h2.Driver");
 39         con = DriverManager.getConnection("jdbc:h2:./db_data/h2/test.db","SA","PASS");
 40         System.out.println("DB Connectioning.....");
 41     }
 42 
 43     public void dropTbl() {
 44         System.out.println("---------------DROP TABLE---------------------");
 45         try {
 46             stmt = con.createStatement();
 47             String sql = "DROP TABLE IF EXISTS COMPANY;";
 48             stmt.executeUpdate(sql);
 49             stmt.close();
 50         } catch (Exception e) {
 51             
 52         }
 53     }
 54 
 55     public void createTbl() throws Exception {
 56         System.out.println("---------------Create TABLE---------------------");
 57         stmt = con.createStatement();
 58         String sql = "";
 59                 sql += "CREATE TABLE COMPANY ";
 60                 sql += "(ID INT PRIMARY KEY     NOT NULL,";
 61                 sql += " NAME           VARCHAR(50)    NOT NULL, ";
 62                 sql += " AGE             INT     NOT NULL, ";
 63                 sql += " ADDRESS      VARCHAR(50), ";
 64                 sql += " SALARY        REAL)";
 65 
 66         stmt.executeUpdate(sql);
 67         stmt.close();
 68     }
 69 
 70     public void insertData() throws Exception {
 71         System.out.println("---------------INSERT DATA---------------------");
 72         con.setAutoCommit(false);
 73         stmt = con.createStatement();
 74         String sql = "";
 75         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " + "VALUES (1, 'Paul', 32, 'California', 20000.00 );";
 76         stmt.executeUpdate(sql);
 77 
 78         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " + "VALUES (2, 'Allen', 25, 'Texas', 15000.00 );";
 79         stmt.executeUpdate(sql);
 80 
 81         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " + "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );";
 82         stmt.executeUpdate(sql);
 83 
 84         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " + "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";
 85         stmt.executeUpdate(sql);
 86 
 87         stmt.close();
 88         con.commit();
 89     }
 90 
 91     public void selectData() throws Exception {
 92         System.out.println("---------------SELECT DATA---------------------");
 93         //con.setAutoCommit(false);
 94         stmt = con.createStatement();
 95         ResultSet rs = stmt.executeQuery("SELECT * FROM COMPANY;");
 96         while (rs.next()) {
 97             int id = rs.getInt("id");
 98             String name = rs.getString("name");
 99             int age = rs.getInt("age");
100             String address = rs.getString("address");
101             float salary = rs.getFloat("salary");
102             System.out.println("ID = " + id);
103             System.out.println("NAME = " + name);
104             System.out.println("AGE = " + age);
105             System.out.println("ADDRESS = " + address);
106             System.out.println("SALARY = " + salary);
107             System.out.println();
108         }
109 
110         stmt.close();
111         con.commit();
112     }
113 
114     public void updateData() throws Exception {
115         System.out.println("---------------UPDATE DATA---------------------");
116         con.setAutoCommit(false);
117         stmt = con.createStatement();
118         String sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1;";
119         stmt.executeUpdate(sql);
120         con.commit();
121         ResultSet rs = stmt.executeQuery("SELECT * FROM COMPANY;");
122         while (rs.next()) {
123             int id = rs.getInt("id");
124             String name = rs.getString("name");
125             int age = rs.getInt("age");
126             String address = rs.getString("address");
127             float salary = rs.getFloat("salary");
128             System.out.println("ID = " + id);
129             System.out.println("NAME = " + name);
130             System.out.println("AGE = " + age);
131             System.out.println("ADDRESS = " + address);
132             System.out.println("SALARY = " + salary);
133             System.out.println();
134         }
135         rs.close();
136         stmt.close();
137     }
138 
139     public void deleteData() throws Exception {
140         System.out.println("---------------DELETE DATA---------------------");
141         stmt = con.createStatement();
142         String sql = "DELETE from COMPANY where ID=2;";
143         stmt.executeUpdate(sql);
144         con.commit();
145 
146         ResultSet rs = stmt.executeQuery("SELECT * FROM COMPANY;");
147         while (rs.next()) {
148             int id = rs.getInt("id");
149             String name = rs.getString("name");
150             int age = rs.getInt("age");
151             String address = rs.getString("address");
152             float salary = rs.getFloat("salary");
153             System.out.println("ID = " + id);
154             System.out.println("NAME = " + name);
155             System.out.println("AGE = " + age);
156             System.out.println("ADDRESS = " + address);
157             System.out.println("SALARY = " + salary);
158             System.out.println();
159         }
160         rs.close();
161         stmt.close();
162     }
163 
164     public static void main(String[] args) {
165         new H2Test();
166     }
167 
168 }
H2Test.java

③HSQLDB

  1 package cn.com.sy;
  2 
  3 import java.sql.Connection;
  4 import java.sql.DriverManager;
  5 import java.sql.ResultSet;
  6 import java.sql.Statement;
  7 
  8 public class HSQLTest2 {
  9 
 10     Connection con = null;
 11     Statement stmt = null;
 12     public HSQLTest2() {
 13 
 14         try {
 15             this.getConnection();
 16             this.dropTbl();
 17             this.createTbl();
 18             this.insertData();
 19             this.selectData();
 20             this.updateData();
 21             this.deleteData();
 22         } catch (Exception e) {
 23             System.err.println(e.getClass().getName() + ": " + e.getMessage());
 24             System.exit(0);
 25         } finally {
 26             try {
 27                 if (con != null && !con.isClosed()) {
 28                     con.close();
 29                     System.out.println("DB Connection is Closed.");
 30                 }
 31             } catch (Exception e2) {
 32                 System.out.println(e2.getMessage());
 33             }
 34         }
 35     }
 36 
 37     public void getConnection() throws Exception {
 38         Class.forName("org.hsqldb.jdbc.JDBCDriver");
 39         con = DriverManager.getConnection("jdbc:hsqldb:./db_data/hsql/test.db","SA","PASS");
 40         System.out.println("DB Connectioning.....");
 41     }
 42 
 43     public void dropTbl() {
 44         System.out.println("---------------DROP TABLE---------------------");
 45         try {
 46             stmt = con.createStatement();
 47             String sql = "DROP TABLE IF EXISTS COMPANY;";
 48             stmt.executeUpdate(sql);
 49             stmt.close();
 50         } catch (Exception e) {
 51             
 52         }
 53     }
 54 
 55     public void createTbl() throws Exception {
 56         System.out.println("---------------Create TABLE---------------------");
 57         stmt = con.createStatement();
 58         String sql = "";
 59                 sql += "CREATE TABLE COMPANY ";
 60                 sql += "(ID INT PRIMARY KEY     NOT NULL,";
 61                 sql += " NAME           VARCHAR(50)    NOT NULL, ";
 62                 sql += " AGE             INT     NOT NULL, ";
 63                 sql += " ADDRESS      VARCHAR(50), ";
 64                 sql += " SALARY        REAL)";
 65 
 66         stmt.executeUpdate(sql);
 67         stmt.close();
 68     }
 69 
 70     public void insertData() throws Exception {
 71         System.out.println("---------------INSERT DATA---------------------");
 72         con.setAutoCommit(false);
 73         stmt = con.createStatement();
 74         String sql = "";
 75         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " + "VALUES (1, 'Paul', 32, 'California', 20000.00 );";
 76         stmt.executeUpdate(sql);
 77 
 78         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " + "VALUES (2, 'Allen', 25, 'Texas', 15000.00 );";
 79         stmt.executeUpdate(sql);
 80 
 81         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " + "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );";
 82         stmt.executeUpdate(sql);
 83 
 84         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " + "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";
 85         stmt.executeUpdate(sql);
 86 
 87         stmt.close();
 88         con.commit();
 89     }
 90 
 91     public void selectData() throws Exception {
 92         System.out.println("---------------SELECT DATA---------------------");
 93         //con.setAutoCommit(false);
 94         stmt = con.createStatement();
 95         ResultSet rs = stmt.executeQuery("SELECT * FROM COMPANY;");
 96         while (rs.next()) {
 97             int id = rs.getInt("id");
 98             String name = rs.getString("name");
 99             int age = rs.getInt("age");
100             String address = rs.getString("address");
101             float salary = rs.getFloat("salary");
102             System.out.println("ID = " + id);
103             System.out.println("NAME = " + name);
104             System.out.println("AGE = " + age);
105             System.out.println("ADDRESS = " + address);
106             System.out.println("SALARY = " + salary);
107             System.out.println();
108         }
109 
110         stmt.close();
111         con.commit();
112     }
113 
114     public void updateData() throws Exception {
115         System.out.println("---------------UPDATE DATA---------------------");
116         con.setAutoCommit(false);
117         stmt = con.createStatement();
118         String sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1;";
119         stmt.executeUpdate(sql);
120         con.commit();
121         ResultSet rs = stmt.executeQuery("SELECT * FROM COMPANY;");
122         while (rs.next()) {
123             int id = rs.getInt("id");
124             String name = rs.getString("name");
125             int age = rs.getInt("age");
126             String address = rs.getString("address");
127             float salary = rs.getFloat("salary");
128             System.out.println("ID = " + id);
129             System.out.println("NAME = " + name);
130             System.out.println("AGE = " + age);
131             System.out.println("ADDRESS = " + address);
132             System.out.println("SALARY = " + salary);
133             System.out.println();
134         }
135         rs.close();
136         stmt.close();
137     }
138 
139     public void deleteData() throws Exception {
140         System.out.println("---------------DELETE DATA---------------------");
141         stmt = con.createStatement();
142         String sql = "DELETE from COMPANY where ID=2;";
143         stmt.executeUpdate(sql);
144         con.commit();
145 
146         ResultSet rs = stmt.executeQuery("SELECT * FROM COMPANY;");
147         while (rs.next()) {
148             int id = rs.getInt("id");
149             String name = rs.getString("name");
150             int age = rs.getInt("age");
151             String address = rs.getString("address");
152             float salary = rs.getFloat("salary");
153             System.out.println("ID = " + id);
154             System.out.println("NAME = " + name);
155             System.out.println("AGE = " + age);
156             System.out.println("ADDRESS = " + address);
157             System.out.println("SALARY = " + salary);
158             System.out.println();
159         }
160         rs.close();
161         stmt.close();
162     }
163 
164     public static void main(String[] args) {
165         new HSQLTest2();
166     }
167 
168 }
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>

■ 補足（2021-12-08）

package demo.h2;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Enumeration;

public class DBConnection {

    static {
        try {
            // 方法一
            Class.forName("org.h2.Driver");
            // 方法二（实例化2次）
            // Driver driver = (Driver)Class.forName("org.h2.Driver").newInstance();
            // DriverManager.registerDriver(driver);
            // 方法三
            // org.h2.Driver.load();
            // 方法四
            // DriverManager.registerDriver(org.h2.Driver.load());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public DBConnection() {
        try {
            Connection conn = getConn();
            if (conn != null) {
                Statement stmt = getStmt(conn);
                String sql = "select * from test";
                ResultSet rs = executeQuery(stmt, sql);
                while (rs.next()) {
                    // 获取数据
                    System.out.println("Data:" + rs.getString(1) + " " + rs.getString(2));
                }

                ResultSetMetaData rsmd = rs.getMetaData();
                int numberOfColumns = rsmd.getColumnCount();
                for (int i = 1; i <= numberOfColumns; i++) {
                    // 查看表中字段情况
                    System.out.println(
                            rsmd.getColumnName(i) + " " + rsmd.getColumnTypeName(i) + "(" + rsmd.getColumnType(i) + ") "
                                    + rsmd.getColumnDisplaySize(i) + " " + rsmd.getColumnLabel(i));
                }

                // 关闭数据库
                closeRs(rs);
                closeStmt(stmt);
                closeConn(conn);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 查看数据库驱动
            Enumeration<Driver> drivers = DriverManager.getDrivers();
            int nums = 0;
            while (drivers.hasMoreElements()) {
                nums++;
                System.out.println(drivers.nextElement());
            }
            System.out.println("Dirver Nums:" + nums);
        }
    }

    /**
     * 获取JDBC连接
     * 
     * @return
     */
    private static Connection getConn() {
        Connection conn = null;
        try {
            String url = "jdbc:h2:tcp://localhost/~/sj";
            String user = "sysj";
            String pwds = "sysj";
            conn = DriverManager.getConnection(url, user, pwds);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 获取Statement
     * 
     * @param conn
     * @return
     * @throws SQLException
     */
    private static Statement getStmt(Connection conn) throws SQLException {
        Statement stmt = null;
        try {
            stmt = conn.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }
        return stmt;
    }

    /**
     * 获取PreparedStatement
     * 
     * @param conn
     * @param sql
     * @return
     * @throws SQLException
     */
    private static PreparedStatement getPStmt(Connection conn, String sql) throws SQLException {
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }
        return pstmt;
    }

    /**
     * 获取查询结果
     * 
     * @param stmt
     * @param sql
     * @return
     * @throws SQLException
     */
    private static ResultSet executeQuery(Statement stmt, String sql) throws SQLException {
        ResultSet rs = null;
        try {
            rs = stmt.executeQuery(sql);
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }
        return rs;
    }

    /**
     * 关闭连接
     * 
     * @param conn
     * @throws SQLException
     */
    private static void closeConn(Connection conn) throws SQLException {
        try {
            if (conn != null) {
                conn.close();
                conn = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }
    }

    /**
     * 关闭Statement
     * 
     * @param stmt
     * @throws SQLException
     */
    private static void closeStmt(Statement stmt) throws SQLException {
        try {
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }
    }

    /**
     * 关闭ResultSet
     * 
     * @param rs
     * @throws SQLException
     */
    private static void closeRs(ResultSet rs) throws SQLException {
        try {
            if (rs != null) {
                rs.close();
                rs = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }
    }

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

}
JDBCーH2

 

posted on 2019-01-21 06:51  农民工024  阅读(184)  评论(0)  编辑  收藏  举报
 
Powered by:
博客园
Copyright © 2024 农民工024
Powered by .NET 8.0 on Kubernetes