数据库连接、批量插入

insert（批量插入）

	public static void insertValue(Connection conn, List<TransObj> transList) {
		
		String sql = "insert into bank_trans ("
				+ "no, trans_dt, rec_dt, trans_currency, trans_status, trans_income, trans_expense, trans_biz_no, trans_term_no, "
				+ "trans_biz_name, trans_biz_cat, trans_biz_city, trans_biz_addr, trans_cat, trans_channel, card_class, card_attr, card_brand, "
				+ "card_product, card_level, card_addr, bank_ac, total_income, total_expense, total_trans_count, total_first_date, total_last_date"
				+ ") values(?, ?, ?, ?, ?, ?, ?, ?, ?, "
				+ "?, ?, ?, ?, ?, ?, ?, ?, ?, "
				+ "?, ?, ?, ?, ?, ?, ?, ?, ?)";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			conn.setAutoCommit(false);
			for (TransObj trans:transList) {
				ps.setString(1,trans.getNo());
				ps.setString(2,trans.getTransDt());
				ps.setString(3,trans.getRecDt());
				ps.setString(4,trans.getTransCurrency());
				ps.setString(5,trans.getTransStatus());
				ps.setString(6,trans.getTransIncome());
				ps.setString(7,trans.getTransExpense());
				ps.setString(8,trans.getTransBizNo());
				ps.setString(9,trans.getTransTermNo());
				ps.setString(10,trans.getTransBizName());
				ps.setString(11,trans.getTransBizCat());
				ps.setString(12,trans.getTransBizCity());
				ps.setString(13,trans.getTransBizAddr());
				ps.setString(14,trans.getTransCat());
				ps.setString(15,trans.getTransChannel());
				ps.setString(16,trans.getCardClass());
				ps.setString(17,trans.getCardAttr());
				ps.setString(18,trans.getCardBrand());
				ps.setString(19,trans.getCardProduct());
				ps.setString(20,trans.getCardLevel());
				ps.setString(21,trans.getCardAddr());
				ps.setString(22,trans.getBankAc());
				ps.setString(23,trans.getTotalIncome());
				ps.setString(24,trans.getTotalExpense());
				ps.setString(25,trans.getTotalTransCount());
				ps.setString(26,trans.getTotalFirstDate());
				ps.setString(27,trans.getTotalLastDate());
				ps.addBatch();
			}
			ps.executeBatch();
			conn.commit();
			conn.setAutoCommit(true);
        } catch (SQLException e) {
        	e.printStackTrace();
        }
	}

Connect

	public static Connection getConnection() {
		Connection conn = null;
        String url = "jdbc:mysql://192.168.22.248:3306/qqm?"
                + "user=guest&password=123456&useUnicode=true&characterEncoding=UTF8";
        try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(url);
        } catch (ClassNotFoundException e) {
        	e.printStackTrace();
        } catch (SQLException e) {
			e.printStackTrace();
		}
    	return conn;
	}

Select

	public static ResultSet selectValue(Connection conn) {
	    
		ResultSet rs = null;
	    String sql = "select tmp.mobile_no, tmp.name, tmp.loan_amount, tmp.terms, tmp.appl_dt," + 
	    		"tmp.zhangdan_no, tmp.ARG, tmp.DEF," +
	    		"if(tmp.DEF_days_tmp <= 0, 0, tmp.DEF_days_tmp) DEF_days " + 
	    		"from (" +
	    		"select mobile_no, name, loan_amount, terms, appl_dt, zhangdan_no," + 
	    		"IF(TO_DAYS(now()) - TO_DAYS(date_format(predict_pay_dt,'%Y-%m-%d')) > 0, 1, 0) ARG," +
	    		"IF((actual_pay_dt = '' and TO_DAYS(now()) - TO_DAYS(date_format(predict_pay_dt,'%Y-%m-%d')) > 0) ||" +
	    		"(actual_pay_dt <> '' and TO_DAYS(date_format(actual_pay_dt,'%Y-%m-%d')) - TO_DAYS(date_format(predict_pay_dt,'%Y-%m-%d')) > 0), 1, 0) DEF," +
	    		"IF(actual_pay_dt = '', TO_DAYS(now()) - TO_DAYS(date_format(predict_pay_dt,'%Y-%m-%d')), TO_DAYS(date_format(actual_pay_dt,'%Y-%m-%d')) - TO_DAYS(date_format(predict_pay_dt,'%Y-%m-%d'))) DEF_days_tmp " +
	    		"from bill_rec" +
	    		") tmp " +
	    		"order by tmp.mobile_no, tmp.name, tmp.loan_amount, tmp.terms, tmp.appl_dt, tmp.zhangdan_no";
	    try {  
	        PreparedStatement ps = conn.prepareStatement(sql);
	        rs = ps.executeQuery();  
	    } catch (SQLException e) {  
	        e.printStackTrace();  
	    }
	    return rs;
	}

读取Select后的result

	public static List<Fpd> select(Connection conn) {
		List<Fpd> fpdList = new ArrayList<Fpd>();
		Fpd preFpd = new Fpd();
		Fpd fpd = null;
		ResultSet rs = selectValue(conn);
		try {
			while(rs.next()){
				if (fpd != null)
					preFpd = fpd;
				fpd = new Fpd();
				fpd.setMobileNo(rs.getString("mobile_no"));
				fpd.setName(rs.getString("name"));
				fpd.setLoanAmount(rs.getString("loan_amount"));
				fpd.setTerms(rs.getString("terms"));
				fpd.setApplDt(rs.getString("appl_dt"));
				fpd.setZhangdanNo(rs.getInt("zhangdan_no"));
				
				fpdList.add(fpd);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return fpdList;
	}