Insert String into JDBC SQLite database

问题:

I am creating a java application that requires a local database. I am using a SQLite database with JDBC. I am new to Java local database and JDBC.

From tutorials on the internet I have just about worked out what I am doing, but it is not very clear to me. I am now trying to create a method that can take in a table name and a set of strings and add them to the table.

I am having 2 problems:

First, the method does not accept table as a parameter to pass in. I know I can create a method for each table, but this is inefficient and if possible I would like one method to work for all tables.

Second, I cannot work out how to pass the strings into the table, because the data to be sent to the table is inside a SQL statement and does not recognise the Java string.

Below is the method so far:

public static void Insert(Table table, String id, String name, String age, String address, String salary) {
    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 );";
        //I want this to write the strings passed in by the method, and to the table passed in by the method
        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");
}

回答1:

Use a Prepared Statement instead of CreateStatement as follows:

PreparedStatement stmt = c.prepareStatement
      ("insert into COMPANY values(?,?,?,?,?)");
      stmt.setInt(1,1);
      stmt.setString(2,"Paul");
      stmt.setInt(3,32);
      stmt.setString(4, "California");
      stmt.setBigDecimal(5, 20000.00);
      stmt.executeUpdate();

You should create a Prepared Statement for each Table that you want to insert into. Alternatively you can build up the insert string dynamically with Table as a parameter

String insert_string = "insert into" + Table + "values, etc, etc"