sqlite数据库事务 Java,SQLite数据库在java中应用

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.SQLite与java交互

SQLite在java中的基本使用方法和其他诸如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。