Trouble writing SQLite Database Java

Question

I am trying to create a SQLite database in java using JDBC library. My code creates the database fine, but when it comes to creating the table within the database, it doesn't actually create a table. I know this because I am trying to print the recordset and it doesn't reach the print statements. My overall goal is to parse an XML, and then write it to corresponding fields for use in my android app. Heres my code.

import java.io.File;

import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.DocumentBuilder;

import org.w3c.dom.Document;
import org.w3c.dom.NodeList;
import org.w3c.dom.Node;
import org.w3c.dom.Element;

import java.sql.*;

public class TestingXML
{

public static void main(String[] args)
{

    try
    {

        Class.forName("org.sqlite.JDBC");
        Connection conn = DriverManager.getConnection("jdbc:sqlite:productrecalls.db");
        Statement stat = conn.createStatement();
        stat.executeUpdate("drop table if exists products;");
        stat.executeUpdate("create table products (date, brandname, productdescription, reason, company, link);");
        PreparedStatement prep = conn.prepareStatement("insert into products values (?, ?, ?, ?, ?, ?);");

        File fXmlFile = new File("/Users/Trevor/Desktop/1.7-RecallsDataSet.xml");
        DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance();
        DocumentBuilder dBuilder = dbFactory.newDocumentBuilder();
        Document doc = dBuilder.parse(fXmlFile);

        //optional, but recommended
        //read this - http://stackoverflow.com/questions/13786607/normalization-in-dom-parsing-with-java-how-does-it-work
        doc.getDocumentElement().normalize();

        NodeList nList = doc.getElementsByTagName("PRODUCT");

        for (int temp = 0; temp < nList.getLength(); temp++) {

            Node nNode = nList.item(temp);

            if (nNode.getNodeType() == Node.ELEMENT_NODE) {

                Element eElement = (Element) nNode;

                String day = eElement.getElementsByTagName("DATE").item(0).getTextContent().substring(5, 7);
                String month = eElement.getElementsByTagName("DATE").item(0).getTextContent().substring(8, 11);
                String year = eElement.getElementsByTagName("DATE").item(0).getTextContent().substring(12, 17);

                String date = month + "-" + day + "-" + year;
                String brandname = eElement.getElementsByTagName("BRAND_NAME").item(0).getTextContent();
                String productdescription = eElement.getElementsByTagName("PRODUCT_DESCRIPTION").item(0).getTextContent();
                String reason = eElement.getElementsByTagName("REASON").item(0).getTextContent();
                String company = eElement.getElementsByTagName("COMPANY").item(0).getTextContent();
                String link = eElement.getElementsByTagName("COMPANY_RELEASE_LINK").item(0).getTextContent();

                prep.setString(1, date);
                prep.setString(2, brandname);
                prep.setString(3, productdescription);
                prep.setString(4, reason);
                prep.setString(5, company);
                prep.setString(6, link);

                conn.setAutoCommit(false);
                prep.executeBatch();
                conn.setAutoCommit(true);

            }
        }
        ResultSet rs = stat.executeQuery("select * from products;");
        while (rs.next())
        {
            System.out.println(" ");
            System.out.println("date = " + rs.getString("date"));
            System.out.println("brandname = " + rs.getString("brandname"));
            System.out.println("productdescription = " + rs.getString("productdescription"));
            System.out.println("reason = " + rs.getString("reason"));
            System.out.println("company = " + rs.getString("company"));
            System.out.println("link = " + rs.getString("link"));
        }
    }
    catch (Exception e)
    {
      e.printStackTrace();
    }

    System.out.println("Done.");

}
}

you can open the sqlite file using firefox browser add-on and see for sure if the tables are there or not. you're also not closing the connection and other stuff... — Saeid Farivar, Feb 22 '14 at 20:24

score 1 · Accepted Answer · answered Feb 22 '14 at 20:22

You are close :) The syntax for your "create table" statement needs more info. Here is the SQLite doc page: enter link description here

Here is an example statement I recently used on a project. It gives you a concrete example of a create table statement:

create table authors (id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL, first_name varchar(25) NOT NULL,
last_name varchar(25) NOT NULL,created_at datetime, updated_at datetime);

Note! You need to specify the column data types. I also showed you how to create a primary key (whose value is generated by the database).

Hope that helps :)

Trouble writing SQLite Database Java

1 Answers1