day28
Today's content
- Basic concepts of JDBC
- quick get start
- A detailed explanation of interfaces and classes in JDBC
JDBC
- Concept: Java Database Connectivity (JDBC) is an application program interface in the Java language used to standardize how client programs access the database, and provides methods such as querying and updating data in the database. JDBC is also a trademark of Sun Microsystems. We usually say JDBC is oriented to relational database.
The essence of JDBC: 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 the implementation class that drives the jar package.
- Quick start:
Steps:
1.Import driver jar package mysql-connector-java-5.1.37-bin.jar
1.copy mysql-connector-java-5.1.37-bin.jar To project libs Under the directory
2.Right click-->Add As Library
2.Register driver
3.Get database connection object Connection
4.definition sql
5.Get execution sql Statement object Statement
6.implement sql，Accept returned results
7.Processing results
8.Release resources
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class JdbcDemo01 {
public static void main(String[] args) throws Exception {
// 1. Import the driver jar package
// 2. Register driver
Class.forName("com.mysql.jdbc.Driver");
// 3. Get the database Connection object Connection
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3", "root", "root");
// 4. Define sql
String sql = "update account set balance = 500 where id = 1";
// 5. Get the object Statement that executes the sql Statement
Statement statement = conn.createStatement();
// 6. Execute sql and accept the returned results
int count = statement.executeUpdate(sql);
// 7. Treatment results
System.out.println(count);
// 8. Release resources
statement.close();
conn.close();
}
}
- Explain each object in detail:
- DriverManager: drive management object, class
- Function:
- 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 viewing the source code, it is found that: com.mysql.jdbc.Driver Static code block in class
static {
try {
DriverManager.registerDriver(new Driver());
} catch (SQLException var1) {
throw new RuntimeException("Can't register driver!");
}
}
be careful: mysql 5 Subsequent drive jar The package can omit the step of registering the driver
Class.forName("com.mysql.jdbc.Driver");
- Get database connection
method: static Connection getConnection(String url, String user, String password) Try to build a database with the given URL Connection of. Parameters: url:Specify the path of the connection Syntax: jdbc:mysql://ip address (domain name): port number / database name example: jdbc:mysql://localhost:3306/db3 Details: if the connection is local mysql Server, and mysql The default service port is 3306, then url It can be abbreviated as: jdbc:mysql:///Database name user:user name password:password
- Connection: database connection object, interface
- Function:
1. Get execution sql Object of Statement createStatement()Create a Statement Object that lets you SQL Statement to the database. PreparedStatement prepareStatement(String sql)Create a PreparedStatement Object to parameterize SQL Statement to the database. 2. Management services: Open transaction: void setAutoCommit(boolean autoCommit)Set the auto submit mode of this connection to the given state. Call this method to set the parameter to false，The transaction is started Commit transaction: void commit()Make since last submission/All changes made since the rollback will be permanent and will be freed Connection Object currently holds any database locks. Rollback transaction: void rollback()Undo all changes made in the current transaction and release the Connection Object currently holds any database locks.
- Statement: the object and interface used to execute static SQL statements and return the generated results
- Execute sql
boolean execute(String sql)Execute the given SQL Statement, which may return multiple results. (understand)
Return value:
true Indicates that the first result is a ResultSet object;
false Indicates an update count or no results.
int executeUpdate(String sql)Execute the given SQL sentence(DML)，This could be INSERT,UPDATE,or DELETE Statement, or return nothing, such as DDL Declarative SQL sentence(create,alter,drop).
Return value:
(1)Return value>0 express sql How many rows of records are affected by the statement can be judged by the number of rows affected DML Whether the statement is executed successfully, return value>0 If yes, the execution is successful, otherwise, it fails.
(2)0 Not returned SQL Statement, such as DDL sentence
ResultSet executeQuery(String sql)Execute the given SQL Statement that returns a single ResultSet Object.
One ResultSet Object that contains the data generated by a given query; never null
- practice
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/*
account Add a record insert statement to the table
*/
public class JDBCDemo02 {
public static void main(String[] args) {
Statement statement = null;
Connection connection = null;
try {
//1. Register driver
Class.forName("com.mysql.jdbc.Driver");
//2. Define sql
String sql = "insert into account values(null, 'Wang Wu', 3000)";//id is automatically increased and null is a placeholder
//Execute DDL statements, such as creating a table
//String sql = "create table student (id int, name varchar(20))";
//3. Get the Connection object
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3", "root", "root");
//4. Get the statement of the object executing SQL
statement = connection.createStatement();
//5. Execute sql
int count = statement.executeUpdate(sql);
//Process print results
System.out.println(count);
if (count > 0){
System.out.println("Added successfully");
}else{
System.out.println("Add failed");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
//statement.close();
//Avoid null pointer exceptions
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
- Resultset: structure set object, interface, which represents the data table of the database result set. It is usually generated by executing the statement of querying the database.
boolean next()Move the cursor forward one line from the current position.
Return value:
true Indicates that the new current line is valid;
false Indicates that there are no more rows;
getXxx(parameter)get data
Xxx: Representative data types, such as: int getInt() ,String getString()
Parameters:
1. int:Represents the number of the column, starting from 1, such as: getString(1)
2. String:Name of representative column, such as: getDouble("balance")
be careful:
Use steps:
1. Move cursor down one line
2. Judge whether there is data
3. get data
//6.1 loop to determine whether the cursor is at the end of the last line
while (resultSet.next()){
//
//6.2 data acquisition
int id = resultSet.getInt(1);
String name = resultSet.getString("name");
double balance = resultSet.getDouble(3);
System.out.println(id + "------" + name + "------" + balance);
}
- practice
/*
Define a method to query the data of emp table, encapsulate it as an object, then load the collection and return
1. Define emp class
2. Define method public list < EMP > findall() {}
3. Implementation method select * from emp;
*/
package cn.itcast.domain;
import java.util.Date;
/*
javaBean encapsulating Emp table data
*/
public class Emp {
private int id;
private String name;
private int job_id;
private int mgr;
private Date joindate;
private double salary;
private double bonus;
private int dept_id;
public Emp() {
}
public Emp(int id, String name, int job_id, int mgr, Date joindate, double salary, double bonus, int dept_id) {
this.id = id;
this.name = name;
this.job_id = job_id;
this.mgr = mgr;
this.joindate = joindate;
this.salary = salary;
this.bonus = bonus;
this.dept_id = dept_id;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getJob_id() {
return job_id;
}
public void setJob_id(int job_id) {
this.job_id = job_id;
}
public int getMgr() {
return mgr;
}
public void setMgr(int mgr) {
this.mgr = mgr;
}
public Date getJoindate() {
return joindate;
}
public void setJoindate(Date joindate) {
this.joindate = joindate;
}
public double getSalary() {
return salary;
}
public void setSalary(double salary) {
this.salary = salary;
}
public double getBonus() {
return bonus;
}
public void setBonus(double bonus) {
this.bonus = bonus;
}
public int getDept_id() {
return dept_id;
}
public void setDept_id(int dept_id) {
this.dept_id = dept_id;
}
@Override
public String toString() {
return "Emp{" +
"id=" + id +
", name='" + name + '\'' +
", job_id=" + job_id +
", mgr=" + mgr +
", joindate=" + joindate +
", salary=" + salary +
", bonus=" + bonus +
", dept_id=" + dept_id +
'}';
}
}
package cn.itcast.jdbc;
import cn.itcast.domain.Emp;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/*
Define a method to query the data of emp table, encapsulate it as an object, then load the collection and return.
*/
public class JDBCDemo08 {
public static void main(String[] args) {
List<Emp> all = new JDBCDemo08().findAll();
System.out.println(all);
System.out.println(all.size());
}
/**
* Query all emp objects
* @return
*/
public List<Emp> findAll(){
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
List<Emp> list = null;
try {
//1. Register driver
Class.forName("com.mysql.jdbc.Driver");
//2. Get connection
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3","root","root");
//3. Define sql
String sql = "select * from emp";
//4. Get the object executing sql
statement = connection.createStatement();
//5. Execute sql
resultSet = statement.executeQuery(sql);
//6. Traverse the structure set, encapsulate the object, and load the set
Emp emp = null;
list = new ArrayList<>();
while (resultSet.next()){
int id = resultSet.getInt("id");
String ename = resultSet.getString("ename");
int job_id = resultSet.getInt("job_id");
int mgr = resultSet.getInt("mgr");
Date joindate = resultSet.getDate("joindate");
double salary = resultSet.getDouble("salary");
double bonus = resultSet.getDouble("bonus");
int dept_id = resultSet.getInt("dept_id");
//Create an emp object and assign a value
emp = new Emp(id, ename, job_id, mgr, joindate, salary, bonus, dept_id);
list.add(emp);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (resultSet != null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return list;
}
}
- practice:
Requirements:
1.Enter the user name and password through the keyboard
2.Judge whether the user logs in successfully
-- Create database
CREATE DATABASE db4;
USE db4;
CREATE TABLE USER(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(32),
PASSWORD VARCHAR(32)
);
INSERT INTO USER VALUE
(NULL, 'zhangsan', '123'),
(NULL, 'lisi', '234');
package cn.itcast.jdbc;
import cn.itcast.util.JDBCUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
/*
practice:
Requirements:
1.Enter the user name and password through the keyboard
2.Judge whether the user logs in successfully
*/
public class JDBCDemo09 {
public static void main(String[] args) {
//1. Keyboard entry, accept user name and password
Scanner sc = new Scanner(System.in);
System.out.println("enter one user name:");
String username = sc.nextLine();
System.out.println("Please input a password:");
String password = sc.nextLine();
//2. Call method
boolean flag = new JDBCDemo09().login(username, password);
//3. Judge the result and output different statements
if(flag){
System.out.println("Login succeeded!");
}else{
System.out.println("Wrong user name or password!");
}
}
/**
* Login method
*/
public boolean login(String username, String password){
if (username == null || password == null){
return false;
}
//Connect to the database to determine whether the login is successful
Connection conn = null;
Statement statement = null;
ResultSet resultSet = null;
try {
//1. Get connection
conn = JDBCUtils.getConnection();
//2. Define sql
String sql = "select * from user where username = '" + username + "'and password = '" + password + "'";
//3. Get the object executing sql
statement = conn.createStatement();
//Execute query
resultSet = statement.executeQuery(sql);
//5. Judgment
return resultSet.next();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.close(resultSet, statement, conn);
}
return false;
}
}
- PreparedStatement: the object that executes SQL, the interface, and the object that represents the precompiled SQL statement.
* SQL Injection problem: in splicing sql When, there are some sql Special keywords of participate in string splicing. It will cause security problems
1. Enter the user name and password: a' or 'a' = 'a
2. sql : select * from user where username = 'fhdsjkf' and password = 'a' or 'a' = 'a'
* solve sql Injection problem: Using PreparedStatement Object to solve
* Precompiled sql: Parameter use?As placeholder
* Steps:
1.Import driver jar package mysql-connector-java-5.1.37-bin.jar
1.copy mysql-connector-java-5.1.37-bin.jar To project libs Under the directory
2.Right click-->Add As Library
2.Register driver
3.Get database connection object Connection
4.definition sql
* be careful: sql Parameter usage of?As a placeholder. For example: select * from user where username = ? and password = ?
5.Get execution sql Statement object PreparedStatement
PreparedStatement prepareStatement(String sql)
Create a PreparedStatement Object to parameterize SQL Statement to the database.
6.to?Assignment:
method: setXxx(Parameter 1, Parameter 2)
* Parameter 1: ?Position number of the, starting from 1
* Parameter 2:?Value of
7.implement sql，Accept the returned result without passing sql sentence
8.Processing results
9.Release resources
* Note: it will be used later Preparedstatement To complete all operations of adding, deleting, modifying and querying
1.Can prevent SQL injection
2.More efficient
/**
* Login method: implemented using PreparedStatement
*/
public boolean login2(String username, String password){
if (username == null || password == null){
return false;
}
//Connect to the database to determine whether the login is successful
Connection conn = null;
ResultSet resultSet = null;
PreparedStatement preparedStatement = null;
try {
//1. Get connection
conn = JDBCUtils.getConnection();
//2. Define sql
String sql = "select * from user where username = ? and password = ?";
//3. Get the object executing sql
preparedStatement = conn.prepareStatement(sql);
//Here? assignment
preparedStatement.setString(1, username);
preparedStatement.setString(2, password);
//4. There is no need to pass sql to execute the query
resultSet = preparedStatement.executeQuery();
//5. Judgment
return resultSet.next();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.close(resultSet, preparedStatement, conn);
}
return false;
}
Extract JDBC tool class: JDBC utils
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 want to ensure the universality of tool classes. Resolution: Profile jdbc_properties url = user = password 3. Extract a method to release resources
url = jdbc:mysql://localhost:3306/db3 user = root password = root driver = com.mysql.jdbc.Driver
package cn.itcast.util;
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 to get these values, using static code blocks
*
*/
static {
//Read the resource file and get the value
try {
//1. Create Properties collection class
Properties properties = new Properties();
//Get the path of classloader -- >
ClassLoader classLoader = JDBCUtils.class.getClassLoader();
URL res = classLoader.getResource("jdbc_properties");
String path = res.getPath();
//2. Load file
//properties.load(new FileReader("E:\\Java\\JavaWorkSpace\\JDBC\\day01_jdbc\\src\\jdbc_properties"));
properties.load(new FileReader(path));
//3. Obtain data and assign value
url = properties.getProperty("url");
user = properties.getProperty("user");
password = properties.getProperty("password");
driver = properties.getProperty("driver");
//4. Register driver
Class.forName(driver);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* Get connection object
* @return Connection object
*/
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, user, password);
}
/**
* Release resources
* @param statement
* @param connection
*/
public static void close(Statement statement, Connection connection){
if (statement != null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* Release resources
* @param statement
* @param connection
* @param resultSet
*/
public static void close(ResultSet resultSet, Statement statement, Connection connection){
if (resultSet != null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
JDBC control transaction:
- Transaction: a business operation with multiple steps. If this business operation is managed by things, these multiple steps will either succeed or fail at the same time
- Operation:
1. Start transaction
2. Submission of services
3. Rollback transaction - Use the Connection object to manage transactions
- Start transaction: void setAutoCommit(boolean autoCommit) sets the auto commit mode of this connection to the given state. Call this method and set the parameter to false to start the transaction
- Start transaction before executing sql
- Commit transaction: void commit() makes all changes made since the last commit / rollback permanent and releases any database locks currently held by this Connection object.
- When all sql has completed the commit transaction
- Rollback transaction: void rollback() undoes all changes made in the current transaction and releases any database locks currently held by this Connection object.
- Rolling back transactions in catch
- Start transaction: void setAutoCommit(boolean autoCommit) sets the auto commit mode of this connection to the given state. Call this method and set the parameter to false to start the transaction
import cn.itcast.util.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/*
Transaction operation
*/
public class JDBCDemo10 {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement1 = null;
PreparedStatement preparedStatement2 = null;
try {
//1. Get connection
connection = JDBCUtils.getConnection();
// Open transaction
connection.setAutoCommit(false);
//2. Define sql
//2.1 sheet 3 - 500
String sql1 = "update account set balance = balance - ? where id = ?";
//2.2 Li Si + 500
String sql2 = "update account set balance = balance + ? where id = ?";
//3. Get and execute sql objects
preparedStatement1 = connection.prepareStatement(sql1);
preparedStatement2 = connection.prepareStatement(sql2);
//4. Set parameters
preparedStatement1.setDouble(1, 500);
preparedStatement1.setInt(2, 1);
preparedStatement2.setDouble(1, 500);
preparedStatement2.setInt(2, 2);
//5. Execute sql
preparedStatement1.executeUpdate();
//Manual manufacturing exception
int i = 3/0;
preparedStatement2.executeUpdate();
connection.commit();
} catch (Exception e) {
//Transaction rollback
try {
if (connection != null)
connection.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
} finally {
JDBCUtils.close(preparedStatement1,connection);
JDBCUtils.close(preparedStatement2,null);
}
}
}