Implement CRUD operations for student table using prepared statements. Ask for user input where applicable. [Tu question ]
Before building the project you need to follow the below step, CRUD operations using prepared statements
1. Start your 'Xampp'
2. At first create the database using sql query which is inside 'src' folder in 'script.sql' file.
or simply You can also import 'JavaJdbc.sql' and 'bank.sql' database.
3. Note:
a) Inside each source code:
If windows:
String url = "jdbc:mariadb://localhost:3306/onlinelab";
String username = "root";
String password= "";
If Linux:
String url = "jdbc:MySQL://localhost:3306/onlinelab";
String username = "db_username"; --> your localhost username.
String password= "db_password"; --> your localhost password
4) Extra(Not needed here in my project):
- I have also attached mariadb connector jar file. If you are creating your own project
and want to connect database with your project then you must implement the connection
using mariadb connector jar file.
- To implement it follow given link:-
For Netbeans:
https://www.youtube.com/watch?v=9BqHWF_-Fk0
For Intellij IDEA
https://www.youtube.com/watch?v=T5Hey0e2Y_gCreate Database using the following script or you can manually create student table with database name onlinelab.
create database onlinelab;
use onlinelab;
CREATE TABLE student (
id int NOT NULL AUTO_INCREMENT,
name varchar(25) NOT NULL,
district varchar(25),
age int,
PRIMARY KEY (id)
);Prepared Statements Code in java for CRUD Operations
Create Prepare Statement :
package database;
import java.sql.*;
import java.util.Scanner;
public class CreatePreparedStatement {
public static void main(String[] args) throws SQLException {
//********************************************* Establishing Connection to the Database ************************************************
String url = "jdbc:mariadb://localhost:3306/onlinelab";
String username = "root";
String password = "";
Connection connection = DriverManager.getConnection(url, username, password);
//************************************ Insert into students table according to user input ****************************************************************************
Scanner in = new Scanner(System.in);
System.out.println("Enter Name:");
String name = in.next();
System.out.println("Enter District:");
String district = in.next();
System.out.println("Enter Age:");
int age = in.nextInt();
//************************** Sql prepared statement *****************************************
String sql = String.format("Insert into student (name,district,age) Values (?,?,?)");
PreparedStatement statement = connection.prepareStatement(sql);
//****************** Binding Parameter ******************************************************
statement.setString(1,name);
statement.setString(2,district);
statement.setInt(3,age);
int rowInserted = statement.executeUpdate();
//******************** message for user if rows are successfully updated or not **********
if(rowInserted>0)
System.out.println("Row Inserted Successfully!");
else
System.out.println("Row Insertion Failed!!");
statement.close();
connection.close();
}
}Read Prepared Statement
package database;
import java.sql.*;
import java.util.Scanner;
public class ReadPreparedStatement
{
public static void main(String[] args) throws SQLException
{
//********************************************* Establishing Connection to the Database ************************************************
String url = "jdbc:mariadb://localhost:3306/onlinelab";
String username = "root";
String password = "";
Connection connection = DriverManager.getConnection(url, username, password);
// **************** If user want to select but he/she don't know what values are inside database table ***********************************
System.out.println("Do You want to see database table before making Selection Query??(Y/N):");
Scanner in = new Scanner(System.in);
char userChoice = in.next().charAt(0);
if (userChoice == 'Y')
{
String sql = "select * from student";
PreparedStatement statement = connection.prepareStatement(sql);
ResultSet resultSet = statement.executeQuery();
while (resultSet.next())
{
System.out.printf("%d, %s, %s, %s, n",
resultSet.getInt("id"),
resultSet.getString("name"),
resultSet.getString("district"),
resultSet.getString("age")
);
}
statement.close();
}
// **************** If user want to select but he/she don't know what values are inside database table ***********************************
if(userChoice== 'N')
{
//************************** Taking user input *******************************
System.out.println("Enter the Id to select from database:");
int id = in.nextInt();
//********************** Sql Prepared Statement************************************
String sql = "select * from student where id = ?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setInt(1,id);
ResultSet resultSet = statement.executeQuery();
while (resultSet.next())
{
System.out.printf("%d, %s, %s, %s n",
resultSet.getInt("id"),
resultSet.getString("name"),
resultSet.getString("district"),
resultSet.getString("age")
);
}
statement.close();
}
connection.close();
}
}
Update Prepared Statement
package database;
import java.sql.*;
import java.util.Scanner;
public class UpdatePreparedStatement
{
public static void main(String[] args) throws SQLException {
//********************************************* Establishing Connection to the Database ************************************************
String url = "jdbc:mariadb://localhost:3306/onlinelab";
String username = "root";
String password = "";
Connection connection = DriverManager.getConnection(url, username, password);
// **************** If user want to update but he/she don't know what values are inside database table ***********************************
System.out.println("Do You want to see database table before updating??(Y/N):");
Scanner in = new Scanner(System.in);
char userChoice = in.next().charAt(0);
if (userChoice == 'Y')
{
String sql = "select * from student";
PreparedStatement statement = connection.prepareStatement(sql);
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
System.out.printf("%d, %s, %s, %s, n",
resultSet.getInt("id"),
resultSet.getString("name"),
resultSet.getString("district"),
resultSet.getString("age")
);
}
statement.close();
}
//******************* If user knows what to update in database table ****************************************************************************
if (userChoice == 'N')
{
//************************ Taking User Input ********************************
System.out.println("Enter the Id to update into database:");
int id = in.nextInt();
System.out.println("Enter the Name to update into database:");
String name = in.next();
System.out.println("Enter the District to update into database:");
String district = in.next();
System.out.println("Enter the Age to update into database:");
int age = in.nextInt();
//************************ Executing Sql statement **************************
String sql = "update student set name = ?,district= ?, age = ? where id= ?";
PreparedStatement statement = connection.prepareStatement(sql);
//****************** Binding Parameter ******************************************************
statement.setString(1,name);
statement.setString(2,district);
statement.setInt(3,age);
statement.setInt(4,id);
int rowUpdated = statement.executeUpdate();
//******************** message for user if rows are successfully updated or not **********
if (rowUpdated > 0)
System.out.println("Row updated Successfully!!");
else
System.out.println("Row isn't Updated!!");
statement.close(); //statement has to be close.
}
connection.close(); // connection is also closed.
}
}Delete Prepared Statements
package database;
import java.sql.*;
import java.util.Scanner;
public class DeletePreparedStatement
{
public static void main(String[] args) throws SQLException
{
//********************************************* Establishing Connection to the Database ************************************************
String url = "jdbc:mariadb://localhost:3306/onlinelab";
String username = "root"; //For connection we need 'username' and 'password'
String password = "";
Connection connection = DriverManager.getConnection(url, username, password);
// **************** If user want to select but he/she don't know what values are inside database table ***********************************
System.out.println("Do You want to see database table before making Selection Query??(Y/N):");
Scanner in = new Scanner(System.in);
char userChoice = in.next().charAt(0);
if (userChoice == 'Y') {
String sql = "select * from student";
PreparedStatement statement = connection.prepareStatement(sql);
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
System.out.printf("%d, %s, %s, %s, n",
resultSet.getInt("id"),
resultSet.getString("name"),
resultSet.getString("district"),
resultSet.getString("age")
);
}
}
// **************** If user want to select but he/she don't know what values are inside database table ***********************************
if (userChoice == 'N')
{
//********************* Taking input from user*********************
System.out.println("Enter the Id to Delete from database:");
int id = in.nextInt();
// ************* Sql prepared statement *************************
String sql = "delete from student where id= ?";
PreparedStatement statement = connection.prepareStatement(sql);
//************* Binding Parameters ***************************
statement.setInt(1, id);
int rowDeleted = statement.executeUpdate();
//******************** message for user if rows are successfully deleted or not ******
if (rowDeleted > 0)
System.out.println("Row Deleted Successfully!!");
else
System.out.println("Row isn't Deleted!!");
statement.close();
connection.close();
}
}
}
