1:收集资料,整理数据字典
2:打开Qracle数据库
--删除产品list数据库
DROP table diseaseInfo4
--新建产品list数据库.参考diseaseInfo.xls
CREATE table diseaseInfo2
(
DE003 varchar2(20),
DE004 varchar2(20),
DE005 varchar2(500),
DE006 varchar2(20),
DE007 varchar2(500),
DE008 varchar2(500),
DE009 varchar2(500)
)
CREATE table diseaseInfo3
(
DE010 varchar2(20),
DE011 varchar2(500),
DE012 varchar2(500)
)
CREATE table diseaseInfo4
(
DE013 varchar2(500),
DE014 varchar2(500),
DE015 varchar2(500),
DE016 varchar2(20),
DE017 varchar2(2000),
DE018 varchar2(20),
DE019 clob,
DE020 clob,
DE021 clob,
DE022 clob
)
--删除diseaseInfo2数据表里面的数据,但不删除数据结构
TRUNCATE table diseaseInfo4
--修改字段类型
ALTER TABLE diseaseInfo2 MODIFY DE003 varchar2(100)
--通过excel导入到数据库内,通过java程序,参考JDBCdemoTest5diseaseInfo.java
参考附件!
--查询diseaseInfo2
SELECT distinct *
FROM diseaseInfo2
WHERE DE004 like 'DX0634' OR DE006 like 'DX0634'
--查询diseaseInfo4-对于疾病相关基因,
SELECT distinct DE013,DE014,DE017
FROM diseaseInfo4
WHERE
DE017 IS NOT Null
and
(DE013 like '痉挛性截瘫' OR DE014 like 'GNE-Related Myopathy')
--查询diseaseInfo4- 及对于疾病简介
SELECT DE013,DE014,dbms_lob.substr(DE019),dbms_lob.substr(DE021)
FROM diseaseInfo4
WHERE
(DE019 IS NOT NULL OR DE021 IS NOT NULL )
AND
(DE013='假性软骨发育不全' OR DE014='Pseudoachondroplasia')
--查询diseaseInfo3 通过子产品编号查询 相关疾病,然后查询相关疾病的基因
SELECT distinct DE011,DE012
FROM diseaseInfo3
WHERE
DE010='S0492'
import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import jxl.Sheet;
import jxl.Workbook;
import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
public class JDBCdemoTest5diseaseInfo extends AbstractDao{
private String path;
//private String path="I:\\历史代码存储-201609013\\单病遗传分析V1.0-20160906\\数据字典\\diseaseInfo.xls";
public JDBCdemoTest5diseaseInfo(){}
public JDBCdemoTest5diseaseInfo(String path){
this.path=path;
}
/* public static void main(String[] args){
JDBCdemoTest5diseaseInfo a=new JDBCdemoTest5diseaseInfo();
System.out.println(a.addAllSheets());
}*/
/**添加diseaseInfo.xls信息到疾病数据库(diseaseInfo2,diseaseInfo3,diseaseInfo4)
* step1:取得diseaseInfo.xls文件。获取excle所有的工作表:sheets[]
* jxl.Workbook rwb = jxl.Workbook.getWorkbook(new File(sourcefile));
* Sheet[] sheets = rwb.getSheets();
* step2:遍历sheets[],取得每个工作表的序号和名称
* jxl.Workbook rwb = jxl.Workbook.getWorkbook(new File(sourcefile));
* jxl.Sheet rs = rwb.getSheet(0);
* String sheetName = rs.getName();
* step3: 如果sheetName.equalsIgnore("R002 产品list"); 则通过处理产品list信息添加到diseaseInfo2 返回插入信息n条
* step4: 如果sheetName.equalsIgnore("R003 子产品list(对于1445产品)"); 则通过处理CNV位点添加到diseaseInfo3 返回插入信息n条
* step5: 如果sheetName.equalsIgnore("R004 疾病介绍"); 则处理后添加到diseaseInfo4 返回插入信息n条
* */
public int addAllSheets(){
File diseaseInfo=new File(this.path);
Workbook book;
try{
book = Workbook.getWorkbook(diseaseInfo);
int sheetsNum = book.getNumberOfSheets();
int R002N=0;
int R003N=0;
int R004N=0;
System.out.println("共有:"+sheetsNum+"张sheet");
for(int i=0;i<sheetsNum;i++){
Sheet sheet=book.getSheet(i);
String sheetName=sheet.getName();
if("R002 产品list".equalsIgnoreCase(sheetName.trim())){
//添加到diseaseInfo2
R002N+=addTodiseaseInfo2(sheet);
}else if("R003 子产品list(对于1445产品)".equalsIgnoreCase(sheetName.trim())){
//添加到diseaseInfo3
R003N+=addTodiseaseInfo3(sheet);
}else if("R004 疾病介绍".equalsIgnoreCase(sheetName.trim())){
//添加到diseaseInfo4
R004N+=addTodiseaseInfo4(sheet);
}
}
return (R002N+R003N+R004N);
//return "插入产品list数目:"+R002N+"; 插入子产品list数目:"+R003N+"; 插入疾病信息数目:"+R004N+";";
}catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return 0;
}
/**
* 计算sheet的列数col
* 通过for(int i=0;i<col.length;i++)取出首行每列中的字段 colName=sheet.getCell(i, 0).getContents().trim();
* if(colName==产品方向) productDirectionNo=i;
* if(colName==产品编号(中文)) productIDEngNo=i;
* ....
* 计算对于的字段在哪列
*
* 计算sheet的行数row
* 通过for(int i=0;i<row.length;i++)取出每列的字段
* productDirection=String.valueOf(sheet.getCell(productDirectionNo, i).getContents().trim());
* productIDEng=String.valueOf(sheet.getCell(pproductIDEngNo, i).getContents().trim());
* ......
* 通过ps.setString(1, productDirection);
* ps.setString(2, productIDEng);
* ...
* if(ps.executeUpdate()>0) n++ 进行把每行添加到数据库内
*
* */
public int addTodiseaseInfo2(Sheet sheet){
int n=0;
Connection conn=null;
try{
conn=super.getConnection();
String sql="INSERT INTO diseaseInfo2(DE003,DE004,DE005,DE006,DE007,DE008,DE009)" +
" VALUES(?,?,?,?,?,?,?)" ;
PreparedStatement ps=conn.prepareStatement(sql);
{
int col=sheet.getColumns();
int row=sheet.getRows();
int productDirectionNo=10;
int productIDEngNo=10;
int productIDChiNo=10;
int productNameEngNo=10;
int productNameChiNo=10;
int subproductNameChiNo=10;
int subproductNameEngNo=10;
String productDirection;//产品方向
String productIDEng;//产品编号(英文)
String productIDChi;//产品编号(中文)
String productNameEng;//产品名称(英文)
String productNameChi;//产品名称(中文)
String subproductNameChi;//子产品名称(中)
String subproductIDChi;//子产品名称(英)
//找到对应的列数,如果未找到列名,默认为10
for(int i=0;i<col;i++){
String colName=sheet.getCell(i, 0).getContents().trim();
if("产品方向".equals(colName.trim())){
productDirectionNo=i;
}else if("产品编号(英文)".equals(colName.trim())){
productIDEngNo=i;
}else if("产品编号(中文)".equals(colName.trim())){
productIDChiNo=i;
}else if("产品名称(英文)".equals(colName.trim())){
productNameEngNo=i;
}else if("产品名称(中文)".equals(colName.trim())){
productNameChiNo=i;
}else if("子产品名称(中)".equals(colName.trim())){
subproductNameChiNo=i;
}else if("子产品名称(英)".equals(colName.trim())){
subproductNameEngNo=i;
}
}
//通过已知列数,给赋值非对于的字符串,如果如果没有自己的首行字段,则默认为null
for(int i=1;i<row;i++){
/**通过测试发现excel里面单元格为空的内容 sheet.getCell(productDirectionNo, i).getContents().trim()取值为空字符串(""),而不是null;
*通过 sheet.getCell方法所取到的单元格,是被初始化的。可以excel中 通过对每个单元格初始化的方式为:画边框,或者填写字符等
*/
productDirection=String.valueOf(sheet.getCell(productDirectionNo, i).getContents().trim());
productIDChi=String.valueOf(sheet.getCell(productIDChiNo, i).getContents().trim());
productNameChi=String.valueOf(sheet.getCell(productNameChiNo, i).getContents().trim());
productIDEng=String.valueOf(sheet.getCell(productIDEngNo, i).getContents().trim());
productNameEng=String.valueOf(sheet.getCell(productNameEngNo, i).getContents().trim());
subproductNameChi=String.valueOf(sheet.getCell(subproductNameChiNo, i).getContents().trim());
subproductIDChi=String.valueOf(sheet.getCell(subproductNameEngNo, i).getContents().trim());
//System.out.println("productIDChi:"+productIDChi+";productNameChi"+productNameChi+";productIDEng"+productIDEng+"productNameEng"+productNameEng+";");
ps.setString(1, productDirection);
ps.setString(2, productIDChi);
ps.setString(3, productNameChi);
ps.setString(4, productIDEng);
ps.setString(5, productNameEng);
ps.setString(6, subproductNameChi);
ps.setString(7, subproductIDChi);
if(ps.executeUpdate()>0)
n++;
}
}
}catch (Exception e) {
e.printStackTrace();
}
return n;
}
public int addTodiseaseInfo3(Sheet sheet){
int n=0;
Connection conn=null;
try{
conn=super.getConnection();
String sql="INSERT INTO diseaseInfo3(DE010,DE011,DE012)" +
" VALUES(?,?,?)" ;
PreparedStatement ps=conn.prepareStatement(sql);
{
int col=sheet.getColumns();
int row=sheet.getRows();
int subProductIDNo=3;
int subProductNameChiNo=3;
int subProductNameEngNo=3;
String subProductID;//子产品编号
String subProductNameChi;//子产品名称(英文)
String subProductNameEng;//子产品名称(中文)
//找到对应的列数,如果未找到列名,默认为10
for(int i=0;i<col;i++){
String colName=sheet.getCell(i, 0).getContents().trim();
if("子产品编号".equals(colName.trim())){
subProductIDNo=i;
}else if("子产品名称(中文)".equals(colName.trim())){
subProductNameChiNo=i;
}else if("子产品名称(英文)".equals(colName.trim())){
subProductNameEngNo=i;
}
}
//通过已知列数,给赋值非对于的字符串,如果如果没有自己的首行字段,则默认为null
for(int i=1;i<row;i++){
/**通过测试发现excel里面单元格为空的内容 sheet.getCell(productDirectionNo, i).getContents().trim()取值为空字符串(""),而不是null;
*通过 sheet.getCell方法所取到的单元格,是被初始化的。可以excel中 通过对每个单元格初始化的方式为:画边框,或者填写字符等
*/
subProductID=String.valueOf(sheet.getCell(subProductIDNo, i).getContents().trim());
subProductNameChi=String.valueOf(sheet.getCell(subProductNameChiNo, i).getContents().trim());
subProductNameEng=String.valueOf(sheet.getCell(subProductNameEngNo, i).getContents().trim());
//System.out.println("subProductID:"+subProductID+";subProductNameChi"+subProductNameChi+";subProductNameEng"+subProductNameEng);
ps.setString(1, subProductID);
ps.setString(2, subProductNameChi);
ps.setString(3, subProductNameEng);
if(ps.executeUpdate()>0)
n++;
}
}
}catch (Exception e) {
e.printStackTrace();
}
return n;
}
public int addTodiseaseInfo4(Sheet sheet){
int n=0;
Connection conn=null;
try{
conn=super.getConnection();
String sql="INSERT INTO diseaseInfo4(DE013,DE014,DE015,DE016,DE017,DE018,DE019,DE020,DE021,DE022)" +
" VALUES(?,?,?,?,?,?,?,?,?,?)" ;
PreparedStatement ps=conn.prepareStatement(sql);
{
int col=sheet.getColumns();
int row=sheet.getRows();
int diseaseChiNo=10;
int diseaseEngNo=10;
int diseaseCENo=10;
int diseaseGeneNo=10;
int diseaseGeneNumNo=10;
int diseaseHeredityModeNo=10;
int diseaseBriefChiNo=10;
int diseaseBriefChiSourceNo=10;
int diseaseBriefEngNo=10;
int diseaseBriefEngSourceNo=10;
String diseaseChi;//疾病名称(中)
String diseaseEng;//疾病名称(英)
String diseaseCE;//疾病名称(中,英)
String diseaseGene;//检查基因
String diseaseGeneNum;//检查基因个数
String diseaseHeredityMode;//遗传方式
String diseaseBriefChi;//中文疾病介绍
String diseaseBriefChiSource;//中文参考来源
String diseaseBriefEng;//英文疾病介绍
String diseaseBriefEngSource;//英文参考来源
//找到对应的列数,如果未找到列名,默认为10
for(int i=0;i<col;i++){
String colName=sheet.getCell(i, 0).getContents().trim();
if("疾病名称(中)".equals(colName.trim())){
diseaseChiNo=i;
}else if("疾病名称(英)".equals(colName.trim())){
diseaseEngNo=i;
}else if("疾病名称(中,英)".equals(colName.trim())){
diseaseCENo=i;
}else if("检查基因".equals(colName.trim())){
diseaseGeneNo=i;
}else if("检查基因个数".equals(colName.trim())){
diseaseGeneNumNo=i;
}else if("遗传方式".equals(colName.trim())){
diseaseHeredityModeNo=i;
}else if("中文疾病介绍".equals(colName.trim())){
diseaseBriefChiNo=i;
}else if("中文参考来源".equals(colName.trim())){
diseaseBriefChiSourceNo=i;
}else if("英文疾病介绍".equals(colName.trim())){
diseaseBriefEngNo=i;
}else if("英文参考来源".equals(colName.trim())){
diseaseBriefEngSourceNo=i;
}
}
//通过已知列数,给赋值非对于的字符串,如果如果没有自己的首行字段,则默认为null
for(int i=1;i<row;i++){
/**通过测试发现excel里面单元格为空的内容 sheet.getCell(productDirectionNo, i).getContents().trim()取值为空字符串(""),而不是null;
*通过 sheet.getCell方法所取到的单元格,是被初始化的。可以excel中 通过对每个单元格初始化的方式为:画边框,或者填写字符等
*/
diseaseChi=String.valueOf(sheet.getCell(diseaseChiNo, i).getContents().trim());
diseaseEng=String.valueOf(sheet.getCell(diseaseEngNo, i).getContents().trim());
diseaseCE=String.valueOf(sheet.getCell(diseaseCENo, i).getContents().trim());
diseaseGene=String.valueOf(sheet.getCell(diseaseGeneNo, i).getContents().trim());
diseaseGeneNum=String.valueOf(sheet.getCell(diseaseGeneNumNo, i).getContents().trim());
diseaseHeredityMode=String.valueOf(sheet.getCell(diseaseHeredityModeNo, i).getContents().trim());
diseaseBriefChi=String.valueOf(sheet.getCell(diseaseBriefChiNo, i).getContents().trim());
diseaseBriefChiSource=String.valueOf(sheet.getCell(diseaseBriefChiSourceNo, i).getContents().trim());
diseaseBriefEng=String.valueOf(sheet.getCell(diseaseBriefEngNo, i).getContents().trim());
diseaseBriefEngSource=String.valueOf(sheet.getCell(diseaseBriefEngSourceNo, i).getContents().trim());
/*System.out.println("diseaseChi:"+diseaseChi+";diseaseEng:"+diseaseEng+";diseaseCE:"+diseaseCE+";diseaseGene: "+diseaseGene+" ;diseaseGene:"+diseaseGene
+"; diseaseGeneNum"+diseaseGeneNum+" ;diseaseHeredityMode:"+diseaseHeredityMode+";");*/
ps.setString(1, diseaseChi);
ps.setString(2, diseaseEng);
ps.setString(3, diseaseCE);
ps.setString(4, diseaseHeredityMode);
ps.setString(5, diseaseGene);
ps.setString(6, diseaseGeneNum);
ps.setString(7, diseaseBriefChi);
ps.setString(8, diseaseBriefChiSource);
ps.setString(9, diseaseBriefEng);
ps.setString(10,diseaseBriefEngSource);
if(ps.executeUpdate()>0)
n++;
}
}
}catch (Exception e) {
e.printStackTrace();
}
return n;
}
/** public int AddDiseaseInfoToData() {
Connection conn=null;
int n=0;//记录插入信息的条�?
try {
String sql="INSERT INTO diseaseInfo(productNoC, productNoE,productNameC, "
+ "productNameE, productNameCE,diseaseNameC, diseaseNameE,"
+ " procuctGene ,procuctGeneNum,heredityMode,ClinicalCharacteristicsC,ClinicalCharacteristicsE,Prevalence,subproject )"
+ " VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
PreparedStatement ps=conn.prepareStatement(sql);
{
conn.setAutoCommit(false);
File diseaseInfo=new File(this.path);
Workbook book=Workbook.getWorkbook(diseaseInfo);
for(int i=0;i<3;i++) //遍历diseaseExcek表格�?��的sheet
{
Sheet sheet=book.getSheet(i);
int row=sheet.getRows();
int col=sheet.getColumns();
String productNoC;
String productNoE;
String productNameC;
String productNameE;
String productNameCE;
String subproject;
String diseaseNameC;
String diseaseNameE;
String procuctGene;
String procuctGeneNum;
String heredityMode;
String ClinicalCharacteristicsC;
String ClinicalCharacteristicsE;
String Prevalence;
int productNoCNo=100;
int productNoENo=100;
int productNameCNo=100;
int productNameENo=100;
int productNameCENo=100;
int diseaseNameCNo=100;
int diseaseNameENo=100;
int procuctGeneNo=100;
int procuctGeneNumNo=100;
int heredityModeNo=100;
int ClinicalCharacteristicsCNo=100;
int ClinicalCharacteristicsENo=100;
int PrevalenceNo=100;
int subprojectNo=100;
for(int j=0;j<col;j++) //第i份sheet 对应的字段名(寻找对应属�?的列col)
{
String colName=String.valueOf(sheet.getCell(j, 1).getContents().trim());
if("productNoC".equalsIgnoreCase(colName))
{productNoCNo=j;
//System.out.println(colName+":�?+productNoCNo);
}
else if("productNoE".equalsIgnoreCase(colName))
{productNoENo=j;
//System.out.println(colName+":�?+productNoENo);
}
else if("subproject".equalsIgnoreCase(colName)) {subprojectNo=j;}
else if("productNameC".equalsIgnoreCase(colName)) {productNameCNo=j;}
else if("productNameE".equalsIgnoreCase(colName)) {productNameENo=j;}
else if("productNameCE".equalsIgnoreCase(colName)) {productNameCENo=j;}
else if("diseaseNameC".equalsIgnoreCase(colName)) {diseaseNameCNo=j;}
else if("diseaseNameE".equalsIgnoreCase(colName)) {diseaseNameENo=j;}
else if("procuctGene".equalsIgnoreCase(colName)) {procuctGeneNo=j;}
else if("procuctGeneNum".equalsIgnoreCase(colName)) {procuctGeneNumNo=j;}
else if("heredityMode".equalsIgnoreCase(colName)) {heredityModeNo=j;}
else if("ClinicalCharacteristicsC".equals(colName)) {ClinicalCharacteristicsCNo=j;}
else if("ClinicalCharacteristicsE".equalsIgnoreCase(colName)) {ClinicalCharacteristicsENo=j;}
else if("Prevalence".equalsIgnoreCase(colName)) {PrevalenceNo=j;}
}
for(int j=1;j<row;j++)
{
if(productNoCNo==100)
productNoC=null;
else
productNoC=String.valueOf(sheet.getCell(productNoCNo, j).getContents().trim());
if(productNoENo==100)
productNoE=null;
else
productNoE=String.valueOf(sheet.getCell(productNoENo, j).getContents().trim());
if(productNameCNo==100)
productNameC=null;
else
productNameC=String.valueOf(sheet.getCell(productNameCNo, j).getContents().trim());
if(productNameENo==100)
productNameE=null;
else
productNameE=String.valueOf(sheet.getCell(productNameENo, j).getContents().trim());
if(productNameCENo==100)
productNameCE= null;
else
productNameCE=String.valueOf(sheet.getCell(productNameCENo, j).getContents().trim());
if(diseaseNameCNo==100)
diseaseNameC=null;
else
diseaseNameC=String.valueOf(sheet.getCell(diseaseNameCNo, j).getContents().trim());
if(diseaseNameENo==100)
diseaseNameE=null;
else
diseaseNameE=String.valueOf(sheet.getCell(diseaseNameENo, j).getContents().trim());
if(procuctGeneNo==100)
procuctGene=null;
else
procuctGene=String.valueOf(sheet.getCell(procuctGeneNo, j).getContents().trim());
if(procuctGeneNumNo==100)
procuctGeneNum=null;
else
procuctGeneNum=String.valueOf(sheet.getCell(procuctGeneNumNo, j).getContents().trim());
if(heredityModeNo==100)
heredityMode=null;
else
heredityMode=String.valueOf(sheet.getCell(heredityModeNo, j).getContents().trim());
if(ClinicalCharacteristicsCNo==100)
ClinicalCharacteristicsC=null;
else
ClinicalCharacteristicsC=String.valueOf(sheet.getCell(ClinicalCharacteristicsCNo, j).getContents().trim());
if(ClinicalCharacteristicsENo==100)
ClinicalCharacteristicsE=null;
else
ClinicalCharacteristicsE=String.valueOf(sheet.getCell(ClinicalCharacteristicsENo, j).getContents().trim());
if(PrevalenceNo==100)
Prevalence=null;
else
Prevalence=String.valueOf(sheet.getCell(PrevalenceNo, j).getContents().trim());
if(subprojectNo==100)
subproject=null;
else
subproject=String.valueOf(sheet.getCell(subprojectNo, j).getContents().trim());
ps.setString(1,productNoC);
ps.setString(2,productNoE);
ps.setString(3,productNameC);
ps.setString(4,productNameE);
ps.setString(5,productNameCE);
ps.setString(6,diseaseNameC);
ps.setString(7,diseaseNameE);
ps.setString(8,procuctGene);
ps.setString(9,procuctGeneNum);
ps.setString(10,heredityMode);
ps.setString(11,ClinicalCharacteristicsC);
ps.setString(12,ClinicalCharacteristicsE);
ps.setString(13,Prevalence);
ps.setString(14,subproject);
if(ps.executeUpdate()>0)
n++;
}
}
conn.commit();//手动提交
conn.setAutoCommit(true);
}
}catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
//super.closeConnection(conn);
}
return n;
}
*/
/**删除旧的疾病数据表全部信息,慎用*/
public boolean DeleteDiseaseInfoFromData(){
Connection conn=null;
boolean flag=false;//判断该方法是否成功实�?
try{
conn=super.getConnection();
String sql="TRUNCATE table diseaseInfo2";
PreparedStatement ps=conn.prepareStatement(sql);
ps.execute();
sql="TRUNCATE table diseaseInfo3";
ps=conn.prepareStatement(sql);
ps.execute();
sql="TRUNCATE table diseaseInfo4";
ps=conn.prepareStatement(sql);
ps.execute();
flag=true;
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
//super.closeConnection(conn);
}
return flag;
}
/**取得 diseaseInfo.xml上传文件路径*/
public String getPath() {
return path;
}
/**设置 diseaseInfo.xml上传文件路径*/
public void setPath(String path) {
this.path = path;
}
}