Overview

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

1.安装SQLite

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

~sqlite3
SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

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

sudo apt-get install sqlite3

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

2.SQLitejava交互

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

2.1 创建并连接数据库

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 建表

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)

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)

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)

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)

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