CODEWITHSUNDEEP
javasqliteinsertsyntax-errorsqlexception
Calu
Calu

Reputation: 69

Java - How can I INSERT values in SQLite?

Im starting in SQLite, I created my db and the connection works. When I try to Insert a value (the db is empty) I get the following error:

java.sql.SQLException: near ".684": syntax error

import java.sql.*;

public class connection{
    String route = "C:\\Freeman SA.db";
    Connection c = null;
    Statement stat = null;
    String op;

    public void connect(){
        try{
            c = DriverManager.getConnection("jdbc:sqlite:"+route);
            if (c!=null)
                System.out.println("Connected to db.");
        }
        catch (SQLException ex) {
            System.err.println("Couldn't connect."+ex.getMessage());
        }
    }

    public void insert_DB(String NAME, String LNAME, String ID, int AGE, int TIME, int SALARY) throws SQLException{
        connect();
        try {
            stat = c.createStatement();
            op = "INSERT INTO Remuneraciones (Nombre, Apellido, Rut, Edad, Tiempo, Sueldo) VALUES (" + NAME + ", " + LNAME + ", " + ID + ", " + AGE + ", " + TIME + ", " + SALARY + ");";
            stat.executeUpdate(op);       //Here is the problem
            stat.close();
        }
        catch (SQLException e) {
            System.err.println(e.getClass().getName() + ": " + e.getMessage());
        }
        c.close();
    }
}

Main.

public static void main(String[] args) throws IOException {

        connection n = new connection();
        try {
            n.insert_DB("Charlie", "White", "18.954.621-K", 21, 2, 650000);
        } catch (SQLException ex) {
            Logger.getLogger(main.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

PD: I'm learning from here: http://www.tutorialspoint.com/sqlite/sqlite_java.htm

Upvotes: 0

Views: 13297

Answers (1)

duffymo
duffymo

Reputation: 308938

It's a bad idea to create a SQL statement by concatenating Strings like that. Do some research into SQL injection attack and Little Bobby Tables.

PreparedStatement is a better idea. Bind your variables after validation.

See if this makes your life better:

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

/**
 * Demo RenumerationDao
 * Created by Michael
 * Creation date 6/8/2016.
 * @link https://stackoverflow.com/questions/37714254/java-how-can-i-insert-values-in-sqlite/37714292#37714292
 */
public class RenumerationDao {

    private static final String INSERT_SQL = "INSERT INTO Remuneraciones(Nombre, Apellido, Rut, Edad, Tiempo, Sueldo) VALUES(?, ?, ?, ?, ?, ?)";

    private Connection connection; // Better to get this from a pool.

    public RenumerationDao(Connection connection) {
        this.connection = connection;
    }

    public int insert(String firstName, String lastName, String id, int age, int timeInHours, int salary) {
        int numRowsInserted = 0;
        PreparedStatement ps = null;
        try {
            ps = this.connection.prepareStatement(INSERT_SQL);
            ps.setString(1, firstName);
            ps.setString(2, lastName);
            ps.setString(3, id);
            ps.setInt(4, timeInHours);
            ps.setInt(5, age);  // You'll have to update this each and every year. BirthDate would be better.
            ps.setInt(6, salary);
            numRowsInserted = ps.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(ps);
        }
        return numRowsInserted;
    }

    public static void close(Statement statement) {
        try {
            if (statement != null) {
                statement.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Upvotes: 5

Related Questions