当前位置：Gxlcms > 数据库问题 > 【Java EE 学习第17天】【数据库导出到Excel】【多条件查询方法】

【Java EE 学习第17天】【数据库导出到Excel】【多条件查询方法】

时间：2021-07-01 10:21:17 帮助过：2人阅读

　　1.使用DatabaseMetaData分析数据库的数据结构和相关信息。

　　　　（1）测试得到所有数据库名：

  1. <span style="color: #0000ff;">private</span> <span style="color: #0000ff;">static</span> DataSource ds=<span style="color: #000000;">DataSourceUtils_C3P0.getDataSource();
  2. Connection conn</span>=<span style="color: #000000;">ds.getConnection();
  3.         DatabaseMetaData dbmd</span>=<span style="color: #000000;">conn.getMetaData();
  4.         ResultSet rs</span>=<span style="color: #000000;">dbmd.getCatalogs();
  5.         </span><span style="color: #0000ff;">while</span><span style="color: #000000;">(rs.next())
  6.         {
  7.             System.out.println(rs.getString(</span>"TABLE_CAT"<span style="color: #000000;">));
  8.         }
  9.         String dbName</span>=<span style="color: #000000;">dbmd.getDatabaseProductName();
  10.         String dbVersion</span>=<span style="color: #000000;">dbmd.getDatabaseProductVersion();
  11.         System.out.println(dbName</span>+":"+dbVersion);

　　　　运行结果：

  1. <span style="color: #000000;">information_schema
  2. bms
  3. bookstore
  4. contacts
  5. day20
  6. mysql
  7. performance_schema
  8. shopping
  9. test
  10. user
  11. users
  12. MySQL:</span>5.5.25

　　　　（2）根据数据库名得到所有表名

  1. <span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span> testGetTalbesByDBName() <span style="color: #0000ff;">throws</span><span style="color: #000000;"> SQLException
  2.     {
  3.         Connection conn</span>=<span style="color: #000000;">ds.getConnection();
  4.         DatabaseMetaData dbmd</span>=<span style="color: #000000;">conn.getMetaData();
  5.         ResultSet rs</span>=dbmd.getTables("test", "test", <span style="color: #0000ff;">null</span>, <span style="color: #0000ff;">new</span> String[]{"TABLE"<span style="color: #000000;">});
  6.         </span><span style="color: #0000ff;">while</span><span style="color: #000000;">(rs.next())
  7.         {
  8.             System.out.println(rs.getString(</span>"TABLE_NAME"<span style="color: #000000;">));
  9.         }
  10.     }</span>

　　　　运行结果：

  1. <span style="color: #000000;">people
  2. user</span>

　　2.使用ResultSetMetaData分析表结构。

  1. <span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span> testTest1() <span style="color: #0000ff;">throws</span><span style="color: #000000;"> SQLException
  2.     {
  3.         Connection conn</span>=<span style="color: #000000;">ds.getConnection();
  4.         Statement st</span>=<span style="color: #000000;">conn.createStatement();
  5.         ResultSet rs</span>=st.executeQuery("select id,name,age,sex from people"<span style="color: #000000;">);
  6.         ResultSetMetaData rsmd</span>=<span style="color: #000000;">rs.getMetaData();
  7.         </span><span style="color: #0000ff;">int</span> columnsCount=<span style="color: #000000;">rsmd.getColumnCount();
  8.         System.err.println(</span>"一共有"+columnsCount+"列！"<span style="color: #000000;">);
  9.         </span><span style="color: #0000ff;">for</span>(<span style="color: #0000ff;">int</span> i=0;i<columnsCount;i++<span style="color: #000000;">)
  10.         {
  11.             String columnName</span>=rsmd.getColumnName(i+1<span style="color: #000000;">);
  12.             System.out.print(columnName</span>+"\t\t"<span style="color: #000000;">);
  13.         }
  14.         System.out.println();
  15.         </span><span style="color: #0000ff;">while</span><span style="color: #000000;">(rs.next())
  16.         {
  17.             </span><span style="color: #0000ff;">for</span>(<span style="color: #0000ff;">int</span> i=0;i<columnsCount;i++<span style="color: #000000;">)
  18.             {
  19.                 String columnName</span>=rsmd.getColumnName(i+1<span style="color: #000000;">);
  20.                 System.out.print(rs.getString(columnName)</span>+"\t\t"<span style="color: #000000;">);
  21.             }
  22.             System.out.println();
  23.         }
  24.         conn.close();
  25.     }</span>

