1. Create a table
CREATE TABLE shs_users ( `id` int DEFAULT NULL, `name` varchar(255) DEFAULT NULL, `pwd` varchar(255) DEFAULT NULL, ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3; INSERT INTO shs_users (`id`, `name`, `pwd`) VALUES ('1', 'mm', '123'); INSERT INTO shs_users (`id`, `name`, `pwd`) VALUES ('2', 'yy', '666');
package JDBC.entity; public class UserEntity {<!-- --> private Long id; private String name; private String pwd; public UserEntity(String name, String pwd) {<!-- --> this.name = name; this.pwd = pwd; } public UserEntity(Long id, String name, String pwd) {<!-- --> this.id=id; this.name = name; this.pwd = pwd; } public Long getId() {<!-- --> return id; } public String getName() {<!-- --> return name; } public String getPwd() {<!-- --> return pwd; } public void setId(Long id) {<!-- --> this.id = id; } public void setName(String name) {<!-- --> this.name = name; } public void setPwd(String pwd) {<!-- --> this.pwd = pwd; } }
package JDBC.dao; import JDBC.entity.UserEntity; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class UserDao {<!-- --> public int registerUser(UserEntity userEntity) {<!-- --> Connection connection = null; Statement statement = null; try {<!-- --> //A.java connects to the mysql database to query all data //1. Import the mysql driver jar package; //2. Register driver javase reflection mechanism Class.forName() Class.forName("com.mysql.cj.jdbc.Driver"); //3. Get the database connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/shs_demo?serverTimezone=UTC", "root", "root"); //4. Get the executor object statement = connection. createStatement(); String insertUserSql = "INSERT INTO `shs_users` (`id`, `name`, `pwd`) VALUES (null, '" + userEntity.getName() + "', '" + userEntity.getPwd() + "') ;"; System.out.println("insertUserSql:" + insertUserSql); int result = statement. executeUpdate(insertUserSql); return result; } catch (Exception e) {<!-- --> e.printStackTrace(); return 0; }finally{<!-- -->// 7. Release jdbc resources try {<!-- --> if (statement != null) statement. close(); if (connection != null) connection. close(); } catch (Exception e) {<!-- --> e.printStackTrace(); } } } // query by name public UserEntity getByNameUser(String name){<!-- --> if (name==null ||name. length()==0){<!-- --> return null; } Connection connection=null; Statement statement=null; ResultSet resultSet=null; try {<!-- --> Class.forName("com.mysql.cj.jdbc.Driver"); connection=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/shs_demo?serverTimezone=UTC", "root", "root"); //4. Get the executor object statement = connection. createStatement(); String getByNameUserSql="SELECT * FROM shs_users WHERE `name`='" + name + "' "; System.out.println(getByNameUserSql); resultSet = statement. executeQuery(getByNameUserSql); boolean result = resultSet. next(); if (!result){<!-- --> return null; } Long dbId=resultSet. getLong("id"); String dbname=resultSet. getString("name"); String dbpwd=resultSet. getString("pwd"); return new UserEntity(dbId, dbname, dbpwd); }catch (Exception e){<!-- --> e.printStackTrace(); return null; }finally {<!-- --> try {<!-- --> if (statement!=null) statement. close(); if (connection!=null) connection. close(); if (resultSet!=null) resultSet. close(); }catch (Exception e){<!-- --> e.printStackTrace(); } } } public UserEntity login(UserEntity userEntity) {<!-- --> Connection connection = null; Statement statement = null; ResultSet resultSet = null; try {<!-- --> Class.forName("com.mysql.cj.jdbc.Driver"); connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/shs_demo?serverTimezone=UTC", "root", "root"); //4. Get the executor object statement = connection. createStatement(); String loginSql = "SELECT * FROM shs_users WHERE `name`='" + userEntity.getName() + "' and pwd='" + userEntity.getPwd() + "';"; System.out.println(loginSql); resultSet = statement. executeQuery(loginSql); boolean result = resultSet. next(); if (!result) {<!-- --> return null; } Long dbID = resultSet. getLong("id"); String dbName = resultSet. getString("name"); String dbPwd = resultSet. getString("pwd"); return new UserEntity(dbID, dbName, dbPwd); } catch (Exception e) {<!-- --> e.printStackTrace(); return null; } finally {<!-- --> try {<!-- --> if (connection != null) {<!-- --> connection. close(); } if (resultSet != null) {<!-- --> resultSet. close(); } if (statement != null) {<!-- --> statement. close(); } } catch (Exception e) {<!-- --> e.printStackTrace(); } } } }
package JDBC.service; import JDBC.dao.UserDao; import JDBC.entity.UserEntity; public class UserService {<!-- --> private UserDao userDao=new UserDao(); public int registerUser(UserEntity userEntity){<!-- --> String name=userEntity. getName(); UserEntity userEntity1=userDao.getByNameUser(name); if (userEntity1!=null){<!-- --> System.out.println("Username already exists"); return 0; } return userDao. registerUser(userEntity); } public UserEntity getByNameUser(String name){<!-- --> return userDao.getByNameUser(name); } public UserEntity login(UserEntity userEntity){<!-- --> return userDao. login(userEntity); } }
package JDBC.test; import JDBC.dao.UserDao; import JDBC.entity.UserEntity; import JDBC.service.UserService; import java.util.Scanner; public class UserTest {<!-- --> private UserService userService = new UserService(); public static void main(String[] args) {<!-- --> UserTest userTest = new UserTest(); userTest. index(); } public void index(){<!-- --> Scanner scanner = new Scanner(System.in); System.out.println("Please enter the number 1: user registration"); System.out.println("Please enter the number 2: user login"); int number = scanner. nextInt(); switch (number){<!-- --> case 1: registerUser(); break; case 2: login(); break; } } public void registerUser(){<!-- --> Scanner scanner = new Scanner(System.in); System.out.println("Please enter the name:"); String name=scanner. nextLine(); System.out.println("Please enter the password:"); String pwd = scanner. nextLine(); int result = userService. registerUser(new UserEntity(name,pwd)); if (result>0){<!-- --> System.out.println("User registered successfully"); }else {<!-- --> System.out.println("User registration failed"); } } public void login(){<!-- --> for (int i=1;i<=3;i + + ){<!-- --> Scanner scanner = new Scanner(System.in); System.out.println("Please enter the name:"); String name=scanner. nextLine(); System.out.println("Please enter the password:"); String pwd = scanner. nextLine(); UserEntity userEntity=userService.login(new UserEntity(name,pwd)); if (userEntity!=null){<!-- --> System.out.println("login successful"); return; }else {<!-- --> System.out.println("The name or password is incorrect, the number of errors: " + i); } } } }