Overview

这次的服务器，我们准备使用消息队列和SQLite这两个新东西，SQLite主要负责将用户提交的序列信息存储，并在结果页面查询显示。存储的信息包括用户的ip+timeStamp，序列内容，序列的处理状态。

1.安装SQLite

SQLite的安装特别简单。
ubuntu14.04这个版本自带SQLite，在命令行输入sqlite3，显示如下：

1
2
3
4
5
～sqlite3
SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

如果没有出现上述信息，可以使用如下命令进行安装：

1
sudo apt-get install sqlite3

每个版本的ubuntu的软件源略有新旧不同，但不影响使用。

2.SQLite与java交互

SQLite在java中的基本使用方法和其他诸如mysql等数据库大同小异，下面我们从创建并连接数据库，建表，增，删，改，查这6个方面简单介绍下。

2.1 创建并连接数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
package testSQLite;
 
import java.sql.Connection;
import java.sql.DriverManager;
 
public class SQLiteJDBC {
    public static void main( String args[] ) {
        Connection c;
        try {
            Class.forName("org.sqlite.JDBC");
            c = DriverManager.getConnection("jdbc:sqlite:testSQLite.db");
        } catch ( Exception e ) {
            e.printStackTrace();
        }
        System.out.println("Connected database successfully");
    }
}

2.2 建表

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
package testSQLite;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
 
public class createTable {
    public static void main( String args[] ) {
        Connection c;
        Statement stmt;
        try {
            Class.forName("org.sqlite.JDBC");
            c = DriverManager.getConnection("jdbc:sqlite:testSQLite.db");
            System.out.println("Connected database successfully");
 
            stmt = c.createStatement();
            String sql = "CREATE TABLE TEAM " +
                         "(NUMBER INT PRIMARY KEY     NOT NULL, " +
                         " NAME           TEXT    NOT NULL, " +
                         " AGE            INT     NOT NULL, " +
                         " COUNTRY        CHAR(50), " +
                         " POSITION       CHAR(50))";
            stmt.executeUpdate(sql);
            stmt.close();
            c.close();
        } catch ( Exception e ) {
            e.printStackTrace();
        }
        System.out.println("Table created successfully");
    }
 
}

2.3 增加(insert)

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
package testSQLite;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
 
 
public class insert {
    public static void main(String args[]) {
        Connection c;
        Statement stmt;
        try {
            Class.forName("org.sqlite.JDBC");
            c = DriverManager.getConnection("jdbc:sqlite:testSQLite.db");
            c.setAutoCommit(false);
            System.out.println("Connected database successfully");
 
            stmt = c.createStatement();
            String sql = "INSERT INTO TEAM (NUMBER,NAME,AGE,COUNTRY,POSITION) " +
                       "VALUES (1, 'Navas', 29, 'Costa Rica', 'goalkeeper' );";
            stmt.executeUpdate(sql);
 
            sql = "INSERT INTO TEAM (NUMBER,NAME,AGE,COUNTRY,POSITION) " +
                "VALUES (2, 'Varane', 23, 'France', 'center back' );";
            stmt.executeUpdate(sql);
 
            sql = "INSERT INTO TEAM (NUMBER,NAME,AGE,COUNTRY,POSITION) " +
                "VALUES (3, 'Pepe', 33, 'Portugal', 'center back' );";
            stmt.executeUpdate(sql);
 
            sql = "INSERT INTO TEAM (NUMBER,NAME,AGE,COUNTRY,POSITION) " +
                "VALUES (4, 'Ramos', 30, 'Spain', 'center back' );";           
            stmt.executeUpdate(sql);
             
            sql = "INSERT INTO TEAM (NUMBER,NAME,AGE,COUNTRY,POSITION) " +
                    "VALUES (7, 'Ronaldo', 31, 'Portugal', 'stricker' );";         
            stmt.executeUpdate(sql);
 
            stmt.close();
            c.commit();
            c.close();
        } catch ( Exception e ) {
            e.printStackTrace();
        }
        System.out.println("Insert successfully");     
    }
 
}

