Accepting SQL Queries from an external text file in Java

Question

I was assigned to make a Java program that accepts SQL queries from a text file line by line, with line 1 being the driver name, line 2 is the URL, line 3 and 4 is the username and password respectively and line 5 is the query. So for example my text file would have the following:

org.apache.derby.jdbc.ClientDriver
jdbc:derby://localhost:1527/STUDENTDB
app
app
SELECT * FROM StudentDb WHERE STUDENT_NAME = ?
Jesse

And here is the code:

import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import static java.lang.Boolean.parseBoolean;
import static java.lang.Integer.parseInt;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;

public class TestPreparedStatement {
      public static void main (String args[]) throws FileNotFoundException, IOException {
    try {
      BufferedReader br = new BufferedReader(new FileReader("C:\\Users\\PCUSER\\info.txt"));
      String driver = br.readLine();
      Class.forName(driver);
      System.out.println("LOADED DRIVER  ---> " + driver);

      String url = br.readLine();
      Connection con = DriverManager.getConnection (url, br.readLine(), br.readLine());
      System.out.println("CONNECTED TO   ---> "+ url);

            String queryStr = br.readLine();
            PreparedStatement ps = con.prepareStatement(queryStr);
            String argu = br.readLine();
            ps.setString(1, argu);

            String queryStr2 = br.readLine();

            ResultSet rs = ps.executeQuery();
            System.out.println("EXECUTED QUERY ---> " + queryStr);
            System.out.println("\nPROCESSING RESULTS:\n");
            while (rs.next()) 
            {
                System.out.println("Name: " + rs.getString("STUDENT_NAME").trim());
                System.out.println("Student Number: " + rs.getString("STUDENT_NUMBER").trim()); 
                System.out.println("Course: " + rs.getString("COURSE").trim()); 
            }

        rs.close();
        ps.close();
        con.close();
    } 

    catch (SQLException e) {
        e.printStackTrace();
    } 

    catch (ClassNotFoundException e) {
        e.printStackTrace();
    }
  }

}

The SELECT command I managed to make it work but I'm not sure how I can implement other query commands like INSERT INTO and DELETE. Like for example if I wanted to insert or if I wanted to update or delete. Basically, I need help in being able to use other commands besides SELECT.

why dont you try to keep all the configuration in .properties file and read that file and get url,passwd etc.. and for querries you use other file and read that one separately.This wy it will be more clearner and obvious — Ayushi Keshri
– Ayushi Keshri, Commented Oct 20, 2019 at 10:07

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

You run executeUpdate instead of executeQuery, the rest is just the same. Check this out for examples. You'd have to detect, if you're dealing with a select statement or otherwise, which is also straight forward: queryStr.trim().toUpperCase().startsWith("SELECT ")

I'm paranoid about input, it could be nice like "SELECT a FROM B" or not so nice " seLect a from b", which is still valid sql but a simple startsWith won't do. trim() removes leading blanks, toUpperCase makes it uppercase ;)

To detect the correct number of parameters (the "?" in the queries) I'd recommend, not to derive it from the query but from the rest of your input. In the query there could be string values containing question marks as well (INSERT INTO TABLE (COMMENT, GRADE), VALUES("To be or not to be?", ?)), that makes it a bit complicated.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Oct 20, 2019 at 10:21

Curiosa Globunznik

3,2331 gold badge18 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Titus Over a year ago

He will also have to replace the letters (x, y, etc.) representing the missing parameters with ? characters and counting the ? to see how many of the fallowing lines in the text file represent parameters.

Collectives™ on Stack Overflow

Accepting SQL Queries from an external text file in Java

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related