　　运行结果：

  1. <span style="color: #000000;">id        name        age        sex        
  2. </span>001        张三        12<span style="color: #000000;">        男        
  3. </span>002        李四        13<span style="color: #000000;">        男        
  4. </span>003        王五        15        男

　　3.使用第三方jar包测试操作Excel文件。

　　　　（1）POI下载：http://poi.apache.org/download.html

　　　　（2）测试POI

  1. <span style="color: #0000ff;">package</span><span style="color: #000000;"> day17.kdyzm.Test;
  2. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> java.io.FileOutputStream;
  3. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> org.apache.poi.hssf.usermodel.HSSFWorkbook;
  4. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> org.apache.poi.ss.usermodel.Cell;
  5. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> org.apache.poi.ss.usermodel.Row;
  6. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> org.apache.poi.ss.usermodel.Sheet;
  7. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> org.apache.poi.ss.usermodel.Workbook;
  8. </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">class</span><span style="color: #000000;"> TestPOI {
  9.     </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">static</span> <span style="color: #0000ff;">void</span> main(String[] args) <span style="color: #0000ff;">throws</span><span style="color: #000000;"> Exception {
  10.         FileOutputStream fos</span>=<span style="color: #0000ff;">new</span> FileOutputStream("first.xls"<span style="color: #000000;">);
  11.         Workbook workbook</span>=<span style="color: #0000ff;">new</span><span style="color: #000000;"> HSSFWorkbook();
  12.         Sheet sheet</span>=workbook.createSheet("第一张表"<span style="color: #000000;">);
  13.         Row row</span>=sheet.createRow(0<span style="color: #000000;">);
  14.         Cell cell1</span>=row.createCell(0<span style="color: #000000;">);
  15.         cell1.setCellValue(</span>"第一行第一列第一个数据"<span style="color: #000000;">);
  16.         Cell cell2</span>=row.createCell(1<span style="color: #000000;">);
  17.         cell2.setCellValue(</span>"第一行第二列第一个数据"<span style="color: #000000;">);
  18.         workbook.close();
  19.         workbook.write(fos);
  20.         fos.close();
  21.     }
  22. }</span>

运行结果：

　　技术分享

