A simple instance of JDBC connecting to MySQL has been written before, which can only be understood as simple. Some of these processes can be reused, such as loading drivers, establishing connections, releasing resources, etc. in order to improve the reusability of the code, we need to standardize the overall framework of the project.
The following student management system further expands JDBC connection to MySQL, using Properties and encapsulated methods. It may seem more complex, but it can improve the awareness of the whole process.
Refer to: JDBC connection MySQL instance
Refer to: Problem record
All files of the project have been uploaded to CSDN
Properties
properties (Java.util.Properties): used to read Java configuration files. Usually, configuration files are text files prefixed with. properties. The content format is "key = value" pair (leave a space on both sides of the equal sign), and the content is of String type.
Properties configuration file
Through this file, we need to obtain four parameters: JDBC driver, URL, user name and password. Storing these parameters in the configuration file can facilitate later maintenance of relevant configuration items.
Create a new configuration file named db Properties (db refers to database). After being created with IDEA, the icon is as follows:
Add the following information to the file, corresponding to the four required parameters:
driver = com.mysql.cj.jdbc.Driver url = jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT username = root password = root123
Ps: write driver according to the corresponding drive!
Load profile
Loading process:
- Create an I/O stream that reads the configuration file
- Instantiate the Properties object
- Use the load() method to get the parameter list from the input stream
- Use the getProperty() method of the Properties object to get the property value
Attribute loading is a common resource, which can be placed in a static block so that the static block can be executed when the project is started.
The load driver is also placed in the static block.
static {
try {
InputStream in = BaseDao.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(in);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
// Load driver
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
Relevant methods:
- Getproperty (string key), search for properties in this property list with the specified key. That is, get the value corresponding to the key through the parameter key.
- Load (InputStream instream) to read the property list (key and element pairs) from the input stream. Load the specified file (such as the db.properties file above) to obtain all key value pairs in the file for search by getproperty (string key).
- Setproperty (string key, string value), call the put method of Hashtable. He sets key value pairs by calling the put method of the base class.
BaseDAO
BaseDAO interface: connect to the database to implement operations related to addition, deletion, modification and query. If you connect multiple database tables, you need to write a class (DAO) for operation for each table. However, if you write a BaseDAO interface and let other DA implement it, a lot of redundant code will be reduced.
Since the student management system to be written below is relatively simple, I don't want to write the database in this BaseDAO. However, for convenience, I write the public methods of loading static blocks, database connection and closing resources in it.
Create a new DAO file in the src directory, and create a BaseDao in the file Java file.
Establish connection
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
close resource
public static void CloseConnection(Connection conn, PreparedStatement pstm, ResultSet rs) throws SQLException {
if (rs != null){
rs.close();
}
if (pstm != null){
pstm.close();
}
if (conn != null){
conn.close();
}
}
Integration code
First, the four parameter values are null by default and assigned in the static block.
Related Guide Package:
import java.io.InputStream; import java.sql.*; import java.util.Properties;
BaseDAO section:
public class BaseDAO {
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
static {
try {
InputStream in = BaseDao.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(in);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
// 1. Drive
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
// Get connection
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
public static void CloseConnection(Connection conn, PreparedStatement pstm, ResultSet rs) throws SQLException {
if (rs != null){
rs.close();
}
if (pstm != null){
pstm.close();
}
if (conn != null){
conn.close();
}
}
}
Database related
Table information
In order to meet the requirements, three tables are established. There is no connection between the tables, and the first field is the primary key. The information of each table is as follows:
Student information form: stu
Attributes: stu_id,stu_name,Java_score,Python_score,HTML_score
Student user table: stu_user
Attributes: stu_username,stu_password
teacher user table: teacher
Attribute: tea_username,tea_password
Student business
Student login
Student login: query whether there is information in the student user table according to the name and password entered by the student. The return value type is Boolean.
Method name: Stu_Login(String stu_username, String stu_password)
Detailed method:
public boolean Stu_Login(String stu_username, String stu_password){
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
BaseDAO baseDAO = new BaseDAO();
try {
connection = baseDAO.getConnection();
String sql = "select * from stu_user where `stu_username` = ? and `stu_password` = ? ";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,stu_username);
preparedStatement.setString(2,stu_password);
resultSet = preparedStatement.executeQuery();
if (resultSet.next()){
return true;
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
baseDAO.CloseConnection(connection, preparedStatement, resultSet);
} catch (SQLException e) {
e.printStackTrace();
}
}
return false;
}
Score query
Score query: query the score information in the student information table according to the name entered by the student. The return value type is ResultSet.
Method name: Stu_Select(String stu_name)
Code body:
String sql = "select * from stu where `stu_name` = ? ";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,stu_name);
resultSet = preparedStatement.executeQuery();
if (resultSet.next()){
System.out.println("Your transcript is as follows:");
System.out.println("Name: " + resultSet.getString("stu_name"));
System.out.println("Java Score: " + resultSet.getInt("Java_score"));
System.out.println("Python Score: " + resultSet.getInt("Python_score"));
System.out.println("HTML Score: " + resultSet.getInt("HTML_score"));
}else{
System.out.println("Select Failed");
}
Change Password
Change Password: change the password according to the name and new password entered by the student.
Method name: Stu_Update(String username, String password)
Code body:
String sql = "update `stu_user` set stu_password= ? where `stu_username`= ? ";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,password);
preparedStatement.setString(2,username);
int count = preparedStatement.executeUpdate();
if (count>0){
System.out.println("Password Update Successful! ");
}else {
System.out.println("Password Update Failed!");
}
Teacher business
Teacher login
Teacher login: query whether there is information in the teacher user table according to the name and password entered by the teacher. The return value type is Boolean.
Method name: Tea_Login(String tea_username, String tea_password)
Detailed method:
public boolean Tea_Login(String tea_username, String tea_password){
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
BaseDAO baseDAO = new BaseDAO();
try {
connection = baseDAO.getConnection();
String sql = "select * from teacher where `tea_username` = ? and `tea_password` = ? ";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,tea_username);
preparedStatement.setString(2,tea_password);
resultSet = preparedStatement.executeQuery();
if (resultSet.next()){
return true;
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
baseDAO.CloseConnection(connection, preparedStatement, resultSet);
} catch (SQLException e) {
e.printStackTrace();
}
}
return false;
}
Query student information
Query student information: query whether there is information in the teacher user table according to the name and password entered by the teacher. The return value type is ResultSet.
Method name: Tea_Select()
Code body:
String sql = "select * from stu";
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
System.out.println("Your student information is as follows:");
System.out.println("ID : " + resultSet.getInt("stu_id"));
System.out.println("Name: " + resultSet.getString("stu_name"));
System.out.println("Java Score: " + resultSet.getInt("Java_score"));
System.out.println("Python Score: " + resultSet.getInt("Python_score"));
System.out.println("HTML Score: " + resultSet.getInt("HTML_score"));
}
Add student
Add student: add a field to the student user table according to the name and password entered by the teacher, and return the number of rows affected by the value.
Method name: Tea_Insert(String stu_username, String stu_password)
Code body:
String sql = "insert into stu_user(stu_username, stu_password) values( ? , ? ) ";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,stu_username);
preparedStatement.setString(2,stu_password);
int count = preparedStatement.executeUpdate();
if (count>0){
System.out.println("Insert Successful! Number of affected rows: " + count);
}else {
System.out.println("Insert Failed!");
}
Delete student
Delete student: delete this field in the student user table according to the name and password entered by the teacher, and return the number of rows affected by the value.
Method name: Tea_Delete(String stu_username)
Code body:
String sql = "delete from stu_user where stu_username= ? ";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,stu_username);
int count = preparedStatement.executeUpdate();
if (count>0){
System.out.println("Delete Successful! Number of affected rows: " + count);
}else {
System.out.println("Delete Failed!");
}
Enter results
Enter grade: according to the name and grade information entered by the teacher, modify the corresponding fields in the student information table to return the number of rows affected by the value.
Method name: Tea_Update(String stu_name, Integer Java_score, Integer Python_score, Integer HTML_score)
Code body:
String sql = "update `stu` set `Java_score`= ? , `Python_score`= ? , `HTML_score`= ? where `stu_name`= ? ";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,Java_score);
preparedStatement.setInt(2,Python_score);
preparedStatement.setInt(3,HTML_score);
preparedStatement.setString(4,stu_name);
int count = preparedStatement.executeUpdate();
if (count>0){
System.out.println("Update Successful! Number of affected rows: " + count);
}else {
System.out.println("Update Failed!");
}
System management implementation
explain
Several important files and database tables are surrounded by boxes.
Student business realization
System.out.println("Please enter student user name:");
String stu_username = sc.next();
while(true) {
System.out.println("Please input a password:");
String stu_password = sc.next();
boolean stu_bool = stu_biz.Stu_Login(stu_username, stu_password);
if (stu_bool) {
System.out.println("Login succeeded! The current user is:" + stu_username);
do {
System.out.println("1.Query results");
System.out.println("2.Change Password");
System.out.println("3.Exit the system");
biz_choose = sc.nextInt();
switch (biz_choose) {
case 1:
stu_biz.Stu_Select(stu_username);
break;
case 2:
System.out.println("Please enter a new password");
String update_stu_password_1 = sc.next();
System.out.println("Please enter the new password again");
String update_stu_password_2 = sc.next();
if(update_stu_password_1.equals(update_stu_password_2)){
stu_biz.Stu_Update(stu_username, update_stu_password_1);
}else{
System.out.println("Passwords are inconsistent, modification failed!");
}
break;
case 3:
System.out.println("Quit successfully, bye!");
System.exit(0);
break;
}
System.out.println("Press 0 to return to the menu!");
biz_choose = sc.nextInt();
} while (biz_choose == 0);
} else {
System.out.println("Login failed!");
continue;
}
break;
}
Teacher business realization
System.out.println("Please enter the teacher user name:");
String tea_username = sc.next();
System.out.println("Please input a password:");
String tea_password = sc.next();
boolean tea_bool = tea_biz.Tea_Login(tea_username, tea_password);
if (tea_bool) {
System.out.println("Login succeeded! The current user is:" + tea_username);
do {
System.out.println("1.Query student information");
System.out.println("2.Add student");
System.out.println("3.Delete student");
System.out.println("4.Score entry");
System.out.println("5.Exit the system");
biz_choose = sc.nextInt();
switch (biz_choose) {
case 1:
tea_biz.Tea_Select();
break;
case 2:
System.out.println("Please enter the name of the added student:");
String insert_stu_username = sc.next();
System.out.println("Please enter the added student password:");
String insert_stu_password = sc.next();
tea_biz.Tea_Insert(insert_stu_username, insert_stu_password);
break;
case 3:
System.out.println("Please enter the name of the student to delete:");
String delete_stu_username = sc.next();
tea_biz.Tea_Delete(delete_stu_username);
break;
case 4:
System.out.println("Please enter the name of the student to be entered:");
String stu_name = sc.next();
System.out.println("Please enter Java_score:");
int Java_score = sc.nextInt();
System.out.println("Please enter Python_score:");
int Python_score = sc.nextInt();
System.out.println("Please enter HTML_score:");
int HTML_score = sc.nextInt();
tea_biz.Tea_Update(stu_name, Java_score, Python_score, HTML_score);
break;
case 5:
System.out.println("Quit successfully, bye!");
System.exit(0);
break;
}
System.out.println("Press 0 to return to the menu!");
biz_choose = sc.nextInt();
} while (biz_choose == 0);
} else {
System.out.println("Login failed!");
}
All files of the project have been uploaded to CSDN