passing java string variable in mysql query

Question

How to pass java string variable in sql query .I have done all the JDBC connection .

My sql database query is

sql = "Select * 
       from production AS cust 
       INNER JOIN location AS comp 
       ON cust.location_id = comp.location_id 
       where comp.name = locationnames AND crop_id =1";

It is not working. However if i do the following code its working

sql = "Select * 
       from production AS cust 
       INNER JOIN location AS comp 
       ON cust.location_id = comp.location_id 
       where comp.name = "\taplejung"\  
       AND crop_id =1";

Now tell me how should i pass variable name to the sql query to execute this. Jst tell me how to pass the variable locationnames to comp.name.

My complete java function looks like this: locationCombo denotes item selected in combobox. CropCombo also denotes the same...

public void displayYearwise() throws SQLException, ClassNotFoundException{

       //jComboBox4.setSelectedItem("Crops");
        //DefaultCategoryDataset dataset = new DefaultCategoryDataset();
         XYSeriesCollection dataset = new XYSeriesCollection();
         XYSeries series = new XYSeries("production");
         XYSeries series1 = new XYSeries("scat");
        String JDBC_DRIVER="com.mysql.jdbc.Driver";
    String DB_URL="jdbc:mysql://localhost/data2";
    Connection conn;
    Statement stmt;
    String USER = "root";
    String PASS = "";
        Object cropname = CropCombo.getSelectedItem();
       String cropnames = cropname.toString();
       Object locationname = locationCombo.getSelectedItem();
       //       String locationnames = locationname.toString();
       String locationnames = "taplejung";
       String pd="paddy ";
            System.out.println(cropnames.length()+" "+pd.length());

            System.out.println(cropsList);
         String sql=null;
         if(cropnames.equals("paddy"))
         {
             //System.out.println();                     
             sql="Select * 
                  from production AS cust 
                  INNER JOIN location AS comp 
                  ON cust.location_id = comp.location_id 
                  WHERE comp.name = "+locationnames+" 
                  AND crop_id =1";
         }


          else{
          sql="SELECT * 
               FROM `production` 
               WHERE crop_id = 4 
               AND location_id = 10";
         }

           try{
            Class.forName(JDBC_DRIVER);
            conn=DriverManager.getConnection(DB_URL,USER,PASS);
            System.out.println("Creating statement...");
            stmt = conn.createStatement();                       
                       System.out.println(sql);            
                         ResultSet rs=stmt.executeQuery(sql);                      
                        while (rs.next()){
                            //String student = rs.getString("studentname");
                            String yeartext = rs.getString("year_of_production");
                            //double value = Double.parseDouble(text);
                            String productiontext = rs.getString("production_amount");
                            Double yield = rs.getDouble("yield_amount");
                            double production = Double.parseDouble(productiontext);
                            double year = Double.parseDouble(yeartext);
                            series.add(year,production) ;
                            series1.add(year,yield) ;
                            //dataset.addSeries(series);              
             }
                        dataset.addSeries(series);
                        dataset.addSeries(series1);     
                        chartArea.removeAll();
                       JFreeChart chart = ChartFactory.createScatterPlot("Scatter Plot","Year","Paddy Production", dataset);
                       // JFreeChart chart = ChartFactory.createScatterPlot("Scatter Plot","Year","Paddy Production", dataset, PlotOrientation.HORIZONTAL, rootPaneCheckingEnabled, rootPaneCheckingEnabled, rootPaneCheckingEnabled);
//                        CategoryPlot p = chart.getCategoryPlot();
                         //XYPlot xyplot = (XYPlot)jfreechart.getPlot();
                        //http://stackoverflow.com/questions/12417732/jfreechart-with-scroller
                        ChartPanel chartPanel = new ChartPanel(chart, false);
                        chartArea.setLayout(new BorderLayout());
                        chartArea.add(chartPanel, BorderLayout.EAST);
                        chartArea.add(chartPanel);
                        SwingUtilities.updateComponentTreeUI(this);
//                        p.setRangeGridlinePaint(blue);
                        chartArea.updateUI();
                        System.out.println("Database created successfully...");

                }
           catch(SQLException se)
                {
                    //Handle errors for JDBC
                    System.out.println("Connect failed ! ");
                    se.printStackTrace();
//                    JOptionPane.showMessageDialog(MajorUI.this, err.getMessage());
                    }

    }

score 7 · Answer 1 · answered Jul 09 '14 at 03:25

7

String locationnames = "taplejung";

String sql = "Select * from production AS cust INNER JOIN location AS comp ON cust.location_id = comp.location_id where comp.name ='"+ locationnames +"' AND crop_id =1";

answered Jul 09 '14 at 03:25

Bishan

15,211
52
164
258

