【Java EE 学习 17 下】【数据库导出到Excel】【多条件查询方法】
一、导出到Excel
1.使用DatabaseMetaData分析数据库的数据结构和相关信息。
（1）测试得到所有数据库名：
private static DataSource ds=DataSourceUtils_C3P0.getDataSource(); Connection conn=ds.getConnection(); DatabaseMetaData dbmd=conn.getMetaData(); ResultSet rs=dbmd.getCatalogs(); while(rs.next()) { System.out.println(rs.getString("TABLE_CAT")); } String dbName=dbmd.getDatabaseProductName(); String dbVersion=dbmd.getDatabaseProductVersion(); System.out.println(dbName+":"+dbVersion);
运行结果：
information_schema
bms
bookstore
contacts
day20
mysql
performance_schema
shopping
test
user
users
MySQL:5.5.25
（2）根据数据库名得到所有表名
public void testGetTalbesByDBName() throws SQLException { Connection conn=ds.getConnection(); DatabaseMetaData dbmd=conn.getMetaData(); ResultSet rs=dbmd.getTables("test", "test", null, new String[]{"TABLE"}); while(rs.next()) { System.out.println(rs.getString("TABLE_NAME")); } }
运行结果：
people
user
2.使用ResultSetMetaData分析表结构。
public void testTest1() throws SQLException { Connection conn=ds.getConnection(); Statement st=conn.createStatement(); ResultSet rs=st.executeQuery("select id,name,age,sex from people"); ResultSetMetaData rsmd=rs.getMetaData(); int columnsCount=rsmd.getColumnCount(); System.err.println("一共有"+columnsCount+"列！"); for(int i=0;i<columnsCount;i++) { String columnName=rsmd.getColumnName(i+1); System.out.print(columnName+"\t\t"); } System.out.println(); while(rs.next()) { for(int i=0;i<columnsCount;i++) { String columnName=rsmd.getColumnName(i+1); System.out.print(rs.getString(columnName)+"\t\t"); } System.out.println(); } conn.close(); }
运行结果：
id name age sex 001 张三 12 男 002 李四 13 男 003 王五 15 男
3.使用第三方jar包测试操作Excel文件。
（1）POI下载：http://poi.apache.org/download.html
（2）测试POI
package day17.kdyzm.Test; import java.io.FileOutputStream; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; public class TestPOI { public static void main(String[] args) throws Exception { FileOutputStream fos=new FileOutputStream("first.xls"); Workbook workbook=new HSSFWorkbook(); Sheet sheet=workbook.createSheet("第一张表"); Row row=sheet.createRow(0); Cell cell1=row.createCell(0); cell1.setCellValue("第一行第一列第一个数据"); Cell cell2=row.createCell(1); cell2.setCellValue("第一行第二列第一个数据"); workbook.close(); workbook.write(fos); fos.close(); } }
运行结果：
4.导出数据库到Excel，每一个标签页对应着一张表，而且要求灵活更换内容。
package day17.kdyzm.exportToExcel; import java.io.FileOutputStream; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import day17.regular.utils.DataSourceUtils_C3P0; /** * 将数据库中的表数据导出到Excel表格中 * @author kdyzm * */ public class ExportDataToExcel { private static String dbname="bookstore"; public static void main(String[] args) throws Exception { List<String>tablenames=getAllTableNames(dbname); backupToXls(tablenames); } //通过所有的表名将数据被分到xls文件中 private static void backupToXls(List<String> tablenames) throws Exception { Connection conn=DataSourceUtils_C3P0.getConnection(); Workbook wb=new HSSFWorkbook(); FileOutputStream fos=new FileOutputStream(dbname+".xls"); Statement st=conn.createStatement(); for(String tablename:tablenames) { Sheet sheet=wb.createSheet(tablename); String sql="select * from "+dbname+"."+tablename; ResultSet rs=st.executeQuery(sql); ResultSetMetaData rsmt=rs.getMetaData(); int columns=rsmt.getColumnCount(); //写入第一行tablehead Row tablehead=sheet.createRow(0); for(int i=0;i<columns;i++) { String columnName=rsmt.getColumnName(i+1); Cell cell=tablehead.createCell(i); cell.setCellValue(columnName); } //写入数据 int index=1; while(rs.next()) { Row row=sheet.createRow(index++); for(int i=0;i<columns;i++) { String columnName=rsmt.getColumnName(i+1); String value=rs.getString(columnName); Cell cell=row.createCell(i); cell.setCellValue(value); } } } wb.write(fos); wb.close(); fos.close(); conn.close(); } //首先获得所有的表名列表 private static List<String> getAllTableNames(String dbname) throws SQLException { Connection conn=DataSourceUtils_C3P0.getConnection(); DatabaseMetaData dmd=conn.getMetaData(); ResultSet rs=dmd.getTables(dbname, dbname, null, new String[]{"TABLE"}); List<String>tablenames=new ArrayList<String>(); while(rs.next()) { tablenames.add(rs.getString("TABLE_NAME")); } conn.close(); return tablenames; } }
运行结果：
二、多条件查询方法简介
1.核心思想： where 1=1 使用的要恰到好处
2.实现方法：
package day17.kdyzm.searchByMultipleInput; public class People { private String id; private String name; private int age; private String sex; public People() { } public People(String id, String name, int age, String sex) { super(); this.id = id; this.name = name; this.age = age; this.sex = sex; } public String getId() { return id; } public void setId(String id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } @Override public String toString() { return "People [id=" + id + ", name=" + name + ", age=" + age + ", sex=" + sex + "]"; } }
package day17.kdyzm.searchByMultipleInput; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import javax.sql.DataSource; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.BeanListHandler; import day17.regular.utils.DataSourceUtils_C3P0; /** * 多条件查询方法:可以极大提高代码书写效率 * 结合dbutils * 结合java Bean * @author kdyzm * */ public class SearchByMultipleInput { public static void main(String args[]) throws SQLException{ DataSource ds=DataSourceUtils_C3P0.getDataSource(); String sql="select * from people where 1=1"; People p=new People(); p.setId(null); p.setName(null); p.setAge(0); p.setSex("女"); List<String>list=new ArrayList<String>(); if(p.getId()!=null){ sql=sql+" and id=?"; list.add(p.getId()); } if(p.getName()!=null){ sql=sql+" and name like ?"; list.add("%"+p.getName()+"%"); } if(p.getAge()!=0){ sql=sql+" and age=?"; list.add(p.getAge()+""); } if(p.getSex()!=null){ sql=sql+" and sex=?"; list.add(p.getSex()); } QueryRunner run=new QueryRunner(ds); List<People>peoples=run.query(sql,new BeanListHandler<People>(People.class),list.toArray()); for(People pp:peoples) { System.out.println(pp); } } }
