java之 jdbc连接数据库与操作excel文件代码

1 import java.io.FileInputStream;
2 import java.sql.Connection;
3 import java.sql.DriverManager;
4 import java.sql.SQLException;
5 import java.sql.Statement;
6 import java.text.DateFormat;
7 
8 import org.apache.poi.hssf.usermodel.HSSFCell;
9 import org.apache.poi.hssf.usermodel.HSSFRow;
10 import org.apache.poi.hssf.usermodel.HSSFSheet;
11 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
12 
13 public class Test {
14 public static String fileToBeRead="D:/aa.xls"; 
15 public static void main(String[] args) {
16 
17 Connection conn = null;
18 Statement stmt = null;
19 String url = "jdbc:oracle:thin:@localhost:1521:orcl";
20 String username = "用户名";
21 String password = "密码"; 
22 
23 try{ 
24 // 创建对Excel工作簿文件的引用
25 FileInputStream fis = new FileInputStream(fileToBeRead);
26 HSSFWorkbook workbook = new HSSFWorkbook(fis);
27 
28 // 创建对工作表的引用。按照sheet名引用
29 HSSFSheet sheet = workbook.getSheet("sheet1"); 
30 
31 // 也可用getSheetAt(int index)按索引引用，在Excel文档中，第一张工作表的缺省索引是0，
32 // 其语句为：HSSFSheet sheet = workbook.getSheetAt(0);
33 
34 Class.forName("oracle.jdbc.driver.OracleDriver");
35 
36 conn = DriverManager.getConnection(url, username, password);
37 
38 //设置事务提交模式
39 //conn.setAutoCommit(true);
40 //若禁止了自动提交模式,那么在关闭Connection对象时会执行一次自动隐式提交,
以保证还没有提交的所有DML语句被自动提交 
41 conn.setAutoCommit(false);
42 
43 for (int i=1; i<=100;i++)
44 {
45 HSSFRow row = sheet.getRow(i);
46 HSSFCell cell_id = row.getCell(0);
47 HSSFCell cell_name = row.getCell(1);
48 HSSFCell cell_sex = row.getCell(2);
49 HSSFCell cell_age = row.getCell(3);
50 HSSFCell cell_birthday = row.getCell(4);//出生时间（精确到几点几分） 
51 
52 try { 
53 //创建JDBC Statement对象
54 stmt = conn.createStatement();
55 
56 //格式化日期格式 与 时间格式
57 String strBirthday_day = DateFormat.getDateInstance().
format(cell_birthday.getDateCellValue()); 
58 String strBirthday_Time = DateFormat.getTimeInstance().
format(cell_birthday.getDateCellValue());
59 String strBirthday = strBirthday_day + " " + strBirthday_Time;
60 
61 //向数据库中添加行
62 @SuppressWarnings("unused")
63 int sql = stmt.executeUpdate("insert into customers values (" 
+ cell_id.getNumericCellValue() 
64 + "," + cell_name.getStringCellValue() 
+ "," + cell_sex.getNumericCellValue() 
65 + ",'" + cell_age.getNumericCellValue() 
+ ",TO_DATE('" + strBirthday + "','yyyy-MM-DD')" 
+ ")");
66 
67 /*select 语句用executeQuery()
68 insert,update,delete语句用executeUpdate()
69 若预先不知道要执行的SQL语句类型，那么用execute()
70 */
71 //从数据库获取行
72 ResultSet rs = stmt.executeQuery("select id,name,sex,age,birthday from customers");
73 
74 while (rs.next()) {
75 long id= rs.getLong("id");
76 String name= rs.getString("name");
77 int sex = rs.getInt("sex");
78 int age = rs.getInt("age");
79 Date birthday = rs.getDate("birthday");
80 }
81 
82 rs.close();
83 
84 //修改数据中的行
85 int sql_update = stmt.executeUpdate("update customers set NAME='张三' 
where id=10000000000001");
86 
87 //从数据库中删除行
88 int sql_delete = stmt.executeUpdate("delete from customers where id=10000000000001");
89 
90 //执行数据定义语言语句(DDL:CREATE,ALTER,DROP)----采用execute()方法执行DDL语句
91 //执行DDL语句会导致一次隐式提交,因此,如果你在发出DDL语句之前执行了一些未提交的DML语句,
那么这些DML语句将被提交
92 boolean result = stmt.execute("create table customers(" +
93 "id integer constraint customers_pK primary key," +
94 "first_name varchar2(10) not null," +
95 "last_name varchar2(10) not null," +
96 "dob date," +
97 "phone varchar2(15)" +
98 "");
99 if (result == true) {
100 System.out.println("The table has Created!");
101 }
102 else {
103 System.out.println("The table hasn't Create");
104 }
105 
106 //每10行提交一次
107 if (i % 10 == 0)
108 {
109 conn.commit(); 
110 } 
111 
112 System.out.println(i);//在控制台打印出执行情况。测试用。
113 }catch (Exception e) {
114 System.out.println(i + "重复值"); 
115 e.printStackTrace();//打印出异常
116 try {
117 conn.rollback();
118 }
119 catch (SQLException sqle) { 
120 }
121 
122 continue;//如果有主键或索引重复，跳出循环。
123 }
124 
125 //关闭游标
126 stmt.close(); 
127 }
128 conn.commit();
129 conn.close();
130 fis.close(); 
131 }catch(Exception e) {
132 System.out.println("已运行xlRead() : " + e );
133 } 
134 }
135 }
136