Simple Universal JDBC Helper class Encapsulation (instance) _java

Hey, it's been a long time since I haven't written a thing, for the reason of work, contact the bottom ORM framework of the company's own development, and find that the framework refers to the simplejdbctemplate in hibernate when invoking JDBC operation. Here I think of a simple JDBC package I used when I was in college, and now I'm going to post the code and share it with you:

Config class: Read the database connection configuration file under the same package, for better commonality consideration

 package com.tly.dbutil;
Import java.io.IOException;

Import java.util.Properties;  
  public class Config {private static properties prop = new properties (); static{try {//Load dbconfig.properties configuration file Prop.load (Config.class.getResourceAsStream ("Dbconfig.propert
    ies "));
    catch (IOException e) {//TODO auto-generated catch block E.printstacktrace ();
  }//Set constant public static final String class_name = Prop.getproperty ("class_name");
  public static final String Database_url = Prop.getproperty ("Database_url");
  public static final String server_ip = Prop.getproperty ("server_ip");
  public static final String Server_port = Prop.getproperty ("Server_port");
  public static final String Database_sid = Prop.getproperty ("Database_sid");
  public static final String USERNAME = Prop.getproperty ("USERNAME");
  
public static final String PASSWORD = Prop.getproperty ("PASSWORD"); }

Dbconfig.properties: Database configuration file, you can also use XML format, etc., note Config class inside the file call location

Class_name=com.mysql.jdbc.driver
database_url=jdbc:mysql
server_ip=localhost
server_port=3306
Database_sid=employees
username=root
password=1

The next step is the database connection helper class Dbconn.

Package com.employees.dbutil;
Import java.sql.Connection;
Import Java.sql.DriverManager;
Import java.sql.PreparedStatement;
Import Java.sql.ResultSet;


Import java.sql.SQLException;
  public class Dbconn {//Three properties, four methods//three core interfaces private Connection conn = null;
  Private PreparedStatement pstmt = null;
  
  Private ResultSet rs = null; Four methods//METHOD1: Create a connection to the database public Connection getconntion () {try {//1: Load connection Drive, Java reflection principle Class.forna
      Me (Config.class_name); 2: Create the Connection interface object to get the connection object for the MySQL database. Three parameters: URL connection string account password string url = config.database_url+ "://" +config.server_ip+ ":" +config.server_port+ "/" +config.datab
      Ase_sid;
    conn = Drivermanager.getconnection (Url,config.username,config.password);
    catch (ClassNotFoundException e) {e.printstacktrace ();
    catch (SQLException e) {e.printstacktrace ();
  Return conn; //method2: Method to close the database public void Closeconn () {if (rs!=null) {try {Rs.close();
      catch (SQLException e) {e.printstacktrace ();
      } if (Pstmt!=null) {try {pstmt.close ();
      catch (SQLException e) {e.printstacktrace ();
      } if (Conn!=null) {try {conn.close ();
      catch (SQLException e) {e.printstacktrace (); //method3: Method public int Execother (PreparedStatement pstmt) {try {//1), which is dedicated to sending additions and deletions, using stateme
      NT object sends SQL statement int affectedrows = Pstmt.executeupdate ();
    2, return the result returns AffectedRows;
      catch (SQLException e) {e.printstacktrace ();
    return-1; }//METHOD4: Dedicated to sending query statements public ResultSet ExecQuery (PreparedStatement pstmt) {try {//1, sending with statement object
      SQL Statement rs = pstmt.executequery ();
    2, return the result returns RS;
      catch (SQLException e) {e.printstacktrace ();
    return null; }
  }

}

Usually use the above code to solve some simple crud application, but there are many limitations, such as each program to get the connection to the new, so that the system increased the burden, no business, no datasource, etc. Today I saw a friend in the garden wrote a reflection solution directly with the object parameters of the way crud, this I have written before, did not finish, mainly I want to write a general Dbutil, the final study to study, Found more and more and hibernate inside the simplejdbctemplate close, so directly to see the source of hibernate, plus that time some things, no time, will this thing idle up, now put this thing up, also give yourself a review of the next

Basedao class

Package Com.employees.dao;
Import Java.io.InputStream;
Import Java.lang.reflect.Method;
Import Java.lang.reflect.ParameterizedType;
Import java.sql.Connection;
Import Java.sql.Date;
Import java.sql.PreparedStatement;
Import Java.sql.ResultSet;
Import java.util.ArrayList;
Import Java.util.Iterator;

Import java.util.List;

Import Com.employees.dbutil.DBConn;
  public class Basedao<t> {Dbconn conn = new Dbconn ();
  
  Private Connection Connection = null;
  
  @SuppressWarnings ("unused") private class<t> Persistentclass;
    @SuppressWarnings ("unchecked") public Basedao () {initconnection ();
    Gets the parameterized type Parameterizedtype = (parameterizedtype) getclass (). Getgenericsuperclass ();
  Persistentclass = (class<t>) type.getactualtypearguments () [0];      
  /** * Obtain database connection/public void Initconnection () {connection = Conn.getconntion (); }/** * Save/public void Save (T entity) throws exception{//sql statement, insertinto table name (String sql = INSERT INTO + entity.getclass (). Getsimplename (). toLowerCase () + "(";
    
    Gets the object list<method> List = This.matchpojomethods (Entity, "get") for all methods with string got;
    
    Iterator<method> iter = List.iterator ();
      concatenation field order insert into table name (Id,name,email, while (Iter.hasnext ()) {Method method = Iter.next ();
    SQL + + method.getname (). substring (3). toLowerCase () + ","; ///Remove the last one, symbol insert INSERT into table name (id,name,email) VALUES (sql = sql.substring (0, Sql.lastindexof (",")
    
    ) + ") VALUES (";
      Assemble precompiled SQL statements insert INSERT INTO table name (Id,name,email) VALUES (?,?,?, for (int j = 0; J < list.size (); + +) {
    SQL + = "?,";
    //Remove the last SQL statement, the symbol Insert INSERT into table name (Id,name,email) values (?,?,?);
    
    sql = sql.substring (0, Sql.lastindexof (",")) + ")";
    
    To this SQL statement concatenation completes, print SQL statement System.out.println (SQL); Get a reference to a precompiled object PreparedStatement statement = connection. preparestatement (SQL);
    int i = 0;
    Move the pointer to the last line of the iterator to the first row.
    iter = List.iterator ();
      while (Iter.hasnext ()) {Method method = Iter.next (); This initial judgment returns the type of the value, because some field value formats need to be changed when the database is stored, such as the String,sql statement is ' +abc+ ' if (Method.getreturntype (). Getsimplename (). IndexOf ("
      String ")!=-1) {statement.setstring (++i, This.getstring (method, entity)); else if (Method.getreturntype (). Getsimplename (). IndexOf ("Date")!=-1) {statement.setdate (++i, This.getdate (metho
      D, entity)); else if (Method.getreturntype (). Getsimplename (). IndexOf ("InputStream")!=-1) {Statement.setasciistream (++i, Thi
      S.getblob (method, entity), 1440);
      else {statement.setint (++i, This.getint (method, entity));
    }//execute Conn.execother (statement);
  Close connection conn.closeconn (); /** * Modify */public void update (T entity) throws exception{String sql = "Update" + Entity.getclass (
   ). Getsimplename (). toLowerCase () + "set"; 
    Gets the collection of all get method objects for the class list<method> List = This.matchpojomethods (Entity, "got");
    A temporary method object that is responsible for iterating the fashion method object.
    
    Method tempmethod = null;
    Because you do not need to modify the ID when you modify it, you should move the ID to the last by adding the parameter in order.
    Method idmethod = null;
    Iterator<method> iter = List.iterator ();
      while (Iter.hasnext ()) {Tempmethod = Iter.next ();
      If the method name has an ID string and the length is 2, it is considered an ID. if (Tempmethod.getname (). LastIndexOf ("Id")!=-1 && tempmethod.getname (). substring (3). Length () = 2) {//Put I
        The object in the D field is stored in a variable and then deleted in the collection.
        Idmethod = Tempmethod;
      Iter.remove (); If the method name is removed from the Set/get string and Pojo + ID wants to conform (case-insensitive), it is treated as an ID} else if ((Entity.getclass (). Getsimplename () + "id"). equalsignore
        Case (Tempmethod.getname (). SUBSTRING (3))) {Idmethod = Tempmethod;        
      Iter.remove ();
    }///Move the iteration pointer to the first ITER = List.iterator ();
      while (Iter.hasnext ()) {Tempmethod = Iter.next (); SQL + + tempmethod.getname (). substring (3). toLowerCase () + "= ?,";
    
    ///Remove the last one, symbol sql = sql.substring (0,sql.lastindexof (","));
    
    Add Conditional sql = "where" + idmethod.getname (). substring (3). toLowerCase () + "=?";
    
    SQL concatenation completes, print SQL statement System.out.println (SQL);
    
    PreparedStatement statement = this.connection.prepareStatement (SQL);
    int i = 0;
    iter = List.iterator ();
      while (Iter.hasnext ()) {Method method = Iter.next (); This initial judgment returns the type of the value, because some field value formats need to be changed when the database is stored, such as the String,sql statement is ' +abc+ ' if (Method.getreturntype (). Getsimplename (). IndexOf ("
      String ")!=-1) {statement.setstring (++i, This.getstring (method, entity)); else if (Method.getreturntype (). Getsimplename (). IndexOf ("Date")!=-1) {statement.setdate (++i, This.getdate (metho
      D, entity)); else if (Method.getreturntype (). Getsimplename (). IndexOf ("InputStream")!=-1) {Statement.setasciistream (++i, Thi
      S.getblob (method, entity), 1440); else {statement.setint ++i, This.getint (method,entity)); ///Add value to ID field if (Idmethod.getreturntype (). Getsimplename (). IndexOf ("String")!=-1) {Stateme
    Nt.setstring (++i, This.getstring (Idmethod, entity));
    else {statement.setint (++i, This.getint (Idmethod, entity));
        
        }//Execute SQL Statement statement.executeupdate ();
        
        Closes the precompiled object Statement.close ();
  Close connection connection.close (); /** * Delete */public void Delete (T entity) throws exception{String sql = "Delete from" + entity.getc
    
    Lass (). Getsimplename (). toLowerCase () + "where";
    
    The Field object containing the string "id" method idmethod = null;
    Gets the Field object with the string "id" list<method> List = this.matchpojomethods (Entity, "get");
    Iterator<method> iter = List.iterator ();
      while (Iter.hasnext ()) {Method Tempmethod = Iter.next ();
      If the method name has an ID string and the length is 2, it is considered an ID. if (Tempmethod.getname (). LastIndexOf ("Id")!=-1 && tempmethod.getname (). SUBSTRING (3). Length () = = 2) {//The object of the ID field is stored in a variable and then deleted in the collection.
        Idmethod = Tempmethod;
      Iter.remove (); If the method name is removed from the Set/get string and Pojo + ID wants to conform (case-insensitive), it is treated as an ID} else if ((Entity.getclass (). Getsimplename () + "id"). equalsignore
        Case (Tempmethod.getname (). SUBSTRING (3))) {Idmethod = Tempmethod;        
      Iter.remove ();
    
    } SQL + + Idmethod.getname (). substring (3). toLowerCase () + "=?";
    
    PreparedStatement statement = this.connection.prepareStatement (SQL);
    add value int i = 0 for ID field; if (Idmethod.getreturntype (). Getsimplename (). IndexOf ("String")!=-1) {statement.setstring (++i, this.getstring
    Thod, entity));
    else {statement.setint (++i, This.getint (Idmethod, entity));
    }//execute Conn.execother (statement);
  Close connection conn.closeconn (); /** * via ID query/public T FindByID (object) throws exception{String sql = ' select * from ' + P Ersistentclass.getsimplename (). tOlowercase () + "where";
    
    The specific type of the parameterized type is obtained by the constructor of the subclass. For example, basedao<t> is the specific type T entity = persistentclass.newinstance ();
    
    The method object that holds the primary key of the Pojo (or manipulated table) is Idmethod = null;
    list<method> list = This.matchpojomethods (entity, "set");
    
    Iterator<method> iter = List.iterator ();
      Filter gets the method object while (Iter.hasnext ()) {Method Tempmethod = Iter.next ();  if (Tempmethod.getname (). IndexOf ("Id")!=-1 && tempmethod.getname (). substring (3). Length () = 2) {Idmethod
      = Tempmethod; else if ((Entity.getclass (). Getsimplename () + "Id"). Equalsignorecase (Tempmethod.getname (). SUBSTRING (3))} {Idmetho
      D = Tempmethod; }///first letter to lowercase SQL + idmethod.getname (). substring (3,4). toLowerCase () +idmethod.getname (). SUBSTRING (4) + "=?
    
    ";
    
    Encapsulation statement finished, print SQL statement System.out.println (SQL);
    
    Get connection PreparedStatement statement = this.connection.prepareStatement (SQL); To determine the type of ID if (oBject instanceof Integer) {statement.setint (1, (Integer) object);
    else if (object instanceof string) {statement.setstring (1, (String) object);
    //Execute SQL to get the query result set.
    
    ResultSet rs = conn.execquery (statement);
        
    Registers, the record loops to the first few fields int i = 0;
    
    Point Pointer to iterator first line iter = List.iterator ();
        Package while (Rs.next ()) {while (Iter.hasnext ()) {Method method = Iter.next (); if (Method.getparametertypes () [0].getsimplename (). IndexOf ("String")!=-1) {//Because the method object takes out the order of methods in the list collection and the order of the database fields is not
          Consistent (for example: the first method of the list is setdate, and the database is in order "123" value)//So the database field is taken by the name corresponding to.
        This.setstring (method, Entity, Rs.getstring (Method.getname (). substring (3). toLowerCase ())); else if (method.getparametertypes () [0].getsimplename (). IndexOf ("Date")!=-1) {This.setdate (method, entity, Rs.
        GetDate (Method.getname (). substring (3). toLowerCase ()); else if (method.getparametertypes () [0].getsimplename (). IndexOf ("InputStream ")!=-1) {This.setblob (method, Entity, Rs.getblob (Method.getname (). substring (3). toLowerCase ()). Getb
        Inarystream ());
        else {This.setint (method, Entity, Rs.getint (Method.getname (). substring (3). toLowerCase ()));
        
    }}//Close result set rs.close ();
    
    Closes the precompiled object Statement.close ();
  return entity;
   /** * Filters all method objects with incoming strings in the current Pojo class, returning the list collection.  * * Private list<method> Matchpojomethods (T entity,string methodname) {//Get current Pojo All Method objects method[] methods =
    
    Entity.getclass (). Getdeclaredmethods ();
    
    The list container holds all method objects with get strings list<method> List = new arraylist<method> (); Filters all method objects with get strings for the current Pojo class, stored in the list container for (int index = 0; index < methods.length; index++) {if methods[index
      ].getname (). IndexOf (methodname)!=-1) {List.add (Methods[index]);
  } return list; /** * method returns the value of the SQL statement returned when the type is int or an integer. corresponding to GET/Public Integer GetInt (method method, T entity) throws exception{return (Integer) Method.invoke (entity, new O
  bject[]{}); /** * method returns the assembly value of the SQL statement returned when the type is String. For example, ' abc ', corresponding to get/public String getString (methods method, T entity) throws E
  xception{return (String) Method.invoke (Entity, new object[]{}); /** * method returns the assembly value of the SQL statement returned when the type is a blob. Corresponds to get/public InputStream GetBlob (methods method, T entity) throws Exceptio
  n{return (InputStream) Method.invoke (Entity, new object[]{});
    /** * method returns the SQL statement assembly value when the type is Date, corresponding to get/public Date getDate (methods, T entity) throws exception{
  Return (Date) Method.invoke (Entity, new object[]{}); /** * When the parameter type is Integer or int, set the parameter for the Entity field, corresponding to set/public Integer Setint (method, T entity, Integer ar
  g) throws exception{return (Integer) Method.invoke (Entity, new Object[]{arg}); /** * When the parameter type is string, set the parameter for the Entity field, corresponding to set/public String setstringT entity, string arg) throws exception{return (string) Method.invoke (Entity, new Object[]{arg}); /** * When the parameter type is InputStream, set the parameter for the Entity field, corresponding to set/public InputStream Setblob (method, T entity, INPUTSTR
  EAM Arg) throws exception{return (InputStream) Method.invoke (Entity, new Object[]{arg}); /** * When the parameter type is Date, set the parameter for the Entity field, corresponding to set/public Date setdate (method, T entity, Date Arg) throws Ex
  ception{return (Date) Method.invoke (Entity, new Object[]{arg}); }
}

Employeesdao inherits Basedao, you can use the method of the parent class directly, increase the reuse of the Code

Package Com.employees.dao;

Import java.util.ArrayList;
Import java.util.List;
Import Com.employees.po.Employees;

public class Employeesdao extends basedao<employees> {

  //Add employee Information Operations Public
  Boolean addemployees (final Employees Employees) throws Exception {
    save (Employees);
    return true;
  }

  Add employee information to the table public
  list<employees> addemployees (int id) throws Exception {
    list<employees> Lstemployees = new arraylist<employees> ();
    Employees Employees = FindByID (ID);
    Load the current marshaled data into the object
    lstemployees.add (employees);
    return lstemployees;
  }

  public void Deleteemp (final Employees entity) throws Exception {
    this.delete (entity);
  }

  public void Updateemp (final Employees entity) throws Exception {
    this.update (entity);
  }


}

PO Layer code is not posted, now use JUNIT4 to do a test

 package Com.employees.dao;

Import Org.junit.Test;

Import Com.employees.po.Employees;
    public class Employeesdaotest {@Test the public void Testadd () throws Exception {Employees emp = new Employees ();
    Emp.setpname ("tly");
    Emp.setpsex ("male");
    Emp.setpbeliefs ("xxxxx");
    EMP.SETPADDR ("Tianhe");
    Emp.setphobby ("playing basketball");
    Emp.setpsubject ("Computer");
    Emp.setptel ("123456");
    Employeesdao dao = new Employeesdao ();
  Dao.addemployees (EMP);
    @Test public void Testupdate () throws Exception {Employeesdao dao = new Employeesdao ();
    Employees emp = Dao.findbyid (14);
    Emp.setptel ("999999");
  Dao.updateemp (EMP);
    @Test public void Testdelete () throws Exception {Employeesdao dao = new Employeesdao ();
    Employees emp = Dao.findbyid (15);
  Dao.deleteemp (EMP); }

}

After testing, these three methods are working correctly, time is short, some code is to refer to other buddies, some places may consider is not very comprehensive or some code will have the redundancy, Basedao does not have the general crud operation to write the whole, if which small partner is interested, may follow down writes, for instance the inquiry, Batch operation and so on, if the test passed, remember to send me a ah, hehe

The above simple universal JDBC Helper class Encapsulation (example) is a small series to share all the content, hope to give you a reference, but also hope that we support the cloud habitat community.