Mysql jdbc getting started, mysqljdbc getting started
I. Basic concepts of jdbc
Jdbc: Java Database Connectivity
To unify database operations, sun defines a set of Apis called jdbc.
This api is completely composed of interfaces. We call the api when writing a program.
These interfaces are implemented by database manufacturers. Different database manufacturers provide different implementation classes,
These implementation classes are called database drivers.
Standards set by first-class companies
Second-stream companies provide services
Third-stream companies make products
Advantages of database driver handed over to the database vendor:
1. Java developers only need to maintain Java applications and a set of Unified Specifications
2. The database vendor provides specific Java drivers. If the database vendor changes
The underlying implementation of the database requires the database vendor to update the driver without affecting
Completed Java applications.
To put it simply, the technology that uses Java code to send SQL statements is jdbc.
Ii. Conditions for implementing the jdbc Program
1. You need to know where the database server is located
IP address and port number of the database
Ip address to host
Port: locate the program
2. You need to know the user name and password of the database.
3. How to connect to the MySQL database through jdbc?
1. Driver for Database Import
Mysql-connector-java-5.0.8-bin.jar
2. Database Registration driver
DriverManager. registerDriver (new com. mysql. jdbc. Driver ());
3. Establish a connection with the mysql database
String url = "jdbc: mysql: // localhost: 3306/test"; // jdbc is the master protocol and mysql is the sub-protocol.
String user = "root ";
String password = "root ";
Connection conn = DriverManager. getConnection (url, user, password );
4. Create a Statement object for sending SQL statements
Statement stmt = conn. createStatement ();
5. Write SQL statements
String SQL = "select * from users ";
6. Send an SQL statement to obtain the result set.
ResultSet rs = stmt.exe cuteQuery (SQL );
7. processing result set
System. out. println ("id | name | password | email | birthday ");
While (rs. next ()){
// Has the first line
Int id = rs. getInt ("id"); // The value of the column name is intuitive.
String name = rs. getString ("name ");
String psw = rs. getString ("password ");
String email = rs. getString ("email ");
Date birthday = rs. getDate ("birthday ");
System. out. println (id + "|" + name + "|" + psw + "|" + email + "|" + birthday );
}
8. Close the database connection. The last opened resource is released first. You need to determine whether the resource is disconnected before releasing it.
Rs. close ();
Stmt. close ();
Conn. close ();
4. jdbc program details
1. Registration driver
DriverManager. registerDriver (new com. mysql. jdbc. Driver ());
The preceding statement will cause two drivers to be registered.
The reason is that the Driver registration is completed in the static code block by checking the Driver class source code,
That is to say, the registration driver is actually very simple. You only need to load the driver class.
Class. forName ("com. mysql. jdbc. Driver ");
2. Create a database connection
Connection conn = DriverManager. getConnection (url, user, password );
Where:
Url, which is equivalent to the database access address. The programmer uses the url to specify the database to be accessed
// Url of the connected database
// The main jdbc protocol. mysql is a sub-protocol.
// Jdbc Protocol: Database sub-Protocol: // host: Port Number:/Database Name
Jdbc: mysql: [] /// localhost: 3306/test? Parameter Name: Parameter Value
Among them, jdbc is the main protocol, mysql is the sub-protocol, localhost is the host name, 3306 is the port number, and test is the database name.
Url can be followed by parameters, commonly used parameters are: user = root & password = root & characterEncoding = UTF-8
If the url address is followed by the user and password, you do not have to input a value again when creating a Connection object.
Connection conn = DriverManager. getConnection (url );
NOTE: If localhost: 3306 is accessed, the url can be saved as jdbc: mysql: // test.
3. Connection object
The Connection object is used to indicate the Connection with a database. All operations on the database in the program must be completed through this object.
Common methods include:
CreateStatement (): Creates a statement object that sends SQL statements to the database.
PrepareStatement (SQL): Creates a PrepareSatement object that sends pre-compiled SQL statements to the database.
PrepareCall (SQL): Creates a callableStatement object that executes the stored procedure.
SetAutoCommit (boolean autoCommit): sets whether a transaction is automatically committed.
Commit (): Submit the transaction on the link.
Rollback (): rolls back the transaction on this link.
4. Statement object
Used to send SQL statements to a database
Execute (String SQL): used to send arbitrary SQL statements to the database
ExecuteQuery (String SQL): Only query statements can be sent to Data. (Common)
ExecuteUpdate (String SQL): Only insert, update, or delete statements can be sent to the database (commonly used)
AddBatch (String SQL): puts Multiple SQL statements in one batch.
ExecuteBatch (): sends a batch of SQL statements to the database for execution.
5. ResultSet object
This object is especially important for query operations because it is used to encapsulate result sets.
The storage format is a form of table, which is also a column + row. To put it bluntly, it is the same as the query results in the dos command line window.
Traversal method:
The cursor points to the first row of the result set, that is, the header.
Move the cursor to the next row through next. If there is no next row, this method returns false.
To obtain the data of the current row, call the get method:
Get (int index) gets the number of columns starting from 1
Get (String columnName) commonly used values obtained based on column names
Note: The correspondence between the database data type and the Data Type in java
Common Methods for ResultSet objects
Next (): Move to the next row
Previous (): Move to the previous row
Absolute (int row): Move to the specified row
BeforeFirst (): Move the front of the resultSet.
AfterLast (): Move to the end of the resultSet.
6. Release database resources
Because the number of concurrent access connections allowed by data is usually limited, resources need to be released after use.
In java programs, we should put the code that must be executed in finally.
Resource release code
If (rs! = Null ){
Try {
Rs. close ();
} Catch (SQLException e ){
E. printStackTrace ();
}
Rs = null;
}
If (stmt! = Null ){
Try {
Stmt. close ();
} Catch (SQLException e ){
E. printStackTrace ();
}
Stmt = null;
}
If (conn! = Null ){
Try {
Conn. close ();
} Catch (SQLException e ){
E. printStackTrace ();
}
Conn = null;
}
7. Prevent SQL Injection
Perform logical judgment on the service layer
Use PreparedStatement object
Package cn. test. jdbc. dao;
Import java. SQL. Connection;
Import java. SQL. ResultSet;
Import java. SQL. SQLException;
Import java. SQL. Statement;
Import java. util. Date;
Import java. util. Properties;
Import com. mysql. jdbc. Driver;
Public class JDBCDemo {
// Write the url used to connect to the database
// The main jdbc protocol. mysql is a sub-protocol.
// Jdbc Protocol: Database sub-Protocol: // host: Port Number:/Database Name
Private String url = "jdbc: mysql: // localhost: 3306/day15 ";
// User Name
String username = "root ";
// Password
String password = "Name-66437 ";
@ Test
Public void main (String [] args) throws SQLException {
// 1. Create a Driver Class Object
Driver driver = new com. mysql. jdbc. Driver ();
// Set the user name and password
Properties props = new Properties ();
Props. setProperty ("username", username );
Props. setProperty ("password", password );
// 2. Connect to the database
Connection conn = driver. connect (url, props );
Statement stmt = conn. createStatement ();
String SQL = "select * from student ";
ResultSet rs = stmt.exe cuteQuery (SQL );
// 7. processing result set
System. out. println ("id | name | password | email | birthday ");
While (rs. next ()){
// Has the first line
Int id = rs. getInt ("id"); // The value of the column name is intuitive.
String name = rs. getString ("name ");
String psw = rs. getString ("password ");
String email = rs. getString ("email ");
Date birthday = rs. getDate ("birthday ");
System. out. println (id + "|" + name + "|" + psw + "|" + email + "|" + birthday );
}
// Code for resource release
If (rs! = Null ){
Try {
Rs. close ();
} Catch (SQLException e ){
E. printStackTrace ();
}
Rs = null;
}
If (stmt! = Null ){
Try {
Stmt. close ();
} Catch (SQLException e ){
E. printStackTrace ();
}
Stmt = null;
}
If (conn! = Null ){
Try {
Conn. close ();
} Catch (SQLException e ){
E. printStackTrace ();
}
Conn = null;
}
}
}
