JDBC

• JDBC編程步驟

1.注冊驅(qū)動
    Class.forName(“com.mysql.jdbc.Driver”);
2.獲取連接
    DriverManager.getConnection(url,username,password);
3.創(chuàng)建statement對象
    connection.createStatement();
4.執(zhí)行sql
    statement.executeQuery(sql);  //select
    statement.executeUpdate(sql); //update,delete,insert
  5.遍歷結(jié)果集
    While(resultSet.next()){
        resultSet.getXXX(columName/columIndex);
    }
6.釋放資源
    需要放在finally代碼塊中.
    Close();

• JDBCUtils

package cn.itcast.utils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCUtils {
    private static String driver = "oracle.jdbc.OracleDriver";
    private static String url = "jdbc:oracle:thin:@192.168.56.101:1521:orcl";
    private static String user = "scott";
    private static String password = "tiger";
    static{
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            throw new ExceptionInInitializerError(e);
        }
    }
    public static Connection getConnection(){
        try {
            return DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    /*
     *  執(zhí)行java程序
     */
    public static void release(Connection conn,Statement st,ResultSet rs){
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally{
                rs = null;//why?  ---> Java GC
            }
        }
        if(st != null){
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally{
                st = null;
            }
        }
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally{
                conn = null;
            }
        }
    }
}

• sql注入
  1. 解決了sql攻擊(拼接)
  2. statment把sql語句發(fā)送給數(shù)據(jù)庫,再編譯執(zhí)行.每次都發(fā)送,相同的語句會進(jìn)行多次編譯.
  3. preparedStatement預(yù)先編譯,使用時填充數(shù)據(jù),無需多次編譯

    Conection conn = JDBCUtils.getConnection();
    String sql ="select * from user where username = ? and password = ?";
    PreparedStatemnet ps = conn.prepareStatement(sql);
    ps.setString(1,"xiaoming");
    ps.setString(2,"666");
    ResultSet rs = ps.execute(sql);
    while(rs.next()){
        }

• JDBC批處理

    String sql1="";
    String sql2="";
    String sql3="";
    st.addBatch(sq1);
    st.addBatch(sq2);
    st.addBatch(sq3);
    st.executeBatch();
    st.clearBatch();

• JDBC事務(wù)

  事務(wù):控制業(yè)務(wù)邏輯的基本單元.事務(wù)管理的一組sql語句,要么都成功,要么都失敗,不可分割.

  成功commit,失敗rollback
  事務(wù)回滾點(diǎn):理解成游戲的存檔

try{
    Connection conn = JDBCUtils.getConnection();
    conn.setAutoCommit(false);
    st = conn.createStatement();
    st.executeUpdate("delete from user where name='xiaoming'");
    int i=1/0;
    conn.commit();
    }catch(Exception e){
        try {
            con.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
    }