(在上一篇文章中，我們詳細的介紹了連接數據庫的方法，以及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 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 }
新聞熱點
疑難解答
