JavaWeb (Part 3: JDBC and MVC)

JDBC connects to different databases by simply loading different database driver packages, without having to worry about differences in database operation languages.

2. Write a JDBC program

① Create database tables


CREATE TABLE users(
    id INT PRIMARY KEY,
    `name` VARCHAR(40),
    `password` VARCHAR(40),
    email VARCHAR(60),
    birthday DATE
);
INSERT INTO users(id,`name`,`password`,email,birthday)
VALUES(1,'张三','123456','[email protected]','2000-01-01');
INSERT INTO users(id,`name`,`password`,email,birthday)
VALUES(2,'李四','123456','[email protected]','2000-01-01');
INSERT INTO users(id,`name`,`password`,email,birthday)
VALUES(3,'王五','123456','[email protected]','2000-01-01');
SELECT * FROM users;

② Import database dependencies


<!--mysql的驱动-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.31</version>
</dependency>

③ Use of JDBC:

Ⅰ. Load the database driver (the bridge between the Java program and the database)

Ⅱ. Get Connection, a connection between the Java program and the database

III. Create a Statement object to send SQL to the database, generated by Connection

IV. Write SQL statements (write different SQL statements according to business)

Ⅴ. Execute SQL (if you want to receive a return value, create a ResultSet object to save the query results after the Statement is executed)

VI. Closing the connection


