Java database insertion fails

Question

My program should get data from xml files and put them in the db.

I use phpmyadmin mysql database.

I succeeded getting data from the XML, but when I try to put it in database it fails.

DBInput.java // JFrame with a button. when button is pressed program is supposed to put data in database.

package jSpyDroidEclipse;

import java.awt.BorderLayout;
import java.awt.EventQueue;

import javax.swing.JFrame;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.border.EmptyBorder;
import javax.swing.JButton;
import javax.swing.JFileChooser;

import java.awt.event.ActionListener;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.util.spi.CurrencyNameProvider;
import java.awt.event.ActionEvent;

public class DBInput extends JFrame {

private JPanel contentPane;
private File xmlFile;

/**
 * Launch the application.
 */


public String strStr(String haystack, String needle) {
      if(haystack==null || needle==null) return null; 
      int hLength=haystack.length(); 
      int nLength=needle.length(); 
      if(hLength<nLength) return null; 
      if(nLength==0) return haystack;
      for(int i=0; i<=hLength-nLength; i++)
      {
        if(haystack.charAt(i)==needle.charAt(0))
        {
          int j=0; 
          for(; j<nLength; j++)
          {
            if(haystack.charAt(i+j)!=needle.charAt(j))
            {
              break; 
            }
          }
          if(j==nLength) return haystack.substring(i) ; 
        }  
      }
      return null; 
    }


public static void main(String[] args) {
    EventQueue.invokeLater(new Runnable() {
        public void run() {
            try {
                DBInput frame = new DBInput();
                frame.setVisible(true);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    });
}

/**
 * Create the frame.
 */
public DBInput() {
    setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
    setBounds(100, 100, 450, 300);
    contentPane = new JPanel();
    contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
    setContentPane(contentPane);
    contentPane.setLayout(null);

    JButton b_browse = new JButton("Browse");
    b_browse.addActionListener(new ActionListener() {
        public void actionPerformed(ActionEvent arg0) {
            JFileChooser fileChooser = new JFileChooser();

             fileChooser.setCurrentDirectory(new java.io.File("user.home"));
                fileChooser.setDialogTitle("Select the XML file");
                fileChooser.setFileSelectionMode(JFileChooser.FILES_AND_DIRECTORIES);
                if (fileChooser.showOpenDialog(b_browse) == JFileChooser.APPROVE_OPTION) {
                    xmlFile = fileChooser.getSelectedFile();


                    BufferedReader bufferedReader = null;
                    try {
                        bufferedReader = new BufferedReader(new FileReader(xmlFile));
                    } catch (FileNotFoundException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }

                    String currentLine = null;
                    try {
                        currentLine = bufferedReader.readLine();
                    } catch (IOException e1) {
                        // TODO Auto-generated catch block
                        e1.printStackTrace();
                    }


                    StringBuilder xmlCode = new StringBuilder();
                    xmlCode.append(currentLine);

                    if((currentLine!=null) && !currentLine.equals(""))
                    {
                        try {
                            while((currentLine = bufferedReader.readLine())!=null)
                            {
                                xmlCode.append(currentLine);
                            }
                        } catch (IOException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                        }
                    }


                    String sXMLCode = xmlCode.toString();

                    DBConnect connect = new DBConnect();
                    while(sXMLCode!=null)
                    {

                        String adv_name = XMLParser.getItem(new String(sXMLCode), XMLParser._TAG_ADV_NAME);
                        String category = XMLParser.getItem(new String(sXMLCode), XMLParser._TAG_CATEGORY);
                        String curency = XMLParser.getItem(new String(sXMLCode), XMLParser._TAG_CURENCY);
                        String free_shiping = XMLParser.getItem(new String(sXMLCode), XMLParser._TAG_FREE_SHIPPING);
                        String gift = XMLParser.getItem(new String(sXMLCode), XMLParser._TAG_GIFT_INCLUDED);
                        String manufacturer = XMLParser.getItem(new String(sXMLCode), XMLParser._TAG_MANUFACTURER);
                        String price_no_vat = XMLParser.getItem(new String(sXMLCode), XMLParser._TAG_PRICE_NO_VAT);
                        String price_vat = XMLParser.getItem(new String(sXMLCode), XMLParser._TAG_PRICE_VAT);
                        String PRODUCT_AFF_LINK = XMLParser.getItem(new String(sXMLCode), XMLParser._TAG_PRODUCT_AFF_LINK);
                        String PRODUCT_CODE = XMLParser.getItem(new String(sXMLCode), XMLParser._TAG_PRODUCT_CODE);
                        String PRODUCT_DESC = XMLParser.getItem(new String(sXMLCode), XMLParser._TAG_PRODUCT_DESC);
                        String PRODUCT_NAME = XMLParser.getItem(new String(sXMLCode), XMLParser._TAG_PRODUCT_NAME);
                        String PRODUCT_PIC = XMLParser.getItem(new String(sXMLCode), XMLParser._TAG_PRODUCT_PIC);


                        String query = "INSERT INTO PRODUCTS(product_code, adv_name, category, manufacturer, gift_included, product_name, product_desc, product_aff_link, product_pic, price_no_vat, price_vat, free_shipping) VALUES("+
                                                            PRODUCT_CODE + "," + adv_name + "," + category + "," + manufacturer + "," + gift + "," + PRODUCT_NAME + "," + PRODUCT_DESC + "," + PRODUCT_AFF_LINK + "," + PRODUCT_PIC + "," + price_no_vat + "," + price_vat + "," + free_shiping + ");";

                        connect.insertData(query);                      


                        //shifting to next product
                        String code = xmlCode.toString();
                        sXMLCode = strStr(sXMLCode.substring(1), XMLParser._TAG_PRODUCT);
                        //connect.disconnect();
                        //connect = null;
                    }
                }
        }
    });
    b_browse.setBounds(164, 103, 97, 25);
    contentPane.add(b_browse);
    }
}

DBConnect.java

package jSpyDroidEclipse;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.mysql.jdbc.PreparedStatement;

public class DBConnect {

private Connection connection;
private Statement statement;
private ResultSet resultSet;
private PreparedStatement preparedStatement;

public DBConnect()
{
    try {
        Class.forName("com.mysql.jdbc.Driver");
        //TODO: hardcoded
        connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/admin", "root", "");
    } catch (ClassNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

}


// it runs the query and returns the dataset
// query is supposed to be a select statement
// TODO: CHECK query to be a select statement
public ResultSet selectData(String query)
{
    try {
        statement = connection.createStatement();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    try {
        resultSet = statement.executeQuery(query);
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    try {
        statement.close();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    try {
        resultSet.close();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    return resultSet;
}



//TODO: set type ResultSet and return inserted data
public void insertData(String query)
{
    try {
        preparedStatement = (PreparedStatement) connection.prepareStatement(query);
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    try {
        preparedStatement.execute();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }


    try {
        preparedStatement.close();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

public void updateData(String query)
{

}

public static void main(String[] args) {
     DBConnect connect = new DBConnect();
}

public void disconnect()
{
    try {
        connection.close();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    try {
        preparedStatement.close();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    try {
        resultSet.close();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    try {
        statement.close();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    connection = null;
    preparedStatement = null;
    resultSet = null;
    statement = null;
 }
}

I also tested insertion method without that while loop and it works.

Error message:

java.sql.SQLException: No value specified for parameter 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964‌)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:897‌)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:886‌)

have you tried debugging your application? Check your query and see if the number of parameters are matching or not. Check if none of the parameters are getting null value. If you are able to insert value without while loop, then check whether all parameters are present throughout the while loop. — Gaurang Deshpande
– Gaurang Deshpande, Commented Aug 9, 2017 at 14:58
@Gaurang Despande Paramaeters are not null for sure. I read it from a file and i double checked what's inside. I also checked the number of parameters. "No value specified for paramater 1"... I really don't know what that is supposed to mean. — Radu
– Radu, Commented Aug 9, 2017 at 15:00
Ok can you add line nuber shown by the stacktrace because your code is so long — Blasanka
– Blasanka, Commented Aug 9, 2017 at 15:01

Youcef LAIDANI · Accepted Answer · 2017-08-09 15:14:59Z

First : You are using PrepapredStatement with a wrong way,
Second: i assume that type values like PRODUCT_CODE, adv_name is String, and String should be between two quotes 'value'

To solve your problem, you can :

Instead of public void insertData(String query) you can create two method, one return the prepared statement, the second to close the the statement
for each object create a method which take your Object, call prepapred statement, execute the statement, and close the statement in the end

Here is a simple example :

String query = "INSERT INTO PRODUCTS(product_code, adv_name, category, manufacturer, "
        + "gift_included, product_name, product_desc, product_aff_link, "
        + "product_pic, price_no_vat, price_vat, free_shipping) VALUES(?, ?, ?, ....)";
try (PreparedStatement pstm = getPreparedStatement(query)) {
    pstm.setString(1, PRODUCT_CODE);
    pstm.setString(2, adv_name);
    pstm.setString(3, category);
    ...
    pstm.execute();
    closeStatement(pstm);
}

Steve Smith · Accepted Answer · 2017-08-09 15:14:45Z

2

You don't seem to be using apostrophes. At the least it should be something like:-

   String query = "INSERT INTO PRODUCTS(product_code, adv_name, category, manufacturer, gift_included, product_name, product_desc, product_aff_link, product_pic, price_no_vat, price_vat, free_shipping) 
   VALUES('"+ PRODUCT_CODE + "','" + adv_name + "', [etc]

..although this can leave you open to SQL injection attacks, so I would use parameters.

answered Aug 9, 2017 at 15:14

Steve Smith

2,2862 gold badges19 silver badges24 bronze badges

Comments

Blasanka · Accepted Answer · 2017-08-09 15:15:01Z

For sure in below line:

String query = "INSERT INTO PRODUCTS(product_code, adv_name, category, manufacturer, gift_included, product_name, product_desc, product_aff_link, product_pic, price_no_vat, price_vat, free_shipping) VALUES("+
                                                            PRODUCT_CODE + "," + adv_name + "," + category + "," + manufacturer + "," + gift + "," + PRODUCT_NAME + "," + PRODUCT_DESC + "," + PRODUCT_AFF_LINK + "," + PRODUCT_PIC + "," + price_no_vat + "," + price_vat + "," + free_shiping + ");";

You need to change your code like below:

'"+PRODUCT_CODE+"',

And other variables also.

You can feel this(String concatenation) is a terible way to query. You need to use PreparedStatement. This will also avoid SQL injection.

Collectives™ on Stack Overflow

Java database insertion fails

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related