JDBC connection mode and basic steps, you will gain if you are willing to read it (handwritten thousand words, please point out any deficiencies)

Keywords: Database MySQL JDBC SQL

JDBC connection mode and steps

1. Register driver

Diver dirver =new com.mysql.jdbc.Diver();
In this way, the interface of Sun company is implemented first. mysql manufacturers implement it according to the interface. Dirver on the left is the interface, and interface implementation classes on the right
DiverManager.registerDirver(dirver);

3. Get connection
String url=“jdbc:mysql://localhost:mysql port: database name ";
String user="root";
String password="";

Connection conn=DirverManger.getConnection(url,user,password);
System.out.println("database connection object =" + conn ");

  1. Get database connection object

Statement stmt= conn.createStatement();

  1. Execute sql

String sql="";
The statements in sql and DML statements (insert update delete) return the number of records that affect the database;
For example: insert into table name (field name 1. Field name 2) value (value of field name 1, value of field name 2);
int count=stmt.executeUpdate(sql);

  1. Release resources

To ensure that resources are released, close resources in finall
And close from small to large
try... catch

Merge code

puclic static void main(String[] args){
Connection conn=null;
Statement stmt=null;
//The reason why conn and state are put here is to change them into member variables, otherwise they cannot be accessed in finally;
//Register driver
try{
Diver dirver =new com.mysql.jdbc.Diver();
DiverManager.registerDirver(new com.mysql.jdbc.Diver());
//You can also merge and write diversmanager. Registerdirver (dirver);
//Get connection
String url="jdbc:mysql://localhost:mysql port number (3306): database name ";
String user="root";//Here is your mysql user name
String password="";//The password here is your mysql login password
conn=DirverManger.getConnection(url,user,password);
stmt= conn.createStatement();
 //Execute DML statement
int count=stmt.executeUpdate(sql);}
}catch(SQLException e){
e.printStackTrace();
}
finally{
if(stmt!=null){
try{ stmt.close();
}catch(SQLexception  e){
e.printStackTrace();
}
}
if(conn!=null){
try{
conn.close();
catch(SQLException){
e.printStackTrace();
}
}
}

2. The second method

When we open the mysql installation path, open SRC (decompress it first), click the com directory, and then click the mysql directory. There is a jdbc file in the directory, and a java file of Diver.java will appear
You will find a static code block in it
We should know that static code blocks will be executed during class loading. This part of code is equivalent to completing our previous registration driver, that is, as long as we can load this class, we don't have to knock the registration information

static{
try{
java.sql.DiverManfer.registerDriver(new Dirver());
}catch{
throw new RuntimeException("Con't register driver!")
} }

But how do we load into this class 💫?
At this time, you can consider using reflection to load bytecode
class.forname("com.msql.jdbc.Driver"); loading will be executed at this time

static{
try{
java.sql.DiverManfer.registerDriver(new Dirver());
}catch{
throw new RuntimeException("Con't register driver!")
} }

So we can write it in another way

puclic static void main(String[] args){
Connection conn=null;
Statement stmt=null;
//The reason why conn and state are put here is to change them into member variables, otherwise they cannot be accessed in finally;
//Register driver
try{
dirver="com.mysql.jdbc.Driver";
class.forname("driver");
//class.forname("com.msql.jdbc.Driver");
DiverManager.registerDirver(new com.mysql.jdbc.Diver());
//You can also merge and write diversmanager. Registerdirver (dirver);
//Get connection
String url="jdbc:mysql://localhost:mysql port number (3306): database name ";
String user="root";//Here is your mysql user name
String password="333";//The password here is your mysql login password
conn=DirverManger.getConnection(url,user,password);
stmt= conn.createStatement();
 //Execute DML statement
int count=stmt.executeUpdate(sql);}
}catch(SQLException e){
e.printStackTrace();
}
finally{
if(stmt!=null){
try{ stmt.close();
}catch(SQLexception  e){
e.printStackTrace();
}
}
if(conn!=null){
try{
conn.close();
catch(SQLException){
e.printStackTrace();
}
}
}

The second method is used most. Why is this method used most 💫?
Because the parameter is a string, the string can be written to the XXX.properties file
This kind of file is called property configuration file class, such as this

The third method

With this file, we can also configure the resource binding attribute file
ResourceBundle bundle=ResourceBundle.getBundle("jdbc");
//This jdbc is the name of the propeties file, but be careful to remove the suffix and do not add propeties;

puclic static void main(String[] args){

//The following code is from the previous properties file, that is, the jdbc.propeties file
ResourceBundle bundle=ResourceBundle.getBundle("jdbc");
//The parameters passed in by getString() are the attributes in the above file
String dirver=bundle.getString("diver");
String url=bundle.getString("url");
String user=bundle.getString("user");
String password=bundle.getString("password");

Connection conn=null;
Statement stmt=null;
//The reason why conn and state are put here is to change them into member variables, otherwise they cannot be accessed in finally;
//Register driver
try{
dirver="com.mysql.jdbc.Driver";
class.forname("driver");
//class.forname("com.msql.jdbc.Driver");
DiverManager.registerDirver(new com.mysql.jdbc.Diver());
//You can also merge and write diversmanager. Registerdirver (dirver);
//Get connection
conn=DirverManger.getConnection(url,user,password);
stmt= conn.createStatement();
 //Execute DML statement
int count=stmt.executeUpdate(sql);}
}catch(SQLException e){
e.printStackTrace();
}
finally{
if(stmt!=null){
try{ stmt.close();
}catch(SQLexception  e){
e.printStackTrace();
}
}
if(conn!=null){
try{
conn.close();
catch(SQLException){
e.printStackTrace();
}
}
}

At this time, the question arises: why use such code? 💫
In fact, in actual development, it is not recommended to write the code dead (that is, put the code in the java file) If we use this method, we can just modify the attribute value in the file propeties. If you ask a non-technical person to tell him that you need to open the java file first and then modify the password and user name, it is obviously inappropriate, so tell him that you only need to open this file and change some passwords and names on it. It's more convenient You can get a graphical interface\

Now let's show you how to process the result set. Previously, we can only use DML(insert delete update) statements. To check whether it is successful, we need to enter mysql to view the table. This has some shortcomings, so we provide a way to view the results. First look at the next code:

public static void mian(String[] args)
{
Connection conn=null;
Statement stmt=null;
ResultSet rs=null;
try{
class.forName("com.mysql.jdbc.Dirver");
String url="jdbc:mysql://localhost/3306 / database name ";
String password="333";
String user=root;
conn=DriverManger.getConnection(url,user,password);
stmt=conn.createStatement();
String sql="select empno,name from emp;
//This method cannot be used at this time. This method can only execute DML statements, that is (insert delete update)
rs=stmt.executeUpdate(sql);❌
//To execute an MQL statement, that is, a select query statement, you need to use this method
rs.executeQuery(sql);✅
}catch(Exception e){
e.printStackTrace();
}finally{
if(rs!=null){
try{
rs.close();
}catce(Exception  e){
e.printStackTrace();
}
}
if(stmt!=null){
try{
stmt.close()
}catch(Exception  e){
e.printStackTrace();
}
}
if(conn!=null){
try{
conn.close()
}catch(Exception  e){
e.printStackTrace();
}
}
}

Because I didn't install mysql, I drew a table to facilitate you to understand the table similar to ours

empnoname
231123jack
123145lusi
532666yuhan

If you are sensitive, you may think that such a result seems to be generated by traversing the result set
It is verified that there is a method next() in the ResultSet interface; it returns a Boolean type
boolean flag=rs.next(); this method is used to judge whether there is data in the next line. It is worth noting that this method is similar to the tube cursor. If I remember correctly, it is similar to the pointer to the stack. At the beginning, it does not point to the first element (I will not elaborate here, and the data structure has not been reviewed) , we are similar, that is, we just point to the head of the table, and then next is True, which means that there are elements in this row (I may be sorry for my expression). I'd better draw a picture.

How to get the data of each field when the cursor points to this line 💫?
Sure enough, there is a way to get the value of the field, which is getString();

while(rs.next())
{
String empno=rs.getString(1);
String name=rs.getString(2);
System.out.pritln(empno+","+name);
}

💦 Run out is our table for
231123,jack
123145, lusi
532666,yuhan

So the question is, can we get the result without subscript and field name? Let's try
while(rs.next())

{
String empno=rs.getString("empno");
String name=rs.getString("name");
System.out.pritln(empno+","+name);
}

The result of running is still like this
231123,jack
123145, lusi
532666,yuhan

This is very interesting. So if we fix the watch name, can it still have such an effect
We all know that it is easy to modify the field name of the table by using as, so we change it to select empno as ID, name from EMP; can we find the code when we do not change the field name before running

while(rs.next())
{
String empno=rs.getString("empno");
String name=rs.getString("name");
System.out.pritln(empno+","+name);
}
//At this time, it was reported that empno was not found

Then I run the code after modifying the field

while(rs.next())
{
String empno=rs.getString("id");
String name=rs.getString("name");
System.out.pritln(id+","+name);
}

The wonder is that the running result appears at this time
231123,jack
123145, lusi
532666,yuhan
Through comparison, we know that we actually run one of the codes written on our sql statements. If you modify the fields, you can only get the results through the modified fields:

💥💥💥💥 a key:
The column name is not the name of the msql table, but the set column name defined by you
select empno as id, name from emp

Posted by wxflint on Sat, 23 Oct 2021 07:33:34 -0700