jdbc实现增删改查分页分页处理以及在页面上显示

最新推荐文章于 2023-02-18 13:23:35 发布

前方太黑暗

最新推荐文章于 2023-02-18 13:23:35 发布

阅读量572

点赞数 1

分类专栏： java web 文章标签： jdbc实现增删改查 jdbc实现分页

本文链接：https://blog.csdn.net/xcc_2269861428/article/details/100527409

版权

java web 专栏收录该内容

35 篇文章 0 订阅

订阅专栏

使用mysql+jdbc+easyui实现，使用纯servlet实现

代码量太多，仅展示部分代码。

如图所示：

实现基础的增删改查

jdbc连接

/**
 * jdbc 连接数据库
 * @author 徐长城
 * @date:  2019年8月31日
 */
public class UtilsDB {
    // url
	private static final String URL = "jdbc:mysql://localhost:3306/web_jdbc";
	// 用户名
	private static final String USER = "root";
	// 密码
	private static final String PASSWORD = "admin";
	
    private static Connection con=null;
    
    // 静态代码块 用来初始化数据库连接
	static{
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con=DriverManager.getConnection(URL, USER, PASSWORD);
		}catch(Exception e)
		{
			e.printStackTrace();
		}	
	}
	
	/**
	 * 对外提供连接
	 * @return
	 */
	public static Connection getConnection()
	{
		return con;
	}
}

/**
 * 商户的增删改查
 * @author 徐长城
 * @date:  2019年8月31日
 */
public class MerchantServiceImpl implements MerchantService {

	/**
	 * 添加商户
	 */
	@Override
	public void add(Merchant merchant) throws Exception {
		//获取数据库
		Connection con=UtilsDB.getConnection();
		String sql="insert into merchant(name,address) values(?,?)";
		PreparedStatement pst=con.prepareStatement(sql);
		//向里面添加用户
		pst.setString(1,merchant.getName());
		pst.setString(2, merchant.getAddress());
		pst.executeUpdate();
		UtilsClose.toClose(pst);
	}

	/**
	 * 获取商户通过id
	 */
	@Override
	public Merchant getMerchantById(int id) throws Exception {
		Merchant merchant=null;
		//获取数据库
		Connection con=UtilsDB.getConnection();
		String sql="select id,name,address from merchant where id=?";
		PreparedStatement pst = con.prepareStatement(sql);
		pst.setInt(1, id);
		ResultSet rst = pst.executeQuery();
		if(rst.next()){
			int id_ = rst.getInt(1);
			String name = rst.getString(2);
			String address = rst.getString(3);
			
			merchant = new Merchant();
			merchant.setId(id_);
			merchant.setName(name);
			merchant.setAddress(address);
		}
		UtilsClose.toClose(rst);
		UtilsClose.toClose(pst);
		return merchant;
	}

	/**
	 * 修改商户
	 */
	@Override
	public void update(Merchant merchant) throws Exception {
		//获取数据库
		Connection con=UtilsDB.getConnection();
		String sql="update merchant set name=?,address=? where id=?";
		PreparedStatement pst=con.prepareStatement(sql);
		pst.setString(1,merchant.getName());
		pst.setString(2,merchant.getAddress());
		pst.setInt(3,merchant.getId());
		pst.executeUpdate();
		UtilsClose.toClose(pst);
	}

	/**
	 * 删除商户
	 */
	@Override
	public void del(int id) throws Exception {
		//获取数据库
		Connection con=UtilsDB.getConnection();
		String sql="delete from merchant where id=?";
		PreparedStatement pst=con.prepareStatement(sql);
		pst.setInt(1, id);
		pst.executeUpdate();
		UtilsClose.toClose(pst);
	}

	/**
	 * 获取总页数
	 * @return
	 * @throws Exception
	 */
	private int getTotal() throws Exception {
		int count = 0;
		//获取数据库
		Connection con=UtilsDB.getConnection();
		String sql="select count(*) from merchant";
		PreparedStatement pst = con.prepareStatement(sql);
		ResultSet rst = pst.executeQuery();
		if(rst.next()){
			count = rst.getInt(1);
		}
		UtilsClose.toClose(rst);
		UtilsClose.toClose(pst);
		return count;
	}
	
