I have established a connection with mySql and Java and currently I can run a query from my database. I can run queries such as "Select * from Customer". The problem appears, when i try to scan a value from the user and try to run it in my db.
select E.rent_id, name, telephone, Pa.date_get, Pr.date_return
from Rent as E, Customer as P, Get as Pa, return as Pr, Makes as Pg
where E.rent_id = Pg.rent_id
and Pg.cust_id = P.cust_id
and E.rent_id = Pa.rent_id
and E.rent_id = Pr.rent_id
and Month (Pa.date_get) = @input
and Year (Pa.date_get) = @input;
This is a query, which selects an id, a name, telephone, and dates of get and return of a vehicle (this is a db for a car rental company). Unfortunately, when I connect to my db via Java, using the connector, I can't run this query. Do you have any idea what to do? Here follows the code in java that makes the connection and the use of the query, stated above.
package database;
import java.util.Scanner;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Connect {
public void databaseConnect(String connect_string, String username, String password) {
try {
Scanner scanner = new Scanner (System.in);
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection(connect_string, username, password);
System.out.println("Connection Succesful");
Statement statement = conn.createStatement();
System.out.println("Insert month: ");
int mina = scanner.nextInt();
System.out.println("Insert year: ");
int xrono = scanner.nextInt();
String query = "select E.rent_id, name, telephone, Pa.date_get, Pr.date_return\r\n" +
"from Rent as E, Customers P, Get as Pa, return as Pr, makes as Pg\r\n" +
"where E.rent_id = Pg.rent_id \r\n" +
"and Pg.cust_id = P.cust_id \r\n" +
"and E.rent_id = Pa.rent_id \r\n" +
"and E.rent_id = Pr.rent_id \r\n" +
"and Month (Pa.date_get) = @mina \r\n" +
"and Year (Pa.date_get) = @xrono;";
ResultSet rs = statement.executeQuery(query);
while (rs.next()) {
System.out.println(rs.getString(1));
System.out.println(rs.getString(2));
System.out.println(rs.getString(3));
System.out.println(rs.getString(4));
System.out.println(rs.getString(5));
}
} catch (Exception e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
Connect connectToServer = new Connect();
connectToServer.databaseConnect("jdbc:mysql://localhost/acr", "/*username*/", "/*password*/");
}
}
Can you see a mistake? Thank you so much in advance!