DB2 Java JDBC 分页demo

原创 于 2023-06-11 16:25:21 发布 · 211 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#java #开发语言

该代码示例展示了在Java中使用SpringBatch进行数据库操作，包括插入用户数据和执行分页查询。两种分页查询方法被比较，一种使用`ROW_NUMBER()`和`WHERE`子句，另一种使用`FETCHFIRST`子句。代码中还包含了获取数据库连接、预编译SQL语句以及处理结果集的逻辑。

INSERT INTO DUFUY."user"
(ID, CUSTOMER_NO, CREATEDATE, NAME1, NAME2, NAME3, NAME4, NAME5, COUNTRY)
VALUES(1, '', '', '', '', '', '', '', '');

方式一：
SELECT * FROM (
    SELECT ROW_NUMBER() over(ORDER BY id asc) AS row_number, ID, CUSTOMER_NO, CREATEDATE
    FROM DUFUY."user"
    --WHERE COUNTRY = 'HK'
    --AND CREATEDATE >='2023-06-11 00:00:00' AND CREATEDATE <= '2023-06-11 23:59:59'
) t
WHERE t.ROW_NUMBER > 0  AND t.ROW_NUMBER <= 10

方式2
SELECT * FROM (
    SELECT ROW_NUMBER() over(ORDER BY id asc) AS row_number, ID, CUSTOMER_NO, CREATEDATE
    FROM DUFUY."user"
    --WHERE COUNTRY = 'HK'
    --AND CREATEDATE >='2023-06-11 00:00:00' AND CREATEDATE <= '2023-06-11 23:59:59'
) t
WHERE t.ROW_NUMBER > 0
FETCH FIRST 10 ROWS ONLY

