Java個人技術知識點總結（資料庫篇）

資料庫篇

JDBC連線資料庫步驟(以MYSQL為例)

1、載入JDBC驅動程式：   

透過Class類的forName方法實現，並將驅動地址放進去

成功載入後，會將Driver類的例項註冊到DriverManager類中。   

2、提供JDBC連線的URL 、建立資料庫的連線   

•要連線資料庫，需要向java.sql.DriverManager請求並獲得Connection物件，   

該物件就代表一個資料庫的連線。

•使用DriverManager的getConnectin()方法傳入指定的欲連線的資料庫的路徑、數 據庫的使用者名稱和密碼。

     Connection con=DriverManager.getConnection(url , username , password);

&&&:"jdbc:mysql://localhost/test?user=root&password=123&useUnicode=true&characterEncoding=utf-8”;  

3、建立一個Statement   

      •要執行SQL語句，必須獲得java.sql.Statement例項

•執行靜態SQL語句。通常透過Statement例項實現。   

•執行動態SQL語句。通常透過PreparedStatement例項實現。   

        String sql = “”;

        Statement st = con.createStatement() ;   

        PreparedStatement pst = con.prepareStatement(sql) ;

4、執行SQL語句   

Statement介面提供了executeQuery、executeUpdate、execute三種方法  

executeQuery：執行select語句，返回ResultSet結果集

ResultSet rst = pst.executeQuery();  

•  executeUpdate：執行insert、update、delete語句

   pst.executeUpdate();

5、關閉JDBC物件    

操作完成以後要把所有使用的JDBC物件全都關閉，以釋放JDBC資源。

資料庫連線池

資料庫連線池的優點執行原理:

在我們不使用資料庫連線池的時候，每次訪問資料庫都需要建立連線，使用完成之後需要釋放關閉連線，而這樣是很耗費資源的。當我們使用資料庫連線池的時候，在tomcat啟動的時候就建立了指定數量的連線，之後當我們程式使用的時候就直接從連線池裡面取，而不需要建立，同理，當我們使用完的時候也不需要關閉連線，而是將連線返回到連線池中，供其他請求繼續使用。

DBCP：比較穩定。

C3P0:效能比較高。

mysql的資料庫匯入匯出

 配置：

首先找到mysql的安裝目錄，進入bin目錄下複製路徑、將mysql的bin目錄貼上在計算機環境變數的path中、授權：、登入mysq、將某張表的某個許可權賦給某個使用者。

grant [select,insert,update,delete,create,drop] on [databaseName].[tableName]   to [userName]@[userIP] identified by [‘連線口令’]

grant select,insert,update,delete,create,drop on oa_ssh.user to root@[IP]  identified by 'root';

將所有庫的所有許可權賦給某個使用者

grant all privileges on *.* to [userName]@[userIp] identified by [‘連線口令’]

grant all privileges on *.* to root@[IP] identified by ‘root';

將所有庫的所有許可權賦給所有使用者

　　　　grant all privileges on *.* to root@'%' identified by ‘root’;

　　　　匯出本地資料庫：

　　　　mysqldump -u使用者名稱 -p 資料庫名 > 磁碟：匯出的檔名(加字尾)

　　　　遠端匯出資料庫：

　　　　mysqldump -h IP -u使用者名稱 -p 資料庫名稱 >匯出的檔名(加字尾)

　　　　遠端匯出資料表：

　　　　mysqldump -u root -p -d --add-drop-table資料庫名稱 > 匯出檔名(加字尾)

　　　　匯入資料：

　　　　mysql -u root -p登入成功後 ==》 source 磁碟：匯入的檔名(加字尾)

jdbc分段批次提交的時候出現異常怎麼處理?

      透過Map來解決效能問題。首先在分段批次提交的時候，我們不採用事務，這樣就保證了合法的資料就自動提交，不合法的資料就自己自動進行回滾，為了避免不合法資料影響後續合法資料的提交，採用定義業務規則字典表，實現對資料的驗證，將不合法的資料記錄下來，供使用者進行後續處理，而合法的資料就全部提交。

