1. Concept: Java database connectivity, Java database connection, Java language operation database
JDBC essence: it is actually a set of rules defined by the official (sun company) to operate all relational databases, that is, interfaces. Each database manufacturer implements this interface and provides database driver jar package. We can use this set of interfaces (JDBC) to program, and the real executed code is to drive the implementation classes in the jar package
2. Steps: (there are improvements later)
1. Import driver jar package
2. Register driver
3. Get database Connection object Connection
4. Define sql
5. Gets the object Statement that executes the sql Statement
6. Execute sql and receive the returned object
7. Processing results
8. Release resources
Implementation code:
//1. Import the driver jar package
//2. Register driver
Class.forName("com.mysql.jdbc.Driver");
//3. Get database connection object
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1","root","root");
//4. Define sql statements
String sql = "update account set balance = 1500 where id = 1";
//5. Get the object Statement executing sql
Statement stmt = conn.createStatement();
//6. Execute sql
int count = stmt.executeUpdate(sql);
//7. Treatment results
System.out.println(count);
//8. Release resources
stmt.close();
conn.close();3. Explain each object in detail:
1.DriverManager: drive management object
Function:
1.1. Register driver: tell the program which database driver jar to use
static void registerDriver(Driver driver): register with the given driver DriverManager
Write code using: class forName("com.mysql.jdbc.Driver");
By looking at the source code, I found that: on COM mysql. jdbc. Static code block in driver class
static{
try{
java.sql.DriverManager.registerDriver(new Driver());
}catch (SQLException E){
throw new RuntimeException("Can't register driver");
}
}Note: the driver jar package after mysql5 can omit the steps of registering the driver.
1.2. Get database connection:
Method: static Connection getConnection(String url,String user,String password)
Parameters:
url: specify the connection path
Syntax: jdbc:mysql://ip Address (domain name): port number / database name
Details: if the local mysql server is connected and the default port number of the mysql server is 3306, the url can be abbreviated as: jdbc:mysql: / / / database name
User: user name
Password: password
2.Connection: database connection object
2.1. Get the object executing sql
Statement createStatement()
Preparedstatement preparedstatement(String sql)
2.2. Management services:
Start transaction: setAutoCommit(boolean autoCommit): call this method and set the parameter to false, that is, start the transaction
Commit transaction: commit()
Rollback transaction: rollback()
3.Statement: object to execute sql
3.1. Execute sql
1.boolean execute(String sql): arbitrary sql can be executed
2.int executeupdate(String sql): execute DML(insert,update,delete) statements and DDL(create,alter,drop) statements
* return value: the number of rows affected. You can judge whether the DML statement is executed successfully by the number of rows affected. If the return value > 0, the execution is successful, otherwise, it fails
3.ResultSet executeQuery(String sql): executes DQL(select) statements
Exercise: adding a message to the database
/**
* jdbc Exercise to add information to the database
*/
public class JdbcDemo2 {
public static void main(String[] args) {
Connection conn =null;
Statement stat = null;
int num = 0;
try {
Class.forName("com.mysql.jdbc.Driver");
String sql = "insert into account value(4,1000)";
conn = DriverManager.getConnection("jdbc:mysql:///db1", "root", "root");
stat = conn.createStatement();
num = stat.executeUpdate(sql);
System.out.println(num);
if(num>0){
System.out.println("Added successfully");
}else{
System.out.println("Add failed");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
if (stat != null) {
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
Exercise: querying data in a table
/**
* jdbc Exercise, query the information in the table
*/
public class JdbcDemo3 {
public static void main(String[] args) {
Connection conn =null;
Statement stat = null;
ResultSet set = null;
try {
Class.forName("com.mysql.jdbc.Driver");
String sql = "select * from account";
conn = DriverManager.getConnection("jdbc:mysql:///db1", "root", "root");
stat = conn.createStatement();
set = stat.executeQuery(sql);
while(set.next()){
int id = set.getInt(1);
String balance = set.getString("balance");
System.out.println("Employee number:"+id+" "+"The salary of employees is:" + balance);
}
if(set !=null){
System.out.println("Search succeeded");
}else{
System.out.println("Search failed");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
if (stat != null) {
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}4.ResultSet: result set object} encapsulates query results
next(): cursor moves down one line
Getxxx (parameter): get data
* Xxx: represents the data type. For example: int getInt(), String getString()
* parameters:
1.int: represents the number of the column, starting from 1. For example: getString(1)
2.String: represents the name of the column. For example: getDouble("field name")
Note:
* use steps: 1 Move the cursor down one line {2 Judge whether there is data # 3 get data
5. Preparedstatement: the object that executes sql
1.SQL injection problem: when splicing sql, some special keywords of sql participate in string splicing. It will cause security problems
* enter the user's password: a 'or' a '=' a '
2. Solve the problem of sql injection: use the Preparedstatement object to solve it
3. Precompiled SQL: parameter usage? As placeholder
!!! 4. Steps:
1. Import driver jar package
2. Register driver
3. Get database Connection object Connection
4. Define sql
* Note: the sql parameters are used in the following way:? As a placeholder.
For example: select * from user where username =? and password = ?;
5. Get the object perforedstatement connection. That executes the SQL statement prepareStatement(String sql)
6. Here? Assignment:
Method: setXXX (parameter 1, parameter 2)
Parameter 1:? The position number of starts from 1
Parameter 2:? Value of
7. Execute sql and receive the returned results without passing sql statements
8. Processing results
9. Release resources
4. Extract JDBC tool class
Objective: to simplify writing
Analysis:
1. Registered drivers are also extracted
2. Extract a method to obtain the connection object
* requirements: do not want to pass parameters, but also ensure the universality of tool classes
* solution: Profile
jdbc.properties
url= user= password=
3. Extract a method to release resources
Exercise: use JDBC tools to simplify the query function
1. Create a file. The data in the file is as follows:
url=jdbc:mysql:///db1 user=root password=root driver=com.mysql.jdbc.driver
2. Create JDBC tool class
package cn.itcast.jdbc.utils;
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;
/**
* JDBC Tool class
*/
public class JDBCUtils {
private static String url;
private static String user;
private static String password;
private static String driver;
/**
* The file can be read only once, using static code blocks
*/
static{
try {
//Read the resource file and get the value
//1. Create the properties collection class
Properties pro = new Properties();
//How to get the file under src path ----- > classloader classloader
ClassLoader classLoader = JDBCUtils.class.getClassLoader();
URL resource = classLoader.getResource("jdbc.properties");
String path = resource.getPath();
System.out.println(path);
//2. Load file
pro.load(new FileReader(path));
//3. Obtain data and assign value
url = pro.getProperty("url");
user = pro.getProperty("user");
password = pro.getProperty("password");
driver = pro.getProperty("driver");
//4. Register driver
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* Get connection
* @return Connection object
*/
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,user,password);
}
/**
* Release resources
* @param stat
* @param conn
*/
public static void close(ResultSet set,Statement stat, Connection conn){
if(stat !=null)
{
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn !=null)
{
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(set !=null)
{
try {
set.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
3. Improved class code
package cn.itcast.jdbc;
import cn.itcast.jdbc.utils.JDBCUtils;
import java.sql.*;
/**
* jdbc Exercise to demonstrate JDBC tool class
*/
public class JdbcDemo4 {
public static void main(String[] args) {
Connection conn =null;
Statement stat = null;
ResultSet set = null;
try {
//Class.forName("com.mysql.jdbc.Driver");
String sql = "select * from account";
// conn = DriverManager.getConnection("jdbc:mysql:///db1", "root", "root");
conn = JDBCUtils.getConnection();
stat = conn.createStatement();
set = stat.executeQuery(sql);
while(set.next()){
int id = set.getInt(1);
String balance = set.getString("balance");
System.out.println("Employee number:"+id+" "+"The salary of employees is:" + balance);
}
if(set !=null){
System.out.println("Search succeeded");
}else{
System.out.println("Search failed");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
// if (stat != null) {
// try {
// stat.close();
// } catch (SQLException e) {
// e.printStackTrace();
// }
// }
// if (conn != null) {
// try {
// conn.close();
// } catch (SQLException e) {
// e.printStackTrace();
// }
JDBCUtils.close(set,stat,conn);
}
}
}
Exercise: implement a simple login function
Requirements: enter the user name and password through the keyboard, and {judge whether the user logs in successfully
/**
* Requirements: enter the user name and password through the keyboard to judge whether the user has successfully logged in
* Problem: there is a SQL injection problem
*/
public class JdbcDemo5 {
public static void main(String[] args) {
Scanner scanner = new Scanner(System.in);
System.out.println("Please enter your name:");
String name = scanner.nextLine();
System.out.println("Please input a password:");
String password = scanner.nextLine();
boolean flag = new JdbcDemo5().login(name, password);
if(flag ==true){
System.out.println("Login successful");
}else{
System.out.println("Login failed");
}
}
public boolean login(String name,String password){
ResultSet res = null;
Connection conn = null;
Statement stat = null;
if(name == null || password ==null)
return false;
try {
conn = JDBCUtils.getConnection();
String sql = "select * from user where name='"+name+"' and password='"+password+"'";
stat = conn.createStatement();
res = stat.executeQuery(sql);
return res.next();
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.close(res,stat,conn);
}
return false;
}
}
/**
* Requirements: enter the user name and password through the keyboard to judge whether the user has successfully logged in
* Note: solve the SQL injection problem
*/
public class JdbcDemo6 {
public static void main(String[] args) {
Scanner scanner = new Scanner(System.in);
System.out.println("Please enter your name:");
String name = scanner.nextLine();
System.out.println("Please input a password:");
String password = scanner.nextLine();
boolean flag = new JdbcDemo6().login(name, password);
if(flag ==true){
System.out.println("Login successful");
}else{
System.out.println("Login failed");
}
}
public boolean login(String name,String password){
ResultSet res = null;
Connection conn = null;
PreparedStatement pstat = null;
if(name == null || password ==null)
return false;
try {
conn = JDBCUtils.getConnection();
String sql = "select * from user where name= ? and password= ?";
pstat = conn.prepareStatement(sql);
pstat.setString(1,name);
pstat.setString(2,password);
res = pstat.executeQuery();
return res.next();
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.close(res,pstat,conn);
}
return false;
}
}
5.JDBC transaction
1. Transaction: a business operation with multiple steps. If the business operation is managed by a transaction, the multiple steps will either succeed or fail at the same time.
2. Operation:
1. Open transaction
2. Commit transaction
3. Rollback transaction
3. Use the Connection object to manage transactions
* start transaction: setAutoCommit(boolean autoCommit): call this method and set the parameter to false, that is, start the transaction
* * start transaction before executing sql
* commit transaction: commit()
* * when all sql has completed the commit transaction
* rollback transaction: rollback()
* * rollback transaction in catch
Exercise: do a simple transfer function
/**
* jdbc Using transactions, a simple transfer
*/
public class jdbcDemo7 {
public static void main(String[] args) {
PreparedStatement pstat1 = null;
PreparedStatement pstat2 = null;
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
conn.setAutoCommit(false);//Open transaction
String sql1="update accounts set balance = balance - ? where id = ?";
String sql2="update accounts set balance = balance + ? where id = ?";
pstat1 = conn.prepareStatement(sql1);
pstat2 = conn.prepareStatement(sql2);
pstat1.setInt(1,500);
pstat1.setInt(2,1);
pstat2.setInt(1,500);
pstat2.setInt(2,2);
pstat1.executeUpdate();
//Manufacturing error
int i = 3/0;
pstat2.executeUpdate();
conn.commit();//Commit transaction
} catch (Exception e) {
try {
conn.rollback();//Rollback transaction
} catch (SQLException ex) {
ex.printStackTrace();
}
e.printStackTrace();
}finally {
JDBCUtils.close(conn,pstat1);
JDBCUtils.close(null,pstat2);
}
}
}