accountdao.java

JDBC连数据库,这是一门很基初的技术,一定要好好研究

package com.allanlxf.jdbc.core20;

import java.sql.*;
import java.util.*;
import com.allanlxf.jdbc.core.Account;

import com.allanlxf.jdbc.util.ConnectionFactory;
import com.allanlxf.jdbc.util.JdbcUtil;

public class AccountDao
{
    public void insertByBatchStatement(Collection accounts)
    {
        Connection con = null;
        Statement st = null;
        
        try
        {
            con = ConnectionFactory.getConnection();
            con.setAutoCommit(false);
            st = con.createStatement();
            
            Iterator iter = accounts.iterator();
            while(iter.hasNext())
            {
                Account account = (Account)iter.next();
                StringBuffer sql = new StringBuffer();
                sql.append("insert into sd0703_account(id, no, pwd,owner, balance, cdate)");
                sql.append(" values(");
                sql.append(account.getId());
                sql.append(",'");
                sql.append(account.getNo());
                sql.append("','");
                sql.append(account.getPassword());
                sql.append("','");
                sql.append(account.getOwner());
                sql.append("',");
                sql.append(account.getBalance());
                sql.append(",to_date('");
                sql.append(account.getCreateDate());
                sql.append("','yyyy-mm-dd')");
                sql.append(")");
                System.out.println(sql.toString());
                st.addBatch(sql.toString());
            }
                
            st.executeBatch();
            con.commit();         
        }catch(SQLException e)
        {
            try
            {
                con.rollback();
            }catch(Exception re)
            {
            }
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }finally
        {
            JdbcUtil.close(st, con);
        }
    }
    
    public void insertByBatchPreparedStatement(Collection accounts)
    {
        Connection con = null;
        PreparedStatement ps = null;
        
        try
        {
            con = ConnectionFactory.getConnection();
            con.setAutoCommit(false);
            
            String sql = "insert into sd0703_account(id, no, pwd,owner, balance, cdate)";
            sql += " values(?,?,?,?,?,?)";
            ps = con.prepareStatement(sql);
            
            Iterator iter = accounts.iterator();
            while(iter.hasNext())
            {
                Account account = (Account)iter.next();
                int index = 1;
                ps.setInt(index++, account.getId());
                ps.setString(index++, account.getNo());
                ps.setString(index++, account.getPassword());
                ps.setString(index++, account.getOwner());
                ps.setDouble(index++, account.getBalance());
                ps.setDate(index++, account.getCreateDate());
                
                ps.addBatch();
            }       
                 
            ps.executeBatch();
            con.commit();
        }catch(SQLException e)
        {
            try
            {
                con.rollback();
            }catch(Exception re)
            {
            }
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }finally
        {
            JdbcUtil.close(ps, con);
        }
    }
    
    public static void main(String[] args)
    {
        AccountDao accountDao = new AccountDao();
        
        List<Account> accounts = new ArrayList<Account>();
        for(int i = 6170; i <= 6180; i++)
        {
            Account account = new Account();
            account.setId(i);
            account.setNo("acc[" + i + "]");
            account.setOwner("u(" + i + ")");
            account.setPassword("123456");
            account.setBalance(10000);
            account.setCreateDate(java.sql.Date.valueOf("2007-07-18"));
            
            accounts.add(account);
        }
        
        accountDao.insertByBatchPreparedStatement(accounts);
    }
}