Java with MySQL CRUD


Source Code

MySQLDemoPro.java
import java.sql.*;
import java.util.Scanner;
public class MySQLDemoPro {
 
	public static void main(String[] args) throws Exception  {
		Class.forName("com.mysql.jdbc.Driver");
		String  url="jdbc:mysql://localhost:3306/dbjoes?characterEncoding=utf8";
		String username="root";
		String password="root";
		Connection con =DriverManager.getConnection(url,username,password);
 
		Statement stmt =con.createStatement();
		ResultSet rs;
		PreparedStatement st;
 
		String qry="";
		int id,age,choice;
		String name,city;
 
		Scanner in = new Scanner(System.in);
		Scanner str = new Scanner(System.in);
 
		while(true)
		{
			System.out.println("MySQL Java CRUD Operation");
			System.out.println("1. Insert");
			System.out.println("2. Update");
			System.out.println("3. Delete");
			System.out.println("4. Select");
			System.out.println("5. Exit");
			System.out.print("Enter a choice: ");
			choice = in.nextInt();
			System.out.println("-----------------------------------------");
			switch(choice){
			case 1:
				System.out.println("1. Insert New Data");
 
				System.out.println("Enter Name : ");
				name=str.nextLine();
				System.out.println("Enter Age : ");
				age=in.nextInt();
				System.out.println("Enter City : ");
				city=str.nextLine();
 
				qry="insert into users (NAME,AGE,CITY) values(?,?,?)";
				st= con.prepareStatement(qry);
				st.setString(1, name);
				st.setInt(2, age);
				st.setString(3, city);
 
				st.executeUpdate();
				System.out.println("Data Insert Success");
 
				break;
			case 2:
				System.out.println("2. Updating a Data");
 
				System.out.println("Enter ID : ");
				id=in.nextInt();
				System.out.println("Enter Name : ");
				name=str.nextLine();
				System.out.println("Enter Age : ");
				age=in.nextInt();
				System.out.println("Enter City : ");
				city=str.nextLine();
 
				qry="update users set NAME=?,AGE=?,CITY=? where ID=?";
				st= con.prepareStatement(qry);
 
				st.setString(1, name);
				st.setInt(2, age);
				st.setString(3, city);
				st.setInt(4, id);
				st.executeUpdate();
				System.out.println("Data Update Success");
 
				break;
			case 3:
				System.out.println("3. Deleting a Data");
				System.out.println("Enter ID : ");
				id=in.nextInt();
 
				qry="delete from users where ID=?";
				st= con.prepareStatement(qry);
				st.setInt(1, id);
 
				st.executeUpdate();
				System.out.println("Data Deleted Success");
 
				break;
			case 4:
				System.out.println("4. Print all Records");
				qry="SELECT ID,NAME,AGE,CITY from users";
				rs=stmt.executeQuery(qry);
				while(rs.next())
				{
					id=rs.getInt("ID");
					name=rs.getString("NAME");
					age=rs.getInt("AGE");
					city=rs.getString("CITY");
 
					System.out.print(id+" ");
					System.out.print(name+" ");
					System.out.print(age+" ");
					System.out.println(city+" ");
 
				}
				break;
			case 5:
				System.out.println("Thank You");
				System.exit(0);
				break;
			default:
				System.out.println("Invalid Selection");
				break;
			}
			System.out.println("-----------------------------------------");
		}
	}
 
}
 
To download raw file Click Here

dbjoes.sql
CREATE DATABASE IF NOT EXISTS dbjoes;
USE dbjoes;
 
DROP TABLE IF EXISTS users;
CREATE TABLE users (
  ID int(11) NOT NULL AUTO_INCREMENT,
  NAME varchar(50) DEFAULT NULL,
  AGE int(11) DEFAULT NULL,
  CITY varchar(50) DEFAULT NULL,
  PRIMARY KEY (ID)
) ENGINE=InnoDB AUTO_INCREMENT=4;
 
 
INSERT INTO users (ID,NAME,AGE,CITY) VALUES 
 (1,'Ram Kumar',25,'Salem'),
 (2,'Sam Kumar',25,'Salem'),
 (3,'Rakesh',25,'Salem');
 
To download raw file Click Here

Basic Programs



Learn All in Tamil © Designed & Developed By Tutor Joes