1 事务处理
1.1 事务的四大特性(ACID)
1、原子性(Atomicity):事务中所有操作是不可再分割的原子单位。事务中所有操作要么全部执行成功,要么全部执行失败。
2、一致性(Consistency):事务执行后,数据库状态与其它业务规则保持一致。如转账业务,无论事务执行成功与否,参与转账的两个账号余额之和应该是不变的。
3、隔离性(Isolation):隔离性是指在并发操作中,不同事务之间应该隔离开来,使每个并发中的事务不会相互干扰。
4、持久性(Durability):一旦事务提交成功,事务中所有的数据操作都必须被持久化到数据库中,即使提交事务后,数据库马上崩溃,在数据库重启时,也必须保证通过某种机制恢复数据。
1.2 MySQL中开启和关闭事务
MySQL中开启事务的命令:start transaction
MySQL中关闭事务的命令:commit
事务提交之后使用
rollback
就不能实现想要的结果。
1.3 JDBC中完成事务处理
Connection接口中与事务相关的三个方法:
(1)void setAutoCommit(boolean autoCommit) throws SQLException
:设置是否自动提交事务,如果为true(默认值为true)表示自动提交,也就是每条执行的SQL都是一个单独的事务,如果设置false,那么就相当于开启了事务;
(2)void commit() throws SQLException
:提交结束事务;
(3)void rollback() throws SQLException
:回滚结束事务。
JDBC处理事务的代码格式:
try{
conn.setAutoCommit(false); //开启事务
...
...
conn.commit(); //try的最后提交事务
}catch(){
conn.rollback(); //回滚事务
}
同一事务中所有的操作,都在使用同一个Connection对象。
package org.lks.jdbc;
import java.sql.Connection;
import java.sql.SQLException;
import org.junit.jupiter.api.Test;
import org.lks.jdbcutil.JDBCUtils;
public class Demo {
@Test
public void fun1() {
transfer("lks", "zsl", 200);
}
/**
* transfer
* @param from
* @param to
* @param money
*/
public static void transfer(String from, String to, double money) {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
conn.setAutoCommit(false);
AccountDao accountDao = new AccountDao();
accountDao.updateBalance(conn, from, -money);
accountDao.updateBalance(conn, to, money);
if(true) {
throw new RuntimeException("Error");
}
conn.commit();
}catch(Exception e) {
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
throw new RuntimeException(e);
}finally {
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}
1.4 事务的隔离级别
1、事务的并发读问题
(1)脏读:读取到另一个事务未提交数据据;
(2)不可重复读:两次读取不一致;
(3)幻读(虚读):读到另一个事务已提交数据。
2、并发事务问题
因为并发事务导致的问题大致有五类,其中两类是更新问题,三类是读问题。
(1)脏读(dirty read):读到另外一个事务的未提交更新数据,即读取到了脏数据。
(2)不可重复读(unrepeatable read):对同一记录的两次读取不一致,因为另一个事务对该记录做了一个修改;
(3)幻读(虚读)(phantom raed):对同一张表的两次查询不一致,因为另一事务插入了一条记录。
3、不可重复读和幻读的区别
(1)不可重复读是读取到了另一个事务的更新;
(2)幻读是读取到了另一事务的插入(MySQL中无法测试到幻读)。
4、四大隔离级别
4个等级的事务隔离级别,在相同的数据环境下,使用相同的输入,执行相同的工作,根据不同的隔离级别,可以导致不同的结果。不同事务隔离级别能够解决的数据并发问题的能力是不同的。
(1)SERIALIZABLE(串行化)
|——不会出现任何并发问题,因为它是对同一数据的访问是串行,非并发访问的。
|——性能最差。
(2)REPEATABLE READ(可重复读)(MySQL默认)
|——防止脏读和不可重复读,不能处理幻读问题。
|——性能别SERIALIZAVLE好。
(3)READ COMMITTED(读已提交数据)(Oracle默认)
|——防止脏读,没有处理不可重复读,也没有处理幻读。
|——性能比REPEATABLE READ好
(4)READ UNCOMMITTED(读未提交数据)
|——可能出现任何事务并发问题
|——性能最好。
5、查看数据库隔离级别
MySQL的默认隔离级别为Repeatable read,可以通过下面语句查看:
命令:SELECT @@tx_isolation
也可以通过下面语句来设置当前连接的隔离级别:
命令:SET TRANSACTION isolationlevel [ 4 选 1]
;
6、JDBC设置隔离级别(使用Connection中的方法)
方法: void setTransactionIsolation(int level) throws SQLException
参数可选值:
(1)static final int TRANSACTION_READ_COMMITTED
(2)static final int TRANSACTION_REPEATABLE_READ
(3)static final int TRANSACTION_SERIALIZABLE
(4)static final int TRANSACTION_READ_UNCOMMITTED
2 连接池
2.1 dbcp连接池
用池来管理Connection,这可以重复使用Connection。有了池,我们就不用自己来创建Connection,而是通过池来获取Connection对象,当使用完Connection后,调用Connection的close()方法也不会真的关闭Connection,而是把Connection“归还”给池。池就可以在利用这个Connection对象了。
1、池参数(所有池参数都有默认值)
初始大小、最小空闲连接数、增量(一次创建的最小单位)、最大空闲连接数、最大连接数、最大等待时间。
2、四大连接参数
连接池也是使用四大连接参数来完成创建连接对象。
3、实现的接口
(1)连接池必须实现:javax.sql.DataSource接口
(2)连接池返回的Connection对象,它的close()方法不是关闭连接,而是把连接归还给池。
(3)连接池内部使用四大参数创建了连接对象,即mysql驱动提供的Connection。连接池使用mysql的连接对象进行了装饰,只对close()方法进行了增强,装饰之后的Connection的close()方法,用来把当前连接归还给池。
package org.lks.jdbc;
import java.sql.Connection;
import java.sql.SQLException;
import org.apache.commons.dbcp.BasicDataSource;
import org.junit.jupiter.api.Test;
public class dbcpDemo {
@Test
public void getConnection() {
BasicDataSource dataSource = new BasicDataSource();
dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/test_simple_practice?serverTimezone=UTC");
dataSource.setUsername("simple");
dataSource.setPassword("123");
try {
Connection conn = dataSource.getConnection();
System.out.println(conn.getClass().getName());
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
2.2 装饰者模式
1、对象增强的手段
(1)继承
|——使用继承实现会导致类的暴增。因为一种组合就是一个类,就算组合数量非常少也不适合,因为后期维护的时候类的数量也会暴增。
|——缺点:增强内容固定,被增强的对象也是固定的。
(2)装饰者模式
|——特点:增强的内容是固定的,但是被增强的对象是任意的。不知到被增强对象的具体类型时,可以使用。
|——应用:Java API中的IO流
|————FileInputStream:节点流,就是指和一个资源绑定在一起,例如文件。
|————BufferedInputStream:装饰流,创建时一定要给一个底层对象,然后会给这个底层对象添加缓冲区。
class MyConnection implements Connection{
private Connection conn; //底层对象,被增强对象
public MyConnection(Connection conn){ //通过构造器传递底层对象
this.conn = conn;
}
public Statement createStatement(){
return this.conn.createStatement();
}
//增强点
public void close(){
//把当前连接归还给池
}
}
(3)动态代理
|——被增强的对象可以切换,增强的内容也可以切换。
参考代码:代码
2.3 c3p0连接池的基本使用方式
c3p0中池类是:ComboPooledDataSource。
package org.lks.jdbc;
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.SQLException;
import org.junit.jupiter.api.Test;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class c3p0Demo {
@Test
public void getConnection() throws PropertyVetoException, SQLException {
ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
dataSource.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/test_simple_practice?serverTimezone=UTC");
dataSource.setUser("simple");
dataSource.setPassword("123");
Connection conn = dataSource.getConnection();
System.out.println(conn.getClass().getName());
conn.close();
}
}
2.4 c3p0连接的配置文件使用
1、配置文件的要求
(1)文件名称必须叫c3p0-config.xml。
(2)文件必须位于src文件目录下。
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/test_simple_practice?serverTimezone=UTC</property>
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<property name="user">simple</property>
<property name="password">123</property>
<!--当连接池用完时客户端调用getConnection()后等待获取新连接的时间,超时后将抛出SQLException,如设为0则无限期等待。单位毫秒。Default: 0 -->
<property name="checkoutTimeout">1000</property>
<!--每60秒检查所有连接池中的空闲连接。Default: 0 -->
<property name="idleConnectionTestPeriod">30</property>
<!--初始化时获取三个连接,取值应在minPoolSize与maxPoolSize之间。Default: 3 -->
<property name="initialPoolSize">10</property>
<!--最大空闲时间,60秒内未使用则连接被丢弃。若为0则永不丢弃。Default: 0 -->
<property name="maxIdleTime">30</property>
<!--连接池中保留的最大连接数。Default: 15 -->
<property name="maxPoolSize">100</property>
<property name="minPoolSize">10</property>
<!--JDBC的标准参数,用以控制数据源内加载的PreparedStatements数量。
但由于预缓存的statements属于单个connection而不是整个连接池。所以设置这个参数需要考虑到多方面的因素。
如果maxStatements与maxStatementsPerConnection均为0,则缓存被关闭。Default: 0-->
<property name="maxStatement">200</property>
</default-config>
</c3p0-config>
package org.lks.jdbc;
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.SQLException;
import org.junit.jupiter.api.Test;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class c3p0Demo {
@Test
public void getConnection() throws PropertyVetoException, SQLException {
ComboPooledDataSource dataSource = new ComboPooledDataSource();
Connection conn = dataSource.getConnection();
System.out.println(conn.getClass().getName());
conn.close();
}
}
3 JDBC工具类
3.1 LKSJDBCUtils
package org.lks.jdbcutil;
import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class LKSJDBCUtils {
private static ComboPooledDataSource dataSource = new ComboPooledDataSource();
private static Connection conn = null;
public static Connection getConnection() {
return conn;
}
public static DataSource getDataSource() {
return dataSource;
}
public static void beginTransaction() {
if (LKSJDBCUtils.conn == null) {
try {
LKSJDBCUtils.conn = dataSource.getConnection();
} catch (SQLException e) {
// TODO Auto-generated catch block
throw new RuntimeException(e);
}
} else {
throw new RuntimeException("Error create!!");
}
try {
LKSJDBCUtils.conn.setAutoCommit(false);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public static void commitTransaction() {
if (LKSJDBCUtils.conn != null) {
try {
LKSJDBCUtils.conn.commit();
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
try {
LKSJDBCUtils.conn.close();
LKSJDBCUtils.conn = null;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
} else {
throw new RuntimeException("Error commit!!");
}
}
public static void rollbackTransaction() {
if (LKSJDBCUtils.conn != null) {
try {
LKSJDBCUtils.conn.rollback();
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
try {
LKSJDBCUtils.conn.close();
LKSJDBCUtils.conn = null;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
} else {
throw new RuntimeException("Error rollback!!");
}
}
}
3.2 LKSJDBCUtils小工具
public class LKSJDBCUtils {
private static ComboPooledDataSource dataSource = new ComboPooledDataSource();
//private static Connection conn = null;
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
public static DataSource getDataSource() {
return dataSource;
}
}
3.3 JNDI配置
JNDI(Java Naming and Directory Interface),Java命名和目录接口。JNDI的作用就是:在服务器上配置资源,然后通过统一的的方式来获取配置的资源。
JNDI配置文件:
<Context>
<!--
name:指定资源的名称
factory:资源由谁来负责创建
type:资源的类型
剩下的都为资源的参数
-->
<Resource name="jdbc/dataSource"
factory="org.apache.naming.factory.BeanFactory"
type="com.mchange.v2.c3p0.ComboPooledDataSource"
jdbcUrl="jdbc:mysql://127.0.0.1:3306/test_simple_practice?serverTimezone=UTC"
driverClass="com.mysql.cj.jdbc.Driver"
user="simple"
password="123"
/>
</Context>
AServlet测试类:
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
try {
//创建JNDI的上下文对象
Context context = new InitialContext();
//查询出入口
Context envContext = (Context)context.lookup("java:comp/env");
//再次进行二次查询,找到我们需要的资源,使用的名称与<Resource>元素的name对应
DataSource dataSource = (DataSource)envContext.lookup("jdbc/dataSource");
Connection conn = dataSource.getConnection();
}catch(Exception e) {
throw new RuntimeException(e);
}
}
3.4 ThreadLocal
3.5 dbUtils原理
commons-dbUtils:简化的jdbc代码
package org.lks.jdbc;
import java.sql.SQLException;
import org.apache.commons.dbutils.QueryRunner;
import org.junit.jupiter.api.Test;
import org.lks.jdbcutil.LKSJDBCUtils;
public class dbUtilDemo {
@Test
public void fun() {
try {
QueryRunner qr = new QueryRunner(LKSJDBCUtils.getDataSource());
String sql = "INSERT INTO test_info VALUES(?,?,?)";
Object[] params = {1001, "lks", "男"};
qr.update(sql, params);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
3.6 dbUtils结果集处理器介绍
1、QueryRunner类:
(1)定义:
public class QueryRunner extends AbstractQueryRunner {}
(2)方法:
|——public int update(String sql, Object... params) throws SQLException
:可执行增、删、改语句。
|——public <T> T query(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException
:可执行查询。它会先得到ResultSet,然后调用rsh的handle把rs转换成需要的类型。
2、ResultSetHandler接口
(1)定义:
public interface ResultSetHandler<T> {}
(2)方法:
|——T handle(ResultSet rs) throws SQLException
:将ResultSet转换为一个Object对象。
(3)实现子类:
|——BeanHandler(单行):构造器需要一个Class类型的参数,用来把一行结果转换成指定类型的JavaBean对象。
|——BeanListHandler(多行):构造器需要一个Class类型的参数,用来把一个或多个结果转换成指定类型的JavaBean对象的List集合。
|——MapHandler(单行):把一个结果集转换成一个Map对象。
|——MapListHandler(多行):用来把一个或多个结果转换成指定类型的JavaBean对象的List<Map>
集合。
|——ScalarHandler(单行单列):通常与select count(*) from table语句,结果集是单行单列的,它返回一个Object。
3.7 编写TxQueryRunner配合LKSJDBCUtils
package org.lks.jdbc;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.lks.jdbcutil.LKSJDBCUtils;
public class TxQueryRunner extends QueryRunner{
@Override
public int[] batch(String sql, Object[][] params) throws SQLException {
// TODO Auto-generated method stub
Connection conn = LKSJDBCUtils.getConnection();
int[] result = super.batch(conn, sql, params);
LKSJDBCUtils.releaseConnection(conn);
return result;
}
@Override
public <T> T query(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
// TODO Auto-generated method stub
Connection conn = LKSJDBCUtils.getConnection();
T result = super.query(conn, sql, rsh, params);
LKSJDBCUtils.releaseConnection(conn);
return result;
}
@Override
public <T> T query(String sql, ResultSetHandler<T> rsh) throws SQLException {
Connection conn = LKSJDBCUtils.getConnection();
T result = super.query(conn, sql, rsh);
LKSJDBCUtils.releaseConnection(conn);
return result;
}
@Override
public int update(String sql) throws SQLException {
Connection conn = LKSJDBCUtils.getConnection();
int result = super.update(conn, sql);
LKSJDBCUtils.releaseConnection(conn);
return result;
}
@Override
public int update(String sql, Object param) throws SQLException {
Connection conn = LKSJDBCUtils.getConnection();
int result = super.update(conn, sql, param);
LKSJDBCUtils.releaseConnection(conn);
return result;
}
@Override
public int update(String sql, Object... params) throws SQLException {
Connection conn = LKSJDBCUtils.getConnection();
int result = super.update(conn, sql, params);
LKSJDBCUtils.releaseConnection(conn);
return result;
}
@Override
public <T> T insert(String sql, ResultSetHandler<T> rsh) throws SQLException {
Connection conn = LKSJDBCUtils.getConnection();
T result = super.insert(conn, sql, rsh);
LKSJDBCUtils.releaseConnection(conn);
return result;
}
@Override
public <T> T insert(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
Connection conn = LKSJDBCUtils.getConnection();
T result = super.insert(conn, sql, rsh, params);
LKSJDBCUtils.releaseConnection(conn);
return result;
}
@Override
public <T> T insertBatch(String sql, ResultSetHandler<T> rsh, Object[][] params) throws SQLException {
Connection conn = LKSJDBCUtils.getConnection();
T result = super.insertBatch(conn, sql, rsh, params);
LKSJDBCUtils.releaseConnection(conn);
return result;
}
@Override
public int execute(String sql, Object... params) throws SQLException {
Connection conn = LKSJDBCUtils.getConnection();
int result = super.execute(conn, sql, params);
LKSJDBCUtils.releaseConnection(conn);
return result;
}
@Override
public <T> List<T> execute(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
Connection conn = LKSJDBCUtils.getConnection();
List<T> result = super.execute(conn, sql, rsh, params);
LKSJDBCUtils.releaseConnection(conn);
return result;
}
}
3.8 LKSJDBCUtils处理多线程并发访问
package org.lks.jdbcutil;
import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class LKSJDBCUtils {
private static ComboPooledDataSource dataSource = null;
static {
try {
dataSource = new ComboPooledDataSource();
dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
dataSource.setJdbcUrl("jdbc:mysql://127.0.0.1/test_simple_practice?serverTimezone=UTC");
dataSource.setUser("simple");
dataSource.setPassword("123");
} catch (Exception e) {
throw new RuntimeException(e);
}
}
private static ThreadLocal<Connection> threadLocal = new ThreadLocal<Connection>();
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
public static DataSource getDataSource() {
return dataSource;
}
public static void beginTransaction() {
Connection conn = threadLocal.get();
if (conn == null) {
try {
conn = dataSource.getConnection();
threadLocal.set(conn);
} catch (SQLException e) {
// TODO Auto-generated catch block
throw new RuntimeException(e);
}
} else {
throw new RuntimeException("Error create!!");
}
try {
conn.setAutoCommit(false);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public static void commitTransaction() {
Connection conn = threadLocal.get();
if (conn != null) {
try {
conn.commit();
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
try {
conn.close();
conn = null;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
} else {
throw new RuntimeException("Error commit!!");
}
}
public static void rollbackTransaction() {
Connection conn = threadLocal.get();
if (conn != null) {
try {
conn.rollback();
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
try {
conn.close();
conn = null;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
} else {
throw new RuntimeException("Error rollback!!");
}
}
public static void releaseConnection(Connection conn2) {
Connection conn = threadLocal.get();
try {
if( conn == null) {
conn2.close();
}
if( conn != conn2) {
conn2.close();
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
4 分页
4.1 分页准备工作
1、分页的优点:只查询一页,不用查询所有页。
2、分页数据
页面数据都是由Servlet传递来的。
(1)Servlet
|——当前页:pageNumber,pn;
|————pn:如果页面没有传递当前页码,那么Servlet默认是第一页,或者按页面传递的页码设置。
|——总页数:totalPage,tp;
|————tp:总记录数/每页记录数
|——总记录数:totalRecord,tr;
|——每页记录数:业务数据或叫系统数据。pageSize,ps
|——当前页数据:beanList
|——url
(2)数据的传递
因为分页数据总是要在各层之间来回的传递,因此就将它们封装到一个JavaBean中,PageBean。
3、简单实现
(1)DIvidePage,存储每一页的具体信息
package org.lks.domain;
import java.util.List;
public class DividePage {
private Integer pageNumber;
private Integer pageCount;
private Long totalPage;
private List<User> infos;
public DividePage() {}
public Integer getPageNumber() {
return this.pageNumber;
}
public void setPageNumber(Integer pageNumber) {
this.pageNumber = pageNumber;
}
public Integer getPageCount() {
return this.pageCount;
}
public void setPageCount(Integer pageCount) {
this.pageCount = pageCount;
}
public List<User> getInfos() {
return this.infos;
}
public void setInfos(List<User> infos) {
this.infos = infos;
}
public Long getTotalPage() {
return this.totalPage;
}
public void setTotalPage(Long totalPage) {
this.totalPage = totalPage;
}
}
(2)User类,JavaBean
package org.lks.domain;
import java.util.Date;
/**
* 实体类
* @author 海燕大笨蛋
*
*/
public class User{
private Long id;
private String name;
private Integer age;
private String gender;
private String department;
private String phone;
private String idnumber;
private String birthplace;
private String address;
private Date birthday;
private String password;
public User() {
super();
}
public User(Long id, String name, Integer age, String gender, String department, String phone,
String idnumber, String birthplace, String address, Date birthday, String password) {
super();
this.id = id;
this.name = name;
this.age = age;
this.gender = gender;
this.department = department;
this.phone = phone;
this.idnumber = idnumber;
this.birthplace = birthplace;
this.address = address;
this.birthday = birthday;
this.password = password;
}
public Long getId() {
return this.id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return this.name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return this.age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getGender() {
return this.gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public String getDepartment() {
return this.department;
}
public void setDepartment(String department) {
this.department = department;
}
public String getPhone() {
return this.phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getIdnumber() {
return this.idnumber;
}
public void setIdnumber(String idnumber) {
this.idnumber = idnumber;
}
public String getBirthplace() {
return this.birthplace;
}
public void setBirthplace(String birthplace) {
this.birthplace = birthplace;
}
public String getAddress() {
return this.address;
}
public void setAddress(String address) {
this.address = address;
}
public Date getBirthday() {
return this.birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getPassword() {
return this.password;
}
public void setPassword(String password) {
this.password = password;
}
}
(3)DividePageDao,分页逻辑
package org.lks.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.lks.domain.User;
import org.lks.util.LKSJDBCUtil;
public class DividePageDao {
public DividePageDao() {}
public Long totalRow(String tableName) {
Connection conn = null;
ResultSet rs = null;
Statement stmt = null;
String sql = "select count(*) from " + tableName;
try {
conn = LKSJDBCUtil.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
if(rs.next()) {
return rs.getLong(1);
}else {
return 0L;
}
}catch(Exception e) {
throw new RuntimeException(e);
}finally {
try {
if(stmt != null) {
stmt.close();
}
if(conn != null) {
conn.close();
}
}catch(Exception e) {
throw new RuntimeException(e);
}
}
}
public List<?> find(String[] attributes, String tableName, Integer pageNumber, Integer pageCount) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
List<User> list = null;
StringBuilder sql = new StringBuilder("select");
if(attributes == null) {
attributes = "id,name,age,gender,department,phone,idnumber,birthplace,address,birthday,password".split(",");
}
for(int i = 0; i < attributes.length; i++) {
if(i > 0) {
sql.append(",");
}
sql.append(" u" + attributes[i] + " ");
}
sql.append(" from " + tableName + " limit ?,?");
try {
conn = LKSJDBCUtil.getConnection();
pstmt = conn.prepareStatement(sql.toString());
pstmt.setInt(1, (pageNumber - 1) * pageCount);
pstmt.setInt(2, pageCount);
rs=pstmt.executeQuery();
if(rs != null) {
list = new ArrayList<User>();
}
while(rs.next()) {
User user = new User();
user.setId(rs.getLong(1));
user.setName(rs.getString(2));
user.setAge(rs.getInt(3));
user.setGender(rs.getString(4));
user.setDepartment(rs.getString(5));
user.setPhone(rs.getString(6));
user.setIdnumber(rs.getString(7));
user.setBirthplace(rs.getString(8));
user.setAddress(rs.getString(9));
user.setBirthday(new Date(rs.getDate(10).getTime()));
user.setPassword(rs.getString(11));
list.add(user);
}
return list;
}catch(Exception e) {
throw new RuntimeException(e);
}finally {
try {
if(pstmt != null) {
pstmt.close();
}
if(conn != null) {
conn.close();
}
}catch(Exception e) {
throw new RuntimeException(e);
}
}
}
}
(4)DividePageService,分页业务
package org.lks.service;
import java.util.List;
import org.lks.dao.DividePageDao;
import org.lks.domain.DividePage;
import org.lks.domain.User;
public class DividePageService {
private DividePageDao dao = new DividePageDao();
@SuppressWarnings("unchecked")
public DividePage DividePage(Integer pageNumber, Integer pageCount, String tableName) {
DividePage dividePage = null;
List<?> list = dao.find(null, tableName, pageNumber, pageCount);
if(list != null) {
dividePage = new DividePage();
dividePage.setInfos((List<User>)list);
dividePage.setPageCount(pageCount);
dividePage.setPageNumber(pageNumber);
dividePage.setTotalPage(dao.totalRow(tableName) % pageCount ==0 ? dao.totalRow(tableName) / pageCount : dao.totalRow(tableName) / pageCount + 1);
}
return dividePage;
}
}
(5)Servlet类
package org.lks.web.servlet;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.lks.domain.DividePage;
import org.lks.service.DividePageService;
/**
* Servlet implementation class DividePageServlet
*/
@WebServlet("/DividePageServlet")
public class DividePageServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
private static DividePageService service = new DividePageService();
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
Integer pageCount = Integer.parseInt(request.getParameter("pageCount"));
Integer pageNumber = Integer.parseInt(request.getParameter("pageNumber"));
String tableName = request.getParameter("tableName");
DividePage dividePage = service.DividePage(pageNumber, pageCount, tableName);
request.setAttribute("page", dividePage);
request.getRequestDispatcher("/page.jsp").forward(request, response);
}
}
(6)数据库连接池
package org.lks.util;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;
import javax.sql.DataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
public class LKSJDBCUtil {
private static DataSource dataSource = null;
public static Connection getConnection() {
try {
if(dataSource != null) {
return dataSource.getConnection();
}else {
InputStream in = LKSJDBCUtil.class.getClassLoader().getResourceAsStream("application.properties");
Properties props = new Properties();
props.load(in);
dataSource = DruidDataSourceFactory.createDataSource(props);
return dataSource.getConnection();
}
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
(7)分页页面
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>分页</title>
</head>
<body align="center">
<h1>用户信息</h1>
<table border="1">
<thead>
<tr>
<th>index</th>
<th>id</th>
<th>name</th>
<th>age</th>
<th>gender</th>
<th>department</th>
<th>phone</th>
<th>idnumber</th>
<th>birthplace</th>
<th>address</th>
<th>birthday</th>
<th>password</th>
</tr>
</thead>
<tbody>
<c:forEach items="${requestScope.page.infos }" var="user" varStatus="countIndex">
<tr>
<td>${countIndex.index + 1}</td>
<td>${user.id }</td>
<td>${user.name }</td>
<td>${user.age }</td>
<td>${user.gender }</td>
<td>${user.department }</td>
<td>${user.phone }</td>
<td>${user.idnumber }</td>
<td>${user.birthplace }</td>
<td>${user.address }</td>
<td>${user.birthday }</td>
<td>${user.password }</td>
</tr>
</c:forEach>
</tbody>
</table>
<p>
<span>第${requestScope.page.pageNumber }页, 共${requestScope.page.pageCount }条</span>
<c:if test="${requestScope.page.pageNumber>1 }">
<span><a href="${pageContext.request.contextPath }/DividePageServlet?pageNumber=${requestScope.page.pageNumber-1 }&pageCount=10&tableName=user_table">上一页</a></span>
</c:if>
<c:if test="${requestScope.page.pageNumber < requestScope.page.totalPage }">
<span><a href="${pageContext.request.contextPath }/DividePageServlet?pageNumber=${requestScope.page.pageNumber+1 }&pageCount=10&tableName=user_table">下一页</a></span>
</c:if>
</p>
</body>
</html>