Java操作Sqlite数据库1.介绍2.特点3.Java操作Sqlite

121 阅读 0 评论 80 点赞

我是靠谱客的博主负责保温杯，这篇文章主要介绍Java操作Sqlite数据库1.介绍2.特点3.Java操作Sqlite，现在分享给大家，希望可以做个参考。

1.介绍
2.特点
3.Java操作Sqlite

1.介绍

SQLite是一个进程内的库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。它是一个零配置的数据库，这意味着与其它数据库不同，不需要在系统中配置。
就像其它数据库，SQLite引擎不是一个独立的进程，可以按应用程序需求进行静态或动态连接。SQLite直接访问其存储文件。

2.特点

不需要一个单独的服务器进程或操作的系统（无服务器的）。
SQLite不需要配置，这意味着不需要安装或管理。
一个完整的SQLite数据库是存储在一个单一的跨平台的磁盘文件。
SQLite是非常小的，是轻量级的，完全配置时小于 400KiB，省略可选功能配置时小于250KiB。
SQLite是自给自足的，这意味着不需要任何外部的依赖。
SQLite事务是完全兼容 ACID 的，允许从多个进程或线程安全访问。
SQLite支持 SQL92（SQL2）标准的大多数查询语言的功能。
SQLite使用 ANSI-C 编写的，并提供了简单和易于使用的 API。
SQLite可在UNIX（Linux, Mac OS-X, Android, iOS）和 Windows（Win32, WinCE, WinRT）中运行。

3.Java操作Sqlite

从 sqlite-jdbc 库下载 sqlite-jdbc-(VERSION).jar 的最新版本。或者从maven仓库下载。
在class 路径中添加下载的 jar 文件 sqlite-jdbc-(VERSION).jar

复制代码

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

public class SQLiteJDBCUtils {

Connection c;
   Statement stmt;

/**
    * 连接到一个现有的数据库。如果数据库不存在， 那么它就会被创建，最后将返回一个数据库对象。
    */
   @Before
   public void before() {
       try {
           Class.forName("org.sqlite.JDBC");
           c = DriverManager.getConnection("jdbc:sqlite:C:\db\test.db");
           System.out.println("Opened database successfully");
           stmt = c.createStatement();
       } catch (Exception e) {
           e.printStackTrace();
       }
   }

@After
   public void after() {
       try {
           stmt.close();
           c.close();
       } catch (SQLException e) {
           e.printStackTrace();
       }
   }

@Test
   public void createTable() throws SQLException {
       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);
   }

@Test
   public void insert() throws SQLException {
       c.setAutoCommit(false);
       StringBuffer sb = new StringBuffer();
       sb.append("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
               + "VALUES (1, 'Paul', 32, 'California', 20000.00 );n");
       sb.append("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " 
               + "VALUES (2, 'Allen', 25, 'Texas', 15000.00 );n");
       sb.append("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
               + "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );n");
       sb.append("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
               + "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );");
       stmt.executeUpdate(sb.toString());
       c.commit();
   }

@Test
   public void select() throws SQLException {
       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();
   }

@Test
   public void update() throws SQLException {
       c.setAutoCommit(false);
       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();
   }

@Test
   public void delete() throws SQLException {
       c.setAutoCommit(false);
       String sql = "DELETE from COMPANY where ID=2;";
       stmt.executeUpdate(sql);
       c.commit();

}

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
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

public class SQLiteJDBCUtils {

   Connection c;
   Statement stmt;

   /**
    * 连接到一个现有的数据库。如果数据库不存在， 那么它就会被创建，最后将返回一个数据库对象。
    */
   @Before
   public void before() {
       try {
           Class.forName("org.sqlite.JDBC");
           c = DriverManager.getConnection("jdbc:sqlite:C:\db\test.db");
           System.out.println("Opened database successfully");
           stmt = c.createStatement();
       } catch (Exception e) {
           e.printStackTrace();
       }
   }

   @After
   public void after() {
       try {
           stmt.close();
           c.close();
       } catch (SQLException e) {
           e.printStackTrace();
       }
   }

   @Test
   public void createTable() throws SQLException {
       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);
   }

   @Test
   public void insert() throws SQLException {
       c.setAutoCommit(false);
       StringBuffer sb = new StringBuffer();
       sb.append("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
               + "VALUES (1, 'Paul', 32, 'California', 20000.00 );n");
       sb.append("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " 
               + "VALUES (2, 'Allen', 25, 'Texas', 15000.00 );n");
       sb.append("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
               + "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );n");
       sb.append("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
               + "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );");
       stmt.executeUpdate(sb.toString());
       c.commit();
   }

   @Test
   public void select() throws SQLException {
       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();
   }

   @Test
   public void update() throws SQLException {
       c.setAutoCommit(false);
       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();
   }

   @Test
   public void delete() throws SQLException {
       c.setAutoCommit(false);
       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();
   }

}