jdbc批次處理資料

批次處理資料:(程式碼最佳化:提高程式執行效能)降低了java程式程式碼(客戶端)和資料庫之間的 網路通訊的次數。在jdbc中進行批次插入的核心API為 addBatch,executeBatch大資料量的插入問題:（jdbc,hibernate,ibatis）

1.每次只插入一條和資料庫互動多次(很耗時間)

2.批次插入和資料庫只互動一次(記憶體溢位)

3.分段批次插入(推薦)

    jdbc批次處理資料是透過PreparedStatement物件的 addbatch(), executebatch（） clearbatch()進行和資料庫的互動。通常我們使用分段批次處理的方式 這樣可以提高程式的效能 ，防止記憶體溢位。

　1.每個sql語句都和資料庫互動一次(非批次操作)

　2.只和資料庫互動一次(批次操作)(記憶體溢位)

　當資料達到一定額度的時候就和資料庫進行互動，分多次進行(分段批次操作)

　(500或者1000)

pst.addBatch();

             if (i > 0 && i%1000 == 0) {

                                        pst.executeBatch();

                                        pst.clearBatch();

 }

Oracle分頁

select * from (select * from (select s.*,rownum rn from student s ) where rn<=5) where rn>0

如果你依然覺得有些茫然，不如加入我的Java架構師之路：766529531 跟有多年Java開發經驗的資深工程師聊一聊。也可獲取免費的影片學習資料以及電子書學習資料喔！

Oracle的基本資料型別

  Oracle的基本資料型別（常用）：

1、字元型

Char固定長度字串  佔2000個位元組

Varchar2可變長度字串 佔4000個位元組

Nvarchar2佔2000個字元（最多能存2000個字母/中文）

2、大物件型（lob）

Blob：二進位制資料 最大長度4G

Blob用於存一些圖片，影片，檔案。

比如：當我們在進行檔案上傳時，我們一般把上傳的檔案存在硬碟上，可以不佔用 資料庫，下載時，如果專案遷移時，檔案也要跟著遷移。因此我們可以把用blob把它存在資料庫中。但這樣也增加了資料庫的負擔。

Clob：字元資料 最大長度4G，可以存大字串  varchar2和nvarchar2都具有一定的侷限性，它們長度有限，但資料庫中無論用varchar2或nvarchar2型別，還是用clob，在java端都使用String接收。

3、數值型

Integer整數型別，小的整數。

Float浮點數型別。

Real實數型別。

Number（p,s）包含小數位的數值型別。P表示精度，s表示小數後的位數。

                Eg: number(10,2)表示小數點之前可有8位數字，小數點後有2位。

4、日期型別

Date日期（日-月-年） DD-MM-YY(HH-MI-SS)

Timestamp跟date比 它可以精確到微秒。精確範圍0~9 預設為6.

id、rowid、rownum的區別

rowid物理位置的唯一標識。

而id是邏輯上的唯一標識，所以rowid查詢速度要快於id,是目前最快的         

定位一條記錄的方式

rowid和rownum都是"偽數列"

所謂“偽數列”也就是預設隱藏的一個數列。

rownum用於標記結果集中結果順序的一個欄位，

它的特點是按順序標記，而且是連續的，

換句話說就是隻有有rownum=1的記錄，才可能有rownum=2的記錄。

rownum關鍵字只能和<或者<=直接關聯

如果是>或者=則需要給他起個別名

主鍵和唯一索引的區別？  

在建立主鍵的同時會生成對應的唯一索引，主鍵在保證資料唯一性的同時不允許為空，而唯一可以有一個為空資料項，一個表中只能有一個主鍵，但是一個主鍵可以有多個欄位，一個表中可以有多個唯一索引。

