java - 在 mysql 查询中传递 java 字符串变量

Question

如何在 sql 查询中传递 java 字符串变量。我已经完成了所有的 JDBC 连接。

我的sql数据库查询是

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

它不起作用。但是，如果我执行以下代码，它就可以工作了

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

现在告诉我应该如何将变量名传递给 sql 查询来执行它。请告诉我如何将变量 locationnames 传递给 comp.name。

我完整的 java 函数如下所示:locationCombo 表示在组合框中选择的项目。 CropCombo也表示同...

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

使用 PreparedStatement并绑定(bind) String 参数，

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) {
    }
  }
}

编辑(根据您的附加代码，将其更改为类似内容)

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