java - 将数据插入sqlite java表

Question

我在将数据插入 SQLite 表时遇到问题。我想将变量插入列中而不是固定数据

Fixed data:
"INSERT INTO DATOS (ID,NOMBRE,GRUPO,TUTOR) " +
                "VALUES (1, 'Paul', 32, 'California');";

That i want:
"INSERT INTO DATOS (ID,NOMBRE,GRUPO,TUTOR) " +
                "VALUES (variableID, variableName, variableNumber, variableCity);";

我一直在阅读文档，但我无法将其取出，请您帮助我。目前我设法创建数据库并手动插入数据:

package com.proyecto.demo;

import java.sql.*;

public class SQLiteJDBC {
public static void main( String args[] ) {
  Connection c = null;
  Statement stmt = null;

  try { // Create the table
     Class.forName("org.sqlite.JDBC");
     c = DriverManager.getConnection("jdbc:sqlite:test.db");
     System.out.println("Paso 1: Base de datos creada");

     stmt = c.createStatement();
     String sql = "CREATE TABLE DATOS " +
                    "(ID INT PRIMARY KEY     NOT NULL," +
                    " NOMBRE           TEXT    NOT NULL, " + 
                    " GRUPO            TEXT     NOT NULL, " + 
                    " TUTOR        INT)"; 
     stmt.executeUpdate(sql);
     stmt.close();
     c.close();
  } catch ( Exception e ) {
     System.err.println( e.getClass().getName() + ": " +  e.getMessage() );
     System.exit(0);
  }

  try { // Insert date to the table (here is where i have problems)
    System.out.println("Paso 2: Conectar Base de datos");
     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 DATOS (ID,NOMBRE,GRUPO,TUTOR) " +
                    "VALUES (1, 'Paul', 32, 'California');"; 
     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("All inserted!");
 }
}

PD:一位用户建议我使用 SQL 脚本来执行我的任务，但另一位用户告诉我使用“preparedstatement”。哪个更好？

Answer 1

您必须使用PreparedStatement.另外，在 Java-8 之后，强烈建议将数据库连接视为 AutoClosables 。您可以阅读the try-with-resources Statement为了了解如何做到这一点。最后，不要使用 Exception 类来捕获异常。捕获大多数情况下会遇到的异常类型，在您的例子中是SQLException。

这是上述内容的完整示例:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

public class SQLiteSample {
    private static final String URL = "jdbc:sqlite:data.db";

    public static void main(String[] args) {
        createDb();
        createTable();
        insertPerson("Thomas", 15);
        insertPerson("Walter", 32);
    }

    private static void insertPerson(String name, int age) {
        final String SQL = "INSERT INTO persons VALUES(?,?)";
        try (Connection con = getConnection(); PreparedStatement ps = con.prepareStatement(SQL);) {
            ps.setString(1, name); // First question mark will be replaced by name variable - String;
            ps.setInt(2, age); // Second question mark will be replaced by name variable - Integer;
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void createTable() {
        final String SQL = "CREATE TABLE IF NOT EXISTS persons (name TEXT, age INTEGER);";
        // This SQL Query is not "dynamic". Columns are static, so no need to use
        // PreparedStatement.
        try (Connection con = getConnection(); Statement statement = con.createStatement();) {
            statement.executeUpdate(SQL);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void createDb() {
        try (Connection conn = getConnection()) {
            if (conn != null) {
                conn.getMetaData();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL);
    }
}