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 @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 }
