Introduction to jdbc and jdbc connection to databases

Introduction to jdbc and jdbc connection to databases
This document can be used as a learning note for the jdbc course of Beijing shangxue;
What is jdbc?
Jdbc full name (Java Database Connectivity java Database connection)
What does it do?
As for what it does, it's time to say the old saying ..
A long time ago, as users, we had various languages such as c, c ++, java... and Oracle, Mysql, DB2, and SQLServer in the database field.
To make it easier for our users to use their own APIs, such

It looks pretty good.
Later, we still felt in trouble. Although each vendor has its own API, my Oracle and Myaql are different after all! As a customer, we don't want to learn so many APIs. What should we do?
Add another layer!

Odbc is a set of things developed by Microsoft. Similar to jdbc, odbc shields different databases!
You can see from the figure above that each database manufacturer develops its own jdbc jar package.
So far, we do not need to worry about the differences between different databases. (Of course, the SQL Syntax of each database is slightly different. Later, Hibernate emerged. We will not talk about it here)
We only need to know a unified interface to make it easy to use each database.


Procedure
LoadDriver

  Class.forName("com.mysql.jdbc.Driver");
    Class.forName("com.mysql.jdbc.Driver").newInstance();
    new com.mysql.jdbc.Driver();

You can use either of the three methods, but we often use the first method. What is the difference between the three methods?
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");
    }
}

Running result:
I am static
*******
I am constructed
*****
I am constructed
You should understand
Class. forName ("***"); a Class is loaded and the returned result is of the Class type. During the loading process, a static statement block in the Class is executed;
Class. forName ("***"). newInstance (); it is actually the same as new *** (). Of course, the class is only loaded once, so I am static is not displayed when I call this line of code.
The difference between new *** () and above is that this method can pass parameters to construct different classes, but the newInstance above can only construct objects without parameters;
In other words, jvm must generate a class.
It takes two steps to load and instantiate it!
The first method is to load, and the last two are both loaded and instantiated (of course, if the previous one has already been loaded, you do not need to load it again)

In addition, the basic analysis of this part of source code, such as what is done in com. mysql. jdbc. Driver, will be discussed later.

Get Connection

DriverManager. getConnection (url, username, password );

I will not say much about the meaning of the three parameters. here we need to pay attention to the fact that it is also a url, but you do not have to remember it. If there is such a thing, it will be OK;

Get Statemment; Statement st = con. creatStatemnent ();
Obtain the result set. First, we must note that the result set here is organized by ResultSet.
ResutSet rs1_st.exe cuteQuery ("select * from admininfo ");
Traverse result set: while (rs. next ()){
System. out. println (rs. getString ("Filedname "));
}
Here I have to note that the rs. next () line is required even if you want to obtain the first data in the dataset. We can understand that the "cursor" in the resultset is in front of the first data in the initial situation. We need to click next to find the next data!
Close is a principle, and the created object is closed first.


The most basic jdbc

The following code is completely written. There is only one requirement! Don't talk about it. Let's look at it first. Later on, it's the foundation! The full text should be written in silence !!

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 record
        }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();
            }
        }
    }
}

Several knowledge points: in fact, the Statement itself is enough, but it is a little inconvenient. Where is it inconvenient?
See the following example:
I want to insert a piece of data into the database as follows:

  String name="Dong Leifeng";
            int id=16;
            String sql="insert into admininfo (Aname,Aid) values('"+name+"',"+id+") ";
            st.executeUpdate(sql);

Do you think it is very troublesome to generate that SQL statement? If you write less than one ', errors may occur.
Therefore, with PreparedStatement, it is also an interface inherited from Statement.
Its usage is

String name = "Dong Leifeng 2";
Int id=18;
String sql="insert into admininfo (Aname,Aid) values(?,?)";
PreparedStatement st=conn.prepareStatement(sql);
St.setstring (1, name); / / inject string type value name into the first field, which does not start from 0
st.setInt(2, id);
st.execute();

How many? It is much clearer than the preceding SQL statement to replace the characters to be inserted, isn't it?

Batch Processing what if I want to execute multiple SQL statements?
Use Statement

String name="Dong Leifeng 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);

Use PretaredStatement

  String name="Dong Leifeng2";
            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 too convenient.
