Twenty thousand words. The blogger worked hard to write it for you. I'm here for three consecutive ~ ~.
1, Introduction to JDBC
That is, Java Database Connectivity, Java and database links. Is an API that can execute SQL statements. JDBC is a specification, which provides a complete set of interfaces, allowing portable access to the underlying database, so different types of executable files can be written in Java
2, Preparatory work
If you want to do a good job, you must first sharpen your tools. Before starting MYSQL JDBC programming, we should make some preparations.
Programming language: Java
Database: MYSQL
Database driver package: different databases need to import different data packages, and the version of the database should be the same as that of the driver package under the large version, and the small version can be ignored. For example, if MYSQL uses version 5.x.x, the corresponding driver package should also be 5.x.x. I have version 8.xx here. A link is attached here.
3, JDBC five poison God palm
3.1 import driver package and create DataBase instance
1. Create a package in the project directory, which I call JDBC test5, and drag the downloaded driver package directly under the package.
Then click refactor to see your driver package under the package.
Then right-click the current package and find add as a library at the bottom. Click ok.
In this way, the driver package is completed.
Create a new Java.class file. This is JDBC 0925. In this file, first create the main function. then
Start creating DataSource instance. At the same time, set parameters for this DataSource, namely URL, user and password.
Here, a downward transformation is used to point the parent class instance to a subclass reference. You also need to convert the DataSource back to MysqlDatasource to set the URL, user name and password.
As the name suggests, datasource describes the source of data. When we create a datasource instance according to our URL, account name and password, we make a step of preparation for connecting to the database.
//1. Create DataSource instance
DataSource dataSource = new MysqlDataSource();
((MysqlDataSource)dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/java_100?characterEncoding=utf8&useSSL=false");
((MysqlDataSource)dataSource).setUser("root");
((MysqlDataSource)dataSource).setPassword(" ");
That's the problem. The downward transformation is so troublesome. Can we do without the transformation?
The answer is absolutely possible! However, if the scenario is written directly without transformation, the code changes will be very large if the database is to be replaced.
2. Establish a connection with the database.
//2. Establish connection with database
Connection connection = dataSource.getConnection();
3. Construct SQL statement
In JDBC, SQL is a string. If you want the database to recognize and execute SQL statements, you also need to convert this string type of SQL into a preparestatement object. This preparestatement object can help us dynamically construct SQL and check it.
Here, my sql statement is to query all the information of the student table.
//3. Construct SQL
String sql="select * from student";
PreparedStatement statement=connection.prepareStatement(sql);
4. Execute SQL statement
statement provides two series of execute. One is executeUpdate, which is used to add, delete and modify. Returns an int indicating how many rows can be affected.
The other is executeQuery. It is used for searching. Here I am searching, so Query is used. The lookup also requires a resultSet. Returns a result set, equivalent to a table.
//4. Execute SQL
ResultSet resultSet=statement.executeQuery();
//Traversal result set
//First take out each row in the result, and then take out the required columns in each row.
//The traversal code is much like an iterator
while (resultSet.next()){
int id=resultSet.getInt("id");
String name=resultSet.getString("name");
System.out.println(id+"\t"+name);
}
5. Release related resources
What are the relevant resources? Why release them? What should I pay attention to when releasing resources?
Related resources include connection, statement and resultSet. If you don't close related resources like this. It may lead to resource leakage. This is very similar to malloc in C language. If you do not close the connection. At present, the connection object has only memory, and the socket file descriptor held internally is difficult to release. This will also cause a situation similar to memory leakage.
The order in which resources are released needs to be the reverse of the order in which they are created. The order we create is connection, statement and resultSet. Therefore, the closing sequence should be as in the following code.
//5. Release related resources
resultSet.close();
statement.close();
connection.close();
4, Join SQL database
If you want to learn JDBC in this way, I guess you are husky thinking about small problems.
Wang Xiang!
In order to make readers better master JDBC programming, next I will demonstrate mysql library management system to you.
4.1 system function summary
Library management system:
1. Can represent the information of each book. Including the serial number, title, author, price and type of the book.
2. It can represent user information, which can be divided into two types: administrator and ordinary user
3. Different operations are provided for the above two users:
Ordinary users: view the book list, find specified books, borrow books and return books
Administrator user: View book list, add books, delete books
4.2 database preparation
1. Book list:
create table book( bookid int primary key auto_increment, name varchar(20), author varchar(20), -- Money is a decimal, but the floating-point number of decimal is difficult to store accurately in the computer, float,double Can not be accurately expressed -- decimal Type can accurately represent decimals, but the efficiency is greatly reduced -- Solution: still use int But the unit is cents, not dollars price int, type varchar(20), isBrrowed int );
2. User table:
drop table if exists user; create table user( userId int primary key auto_increment, username varchar(20), password varchar(20), --isAdmin When it is 1, it represents an administrator, and when it is 0, it represents an ordinary user isAdmin int );
Insert some data first:
-- Insert some data insert into book values(null,'Journey to the West','Wu Chengen',10000,'Classical novel',0); insert into book values(null,'Romance of the Three Kingdoms','Luo Guanzhong',10000,'Classical novel',0); insert into book values(null,'Water Margin','Shi Naian',10000,'Classical novel',0); insert into book values(null,'The Plum in the Golden Vase','Lanling Xiaosheng',10000,'Classical novel',0); -- Insert some users insert into user values(null,'admin','123',1); insert into user values(null,'zjw','123',0);
So the preliminary data is ready.
4.3 construct entity classes related to database
Entities in databases: users, books
Therefore, the corresponding user class and book class are also required in Java code.
Book codes are as follows:
package jdbcBook;
//Use this class to represent a book
/*+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| bookid | int | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| author | varchar(20) | YES | | NULL | |
| price | int | YES | | NULL | |
| type | varchar(20) | YES | | NULL | |
| isBrrowed | int | YES | | NULL | |
+-----------+-------------+------+-----+---------+----------------+*/
public class book {
private int bookId;
private String name;
private String author;
private int price;
private String type;
private int isBorrowed;
public int getBookId() {
return bookId;
}
public void setBookId(int bookId) {
this.bookId = bookId;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public int getPrice() {
return price;
}
public void setPrice(int price) {
this.price = price;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public int getIsBrrowed() {
return isBrrowed;
}
public void setIsBorrowed(int isBorrowed) {
this.isBorrowed = isBorrowed;
}
@Override
public String toString() {
return "book{" +
"bookId=" + bookId +
", name='" + name + '\'' +
", author='" + author + '\'' +
", price=" + price +
", type='" + type + '\'' +
", isBrrowed=" + isBrrowed +
'}';
}
}
User class:
package jdbcBook;
//Use this class to represent users
/*+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| userId | int | NO | PRI | NULL | auto_increment |
| username | varchar(20) | YES | | NULL | |
| password | varchar(20) | YES | | NULL | |
| isAdmin | int | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+*/
// Two different subclasses are used to distinguish isAdmin here
// The reason is that different identities, administrators and users support different methods
import Operations.IOperation;
abstract public class User {
private int userId;
private String userName;
private String password;
//Contains an array containing the supported operations
//Set different operations for different users
IOperation[]operations;
//The menus seen by ordinary users and administrators are also different
//An abstract method cannot have a method body. Subclasses are required to override this abstract method
//Abstract classes cannot be instantiated, they can only be inherited
//If a method is an abstract method, it must exist in an abstract class
abstract public int menu();
public int getUserId() {
return userId;
}
public void setUserId(int userId) {
this.userId = userId;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "User{" +
"userId=" + userId +
", userName='" + userName + '\'' +
", password='" + password + '\'' +
'}';
}
}
Administrator class:
package jdbcBook;
import Operations.*;
import java.util.Scanner;
public class Admin extends User{
public Admin(){
this.operations=new IOperation[]{
new ExitOperation(),
new DisplayOperation(),
new FindOperation(),
new AddOperation(),
new DelOperation(),
};
}
//Print administrator's menu
@Override
public int menu(){
System.out.println("~~~~~~~~~~~~~");
System.out.println("welcome "+this.getUserName()+"Administrator user");
System.out.println("Please select the operation you want to perform");
System.out.println("1,View a list of books");
System.out.println("2,View specified books");
System.out.println("3,New books");
System.out.println("4,Delete books");
System.out.println("0,Exit the system");
Scanner scanner=new Scanner(System.in);
int choice=scanner.nextInt();
return choice;
}
}
Ordinary users:
package jdbcBook;
import Operations.*;
import java.util.Scanner;
public class NormalUser extends User{
public NormalUser(){
//Initialize OPERATIONS
this.operations=new IOperation[]{
new ExitOperation(),
new DisplayOperation(),
new FindOperation(),
new BorrowOperation(),
new ReturnOperation(),
};
}
//Print menus for ordinary users
@Override
public int menu(){
System.out.println("~~~~~~~~~~~~~");
System.out.println("welcome"+this.getUserName());
System.out.println("Please select the operation you want to perform");
System.out.println("1,View a list of books");
System.out.println("2,View specified books");
System.out.println("3,Borrow books");
System.out.println("4,Return books");
System.out.println("0,Exit the system");
Scanner scanner=new Scanner(System.in);
int choice=scanner.nextInt();
return choice;
}
}
The above code is very similar to the pure Java library management system I have written before, almost exactly the same. Unfamiliar students can go and have a look. A link is attached here
Java library management system
The above four categories are the benchmark classes of library management system. Next, we begin to encapsulate the related operations of the database.
4.4 related operations of encapsulated database
Starting from the following, it is different from the pure Java version. Now write some JDBC related codes to let the program complete the addition, deletion, query and modification of the corresponding data table through these codes. Specific steps of implementation
1: Encapsulate the operation of establishing links with the database
1) : we create a separate class DButil to complete this operation
Remember the JBDC five poison God palm above? If you don't remember, look at the following code to get familiar with it.
We expect the URL, username and password defined here to be constants. So we use private, static and final to decorate them. Prevent further modification.
//Set URL
private static final String URL="jdbc:mysql://127.0.0.1:3306/bookmanager?characterEncoding=utf8&useSSL=false";
//Database user name
private static final String USERNAME="root";
//Database password
private static final String PASSWORD=" ";
(1) Before creating a Datasource instance, we want it to be constant like the user name, password and URL above, and to be created only once. So we also add the static modifier to it.
//After adding static, the DataSource becomes a class attribute. It has nothing to do with instances, but only with classes. //Class attributes are created only when the class is created. //When to create a class? Class loading phase, but for classes, a class is only loaded once. // Instance properties are created when each instance is created
private static DataSource dataSource=new MysqlDataSource ();
(2) Linked database
What we expect is that we can link the database directly even without creating a DBUtil instance, so we don't link the database in the form of construction methods, but use static code blocks.
//Static code block
static {
((MysqlDataSource)dataSource).setUrl (URL);
((MysqlDataSource)dataSource).setUser (USERNAME);
((MysqlDataSource)dataSource).setPassword (PASSWORD);
}
Provide a static method to link
//This method is also designed as static so that it can be called without creating a DBUtil instance
public static Connection getConnection() throws SQLException {
return dataSource.getConnection ();
}
When you run out, free up resources.
Apply after the sequence is adopted and close first.
}
//Release resources
public void close(Connection connection, PreparedStatement statement, ResultSet resultSet) {
if(resultSet!=null){
try {
resultSet.close ();
}catch (SQLException e){
e.printStackTrace ();
}
}
if(statement!=null){
try {
statement.close ();
}catch (SQLException e){
e.printStackTrace ();
}
}
if(connection!=null){
try {
connection.close ();
}catch (SQLException e){
e.printStackTrace ();
}
}
}
In case you miss any part of the code, I attach the complete code here
import com.mysql.cj.jdbc.MysqlDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
//Use this class to encapsulate the operation of establishing a link with the database
public class DBUtil {
//Set URL
private static final String URL="jdbc:mysql://127.0.0.1:3306/bookmanager?characterEncoding=utf8&useSSL=false";
//Database user name
private static final String USERNAME="root";
//Database password
private static final String PASSWORD=" ";
//1. Create DataSource instance
//After adding static, the DataSource becomes a class attribute. It has nothing to do with instances, but only with classes.
//Class attributes are created only when the class is created.
//When to create a class? Class loading phase, but for classes, a class is only loaded once.
// Instance properties are created when each instance is created
private static DataSource dataSource=new MysqlDataSource ();
//Use a constructor to set the Datasource property, OK? no way. Constructing methods requires instance objects, but we don't want to
//Static code block
static {
((MysqlDataSource)dataSource).setUrl (URL);
((MysqlDataSource)dataSource).setUser (USERNAME);
((MysqlDataSource)dataSource).setPassword (PASSWORD);
}
//Provides a way to establish links
//This method is also designed as static so that it can be called without creating a DBUtil instance
public static Connection getConnection() throws SQLException {
return dataSource.getConnection ();
}
//Release resources
public void close(Connection connection, PreparedStatement statement, ResultSet resultSet) {
if(resultSet!=null){
try {
resultSet.close ();
}catch (SQLException e){
e.printStackTrace ();
}
}
if(statement!=null){
try {
statement.close ();
}catch (SQLException e){
e.printStackTrace ();
}
}
if(connection!=null){
try {
connection.close ();
}catch (SQLException e){
e.printStackTrace ();
}
}
}
}
2: Package the addition, deletion, query and modification operations for the book table
Here I name it BookDao, which is a common naming habit
2.1: new books:
//1. New books
public boolean add (book book) {
//Insert the book object into the book table of the database
Connection connection = null;
PreparedStatement statement = null;
try {
//1. Establish database link
connection = DBUtil.getConnection ();
//2. Assembling sql statements
String sql = "insert into book values(null,?,?,?,?,?)";
statement = connection.prepareStatement (sql);
statement.setString (1, book.getName ());
statement.setString (2, book.getAuthor ());
statement.setInt (3, book.getPrice ());
statement.setString (4, book.getType ());
statement.setInt (5, book.getIsBorrowed ());
//3. Execute sql
int ret = statement.executeUpdate ();
if (ret != 1) {
//If the insertion fails, false is returned.
return false;
}
return true;
} catch (SQLException e) {
e.printStackTrace ();
} finally {
DBUtil.close (connection, statement, null);
}
return false;
}
2.2: view all books:
//2. View all books
public List<book> selectAll () {
List<book> books = new ArrayList<> ();
//Get all book records in the database table
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
//1. First establish a link with the database
connection = DBUtil.getConnection ();
//2. Splicing SQL
String sql = "select * from book";
statement = connection.prepareStatement (sql);
//3. Execute SQL
resultSet = statement.executeQuery ();
//4. Traversal result set
while (resultSet.next ()) {
//Take out these columns of the current row and construct a book object
book book = new book ();
book.setBookId (resultSet.getInt ("bookid"));
book.setName (resultSet.getString ("name"));
book.setAuthor (resultSet.getString ("author"));
book.setPrice (resultSet.getInt ("price"));
book.setType (resultSet.getString ("type"));
book.setIsBrrowed (resultSet.getInt ("isBrrowed"));
books.add (book);
}
} catch (SQLException e) {
e.printStackTrace ();
} finally {
DBUtil.close (connection, statement, resultSet);
}
return books;
}
2.3: find books by name
//3. Find books by name
public List<book> selectByName (String name) {
List<book> books = new ArrayList<> ();
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
//1. First establish a connection with the database
connection = DBUtil.getConnection ();
//2. Assemble SQL statements
String sql = "select * from book where name= ?";
statement = connection.prepareStatement (sql);
statement.setString (1, name);
//3. Execute SQL
resultSet = statement.executeQuery ();
//4. Traversal result set
while (resultSet.next ()) {
book book = new book ();
book.setBookId (resultSet.getInt ("bookid"));
book.setName (resultSet.getString ("name"));
book.setAuthor (resultSet.getString ("author"));
book.setPrice (resultSet.getInt ("price"));
book.setType (resultSet.getString ("type"));
book.setIsBrrowed (resultSet.getInt ("isBrrowed"));
books.add (book);
}
} catch (SQLException throwables) {
throwables.printStackTrace ();
} finally {
DBUtil.close (connection, statement, resultSet);
}
return books;
}
2.4: delete books
//4. Delete books
public boolean delete (int bookId) {
Connection connection = null;
PreparedStatement statement = null;
try {
//1. Establish link
connection = DBUtil.getConnection ();
//2. Assemble SQL
String sql = "delete from book where bookid= ?";
statement = connection.prepareStatement (sql);
statement.setInt (1, bookId);
//3. Execute SQL
int ret = statement.executeUpdate ();
if (ret != 1) {
return false;
}
return true;
} catch (SQLException throwables) {
throwables.printStackTrace ();
} finally {
DBUtil.close (connection, statement, null);
}
return false;
}
2.5: borrowing books
//5. Borrow books
public boolean borrowBook (int bookId) {
Connection connection = null;
PreparedStatement statement1 = null;
PreparedStatement statement2 = null;
ResultSet resultSet = null;
try {
//1. Link to database
connection = DBUtil.getConnection ();
//Start with advanced search books.
//2. Assemble SQL
//If the isBorrowed is already 1, can I borrow it?
//Can I borrow this book if it doesn't exist?
//So let's find out whether the book exists or not, whether it has been lent or not, and then say something else
String sql = "select * from book where bookid= ?";
statement1 = connection.prepareStatement (sql);
statement1.setInt (1, bookId);
//3. Execute SQL
resultSet = statement1.executeQuery ();
//4. The traversal result is expected to be 1 or 0
if (resultSet.next ()) {
//Therefore, only if judgment is needed here
boolean isBorrowed = (resultSet.getInt ("isBrrowed") == 1);
if (isBorrowed) {
System.out.println ("The book has been lent out! I can't borrow it bookId=" + bookId);
return false;
}
} else {
//Here, the manual does not exist
System.out.println ("The book doesn't exist. I can't borrow it bookId=" + bookId);
return false;
}
//The next step is to really borrow books
//5. Assemble SQL
sql = "update book set isBrrowed =1 where bookid = ?";
statement2 = connection.prepareStatement (sql);
statement2.setInt (1, bookId);
//6. Execute SQL
int ret = statement2.executeUpdate ();
if (ret != 1) {
return false;
}
return true;
} catch (SQLException throwables) {
throwables.printStackTrace ();
} finally {
if (resultSet != null) {
try {
resultSet.close ();
} catch (SQLException e) {
e.printStackTrace ();
}
}
if (statement2 != null) {
try {
statement2.close ();
} catch (SQLException e) {
e.printStackTrace ();
}
}
if (statement1 != null) {
try {
statement1.close ();
} catch (SQLException e) {
e.printStackTrace ();
}
}
}
return false;
}
2.6 returning books
//6. Return the book
public boolean returnBook (int bookId) throws SQLException {
Connection connection = null;
PreparedStatement statement1 = null;
PreparedStatement statement2 = null;
ResultSet resultSet = null;
try {
connection = DBUtil.getConnection ();
String sql = "select * from book where bookid = ?";
statement1 = connection.prepareStatement (sql);
statement1.setInt (1, bookId);
resultSet = statement1.executeQuery ();
if (resultSet.next ()) {
//I found the book
boolean isBorrowed = (resultSet.getInt ("isBrrowed") == 1);
if (!isBorrowed) {
System.out.println ("The book is not lent, so you don't have to return it bookid=" + bookId);
return false;
} else {
//The book does not exist
System.out.println ("The book does not exist bookid=" + bookId);
}
//Status of amendment
sql = "update book set isBrrowed = 0 where bookid = ?";
statement2=connection.prepareStatement (sql);
statement2.setInt (1, bookId);
int ret=statement2.executeUpdate ();
if(ret!=1){
return false;
}
return true;
}
}catch (SQLException e){
e.printStackTrace ();
}finally {
if (resultSet != null) {
try {
resultSet.close ();
} catch (SQLException e) {
e.printStackTrace ();
}
}
if (statement2 != null) {
try {
statement2.close ();
} catch (SQLException e) {
e.printStackTrace ();
}
}
if (statement1 != null) {
try {
statement1.close ();
} catch (SQLException e) {
e.printStackTrace ();
}
}
}
return false;
}
}
3: Encapsulate the addition, deletion, query and modification operations for the user table
It is similar to the above. It is named UserDao here
It mainly implements the logic of finding a password for the user name
import jdbcBook.Admin;
import jdbcBook.DBUtil;
import jdbcBook.NormalUser;
import jdbcBook.User;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class UserDao {
//It mainly implements the logic of finding a password for the user name
//The user name is unique. Only 1 or 0 can be found
//You should put a unique constraint on this line
public User selectByName(String name){
Connection connection=null;
PreparedStatement statement=null;
ResultSet resultSet=null;
try {
connection= DBUtil.getConnection ();
String sql="select * from user where username= ?";
statement=connection.prepareStatement (sql);
statement.setString (1, name);
resultSet=statement.executeQuery ();
if(resultSet.next ()){
//If there is a user name, a specific object is returned
//You need to decide whether to return an admin or normalUser according to whether the current user is an administrator
boolean isAdmin=resultSet.getInt ("isAdmin")==1;
User user=null;
if(isAdmin){
user=new Admin ();
}else {
user=new NormalUser ();
}
user.setUserId (resultSet.getInt ("userId"));
user.setUserName (resultSet.getString ("username"));
user.setPassword (resultSet.getString ("password"));
return user;
}
} catch (SQLException e) {
e.printStackTrace ();
}finally {
DBUtil.close (connection, statement, resultSet);
}
return null;
}
}
4.5 Operation package
The above bookDao and userDao are for the database. We will write the java code again, but this time we only need to write it before calling. More convenient
4.5.1 AddOperation
package Operations;
import java.util.Scanner;
import jdbcBook.BookDao;
import jdbcBook.book;
import java.awt.print.Book;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
public class AddOperation implements IOperation{
@Override
public void work(){
System.out.println ("New books");
Scanner scanner=new Scanner (System.in);
System.out.println ("Please enter the title of the book");
String bookName=scanner.next ();
System.out.println ("Please enter author");
String author=scanner.next ();
System.out.println ("Please enter price");
int price=scanner.nextInt ();
System.out.println ("Please enter a category");
String type=scanner.next ();
book book=new book ();
book.setName (bookName);
book.setPrice (price);
book.setAuthor (author);
book.setType (type);
BookDao bookDao=new BookDao ();
boolean ret=bookDao.add (book);
if(ret){
System.out.println ("Successfully added books");
}else {
System.out.println ("Failed to add book");
}
}
}
4.5.2 BorrowOperation
package Operations;
import jdbcBook.BookDao;
import java.util.Scanner;
public class BorrowOperation implements IOperation{
@Override
public void work() {
System.out.println ("Borrow books");
Scanner scanner=new Scanner (System.in);
System.out.println ("Please enter the books to borrow id");
int bookID=scanner.nextInt ();
BookDao bookDao=new BookDao ();
boolean ret=bookDao.borrowBook (bookID);
if(ret)
{
System.out.println ("success");
}else {
System.out.println ("fail");
}
}
}
4.5.3 DelOperation
package Operations;
import jdbcBook.BookDao;
import java.util.Scanner;
public class DelOperation implements IOperation{
@Override
public void work() {
System.out.println ("Delete books");
Scanner scanner=new Scanner (System.in);
System.out.println ("Please enter the to delete id : ");
int bookid=scanner.nextInt ();
BookDao bookDao=new BookDao ();
boolean ret=bookDao.delete (bookid);
if(ret){
System.out.println ("Delete succeeded");
}else {
System.out.println ("Deletion failed");
}
}
}
4.5.4 DisplayOperation
package Operations;
import jdbcBook.BookDao;
import jdbcBook.book;
import java.util.List;
public class DisplayOperation implements IOperation{
@Override
public void work() {
System.out.println ("Show all books");
BookDao bookDao=new BookDao();
List<book> books=bookDao.selectAll ();
for (book book:books) {
System.out.println (book);
}
System.out.println ("This is all the books");
}
}
4.5.5 ExitOperation
package Operations;
public class ExitOperation implements IOperation{
@Override
public void work(){
System.out.println ("Exit program");
System.exit (0);
}
}
4.5.6 FindOperation
package Operations;
import jdbcBook.BookDao;
import jdbcBook.book;
import java.awt.print.Book;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
public class FindOperation implements IOperation{
@Override
public void work() {
System.out.println ("Find the books you want by name");
System.out.println ("Please enter the title of the book you want to find: ");
Scanner scanner=new Scanner (System.in);
String bookName=scanner.next ();
BookDao bookDao=new BookDao ();
List<book> bookList=bookDao.selectByName (bookName);
for (book book:bookList) {
System.out.println (book);
}
System.out.println ("Search complete");
}
}
4.5.7 IOperation
This is an interface. Which operations above implement it
package Operations;
import java.sql.SQLException;
public interface IOperation {
void work() throws SQLException;
}
4.5.8 ReturnOperation
package Operations;
import jdbcBook.BookDao;
import java.sql.SQLException;
import java.util.Scanner;
public class ReturnOperation implements IOperation{
@Override
public void work() throws SQLException {
System.out.println ("Return books");
Scanner scanner=new Scanner (System.in);
System.out.println ("Please enter the books to borrow id");
int bookID=scanner.nextInt ();
BookDao bookDao=new BookDao ();
boolean ret=bookDao.returnBook (bookID);
if(ret)
{
System.out.println ("success");
}else {
System.out.println ("fail");
}
}
}
4.6 Main
This is the backbone of the whole program. Everything is scheduled by it.
A login link is simulated here.
import jdbcBook.User;
import java.sql.SQLException;
import java.util.Scanner;
public class Main {
public static void main (String[] args) throws SQLException {
//Log in through login
User user=login();
while (true){
int choice=user.menu ();
user.doOperation(choice);
}
}
private static User login(){
Scanner scanner=new Scanner (System.in);
System.out.println ("enter one user name");
String username=scanner.next ();
System.out.println ("Please input a password");
String password=scanner.next ();
//2. Check the password from the database according to the user name, right
UserDao userDao=new UserDao ();
User user=userDao.selectByName (username);
if(user==null){
//non-existent
System.out.println ("Login failed, user does not exist");
//Exit program
System.exit (1);
}
if(!user.getPassword ().equals (password)){
//Wrong password
System.out.println ("Login failed, wrong password");
System.exit (1);
}
return user;
}
}
5, Final effect
Kung Fu pays off. Those who can see here must be students who love learning very much. Let's see the final result.
1: Use zjw ordinary users to show the display methods of ordinary users
2: Use zjw ordinary users to show ordinary users how to view specified books
3: Use zjw ordinary users to show how ordinary users borrow books
4: Use zjw ordinary users to show how ordinary users return books
5: Use the admin administrator user to show the administrator user's method of returning new books
