essefi intidhar essefi intidhar - 2 months ago 10
MySQL Question

inserting Mysql query in java code

I'm trying to retreive some data from data base. I executed the following Mysql query in Mysql workbench and it works perfectly :

SELECT DISTINCT orders.orderer, orders.start_date, order_type.name, concept_name.name
FROM orders, order_type, concept_name
WHERE orders.patient_id="4" AND orders.order_type_id=order_type.order_type_id AND concept_name.concept_name_id=orders.concept_id;


Now trying to write this code in java as it shown in the following code :

public class ConnectionMysql {
static Connection cnx;
static Statement st;
static ResultSet rst;

public static void main(String[] args){
// Connection cnx= connecterDB();
rechercheOrdre(4);
}

public static Connection connecterDB(){
try{
Class.forName("com.mysql.jdbc.Driver");

String url="jdbc:mysql://localhost:3306/openmrs";
String user="root";
String password="root123";

Connection cnx=DriverManager.getConnection(url, user, password);

return cnx;
} catch(Exception e){
e.printStackTrace();
return null;
}

}
public static void rechercheOrdre(int PatientId){
try{
Connection cnx= connecterDB();
st=cnx.createStatement();
String query=" SELECT DISTINCT orders.orderer, orders.start_date, order_type.name, concept_name.name"
+ "FROM orders, order_type, concept_name"
+ "WHERE orders.order_type_id = order_type.order_type_id AND concept_name.concept_name_id = orders.concept_id AND orders.patient_id ='"+PatientId+"'";
rst=st.executeQuery(query);

} catch(SQLException e){
System.out.println(e.getMessage());
}
}

}


Unfortunately, I'm getting an error that I couldn't understand:


You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.order_type_id == order_type.order_type_id AND concept_name.concept_name_id == o' at line 1

Answer Source

You are missing white spaces at end of lines when concated at runtime you will have concept_nameFROM

String query=" SELECT DISTINCT  orders.orderer, orders.start_date, order_type.name, concept_name.name "
            + "FROM orders, order_type, concept_name "
            + "WHERE orders.order_type_id = order_type.order_type_id AND concept_name.concept_name_id = orders.concept_id AND orders.patient_id ='"+PatientId+"'";