JDBC連接數據庫方法的封裝，以及查詢數據方法的封裝

2019-11-14 15:30:11

字體：大中小

來源：轉載

供稿：網友

(在上一篇文章中，我們詳細的介紹了連接數據庫的方法，以及eclipse操作數據庫信息的相關方法，在這里我們將主要講封裝。)

主要內容：

一般的連接數據庫測試
把連接數據庫的方法封裝成一個類和測試
一個簡單的插入表實例
查詢數據實例
封裝查詢的數據庫的信息
封裝信息后的查詢數據庫

一.一般的數據庫連接測試

 1 public class TestConnection1 { 2     public static void main(String[] args) throws Exception { 3        Class.forName("com.MySQL.jdbc.Driver"); 4        String url="jdbc:mysql://localhost:3306/test?"//數據庫url 5                + "useUnicode=true&characterEncoding=UTF8";//防止亂碼 6        String user="h4"; 7        String pass="111"; 8        Connection conn=DriverManager.getConnection(url, user, pass); 9        10        System.out.);11        conn.close();12     }13 }

二.我們不可能每寫一個處理信息功能就寫一次連接，這樣太麻煩，那么為了方便以后的應用，我們通常把數據庫連接封裝起來。

具體實現步驟如下：

1.定義變量：

private static String DRIVER_CLASS;
private static String URL;
private static String USERRNAME;
private static String PASS Word;

2.在你建的eclipse根目錄下新建一個File文件Properties；

文件內容為你定義的變量所指向的對象：

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test? useUnicode=true&characterEncoding=UTF8
user=h4
pass=111

3.構建一個Properties對象：Properties p=new Properties();

4. java.io下的類FileInputStream的方法；FileInputStream(String name) ：通過打開一個到實際文件的連接來創建一個 FileInputStream，該文件通過文件系統中的路徑名 name 指定。
來獲取這個文件里面的資料：FileInputStream fis=new FileInputStream("db.properties");

5. 用3構建的變量p來下載資料：p.load(fis);

6.利用getProperty();獲取參數：

DRIVER_CLASS=p.getProperty("driver");
URL=p.getProperty("url");
USERRNAME=p.getProperty("user");
PASSWORD=p.getProperty("pass");

7.寫一個連接數據庫的方法getConection()；

8.寫一個關閉數據庫的方法close(Connection conn)；

寫好后代碼如下：

 1 public class jdbcutil { 2     private static String DRIVER_CLASS; 3     private static String URL; 4     private static String USERRNAME; 5     private static String PASSWORD; 6     private static Properties p=new Properties(); 7     static{ 8         try { 9             FileInputStream fis=new FileInputStream("db.properties");10             p.load(fis);11             DRIVER_CLASS=p.getProperty("driver");12             URL=p.getProperty("url");13             USERRNAME=p.getProperty("user");14             PASSWORD=p.getProperty("pass");15             Class.forName(DRIVER_CLASS);16             fis.close();17         } catch (IOException e) {18             e.printStackTrace();19         } catch (ClassNotFoundException e) {20             e.printStackTrace();21         }22     }23     public static Connection getConection(){24         Connection conn=null;25         try{26         conn=DriverManager.getConnection(URL, USERRNAME, PASSWORD);27         }28         catch (Exception e) {29                 e.printStackTrace();30             }31          return conn;32        }33     public static void close(Connection conn) {34           try {35               if (conn != null)36                    conn.close();37              } catch (Exception e) {38                e.printStackTrace();39              }40          }41     42       }

那么封裝好之后，我們來寫一個測試類，測試連接

1 public class TestConnection2 {2 3     public static void main(String[] args) throws Exception {4        Connection conn=jdbcutil.getConection();//利用封裝好的類名來調用連接方法便可5        System.out.println(conn+",成功連接數據庫");6        jdbcutil.close( conn);//同樣利用類名調用關閉方法即可7     }8 }

三.連接成功，我們寫一個簡單的向數據庫插入表的實例。

 1 public class TestDDl { 2  3     public static void main(String[] args) { 4         Connection conn=null; 5         Statement stmt=null; 6         conn=jdbcutil.getConection();//連接數據庫 7         String createTableSql= " create table user_test1( "+//記住引號和單詞間一定要有空格 8                                " id int, "+ 9                                " name varchar(32) , "+10                                " password varchar(32) , "+11                                " birthday date "+12                                " ) ";  13         try {14             stmt=conn.createStatement();15             stmt.execute(createTableSql);16         } catch (SQLException e) {17             e.printStackTrace();18         }19         jdbcutil.close(null, stmt, conn);//關閉數據庫20     }21 }

四.我們在寫一個查詢數據庫數據的實例。(有三種方法)

 1 public class TestDQL { 2    public static void main(String[] args){ 3        Connection conn=null;//定義為空值 4        Statement stmt=null; 5        ResultSet rs=null; 6        String sql="select * from employees";//sql語句 7        conn=jdbcutil.getConection(); 8        try { 9         stmt=conn.createStatement();//創建一個Statement語句對象10         rs=stmt.executeQuery(sql);//執行sql語句11         while(rs.next()){12             System.out.print(rs.getInt(1)+",");13             System.out.print(rs.getString(2)+",");//直接使用參數14             System.out.print(rs.getString(3)+",");15             System.out.print(rs.getString(4)+",");16             System.out.println(rs.getString(5));17             }18     } catch (SQLException e) {19         e.printStackTrace();20     }finally{21         jdbcutil.close(rs,stmt,conn);//關閉數據庫22     }23   }24 }
    //第二種方法如下：

 1 public class TestDQl2 { 2  3     public static void main(String[] args) { 4            Connection conn=null; 5            Statement stmt=null; 6            ResultSet rs=null; 7            String sql="select * from employees"; 8            conn=jdbcutil.getConection(); 9            try {10             stmt=conn.createStatement();11             rs=stmt.executeQuery(sql);12             while(rs.next()){13                 System.out.print(rs.getInt("userid")+",");//里面直接寫要查找的內容名稱14                 System.out.print(rs.getString("employee_id")+",");15                 System.out.print(rs.getString("last_name")+",");16                 System.out.print(rs.getString("salary")+",");17                 System.out.println(rs.getString("department_id"));18                 }19         } catch (SQLException e) {20             e.printStackTrace();21         }finally{22             jdbcutil.close(rs,stmt,conn);23         }24     }25 }

 1 //第三種方法如下： 2 public class TestDQL3 { 3        public static void main(String[] args) { 4            Connection conn=null; 5            Statement stmt=null; 6            ResultSet rs=null; 7            String sql="select * from employees"; 8            conn=jdbcutil.getConection(); 9           try {10             stmt=conn.createStatement();11             rs=stmt.executeQuery(sql);12             while(rs.next()){13                 int index=1;14                 System.out.print(rs.getInt(index++)+",");15                 System.out.print(rs.getString(index++)+",");16                 System.out.print(rs.getString(index++)+",");17                 System.out.print(rs.getString(index++)+",");18                 System.out.println(rs.getString(index++));19                 }20         } catch (SQLException e) {21             e.printStackTrace();22         }finally{23             jdbcutil.close(rs,stmt,conn);24         }25      }26   }

五.在四里面我們寫了查詢員工資料的信息，但是有的時候我們要保存起來方便之后更好的查找，那怎么辦呢？沒錯，封裝。

 1 public class employees implements Serializable { 2         private Integer userid; 3         private String employee_id; 4         private String last_name; 5         private String salary; 6         private String department_id; 7          8         public employees() { 9             super();10         }11 12         public employees(String employee_id, String last_name, String salary, String department_id) {13             super();14             this.employee_id = employee_id;15             this.last_name = last_name;16             this.salary = salary;17             this.department_id = department_id;18         }19 20       @Override21         public String toString() {22             return "employees [userid=" + userid + ", employee_id=" + employee_id + ", last_name=" + last_name23                     + ", salary=" + salary + ", department_id=" + department_id + "]";24         }25 26        public Integer getUserid() {27             return userid;28         }29 30        public void setUserid(Integer userid) {31             this.userid = userid;32         }33 34        public String getEmployee_id() {35             return employee_id;36         }37 38        public void setEmployee_id(String employee_id) {39             this.employee_id = employee_id;40         }41 42        public String getLast_name() {43             return last_name;44         }45 46       public void setLast_name(String last_name) {47             this.last_name = last_name;48         }49 50       public String getSalary() {51             return salary;52         }53 54       public void setSalary(String salary) {55             this.salary = salary;56         }57 58       public String getDepartment_id() {59             return department_id;60         }61 62       public void setDepartment_id(String department_id) {63             this.department_id = department_id;64         }65    }

六.封裝好后的查詢和上面沒封裝之前有點變化。

 1 public class TestDQL4 { 2     public static void main(String[] args) { 3        Connection conn=null; 4        Statement stmt=null; 5        ResultSet rs=null; 6        List<employees> emps=new ArrayList<>();//構造集合對象 7         8        String sql="select * from employees"; 9        10        conn=jdbcutil.getConection();//獲取數據庫連接11        12        try {13         stmt=conn.createStatement();14         rs=stmt.executeQuery(sql);15         while(rs.next()){//遍歷結果集16             int index=1;17             employees emp=new employees();//構造員工類對象18             emp.setUserid(rs.getInt(index++));//獲取值19             emp.setEmployee_id(rs.getString(index++));20             emp.setLast_name(rs.getString(index++));21             emp.setSalary(rs.getString(index++));22             emp.setDepartment_id(rs.getString(index++));23             emps.add(emp);//放到集合中去24             }25     } catch (SQLException e) {26         e.printStackTrace();27     }finally{28         jdbcutil.close(rs,stmt,conn);//關閉連接29     }30        for(employees emp:emps){//遍歷31            System.out.println(emp);32        }33     }       34 }

其實我們可以繼續封裝，把遍歷結果集給封裝起來。

 1 public class TestDQL5 { 2  3     public static void main(String[] args) { 4            Connection conn=null; 5            Statement stmt=null; 6            ResultSet rs=null; 7            List<employees> emps=new ArrayList<>(); 8             9            String sql="select * from employees";10            11            conn=jdbcutil.getConection();12            13            try {14             stmt=conn.createStatement();15             rs=stmt.executeQuery(sql);16             emps=resultSetToEmployees(rs);17         } catch (SQLException e) {18             e.printStackTrace();19         }finally{20             jdbcutil.close(rs,stmt,conn);21         }22            for(employees emp:emps){23                System.out.println(emp);24            }25         }26          public static List<employees> resultSetToEmployees(ResultSet rs){27              List<employees> emps=new ArrayList<>();28                 try {29                     while(rs.next()){30                         int index=1;31                         employees emp=new employees();32                         emp.setUserid(rs.getInt(index++));33                         emp.setEmployee_id(rs.getString(index++));34                         emp.setLast_name(rs.getString(index++));35                         emp.setSalary(rs.getString(index++));36                         emp.setDepartment_id(rs.getString(index++));37                         emps.add(emp);38                         }39                 } catch (SQLException e) {40                     e.printStackTrace();41                 }42              43              return emps;44          }45     }

如果是一個人查詢信息呢？還可以這樣封裝。

 1 public class TestDQL6 { 2      public static void main(String[] args) { 3            Connection conn=null; 4            Statement stmt=null; 5            ResultSet rs=null; 6            List<employees> emps=new ArrayList<>(); 7             8            String sql="select * from employees"; 9            10            conn=jdbcutil.getConection();11            12            try {13             stmt=conn.createStatement();14             rs=stmt.executeQuery(sql);15            while(rs.next()){16                employees emp=resultSetToEmployee(rs);17                emps.add(emp);18              }19         } catch (SQLException e) {20             e.printStackTrace();21         }finally{22             jdbcutil.close(rs,stmt,conn);23         }24            for(employees emp:emps){25                System.out.println(emp);26            }27         }28          public static employees resultSetToEmployee(ResultSet rs){29             employees emp=null;30                 try {31                         int index=1;32                         emp=new employees();33                         emp.setUserid(rs.getInt(index++));34                         emp.setEmployee_id(rs.getString(index++));35                         emp.setLast_name(rs.getString(index++));36                         emp.setSalary(rs.getString(index++));37                         emp.setDepartment_id(rs.getString(index++));38                 } catch (SQLException e) {39                     e.printStackTrace();40                 }41                  return emp;42          }43     }