MySQL Introductory learning summary for JDBC

Turn from: Focus on Javaweb development http://www.javaweb1024.com/data/MySQL/2015/04/25/618.html

First, the basic concept of JDBC
Jdbc:java Database Connectivity
In order to unify the operation of the database, Sun has defined a set of APIs, called the JDBC
This set of APIs consists entirely of interfaces, and we make calls to the interface when we write the program.
These interfaces to the database manufacturers to achieve, different database factory Chamber of Commerce to provide different implementation classes,
These implementation classes are driven by what we call the database.

First-class company set standards
Second-rate companies do services
Three-stream company to do products

The advantages of database-driven database vendors:
1.Java developers only need to maintain Java applications and a uniform set of specifications
2. The database vendor provides specific Java drivers, and if the database vendor changes the
Database implementation of the underlying, the database vendor to update the driver, will not affect the
Java applications that have been completed.

The simple technique of using Java code to send SQL statements is the JDBC

Ii. conditions for implementing the JDBC Program
1. Need to know the location of the connection database server
Requires the IP address and port number of the database
IP location to host
Port number: Navigate to Program
2. Need to know the user name and password of the database

Third, how to implement the JDBC connection MySQL database?
1. Importing a database driver
Mysql-connector-java-5.0.8-bin.jar
2. Registering the database driver
Drivermanager.registerdriver (New Com.mysql.jdbc.Driver ());
3. Establish a connection to the MySQL database
String url = "Jdbc:mysql://localhost:3306/test";//jdbc is the Master protocol, 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. Writing SQL statements
String sql = "SELECT * from users";
6. Send SQL to get result set
ResultSet rs = stmt.executequery (SQL);
7. Working with result sets
SYSTEM.OUT.PRINTLN ("id | name | password | email | Birthday ");
while (Rs.next ()) {
Have the first line
int id = rs.getint ("id"); Intuitive value by column name
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 Open resource is released first, and you need to determine if the resource has been disconnected before releasing
Rs.close ();
Stmt.close ();
Conn.close ();
Iv. detailed JDBC Program
1. Registration Driver
Drivermanager.registerdriver (New Com.mysql.jdbc.Driver ());
The above statement will result in the registration of two drives
The reason is that viewing the source code of the driver class will find that the registration-driven work is done in a static block of code,
In other words, the registration driver is very simple, only need to load the driver class can
Class.forName ("Com.mysql.jdbc.Driver");
2. Creating a connection to a database
Connection conn = drivermanager.getconnection (URL, user, password);
which

URL, equivalent to the access address of the database, the programmer specifies the database to be accessed via the URL

URL of the connection database
JDBC Master protocol, MySQL is a sub-protocol
JDBC Protocol: Database Sub-protocol://HOST: Port number:/Database name

Jdbc:mysql:[]//localhost:3306/test? Parameter name: value of argument
Where JDBC is the primary protocol, MySQL is the sub-protocol, localhost is the host name, 3306 is the port number, test is the database name
After the URL can be followed by parameters, commonly used parameters are: User=root&password=root&characterencoding=utf-8

If the URL address is followed by user and password, creating the connection object will not have to pass in the value again
Connection conn = drivermanager.getconnection (URL);

Supplemental Note: If the localhost:3306,url accessed can be saved as Jdbc:mysql:///test
3. Connection objects
The connection object is used to represent a connection to a database, and all operations on the database need to be done through this object.
Common methods are:
Createstatement (): Creates a statement object that sends SQL to the database.
Preparestatement (SQL): Creates a Preparesatement object that sends precompiled SQL to the database.
Preparecall (SQL): Creates the CallableStatement object that executes the stored procedure.
Setautocommit (Boolean autocommit): Sets whether the transaction is automatically committed.
Commit (): commits the transaction on the link.
Rollback (): Rolls back the transaction on this link.

4. Statement objects
Used to send SQL statements to the 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): Insert, UPDATE, or DELETE statements can only be sent to the database (common)
Addbatch (String sql): puts multiple SQL statements into one batch.
ExecuteBatch (): Sends a batch of SQL statement execution to the database.

5. ResultSet Objects
For query operations, this object is particularly important because it is designed to encapsulate the result set
The form of storage is a form of a table, the same column + row, which is just like the result of our query in the DOS command line window

Traversal mode:
Start cursor points to the first row of the result set, which is the table header
Move the cursor down one line through next, and the method returns False if there is no next line
Getting the data for the current row requires calling the Get method:
Get (int index) Gets the number of column columns starting from 1
Get (String columnName) gets values based on column name commonly used

Note: The data type of the database corresponds to the data type in Java

Common methods for ResultSet objects
Next (): Move to the next line
Previous (): Move to previous line
Absolute (int row): Move to the specified line
Beforefirst (): Moves the front of the resultset.
Afterlast (): Moves to the last face of the resultset.
6. Releasing Database resources
Because the number of concurrent access connections that the data allows is often limited, the resource needs to be freed after use is complete

In a Java program, we should put the final code that must be executed in the finally
Code to release resources
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. Preventing SQL injection
Logical judgment at the service level
Using the 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 {
The URL of the connection database is spelled
JDBC Master 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. Creating a Class object for a driver
Driver Driver = new Com.mysql.jdbc.Driver ();
Set 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.executequery (SQL);
7. Working with result sets
SYSTEM.OUT.PRINTLN ("id | name | password | email | Birthday ");
while (Rs.next ()) {
Have the first line
int id = rs.getint ("id"); Intuitive value by column name
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 to release resources
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;
}
}
}

MySQL Introductory learning summary for JDBC