15709

Insert String into JDBC SQLite database

Question:

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"); }

Answer1:

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"

Recommend

  • How to 'create temp table as select' in Slick?
  • Azure table store snapshot/backup capability
  • Bigquery event streaming and table creation
  • pyodbc doesn't report sql server error
  • Linq Objects Group By & Sum
  • PHPUnit_Framework_TestCase class is not available. Fix… - Makegood , Eclipse
  • Splitting given String into two variables - php
  • What is Eclipse's Declaration View used for?
  • PHP - How to update data to MySQL when click a radio button
  • req.body is undefined - nodejs
  • Counter field in MS Access, how to generate?
  • Possible to stop flickering java tooltip in heavyweight mode?
  • Validaiting emails with Net.Mail MailAddress
  • Join two tables and save into third-sql
  • How to handle AllServersUnavailable Exception
  • sending/ receiving email in Java
  • How to set my toolbar fixed while scrolling android
  • Large data - storage and query
  • Delete MySQLi record without showing the id in the URL
  • AT Commands to Send SMS not working in Windows 8.1
  • Unanticipated behavior
  • Cannot Parse HTML Data Using Android / JSOUP
  • Comma separated Values
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • How to delete a row from a dynamic generate table using jquery?
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • Proper way to use connect-multiparty with express.js?
  • JTable with a ScrollPane misbehaving
  • Trying to get generic when generic is not available
  • embed rChart in Markdown
  • apache spark aggregate function using min value
  • unknown Exception android
  • EntityFramework adding new object to nested object collection
  • Checking variable from a different class in C#
  • Sorting a 2D array using the second column C++
  • How to get NHibernate ISession to cache entity not retrieved by primary key
  • How can I use `wmic` in a Windows PE script?
  • failed to connect to specific WiFi in android programmatically
  • Unable to use reactive element in my shiny app
  • How can I use threading to 'tick' a timer to be accessed by other threads?