InputMismatchException? I still can't figure it out

Question

im trying to make an txt file into sqlite with java.

(ID,NAME,CATEGORY,XCOORDINATE,YCOORDINATE,LENGTH,WIDTH,FLOOR)

types are orderly INTEGER text text int int int int. (I create ID by AUTOINCREMENT.)

An example is like

maleToilet    room -58   0 58 48 9 

femaleToilet  room -58 -48 58 48 9

here is the main code:

import java.sql.*;
import java.io.*;
import java.util.*;

class Read{
public Scanner input;

public void openFile() {
    try {
        input = new Scanner(new File("D:\\room.txt"));
    } catch (FileNotFoundException fileNotFoundException) {
        System.err.println("Error opening file.");
        System.exit(1);
    }
}

public void closeFile() {
    if (input!=null) 
        input.close();
    }
}

public class TxtToSqlite
{
    public static void main( String args[] )
    {
        Read r = new Read();
        Connection c = null;
        Statement stmt = null;

    try {
  Class.forName("org.sqlite.JDBC");
  c = DriverManager.getConnection("jdbc:sqlite:test.db");
  c.setAutoCommit(false);

  stmt = c.createStatement();
  
  //create the schema 
  /*String sql = "CREATE TABLE ROOM " +
               "(ID INTEGER PRIMARY KEY AUTOINCREMENT," +
               " NAME           TEXT    NOT NULL, "+ 
               " CATEGORY       TEXT    NOT NULL, "+ 
               " XCOORDINATE    REAL    NOT NULL, "+ 
               " YCOORDINATE    REAL    NOT NULL, "+
               " LENGTH         REAL    NOT NULL, "+
               " WIDTH          REAL    NOT NULL, "+
               " FLOOR          INT     NOT NULL)";*/
  
  
  r.openFile();
  
  String sql = null;
  int i = 1;
  while(r.input.hasNext()){
    sql = "INSERT INTO ROOM (NAME,CATEGORY,XCOORDINATE,YCOORDINATE,LENGTH,WIDTH,FLOOR) " +
            "VALUES ("+"'"+r.input.next()+"', '"+r.input.next()+"', "+
            r.input.nextInt()+", "+r.input.nextInt()+", "+
            r.input.nextInt()+", "+r.input.nextInt()+", "+r.input.nextInt()+");";
    stmt.executeUpdate(sql);
    i++;
  }
  r.closeFile();
  
  
  stmt.close();
  c.close();
} catch (InputMismatchException e) {
    System.out.print("Input Error!");
} catch ( Exception e ) {
   System.err.println( e.getClass().getName() + ": " + e.getMessage() );
   System.exit(0);
}

} }

But it throws an InputMismatchException. So, can anyone help me with it? Thanks:)

BTW, I download sqlite-jdbc-3.7.2.jar from http://www.tutorialspoint.com/sqlite/sqlite_java.htm and make it into the referenced libraries.

Please add the `stacktrace` you are getting for `exception`. Also add the `sql schema` about how are you you creating table? — Smit, Oct 07 '13 at 17:55
This [exception](http://docs.oracle.com/javase/7/docs/api/java/util/InputMismatchException.html) comes from your `Scanner` object under the following conditions: "*the token retrieved does not match the pattern for the expected type, or that the token is out of range for the expected type*" — PM 77-1, Oct 07 '13 at 18:15
Are the data types of each field the main reasons? Like XCOORDINATE for REAL, but I use nextInt()? — newman, Oct 08 '13 at 15:33
@newman I don't think it should create a problem. [--> See here <--] (http://www.sqlite.org/datatype3.html). However as PM 77-1 mentioned could create a problem. First get those individual values to make sure you have them and then insert them. I will strongly suggest to make use of [JDBC PreparedStatement](http://www.mkyong.com/jdbc/jdbc-preparedstatement-example-batch-update/). Currently your `sql` statement is prone to [SQL Injection.](http://en.wikipedia.org/wiki/SQL_injection) — Smit, Oct 08 '13 at 16:00
So far, it finally gets no compile error! Thanks:). But here comes the new one - there is no data inserted into table ROOM in test.db. Why? — newman, Oct 08 '13 at 17:33
@newman It could be because your ID is not autoincremented and you are insrting them manually. Its just speculation though, without enought information its hard to say. Can you show your current code for insertion. Just edit and let us know. — Smit, Oct 08 '13 at 18:52
I've already changed the type of ID to INTEGER and use AUTOINCREMENT, but it won't work. I've also verified that I can capture all the data! — newman, Oct 09 '13 at 03:32
@newman I can see in your querie that you have `7` columns mentioned and values have `8`. Looke at [inserting sqlite record with AUTOINCREMENT column](http://stackoverflow.com/questions/8070252/android-inserting-sqlite-record-with-autoincrement-column). Moreover make sure atleast you have some values as all the columns you mentioned are not accepting `NULL`. Also put `e.printStackTrace()` into your catch block. If any `Exception` thrown then you will be notified and taht can be used — Smit, Oct 09 '13 at 16:09
@Smit It works fine, but there is no data inserted into table ROOM in test.db. TAT I'm gonna cry... — newman, Oct 10 '13 at 15:16
@newman Sorry for not pointing this out. Remove this line `c.setAutoCommit(false);` This forces the `SQL` statement for `Committing Transactions` **OR** put `c.commit();` after executing the querie. I hope this will resolve your issue. — Smit, Oct 10 '13 at 16:04
@Smit Thank you all for solving my question! Data finally appear in the db. TAT — newman, Oct 10 '13 at 16:17

score 1 · Accepted Answer · answered Oct 10 '13 at 16:34

1

As Smit says,

I correct the original one by changing the ID datatype to INTEGER and set it AUTOINCREMENT to make it easier.

then

Remove the c.setAutoCommit(false); in the main code to make it work.

Thank you all for answering! :)

answered Oct 10 '13 at 16:34

newman

75
1
7

InputMismatchException? I still can't figure it out

1 Answers1