Ramble on JDBC

This article can be used as the learning notes of the Beijing Academy JDBC Course;
Introduction What is JDBC?
JDBC Full name (Java database Connectivity Java DB connection)
What does it do?
As for what it is, it should be the old saying, a long story.
A long time ago, as a user, we have C,c++,java ... Various languages, on the other hand the database field Oracle,mysql,db2,sqlserver is also a wide variety.
In order to facilitate the use of our users of the major database vendors have built their own API, such as

It looks good, too.
Later, we still feel trouble, although each vendor has its own API, but I Oracle and MYAQL after all not the same AH! As a customer, we do not want to learn so many APIs, how to do?
Add another layer of chant!

As for ODBC is a set of things Microsoft, do things like JDBC, is to block the different databases!
Looking at the diagram above, you should understand that each database vendor will develop its own JDBC jar package.
At this point, we do not need to control the various database differences. (Of course, the SQL syntax of each database is slightly different, then hibernate appears, here we do not talk first)
We can easily use each database only by understanding a unified interface.


Programming steps
Loaddriver

    Class.forName ("Com.mysql.jdbc.Driver");    Class.forName ("Com.mysql.jdbc.Driver"). newinstance ();    New Com.mysql.jdbc.Driver ();

There are three ways to do it, but we often use the first way, what is the difference between these three?
Let's look at this example