public class JDBCTest {
    public static void main(String[] args) throws Exception {
        //配置信息
        //要连接的数据库URL（解决中文乱码问题：useUnicode=true&characterEncoding=utf8&useSSL=true）
        String url = "jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf8&useSSL=true";
        //连接的数据库时使用的用户名
        String username = "root";
        //连接的数据库时使用的密码
        String password = "123456";
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        
        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取与数据库的链接
            conn = DriverManager.getConnection(url, username, password);
            //3.获取用于向数据库发送sql语句的statement对象
            st = conn.createStatement();
            String sql = "select id,name,password,email,birthday from users";
            //4.向数据库发sql,并获取代表结果集的resultset对象
            //查：executeQuery    增删改：executeUpdate
            rs = st.executeQuery(sql);
            //5.取出结果集的数据
            while (rs.next()) {
                System.out.println("id=" + rs.getInt("id"));
                System.out.println("name=" + rs.getString("name"));
                System.out.println("password=" + rs.getString("password"));
                System.out.println("email=" + rs.getString("email"));
                System.out.println("birthday=" + rs.getDate("birthday"));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                //6.关闭链接，释放资源（先开后关）
                rs.close();
                st.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

3. Detailed analysis

(1) Database URL

jdbc:mysql://localhost:3306/xxx

jdbc	protocol
mysql	Sub-protocol
localhost:3306	Host:Port
xxx	database

Commonly used database URL address writing method:

Oracle writing: jdbc:oracle:thin:@localhost:1521:xxx
SqlServer写法：jdbc:microsoft:sqlserver://localhost:1433; DatabaseName=xxx
MySql writing method: jdbc:mysql://localhost:3306/xxx

(2) Connection class

Connection is used to represent the connection to the database, and Collection is the most important object in database programming.

All interactions between the client and the database are completed through the connection object, the common methods of this object:

createStatement()	Create a statement object that sends SQL to the database
prepareStatement(sql)	Create a PrepareSatement object to send precompiled SQL to the database
setAutoCommit(boolean autoCommit)	Set whether the transaction is automatically committed
commit()	Committing a transaction on a link
rollback()	Rollback transaction on this link

(3) Statement class

The Statement object is used to send SQL statements to the database. Common methods of the Statement object are:

executeQuery(String sql)	Used to send query statements to data and return ResultSet result set
executeUpdate(String sql)	Used to send insert, update, or delete statements to the database and return the number of rows updated in the database
execute(String sql)	Used to send arbitrary sql statements to the database
addBatch(String sql)	Put multiple SQL statements into one batch
executeBatch()	Send a batch of SQL statements to the database for execution

Notice:

To improve operating efficiency, the execute method is generally not used directly.

Instead, use the corresponding methods: executeQuery for query and executeUpdate for addition, deletion and modification.

(4) ResultSet class

ResultSet is used to represent the execution results of Sql statements.

Resultset When encapsulating the execution result set, theSimilar to tableThe way。

The ResultSet object maintains a cursor pointing to the table data row.Initially, the cursor is before the first row.。

Calling the ResultSet.next() method can make the cursor point to a specific data row, and calling the getxxx method to obtain the data of that row.

① Get any type of data

getObject(int index)	Get the Object object according to the specified number of columns
getObject(string columnName)	Get the Object object according to the specified attribute name

② Get data of a specified type, such as String type

getString(int index)	Get a String object according to the specified number of columns
getString(String columnName)	Get a String object according to the specified attribute name

③ How to scroll the result set:

next()	Move to the next line
Previous()	Move to the previous line
absolute(int row)	Move to the specified line
beforeFirst()	Move to the front of resultSet
afterLast()	Move to the end of resultSet

(5) Release resources

After the Jdbc program is finished running, remember to release the objects that interact with the database created during the program running.

These objects are usually ResultSet, Statement, and Connection objects.

Especially the Connection object, which is a very scarce resource and must be released immediately after use.

If the Connection cannot be closed promptly and correctly, it may easily lead to system downtime.

To ensure that the resource release code can run, the resource release code must also be placed in the finally statement.

4. SQL injection issues

There are two problems when using Statement for development:

① Frequent string concatenation is required, resulting in a high error rate


String username = "zhangsan";
String password = "123";
String sql = "select * from users where name='"+username+"' and password='"+password+"'";
ResultSet rs = st.executeQuery(sql);

② There is a potential risk of SQL injection

SQL injection: Inject illegal SQL statements into the data entered by the user, use clever techniques to splice strings, cause SQL short circuit, and thus steal database data.


public class SQLTest {
    public static void main(String[] args) throws Exception {
        // 正常登陆sql：select * from users where name='张三' and password ='123456'
        //login("张三","123456");
        // SQL 注入：select * from users where name='' and password ='123456' or '1'='1'
        login("", "123456' or '1'='1");
    }
 
    public static void login(String username, String password) throws Exception {
        String url = "jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String dbUsername = "root";
        String dbPassword = "123456";
 
        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取与数据库的链接
        Connection conn = DriverManager.getConnection(url, dbUsername, dbPassword);
        //3.获取用于向数据库发送sql语句的statement
        Statement st = conn.createStatement();
        String sql = "select * from users where name='" + username + "' and password='" + password + "'";
        System.out.println(sql);
        //4.向数据库发sql,并获取代表结果集的rs
        ResultSet rs = st.executeQuery(sql);
        if (rs.next()) {
            System.out.println("登录成功");
        } else {
            System.out.println("登录失败");
        }
        //6.关闭链接，释放资源
        rs.close();
        st.close();
        conn.close();
    }
}

operation result:

Notice:

In the SQL statement, and has a higher priority than or, so the executed SQL is equivalent to select * from users where '1' = '1';

Solution:Use the PreparedStatement subclass of Statement, which provides SQL placeholder functionality.

There is no need to concatenate strings, and the data entered by users will be fully checked, which is more secure.


public class PSTest {
    public static void main(String[] args) throws Exception {
        // 正常登陆sql：select * from users where name='张三' and password ='123456'
        //login("张三","123456");
        // SQL 注入：select * from users where name='' and password ='123456' or '1'='1'
        login("", "123456' or '1'='1");
    }
 
    public static void login(String username, String password) throws Exception {
        String url = "jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String dbUsername = "root";
        String dbPassword = "123456";
 
        Class.forName("com.mysql.cj.jdbc.Driver");
 
        Connection conn = DriverManager.getConnection(url, dbUsername, dbPassword);
        //获取用于向数据库发送预编译sql语句的prepareStatement
        String sql = "select * from users where name = ? and password = ?";
        System.out.println(sql);
        PreparedStatement ps = conn.prepareStatement(sql);
        //给占位符 ? 填充数据
        ps.setString(1, username);
        ps.setString(2, password);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            System.out.println("登录成功");
        } else {
            System.out.println("登录失败");
        }
        rs.close();
        ps.close();
        conn.close();
    }
}

operation result:

5.JDBC Transactions

A transaction refers to a logical set of operations, either all of which succeed or all of which fail (ACID principle).

When a Jdbc program obtains a Connection object from a database, by default this Connection object will automatically submit a transaction to the database.

If you want to turn off this default commit mode and execute multiple SQL statements in one transaction, you can use the following JDBC control transaction statement.

Connection.setAutoCommit(false);	Start transaction
Connection.rollback();	Rollback Transaction
Connection.commit();	Committing a transaction

① Create an account table


/*创建账户表*/
CREATE TABLE account(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(40),
    money DECIMAL(9,2)
);
/*插入测试数据*/
insert into account(name,money) values('A',1000);
insert into account(name,money) values('B',1000);
insert into account(name,money) values('C',1000);

② Simulate the business scenario when the transfer is successful


//失败后让数据库自动回滚事务
public class Demo {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String password = "123456";
        Connection conn = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(url, username, password);
 
            //通知数据库开启事务，false表示开启
            conn.setAutoCommit(false);
 
            String sql1 = "update account set money=money-100 where name = 'A' ";
            conn.prepareStatement(sql1).executeUpdate();
 
            //模拟执行完SQL1之后程序出现了异常而导致后面的SQL无法正常执行，事务也无法正常提交
            int x = 1/0;
 
            String sql2 = "update account set money=money+100 where name = 'B' ";
            conn.prepareStatement(sql2)executeUpdate();
 
            //sql1 和 sql2都顺利执行，就提交事务
            conn.commit();
            System.out.println("成功！！！");
        } catch (Exception e) {
            //出现异常，通知数据库回滚事务
            conn.rollback();
            e.printStackTrace();
        } finally {
            conn.close();
        }
    }
}

