Basic knowledge of JDBC connection to MySQL

This is just my own essay blog ~, used to occasionally recall knowledge, there may be some errors, such as errors, please correct ~

First, for the JDBC connection MySQL, to understand the basic framework structure

The picture is quite rotten, about this structure

Then look at the specific implementation of the Code:;

public class Dbutil {private String user = "root";p rivate string password = "root";p rivate string url = "Jdbc:mysql://loca LHOST:3306/MYDB6 ";p rivate static Dbutil dbutil; Connection Connection = null;//Single Example: Construction method Privatization Private Dbutil () {}public synchronized static dbutil getinstance () {if (Dbutil = = null) {Dbutil = new Dbutil ();} return dbutil;} /** * CREATE database connection */public Connection getconnection () {if (Connection = = null) {try {//Register drive Class.forName ("Com.mysql.jdbc.Driv ER ");//Get Connection connection = drivermanager.getconnection (URL, user, password);} catch (ClassNotFoundException e) {//Todo auto-generated catch Blocke.printstacktrace ();} catch (SQLException e) {//TODO Auto-generated catch Blocke.printstacktrace ();}} return connection;}
}

The above is a Dbutil class created by a singleton pattern. What can you do with this class? You can implement----database connection!

Yes, the role of the connection interface is to connect to the database-
A connection to a specific database (session). Executes the SQL statement in the connection context and returns the result.

How do you get this connection?  --To establish this connection, you need to register a driver---Hmm ~ this code class.forname ("Com.mysql.jdbc.Driver");

It is obtained through the mechanism of reflection, does not understand the reflection? Well, then you'll have to remember, hahaha.

Connection = drivermanager.getconnection (URL, user, password); this sentence is used to get the connection, these three parameters are the address of the data, JDBC:MYSQL://IP address: Port number/ Name of the database

Yes, and then years later. How to write data to a database through JDBC

 Public intinsertstubystatement () {String SQL1= "INSERT into Stu (sname,sage) VALUES (' AAA ', 1)"; String SQL2= "INSERT into Stu (sname,sage) VALUES (' BBB ', 1)"; String Sql3= "INSERT into Stu (sname,sage) VALUES (' CCC ', 1)"; Connection Connection= Getconnection ();//get a connection to a databaseStatement stmt =NULL; Try{stmt=(Statement) connection.createstatement (); Connection.setautocommit (false); Stmt.addbatch (SQL1);//Bulk Add SQL statementsStmt.addbatch (SQL2);//Bulk Add SQL statementsStmt.addbatch (SQL3);//Bulk Add SQL statements//Disposable ExecutionStmt.executebatch ();            Connection.commit (); System.out.println ("Batch Succeeded"); } Catch(SQLException e) {//TODO auto-generated Catch blockE.printstacktrace (); } finally {            Try {                if(stmt! =NULL) {stmt.close (); }            } Catch(Exception E2) {}}returni; }

Here you use the statement to implement, write out the SQL statement you want to manipulate. (mention, generally these operations are placed in the Dbutil class of the amount, this method I also put in dbutil).

Step two: Obtain the database according to the method defined above.

The following is the acquisition of a statement object, and then through the object's Stmt.executebatch (), you can execute the statement just now in the database, so that the static insertion of data.

What about the dynamic insertion of data? -----that is the use of another object that can implement the statement PreparedStatement

Go directly to the code.

 Public intInsertstu (String sname,intSage) {String SQL= "INSERT into Stu (sname,sage) VALUES (?,?)"; Connection Connection= Getconnection ();//get a connection to a databasePreparedStatement PS =NULL; inti = 0; Try{PS=(PreparedStatement) connection.preparestatement (SQL); Ps.setstring (1, sname); Ps.setint (2, Sage); I=ps.executeupdate (); } Catch(SQLException e) {//TODO auto-generated Catch blockE.printstacktrace (); }finally {            Try {                if(ps!=NULL) {ps.close (); }            } Catch(Exception E2) {}}returni; }

The defined Insert method has a value of two parameters

What's the difference with statement? --One is the difference between a SQL string statement, a lot more "? ", the effect is---look at the following two lines of code

ps.setstring (1, sname); Ps.setint (2, Sage);
ps.set**** (A, B) has two parameters, and two in the SQL statement? , so the parameter A, which represents the "Number of SQL statements"? ", if the argument is 1, the first in the SQL statement"? "is replaced by parameter B,
if the parameter is 2, then the first "?" in the SQL statement is described. is replaced with parameter B. This makes it possible to insert data dynamically, so we simply pass in the data we want to insert when we call the Insert method, instead of writing a new SQL statement every time.
For i=ps.executeupdate (); statement, it returns this time after your database operation there are several data that have an impact. This method for inserting, of course, is i=1.

But for the output and modification is not necessarily, delete the changes I directly paste the code will not do the explanation

Modify
Public intUpdatestu (intsage,string sname) {String SQL= "Updae stu set sage =?" where sname =? "; Connection Connection=getconnection (); PreparedStatement PS=NULL; inti = 0; Try{PS=(PreparedStatement) connection.preparestatement (SQL); Ps.setint (1, Sage); Ps.setstring (2, sname); I=ps.executeupdate (); } Catch(SQLException e) {//TODO auto-generated Catch blockE.printstacktrace (); }finally {            Try {                if(ps!=NULL) {ps.close (); }            } Catch(Exception E2) {}}returni; }    //Delete         Public intDeletestu (String sname,intSage) {String SQL= "Delete from Stu where sname=?" and sage =? "; Connection Conn= Getconnection ();//get a database connectionPreparedStatement PS =NULL; inti = 0; Try{PS=(PreparedStatement) conn.preparestatement (SQL); Ps.setstring (1, sname); Ps.setint (2, Sage); I=ps.executeupdate (); } Catch(SQLException e) {//TODO auto-generated Catch blockE.printstacktrace (); }finally {                Try {                    if(PS! =NULL) Ps.close (); }Catch(Exception e) {//Todo:handle Exception                }            }            returni; }

Database The most important is what---query data, relative to additions and deletions--may be a little more cumbersome to check
The code is as follows:

/*** Check ALL students *@return     */     PublicArraylist<stu>Querystu () {String SQL= "SELECT * from Stu";//The order of SELECT * or fields is consistent in the databaseConnection conn =getconnection (); PreparedStatement PS=NULL; ResultSet RS=NULL; ArrayList<Stu> Stus =NewArraylist<stu> ();//This collection is used to store each student object        Try{PS=(PreparedStatement) conn.preparestatement (SQL); RS=Ps.executequery (); //Traverse RS             while(Rs.next ()) {//True indicates that there is a next piece of data                intid = rs.getint ("id"); String sname= Rs.getstring ("sname"); intSage = Rs.getint ("Sage"); //Create a Stu objectStu Stu =NewStu (ID, sname, sage); //Collecting ObjectsStus.add (STU); }        } Catch(SQLException e) {//TODO auto-generated Catch blockE.printstacktrace (); }finally {            Try {                if(rs! =NULL) Rs.close (); if(PS! =NULL) Ps.close (); }Catch(Exception e) {//Todo:handle Exception            }        }                returnStus; }

Tell me where the point is. ResultSet rs = null; This object stores a collection of results for a query. SELECT * from Stu this should be all the data for each student in the student table that is queried,

So RS can store such a collection and then iterate through the data, assign the data to a student object, add the object to the collection, and get all the data in the table.

This is mainly about the basic part, and so the next time you continue to explore some of the other JDBC Joint operations ~

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

Basic knowledge of JDBC connection to MySQL