登录
当前位置： 首页 > 工具软件 > jujube-jdbc > 使用案例 >

JDBC —— SQLite JDBC使用

上官华池
2023-12-01

首先在项目引入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


 类似资料：

相关阅读

使用JDBC从Android连接到MySQL使用MySQL和JDBC运行.sql脚本如何在JDBC中使用try-with-resources？Java JDBC基本使用方法详解通过JDBC访问访问（使用ODBC？）

相关文章

JDBC是什么？JSF JDBC连接Java8 JDBC改进JDBC存储过程调用JDBC批量处理

相关问答

JDBC使Java崩溃使用SSL的MySQL JDBC加密使用jdbc的googlecloud-sql连接池使用动态where子句Gatling JDBC使用SQL服务器的JDBC ResultSet

相关文档

JDBC API 中文教程Spring JDBC 中文教程TypeScript 使用手册Sanic 使用教程FreeBSD 使用手册
快捷导航： 新手教程 算法原理 架构设计 Java进阶 数据库进阶 大厂专栏 面试经验 编程笔记 编程问答 所有专题 文档资料 工具软件 电子书籍 小牛导航
在线工具： 房贷计算器 个税计算器 Linux命令查询 Json格式化 正则表达式 颜色转换 AES加解密 SHA1加密 MD5加密 毒鸡汤 字数统计 随机密码生成 进制转换 Base64编解码 励志句子
Copyright © 2019-2024 小牛知识库@xnip.cn. All Rights Reserved.