2.4 查找(select)

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
package testSQLite;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
 
public class select {
 
    public static void main(String[] args) {
        Connection c;
        Statement stmt;
        try {
            Class.forName("org.sqlite.JDBC");
            c = DriverManager.getConnection("jdbc:sqlite:testSQLite.db");
            c.setAutoCommit(false);
            System.out.println("Connected database successfully");
 
            stmt = c.createStatement();
            ResultSet rs = stmt.executeQuery( "SELECT * FROM TEAM;" );
            while ( rs.next() ) {
                int number = rs.getInt("number");
                String  name = rs.getString("name");
                int age  = rs.getInt("age");
                String  country = rs.getString("country");
                String position = rs.getString("position");
                System.out.println( "NUMBER = " + number );
                System.out.println( "NAME = " + name );
                System.out.println( "AGE = " + age );
                System.out.println( "COUNTRY = " + country );
                System.out.println( "POSITION = " + position );
                System.out.println();
            }
            rs.close();
            stmt.close();
            c.close();
        } catch ( Exception e ) {
            e.printStackTrace();
        }
        System.out.println("Select successfully");
 
    }
 
}

2.5 更改(update)

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
package testSQLite;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
 
public class update {
 
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        Connection c = null;
        Statement stmt = null;
        try {
            Class.forName("org.sqlite.JDBC");
            c = DriverManager.getConnection("jdbc:sqlite:testSQLite.db");
            c.setAutoCommit(false);
            System.out.println("Connected database successfully");
 
            stmt = c.createStatement();
            String sql = "UPDATE TEAM set POSITION = 'fullback' where NUMBER=4;";
            stmt.executeUpdate(sql);
            c.commit();
 
            ResultSet rs = stmt.executeQuery( "SELECT * FROM TEAM;" );
            while ( rs.next() ) {
                int number = rs.getInt("number");
                String  name = rs.getString("name");
                int age  = rs.getInt("age");
                String  country = rs.getString("country");
                String position = rs.getString("position");
                System.out.println( "NUMBER = " + number );
                System.out.println( "NAME = " + name );
                System.out.println( "AGE = " + age );
                System.out.println( "COUNTRY = " + country );
                System.out.println( "POSTION = " + position );
                System.out.println();
            }
            rs.close();
            stmt.close();
            c.close();
        } catch ( Exception e ) {
            e.printStackTrace();
        }
        System.out.println("Update successfully");
 
    }
 
}

2.6 删除(delete)

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
package testSQLite;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
 
public class delete {
 
    public static void main(String[] args) {
        Connection c = null;
        Statement stmt = null;
        try {
            Class.forName("org.sqlite.JDBC");
            c = DriverManager.getConnection("jdbc:sqlite:testSQLite.db");
            c.setAutoCommit(false);
            System.out.println("Connected database successfully");
 
            stmt = c.createStatement();
            String sql = "DELETE from TEAM where NUMBER=3;";
            stmt.executeUpdate(sql);
            c.commit();
 
            ResultSet rs = stmt.executeQuery( "SELECT * FROM TEAM;" );
            while ( rs.next() ) {
                int number = rs.getInt("number");
                String  name = rs.getString("name");
                int age  = rs.getInt("age");
                String  country = rs.getString("country");
                String position = rs.getString("position");
                System.out.println( "NUMBER = " + number );
                System.out.println( "NAME = " + name );
                System.out.println( "AGE = " + age );
                System.out.println( "COUNTRY = " + country );
                System.out.println( "POSTION = " + position );
                System.out.println();
            }
            rs.close();
            stmt.close();
            c.close();
        } catch ( Exception e ) {
            e.printStackTrace();
        }
        System.out.println("Delete successfully");
 
    }
 
}

这篇文章主要参考了这里：SQLite - Java。