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());
                    }

    }

Answer 1

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;
  

  }

Answer 2

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/

Answer 3

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();

Answer 4

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