Encapsulation of JDBC Connection database methods and encapsulation of query data methods (Elite)

Main content:

• General Connection Database Testing
• Encapsulates the method of connecting a database into a class and test
• A simple example of inserting a table
• Querying data instances
• Encapsulates information for a queried database
• Query database after encapsulating information

I. GENERAL DATABASE Connectivity Testing

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?" Database URL 5                + "Useunicode=true&characterencoding=utf8";//Prevent garbled 6        string user= "H4"; 7        string pass= "111" ; 8        Connection conn=drivermanager.getconnection (URL, user, pass), 9        System.out.println (conn+ ", Successfully connected to the database ");        conn.close ();     }13}

Two. It is not possible for us to write a connection every time we write a processing information function, which is too cumbersome, so we usually encapsulate the database connection in order to facilitate future applications.

The specific implementation steps are as follows:

1. Define variables:

private static String Driver_class;
private static String URL;
private static String Userrname;
private static String PASSWORD;

2. Create a new file in the Eclipse root directory you built.

The contents of the file refer to the object to which you defined the variable:

Driver=com.mysql.jdbc.driver
Url=jdbc:mysql://localhost:3306/test? Useunicode=true&characterencoding=utf8
User=h4
pass=111

3. Build a Properties object: Properties P=new properties ();

4. Method of class FileInputStream under Java.io; FileInputStream (String name) : Creates one by opening a connection to the actual file FileInputStream , which is specified by a pathname in the file system name .
To get the information in this file: FileInputStream fis=new fileinputstream ("db.properties");

5. Download the data using the variable p of 3 build: P.load (FIS);

6. Using GetProperty (); Get parameters:

Driver_class=p.getproperty ("DRIVER");
Url=p.getproperty ("url");
Userrname=p.getproperty ("user");
Password=p.getproperty ("Pass");

7. Write a method to connect to the database Getconection ();

8. Write a method for closing the database close (Connection conn);

Write the following code as follows:

 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"); p.load (FIS); Driver_class=p.getproperty ("DRIVER"); Url=p.getproperty ("URL"); userrname             =p.getproperty ("user"); Password=p.getproperty ("Pass"); Class.forName (driver_class); 16 Fis.close (); \ n} catch (IOException e) {e.printstacktrace ();) catch (Classnotfounde         Xception e) {e.printstacktrace ()}22}23 public static Connection getconection () {24 Connection conn=null;25 try{26 conn=drivermanager.getconnection (URL, Userrname, PASSWORD); 27}   Exception (e) {29              E.printstacktrace ();}31 return conn;32}33 public static void Close (Conne Ction conn) {CATC try {conn! = null) conn.close (); H (Exception e) {e.printstacktrace (); 39}40}41 42}

Well, then we'll write a test class, test the connection

