建立數(shù)據(jù)庫連接

          //1.使用Driver連接數(shù)據(jù)庫，創(chuàng)建Driver實現(xiàn)類對象
    Driver driver = new Driver();
    
    //2.準備連接數(shù)據(jù)庫的基本信息
    String url = "jdbc:mysql://127.0.0.1:3306/test";
    Properties info = new Properties();
    
    info.put("user", "root");
    info.put("password", "root");
    
    //3.調用Driver的接口connect（url， info） 獲取數(shù)據(jù)庫連接
    Connection connection = driver.connect(url, info);

簡化連接不同數(shù)據(jù)庫，將連接數(shù)據(jù)庫信息存儲到文件中

    InputStream in = TestJDBC.class.getClassLoader().getResourceAsStream("jdbc.properties");
    Properties properties = new Properties();
    properties.load(in);
    driverClass = properties.getProperty("driver");
    jdbcUrl = properties.getProperty("jdbcUrl");
    user = properties.getProperty("user");
    password = properties.getProperty("password");

    Driver driver = (Driver)Class.forName(driverClass).newInstance();
    
    Properties info = new Properties();
    info.put("user", user);
    info.put("password", password);
    
    Connection connection = driver.connect(jdbcUrl, info);

通過獲取驅動連接數(shù)據(jù)庫，可同時獲得多個驅動

    Class.forName(driverClass);
    Connection connection = DriverManager.getConnection(jdbcUrl, user, password);

執(zhí)行Sql語句

insert，update， delete操作

    //1.獲取數(shù)據(jù)庫連接
    Connection conn = getConnection();
    
    //2.準備sql語句
    String sql = "insert into customers(name, email, birth) Values('Wayne', 'wayne@gmail.com', '1993-03-16');";
    
    //3.執(zhí)行插入
    //獲取Statement對象
    Statement statement = conn.createStatement();
    //執(zhí)行sql語句，只能是insert，update，delete
    statement.executeUpdate(sql);
    
    //4.關閉連接，在finally中關閉
    statement.close();
    conn.close();

select操作

• 通過調用 Statement 對象的 excuteQuery() 方法創(chuàng)建該對象

• ResultSet 對象以邏輯表格的形式封裝了執(zhí)行數(shù)據(jù)庫操作的結果集

• ResultSet 對象維護了一個指向當前數(shù)據(jù)行的游標，初始的時候，游標在第一行之前，可以通過 ResultSet 對象的 next() 方法移動到下一行

    //1.獲取數(shù)據(jù)庫連接
    Connection conn = getConnection();
    
    //2.獲取Statement
    Statement statement = conn.createStatement();
    
    //3.準備sql語句
    String sql = "select * from customers";
    
    //4.執(zhí)行查詢
    ResultSet rs = statement.executeQuery(sql);
    
    //5.處理ResultSet
    while(rs.next()){
        int id = rs.getInt(1);
        String name = rs.getString("name");
        String email = rs.getString(3);
        Date birth = rs.getDate(4);
        
        System.out.println(id + " " + name + " " + email + " " + birth);
    }
  

PreparedStatement

• 提高性能

• 防止sql注入

• 便捷sql語句拼寫
  //1.創(chuàng)建PreparedStatement
  String sql = "insert into customers(name, email, birth) Values(?,?,?);";
  PreparedStatement ps = conn.prepareStatement(sql);

    //2.賦值
    ps.setString(1, "Luke");
    ps.setString(2, "Luke@gmail.com");
    ps.setDate(3, new Date(new java.util.Date().getTime()));
    
    //3.執(zhí)行
    ps.executeUpdate();
  

使用ResultSetMetaData和反射，簡化查詢，構造方法完成對不同表查詢，結果生成不同對象

public static <T> T testResultSetMetaData(Class<T> clazz, String sql, Object...objects) throws Exception{
    //1.建立連接
    Connection conn = getConnection();
    
    //2.獲取PreparedStatement
    PreparedStatement ps = conn.prepareStatement(sql);
    
    //3.賦值
    for (int i = 0; i<objects.length; i++){
        ps.setObject(i+1, objects[i]);
    }
    
    //4.執(zhí)行，并使用ResultSet接受
    ResultSet re = ps.executeQuery();
    
    
    if(re.next()){
        //5.創(chuàng)建clazz對象
        T t = clazz.newInstance();
        
        //6、獲取ResultSetMetaData，并放入對象
        ResultSetMetaData rsmd = re.getMetaData();
        for(int i=0; i<rsmd.getColumnCount(); i++){
            String columnLable = rsmd.getColumnLabel(i+1);
            Object columnValue = re.getObject(i+1);
            
            //為對象屬性賦值
            Field field = clazz.getDeclaredField(columnLable);
            field.setAccessible(true);
            field.set(t, columnValue);
            
        }
        return t;
    } else {
        return null;
    }
    
}

存儲過程

    //1.建立連接 
    Connection conn = getConnection();

    //2.獲取CallableStatement
    String sql = "{? = call sum_salary(?,?)}";
     CallableStatement callableStatement = conn.prepareCall(sql);

    //3.注冊參數(shù)
    callableStatement.registerOutParameter(1,Types.NUMERTIC);
    callableStatement.registerOutParameter(3,Types.NUMERTIC);

    //4.給參數(shù)賦值
    callableStatement.setInt(2,80);

    //5.執(zhí)行
    callableStatement.execute();

    //6.獲取執(zhí)行結果
    double sunSalary = callableStatement.getDouble(1);
    long empCount = callableStatement.getLong(3);

小結

• java.sql.DriverManager用來裝載驅動程序，獲取數(shù)據(jù)庫連接。
• java.sql.Connection完成對某一指定數(shù)據(jù)庫的聯(lián)接
• java.sql.Statement在一個給定的連接中作為SQL執(zhí)行聲明的容器，他包含了兩個重要的子類型。
  • Java.sql.PreparedSatement 用于執(zhí)行預編譯的sql聲明
  • Java.sql.CallableStatement用于執(zhí)行數(shù)據(jù)庫中存儲過程的調用
    java.sql.ResultSet對于給定聲明取得結果的途徑