package spring.batch.controller;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class Test1 {
   public static void main(String[] args) {
      // insertDB();
      findListByPage(1,10).stream().forEach((u) -> System.out.println(u.getId()+", "+ u.getCustomerNo()));
      findListByPage2(1,10).stream().forEach((u) -> System.out.println(u.getId()+", "+ u.getCustomerNo()));
   }

   public static void insertDB() {
      String insertSql = "INSERT INTO DUFUY.\"user\" (ID, CUSTOMER_NO, CREATEDATE, NAME1, NAME2, NAME3, NAME4, NAME5, COUNTRY) VALUES(?,?,?,?,?,?,?,?,?)";
      Connection con = null;
      PreparedStatement ps = null;
      try {
         con = getConnection();
         ps = con.prepareStatement(insertSql);
         con.setAutoCommit(false);
         for (int i=51;i<=51;i++) {
            ps.setLong(1, i);
            ps.setString(2, 1000000+i+"");
            Date date = new Date();
            Timestamp ts=new Timestamp(date.getTime());
            ps.setTimestamp(3, ts);
            ps.setString(4, "测试数据看性能NAME1:"+i);
            ps.setString(5, "测试数据看性能NAME2:"+i);
            ps.setString(6, "测试数据看性能NAME3:"+i);
            ps.setString(7, "测试数据看性能NAME4:"+i);
            ps.setString(8, "测试数据看性能NAME5:"+i);
            if (i%5==0){
               ps.setString(9, "SG");
            } else {
               ps.setString(9, "HK");
            }

            ps.addBatch();
         }

         ps.executeBatch();
         con.commit();
         System.out.println("finished");
      } catch (SQLException e) {
         e.printStackTrace();
      } finally {
         if(ps != null){
            try {
               ps.close();
            } catch (SQLException e) {
               throw new RuntimeException(e);
            }
         }
         if(con != null){
            try {
               con.close();
            } catch (SQLException e) {
               throw new RuntimeException(e);
            }
         }
      }
   }

   public static List<User> findListByPage(int pageNo, int pageSize) {
      Connection conn = null;
      PreparedStatement ps = null;
      ResultSet rs = null;
      List<User> list = new ArrayList<>();
        int count = 0;
      int tes = 2147483647;
      System.out.println(tes);
      try {
         String countSql = "SELECT count(*) count FROM ("+
               "     SELECT ROW_NUMBER() over(ORDER BY Id asc) AS row_number, ID, CUSTOMER_NO, CREATEDATE"+
               "     FROM DUFUY.\"user\""+
               "     WHERE 1=1"+
               "     AND COUNTRY = 'HK'"+
               "     AND CREATEDATE >='2023-06-11 00:00:00' AND CREATEDATE <= '2023-06-11 23:59:59'"+
               ") t";

         conn = getConnection();
         ps = conn.prepareStatement(countSql);
         rs = ps.executeQuery();

         if (rs.next()) {
            count = rs.getInt("count");
         }
         System.out.println("总条数：" + count);
         int totalPageSize = count % pageSize == 0 ? count / pageSize : count / pageSize + 1;
         System.out.println("总页数：" + totalPageSize);
         // 页数最小为1
         if (pageNo < 1) {
            pageNo = 1;
         }
         // 传入的页数大于总页数
         if (pageNo > totalPageSize) {
            pageNo = totalPageSize;
         }

         int beginIndex = (pageNo - 1) * pageSize;
         int endIndex = pageNo * pageSize;

         String sql = "SELECT * FROM ("+
         "     SELECT ROW_NUMBER() over(ORDER BY Id asc) AS row_number, ID, CUSTOMER_NO, CREATEDATE"+
         "     FROM DUFUY.\"user\""+
         "     WHERE 1=1"+
            "     AND COUNTRY = 'HK'"+
         "     AND CREATEDATE >='2023-06-11 00:00:00' AND CREATEDATE <= '2023-06-11 23:59:59'"+
         ") t "+
            // WHERE t.ROW_NUMBER > 30  AND t.ROW_NUMBER <= 40
          " WHERE t.ROW_NUMBER > ?  AND t.ROW_NUMBER <= ?";
         System.out.println("exe sql: "+ sql);
         ps = conn.prepareStatement(sql);

         System.out.println("beginIndex: " + beginIndex + ", endIndex: " + endIndex);
         ps.setInt(1, beginIndex);
         ps.setInt(2, endIndex);
         rs = ps.executeQuery();

         while (rs.next()) {
            User user = new User();
            user.setId(rs.getLong("ID"));
            user.setCustomerNo(rs.getString("CUSTOMER_NO"));
            user.setCreateDate(rs.getString("CREATEDATE"));
            list.add(user);
         }
      } catch (Exception e) {
         e.printStackTrace();
      } finally {
         if(rs != null){
            try {
               rs.close();
            } catch (SQLException e) {
               throw new RuntimeException(e);
            }
         }
         if(ps != null){
            try {
               ps.close();
            } catch (SQLException e) {
               throw new RuntimeException(e);
            }
         }
         if(conn != null){
            try {
               conn.close();
            } catch (SQLException e) {
               throw new RuntimeException(e);
            }
         }
      }

      return list;
   }

   public static List<User> findListByPage2(int pageNo, int pageSize) {
      Connection conn = null;
      PreparedStatement ps = null;
      ResultSet rs = null;
      List<User> list = new ArrayList<>();
      int count = 0;
//    int tes = 2147483647;
//    System.out.println(tes);
      try {
         String countSql = "SELECT count(*) count FROM ("+
               "     SELECT ROW_NUMBER() over(ORDER BY Id asc) AS row_number, ID, CUSTOMER_NO, CREATEDATE"+
               "     FROM DUFUY.\"user\""+
               "     WHERE 1=1"+
               "     AND COUNTRY = 'HK'"+
               "     AND CREATEDATE >='2023-06-11 00:00:00' AND CREATEDATE <= '2023-06-11 23:59:59'"+
               ") t";

         conn = getConnection();
         ps = conn.prepareStatement(countSql);
         rs = ps.executeQuery();

         if (rs.next()) {
            count = rs.getInt("count");
         }
         System.out.println("总条数：" + count);
         int totalPageSize = count % pageSize == 0 ? count / pageSize : count / pageSize + 1;
         System.out.println("总页数：" + totalPageSize);
         // 页数最小为1
         if (pageNo < 1) {
            pageNo = 1;
         }
         // 传入的页数大于总页数
         if (pageNo > totalPageSize) {
            pageNo = totalPageSize;
         }

         int beginIndex = (pageNo - 1) * pageSize;
         int endIndex = pageNo * pageSize;

         String sql = "SELECT * FROM ("+
               "     SELECT ROW_NUMBER() over(ORDER BY Id asc) AS row_number, ID, CUSTOMER_NO, CREATEDATE"+
               "     FROM DUFUY.\"user\""+
               "     WHERE 1=1"+
               "     AND COUNTRY = 'HK'"+
               "     AND CREATEDATE >='2023-06-11 00:00:00' AND CREATEDATE <= '2023-06-11 23:59:59'"+
               ") t "+
               " WHERE t.ROW_NUMBER > ? "+
               // 此处不能用？填充，要用+号拼接
               " FETCH FIRST " + pageSize + " ROWS ONLY ";
         System.out.println("exe sql: "+ sql);
         ps = conn.prepareStatement(sql);

         System.out.println("beginIndex: " + beginIndex + ", endIndex: " + endIndex);
         ps.setInt(1, beginIndex);
         rs = ps.executeQuery();

         while (rs.next()) {
            User user = new User();
            user.setId(rs.getLong("ID"));
            user.setCustomerNo(rs.getString("CUSTOMER_NO"));
            user.setCreateDate(rs.getString("CREATEDATE"));
            list.add(user);
         }
      } catch (Exception e) {
         e.printStackTrace();
      } finally {
         if(rs != null){
            try {
               rs.close();
            } catch (SQLException e) {
               throw new RuntimeException(e);
            }
         }
         if(ps != null){
            try {
               ps.close();
            } catch (SQLException e) {
               throw new RuntimeException(e);
            }
         }
         if(conn != null){
            try {
               conn.close();
            } catch (SQLException e) {
               throw new RuntimeException(e);
            }
         }
      }

      return list;
   }

   public static Connection getConnection() {
      try {
         // 需要引入db2jcc4.jar
         Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
      } catch (InstantiationException e) {
         throw new RuntimeException(e);
      } catch (IllegalAccessException e) {
         throw new RuntimeException(e);
      } catch (ClassNotFoundException e) {
         throw new RuntimeException(e);
      }
      // 必须使用IP
      String url = "jdbc:db2://192.168.5.5:50000/SAMPLE";
      Connection conn = null;
      try {
         conn = DriverManager.getConnection(url, "用户", "你自己的密码");
      } catch (SQLException e) {
         e.printStackTrace();
      }
      return conn;
   }
}

