I am creating a application using Java AND JDBC in eclipse IDE to insert user details like name, userId, DOB, Address, Phone number, etc, taken dynamically and to store in database table and then fetch it.
For date-of-birth (DOB) and phone number fields, I have to add validation, i.e. DOB should be between 2000-01-01 and 1940-01-01 and phone number should be exactly 10 digits.
this is the query used to create table--> String q = "create table UserDetails1 (userid int(20),name varchar(100),DOB DATE, address varchar(300),phone BIGINT, UserRole varchar(20)) " ;
Any help would be appreciated.
Below is the code I have done so far
public class UserData {
public static void main(String[] args) throws Exception {
int option = 0;
do {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase", "root", "root");
Scanner sc = new Scanner(System.in);
System.out.println("Select from the following options to interact with the user data :" '\n');
System.out.println("1. Insert user data in database");
System.out.println("2. Display all users data");
System.out.println("3. Delete specific user by Userid");
System.out.println("4. Display specific user data as per their Userid");
System.out.println("5. Exit " '\n');
option = sc.nextInt();
switch (option) {
case 1:
String y = " insert into UserDetails(userid,name,DOB,address,phone,UserRole) values(?,?,?,?,?,?)";
PreparedStatement st1 = con.prepareStatement(y);
System.out.println("Enter Userid");
int id1 = sc.nextInt();
System.out.println("Enter name");
String name1 = sc.next();
System.out.println("Enter DOB in YYYY-MM-DD format");
String dateOfBirth = sc.next();
Date date1 = Date.valueOf(dateOfBirth);
sc.nextLine();
System.out.println("Enter address ");
String address1 = sc.nextLine();
System.out.println("Enter 10 digit phone number");
double phone1 = sc.nextDouble();
System.out.println("Enter user role:");
String userrole1 = sc.next();
st1.setInt(1, id1);
st1.setString(2, name1);
st1.setDate(3, date1);
st1.setString(4, address1);
st1.setDouble(5, phone1);
st1.setString(6, userrole1);
st1.executeUpdate();
System.out.println("data inserted");
con.close();
break;
case 2:
case 3:
case 4:
case 5:
System.out.println("Exiting...." '\n');
break;
default:
System.out.println('\n' "Choose from the available options..! " '\n');
}
} while (option != 5);
}
}
CodePudding user response：
First, some general comments regarding your code.
Class.forName("com.mysql.cj.jdbc.Driver");
This is no longer needed.
public static void main(String[] args) throws Exception {
The only checked exception that method main throws is SQLException so you should change Exception (in the above code line) to SQLException.
You should use try-with-resources and subsequently remove this line:
con.close();
Create the Connection, PreparedStatement and Scanner outside of the do-while loop since you only need to create each of them once only and not repeatedly in every loop iteration.
After calling method nextInt() – and before calling next() – you need to call nextLine(). Refer to Scanner is skipping nextLine() after using next() or nextFoo()?. I suggest nextLine() together with Integer.parseInt(). Also, in my opinion, better to call method nextLine() rather than method next().
Now to your specific questions.
Variable phone1 should have type long and not type int since int cannot store the largest, possible, ten digit number. However phone numbers can have leading zeros. Since the data type of column phone in database table UserDetails is BIGINT, then, according to MySQL documentation, that database data type maps to [Java] long. Therefore phone number cannot have leading zeros so checking the value entered by the user is done using a simple if:
if (phone1 < 1000000000L || phone1 > 9999999999L)
In order to check date-of-birth (DOB), I suggest using LocalDate. Note that java.sql.Date contains both a date and a time and a time-zone. However, I assume that DOB is just a date without a time and hence the time-zone is not relevant. Therefore, you can convert java.sql.Date to LocalDate and call appropriate methods to determine if the entered date is within the valid range.
System.out.print("Enter DOB in YYYY-MM-DD format: ");
String dateOfBirth = sc.nextLine();
Date date1 = Date.valueOf(dateOfBirth);
LocalDate ld = date1.toLocalDate();
if (ld.isBefore(LocalDate.of(1940, 1, 1)) || ld.isAfter(LocalDate.of(2000, 1, 1))) {
// Invalid DOB
}
Refer to Convert between LocalDate and sql.Date
Here is my rewrite of your code. Note that it uses text blocks which were added in Java 15.
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.time.LocalDate;
import java.util.Scanner;
public class UserData {
public static void main(String[] args) throws SQLException {
LocalDate minDob = LocalDate.of(1940, 1, 1);
LocalDate maxDob = LocalDate.of(2000, 1, 1);
Scanner sc = new Scanner(System.in);
String sql = """
insert into UserDetails (userid, name, DOB, address, phone, UserRole)
values (?, ?, ?, ?, ?, ?)
""";
try (Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase",
"root",
"root");
PreparedStatement st1 = con.prepareStatement(sql)) {
int option = 0;
do {
System.out.println("Select from the following options to interact with the user data :" '\n');
System.out.println("1. Insert user data in database");
System.out.println("2. Display all users data");
System.out.println("3. Delete specific user by Userid");
System.out.println("4. Display specific user data as per their Userid");
System.out.println("5. Exit " '\n');
option = sc.nextInt();
switch (option) {
case 1:
System.out.print("Enter Userid: ");
int id1 = Integer.parseInt(sc.nextLine());
System.out.print("Enter name: ");
String name1 = sc.nextLine();
System.out.print("Enter DOB in YYYY-MM-DD format: ");
String dateOfBirth = sc.nextLine();
Date date1 = Date.valueOf(dateOfBirth);
LocalDate ld = date1.toLocalDate();
if (ld.isBefore(minDob) || ld.isAfter(maxDob)) {
System.out.printf("Not between %s and %s%n", minDob, maxDob);
break;
}
System.out.print("Enter address: ");
String address1 = sc.nextLine();
System.out.print("Enter 10 digit phone number: ");
long phone1 = Long.parseLong(sc.nextLine());
if (phone1 < 1000000000L || phone1 > 9999999999L) {
System.out.println("Not ten digits.");
break;
}
System.out.print("Enter user role: ");
String userrole1 = sc.nextLine();
st1.setInt(1, id1);
st1.setString(2, name1);
st1.setDate(3, date1);
st1.setString(4, address1);
st1.setLong(5, phone1);
st1.setString(6, userrole1);
st1.executeUpdate();
System.out.println("data inserted");
break;
case 2:
case 3:
case 4:
case 5:
System.out.println("Exiting...." '\n');
break;
default:
System.out.println('\n' "Choose from the available options..! " '\n');
}
} while (option != 5);
}
}
}