插入数据库

从list把数据插入到数据库对应表去

/**
 * 把从Excel读出来的数据插入到数据库里面去
 * 
 * @author sakyone
 * 
 */
public class ExcelForCaseLog {

	public void importCaseLog(List list) {

		int successCount = 0;
		int failCount = 0;
		CaseLog CaseLog = new CaseLog();
		// JDBCapp jdbccapp = new JDBCapp();
		String context[] = new String[list.size()];
		// 把String转换为 “年月日”的格式
		SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd");
		System.err.println("---------共有数据：" + list.size() + "行-----------");
		System.err.println("---------共有记录：" + (list.size() - 2)
				+ "条-----------");
		for (int i = 0; i < list.size(); i++) {
			context = (String[]) list.get(i);
			for (int j = 0; j < context.length; j++) {
				System.err.println("----------第" + i + "行第" + j + "列值为："
						+ context[j]);

				// 从下标为2（实际对应第3行，第一行一般为表头，第二行为字段名称，第三行为实际内容）开始录入CaseLog
				if (i >= 2 && j >= 1) {
					switch (j) {
					case 2:
						CaseLog.setAj_mc(context[j]);
						break;
					case 3:
						CaseLog.setLogdate(context[j]);
						break;
					case 4:
						CaseLog.setLogry(context[j]);
						break;
					case 5:
						CaseLog.setLogitem(context[j]);
						break;
					case 6:
						CaseLog.setLogbz(context[j]);
						break;
					case 7:
						CaseLog.setCxNumber(context[j]);
						break;
					case 8:
						CaseLog.setCxPassword(context[j]);
						break;
					case 9:
						CaseLog.setBCxNumber(context[j]);
						break;
					case 10:
						CaseLog.setBCxPassword(context[j]);
						break;
					
					default:
						break;
					}// end switch
				}// end if(i>=2&&j>=1)

			}// end for j
			// 从第3行开始插入数据库
			if (i >= 2) {
				final String sql = "insert into CaseLog("
						+ "aj_mc,logdate,logry,logitem,logbz,"
						+ "CxNumber,CxPassword,bCxNumber,bCxPassword)" + " values "					
						+ "(?,?,?,?,?," + "?,?,?,?)";
				try {
					JDBCapp.theConn = MyConnection.getConnection();
					PreparedStatement insertStatement = JDBCapp.theConn
							.prepareStatement(sql);
					insertStatement.setString(1, CaseLog.getAj_mc());
					insertStatement.setString(2, CaseLog.getLogdate());
					insertStatement.setString(3, CaseLog.getLogry());
					insertStatement.setString(4, CaseLog.getLogitem());
					insertStatement.setString(5, CaseLog.getLogbz());
					insertStatement.setString(6, CaseLog.getCxNumber());
					insertStatement.setString(7, CaseLog.getCxPassword());
					insertStatement.setString(8, CaseLog.getBCxNumber());
					insertStatement.setString(9, CaseLog.getBCxPassword());					

					// 执行插入数据
					insertStatement.executeUpdate();
					System.err.println("----------成功导入第" + i + "行记录");
					successCount++;
				} catch (Exception e) {
					System.err.println("--------导入第" + i
							+ "行记录失败,出错原因可能是格式不符--------");
					failCount++;
					e.printStackTrace();
				} finally {
					if (JDBCapp.theConn != null) {
						try {
							JDBCapp.theConn.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
				}// end fianally
			}
		}//end for i
		System.out.println("-------CaseLog表共成功导入" + successCount
				+ "条记录----------");
		System.out.println("-------CaseLog表共有" + failCount
				+ "条记录导入失败------------");
	}