Preparedstatement和statement的區別

     用Prepared statement進行開發。Prepared statement是預編譯的，而statement不是，在每次執行sql語句的增刪改時，如果是一條資料兩者沒差距，但如果資料量大於1，那麼每次執行sql語句statement都要重新編譯一次，而Prepared statement不用，Prepared statement的執行效率大於statement；從程式碼的可維護性和可讀性來說，雖然用Prepared statement來代替statement會使程式碼多出幾行，但這樣的程式碼無論從可讀性還是可維護性來說，都比直接使用statement的程式碼高很多檔次；最重要的一點，從安全形度來說，使用Prepared statement可以大大提高程式的安全性，因為Prepared statement是用‘？’傳參,可以防止sql注入，具有安全性，而statement用的是‘+’字串拼接，安全性較低。

資料庫三正規化

       第一正規化：資料庫表中的所有欄位值都是不可分解的原子值。

       第二正規化：需要確保資料庫表中的每一列都和主鍵相關，而不能只與主鍵的某一部分相關（主要針對聯合主鍵而言）

       第三正規化：需要確保資料表中的每一列資料都和主鍵直接相關，而不能間接相關

如果你依然覺得有些茫然，不如加入我的Java架構師之路：766529531 跟有多年Java開發經驗的資深工程師聊一聊。也可獲取免費的影片學習資料以及電子書學習資料喔！

檢視概述

      檢視可以視為“虛擬表”或“儲存的查詢”

      建立檢視所依據的表稱為“基表”

      檢視的優點：

      提供了另外一種級別的表安全性:隱藏了一些關鍵的欄位

      簡化的使用者的SQL命令

      隔離基表結構的改變

儲存過程概述

儲存過程（Stored Procedure）

　　可以包含邏輯判斷的sql語句集合。

　　是經過預編譯，存在於資料庫中。

　　透過呼叫指定儲存過程的名字（可有參，可無參）來執行。

優點：

　　簡化了複雜的業務邏輯，根據需要可重複使用

　　遮蔽了底層細節，不暴露表資訊即可完成操作

　　降低網路的通訊量，多條語句可以封裝成一個儲存過程來執行

　　設定訪問許可權來提高安全性

　　提高執行效率，因為它是預編譯以及儲存在資料庫中

缺點：

　　可移植性差，相同的儲存過程並不能跨多個資料庫進行操作

　　大量使用儲存過程後，首先會使伺服器壓力增大，而且維護難度逐漸增加

儲存過程的語法：

--下面是在oracle資料庫下最基本的語法

--僅建立一個名為testProcedure 的無參的儲存過程

--IS也可以是AS

--如果已經存在名為 testProcedure 的儲存過程，下面的語法會出現 名稱已被使用的錯誤

--解決辦法：

--第一句可以寫成 create or replace procedure testProcedure

--這樣會替換原有的儲存過程

--NULL表示任何可以正確執行的sql 語句，但至少一句

create procedure testProcedure

IS

BEGIN

NULL

END;

儲存過程的引數的分類:

IN

OUT

INOUT

注意：

　　儲存過程之間可相互呼叫

　　儲存過程一般修改後，立即生效。

索引概述

1、索引的概念

索引就是為了提高資料的檢索速度。

資料庫的索引類似於書籍的索引。

在書籍中，索引允許使用者不必翻閱完整個書就能迅速地找到所需要的資訊。

在資料庫中，索引也允許資料庫程式迅速地找到表中的資料，

而不必掃描整個資料庫.

2、索引的優點

　　1.建立唯一性索引，保證資料庫表中每一行資料的唯一性

　　2.大大加快資料的檢索速度，這也是建立索引的最主要的原因

　　3.減少磁碟IO（向字典一樣可以直接定位）

3、索引的缺點

   1.建立索引和維護索引要耗費時間，這種時間隨著資料量的增加而增加

  2.索引需要佔用額外的物理空間

   3.當對錶中的資料進行增加、刪除和修改的時候，

索引也要動態的維護，降低了資料的維護速度

4、索引的分類

　　1.普通索引和唯一性索引

　　普通索引：CREATE  INDEX mycolumn_index ON mytable (myclumn)

　　唯一性索引：保證在索引列中的全部資料是唯一的

