【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>
1、asp.net MVC 错误信息“没有为该对象定义无参数的构造函数”请求各位大神帮忙！
2、python读取.edf文件
3、java读取property文件
4、使用码云【转】
5、mybatis（二）接口编程 、动态sql 、批量删除 、动态更新、连表查询