你的位置:首页 > Java教程

[Java教程]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.定义变量:

private static String DRIVER_CLASS;
private static String URL;
private static String USERRNAME;
private static String PASSWORD;

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   }