数据库连接、批量插入

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;}