Java操作SQLite数据库

sqlite-jdbc 仓库地址：

https://bitbucket.org/xerial/sqlite-jdbc

sqlite-jdbc.jar

1、下载地址

https://bitbucket.org/xerial/sqlite-jdbc/downloads/

2、示例代码

package com.mouday;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class App 
{ public static void main( String[] args ) throws ClassNotFoundException, SQLException { Class.forName("org.sqlite.JDBC"); Connection connection = DriverManager.getConnection("jdbc:sqlite:test.db"); System.out.println( "Hello World!" ); }
}
  
 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
 

3、运行测试

javac com.mouday.App

# Mac or Linux
java  -classpath ".:sqlite-jdbc-3.7.2.jar" com.mouday.App

# Windows
java  -classpath ".;sqlite-jdbc-3.7.2.jar" com.mouday.App

  
 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
 

使用Maven

依赖

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

  
 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
 

SQLite的存储类

1. NULL
2. INTEGER 带符号的整数
3. REAL 浮点值
4. TEXT 文本字符串
5. BLOB

定义数据结构

package com.mouday;

public class Person { private int id; private String name; public Person(int id, String name) { this.id = id; this.name = name; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; }
}
  
 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
  
17
  
18
  
19
  
20
  
21
  
22
  
23
  
24
  
25
  
26
  
27
  
28
 

CURD

package com.mouday;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class App { public static void main(String[] args) throws ClassNotFoundException { // 加载驱动 Class.forName("org.sqlite.JDBC"); Connection connection = null; try { // 获取连接 connection = DriverManager.getConnection("jdbc:sqlite:test.db"); Statement statement = connection.createStatement(); // 建表 statement.executeUpdate("drop table if exists person"); statement.executeUpdate("create table person (id integer , name text)"); // 插入数据 statement.executeUpdate("insert into person(id, name) values(1, '刘备')"); statement.executeUpdate("insert into person(id, name) values(2, '关羽')"); statement.executeUpdate("insert into person(id, name) values(3, '张飞')"); // 更新数据 statement.executeUpdate("update person set name='诸葛亮' where id = 2"); // 删除数据 statement.executeUpdate("delete from person where id = 3"); // 批处理 String  sql = "insert into person(id, name) values(?, ?)"; PreparedStatement preparedStatement = connection.prepareStatement(sql); List<Person> list = new ArrayList<>(); list.add(new Person(4, "Tom")); list.add(new Person(5, "曹操")); list.add(new Person(6, "刘备")); for(Person person: list){ preparedStatement.setInt(1, person.getId()); preparedStatement.setString(2, person.getName()); preparedStatement.addBatch(); } preparedStatement.executeBatch(); preparedStatement.close(); // 查询数据 ResultSet resultSet = statement.executeQuery("select * from person"); while (resultSet.next()){ System.out.println(resultSet.getInt("id")); System.out.println(resultSet.getString("name")); } statement.close(); } catch (SQLException e) { e.printStackTrace(); } finally { try{ if( connection != null){ connection.close(); } } catch (SQLException e) { e.printStackTrace(); } } }
}
  
 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
  
17
  
18
  
19
  
20
  
21
  
22
  
23
  
24
  
25
  
26
  
27
  
28
  
29
  
30
  
31
  
32
  
33
  
34
  
35
  
36
  
37
  
38
  
39
  
40
  
41
  
42
  
43
  
44
  
45
  
46
  
47
  
48
  
49
  
50
  
51
  
52
  
53
  
54
  
55
  
56
  
57
  
58
  
59
  
60
  
61
  
62
  
63
  
64
  
65
  
66
  
67
  
68
  
69
  
70
  
71
  
72
  
73
  
74
  
75
  
76
 

文章来源: pengshiyu.blog.csdn.net，作者：彭世瑜，版权归原作者所有，如需转载，请联系作者。

原文链接：pengshiyu.blog.csdn.net/article/details/107305821