January 21, 2019January 21, 2019 ocjp

JDBC MySql Data Insertion using PreparedStatement

JDBC

In our previous article of JDBC Connectivity we have been introduced with Connection, PreparedStatement , ResultSet. We performed Select Query in JDBC. Now we are going to demonstrate Insert Query using Form in Swing.

This time we have used Eclipse IDE for our work. As eclipse does not have any inbuilt library for MySQL JDBC Driver, we have to load Jar file as an external file.

We have created a new Java Project in Eclipse named jdbcdemo

As Eclipse Does not have inbuilt library you have to manage jar files for Mysql JDBC Connectivity. You can download the jar file from http://www.java2s.com/Code/Jar/c/Downloadcommysqljdbc515jar.htm if you don’t have or you can google also and download. Extract somewhere in your PC. Now Right Click on Project name >Properties >Java Build Path >Libraries >Add External Jars and Select the jar that you downloaded.

package jdbcdemo;

import java.awt.BorderLayout;
import java.awt.FlowLayout;
import java.awt.Font;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JSpinner;
import javax.swing.JTextArea;
import javax.swing.JTextField;

public class InsertData extends JFrame {

	Connection con=null;
	PreparedStatement ps=null;
	
	JLabel lblTitle;
	JLabel lblWebsiteName;
	JTextField txtWebsiteName;
	
	JLabel lblWebsiteDetails;
	JTextArea txtWebsiteDetails;
	
	JLabel lblWebsiteRank;
	JSpinner spnWebsiteRank;
	
	JButton btnSubmit;
	
	JPanel pnlTop;
	JPanel pnlCenter;
	JPanel pnlBottom;
	
	//Database Details 
	String dbusername="root";
     String dbpassword="";
     String databasename="dbjavademo";
     String port="3306" ;// mostly common for mysql
     
	
	public InsertData(){
	
	setLayout(new BorderLayout());
	
	
	
	pnlTop= new JPanel();
	pnlCenter= new JPanel();
	pnlBottom= new JPanel();
	
	
	this.add(pnlTop,BorderLayout.NORTH);
	this.add(pnlBottom,BorderLayout.SOUTH);
	this.add(pnlCenter,BorderLayout.CENTER);
	
	
	pnlTop.setLayout(new FlowLayout(FlowLayout.CENTER));
	
	lblTitle= new JLabel("WEBSITE MANAGEMENT");
	lblTitle.setFont(new Font("Serif", Font.PLAIN, 24));
	pnlTop.add(lblTitle);
	
	
	pnlCenter.setLayout(null);
	
	lblWebsiteName= new JLabel("Enter Webiste Name");
	lblWebsiteName.setBounds(10,10,150,30);
	pnlCenter.add(lblWebsiteName);
	
	txtWebsiteName= new JTextField();
	txtWebsiteName.setBounds(180,10,290,30);
	pnlCenter.add(txtWebsiteName);
	
	
	lblWebsiteDetails= new JLabel("Enter Webiste Details");
	lblWebsiteDetails.setBounds(10,60,150,30);
	pnlCenter.add(lblWebsiteDetails);
	
	txtWebsiteDetails= new JTextArea();
	txtWebsiteDetails.setBounds(180,60,290,130);
	pnlCenter.add(txtWebsiteDetails);
	
	
	lblWebsiteRank= new JLabel("Select Webiste Rank");
	lblWebsiteRank.setBounds(10,220,150,30);
	pnlCenter.add(lblWebsiteRank);
	
	spnWebsiteRank= new JSpinner();
	spnWebsiteRank.setBounds(180,220,90,30);
	pnlCenter.add(spnWebsiteRank);
	
	
	btnSubmit= new JButton("Save");
	btnSubmit.setBounds(180,260,90,50);
	pnlCenter.add(btnSubmit);
	
	setSize(700,600);
	setDefaultCloseOperation(DISPOSE_ON_CLOSE);
	
	
	
	
	btnSubmit.addActionListener(new ActionListener() {
		
		@Override
		public void actionPerformed(ActionEvent arg0) {
			// TODO Auto-generated method stub
			
			String webname= txtWebsiteName.getText().trim();
			String webdetails= txtWebsiteDetails.getText().trim();
			int webRank= Integer.parseInt(spnWebsiteRank.getValue().toString());
			
			//Here you can process your values
	
		try {
			Class.forName("com.mysql.jdbc.Driver");
			
			con=DriverManager.getConnection("jdbc:mysql://localhost:"+ port+"/"+ databasename,dbusername,dbpassword); //Connecting to Database
			
			String q="insert into tbldata(strwebsitename,strdetails,intrank) values (?,?,?)";
			ps= con.prepareStatement(q) ;// PreparedStatement Created
	        
			ps.setString(1, webname);
			ps.setString(2, webdetails);
			ps.setInt(3, webRank);
			
			int rowsAffected= ps.executeUpdate();
			
			if(rowsAffected>0) {
				
				JOptionPane.showMessageDialog(InsertData.this, "Data Inserted");
			}
			
			con.close();
				

		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		}
	});
	
	}
	
	public static void main(String []a) {
		
		InsertData mainform= new InsertData();
		mainform.setVisible(true);
	}
}

We have used the same table that we used in our previous article of ResultSet Selection. For Insert Update or Delete you have to use executeUpdate() not executeQuery().