1 public class TestConnection2 {2 3 public     static void Main (string[] args) throws Exception {4        Connection conn=jdb Cutil.getconection ();//using the encapsulated class name to invoke the connection method can be 5        System.out.println (conn+ ", successfully connected to the database"); 6        Jdbcutil.close (conn); /also call the Close method with the class name 7     }8}

Three. The connection succeeds, we write a simple instance of inserting a table into the database.

1 public class Testddl {2  3 public     static void Main (string[] args) {4         Connection conn=null; 5         Statement Stmt=null; 6         conn=jdbcutil.getconection ();//Connect Database 7         String createtablesql= "CREATE Table User_test1 (" +//remember that there must be spaces between quotes and words 8                                "id int," + 9                                "name varchar (+)," +10                                "password varchar (+)," +11                                "Birthday Date" +12                                ")"; C11/>13         try {             stmt=conn.createstatement ();             Stmt.execute (createtablesql);         SQLException e) {             e.printstacktrace ()         }19         jdbcutil.close (null, STMT, conn);//Close database     }21}

Four. We are writing an instance of querying database data. (There are three ways)

1 public class Testdql {2 public    static void Main (string[] args) {3        Connection conn=null;//defined as null value 4        Statement Stmt=null; 5        ResultSet rs=null; 6        String sql= "SELECT * FROM Employees";//sql Statement 7        conn=jdbcutil.getconection (); 8        try {9         stmt=conn.createstatement ();//Create a statement statement object of         rs=stmt.executequery (SQL);//Execute SQL statement         one at a while (Rs.next ()) {             System.out.print (Rs.getint (1) + ","),             System.out.print (rs.getstring (2) + ",");// Direct use of Parameters             System.out.print (rs.getstring (3) + ","),             System.out.print (rs.getstring (4) + ",");             System.out.println (Rs.getstring (5));             }18     } catch (SQLException e) {         e.printstacktrace (); 20     }finally{21         jdbcutil.close (rs,stmt,conn);//Close Database (     }23   }24})
    The second method is as follows:

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< C8/>stmt=conn.createstatement ();             rs=stmt.executequery (SQL), while             (Rs.next ()) {                 System.out.print (Rs.getint ("userid") + ",");//directly write the name of the content you want to find in the                 System.out.print (rs.getstring ("employee_id") + ",");                 System.out.print (Rs.getstring ("last_name") + ",");                 System.out.print (Rs.getstring ("salary") + " , ");                 System.out.println (Rs.getstring (" department_id "));                 }19         } catch (SQLException e) {             E.printstacktrace ();         }finally{22             jdbcutil.close (rs,stmt,conn);}24}25     }

 1//The third method is as follows: 2 public class TestDQL3 {3 public static void main (string[] args) {4 Connection con N=null; 5 Statement Stmt=null; 6 ResultSet Rs=null; 7 String sql= "SELECT * FROM Employees"; 8 Conn=jdbcutil.getconection (); 9 try {ten stmt=conn.createstatement (); rs=stmt.executequery (SQL); Rs.next ()) {int index=1;14 System.out.print (rs.getint (index++) + ",");                 Ystem.out.print (rs.getstring (index++) + ","); System.out.print (rs.getstring (index++) + ","); 17                 System.out.print (rs.getstring (index++) + ","); System.out.println (Rs.getstring (index++)); 19 }20} catch (SQLException e) {e.printstacktrace ();}finally{23 Jdbcutil. Close (rs,stmt,conn);}25}26} 

Five. In four we have written a query staff information, but sometimes we have to save it for better after the search, then how to do? Yes, encapsulation.

 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 (),}11 Public Employees (String employee _id, String last_name, String salary, String department_id) {very super (); this.employee_id = Empl oyee_id;15 This.last_name = last_name;16 This.salary = salary;17 this.department_id = department_id;18}19 @Override21 public String toString () {return "Employees [user  Id= "+ userid +", employee_id= "+ employee_id +", last_name= "+ Last_name23 +", salary= "+ salary + ", department_id=" + department_id + "]",}25-public Integer GetUserid () {return useri D;28}29 VOID Setuserid (Integer userid) {This.userid = userid;32}33 public String getemployee_id ()             {employee_id;36}37-public void setemployee_id (String employee_id) {39          this.employee_id = employee_id;40}41 public String getlast_name () {last_name;44  }45-public void Setlast_name (String last_name) {this.last_name = last_name;48}49 public string Getsalary () {salary;52.}53, public void Setsalary (string sal ary) {this.salary = salary;56}57 +-Public String getdepartment_id () {return department_id;60}61-public void setdepartment_id (String department_id) {this.department_i d = department_id;64}65}

Six. The package after the query and the above is not encapsulated before a little change.

 1 public class TestDQL4 {2 public static void Main (string[] args) {3 Connection conn=null; 4 statemen T Stmt=null; 5 ResultSet Rs=null; 6 list<employees> emps=new arraylist<> ();//Construct Collection Object 7 8 String sql= "SELECT * FROM Employee S "; 9 conn=jdbcutil.getconection ();//Get database connection one by one try {stmt=conn.createstatement (); 1  4 rs=stmt.executequery (SQL); (Rs.next ()) {//traversal result set: int index=1;17 Employees Emp=new employees ();//Construct Employee Class object Emp.setuserid (Rs.getint (index++));//Get Value emp.setemployee_id (RS.G Etstring (index++)), Emp.setlast_name (rs.getstring (index++)), Emp.setsalary (Rs.getstring (index+     +)); emp.setdepartment_id (Rs.getstring (index++)); Emps.add (EMP);//Put in the collection 24}25 } catch (SQLException e) {e.printstacktrace ();}finally{28 Jdbcutil.close (RS,stmt,conn)///close connection}30 for (employees emp:emps) {//Traverse System.out.println (EMP); 32}33} 34}

In fact, we can continue to encapsulate, to encapsulate the traversal result set.

 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"; Conn=jdbcutil.getconection (); 1             2 try {stmt=conn.createstatement (); rs=stmt.executequery (SQL); 16         Emps=resultsettoemployees (RS), +} catch (SQLException e) {e.printstacktrace (); 19 }finally{20 Jdbcutil.close (rs,stmt,conn);}22 for (employees emp:emps) {S YSTEM.OUT.PRINTLN (EMP),}25}26 public static list<employees> resultsettoemployees (Res                     Ultset rs) {list<employees> emps=new arraylist<> (); try {29   while (Rs.next ()) {30                      int INDEX=1;31 Employees Emp=new Employees (), EMP.                         Setuserid (Rs.getint (index++)); emp.setemployee_id (Rs.getstring (index++)); 34                         Emp.setlast_name (rs.getstring (index++)); Emp.setsalary (Rs.getstring (index++)); 36                         EMP.SETDEPARTMENT_ID (rs.getstring (index++)); PNs Emps.add (EMP); 38              }39} catch (SQLException e) {e.printstacktrace (); 41}42 Return emps;44}45}

What if a person queries for information? This can also be encapsulated.

 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 conn=jdbcutil.getconection (); try {stmt=conn.createst Atement (); rs=stmt.executequery (sql); (Rs.next ()) {Employees Emp=resultse Ttoemployee (RS); Emps.add (EMP),}19} catch (SQLException e) {E.pri Ntstacktrace (),}finally{22 jdbcutil.close (rs,stmt,conn),}24 for (Employees EMP : Emps) {System.out.println (EMP);}27}28 public static employees Resultsett Oemployee (ResultSet rs) {employees emp=null;30 try {31                         int index=1;32 emp=new Employees (); Emp.setuser ID (rs.getint (index++)); emp.setemployee_id (Rs.getstring (index++)); EMP                         . Setlast_name (Rs.getstring (index++)); Emp.setsalary (Rs.getstring (index++)); 37 EMP.SETDEPARTMENT_ID (rs.getstring (index++)); catch (SQLException e) {E.PR      Intstacktrace ();}41 return emp;42}43

Encapsulation of JDBC Connection database methods, and encapsulation of query data methods (Elite)