1. Environment preparation before system development
1. Download Mysql
Download address: https://dev.mysql.com/downloads/mysql/
Unzip the file to local
Add a my.ini file and a new data folder under this path
Edit the my.ini file
Configure environment variables
Note that it is the Path to edit the system variable.
Run cmd as administrator, or win + r and then ctrl + shift + enter
Enter the command: mysqld install
After successful installation, enter: mysqld --initialize --user=root --console
Note: The text after root@localhost at the end is the initialized root password, which must be remembered.
Start the MySQL service
Enter the command: net start mysql
Log in to MySQL database
Enter the command: mysql -u root -p, then press Enter and enter the password
The password is the password initialized after root@localhost above.
change Password
Enter the command: ALTER USER 'root'@'localhost' IDENTIFIED BY 'new password';
2. Download Navicat
Download and decompress to get 32-bit and 64-bit navicat premium 15 Chinese original programs and patch files; 64-bit is the second one
Taking 64-bit as an example, double-click the file “navicat150_premium_cs_x64.exe” to install the software and follow the prompts to install;
2. Create database
Open navicat
Double-click to create a new database.
If you already have a database file, you need to create a new database here, then select this database, right-click and click “Run SQL File”, select your database file, and you can complete the attachment of the database.
3. Java connection to database
1. Add a record to the database table
package com.hbnu.jdbc;
import java.sql.*;
public class Demo1 {<!-- -->
public static void main(String[] args) {<!-- -->
Connection connection=null;
Statement statement=null;
try {<!-- -->
//1.Register driver
DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
//2. Get the database connection object
String url="jdbc:mysql://localhost:3306/class2110?serverTimezone=GMT & amp;useSSL=false";
String user="root";
String password="123456";
connection=DriverManager.getConnection(url,user,password);
//3. Get the database operation object
statement=connection.createStatement();//Create database operation object
//4. Execute sql statement
String sql="insert into tb_user values('xxxten','Hubei',56233)";
int count=statement.executeUpdate(sql);
//5. Process the query result set
System.out.println("Affected" + count + "records");
} catch (SQLException e) {<!-- -->
e.printStackTrace();
}
//6. Close database resources
finally {<!-- -->
try{<!-- -->
if(statement!=null){<!-- -->
statement.close();
}
}catch (SQLException e){<!-- -->
e.printStackTrace();
}
if(connection!=null){<!-- -->
try {<!-- -->
connection.close();
} catch (SQLException e) {<!-- -->
e.printStackTrace();
}
}
}
}
}
2. Delete a record in the data table
package com.hbnu.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class Demo2 {<!-- -->
public static void main(String[] args) {<!-- -->
try {<!-- -->
DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
String url="jdbc:mysql://localhost:3306/class2110?serverTimezone=GMT & amp;useSSL=false";
String user="root";
String password="123456";
Connection connection = DriverManager.getConnection(url, user, password);
Statement statement = connection.createStatement();
String sql="delete from tb_user where id=1";
int count=statement.executeUpdate(sql);
System.out.println("Affected data table" + count + "data");
statement.close();
connection.close();
} catch (SQLException e) {<!-- -->
e.printStackTrace();
}
}
}
3. Modify a record in the data table
package com.hbnu.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class Demo3 {<!-- -->
public static void main(String[] args) {<!-- -->
try {<!-- -->
DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
String url="jdbc:mysql://localhost:3306/class2110?serverTimezone=GMT & amp;useSSL=false";
String user="root";
String password="123456";
Connection connection = DriverManager.getConnection(url, user, password);
Statement statement = connection.createStatement();
String sql="update tb_user set username='tpplus sprint' where id=2";
int count=statement.executeUpdate(sql);
System.out.println("Affected" + count + "records");
statement.close();
connection.close();
} catch (SQLException e) {<!-- -->
e.printStackTrace();
}
}
}
4. Query the records of the data table
package com.hbnu.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Demo4
{<!-- -->
public static void main(String[] args) {<!-- -->
try {<!-- -->
Class.forName("com.mysql.cj.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/class2110?serverTimezone=GMT & amp;useSSL=false";
String user="root";
String password="123456";
Connection connection = DriverManager.getConnection(url, user, password);
Statement statement = connection.createStatement();
String sql="select id,username,address,account from tb_user";
ResultSet resultSet=statement.executeQuery(sql);
//Process the query result set
while(resultSet.next()){<!-- -->
//Get data with specific type through specific field name
int id=resultSet.getInt("id");
String username=resultSet.getString("username");
String address=resultSet.getString("address");
double account=resultSet.getDouble("account");
System.out.println("id:" + id + "\tusername:" + username + "\taddress:" + address + "\taccount:" + account);
}
//Close database resources
resultSet.close();
statement.close();
connection.close();
} catch (Exception e) {<!-- -->
e.printStackTrace();
}
}
}
5. Create a case where a user logs in using an account and password
Database content such as:
package com.hbnu.jdbc;
import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.Scanner;
public class Demo5 {<!-- -->
public static void main(String[] args) {<!-- -->
//Interface for users to enter account number and password
Map<String,String>loginInfo=loginUI();
//Verify user's login information
boolean result=checkLoginInfo(loginInfo);
System.out.println(result?"Account and password are correct, login successful":"Account and password are incorrect, login failed");
}
private static boolean checkLoginInfo(Map<String, String> loginInfo) {<!-- -->
//Define a mark indicating login success or failure
boolean flag=false;
//Get user information from the collection and get the account number and password entered by the user
String username=loginInfo.get("username");
String password=loginInfo.get("password");
try {<!-- -->
Class.forName("com.mysql.cj.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/class2110?serverTimezone=GMT & amp;useSSL=false";
String user="root";
String pwd="123456";
Connection connection = DriverManager.getConnection(url, user, pwd);
//SQL injection will occur, so use prepareStatement
// Statement statement = connection.createStatement();
//Question mark is a placeholder
String sql="select * from tb_user where username=? and password=?";
//Precompile SQL statements
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//Assign a value to the placeholder. The subscript of the first question mark is 1
preparedStatement.setString(1,username);
preparedStatement.setString(2,password);
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()){<!-- -->
//If you can enter the while loop, it means that the account number and password entered by the user are correct.
flag=true;
break;
}
} catch (Exception e) {<!-- -->
e.printStackTrace();
}
return flag;
}
private static Map<String, String> loginUI() {<!-- -->
//Define a Map collection to store user accounts and passwords
Map<String,String>loginInfo=new HashMap<>();
//Get the account number and password entered by the user
Scanner scanner = new Scanner(System.in);
System.out.println("Please enter your account number:");
String username=scanner.nextLine();
System.out.println("Please enter your password:");
String password=scanner.nextLine();
//Save the account number and password entered by the user into the collection
loginInfo.put("username",username);
loginInfo.put("password",password);
//Send the collection of account numbers and passwords to the backend for verification
return loginInfo;
}
}
Continually updated. . .