	/**
	 * 获取总页数(条件查询)
	 * @return
	 * @throws Exception
	 */
	private int getTotalBySearch(String name) throws Exception {
		int count = 0;
		//获取数据库
		Connection con=UtilsDB.getConnection();
		String sql="select count(*) from merchant where name like '%"+name+"%'";
		PreparedStatement pst = con.prepareStatement(sql);
		ResultSet rst = pst.executeQuery();
		if(rst.next()){
			count = rst.getInt(1);
		}
		UtilsClose.toClose(rst);
		UtilsClose.toClose(pst);
		return count;
	}

	
	@Override
	public Page getPage(int page, int rows) throws Exception {
		int total = getTotal();
		Page page_ = new Page();
		page_.setTotal(total);
		List<Merchant> list = new ArrayList<Merchant>();
	    page = (page-1)*rows;
		//获取数据库
		Connection con=UtilsDB.getConnection();
		String sql="select id,name,address,create_time from merchant limit ?,?";
		PreparedStatement pst = con.prepareStatement(sql);
		pst.setInt(1, page);
		pst.setInt(2, rows);
		ResultSet rst = pst.executeQuery();
		while(rst.next()){
			Merchant merchant= new Merchant();
			int id_ = rst.getInt(1);
			String name = rst.getString(2);
			String address = rst.getString(3);
			String createTime = rst.getString(4);
			
			merchant.setId(id_);
			merchant.setName(name);
			merchant.setAddress(address);
			merchant.setCreateTime(createTime);
			list.add(merchant);
		}
		page_.setRows(list);
		UtilsClose.toClose(rst);
		UtilsClose.toClose(pst);
		return page_;
	}

	/**
	 * 根据条件查询
	 */
	@Override
	public Page getPageBySearch(int page, int rows, String name) throws Exception {
		int total = getTotalBySearch(name);
		Page page_ = new Page();
		page_.setTotal(total);
		List<Merchant> list = new ArrayList<Merchant>();
	    page = (page-1)*rows;
		//获取数据库
		Connection con=UtilsDB.getConnection();
		String sql="select id,name,address,create_time from merchant where name like '%"+name+"%' limit ?,?";
		PreparedStatement pst = con.prepareStatement(sql);
		pst.setInt(1, page);
		pst.setInt(2, rows);
		ResultSet rst = pst.executeQuery();
		while(rst.next()){
			Merchant merchant= new Merchant();
			int id_ = rst.getInt(1);
			String name_ = rst.getString(2);
			String address = rst.getString(3);
			String createTime = rst.getString(4);
			
			merchant.setId(id_);
			merchant.setName(name_);
			merchant.setAddress(address);
			merchant.setCreateTime(createTime);
			list.add(merchant);
		}
		page_.setRows(list);
		UtilsClose.toClose(rst);
		UtilsClose.toClose(pst);
		return page_;
	}
}

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd" id="WebApp_ID" version="3.1">
  <display-name>web_jdbc</display-name>
  
  <!-- 获取商户列表跳转  -->
  <servlet>
	  <servlet-name>list</servlet-name>
	  <servlet-class>com.xcc.servlet.MerchantList</servlet-class>
  </servlet>
  <servlet-mapping>
	  <servlet-name>list</servlet-name>
	  <url-pattern>/list</url-pattern>
  </servlet-mapping>
  
  <!-- 添加商户跳转  -->
  <servlet>
	  <servlet-name>add</servlet-name>
	  <servlet-class>com.xcc.servlet.AddMerchant</servlet-class>
  </servlet>
  <servlet-mapping>
	  <servlet-name>add</servlet-name>
	  <url-pattern>/add</url-pattern>
  </servlet-mapping>
  
  <!-- 获取商户跳转  -->
  <servlet>
	  <servlet-name>get</servlet-name>
	  <servlet-class>com.xcc.servlet.GetMerchant</servlet-class>
  </servlet>
  <servlet-mapping>
	  <servlet-name>get</servlet-name>
	  <url-pattern>/get</url-pattern>
  </servlet-mapping>
  
  <!-- 修改商户跳转  -->
  <servlet>
	  <servlet-name>update</servlet-name>
	  <servlet-class>com.xcc.servlet.UpdateMerchant</servlet-class>
  </servlet>
  <servlet-mapping>
	  <servlet-name>update</servlet-name>
	  <url-pattern>/update</url-pattern>
  </servlet-mapping>
  
  <!-- 删除商户跳转  -->
  <servlet>
	  <servlet-name>del</servlet-name>
	  <servlet-class>com.xcc.servlet.DelMerchant</servlet-class>
  </servlet>
  <servlet-mapping>
	  <servlet-name>del</servlet-name>
	  <url-pattern>/del</url-pattern>
  </servlet-mapping>
	
  <welcome-file-list>
    <welcome-file>index.html</welcome-file>
  </welcome-file-list>
</web-app>

前方太黑暗

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
jdbc实现增删改查分页分页处理以及在页面上显示

使用mysql+jdbc+easyui实现，使用纯servlet实现代码量太多，仅展示部分代码。如图所示：实现基础的增删改查jdbc连接/** * jdbc 连接数据库 * @author 徐长城 * @date: 2019年8月31日 */public class UtilsDB { // url private static final Stri...
复制链接

扫一扫