JDBC连接数据库方法的封装，以及查询数据方法的封装

(在上一篇文章中，我们详细的介绍了连接数据库的方法，以及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.println(conn+",成功连接数据库"); 11        conn.close(); 12     } 13 }

二.我们不可能每写一个处理信息功能就写一次连接，这样太麻烦，那么为了方便以后的应用，我们通常把数据库连接封装起来。

具体实现步骤如下：

1.定义变量：

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

文件内容为你定义的变量所指向的对象：

3.构建一个Properties对象：Properties p=new Properties();

5. 用3构建的变量p来下载资料：p.load(fis);

6.利用getProperty();获取参数：

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       @Override 21         public String toString() { 22             return "employees [userid=" + userid + ", employee_id=" + employee_id + ", last_name=" + last_name 23                     + ", 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     }