Package Load;public class LoadTest {public    static void Main (string[] args) {        try {            class.forname ("load. Loaded ");            System.out.println ("*******");            Class.forName ("load. Loaded "). newinstance ();            SYSTEM.OUT.PRINTLN ("* * * *");            New Loaded ();        } catch (ClassNotFoundException | illegalaccessexception| Instantiationexception e) {            //TODO auto-generated catch block            e.printstacktrace ();        }    }} Package Load;public class Loaded {    static{        System.out.println ("I am Static");    }    Public Loaded () {        System.out.println ("I am Constructed");}    }

Operation Result:
I am static
*******
I am constructed
*****
I am constructed
People should understand.
Class.forName ("* * *"); A class is loaded and the returned result is of class type, and the static statement blocks in the class are executed during the loading process;
Class.forName ("* * *"). newinstance (); It is the same as the new * * * (), of course, the class is only loaded once, so the above call this line of code does not show I am static
The difference between the new * * * and the above is that this method can pass parameters and construct different classes, but the above newinstance can only construct non-parametric objects;
In layman's words, the JVM is going to generate a class
It takes two steps to load and instantiate!
The first way is to load, the next two are both loaded and instantiated (of course, if it's already loaded, it doesn't have to be loaded anymore)

In addition, the basic analysis of this part of the source code, such as the com.mysql.jdbc.Driver in the end of what has been done, we say later, here is not much discussion.

Get connection

Drivermanager.getconnection (Url,username,password);

Three parameters of the meaning I will not say more, here need our attention is actually a URL, but we also do not have to memorize, know that there is such a thing OK;

Access to statemment; Statement st=con.creatstatemnent ();
Get the result set; First we have to show that the result set here is organized using resultset.
Resutset rs=st.executequery ("SELECT * from Admininfo");
Traverse result set: while (Rs.next ()) {
System.out.println (rs.getstring ("Filedname"));
}
Here I have to make it clear that even if you want to get the first piece of data in the dataset, Rs.next () must have this line. We can understand that, in the initial case, the resultset internal "cursor" in front of the first data, we have to next to find the next piece of data!
Close a principle, after the established object is first closed.


The most basic JDBC

The following code, write the comparison of the whole, only a request, back down! Don't say, look first, then again, this is the basis! should be full text dictation!!

Import Java.sql.connection;import java.sql.drivermanager;import java.sql.resultset;import java.sql.SQLException; Import Java.sql.statement;public class Jdbc_test2 {public static void main (string[] args) {Connection        Conn=null;        Statement St=null;        ResultSet Rs=null;        String url= "Jdbc:mysql://localhost:3306/webexample?useunicode=true&characterencoding=utf-8";        String username= "root";        String password= "";            try {class.forname ("com.mysql.jdbc.Driver");            Conn=drivermanager.getconnection (Url,username,password);            St=conn.createstatement ();            Rs=st.executequery ("SELECT * from Admininfo");            while (Rs.next ()) {System.out.println (rs.getstring ("Aname"));           }} catch (ClassNotFoundException e) {e.printstacktrace ();        log4j Specific Records}catch (SQLException e) {e.printstacktrace (); } finally{try {if (rs!=null) {rs.close ();                Rs=null;                    } if (St!=null) {st.close ();                St=null;                    } if (Conn!=null) {conn.close ();                Conn=null; }} catch (SQLException e) {//TODO auto-generated catch block E            . Printstacktrace (); }        }    }}

A few knowledge points preparedstatement In fact statement itself is enough, but a bit inconvenient, where inconvenient?
Look at the following example:
I want to insert a piece of data into the database as follows

            String name= "Dong Lei";            int id=16;            String sql= "INSERT into Admininfo (aname,aid) VALUES ('" +name+ "'," +id+ ")";            St.executeupdate (SQL);

Don't you think it would be a hassle to generate that SQL statement? You can make a mistake by writing less.
So with PreparedStatement, it is also an interface that inherits from statement.
Its usage is

            String name= "Dong Lei Peak 2";            int id=18;            String sql= "INSERT into Admininfo (Aname,aid) VALUES (?,?)";            PreparedStatement st=conn.preparestatement (SQL);            St.setstring (1, name);         Injects a string type value to the first field name  This is not starting from 0            st.setint (2, id);            St.execute ();

Replace the characters you want to insert with a few, much clearer than the above SQL, right?

Batch issues What if I want to execute multiple SQL statements?
Using statement

            String name= "Dong Lei Peak 2";            int id=154;            int id2=487;            String sql= "INSERT into Admininfo (aname,aid) VALUES ('" +name+ "'," +id+ ")";            String sql2= "INSERT into Admininfo (aname,aid) VALUES ('" +name+ "'," +id2+ ")";            SYSTEM.OUT.PRINTLN (SQL);            St=conn.createstatement ();            St.executeupdate (SQL);            St.executeupdate (SQL2);

Using Pretaredstatement

            String name= "Dong Lei Peak 2";            int id=48;            String sql= "INSERT into Admininfo (Aname,aid) VALUES (?,?)";            SYSTEM.OUT.PRINTLN (SQL);            St=conn.preparestatement (SQL);            St.setstring (1, name);            St.setint (2, id);            St.execute ();                        St=conn.preparestatement (SQL);            St.setstring (1, name);            St.setint (2, id+1);            St.execute ();

It doesn't seem so convenient.
Look at the new method
Using statement

            St=conn.createstatement ();            St.addbatch ("INSERT into Admininfo (aname,aid) VALUES ('" +name+ "'," +id+ ")");            St.addbatch ("INSERT into Admininfo (aname,aid) VALUES ('" +name2+ "'," +id2+ ")");            St.executebatch ();

Using Pretaredstatement

            String sql= "INSERT into Admininfo (Aname,aid) VALUES (?,?)";            SYSTEM.OUT.PRINTLN (SQL);            St=conn.preparestatement (SQL);            St.setstring (1, name);            St.setint (2, id);            St.addbatch ();                        St.setstring (1, name+ "SS");            St.setint (2, id+15);            St.addbatch ();            St.executebatch ();

You can use it, there's no need to delve into it.

Transactiontransaction we are generally translated into a session, is an inseparable multiple (or a) SQL statement, in a transaction, these SQL statements are either executed successfully or not executed.
What's the use of it? Think of the bank.
In JDBC, the default SQL statement is a transaction.
Look at the code

            try{                        Conn.setautocommit (false);            St=conn.createstatement ();            St.addbatch ("INSERT into Admininfo (aname,aid) VALUES ('" +name+ "'," +id+ ")");            St.addbatch ("INSERT into Admininfo (aname,aid) VALUES ('" +name2+ "'," +id2+ ")");            St.executebatch ();            Conn.commit ();            Conn.setautocommit (true);    I'm going to have to change it            .}catch (Exception e) {                if (conn!=null) {                conn.rollback ();          SQL code Revocation                conn.setautocommit (true);  I have to change it back.                }            }

Scrollable result set

            String sql= "SELECT * from Admininfo order by Aid";            St=conn.createstatement (resultset.type_scroll_insensitive,resultset.concur_read_only);                    Rs=st.executequery (SQL);            /* Admininfo Table contents * Aid Aname DLF 16 Dong Lei 18 Dong Lei 2 48 Dong Lei PK 2 49 Dong Lei 2 55 Dong Lei 2 154 Dong Lei 2 155 Dong Lei 2 487 Dong Lei 2 1599 Dong Lei PK 2            SS 2000 Dong Lei 2 2015 Dong Lei 2SS 3000 Dong Lei 2666 8000 Dong Lei 2hhhh 10001 WYF            10002 ZRK */Rs.absolute (4);            System.out.println ("4th" + rs.getint ("Aid"));            System.out.println ("The first few" + Rs.getrow ());            Rs.first ();            System.out.println ("First" + Rs.getint ("Aid"));            Rs.last ();            System.out.println ("Last" + Rs.getint ("Aid"));            SYSTEM.OUT.PRINTLN ("Total" + rs.getrow ()); System.out.prinTLN ("Whether the last one" + rs.islast ());            System.out.println ("Last End" + rs.isafterlast ());            Rs.next (); System.out.println ("Last End" + rs.isafterlast ());

Results
4th 48
The first few 4
The first one 15
Last one 10002
Total 16
Whether the last true
Final End False
Final End True
Look at the names of these methods and the results, we should be able to know the role of the method, but the Isafterlast method is a bit confusing.
Back of the last one?
This method is to determine whether the current pointer has reached the end of the file, note: The end of the file and the last record is different.

For example, when we start to read ResultSet, the pointer is in the file header, first requires Rs.next () to execute this method, the pointer moves to the first record before it can be read. To the end of the file is the same, after reading the last record, and then continue to read the end of the file, no record, the execution of the Isafterlast () method returns True.

Resources

The Isafterlast () method in the Http://blog.163.com/yutao_inx_319/blog/static/207234007201311177482930/java,ResultSet class

Http://www.cnblogs.com/shosky/archive/2011/07/22/2114290.html three different kinds of loading methods

Ramble on JDBC