@Bishan i did that i i get the following error. Select * from production AS cust INNER JOIN location AS comp ON cust.location_id = comp.location_id WHERE comp.name = Taplejung AND crop_id =1 Connect failed ! com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'Taplejung' in 'where clause' at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) – nzy Jul 09 '14 at 03:33
Have tried this one ? `"Select * from production AS cust INNER JOIN location AS comp ON cust.location_id = comp.location_id where comp.name ='"+ locationnames +"' AND crop_id =1"` – Bishan Jul 09 '14 at 06:13
That is so insecure as it doesn't prevent SQL Injection. – Carlos López Marí Oct 14 '19 at 08:51

Elliott Frisch · Accepted Answer · 2014-07-09T03:54:18.297

7

Use a PreparedStatement and bind the String parameter,

final String sql = "select * from production AS cust INNER JOIN location"
    + " AS comp ON cust.location_id = comp.location_id where "
    + "comp.name = ? AND crop_id = 1";
PreparedStatement ps = null;
try {
  ps = conn.prepareStatement(sql);
  ps.setString(1, "taplejung");
} catch (Exception e) {
  e.printStackTrace();
} finally {
  if (ps != null) {
    try {
      ps.close();
    } catch (Exception ignored) {
    }
  }
}

Edit (Based on your additional code, change it to something like)

PreparedStatement ps = null;

String sql = null;
if (cropnames.equals("paddy")) {
  // System.out.println();
  sql = "SELECT * FROM `production` AS cust INNER JOIN location AS comp "
      + "ON cust.location_id = comp.location_id WHERE comp.name = "
      + "? AND crop_id = 1";
} else {
  sql = "SELECT * FROM `production` WHERE crop_id = 4 AND location_id = 10";
}
ps = conn.prepareStatement(sql);
if (cropnames.equals("paddy")) {
  ps.setString(1, locationnames);
}
System.out.println(sql);
ResultSet rs = ps.executeQuery();

edited Jul 09 '14 at 03:54

answered Jul 09 '14 at 03:27

Elliott Frisch

198,278
20
158
249

2

PrepareStatement is safe from sql injection too. More details on http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html – Chandra Prakash Jul 09 '14 at 03:30
@Elliott Frisch ...i have all working sql code in java. Its not that i only need taplejung. The location name is imported from combobox in java.The combobox is connected to mysql database for getting locationname. I simply want to pass the variable name to sql query.may be u could see my question once more ..see my query – nzy Jul 09 '14 at 03:40
@ElliottFrisch do you think you could help me in my code. ? Can you tell me what have i done wrong? The problem simply is in sql query to pass the java variable.. how should i pass the java variable – nzy Jul 09 '14 at 03:49
@enjal Edited. Something like that. – Elliott Frisch Jul 09 '14 at 03:54

score 0 · Answer 3 · answered Jul 09 '14 at 03:57

Whenever I have to make sql queries I use a library like jdbi to do it. This will allow you to create an interface with different queries. All you have to do is define the interface, create a POJO, and create a mapper between a SQL table and a Java POJO.

The interface would look something like this.

@RegisterMapper(ProductionMapper.class)
public interface ProductionDAO {
     @SqlQuery("Select * from production AS cust INNER JOIN location AS comp ON cust.location_id = comp.location_id where comp.name = :name AND crop_id =1")
     Production findRow(@Bind("name") String name);
    }

The POJO would look something like this.

public class Production {
   private VariableTypeA variableA;
   // other variables
   public Production(VariableTypeA variableA ....) {
      this.variableA = variableA;
      // set everything else
   }
   // getters and setters
}

The mapper would look something like this.

public class ProductionMapper implements ResultSetMapper<Production> {
  public Production map(int index, ResultSet r, StatementContext ctx) throws SQLException {
    return new Production(r.getSomeType("columnName"), ...);
  }
}

This design makes it really simple to interact with your database and pass variables as well as making it so that your classes dont violate the SRP

http://jdbi.org/sql_object_overview/

suneelpervaiz · Answer 4 · 2017-04-12T07:14:23.947

Passing variable is quiet simple in mysql query using java.

Write your query
and write the variable in ""
In my case i am passing 'conition' and 'tablename' dynamically.

Thank you very much have a good day.

@Override public LinkedList getNameList(String condition, String tableName, String projectName) { // TODO Auto-generated method stub

String query = "select distinct("+condition+") as name  from "+tableName+" ";

//System.out.println(query);

ResultSet rs = null;
PreparedStatement preparedStatement = null;
Connection connection = null;

LinkedList finalList = new LinkedList();
try{
    connection = dataSourceAbacus.getConnection();
    preparedStatement = connection.prepareStatement(query);
    rs= preparedStatement.executeQuery();
    while(rs.next()){

        finalList.add(rs.getString("name"));
    }
}catch(Exception e){
    e.printStackTrace();
}finally{
    if(connection !=null){
        try {
            connection.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    if(preparedStatement != null){
        try{
            preparedStatement.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }
    if(rs != null){
        try{
            rs.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }
}

return finalList;

}

passing java string variable in mysql query

4 Answers4

Linked