Connect To The SQLite Database Using SQLite JDBC Driver

admin

Connect To The SQLite Database Using SQLite JDBC Driver
Summary: in this tutorial, we will show you how to download SQLite JDBC Driver and connect to the SQLite database via JDBC.

Download SQLite JDBC Driver
To download the latest version of SQLite JDBC Driver, you go to the download page. You should download the latest version of the driver. At the time of this writing, the latest version is sqlite-jdbc-3.27.2.1.jar.

The JAR file includes both Java class files and SQLite binaries for Mac OX S, Linux, and Windows, Both 32-bit and 64-bit.

SQLite connection strings
The SQLite JDBC driver allows you to load an SQLite database from the file system using the following connection string:

jdbc:sqlite:sqlite_database_file_path
The sqlite_data_file_path is the path to the SQLite database file, which is either relative or absolute path as follows:

jdbc:sqlite:sample.db
Or

jdbc:sqlite:C:/sqlite/db/chinook.db
To connect to an in-memory database, you use the following connection string:

jdbc:sqlite::memory:
Connect to an SQLite database via JDBC
Step 1
Create a new directory called java under c:\sqlite

Step 2
Inside the java folder create a new folder called connect.

Step 3
Copy the jar file sqlite-jdbc-3.27.2.1.jar to the c:\sqlite\connect folder.


Step 4
Create a new subfolder called net inside c:\sqlite\connect\ and another subfolder called sqlitetutorial inside the net folder.

Step 5
Create a new file named Connect.java in the sqlitetutorial folder with the following contents.
The program will connect to the chinook.db database located in the c:\sqlite\db\ folder.

1. package net.sqlitetutorial;
   
2. 
   
3. import java.sql.Connection;
   
4. import java.sql.DriverManager;
   
5. import java.sql.SQLException;
   
6. 
   
7. /**
   
8. *
   
9. * @author sqlitetutorial.net
   
10. */
    
11. public class Connect {
    
12.      /**
    
13.      * Connect to a sample database
    
14.      */
    
15.     public static void connect() {
    
16.         Connection conn = null;
    
17.         try {
    
18.             // db parameters
    
19.             String url = "jdbc:sqlite:C:/sqlite/db/chinook.db";
    
20.             // create a connection to the database
    
21.             conn = DriverManager.getConnection(url);
    
22.             
    
23.             System.out.println("Connection to SQLite has been established.");
    
24.             
    
25.         } catch (SQLException e) {
    
26.             System.out.println(e.getMessage());
    
27.         } finally {
    
28.             try {
    
29.                 if (conn != null) {
    
30.                     conn.close();
    
31.                 }
    
32.             } catch (SQLException ex) {
    
33.                 System.out.println(ex.getMessage());
    
34.             }
    
35.         }
    
36.     }
    
37.     /**
    
38.      * @param args the command line arguments
    
39.      */
    
40.     public static void main(String[] args) {
    
41.         connect();
    
42.     }
    
43. }

複製代碼

Note that you should have the chinook.db file downloaded and copied to the C:/sqlite/db/ folder.

Step 6
Launch the command line window and navigate to the sqlitetutorial subfolder created above using the following command:

cd c:\sqlite\java\connect\net\sqlitetutorial
Step 7
Compile the Connect.java file using the following command:

javac Connect.java
You will see a new class file generated:
Note that your JDK must be on the PATH, otherwise you will get an error.

Step 8
Change the current directory to the connect directory:

c:\sqlite\java\connect\net\sqlitetutorial>cd..
c:\sqlite\java\connect\net>cd..
Step 9
Run the net.sqlitetutorial.Connect class using the following command:

java -classpath ".;sqlite-jdbc-3.27.2.1.jar" net.sqlitetutorial.Connect
Here is the output:

Connection to SQLite has been established.
It works as expected.

Troubleshooting
If you receive the following message, you should have missed step 8:

Error: Could not find or load main class net.sqlitetutorial.Connect

How the program works
In the connect() method:

First, declare a variable that holds a connecting string to the sqlite database c:\sqlite\db\chinook.db

String url = "jdbc:sqlite:C:/sqlite/db/chinook.db";
Next, use the DriverManager class to get a database connection based on the connection string.

conn = DriverManager.getConnection(url);
Then, trap any SQLException  in the try catch block and display the error message.

After that, close the database connection in the finally block.

Finally, call the connect() method in the main() method of the Connect class.

In this tutorial, you have learned step by step how to use the SQLite JDBC driver to connect to an SQLite database from a Java program.