inserting Mysql query in java code

Question

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

returning from inside the try and catch blocks is ill advised. — bradimus
– bradimus, Commented Aug 16, 2017 at 12:35

bradimus · Accepted Answer · 2017-08-16 12:55:09Z

2

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+"'";

edited Aug 16, 2017 at 12:55

bradimus

2,5341 gold badge19 silver badges24 bronze badges

answered Aug 16, 2017 at 12:34

Sławomir Czaja

3831 silver badge7 bronze badges

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

Comments

Bhavik Patel · Accepted Answer · 2017-08-16 12:35:56Z

You query

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+"'";

Turns out to be this :

SELECT DISTINCT  orders.orderer, orders.start_date, 
order_type.name, concept_name.nameFROM orders, 
order_type, concept_nameWHERE orders.order_type_id = order_type.order_type_id 
AND concept_name.concept_name_id = orders.concept_id 
AND orders.patient_id ='your patientid';

You are missing a whitespace before from and where

So do this :

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

Collectives™ on Stack Overflow

inserting Mysql query in java code

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related