The evolution of JDBC 2

OK, change a new screen, the original computer screen, the Spring festival time to wipe the glass, the glass fell to break the split (glass a little things are not), the new screen feeling is cool, and I spent 280 of the ocean bought the perfect screen LG. The fighting power immediately felt a surge of 20%. Gossip is not much to say, continue to evolve JDBC.
After the JDBC connection is complete, what are we going to do? Delete and change to search Bai!
Well, let's start with a primitive origin, and then we evolve gradually.

Evolution of the 1.JDBC 2-additions and deletions

After we get the connection object, we want to send our SQL statements to the database via this connection, and then let the database return the results to us.
By looking at the API, we discovered the method of Createstatement () in the connection interface, the specific function, "creates a Statement object for sending SQL statements to the Datab ASE. " Creates a statement object that can send a SQL command to the database, which returns a statement object. Well, you can send SQL statement to the database, we want the execution, and then return the results to us. Continue to view the API, statement interface in execute (String sql), ExecuteQuery (String sql), executeupdate (String sql) has so three method parameters are String SQL. All right, I'll take it. Let's try first. Execute (String sql)

 @Test  public  void   Testjdbcinsert  () throws  exception{Connection conn = jdbcutils.getconnecti        On ();        Statement STATM = Conn.createstatement (); String sql =  "INSERT into customers values (' yushen1 ', ' [email protected] ', ' 1998-3-2        ', null) ' ;        boolean  b = statm.execute (SQL);        System.out.println (b);        Statm.close ();    Conn.close (); }

I threw out the exception, which is not very reasonable, but it's not the point. The point is that the execution was successful (by looking at the database is actually plugged in), but the return value here is false, ah, unreasonable, obviously successful, how can it be false? Look at the API, and don't look at it, we look directly at its return value, "True if the first result is a ResultSet object; False if it is a update count or there is no results ", so to do, if the result is a ResultSet object it returns true if there is no result set or a count, which returns false. Contact the learned SQL, we know that the result of the select query is a data table, and other DML operations, only the table has been modified, resulting in a similar result "1 row (s) affected". And that's when the problem comes, how do we know that a result set is still a count, which is not conducive to subsequent operations. Evolve it!

Evolution of the 2.JDBC 2-additions and deletions Evolution of the 2.1JDBC 2-additions and deletions 1

The original problem we already understand, our solution is to modify (edit) operation and query operations separate, see the above three methods, seconds understand, is not. First of all, the simple, modified, because its return value is an affected number of rows, we can determine whether the number of rows is 0, will be able to judge whether the success of the execution.

//Update    @Test     Public void testjdbcupdate() {Connection conn =NULL; Statement STATM =NULL;Try{conn = jdbcutils.getconnection ();            STATM = Conn.createstatement (); String sql ="UPDATE customers set name = ' Woshiyushen ' WHERE id =";introws = statm.executeupdate (SQL);if(Rows >0) {System.out.println ("success!"); }Else{System.out.println ("Failed"); }        }Catch(Exception e)        {E.printstacktrace (); }finally{Jdbcutils.close (STATM, conn); }    }

For the moment we will evolve here and follow it. Wrap it into a method and put it into my tool class.

//Statement:insert, Update,delete  public  static  int  update  (String sql) {Connection conn = null ;        Statement STATM = null ;        int  rows = 0 ; try             {conn = getconnection ();            STATM = Conn.createstatement ();        rows = statm.executeupdate (SQL);        } catch  (Exception e) {e.printstacktrace ();        } finally  {jdbcutils.close (STATM, conn);    } return  rows; }

Evolution of the 3.JDBC 2-check (emphasis) Evolution of the 3.JDBC 2-1

First, check one.

@Test     Public void TestOrderSelect1() {//Get connectionConnection conn =NULL;//Create Statement ObjectStatement STATM =NULL; ResultSet rs =NULL;Try{conn = jdbcutils.getconnection (); STATM = Conn.createstatement ();//ExecuteQuery, and return a ResultSet objectString sql ="Select order_id, Order_name, order_date from ' order '"; rs = statm.executequery (SQL);//Read the RS             while(Rs.next ()) {intOrderId = Rs.getint ("order_id"); String ordername = rs.getstring ("Order_name"); Date orderDate = Rs.getdate ("Order_date");//Put the ResultSet to a classOrder order =NewOrder (OrderId, Ordername, orderDate);            SYSTEM.OUT.PRINTLN (order); }        }Catch(Exception e)        {E.printstacktrace (); }finally{//Close ConnJdbcutils.close (RS, STATM, conn); }    }

My own level of English is not very high, there may be a lot of grammatical problems, we will see, I would continue to learn to improve. Here, the processing of the result set is also written, and here comes a thought:ORM (Object Relational Mapping): Objects relational mapping, in my own words to understand that a table corresponds to a class, a column corresponding to a property, A row corresponds to a data, think about it, is this, maybe we initially want to put the read data into the array, the collection, but with an object to save is not a little better? In Resultset,api's words, "a table of data representing a database result set" contains a data table of a DB result set, which can be thought of as a table that provides a cursor through next ( ) method, move the cursor down, and the cursor indicates a row. We then take the data out of this line and save it to the object.
Then again, here we can only target a specific table, because you are not sure of the number of columns in it, the name of the column. In order to provide a common, highly portable program, evolve it!

Evolution of the 3.JDBC 2-2

We want to provide a common solution, like the evolution of 2.1JDBC 2-Add and subtract 1 we encapsulate it as a method, and we can get the object with just a few parameters. Want to get this object, and we do not know which object, only at run time to determine the specific object to get ... Reflection! Reflection!!! That's right! Let's write this generic query method.

 Public<T> TGet(String sql, class<t> clazz) {T T =NULL;//1.get the connectionConnection conn =NULL;//2.on The base of conn, create Statement objectStatement STATM =NULL;//3.excute SQL command, and get the ResultSet objectResultSet rs =NULL;Try{conn = jdbcutils.getconnection ();            STATM = Conn.createstatement (); rs = statm.executequery (SQL);//Get the ResultSetMetaData objectResultSetMetaData RSMD = Rs.getmetadata ();//4.read The data from Result object, and write to a generic object             while(Rs.next ()) {//4.1through reflect get the T object T ...t = clazz.newinstance ();//4.2 Read the RS ' data to T                //4.21 get the ColumnCount througth the ResultSetMetaData                intColumnCount = Rsmd.getcolumncount ();//4.22 get the Every columnName and Columnval                 for(inti =1; I <= ColumnCount; i++) {//4.23 get the ColumnNameString columnName = Rsmd.getcolumnlabel (i);//4.24 get the Columnval from RsObject cloumnval = Rs.getobject (columnName);//4.25 Set the ColumnName and Columnval to T througth                    //GenericField field = Clazz.getdeclaredfield (ColumnName); Field.setaccessible (true);                Field.set (t, Cloumnval); }            }        }Catch(Exception e)        {E.printstacktrace (); }finally{//5.close the connectionJdbcutils.close (RS, STATM, conn); }//6. Return the T        returnT }

Here need two parameters, SQL does not have to mention, to say is the second parameter Class<T> clazz we need to use reflection to obtain the concrete class instance, here we need to build the corresponding entity class table According to the result of the query, and then through reflection, to the specific value for a property.
The number of columns needed to be obtained by ResultSetMetaData.

When you get here, there's a level in your mind:

Class that describes the class (the class acquired by reflection)
Data that describes the data (metadata, such as ResultSetMetaData)
Mind divergence: There is also a note describing the annotations (meta-annotations), of course, it is not used here ...

Down one level:

The specific class
The specific object
Specific annotations

This evolves a little bit.

Evolution of the 3.JDBC 2-3

Now that we say that we can set a value for it by property, we can also assign a value to the property with GetXXX () and setxxx (). and Apache provided us with such a library (Commons-beanutils-1.8.0.jar,commons-logging-1.1.1.jar, I will then upload, everyone pay attention to my comments, for download use).

 Public<T> list<t>GetAll(String sql, class<t> clazz) {List<t> List =NewArraylist<t> (); Connection conn =NULL; Statement STATM =NULL; ResultSet rs =NULL;Try{conn = jdbcutils.getconnection ();            STATM = Conn.createstatement ();            rs = statm.executequery (SQL); ResultSetMetaData RSMD = Rs.getmetadata (); while(Rs.next ()) {T t = clazz.newinstance ();intCloumncount = Rsmd.getcolumncount (); for(inti =1; I <= Cloumncount;                    i++) {String cloumnname = Rsmd.getcolumnlabel (i);                    Object cloumnval = Rs.getobject (cloumnname);                Propertyutils.setproperty (t, Cloumnname, Cloumnval);            } list.add (t); }        }Catch(Exception e)        {E.printstacktrace (); }finally{Jdbcutils.close (RS, STATM, conn); }returnList }

This is to get multiple rows, return multiple objects, and store them with a list.

Two Tests were done:

 @Test  public  void  test1  ( ) {String sql =  "select order_id orderId, Order_name ordername, order_date orderDate FRO        M ' order ' WHERE order_id > 1 ";        list<order> orders = getAll (sql, Order.class); for  (Order order:orders)        {System.out.println (order); }}  @Test  public  void  test  () {String sql = " select order_id Orde        RId, Order_name ordername, order_date orderDate from ' order ' WHERE order_id = 1 ";        Order order = Get (sql, Order.class);    SYSTEM.OUT.PRINTLN (order); }

As a reminder, the use of aliases, database and Java program naming specifications may be different, so in order to ensure that the corresponding column names and property names are the same, we need to use aliases. (Getcolumnlabel () priority return alias)

Some exercises are also attached:
1. Create database table examstudent, table structure is as follows:

2. Add some data to the database table

3. Insert a new Student message
Please enter the candidate's details
Type:
Idcard:
Examcard:
Studentname:
Location:
Grade:

Information Entry Success!
4. Check the student's basic information according to the ID number and ticket number entered
5. Delete students according to the candidate number

OK, let's evolve here today. Continue tomorrow. LG's screen is indeed very good!

The evolution of JDBC 2