6.JDBC Tools

In different requests, you need to connect to the database and release resources each time, which will result in a lot of duplicate code.

Encapsulate the database connection preparation and release operations into a tool class and call it directly when using it to avoid writing duplicate code.


public class JdbcUtil {
    private static Connection connection;
    private static String url = "jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf8&useSSL=true";
    private static String username = "root";
    private static String password = "123456";
 
    //驱动（类）只需要加载一次，放静态代码块即可
    static {
        try {
            //加载数据库驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
 
    // 获取数据库连接对象
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }
 
    // 释放资源（利用多态：Statement 和 PreparedStatement 都可以传进来）
    public static void release(Connection conn, Statement st, ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (st != null) {
                st.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Call case, such as: add user


 
    public void add(String name, String password) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JdbcUtil.getConnection();
            String sql = "insert into users(name,password) values(?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, name);
            ps.setString(2, password);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.release(conn, ps, null);
        }
    }

7. Database connection pool

(1 Overview

JDBC development process:

Ⅰ. Load the database driver (only need to load once)

Ⅱ. Establish database connection (Connection)

III. Create a Statement object to send SQL to the database, generated by Connection

IV. Writing SQL statements

Ⅴ. Execute SQL (query --> ResultSet object receives result set)

VI. Close the connection and release resources

The database connection object is obtained through DriverManager. Each time it is obtained, it is necessary to apply to the database for connection and verify the username and password.

Each time a user makes a request, they need to obtain a connection from the database. However, creating a connection with the database usually consumes relatively large resources and takes a long time.

Each time a SQL statement is executed, the connection is disconnected, which causes a waste of resources and prevents the database connection resources from being reused well.

Assuming that the website has 100,000 visits a day, the database server will need to create 100,000 connections, which greatly wastes database resources and easily causes database server memory overflow and machine expansion.

Solution: Database connection pool

The basic idea of the database connection pool:

Create a buffer pool for the database and put a certain number of connection objects into the buffer pool in advance.

When you need to obtain a database connection, you only need to take an object from the buffer pool.

After use, it is put back into the buffer pool for the next request, achieving resource reuse without the need for repeated creation.

When there are no idle connection objects in the database connection pool, new requests will enter the waiting queue and wait for other threads to release the connection.

(2) Use

JDBC's database connection pool is implemented using the javax.sql.DataSource interface, which is an interface officially provided by Java.

When using it, developers do not need to implement the interface themselves and can use third-party tools.

C3P0 is a commonly used third-party implementation. In actual development, you can directly use C3P0 to complete the operation of the database connection pool.

Steps for usage:

① Import dependencies in pom.xml


	<dependency>
	    <groupId>com.mchange</groupId>
	    <artifactId>c3p0</artifactId>
	    <version>0.9.5.2</version>
	</dependency>

② Write code


public class DataSourceTest {
    public static void main(String[] args) {
        try {
            //创建C3P0数据库连接池
            ComboPooledDataSource dataSource=new ComboPooledDataSource();
            dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
            dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf8");
            dataSource.setUser("root");
            dataSource.setPassword("123456");
            //设置初始化连接个数
            dataSource.setInitialPoolSize(5);
            //设置最大连接个数（连接池中不够，可以继续申请，申请后最终的上限）
            dataSource.setMaxPoolSize(20);
            //当连接对象不够时，再次申请的连接对象个数
            dataSource.setAcquireIncrement(5);
            //设置最小连接数（当连接池中剩余2个连接对象时，就去申请 --> 提前做准备）
            dataSource.setMinPoolSize(2);
            Connection conn=dataSource.getConnection();
 
            //SQL操作...
 
            //将连接还回到数据库连接池中
            conn.close();
        } catch (PropertyVetoException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Notice:

Connection obtained in the traditional way: com.mysql.cj.jdbc.ConnectionImpl@3c153a1

Connection obtained by C3P0: com.mchange.v2.c3p0.impl.NewProxyConnection@6156496

so,Although both methods call the close method, the implementation classes are different, so the method rewriting is also different. This is interface polymorphism.。

The close method in C3P0 does not directly destroy the connection resources, but returns the connection to the database connection pool.

The above method of setting parameters for the database connection pool is written directly in the Java program.

This is the use ofhardcodeThe way,Each time you change the configurationRecompile required, generate new class files, the efficiency is too low。

In actual development, the configuration information of C3P0 is defined in an XML file, and the Java program only needs to load the configuration file to complete the initialization operation of the database connection pool.

To modify the configuration later, just modify the configuration in xml without recompiling.

Steps for usage:

① In the resources directory, create a new file named c3p0-config.xml

② Fill in the configuration information in c3p0-config.xml:


<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <!--配置连接池mysql-->
    <named-config name="C3P0Test">
        <!--   指定连接数据源的基本属性     -->
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbc?useUnicode=true&amp;characterEncoding=utf8</property>
        <property name="user">root</property>
        <property name="password">123456</property>
 
        <!--   设置初始化连接个数     -->
        <property name="initialPoolSize">5</property>
        <!--   设置最大连接个数（连接池中不够，可以继续申请，申请后最终的上限）     -->
        <property name="maxPoolSize">20</property>
        <!--   当连接对象不够时，再次申请的连接对象个数     -->
        <property name="acquireIncrement">5</property>
        <!--   设置最小连接数（当连接池中剩余2个连接对象时，就去申请 -> 提前做准备） -->
        <property name="minPoolSize">2</property>
    </named-config>
</c3p0-config>

③ Write Java programs


public class DataSourceTest {
    public static void main(String[] args) {
        try {
            //创建C3P0数据库连接池
            ComboPooledDataSource dataSource=new ComboPooledDataSource("C3P0Test");
            Connection conn=dataSource.getConnection();
            System.out.println(conn);
            //将连接还回到数据库连接池中
            conn.close();
        }catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Notice:

① The parameter in the ComboPooledDataSource constructor is the name attribute value of the named-config tag configured in c3p0-config.xml.

② At this time, the JDBC tool class can be modified as follows:


public class JdbcUtil {
    private static DataSource dataSource;
 
    static {
        dataSource = new ComboPooledDataSource("C3P0Test");
    }
 
    // 获取数据库连接对象
    public static Connection getConnection() throws SQLException {
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
 
    // 释放资源（利用多态：Statement 和 PreparedStatement 都可以传进来）
    public static void release(Connection conn, Statement st, ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (st != null) {
                st.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

8.DBUtils


    public static Student findById(Integer idx) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        Student stu = null;
        try {
            conn = JdbcUtil.getConnection();
 
            String sql = "select * from student where id = ?";
            PreparedStatement ps = conn.prepareStatement(sql);
            //给占位符 ? 填充数据
            ps.setInt(1, idx);
            rs = ps.executeQuery();
            //取出结果集的数据
            while (rs.next()) {
                Integer id = rs.getInt(1);
                String name = rs.getString(2);
                Double score = rs.getDouble(3);
                Date birthday = rs.getDate(4);
                stu = new Student(id, name, score, birthday);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                //关闭链接，释放资源
                rs.close();
                st.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return stu;
    }

In the above code, it is too troublesome to fill the placeholders with data and retrieve the data from the result set.

If there are 100 attributes in the Student table, then we will have to write 100 rows in the while loop to fetch the data, and it may also take many rows to fill the placeholders.

Solution:DBUtils can help developers complete data encapsulation (mapping of result sets to Java objects).

Steps for usage:

① Import dependencies in pom.xml


<dependency>
	<groupId>commons-dbutils</groupId>
	<artifactId>commons-dbutils</artifactId>
	<version>1.6</version>
</dependency>

② Write code


    public static Student findById(Integer idx) {
        Connection conn = null;
        Student stu = null;
        try {
            conn = JdbcUtil.getConnection();
 
            String sql = "select * from student where id = ?";
            //使用DBUtils
            QueryRunner qr = new QueryRunner();
            stu = qr.query(conn, sql, new BeanHandler<>(Student.class), idx);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                //关闭链接，释放资源
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return stu;
    }

At this point, filling the placeholder and retrieving the result set can be accomplished with two lines of code.

detail:

① The query method needs to pass in 4 parameters:

Connection Object

SQL Statements

ResultSetHandler interface implementation class object (the type of object to be converted: Student.class)

Fill in the placeholder parameters

② The ResultSetHandler interface is used to process result sets. It can convert the query result sets into Java objects and provides the following four implementation classes.

BeanHandler	Map the result set into a Java object (such as a Student object)
BeanListHandler	Map the result set into a List collection (such as: List<Student > ）
MapHandler	Map the result set into a Map collection object (ie: Map<String,Object> key: attribute name; value: attribute value)
MapListHandler	Map the result set into a MapList collection (ie: List <Map<<String,Object> >)

③ The parameter that fills the placeholder is a variable parameter, so any number of parameters can be passed in to meet different user needs.

④ The converted object class (Student class) must have a no-parameter constructor, otherwise the program will report an error.

reason:The bottom layer finds this class through Student.class, and then throughReflection MechanismFind the no-argument constructor of the class and create its object.

⑤ The name of the attribute in the class must be exactly the same as the name of the field in the database table.

Because after the object is created, when assigning values to the object attributes according to the result set, the search and assignment are performed according to the name.

11. MVC three-tier architecture

What is MVC?

Model: Model (service, dao, entity)
View: View (jsp, html, app client)
Controller: Controller (Servlet, Hander, Action)

After the request enters the Java Web application, the Controller receives the request, performs business logic processing, and finally returns the result to the user (View + Model).

1 Introduction

In the early years, for web application operations, users directly accessed the control layer, and the control layer could directly operate the database:

servlet--CRUD (create, delete, modify and check)-->database

In the servlet code: processing requests, responses, view jumps, processing JDBC, processing business code, processing logic code

Disadvantages:The program is very bloated and not conducive to maintenance

solution: There is nothing that can't be solved by adding a layer. If there is, add another layer!

2. MVC three-tier architecture

Model

Business processing: business logic (Service)
Data persistence layer: CRUD (DAO data persistence object)

view

Displaying data
Provide a link to initiate a Servlet request (a, form, img...)

Controller (Servlet)

Receive user request: (req, request parameters, session information)
Hand over the corresponding code to the business layer
Control view jump

Take user and administrator login as an example:

Controller layer:


@WebServlet("/login")
public class LoginServlet extends HttpServlet {
 
    private LoginService loginService = new LoginServiceImpl();
 
    /* 处理登录的业务逻辑*/
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String username = req.getParameter("username");
        String password = req.getParameter("password");
        String type = req.getParameter("type");
        Object object = loginService.login(username,password,type);
        if(object != null){
            HttpSession session = req.getSession();
            switch (type){
                case "reader":
                    Reader reader = (Reader) object;
                    session.setAttribute("reader",reader);
                    //跳转到用户的首页
                    resp.sendRedirect("/book?page=1");
                    break;
                case "admin":
                    Admin admin = (Admin) object;
                    session.setAttribute("admin",admin);
                    //跳转到管理员的首页
                    resp.sendRedirect("/admin?method=findAllBorrow&page=1");
                    break;
            }
        }else{
            resp.sendRedirect("login.jsp");
        }
    }
 
}

Service layer:


public interface LoginService {
    //利用多态，动态返回不同类型的对象
    public Object login(String username,String password,String type);
}


public class LoginServiceImpl implements LoginService {
 
    private ReaderRepository readerRepository = new ReaderRepositoryImpl();
    private AdminRepository adminRepository = new AdminRepositoryImpl();
 
    @Override
    public Object login(String username, String password,String type) {
        Object object = null;
        //业务逻辑处理：根据type的值，来选择调用不同的登录方法，去查找不同的表
        switch (type){
            case "reader":
                object = readerRepository.login(username,password);
                break;
            case "admin":
                object = adminRepository.login(username, password);
                break;
        }
        return object;
    }
}

Dao / Repository Layer:


public interface AdminRepository {
    public Admin login(String username,String password);
}


public interface ReaderRepository {
    public Reader login(String username,String password);
}


public class AdminRepositoryImpl implements AdminRepository {
    //管理员的登录方法（和数据库交互）
    @Override
    public Admin login(String username, String password) {
        Connection connection = JDBCTools.getConnection();
        String sql = "select * from bookadmin where username = ? and password = ?";
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Admin admin = null;
        try {
            statement = connection.prepareStatement(sql);
            statement.setString(1,username);
            statement.setString(2,password);
            resultSet = statement.executeQuery();
            if(resultSet.next()){
                admin = new Admin(resultSet.getInt(1),resultSet.getString(2),resultSet.getString(3));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCTools.release(connection,statement,resultSet);
        }
        return admin;
    }
}


public class ReaderRepositoryImpl implements ReaderRepository {
    //用户的登录方法（和数据库交互）
    @Override
    public Reader login(String username, String password) {
        Connection connection = JDBCTools.getConnection();
        String sql = "select * from reader where username = ? and password = ?";
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Reader reader = null;
        try {
            statement = connection.prepareStatement(sql);
            statement.setString(1,username);
            statement.setString(2,password);
            resultSet = statement.executeQuery();
            if(resultSet.next()){
                reader = new Reader(resultSet.getInt(1),resultSet.getString(2),resultSet.getString(3),resultSet.getString(4),resultSet.getString(5),resultSet.getString(6),resultSet.getString(7));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCTools.release(connection,statement,resultSet);
        }
        return reader;
    }
}

Technology Sharing