JDBC —— SQLite JDBC使用

jydg

首先在项目引入JAR：sqlite-jdbc-(VERSION).jar 连接到数据库（Connecting To Database） 创建一个表（Create a Table） 插入操作（INSERT Operation） 查询操作（SELECT Operation） 更新操作（UPDATE Operation） 删除操作（DELETE Operation）

连接到数据库（Connecting To Database）

下面的代码演示了如何连接到一个现有的数据库。如果数据库不存在，那么它会被自动创建，最终返回一个数据库连接实例。

public class SQLiteJDBC {   public static void main(String args[]) {   Connection c = null;   try {   Class.forName("org.sqlite.JDBC");   c = DriverManager.getConnection("jdbc:sqlite:test.db");   c.close();   } catch (Exception e) {   System.err.println(e.getClass().getName() + ": " + e.getMessage());   System.exit(0);   }   System.out.println("Opened database successfully");   }   }

创建一个表（Create a Table）

下面的Java程序用于在先前创建的数据库中创建一个表：

public class SQLiteJDBC {   public static void main(String args[]) {   Connection c = null;   Statement stmt = null;   try {   Class.forName("org.sqlite.JDBC");   c = DriverManager.getConnection("jdbc:sqlite:test.db");   System.out.println("Opened database successfully");   stmt = c.createStatement();   String sql = "CREATE TABLE COMPANY "   + "(ID INT PRIMARY KEY     NOT NULL        ,"   + " NAME           TEXT    NOT NULL        ,"   + " AGE            INT     NOT NULL        ,"   + " ADDRESS        CHAR(50)                ,"    + " SALARY         REAL)                ";   stmt.executeUpdate(sql);   stmt.close();   c.close();   } catch (Exception e) {   System.err.println(e.getClass().getName() + ": " + e.getMessage());   System.exit(0);   }   System.out.println("Table created successfully");    }   }

插入操作（INSERT Operation）

下面的Java程序演示了如何在COMPANY表中插入一些记录：

public class SQLiteJDBC {   public static void main(String args[]) {   Connection c = null;   Statement stmt = null;   try {   Class.forName("org.sqlite.JDBC");   c = DriverManager.getConnection("jdbc:sqlite:test.db");   c.setAutoCommit(false);   System.out.println("Opened database successfully");   stmt = c.createStatement();   String 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();   c.commit();   c.close();    } catch (Exception e) {   System.err.println(e.getClass().getName() + ": " + e.getMessage());   System.exit(0);   }   System.out.println("Records created successfully");   }   }    查询操作（SELECT Operation） 下面的Java程序演示了如何能够获取并显示在上面例子中插入的记录：     public class SQLiteJDBC {   public static void main(String args[]) {   Connection c = null;   Statement stmt = null;   try {   Class.forName("org.sqlite.JDBC");   c = DriverManager.getConnection("jdbc:sqlite:test.db");   c.setAutoCommit(false);   System.out.println("Opened database successfully");   stmt = c.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();   }   rs.close();   stmt.close();   c.close();   } catch (Exception e) {   System.err.println(e.getClass().getName() + ": " + e.getMessage());   System.exit(0);   }   System.out.println("Operation done successfully");   }   }     编译并运行后，在控制台的输出： Opened database successfully ID = 1 NAME = Paul AGE = 32 ADDRESS = California SALARY = 20000.0   ID = 2 NAME = Allen AGE = 25 ADDRESS = Texas SALARY = 15000.0   ID = 3 NAME = Teddy AGE = 23 ADDRESS = Norway SALARY = 20000.0   ID = 4 NAME = Mark AGE = 25 ADDRESS = Rich-Mond SALARY = 65000.0   Operation done successfully

更新操作（UPDATE Operation）

下面的Java代码演示了如何使用UPDATE语句来从本公司表更新任何记录，然后获取并显示更新的记录：

public class SQLiteJDBC {   public static void main(String args[]) {   Connection c = null;   Statement stmt = null;   try {   Class.forName("org.sqlite.JDBC");   c = DriverManager.getConnection("jdbc:sqlite:test.db");   c.setAutoCommit(false);   System.out.println("Opened database successfully");   stmt = c.createStatement();   String sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1;";   stmt.executeUpdate(sql);   c.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();   c.close();   } catch (Exception e) {   System.err.println(e.getClass().getName() + ": " + e.getMessage());   System.exit(0);   }   System.out.println("Operation done successfully");   }   }

编译并运行后，在控制台的输出：

Opened database successfully ID = 1 NAME = Paul AGE = 32 ADDRESS = California SALARY = 25000.0   ID = 2 NAME = Allen AGE = 25 ADDRESS = Texas SALARY = 15000.0   ID = 3 NAME = Teddy AGE = 23 ADDRESS = Norway SALARY = 20000.0   ID = 4 NAME = Mark AGE = 25 ADDRESS = Rich-Mond SALARY = 65000.0   Operation done successfully

删除操作（DELETE Operation）

下面的Java代码显示了如何使用DELETE语句删除任何记录，然后获取并显示公司表中未被删除的记录：

public class SQLiteJDBC {   public static void main(String args[]) {   Connection c = null;   Statement stmt = null;   try {   Class.forName("org.sqlite.JDBC");   c = DriverManager.getConnection("jdbc:sqlite:test.db");   c.setAutoCommit(false);   System.out.println("Opened database successfully");   stmt = c.createStatement();   String sql = "DELETE from COMPANY where ID=2;";   stmt.executeUpdate(sql);   c.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();   c.close();   } catch (Exception e) {   System.err.println(e.getClass().getName() + ": " + e.getMessage());   System.exit(0);   }   System.out.println("Operation done successfully");   }   }

编译并运行后，在控制台的输出：

Opened database successfully ID = 1 NAME = Paul AGE = 32 ADDRESS = California SALARY = 25000.0   ID = 3 NAME = Teddy AGE = 23 ADDRESS = Norway SALARY = 20000.0   ID = 4 NAME = Mark AGE = 25 ADDRESS = Rich-Mond SALARY = 65000.0   Operation done successfully