1. initial API
The client can not be implemented out of thin air , But the database will provide us with a set of API For our use ;
But there are many kinds of databases , Provided by different databases API All different ;MYSQL Medium API and SQL server Medium API It's different , And the differences are great ;
What is? API? API namely application programming interface
Provides a set of functions / class / method , Let users use it directly , This is a very broad concept ;
Whenever we operate on a database , Use different API, It's a big one ; So in java in , To solve this problem , Introduced JDBC, Can be understood as java Self contained set of data operation API, This group API It can be said that it covers all kinds of database operation modes , Put different API It's United ;
java Do it yourself JDBC
API And specific databases API Conversion of ; Of different databases API Will be converted into JDBC Stylized API, This intermediate converter acts as a data driver ; Like this, it used to support many formats of charging heads ; We can switch the plug connector of our mobile phone through the adapter , So the database driver is the adapter ;
2, use java Code operation database
1) establish Datasource object , This is a preparatory work ; 2) be based on Datasource object , in the light of DataSource Make some configuration , So that the server can be accessed more conveniently in the future
Here we need to use the downward transformation , Configuration requires three aspects of information URL,user,Password
3) establish Connection, Establish connection with database ( Equivalent to opening the client , Password entered , Connection succeeded
4) utilize PrepareStatement To assemble specific SQL sentence , This is equivalent to specific input on the client SQL Process of ;
5) Assembled SQL after , To execute SQL Statements in , Equivalent to SQL The client hit enter 6） View the results of the server , The screen will show 7） Close connection , Free resources
Here is the code ： According to different scenarios , Assemble different SQL object
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource; import
javax.sql.DataSource; import java.sql.Connection; import
java.sql.PreparedStatement; import java.sql.SQLException; public class MYSQL {
public static void main(String[] args)throws SQLException { 1) Make some preparations
DataSource dataSource=new
MysqlDataSource();((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/java?characterEncoding=utf-8&useSSL=true");
next step Set user name ((MysqlDataSource)dataSource).setUser("root"); Set password
((MysqlDataSource)dataSource).setPassword("12503487");
————————————————————————————————————————————————————————————————————————————————————————
2 Establish connection with database , After the connection is established, the following data can be processed
Make sure Connection Is from java.sql.Connection, When a connection establishment error occurs , Will throw this exception
The purpose of establishing a connection is to ensure that the current network connection is normal , If abnormal , Will throw the above exception
Connection The life cycle of is relatively short , Each request can create a new Connection Connection
connection=dataSource.getConnection();
_______________________________________________________________________________
3 Assemble one SQL sentence , There is a preparestatement // In the current instance , The current data is dead , We can make the program dynamically allocate ; String
SQL="insert into student values(1," Cao Cao ",10); PreparedStatement
preparedStatement=connection.prepareStatement(SQL);
___________________________________________________________________________
The third step can be written as follows , The following three parameters can be passed Scanner This class to dynamically obtain int id=1; String name=" Cao Cao "; int
classID=10; String SQL="insert into students values(???)";
The question mark here is a placeholder , You can replace the value of a specific variable with ? Go inside ; PrepareStatement
statement=connection.PrepareStatement(SQL); statement.setInt(1,id);
statement.setString(2,name); statement.setInt(3,classid);
——————————————————————————————————————————————————————————————————————————————————
4 After assembly , Executable statement int ret=preparedStatement.executeUpdate(); //insert dele
update Universal use ececuteUpdate To execute // however select Just use executeQuery To execute , The return value indicates that the operation has executed multiple lines
System.out.println(ret); //5 close resource , Created after , Who releases first preparedStatement.close();
connection.close(); } }
We need to pay attention ：
((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/java?characterEncoding=utf-8&useSSL=true");
The first part is the downward transformation , The second part is setURL, Indicates the server that needs to access that database
jdbc:mysql This is a fixed protocol name
IP Address indicates which host to access , The port number is used to distinguish multiple servers on a host
The port number is followed by the name of the database to be accessed createdatabase[ Database name ]
? The following indicates that the character set should be consistent with that of the server configured in the database , Otherwise, the character set will be garbled
Finally, it indicates whether to encrypt
Currently we JDBC Classes and objects mainly used in programming
1）DataSource It is used to configure how to connect MYSQL, One of his class objects calls a getConnection Method returns a Connection object ;
2）Connection Indicates the established primary connection , To operate the database, you need to establish a connection to see if the ability to send requests and receive data is defective , His class object calls a prepareStatement Method to return a PrepareStatement object ;
3)PrepareStatement He's connected to a SQL sentence , Then she calls executeUpdate perhaps executeQuery To perform specific SQL sentence ;
4)ResultSet It is used to represent Select Result set of find results ,MYSQL The returned results are all in this class ;
----------------------------------------------------------------------------------------------------------------------------------
Example 1 ： Let's use one JDBC Programming implementation MYSQL Database lookup ; Different from the above results MYSQL A table will be returned according to the query request sent ;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource; import
javax.sql.DataSource; import java.sql.Connection; import
java.sql.PreparedStatement; import java.sql.ResultSet; import
java.sql.SQLException; public class HelloServlet{ public static void
main(String[] args)throws SQLException { //1 establish DataSource object DataSource
dataSource=new MysqlDataSource(); ((MysqlDataSource)
dataSource).setURL("jdbc:mysql://127.0.0.1:3306/javaweb?characterEncoding=utf-8&userSSL=true");
((MysqlDataSource) dataSource).setUser("root"); ((MysqlDataSource)
dataSource).setPassword("12503487"); //2 With the help of Connection object , Connect to the database Connection
connection=dataSource.getConnection(); //3 Assembly SQL sentence ; String sql="select * from
student"; PreparedStatement preparedStatement=
connection.prepareStatement(sql); //4 Execute request ResultSet
resultSet=preparedStatement.executeQuery();
//5 Traversal result set , The result set is equivalent to a table , There are many rows in this table , Each line is a record next() On the one hand, it is used to judge whether the next line currently exists , On the other hand, if there is a next row, you can get this row
while(resultSet.next()) {// Here, you must ensure that the column names in the method are consistent with those in the table int
id=resultSet.getInt("id"); String name=resultSet.getString("name"); int
classid=resultSet.getInt("classid");
System.out.println("id"+id+"name"+name+"classid"+classid); } //6 Close release resource ;
resultSet.close(); preparedStatement.close(); connection.close(); } }
Example 2 ： Delete a specific name
com.mysql.jdbc.jdbc2.optional.MysqlDataSource; import javax.sql.DataSource;
import java.sql.Connection; import java.sql.PreparedStatement; import
java.sql.SQLException; import java.util.Scanner; public class HelloServlet{
public static void main(String[] args)throws SQLException {
System.out.println(" Please enter the name of the student you want to delete "); Scanner scanner=new Scanner(System.in);
String name=scanner.next(); //1 establish DataSource object DataSource dataSource=new
MysqlDataSource(); ((MysqlDataSource)
dataSource).setURL("jdbc:mysql://127.0.0.1:3306/javaweb?characterEncoding=utf-8&userSSL=true");
((MysqlDataSource) dataSource).setUser("root"); ((MysqlDataSource)
dataSource).setPassword("12503487"); //2 Establish connection with database Connection
connection=dataSource.getConnection(); //3 Assembly SQL sentence String SQL="delete from
student where name = ?"; PreparedStatement
preparedStatement=connection.prepareStatement(SQL);
preparedStatement.setString(1,name); //4 implement SQL int
ret=preparedStatement.executeUpdate(); if(ret==1) { System.out.println(" Deletion succeeded ");
}else{ System.out.println(" Delete failed "); } //5 Close and free resources preparedStatement.close();
connection.close(); } }
Example 3 ： Make modification , We need to modify id by n Student name of
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource; import
javax.sql.DataSource; import java.sql.Connection; import
java.sql.PreparedStatement; import java.sql.SQLException; import
java.util.Scanner; public class HelloServlet { public static void main(String[]
args)throws SQLException { System.out.println(" Please enter the name of the student you want to modify id"); Scanner
scanner=new Scanner(System.in); int id=scanner.nextInt(); String
name=scanner.nextLine(); DataSource dataSource=new MysqlDataSource();
((MysqlDataSource)
dataSource).setURL("jdbc:mysql://127.0.0.1:3306/javaweb?characterEncoding=utf-8&userSSL=true");
((MysqlDataSource) dataSource).setUser("root"); ((MysqlDataSource)
dataSource).setPassword("12503487"); Connection
connection=dataSource.getConnection(); String SQL="update student set name = ?
where id = ?"; PreparedStatement
preparedStatement=connection.prepareStatement(SQL);
preparedStatement.setString(1,name); preparedStatement.setInt(2,id); int
ret=preparedStatement.executeUpdate(); if(ret==1) { System.out.println(" Modified successfully ");
}else{ System.out.println(" Modification failed "); } preparedStatement.close();
connection.close(); } }
Technology