Passing java string variable in mysql query

Question

How to pass java string variable in sql query. I did the whole JDBC connection.

My sql database query

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

He does not work. 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 to pass the variable name to the sql query in order to accomplish this. Jst tell me how to pass locationnames variables to comp.name.

My full java function looks like this: locationCombo denotes the item selected in combobox. CropCombo also means 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

Use PreparedStatement and bind 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 extra 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 2

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

Answer 3

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

The interface will 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); } 

POJO will 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 cartographer will 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 very easy to interact with your database and pass variables, and also make it so that your classes do not violate SRP

http://jdbi.org/sql_object_overview/

Answer 4

Passing a variable in a mysql query using java is very simple.

• Write your request
• and write the variable to ""
• In my case, I go through dynamically "conition" and "tablename".

• Thank you for a nice day.

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

   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; 

  }