在本章中，您將學(xué)習(xí)如何在Java程序中使用SQLite。
在我們的Java程序中開始使用SQLite之前，您需要確保在計(jì)算機(jī)上設(shè)置了SQLite JDBC驅(qū)動程序和Java。您可以檢查Java教程以在計(jì)算機(jī)上安裝Java?，F(xiàn)在，讓我們檢查一下如何設(shè)置SQLite JDBC驅(qū)動程序。
從sqlite-jdbc存儲庫下載最新版本的sqlite-jdbc-(VERSION).jar。
將下載的jar文件sqlite-jdbc-(VERSION).jar添加到您的類路徑中，或者將其與-classpath選項(xiàng)一起使用，如以下示例中所述。
以下Java程序顯示了如何連接到現(xiàn)有數(shù)據(jù)庫。如果數(shù)據(jù)庫不存在，則將創(chuàng)建該數(shù)據(jù)庫，最后將返回一個數(shù)據(jù)庫對象。
import java.sql.*;
public class SQLiteJDBC {
public static void main( String args[] ) {
Connection c = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("已成功打開數(shù)據(jù)庫");
}
}現(xiàn)在，讓我們編譯并運(yùn)行上述程序，以test.db在當(dāng)前目錄中創(chuàng)建數(shù)據(jù)庫。您可以根據(jù)需要更改路徑。我們假定當(dāng)前路徑中提供了最新版本的JDBC驅(qū)動程序sqlite-jdbc-3.7.2.jar。
$javac SQLiteJDBC.java $java -classpath ".:sqlite-jdbc-3.7.2.jar" SQLiteJDBC Open database successfully
如果您要使用Windows計(jì)算機(jī)，則可以按以下方式編譯和運(yùn)行代碼-
$javac SQLiteJDBC.java $java -classpath ".;sqlite-jdbc-3.7.2.jar" SQLiteJDBC 已成功打開數(shù)據(jù)庫
以下Java程序?qū)⒂糜谠谙惹皠?chuàng)建的數(shù)據(jù)庫中創(chuàng)建表。
import java.sql.*;
public class SQLiteJDBC {
public static void main( String args[] ) {
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
System.out.println("已成功打開數(shù)據(jù)庫");
stmt = c.createStatement();
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);
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("表創(chuàng)建成功");
}
}編譯并執(zhí)行上述程序后，它將在您的公司中創(chuàng)建COMPANY表，test.db文件的最終列表如下-
-rw-r--r--. 1 root root 3201128 Jan 22 19:04 sqlite-jdbc-3.7.2.jar -rw-r--r--. 1 root root 1506 May 8 05:43 SQLiteJDBC.class -rw-r--r--. 1 root root 832 May 8 05:42 SQLiteJDBC.java -rw-r--r--. 1 root root 3072 May 8 05:43 test.db
以下Java程序顯示了如何在上面的示例中創(chuàng)建的COMPANY表中創(chuàng)建記錄。
import java.sql.*;
public class SQLiteJDBC {
public static void main( String args[] ) {
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
c.setAutoCommit(false);
System.out.println("已成功打開數(shù)據(jù)庫");
stmt = c.createStatement();
String sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
"VALUES (1, 'Paul', 32, 'California', 20000.00 );";
stmt.executeUpdate(sql);
sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
"VALUES (2, 'Allen', 25, 'Texas', 15000.00 );";
stmt.executeUpdate(sql);
sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
"VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );";
stmt.executeUpdate(sql);
sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
"VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";
stmt.executeUpdate(sql);
stmt.close();
c.commit();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("記錄創(chuàng)建成功");
}
}編譯并執(zhí)行上述程序時(shí)，它將在COMPANY表中創(chuàng)建給定記錄，并顯示以下兩行-
已成功打開數(shù)據(jù)庫 記錄創(chuàng)建成功
以下Java程序顯示了如何從在上面的示例中創(chuàng)建的COMPANY表中獲取和顯示記錄。
import java.sql.*;
public class SQLiteJDBC {
public static void main( String args[] ) {
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
c.setAutoCommit(false);
System.out.println("已成功打開數(shù)據(jù)庫");
stmt = c.createStatement();
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();
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("已操作成功");
}
}編譯并執(zhí)行上述程序后，將產(chǎn)生以下結(jié)果。
已成功打開數(shù)據(jù)庫 ID = 1 NAME = Paul AGE = 32 ADDRESS = California SALARY = 20000.0 ID = 2 NAME = Allen AGE = 25 ADDRESS = Texas SALARY = 15000.0 ID = 3 NAME = Teddy AGE = 23 ADDRESS = Norway SALARY = 20000.0 ID = 4 NAME = Mark AGE = 25 ADDRESS = Rich-Mond SALARY = 65000.0 已操作成功
以下Java代碼顯示了如何使用UPDATE語句更新任何記錄，然后從COMPANY表中獲取并顯示更新的記錄。
import java.sql.*;
public class SQLiteJDBC {
public static void main( String args[] ) {
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
c.setAutoCommit(false);
System.out.println("已成功打開數(shù)據(jù)庫");
stmt = c.createStatement();
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();
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("操作成功完成");
}
}編譯并執(zhí)行上述程序后，將產(chǎn)生以下結(jié)果。
已成功打開數(shù)據(jù)庫 ID = 1 NAME = Paul AGE = 32 ADDRESS = California SALARY = 25000.0 ID = 2 NAME = Allen AGE = 25 ADDRESS = Texas SALARY = 15000.0 ID = 3 NAME = Teddy AGE = 23 ADDRESS = Norway SALARY = 20000.0 ID = 4 NAME = Mark AGE = 25 ADDRESS = Rich-Mond SALARY = 65000.0 操作成功完成
以下Java代碼顯示了如何使用DELETE語句刪除任何記錄，然后從COMPANY表中獲取并顯示其余記錄。
import java.sql.*;
public class SQLiteJDBC {
public static void main( String args[] ) {
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
c.setAutoCommit(false);
System.out.println("已成功打開數(shù)據(jù)庫");
stmt = c.createStatement();
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();
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("已操作成功");
}
}編譯并執(zhí)行上述程序后，將產(chǎn)生以下結(jié)果。
已成功打開數(shù)據(jù)庫 ID = 1 NAME = Paul AGE = 32 ADDRESS = California SALARY = 25000.0 ID = 3 NAME = Teddy AGE = 23 ADDRESS = Norway SALARY = 20000.0 ID = 4 NAME = Mark AGE = 25 ADDRESS = Rich-Mond SALARY = 65000.0 已操作成功