user6276841 user6276841 - 6 months ago 10
SQL Question

Problems inserting data from user into database using Java

I've got a database that includes a movie table, I'm trying to write a java program to receive input from the user and insert it into the database. My code is below. I've included the whole code as I'm not sure why I'm getting the errors.

For each of the

stmt.set...
lines I am receiving, for example:


error: cannot find symbol - stmt.setString(fname);


I can't figure out why I'd be getting this as I've received the input from the user and assigned it to this variable before inserting.

Any help would be very much appreciated.
Thanks!

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

public class insert {
public static void main(String [] argv) throws Exception {
Connection conn = null;
try
{
Class.forName("org.postgresql.Driver");
String url = "jdbc:postgresql://localhost//////";
conn = DriverManager.getConnection(url, "/////", "///");
}
catch (ClassNotFoundException e)
{
e.printStackTrace();
System.exit(1);
}
catch (SQLException e)
{
e.printStackTrace();
System.exit(2);
}
BufferedReader bufRead = new BufferedReader(new InputStreamReader(System.in));
try {
System.out.println("Please enter the id for the new movie: ");
String id = bufRead.readLine();
int id_int = Integer.parseInt(id);
System.out.println("Please enter the title for the new movie: ");
String title = bufRead.readLine();
System.out.println("Please enter the director's first name: ");
String fname = bufRead.readLine();
System.out.println("Please enter the director's last name: ");
String lname = bufRead.readLine();
System.out.println("Please enter the genre of the movie: ");
String genre = bufRead.readLine();
System.out.println("Please enter the media type: ");
String media = bufRead.readLine();
System.out.println("Please enter the movie's release date: ");
String date = bufRead.readLine();
java.sql.Date release_d = java.sql.Date.valueOf(date);
System.out.println("Please enter the movie's studio: ");
String studio = bufRead.readLine();
System.out.println("Please enter the retail price of the movie: ");
String price = bufRead.readLine();
double price_d = Double.parseDouble(price);
System.out.println("Please enter the number of copies in stock: ");
String stock = bufRead.readLine();
int stock_int = Integer.parseInt(stock);

} catch (IOException err) {
System.out.println(err);
} catch (NumberFormatException err) {
System.out.println(err);
}

System.out.println("\nWelcome to MovieDirect");
System.out.println();

try {
PreparedStatement stmt = conn.prepareStatement("INSERT INTO movie " + "(movie_id, movie_title, director_first_name, director_last_name, genre, media_type, release_date, studio_name, retail_price, current_stock)" + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

stmt.setInt(1, id_int);
stmt.setString(2, title);
stmt.setString(3, fname);
stmt.setString(4, lname);
stmt.setString(5, genre);
stmt.setString(6, media);
stmt.setDate(7, release_d);
stmt.setString(8, studio);
stmt.setDouble(9, price_d);
stmt.setString(10, stock_int);

stmt.executeUpdate();

} catch (SQLException e) {
System.out.println(e);
conn.close();
System.exit(1);
}
System.out.println("\nSuccess! ");
conn.close();
}
}

Answer

At first glance, your error looks like it's talking about setString and so on, but it's actually about the variables you're passing to those methods.

All of the variables id_int, title, fname, lname, etc. were all declared in a different scope: the other try block.

To fix it, declare all of those variables outside of the try blocks (just like how you did with conn), so that they will be in-scope for all of the try blocks.

Comments