在本章中,您将学习如何在Java程序中使用SQLite。
安装
在无涯教程的Java程序中开始使用SQLite之前,需要确保已在计算机上设置了SQLite JDBC驱动程序和Java。您可以检查Java教程以在计算机上安装Java。现在,让无涯教程检查一下如何设置SQLite JDBC驱动程序。
从sqlite-jdbc存储库。
在类路径中添加下载的jar文件 sqlite-jdbc-(VERSION).jar ,也可以将其与-classpathoptions一起使用,如以下示例中所述。
连接到数据库
以下Java程序显示了如何连接到现有数据库。如果数据库不存在,则将创建该数据库,最后将返回一个数据库对象。
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("Opened database successfully"); } }
现在,让无涯教程编译并运行上述程序,以在当前目录中创建数据库 test.db 。您可以根据需要更改路径。无涯教程假设当前路径中提供了JDBC驱动程序 sqlite-jdbc-3.7.2.jar 的当前版本。
$javac SQLiteJDBC.java $java -classpath ".:sqlite-jdbc-3.7.2.jar" SQLiteJDBC Open database successfully
如果要使用Windows计算机,则可以按以下方式编译和运行代码-
$javac SQLiteJDBC.java $java -classpath ".;sqlite-jdbc-3.7.2.jar" SQLiteJDBC Opened database successfully
创建表
以下Java程序将用于在先前创建的数据库中创建表。
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("Opened database successfully"); 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("Table created successfully"); } }
编译并执行上述程序后,它将在 test.db 中创建COMPANY表,文件的最终列表如下-
-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程序显示了如何在上面的Example中创建的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("Opened database successfully"); 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("Records created successfully"); } }
当上面的程序被编译和执行时,它将在COMPANY表中创建给定的记录,并显示以下两行-
Opened database successfully Records created successfully
选择操作
以下Java程序演示了如何从上面的Example中创建的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("Opened database successfully"); 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("Operation done successfully"); } }
编译并执行上述程序后,将产生以下输出。
Opened database successfully 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 Operation done successfully
更新操作
以下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("Opened database successfully"); 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("Operation done successfully"); } }
编译并执行上述程序后,将产生以下输出。
Opened database successfully 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 Operation done successfully
删除操作
以下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("Opened database successfully"); 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("Operation done successfully"); } }
编译并执行上述程序后,将产生以下输出。
Opened database successfully 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 Operation done successfully
参考链接
https://www.learnfk.com/sqlite/sqlite-java.html