View New Methods
Use 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()

Use 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();

It will be used. You don't have to go into it here.

TransactionTransaction is generally translated into sessions, which is an inseparable multiple (or one) SQL statement. In a Transaction, these SQL statements are either successfully executed or not executed.
What is its use? Are you still using explanations? Think about banks.
In jdbc, a 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); / / you need to change back when you are finished
}catch(Exception e){
if(conn!=null){
Conn.rollback(); / / SQL code undo
Conn.setautocommit (true); / / you need 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 content
*  Aid  Aname
15 DLF
16 Dong Leifeng
18 Dong Leifeng 2
48 Dong Leifeng 2
49 Dong Leifeng 2
55 Dong Leifeng 2
154 Dong Leifeng 2
155 Dong Leifeng 2
487 Dong Leifeng 2
1599 Dong Leifeng 2SS
2000 Dong Leifeng 2
2015 Dong Leifeng 2SS
3000 Dong Leifeng 2666
8000 Dong Leifeng 2hhh
10001 WYF
10002 zrk
* /
rs.absolute(4);
System. Out. Println ("4th" + rs.getint ("aid"));
System. Out. Println ("article" + 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 ("last" + rs.islast());
System. Out. Println ("last ending" + rs.isafterlast());
Rs.next ();
System. Out. Println ("last ending" + rs.isafterlast()); 

Result
4th 48
Article 4
First 15
Last 10002
Total 16
Whether the last one is true
End with false
End with true
Looking at the names and results of these methods, you should be able to understand the functions of the method, but the isAfterLast method is a bit confusing.
After the last one?
This method is used to determine whether the current pointer has reached the end of the file. Note: The end of the file is different from the last record.

For example, when we start to read the ResultSet, the pointer is located in the file header. First we need rs. next () to execute this method. The pointer can be read only after it is moved to the first record. The same is true at the end of the file. After reading the last record, continue to read it at the end of the file. If no record is found, the isAfterLast () method will return true.

References

IsAfterLast () method in the http://blog.163.com/yutao_inx_319/blog/static/207234007201311177482930/ java, ResultSet class

Differences of three types of http://www.cnblogs.com/shosky/archive/2011/07/22/2114290.html Loading Methods

How to Use JDBC to connect to MySQL

First, install MySQL correctly and create a database.
Studentinfo
Mysqlcreate database studentinfo;
Then write the java code,
ConnectToMySQL. java
Import java. SQL. *; public class ConnectToMySQL {public static Connection getConnection () throws SQLException, java. lang. classNotFoundException {String url = jdbc: mysql: // localhost: 3306/studentinfo; Class. forName (com. mysql. jdbc. driver); String userName = root; String password =; Connection con = DriverManager. getConnection (url, userName, password); return con;} public static void main (String [] args) {try {Connection con = getConnection (); Statement SQL = con. createStatement (); SQL .exe cute (drop table if exists student); SQL .exe cute (create table student (id int not null auto_increment, name varchar (20) not null default 'name ', math int not null default 60, primary key (id); SQL .exe cute (insert student values (1, 'aaa', '99 ')); SQL .exe cute (insert student values (2, 'bbb ', '77'); SQL .exe cute (insert student values (3, 'ccc ', '65 ')); string query = select * from student; ResultSet result = SQL .exe cuteQuery (query); System. out. println (Student table data:); System. out. println (---------------------------------); System. out. println (student ID + + name + mathematical score); System. out. println (---------------------------------); int number; String name; String math; while (result. next () {number = result. getInt (id); name = result. getString (name); math = result. getString (math); System. out. println (number ++ name ++ math )...... remaining full text>

Briefly Discuss the functions of spring, struts, and hibernate;

Let me give a brief introduction in a user-friendly language:
1. the role of struts avoids writing too much code on the jsp page, so that the jsp interface can be kept clean and tidy, and the mvc mode can be implemented. The fundamental role of struts is to facilitate later modification of the program.
2. Why does hibernate Save the trouble of connecting to the database by writing your own code?
3. spring reduces coupling. In the middle of the other two frameworks, the relationship between them is not absolute. In the end, it is also intended for future changes.
They are all used to achieve better teamwork.