Monday, July 29, 2013

Java and MySQL(Select, Insert, Update and Delete)

I'm using eclipse as my IDE for this example. I also added the MySQL connector for Java on its CLASSPATH via File, Properties, Run/Debug Settings, click class name, Edit, click ClassPath tab, User Entries, Add External Jars, then browse for the latest MySQL connector for java, in my case I'm using  "mysql-connector-java-5.1.23-bin.jar", open and apply. Then you can begin using "com.mysql.jdbc.Driver" .

You can download a copy of the MySQL J connector from the official MySQL site.

Database Name :  test
Table Name : User
Columns :    UserName and Password

Server: localhost
User: root
Password: root
Port: 3308


import javax.swing.*;
import java.awt.Dimension;
import java.awt.Toolkit;
import java.awt.event.*;
import java.sql.*;

public class login {

Connection con=null;
Statement st=null;
ResultSet rs=null;
JFrame fra1 = new JFrame("User Log-in");
JLabel lun = new JLabel("User Name: ");
JLabel lpw = new JLabel("Password : ");
JTextField txtun = new JTextField(10);
JTextField txtp = new JTextField(10);
JButton b = new JButton("Login");

public login(){
connect();
frame();
}

public void connect(){
try {
String driver="com.mysql.jdbc.Driver";
Class.forName(driver);
String db = "jdbc:mysql://localhost:3308/test";
con=DriverManager.getConnection(db,"root" , "root");
st=con.createStatement();
}
catch(Exception ex){
JOptionPane.showMessageDialog(null, ex.getMessage().toString());
}
}

public void frame(){
int w=250, h=150;
Dimension screen = Toolkit.getDefaultToolkit( ).getScreenSize( );
int x = (screen.width-w)/2;
int y = (screen.height-h)/2;
fra1.setSize(w,h);
fra1.setLocation(x, y);
fra1.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
//fra1.setLayout(new FlowLayout());
fra1.setVisible(true);
JPanel p = new JPanel();
p.add(lun);
p.add(txtun);
p.add(lpw);
p.add(txtp);
p.add(b);
fra1.add(p);

b.addActionListener(new ActionListener(){

public void actionPerformed(ActionEvent arg0) {
try {
String u = txtun.getText().trim();
String p = txtp.getText().trim();

//Guide to using Insert, Update and Delete
//String nonquery ="INSERT INTO User (`UserName`, `Password`) VALUES ('" + u + "', '" + p + "')";
//st.executeUpdate(nonquery);

String sql ="SELECT * FROM User WHERE `UserName` LIKE '" + u + "' AND `Password`
LIKE '" + p + "'";
rs=st.executeQuery(sql);

int c=0;
while(rs.next()){
//The code to retrieve date from column table
//JOptionPane.showMessageDialog(null,  "User Name : " + (String) rs.getObject("UserName"));
c+=1;
}

if (c>0){
JOptionPane.showMessageDialog(null,  "Record Found!");
}
Else {
JOptionPane.showMessageDialog(null, "Record NOT Found!");
}
}
catch(Exception ex){
JOptionPane.showMessageDialog(null, ex.getMessage().toString());
}
}});
}

public static void main(String[] args) {
new login();
}
}

0 comments: