java 原生JDBC操作數(shù)據(jù)庫(kù)，是比較常見(jiàn)的面試題，所以一定要牢牢掌握住。

記住一般步驟，就好寫代碼了。

1、Class.forName()加載數(shù)據(jù)庫(kù)連接驅(qū)動(dòng)。

2、DriverManager.getConnection()獲取數(shù)據(jù)連接對(duì)象。

3、根據(jù)SQL語(yǔ)句獲取會(huì)話對(duì)象，會(huì)話對(duì)象一般用PReparedStatement類型，conn.prepareStatement()，注意方法名和類名不一樣喲。

4、執(zhí)行SQL處理結(jié)果集，執(zhí)行SQL前如果有參數(shù)值就設(shè)置參數(shù)值setXXX()。

5、關(guān)閉結(jié)果集、關(guān)閉會(huì)話、關(guān)閉連接。

MySQL://localhost:3306/JAVA?user=root&passWord=root&useUnicode=true&charactorEncoding=UTF8";          String url2="jdbc:mysql://localhost:3306/JAVA?useUnicode=true&charactorEncoding=UTF8";          String url3="jdbc:mysql://localhost:3306/JAVA";          String user="root";          String password="root";          Connection conn3 = null;          Savepoint point1 = null;                    try {              //1、加載數(shù)據(jù)庫(kù)驅(qū)動(dòng)，包名一般為域名反寫              Class.forName("com.mysql.jdbc.Driver");              Properties info = new Properties();              info.put("user", "root");              info.put("password", "root");              info.put("useUnicode","true");              info.put("charactorEncoding","utf8");              /*              * 2、獲取數(shù)據(jù)連接對(duì)象，DriverManager.getConnection方法有3個(gè)重載方法              */  //          Connection conn1 = DriverManager.getConnection(url3, user, password);//將連接的用戶名、密碼放到方法參數(shù)中  //          Connection conn2 = DriverManager.getConnection(url2,info);//將連接的屬性鍵值對(duì)放在Properties對(duì)象中              conn3 = DriverManager.getConnection(url1);//將所有連接信息都放在URL中              /*              * 3、根據(jù)SQL獲取sql會(huì)話對(duì)象，有2種方式 Statement、PreparedStatement              *      1、 PreparedStatement接口繼承Statement， PreparedStatement 實(shí)例包含已編譯的 SQL 語(yǔ)句，所以其執(zhí)行速度要快于 Statement 對(duì)象。              *      2、作為 Statement 的子類，PreparedStatement 繼承了 Statement 的所有功能。三種方法  execute、 executeQuery 和 executeUpdate 已被更改以使之不再需要參數(shù)              *      3、在JDBC應(yīng)用中,如果你已經(jīng)是稍有水平開(kāi)發(fā)者,你就應(yīng)該始終以PreparedStatement代替 Statement.也就是說(shuō),在任何時(shí)候都不要使用Statement.              *          一.代碼的可讀性和可維護(hù)性.Statement需要不斷地拼接，而PreparedStatement不會(huì)。              *          二.PreparedStatement盡最大可能提高性能.DB有緩存機(jī)制，相同的預(yù)編譯語(yǔ)句再次被調(diào)用不會(huì)再次需要編譯。              *          三.最重要的一點(diǎn)是極大地提高了安全性.Statement容易被SQL注入，而PreparedStatementc傳入的內(nèi)容不會(huì)和sql語(yǔ)句發(fā)生任何匹配關(guān)系。              * 最常用的api              * addBatch()/addBatch(String sql) 預(yù)編譯SQL語(yǔ)句,只編譯一回哦,效率高啊。剝瓜子，一個(gè)一個(gè)剝，最后一口吃。              * setXXX(parameterIndex,value) 設(shè)置指定參數(shù)的值。              * execute()  執(zhí)行 SQL 語(yǔ)句，該語(yǔ)句可以是任何種類的 SQL 語(yǔ)句。              * executeQuery() 執(zhí)行查詢語(yǔ)句返回ResultSet              * executeUpdate() 執(zhí)行增刪改，返回影響的行數(shù)。              *               */              String sql_other = "TRUNCATE TABLE t_user_info";              String sql_insert = "insert into t_user_info(id,user,sex,age) value(null,?,?,?)";              String sql_query = "select * from t_user_info where id < ?";              conn3.setAutoCommit(false);//關(guān)閉自動(dòng)提交              PreparedStatement preparedStatement_other = conn3.prepareStatement(sql_other);              PreparedStatement preparedStatement_insert = conn3.prepareStatement(sql_insert);              PreparedStatement preparedStatement_query = conn3.prepareStatement(sql_query);              preparedStatement_other.execute();                                          for(int i=0;i<100;i++){                  preparedStatement_insert.setString(1, "user_"+i);                  preparedStatement_insert.setString(2, "n");                  preparedStatement_insert.setInt(3, i);                  if(i>50){  //                  preparedStatement_insert.setString(3, "我是錯(cuò)誤的嘗試");                  }                  preparedStatement_insert.addBatch();              }              //一次性插入100條記錄，如果中間有出錯(cuò)，那么這一次性的插入都不會(huì)成功              int[] updateInt = preparedStatement_insert.executeBatch();              conn3.commit();              point1 = conn3.setSavepoint("point1");              System.out.println(updateInt.length);                            preparedStatement_query.setInt(1, 50);              //4、執(zhí)行SQL語(yǔ)句，查詢語(yǔ)句就獲取結(jié)果集              ResultSet result = preparedStatement_query.executeQuery();              while(result.next()){                  int a = result.getInt("id");                  int b = result.getInt(1);                  System.out.println("第一列值，通過(guò)列名:"+a+",通過(guò)下標(biāo)："+b);                                }              /*              * 5、關(guān)閉結(jié)果集、關(guān)閉會(huì)話、關(guān)閉連接              */              result.close();              preparedStatement_other.close();              preparedStatement_insert.close();              preparedStatement_query.close();              conn3.close();                        } catch (ClassNotFoundException e) {              System.out.println("驅(qū)動(dòng)沒(méi)有加載到。。。");              e.printStackTrace();          } catch (SQLException e) {              System.out.println("出現(xiàn)sql異常。。。");              try {                  conn3.rollback(point1);;              } catch (SQLException e1) {                                    e1.printStackTrace();              }              e.printStackTrace();          }      }  }  JDBC%20進(jìn)一步封裝

我們將獲取連接和釋放連接封裝到工具類中，執(zhí)行SQL的感覺(jué)沒(méi)有必要封裝，這樣更靈活。

[java] view%20plain copy package com.lcx.test;    import java.sql.Connection;  import java.sql.DriverManager;  import java.sql.ResultSet;  import java.sql.SQLException;  import java.sql.Statement;    public class JDBCUtil {      /**      * jdbc:mysql://主機(jī)名稱：連接端口/數(shù)據(jù)庫(kù)的名稱?參數(shù)=值      * String url = "jdbc:mysql://localhost:3306/JAVA?user=root&password=root&useUnicode=true&characterEncoding=UTF8";      * 也可在獲取連接的時(shí)候使用用戶名和密碼      * 避免中文亂碼要指定useUnicode和characterEncoding, 執(zhí)行數(shù)據(jù)庫(kù)操作之前要在數(shù)據(jù)庫(kù)管理系統(tǒng)上創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)，名字自己定，      */      private static final String url="jdbc:mysql://localhost:3306/JAVA";      private static final String user="root";      private static final String password="root";      private static Connection conn;      //靜態(tài)代碼塊，類加載時(shí)就加載驅(qū)動(dòng)      static {          //1、加載驅(qū)動(dòng)          try {              Class.forName("com.mysql.jdbc.Driver");          } catch (ClassNotFoundException e) {              System.out.println("Exception: com.mysql.jdbc.driver not found Exception");              e.printStackTrace();          }      }      private JDBCUtil(){                }      //2獲取連接的方法，只產(chǎn)生一個(gè)連接      public static Connection getConnection(){          try {              if(conn==null){                  synchronized (JDBCUtil.class) {                      if(conn==null){                          conn = DriverManager.getConnection(url, user, password);                      }                  }              }          } catch (SQLException e) {              System.out.println("Exception: get mysql connection Exception");              e.printStackTrace();          }          return conn;      }      //最后釋放資源，中間操作由preparedStatement 去操作更靈活      public static void free(ResultSet result,Statement statement,Connection connection){          try {              if(result!=null){//關(guān)閉結(jié)果集                  result.close();              }          } catch (SQLException e) {              e.printStackTrace();          }finally{              try {                  if(statement!=null){//關(guān)閉statement會(huì)話                      statement.close();                  }              } catch (SQLException e) {                  e.printStackTrace();              }finally{                  try {                      if(connection!=null){                          connection.close();//關(guān)閉連接                      }                  } catch (SQLException e) {                      e.printStackTrace();                  }              }                        }                }  }  使用工具類操作數(shù)據(jù)處理

[java] view%20plain copy package com.lcx.test;    import java.sql.Connection;  import java.sql.PreparedStatement;  import java.sql.ResultSet;  import java.sql.SQLException;    public class JDBCTest {        public static void main(String[] args) {          //1、加載驅(qū)動(dòng)、獲取連接          Connection connection = JDBCUtil.getConnection();          String sql_init ="TRUNCATE TABLE t_user_info";          String sql_insert = "insert into t_user_info(id,user,sex,age) value(null,?,?,?)";          String sql_query = "select * from t_user_info where id < ?";          PreparedStatement ps_init;          PreparedStatement ps_insert;          PreparedStatement ps_query;          try {              connection.setAutoCommit(false);              //2、根據(jù)sql語(yǔ)句獲取 獲取預(yù)編譯語(yǔ)句對(duì)象              ps_init = connection.prepareStatement(sql_init);              ps_insert = connection.prepareStatement(sql_insert);              ps_query = connection.prepareStatement(sql_query);                            ps_init.execute();//可以執(zhí)行任何種類的SQL語(yǔ)句              //3、有參數(shù)值，就設(shè)置參數(shù)值，然后執(zhí)行預(yù)編譯語(yǔ)句，獲取結(jié)果集              for(int i=0;i<100;i++){                  ps_insert.setString(1, "user_"+i);                  ps_insert.setString(2, "n");                  ps_insert.setInt(3, i);                  ps_insert.addBatch();//增加的批處理隊(duì)列中              }              ps_insert.executeBatch();//一次性執(zhí)行                            ps_query.setInt(1, 50);              //查詢記錄，獲取結(jié)果集              ResultSet result = ps_query.executeQuery();              while(result.next()){                  int a = result.getInt("id");                  int b = result.getInt(1);                  System.out.println("第一列通過(guò)列明:"+a+",第一列通過(guò)index:"+b);              }              connection.commit();              //5、釋放資源              JDBCUtil.free(result, ps_query, connection);              JDBCUtil.free(result, ps_init, connection);              JDBCUtil.free(result, ps_insert, connection);          } catch (SQLException e) {              try {                  connection.rollback();              } catch (SQLException e1) {                  e1.printStackTrace();              }              e.printStackTrace();          }                }    }