Data_ETL Java

TestExcelToDb.java

package com;
import java.util.List;

import com.DBhelper;
import com.StuEntity;
import com.StuService;
import com.Logger;

public class TestExcelToDb {
public static void main(String[] args) {
//得到表格中所有的数据
System.out.println(“得到表格中所有的数据”);
List listExcel=StuService.getAllByExcel(“D:\Actimize\DataLoad//book.xls”);
String un=“user_aml”;
String psw=“abcd.1234”;
System.out.println(listExcel);
System.out.println(listExcel.size());
Logger.Log(“从excel获取条数:”+listExcel.size());
///得到数据库表中所有的数据
// List listDb=StuService.getAllByDb();/
// List listDb=StuService.getAllByDb();

    DBhelper db=new DBhelper();
    System.out.println("调用数据库连接");
    for (StuEntity stuEntity : listExcel) {

// System.out.println(“循环”);
int id=stuEntity.getId();
System.out.println(“id:”+id);
if (!StuService.isExist(id)) {
//不存在就添加
// System.out.println(“添加”);
System.out.println(!StuService.isExist(id));
String sql=“insert into stu (id,name,sex,num) values(?,?,?,?)”;
String[] str=new String[]{String.valueOf(stuEntity.getId()),stuEntity.getName(),stuEntity.getSex(),stuEntity.getNum()+""};
db.AddU(sql, str,un,psw);
}else {
//存在就更新
System.out.println(“更新”);
System.out.println(!StuService.isExist(id));
String sql=“update stu set id=?, name=?,sex=?,num=? where id=?”;
String[] str=new String[]{String.valueOf(stuEntity.getId()),stuEntity.getName(),stuEntity.getSex(),stuEntity.getNum()+"",id+""};
db.AddU(sql, str,un,psw);
}
}
}

}

---

DBhelper.java
package com;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DBhelper {
String driver = “com.microsoft.sqlserver.jdbc.SQLServerDriver”;
String url = “jdbc:sqlserver://10.133.111.183:54218;DatabaseName=T_UDM_STG”;

// String driver = “com.mysql.jdbc.Driver”;
// String url = “jdbc:mysql://127.0.0.1:3306/mydata”;

Connection con = null;
ResultSet res = null;

public void DataBase(String un,String psw) {
    try {
        Class.forName(driver);

// con = DriverManager.getConnection(url, “user_aml”, “abcd.1234”);
con = DriverManager.getConnection(url, un, psw);
System.err.println(“连接数据库成功。” );
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
System.err.println(“装载 JDBC/ODBC 驱动程序失败。” );
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
System.err.println(“无法连接数据库” );
e.printStackTrace();
}
}

// 查询
public ResultSet  Search(String sql, String str[]) {

// DataBase(un, psw);
try {
PreparedStatement pst =con.prepareStatement(sql);
if (str != null) {
for (int i = 0; i < str.length; i++) {
pst.setString(i + 1, str[i]);
}
}
res = pst.executeQuery();

    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    return res;
}

// 增删修改
public int AddU(String sql, String str[],String un,String psw) {
    int a = 0;
    DataBase(un,psw);
    try {
        PreparedStatement pst = con.prepareStatement(sql);
        if (str != null) {
            for (int i = 0; i < str.length; i++) {
                pst.setString(i + 1, str[i]);
            }
        }
        a = pst.executeUpdate();
    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    return a;
}

}

---

StuEntity.java
package com;

public class StuEntity {
private int id;
private String name;
private String sex;
private int num;

public StuEntity() {
}
public StuEntity(int id, String name, String sex, int num) {
    this.id = id;
    this.name = name;
    this.sex = sex;
    this.num = num;
}

@Override
public String toString() {
    return "com.StuEntity [id=" + id + ", name=" + name + ", sex=" + sex
            + ", num=" + num + "]";
}
public int getId() {
    return id;
}
public void setId(int id) {
    this.id = id;
}
public String getName() {
    return name;
}
public void setName(String name) {
    this.name = name;
}
public String getSex() {
    return sex;
}
public void setSex(String sex) {
    this.sex = sex;
}
public int getNum() {
    return num;
}
public void setNum(int num) {
    this.num = num;
}
}

---

StuService.java
package com;
import java.io.File;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import com.StuEntity;
import com.DBhelper;

public class StuService {
/**
* 查询stu表中所有的数据
*
* @return
*/
public static List getAllByDb() {
List list = new ArrayList();
try {
DBhelper db = new DBhelper();
String sql = “select * from stu”;
ResultSet rs = db.Search(sql, null);
while (rs.next()) {
int id = rs.getInt(“id”);
String name = rs.getString(“name”);
String sex = rs.getString(“sex”);
int num = rs.getInt(“num”);

            //System.out.println(id+" "+name+" "+sex+ " "+num);
            list.add(new StuEntity(id, name, sex, num));
        }

    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    return list;
}

/**
 * 查询指定目录中电子表格中所有的数据
 *
 * @param file 文件完整路径
 * @return
 */
public static List<StuEntity> getAllByExcel(String file) {
    List<StuEntity> list = new ArrayList<StuEntity>();
    System.out.println("开始执行：getAllByExcel");
    try {
        System.out.println("开始执行：rwb");
        Workbook rwb = Workbook.getWorkbook(new File(file));
        System.out.println(rwb);
        System.out.println("开始执行：rs");
        Sheet rs = rwb.getSheet(0);//或者rwb.getSheet(0)
        System.out.println("获取：clos");
        int clos = rs.getColumns();//得到所有的列
        int rows = rs.getRows();//得到所有的行
        System.out.println("打印：表中的行和列的数");
        System.out.println("clos:" + clos + " rows:" + rows);

        for (int i = 1; i < rows; i++) {
            System.out.println("i");
            for (int j = 0; j < clos; j++) {
                //第一个是列数，第二个是行数
                String id = rs.getCell(j++, i).getContents();//默认最左边编号也算一列 所以这里得j++
                String name = rs.getCell(j++, i).getContents();
                String sex = rs.getCell(j++, i).getContents();
                String num = rs.getCell(j++, i).getContents();

                System.out.println("id:" + id + " name:" + name + " sex:" + sex + " num:" + num);
                list.add(new StuEntity(Integer.parseInt(id), name, sex, Integer.parseInt(num)));
            }
        }
    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    return list;

}

/**
 * 通过Id判断是否存在
 *
 * @param id
 * @return
 */
public static boolean isExist(int id) {
    try {
        DBhelper db = new DBhelper();
        ResultSet rs = db.Search("select * from stu where id=?", new String[]{id + ""});
        if (rs.next()) {
            return true;
        }
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    return false;
}

public static void main(String[] args) {
  /*List<StuEntity> all=getAllByDb();
  for (StuEntity stuEntity : all) {
     System.out.println(stuEntity.toString());
  }*/

    System.out.println(isExist(1));

}}

---

Logger.java
package com;

import java.io.*;

import java.text.DateFormat;

import java.text.SimpleDateFormat;

import java.util.Date;

public class Logger {
public static void Log(String b) {
try {
//标准输出流PrintStream，构造方法要传一个OutputStream

//OutputStream是一个抽象类，没办法new，采用子类FileOutputStream

        PrintStream ps = new PrintStream(new FileOutputStream("C:\\wz\\JavaProiect//log.txt",true));

        // D:\AML_Jobs\logs pre_screen_party.log

//改变PrintStream输出方向，向FileOutputStream里的路径输出

        System.setOut(ps);

//记录日志的日期，并格式化

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日，HH:mm:ss");

        Date date = new Date();

//获取当前日期的String

        String noatime = sdf.format(date);

//文字不再向控制台输出，向FileOutputStream里的文件输出

        System.out.println(noatime + "，做了：" + b);

    } catch (FileNotFoundException e) {
        e.printStackTrace();

    }

//标准输出流不需要close

}
//测试Logger类Log(String b) 方法能否执行

}