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

如何在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查询以执行此操作。Jst告诉我如何将变量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());

}

}

回答:

使用PreparedStatement并绑定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();

以上是 在mysql查询中传递java字符串变量 的全部内容, 来源链接: utcz.com/qa/419803.html

回到顶部