Exercise-Library Management System II (Establishing a database and setting up garbled filtering and JDBCUtil)

Insert picture description here

bookinfo

-- bookmanage.bookinfo definition

CREATE TABLE `bookinfo` (
  `bookid` varchar(50) NOT NULL COMMENT '图书编号',
  `bookname` varchar(50) NOT NULL COMMENT '图书名称',
  `publisher` varchar(50) NOT NULL COMMENT '出版社',
  `author` varchar(50) NOT NULL COMMENT '作者',
  `booktype` int(11) NOT NULL COMMENT '图书类别',
  `remain` int(11) NOT NULL COMMENT '剩余数量'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Insert picture description here

borrowinfo

-- bookmanage.borrowinfo definition

CREATE TABLE `borrowinfo` (
  `borrowid` varchar(50) NOT NULL COMMENT '借阅号',
  `bookid` varchar(50) NOT NULL COMMENT '图书编号',
  `borrower` varchar(50) NOT NULL COMMENT '借阅人',
  `phone` varchar(50) NOT NULL COMMENT '联系电话',
  `borrowtime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '借阅时间',
  `returntime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '归还时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Insert picture description here

Build package

Insert picture description here

EncodingFilter.java

package com.epoint.util;

import java.io.IOException;
import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.annotation.WebFilter;

/**
 * Servlet Filter implementation class EncodingFilter
 */
@WebFilter("/*")
public class EncodingFilter implements Filter {
	public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {
		//解决中文乱码问题
		request.setCharacterEncoding("utf-8");
		response.setCharacterEncoding("utf-8");
		chain.doFilter(request, response);
	}

}

jdbc.properties

url=jdbc:mysql://localhost:3306/bookmanage?serverTimezone=UTC
#这个可以缺省的,会根据url自动识别
driverClassName=com.mysql.jdbc.Driver
username=root
password=1127

##初始连接数,默认0
initialSize=5
#最大连接数,默认8
maxActive=10
#最小闲置数
minIdle=5
#获取连接的最大等待时间,单位毫秒
maxWait=2000
#缓存PreparedStatement,默认false
poolPreparedStatements=true
#缓存PreparedStatement的最大数量,默认-1(不缓存)。大于0时会自动开启缓存PreparedStatement,所以可以省略上一句设置
maxOpenPreparedStatements=20

JDBCUtil.java

package com.epoint.util;
/**
 * 
 * @Description JDBC工具类
 * @author loey  Email:[email protected]
 * @version 
 * @date 2021年6月1日下午18:22:47
 *
 */

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

import javax.sql.DataSource;

import com.alibaba.druid.pool.DruidDataSourceFactory;


public class JDBCUtil {

	private static DataSource dataSource;

	
	public DataSource getDataSource() {
		if(null == dataSource) {
			//访问配置文件
			try {
				Properties properties = new Properties();	
				properties.load(JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties"));
				// 创建 数据库连接 池
				dataSource = DruidDataSourceFactory.createDataSource(properties);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		
		return dataSource;
	}
	
	/**
	 * 获取数据库连接池的连接
	 * @Description 
	 * @return
	 */
	public static Connection getConnection() {
		Connection connection = null;
		
		try {
			dataSource = new JDBCUtil().getDataSource();
			connection = dataSource.getConnection();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return connection;
	}
	
	/**
	 * 关闭连接
	 * @Description 
	 * @param conn
	 * @param pstat
	 * @param rs
	 */
	public static void closeConnection(Connection connection,PreparedStatement pStatement,ResultSet rSet) {
		try {
			if (null != rSet) {
				rSet.close();
			} 
			if (null != pStatement) {
				pStatement.close();
			} 
			if (null != connection) {
				connection.close();
			} 
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.epoint</groupId>
	<artifactId>bookmanage</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>war</packaging>


	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<maven-compiler-plugin-version>3.2</maven-compiler-plugin-version>
	</properties>


	<dependencies>
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>3.8.1</version>
			<scope>test</scope>
		</dependency>

		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>javax.servlet-api</artifactId>
			<version>3.1.0</version>
			<scope>provided</scope>
		</dependency>

		<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.2.3</version>
		</dependency>

		<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.46</version>
		</dependency>

		<!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>fastjson</artifactId>
			<version>1.2.73</version>
		</dependency>

	</dependencies>

	<build>
		<finalName>bookmanage</finalName>
		<plugins>
			<!-- 指定编译版本 -->
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>${maven-compiler-plugin-version}</version>
				<configuration>
					<source>1.8</source>
					<target>1.8</target>
					<encoding>${project.build.sourceEncoding}</encoding>
				</configuration>
			</plugin>
			<plugin>
				<groupId>org.apache.tomcat.maven</groupId>
				<artifactId>tomcat7-maven-plugin</artifactId>
				<version>2.2</version>
				<configuration>
					<port>8090</port>
					<uriEncoding>${project.build.sourceEncoding}</uriEncoding>
					<url>http://localhost:8090</url>
					<path>/${project.artifactId}</path>
					<server>tomcat7</server>
				</configuration>
			</plugin>
		</plugins>
	</build>

</project>