Using Java to operate SQLite
Using Java to operate SQLite can be achieved through JDBC (Java Database Connectivity). The following are the basic steps for using Java for data operations:
1. Import JDBC driver: To operate SQLite in Java, you need to use the corresponding JDBC driver. The following Maven dependencies can be used to add SQLite JDBC drivers to the project:
<dependency>
<groupId>org.xerial</groupId>
<artifactId>sqlite-jdbc</artifactId>
<version>3.36.0.1</version>
</dependency>
2. Connect to SQLite database: To connect to the database using JDBC, you need to provide the database URL, username, and password (for SQLite, there is usually no username or password). The following is an example of Java code connecting to a SQLite database:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class SQLiteConnector {
public static void main(String[] args) {
Connection connection = null;
try {
//Load SQLite JDBC driver
Class.forName("org.sqlite.JDBC");
//Connect to SQLite database
String url = "jdbc:sqlite:/path/to/database.db";
connection = DriverManager.getConnection(url);
System. out. println ("Successfully connected to SQLite database!");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
//Close database connection
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
3. Perform data operations: After connecting to the database, you can perform database operations such as inserting, modifying, querying, and deleting. The following are Java code examples for inserting, modifying, querying, and deleting data:
-Data insertion:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class SQLiteInsert {
public static void main(String[] args) {
Connection connection = null;
try {
Class.forName("org.sqlite.JDBC");
String url = "jdbc:sqlite:/path/to/database.db";
connection = DriverManager.getConnection(url);
String sql = "INSERT INTO user (name, age) VALUES (?, ?)";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setString(1, "Tom");
statement.setInt(2, 30);
statement.executeUpdate();
System. out. println ("Insert data successfully!");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
-Data modification:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class SQLiteUpdate {
public static void main(String[] args) {
Connection connection = null;
try {
Class.forName("org.sqlite.JDBC");
String url = "jdbc:sqlite:/path/to/database.db";
connection = DriverManager.getConnection(url);
String sql = "UPDATE user SET age = ? WHERE name = ?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setInt(1, 35);
statement.setString(2, "Tom");
statement.executeUpdate();
System. out. println ("Data modified successfully!");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
-Data Query:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SQLiteSelect {
public static void main(String[] args) {
Connection connection = null;
try {
Class.forName("org.sqlite.JDBC");
String url = "jdbc:sqlite:/path/to/database.db";
connection = DriverManager.getConnection(url);
Statement statement = connection.createStatement();
String sql = "SELECT * FROM user";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
String name = resultSet.getString("name");
int age = resultSet.getInt("age");
System. out. println ("Name:"+name+", Age:"+age);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
-Data deletion:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class SQLiteDelete {
public static void main(String[] args) {
Connection connection = null;
try {
Class.forName("org.sqlite.JDBC");
String url = "jdbc:sqlite:/path/to/database.db";
connection = DriverManager.getConnection(url);
String sql = "DELETE FROM user WHERE name = ?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setString(1, "Tom");
statement.executeUpdate();
System. out. println ("Successfully deleted data!");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
The above are the basic steps and examples of using Java to operate SQLite databases. According to specific business requirements, various data operations can be carried out based on actual situations.
