This article is mainly a case review of the database. Students who don't understand the database can have a look. It's best to try it again. It will be helpful.
Database Version (I)
In order to review and consolidate the previous knowledge, I did this exercise, encountered problems and deepened my understanding.
This article is suitable for Java beginners. You are also welcome to correct the shortcomings contained in it. Thank you!
If you have any questions, please raise them in time or send me a private letter,
If you are interested, you can also encourage each other by private letter and wechat!
This is only the second edition. There is no database connection pool, which is very helpful for everyone to be familiar with data operation. In addition, the previous edition is the array edition. You can enter my home page to browse. You think it's OK. Please click like before you go.
The database connection pool version will be updated later. Please look forward to it!
Requirements: set up a student management system
1. Add student information
2. Display student information
3. Delete student information
4. Modify student information
5. View student information
6. Sorting
7. Exit
Idea:
- Create related databases and student tables.
- Create a student class, import and connect to the database.
- Write sql statements to realize relevant functions.
Problems and feelings
- Because the null pointer in the MT method is not passed as an exception, it is not directly passed as an exception. res = stmt.executeQuery(sql);
- The executeQuery(String sql) method is used when executing the query, while the executeUpdate() method is used for other addition, deletion and modification queries. The excute() method can be executed, but the return value is Boolean.
- When implementing relevant operations, pay attention to passing in Statement, Connection and ResultSet objects so that the method can be called to close later.
- When exiting, you need to close the resource.
Manage.java
package cn.xlb.db.dao;
import cn.xlb.db.domain.Student;
import cn.xlb.db.util.JDBCUtil;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
/*
@author XiHai ShengGe
*/
public class Manage {
public static void main(String[] args) throws SQLException {
Connection conn = null;
Statement stmt = null;
ResultSet res = null;
List<Student> list = null;
//Get database connection
try {
conn = JDBCUtil.getConnection();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
//Start the operation to avoid exiting after the program is used once
System.out.println("Welcome to the student management system\n" +
"1.Add student information\n" +
"2.Display student information\n" +
"3.Delete student information\n" +
"4.Modify student information\n" +
"5.Query student information\n" +
"6.sort\n" +
"7.sign out\n");
//Create a Scanner object to avoid duplicate creation in the loop body
Scanner s = new Scanner(System.in);
Manage manage = new Manage();
while (true) {
System.out.println("Please enter what you want to do:");
int i = s.nextInt();
switch (i) {
case 1:
manage.add(s,conn,stmt);
break;
case 2:
manage.show(conn, list,stmt,res);
break;
case 3:
manage.del(s,conn,res);
break;
case 4:
manage.update(s,stmt,conn,res);
break;
case 5:
manage.inquire(s,stmt,conn,res);
break;
case 6:
manage.sort(s,conn,stmt,res,list);
break;
case 7:
//Release resources
JDBCUtil.close(conn,stmt,res);
System.exit(0);
break;
default:
System.out.println("Please enter 1~7 Number between");
}
}
}
private void sort(Scanner s, Connection conn, Statement stmt, ResultSet res, List<Student> list) {
//The sorting here is to use SQL statements to get the sorted values. It is sorted in ascending order by default
String sql1 = "select * from student order by number";
String sql2 = "select * from student order by grade";
try {
System.out.println("Please enter sorting method(1 Indicates sorting by student number, and 2 indicates sorting by grade)");
int i = s.nextInt();
stmt = conn.createStatement();
switch (i){
case 1:
res = stmt.executeQuery(sql1);
break;
case 2:
res = stmt.executeQuery(sql2);
break;
default:
System.out.println("Please enter 1 or 2");
}
//Create a List collection to encapsulate the Student object
list = new ArrayList<Student>();
Student student = null;
while (res.next()) {
int number = res.getInt("number");
String name = res.getString("name");
int grade = res.getInt("grade");
student = new Student();
student.setNumber(number);
student.setName(name);
student.setGrade(grade);
list.add(student);
}
if (list != null) {
for (Student stu : list) {
System.out.println("Student number:" + stu.getNumber() + " full name:" + stu.getName() + " Achievements:" + stu.getGrade());
}
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
private void update(Scanner s, Statement stmt, Connection conn, ResultSet res) {
System.out.print("Please enter the student ID to be updated:");
int number = s.nextInt();
String sql1 = "select * from student where number = '"+ number +"'";
try {
stmt = conn.createStatement();
res = stmt.executeQuery(sql1);
if (res.next()){
System.out.println("Please enter your changed name");
String name = s.next();
System.out.println("Please enter the changed grade");
int grade = s.nextInt();
String sql = "update student set name = '"+ name +"' ,grade = '"+ grade+"' where number = '" + number +"'";
int i = stmt.executeUpdate(sql);
if (i>0){
System.out.println("Successfully updated");
}
}else {
System.out.println("Students without the student number");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
private void inquire(Scanner s, Statement stmt, Connection conn, ResultSet res) {
System.out.print("Please enter the student number to query:");
int number = s.nextInt();
String sql = "select * from student where number = '" + number +"';";
try {
//There is no value assigned to stmt, resulting in null pointer exception
stmt = conn.createStatement();
res = stmt.executeQuery(sql);
if (res.next()){
int num = res.getInt("number");
String name = res.getString("name");
int grade = res.getInt("grade");
Student student = new Student();
student.setNumber(num);
student.setName(name);
student.setGrade(grade);
System.out.println("Student number:" + student.getNumber() + " full name:" + student.getName() + " Achievements:" + student.getGrade());
}else {
System.out.println("Query failed");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
private void del(Scanner s, Connection conn, ResultSet res) {
System.out.println("Please enter the student number to delete the student information");
int number = s.nextInt();
System.out.println("Are you sure you want to delete?(1 Yes 2 no)");
int n = s.nextInt();
switch (n){
case 1:
String sql1 = "select * from student where number = ?";
String sql = "delete from student where number = ?";
try {
PreparedStatement pstmt = conn.prepareStatement(sql1);
pstmt.setInt(1,number);
res = pstmt.executeQuery();
if (res.next()){
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,number);
int i = pstmt.executeUpdate();
if (i>0){
System.out.println("Deleted successfully");
}else {
System.out.println("Delete failed");
}
}else {
System.out.println("Students without the student number");
}
pstmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
break;
case 2:
System.out.println("The delete operation has exited");
break;
default:
System.out.println("Please enter 1 or 2");
}
}
private static void show(Connection conn, List<Student> list,Statement stmt,ResultSet res) throws SQLException {
String sql = "select * from student";
stmt = conn.createStatement();
res = stmt.executeQuery(sql);
//Create a List collection to encapsulate the Student object
list = new ArrayList<Student>();
Student student = null;
while (res.next()) {
int number = res.getInt("number");
String name = res.getString("name");
int grade = res.getInt("grade");
student = new Student();
student.setNumber(number);
student.setName(name);
student.setGrade(grade);
list.add(student);
}
if (list != null) {
for (Student s : list) {
System.out.println("Student number:" + s.getNumber() + " full name:" + s.getName() + " Achievements:" + s.getGrade());
}
}
}
public void add(Scanner s,Connection conn,Statement stmt){
System.out.print("Please enter the student number to be added:");
int number = s.nextInt();
System.out.print("Please enter the name you want to add:");
String name = s.next();
System.out.print("Please enter the grade you want to add:");
int grade = s.nextInt();
String sql = "insert into student values(?,?,?)";
//Since number is the primary key, there is no need to check the duplicate
try {
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,number);
pstmt.setString(2,name);
pstmt.setInt(3,grade);
int i = pstmt.executeUpdate();
//Cannot use executeQuery() method
if (i > 0){
System.out.println("Added successfully");
}
pstmt.close();
} catch (SQLException throwables) {
//throwables.printStackTrace();
System.out.println("Insertion error, please check whether the student number is the same");
}
}
}
JDBCUtil.java
package cn.xlb.db.util;
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;
/*
@author XiHai ShengGe
*/
public class JDBCUtil {
private static String url;
private static String user;
private static String password;
private static String driver;
static {
try {
//Create a property set to obtain the key value pairs used to connect to the database in the configuration file
Properties pro = new Properties();
//Gets the class loader for this class
ClassLoader classLoader = JDBCUtil.class.getClassLoader();
//load resources
URL res = classLoader.getResource("jdbc.properties");
String path = res.getPath();
//Read attribute list from file byte input stream
pro.load(new FileReader(path));
url = pro.getProperty("url");
password = pro.getProperty("password");
user = pro.getProperty("user");
driver = pro.getProperty("driver");
//Returns the class object associated with the class or interface with the given string name.
Class.forName(driver);
}catch (IOException e){
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//Get database connection
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,user,password);
}
//close resource
public static void close(Connection conn,Statement stmt,ResultSet res){
//close(null,stmt,conn);
if (conn != null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (stmt != null){
try {
stmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (res != null){
try {
res.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
Student.java
package cn.xlb.db.domain;
/*
@author XiHai ShengGe
*/
public class Student {
private int number;
private String name;
private int grade;
public Student() {
}
public Student(int number, String name, int grade) {
this.number = number;
this.name = name;
this.grade = grade;
}
public int getNumber() {
return number;
}
public void setNumber(int number) {
this.number = number;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getGrade() {
return grade;
}
public void setGrade(int grade) {
this.grade = grade;
}
@Override
public String toString() {
return "Student{" +
"number=" + number +
", name='" + name + '\'' +
", grade='" + grade + '\'' +
'}';
}
}