　　4.导出数据库到Excel，每一个标签页对应着一张表，而且要求灵活更换内容。

  1. <span style="color: #0000ff;">package</span><span style="color: #000000;"> day17.kdyzm.exportToExcel;
  2. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> java.io.FileOutputStream;
  3. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> java.sql.Connection;
  4. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> java.sql.DatabaseMetaData;
  5. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> java.sql.ResultSet;
  6. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> java.sql.ResultSetMetaData;
  7. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> java.sql.SQLException;
  8. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> java.sql.Statement;
  9. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> java.util.ArrayList;
  10. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> java.util.List;
  11. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> org.apache.poi.hssf.usermodel.HSSFWorkbook;
  12. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> org.apache.poi.ss.usermodel.Cell;
  13. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> org.apache.poi.ss.usermodel.Row;
  14. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> org.apache.poi.ss.usermodel.Sheet;
  15. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> org.apache.poi.ss.usermodel.Workbook;
  16. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> day17.regular.utils.DataSourceUtils_C3P0;
  17. </span><span style="color: #008000;">/**</span><span style="color: #008000;">
  18.  * 将数据库中的表数据导出到Excel表格中
  19.  * </span><span style="color: #808080;">@author</span><span style="color: #008000;"> kdyzm
  20.  *
  21.  </span><span style="color: #008000;">*/</span>
  22. <span style="color: #0000ff;">public</span> <span style="color: #0000ff;">class</span><span style="color: #000000;"> ExportDataToExcel {
  23.     </span><span style="color: #0000ff;">private</span> <span style="color: #0000ff;">static</span> String dbname="bookstore"<span style="color: #000000;">;
  24.     </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">static</span> <span style="color: #0000ff;">void</span> main(String[] args) <span style="color: #0000ff;">throws</span><span style="color: #000000;"> Exception {
  25.         List</span><String>tablenames=<span style="color: #000000;">getAllTableNames(dbname);
  26.         backupToXls(tablenames);
  27.     }
  28.     </span><span style="color: #008000;">//</span><span style="color: #008000;">通过所有的表名将数据被分到xls文件中</span>
  29.     <span style="color: #0000ff;">private</span> <span style="color: #0000ff;">static</span> <span style="color: #0000ff;">void</span> backupToXls(List<String> tablenames) <span style="color: #0000ff;">throws</span><span style="color: #000000;"> Exception {
  30.         Connection conn</span>=<span style="color: #000000;">DataSourceUtils_C3P0.getConnection();
  31.         Workbook wb</span>=<span style="color: #0000ff;">new</span><span style="color: #000000;"> HSSFWorkbook(); 
  32.         FileOutputStream fos</span>=<span style="color: #0000ff;">new</span> FileOutputStream(dbname+".xls"<span style="color: #000000;">);
  33.         Statement st</span>=<span style="color: #000000;">conn.createStatement();
  34.          </span><span style="color: #0000ff;">for</span><span style="color: #000000;">(String tablename:tablenames)
  35.          {
  36.              Sheet sheet</span>=<span style="color: #000000;">wb.createSheet(tablename);
  37.              String sql</span>="select * from "+dbname+"."+<span style="color: #000000;">tablename;
  38.              ResultSet rs</span>=<span style="color: #000000;">st.executeQuery(sql);
  39.              ResultSetMetaData rsmt</span>=<span style="color: #000000;">rs.getMetaData();
  40.              </span><span style="color: #0000ff;">int</span> columns=<span style="color: #000000;">rsmt.getColumnCount();
  41.              </span><span style="color: #008000;">//</span><span style="color: #008000;">写入第一行tablehead</span>
  42.              Row tablehead=sheet.createRow(0<span style="color: #000000;">);
  43.              </span><span style="color: #0000ff;">for</span>(<span style="color: #0000ff;">int</span> i=0;i<columns;i++<span style="color: #000000;">)
  44.              {
  45.                  String columnName</span>=rsmt.getColumnName(i+1<span style="color: #000000;">);
  46.                  Cell cell</span>=<span style="color: #000000;">tablehead.createCell(i);
  47.                  cell.setCellValue(columnName);
  48.              }
  49.              </span><span style="color: #008000;">//</span><span style="color: #008000;">写入数据</span>
  50.              <span style="color: #0000ff;">int</span> index=1<span style="color: #000000;">;
  51.              </span><span style="color: #0000ff;">while</span><span style="color: #000000;">(rs.next())
  52.              {
  53.                  Row row</span>=sheet.createRow(index++<span style="color: #000000;">);
  54.                  </span><span style="color: #0000ff;">for</span>(<span style="color: #0000ff;">int</span> i=0;i<columns;i++<span style="color: #000000;">)
  55.                  {
  56.                      String columnName</span>=rsmt.getColumnName(i+1<span style="color: #000000;">);
  57.                      String value</span>=<span style="color: #000000;">rs.getString(columnName);
  58.                      Cell cell</span>=<span style="color: #000000;">row.createCell(i);
  59.                      cell.setCellValue(value);
  60.                  }
  61.              }
  62.          }
  63.          wb.write(fos);
  64.          wb.close();
  65.          fos.close();
  66.          conn.close();
  67.     }
  68.     </span><span style="color: #008000;">//</span><span style="color: #008000;">首先获得所有的表名列表</span>
  69.     <span style="color: #0000ff;">private</span> <span style="color: #0000ff;">static</span> List<String> getAllTableNames(String dbname) <span style="color: #0000ff;">throws</span><span style="color: #000000;"> SQLException {
  70.         Connection conn</span>=<span style="color: #000000;">DataSourceUtils_C3P0.getConnection();
  71.         DatabaseMetaData dmd</span>=<span style="color: #000000;">conn.getMetaData();
  72.         ResultSet rs</span>=dmd.getTables(dbname, dbname, <span style="color: #0000ff;">null</span>, <span style="color: #0000ff;">new</span> String[]{"TABLE"<span style="color: #000000;">});
  73.         List</span><String>tablenames=<span style="color: #0000ff;">new</span> ArrayList<String><span style="color: #000000;">();
  74.         </span><span style="color: #0000ff;">while</span><span style="color: #000000;">(rs.next())
  75.         {
  76.             tablenames.add(rs.getString(</span>"TABLE_NAME"<span style="color: #000000;">));
  77.         }
  78.         conn.close();
  79.         </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> tablenames;
  80.     }
  81. }</span>

