java + maven + sqlit3 最简单的数据库操作,建表,插入,查询
起因, 目的:
如题。 也很简单。
过程:
1. UserTableTest.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;public class UserTableTest {private static final String URL = "jdbc:sqlite:test.db"; // 数据库文件名public static void main(String[] args) {createTable();insertData();readData();}private static void createTable() {String createTableSQL = "CREATE TABLE IF NOT EXISTS User ("+ "userId VARCHAR(50) PRIMARY KEY,"+ "userCode VARCHAR(50),"+ "userName VARCHAR(50),"+ "userType INTEGER,"+ "password VARCHAR(50),"+ "phone VARCHAR(50),"+ "email VARCHAR(50),"+ "remarks VARCHAR(50),"+ "createdId VARCHAR(50),"+ "createName VARCHAR(50),"+ "createTime DATE,"+ "deleteMark INTEGER"+ ")";try (Connection conn = DriverManager.getConnection(URL);Statement stmt = conn.createStatement()) {stmt.execute(createTableSQL);System.out.println("表创建成功或已存在。");} catch (SQLException e) {e.printStackTrace();}}private static void insertData() {String sql = "INSERT INTO User (userId, userCode, userName, userType, password, phone, email, remarks, createdId, createName, createTime, deleteMark) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";try (Connection conn = DriverManager.getConnection(URL);PreparedStatement pstmt = conn.prepareStatement(sql)) {pstmt.setString(1, "001");pstmt.setString(2, "user001");pstmt.setString(3, "张三");pstmt.setInt(4, 1);pstmt.setString(5, "password123");pstmt.setString(6, "13800138000");pstmt.setString(7, "zhangsan@example.com");pstmt.setString(8, "这是一条备注");pstmt.setString(9, "creator001");pstmt.setString(10, "张三");pstmt.setDate(11, java.sql.Date.valueOf("2024-11-12"));pstmt.setInt(12, 0);pstmt.executeUpdate();System.out.println("插入成功");} catch (SQLException e) {e.printStackTrace();}}private static void readData() {String sql = "SELECT * FROM User";try (Connection conn = DriverManager.getConnection(URL);Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery(sql)) {while (rs.next()) {System.out.println("ID: " + rs.getString("userId"));System.out.println("用户名: " + rs.getString("userName"));// 打印其他字段...}System.out.println("数据读取成功。");} catch (SQLException e) {e.printStackTrace();}}
}
输出:
2. pom.xml
添加数据库驱动之后,一定要更新项目的环境依赖:
右键,选择 “Maven” -> “Reload Project”
<dependencies><!-- 添加一个外部库,然后 --><!-- 键点击项目并选择 "Maven" -> "Reload Project" --><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.13.2</version></dependency><!-- 新增到这里! --><dependency><groupId>org.xerial</groupId><artifactId>sqlite-jdbc</artifactId><version>3.36.0.3</version></dependency></dependencies>
结论 + todo
java. 是国内最流行的语言?