public class User {
    private Long id;
    private String customerNo;
    private String createDate;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getCustomerNo() {
        return customerNo;
    }

    public void setCustomerNo(String customerNo) {
        this.customerNo = customerNo;
    }

    public String getCreateDate() {
        return createDate;
    }

    public void setCreateDate(String createDate) {
        this.createDate = createDate;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", customerNo='" + customerNo + '\'' +
                ", createDate='" + createDate + '\'' +
                '}';
    }
}
Java开发 - MybatisPlus框架初体验
CodingFire的博客
03-31 1739
我们已经学习过Mybatis框架，基于一定的目的或者需求，我们希望提升此框架的应用效率，于是MyBatisPlus框架就来了，如果说Mybatis框架的快乐是1倍，那么MyBatisPlus带给你的快乐将是double，MyBatisPlus不会改变原有的项目结构，只做增强，你可以轻松引入，简化开发，提高效率。话不多说，下面我们就开始今天的MyBatisPlus之旅。正如前言所说，MyBatisPlus只是为了提高开发效率。（简称 MP）是一个。
Oracle 官方 JDBC Demo JAVA
11-21
一些常用的 JDBC 操作， 官方实例 拿来分享~
java jdbc demo_JAVA_JDBC_DEMO
weixin_42614269的博客
02-17 401
操作步骤：1.注册数据库驱动程序2.获取数据库连接对象3.建立数据库操作语句4.执行并返回结果5.释放连接CODE1.配置Properties属性文件DRIVERS=oracle.jdbc.driver.OracleDriverURL=jdbc:oracle:thin:@localhost:1521:orclUSERNAME=...
java jdbc demo_JDBC demo
weixin_39760434的博客
02-17 238
package com.zhangbz.jdbc;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class JDBCDemo1 {public static...
java jdbc demo_jdbc demo
weixin_39978101的博客
02-24 79
packagecom.zto.branchaging.core.dao.impl;importjava.sql.SQLException;importjava.util.ArrayList;importjava.util.Date;importjava.util.List;importorg.springframework.stereotype.Repository;importcom.zto.b...
java JDBCdemo
qq_43233736的博客
12-01 717
jdbc的简单练习并没有涉及到图形界面 数据库连接基本操作 package red; import java.sql.*; public class link { static { try { String driverName="com.mysql.cj.jdbc.Driver"; Class.forName(dr...
Mybatis3 generator的使用附demo 及sqlserver 分页插件
03-30
<context id="DB2" targetRuntime="MyBatis3"> <jdbcConnection driverClass="${jdbc.driver}" connectionURL="${jdbc.url}" userId="${jdbc.username}" password="${jdbc.password}" /> ...
MyBatisPlus初体验(附demo代码)
Nicer_feng's blog
11-02 410
MyBatisPlus简称MP，看名字就知道是一个MyBatis的增强工具，在 MyBatis 的基础上只做增强不做改变，为简化开发、提高效率而生。今天我们来体验一下
十、SpringBoot整合Mybatis以及分页插件封装
coderlvqj的博客
06-09 2144
​ 本节我们来学习一下SpringBoot整合mybatis。 1、准备依赖 <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.0.0.RELEASE</version> </par
java跨数据库联合_java实现跨数据库关联运算的简便方法
weixin_29958569的博客
03-02 849
Java程序开发中会碰到跨数据库关联运算的情况，这里通过一个例子来看Java实现的方法。例子中sales表在db2数据库中，employee表在mysql数据库中。要将sales和employee表通过sales中的sellerid和employee中的eid关联起来，过滤出state=”California”的所有sales和employee数据。Sales表的结构和数据如下： Employee...
DB2数据库连接demo
08-05
一个完整的三层架构的DB2数据库demo，增删改查功能完善，注释齐全，适合新手学习。
java jdbc demo_JdbcDemo.java
weixin_35681965的博客
02-24 177
package com.enjoylearning.mybatis;//STEP 1. 导入sql相关的包import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;i...
java jdbc 简单demo
Yuanyuanasdf的专栏
09-22 625
java jdbc demo
java jdbc demo_jdbc练习demo
weixin_34183702的博客
02-24 276
//连接云端服务器数据库工具类public class TestJDBCUtil {public static String driver="com.mysql.jdbc.Driver";public static String url = "jdbc:mysql://42.123.87.49:3306/aaaaa?useUnicode=true&characterEncoding=utf...
jdbc mysql demo_Java JDBC 连接数据库 Demo
weixin_39922476的博客
01-27 110
import java.sql.SQLException;import java.sql.Statement;import java.sql.Connection;import java.sql.DriverManager;public class Dao {protected Connection conn;protected Statement stat;private String url;...
Java JDBC 连接数据库 Demo
weixin_30345577的博客
12-25 108
import java.sql.SQLException;import java.sql.Statement;import java.sql.Connection;import java.sql.DriverManager; public class Dao { protected Connection conn;protected Statement stat; private Strin...
JDBC --- java开发中jdbc连接数据库demo
hengzq的博客
03-31 554
看了几天的mybatis源码，追踪到底层。发现已将昔日的jdbc给忘了，真是惭愧呀。于是写个demo回顾一下。      jdbc的jar包：http://download.youkuaiyun.com/detail/mmd0308/9799431 public void demo1() throws ClassNotFoundException, SQLException {     /*
java连接数据库demo,JDBC
爱学习的小强
04-18 580
package ab_connectMySQL; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Iterator; public class JDBCdemo { public static void main(String[]
Java JDBC连接DB2数据库示例
"该资源提供了一个使用Java JDBC连接到DB2数据库的示例代码。代码展示了如何加载DB2驱动、建立数据库连接、执行SQL查询以及处理结果集。" 在Java编程中，JDBC（Java Database Connectivity）是用于与各种数据库进行...
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值