运行结果：

　　　　技术分享

二、多条件查询方法简介

　　1.核心思想： where 1=1 使用的要恰到好处

　　2.实现方法：

  1. <span style="color: #0000ff;">package</span><span style="color: #000000;"> day17.kdyzm.searchByMultipleInput;
  2. </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">class</span><span style="color: #000000;"> People {
  3.     </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> String id;
  4.     </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> String name;
  5.     </span><span style="color: #0000ff;">private</span> <span style="color: #0000ff;">int</span><span style="color: #000000;"> age;
  6.     </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> String sex;
  7.     
  8.     </span><span style="color: #0000ff;">public</span><span style="color: #000000;"> People() {
  9.     }
  10.     </span><span style="color: #0000ff;">public</span> People(String id, String name, <span style="color: #0000ff;">int</span><span style="color: #000000;"> age, String sex) {
  11.         </span><span style="color: #0000ff;">super</span><span style="color: #000000;">();
  12.         </span><span style="color: #0000ff;">this</span>.id =<span style="color: #000000;"> id;
  13.         </span><span style="color: #0000ff;">this</span>.name =<span style="color: #000000;"> name;
  14.         </span><span style="color: #0000ff;">this</span>.age =<span style="color: #000000;"> age;
  15.         </span><span style="color: #0000ff;">this</span>.sex =<span style="color: #000000;"> sex;
  16.     }
  17.     </span><span style="color: #0000ff;">public</span><span style="color: #000000;"> String getId() {
  18.         </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> id;
  19.     }
  20.     </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> setId(String id) {
  21.         </span><span style="color: #0000ff;">this</span>.id =<span style="color: #000000;"> id;
  22.     }
  23.     </span><span style="color: #0000ff;">public</span><span style="color: #000000;"> String getName() {
  24.         </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> name;
  25.     }
  26.     </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> setName(String name) {
  27.         </span><span style="color: #0000ff;">this</span>.name =<span style="color: #000000;"> name;
  28.     }
  29.     </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">int</span><span style="color: #000000;"> getAge() {
  30.         </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> age;
  31.     }
  32.     </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span> setAge(<span style="color: #0000ff;">int</span><span style="color: #000000;"> age) {
  33.         </span><span style="color: #0000ff;">this</span>.age =<span style="color: #000000;"> age;
  34.     }
  35.     </span><span style="color: #0000ff;">public</span><span style="color: #000000;"> String getSex() {
  36.         </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> sex;
  37.     }
  38.     </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> setSex(String sex) {
  39.         </span><span style="color: #0000ff;">this</span>.sex =<span style="color: #000000;"> sex;
  40.     }
  41.     @Override
  42.     </span><span style="color: #0000ff;">public</span><span style="color: #000000;"> String toString() {
  43.         </span><span style="color: #0000ff;">return</span> "People [id=" + id + ", name=" + name + ", age=" +<span style="color: #000000;"> age
  44.                 </span>+ ", sex=" + sex + "]"<span style="color: #000000;">;
  45.     }
  46. }</span>
  1. <span style="color: #0000ff;">package</span><span style="color: #000000;"> day17.kdyzm.searchByMultipleInput;
  2. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> java.sql.SQLException;
  3. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> java.util.ArrayList;
  4. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> java.util.List;
  5. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> javax.sql.DataSource;
  6. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> org.apache.commons.dbutils.QueryRunner;
  7. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> org.apache.commons.dbutils.handlers.BeanListHandler;
  8. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> day17.regular.utils.DataSourceUtils_C3P0;
  9. </span><span style="color: #008000;">/**</span><span style="color: #008000;">
  10.  * 多条件查询方法:可以极大提高代码书写效率
  11.  * 结合dbutils
  12.  * 结合java Bean
  13.  * </span><span style="color: #808080;">@author</span><span style="color: #008000;"> kdyzm
  14.  *
  15.  </span><span style="color: #008000;">*/</span>
  16. <span style="color: #0000ff;">public</span> <span style="color: #0000ff;">class</span><span style="color: #000000;"> SearchByMultipleInput {
  17.     </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">static</span> <span style="color: #0000ff;">void</span> main(String args[]) <span style="color: #0000ff;">throws</span><span style="color: #000000;"> SQLException{
  18.         DataSource ds</span>=<span style="color: #000000;">DataSourceUtils_C3P0.getDataSource();
  19.         String sql</span>="select * from people where 1=1"<span style="color: #000000;">;
  20.         People p</span>=<span style="color: #0000ff;">new</span><span style="color: #000000;"> People();
  21.         p.setId(</span><span style="color: #0000ff;">null</span><span style="color: #000000;">);
  22.         p.setName(</span><span style="color: #0000ff;">null</span><span style="color: #000000;">);
  23.         p.setAge(</span>0<span style="color: #000000;">);
  24.         p.setSex(</span>"女"<span style="color: #000000;">);
  25.         List</span><String>list=<span style="color: #0000ff;">new</span> ArrayList<String><span style="color: #000000;">();
  26.         </span><span style="color: #0000ff;">if</span>(p.getId()!=<span style="color: #0000ff;">null</span><span style="color: #000000;">){
  27.             sql</span>=sql+" and id=?"<span style="color: #000000;">;
  28.             list.add(p.getId());
  29.         }
  30.         </span><span style="color: #0000ff;">if</span>(p.getName()!=<span style="color: #0000ff;">null</span><span style="color: #000000;">){
  31.             sql</span>=sql+" and name like ?"<span style="color: #000000;">;
  32.             list.add(</span>"%"+p.getName()+"%"<span style="color: #000000;">);
  33.         }
  34.         </span><span style="color: #0000ff;">if</span>(p.getAge()!=0<span style="color: #000000;">){
  35.             sql</span>=sql+" and age=?"<span style="color: #000000;">;
  36.             list.add(p.getAge()</span>+""<span style="color: #000000;">);
  37.         }
  38.         </span><span style="color: #0000ff;">if</span>(p.getSex()!=<span style="color: #0000ff;">null</span><span style="color: #000000;">){
  39.             sql</span>=sql+" and sex=?"<span style="color: #000000;">;
  40.             list.add(p.getSex());
  41.         }
  42.         QueryRunner run</span>=<span style="color: #0000ff;">new</span><span style="color: #000000;"> QueryRunner(ds);
  43.         List</span><People>peoples=run.query(sql,<span style="color: #0000ff;">new</span> BeanListHandler<People>(People.<span style="color: #0000ff;">class</span><span style="color: #000000;">),list.toArray());
  44.         </span><span style="color: #0000ff;">for</span><span style="color: #000000;">(People pp:peoples)
  45.         {
  46.             System.out.println(pp);
  47.         }
  48.     }
  49. }</span>

 

【Java EE 学习第17天】【数据库导出到Excel】【多条件查询方法】

标签：

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！