　　CREATE unique INDEX mycolumn_index ON mytable (myclumn)

2.單個索引和複合索引

　　單個索引：對單個欄位建立索引

　　複合索引：又叫組合索引，在索引建立語句中同時包含多個欄位名，

　　最多16個欄位

　　CREATE INDEX name_index ON userInfo(firstname,lastname)

3.順序索引，雜湊索引,點陣圖索引

必背的sql語句

    1：oracle分頁

         select * from (select t.*, rownum rn from (select * from menu order by id desc) t where rownum < 10) where rn >=5

  2: mysql分頁

         select * from music where id limit 5,5

  3：oracle中如何快速將一張表的資料複製到另外一張表中(另外一張表不存在，另外一張       表存在，但資料為空)

[if !supportLists]1、[endif].不存在另一張表時：

create  table新表  as  select * from 將要複製的表

[if !supportLists]2、[endif]存在另一張表時：

insert  into新表名  select  欄位  from  將要複製的表名

  4：音樂專輯

   查詢出special 表中的id  專輯名 並下面有多少首歌曲

      Select  s.id , min(s.sname),count(m.mid)  from  special s  inner

      join  ms  m  on  s.id=m.id  group  by  s.id

  5：快速刪除一張表（不可事物回滾，也就是沒有日誌記錄）

TRUNCATE from表名

  6：inner join

select查詢資訊 from 表名 1 inner join 表名2 on 表名1.列名 = 表名2.列名

  7：left join

左外連線select查詢資訊 from 表名1 left join 表名2 on 表名1.列名 = 表名2.列名

  8：right join

右外連線select查詢資訊 from 表名1 right join 表名2 on 表名1.列名 = 表名2.列名

  9：oracle中查詢遍歷樹形結構（start  with）

            select * from extmenu

            start with pid=1

            connect by prior id = pid

            快速刪除父節點以及父節點下的所有節點：

            Delete from extmenu where id in (

            elect * from extmenu

            start with pid=1

            connect by prior id = pid

            )

  10：查詢出來60-70,80-90,95-100學生的資訊

select * from stu where chengji between 60 and 70 or between 80 and 90 or between 95 and 100

select * from stu where chengji > 60 and chengji < 70 or chengji > 80 and chengji < 90 or chengji > 95 and chengji < 100

11：用exists替換in------進行聯表查詢

select * from dept where exists(select * from emp where emp.deptno=dept.deptno);或select  *  from  dept  d  inner  join  emp  e  on  d.deptno = e.deptno(只查詢出兩表共同擁有的欄位資料)

如果你依然覺得有些茫然，不如加入我的Java架構師之路：766529531 跟有多年Java開發經驗的資深工程師聊一聊。也可獲取免費的影片學習資料以及電子書學習資料喔！

12：刪除表中的重複資料：

delete from xin a where a.rowid != (

select max(b.rowid) from xin b

where a.name = b.name

);

13：row_number()，rank() over ，dense_rank() over 按工資排序

  select sal,

                 row_number() over(order by sal desc) rank1,

                 rank() over(order by sal desc) rank,

                 dense_rank() over(order by sal desc) drank

            from emp

14：select * from (select emp.* from(

dense_rank() over(partition by departNo order by sal desc)

rk from emp )

Where rk=4

ibatis批次

this.getSqlMapClientTemplate().execute(

new SqlMapClientCallback() {

        public Object doInSqlMapClient(

                SqlMapExecutor executor)

                throws SQLException {

        executor.startBatch();

        for (int i = 0, n = list.size(); i < n; i++) {

                executor.insert(

                "productAttach.insertProductAttach",

                list.get(i));

        }

        executor.executeBatch();

        return null;

    }

});

ibatis,jdbc,hibernate的分段的實現:

都應該在組裝list的時候進行拆分（如：action層加入）

if(list.size() % 1000 == 0)

{

            productAttachService.addBatch(list);

            list.clear();

}

if (list.size() > 0)

productAttachService.addBatch(list);