## JDBC:
1. Concept: Java DataBase Connectivity, Java language operation database
* essence of JDBC: in fact, it is a set of rules defined by the official (sun company) to operate all relational databases, that is, interfaces. Each database manufacturer will implement the socket and provide the database driver jar package. We can use this set of interface (JDBC) programming, the real code is to drive the implementation class in the jar package.
2. Quick start:
* step:
1. Import the driver jar package mysql-connector-java-5.1.37-bin.jar
1. Copy mysql-connector-java-5.1.37-bin.jar to the libs directory of the project
2. Right click > Add as library
jar package link: https://pan.baidu.com/s/1L-aiZ263j6L1LAG9P_qZKw
Extraction code: 0sqe
2. Registration driver
3. Get database Connection object
4. Define sql
5. Get the Statement of the object executing the sql Statement
6. Execute sql and accept the returned results
7. Treatment results
8. Release resources
* code implementation:
package cn.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
/*
* Jdbc quick get start
*/
public class Jdbcdemo1 {
public static void main(String[] args) throws Exception {
//1. Import the driver jar package
//2. Registration driver
Class.forName("com.mysql.jdbc.Driver");
//3. Get database connection object
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db2","root","123456");
//4. Define sql statement
String sql = "update account set balance = 100 where id = 1";
//5. Get the Statement of the executed sql object
Statement stmt = conn.createStatement();
//6. implementation of 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. Register driver: tell the program which database to use to drive jar
static void registerDriver(Driver driver): registers with the given driver DriverManager.
Write code using: Class.forName("com.mysql.jdbc.Driver");
By looking at the source code, we found that there are static code blocks in the com.mysql.jdbc.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 driver.
2. Get database connection:
* method: static Connection getConnection(String url, String user, String password)
* 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 you are connected to the local mysql server and the default port of mysql service is 3306, the url can be abbreviated as: jdbc:mysql: / / / database name
* user: user name
* password: password
2. Connection: database connection object
1. Function:
1. Get the object to execute sql
* Statement createStatement()
* PreparedStatement prepareStatement(String sql)
2. Management:
* start transaction: setAutoCommit(boolean autoCommit): call this method to set the parameter to false, that is, start transaction
* commit transaction: commit()
* rollback transaction: rollback()
3. Statement: the object to execute sql
1. Execute sql
1. boolean execute(String sql): can execute any sql to understand
2. int executeUpdate(String sql): execute DML (insert, update, delete) statements, 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 is > 0, it is executed successfully. Otherwise, it is failed.
3. ResultSet executeQuery(String sql): execute DQL (select) statement
2. Exercise:
1. Add a record to account table
2. account table modification record
3. Delete a record in account table
Code:
package cn.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/*
* account Table add a record insert statement
*/
public class Jdbcdemo2 {
public static void main(String[] args) {
Statement stmt = null;
Connection conn = null;
try {
//1. Registration driver
Class.forName("com.mysql.jdbc.Driver");
//2. definition of sql
String sql = "insert into account values(null,'Wang Wu',3000)";
//3. Get Connection object
conn = DriverManager.getConnection("jdbc:mysql:///db2", "root", "123456");
//4. Get the object to execute sql
stmt = conn.createStatement();
//5. implementation of sql
int count = stmt.executeUpdate(sql);//Rows affected
//6. Treatment results
System.out.println(count);
if(count > 0) {
System.out.println("Add success");
}else {
System.out.println("Add failure");
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
//7. Release resources
//Avoid null pointer exceptions
if(stmt!=null) {
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}
package cn.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/*
* account Table modify a record update statement
*/
public class Jdbcdemo3 {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try {
// 1. Registration driver
Class.forName("com.mysql.jdbc.Driver");
// 2. Get connection object
conn = DriverManager.getConnection("jdbc:mysql:///db2", "root", "123456");
// 3/ defines sql
String sql = "update account set balance = 1500 where id = 3";
// 4. Get the execution sql object
stmt = conn.createStatement();
// 5. implementation of sql
int count = stmt.executeUpdate(sql);
// 6. Treatment results
if (count > 0) {
System.out.println("Modified success");
} else {
System.out.println("Modification failed");
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
// 7. Release resources
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}
package cn.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/*
* account Table delete a record delete statement
*/
public class Jdbcdemo4 {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try {
// 1. Registration driver
Class.forName("com.mysql.jdbc.Driver");
// 2. Get connection object
conn = DriverManager.getConnection("jdbc:mysql:///db2", "root", "123456");
// 3/ defines sql
String sql = "delete from account where id = 3";
// 4. Get the execution sql object
stmt = conn.createStatement();
// 5. implementation of sql
int count = stmt.executeUpdate(sql);
// 6. Treatment results
if (count > 0) {
System.out.println("Delete successful");
} else {
System.out.println("Delete failed");
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
// 7. Release resources
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}
4. ResultSet: result set object, encapsulating query results
* boolean next(): move the cursor down one row to determine whether the current row is the end of the last row (whether there is data). If yes, return false, if not, return true
* getxxx (parameter): get data
* Xxx: represents the data type, such as: int getInt(), String getString()
* parameters:
1. int: the number of the column, starting from 1. For example: getString(1)
2. String: represents the column name. Such as: getDouble("balance")
* Note:
* procedure:
1. Move the cursor down one row
2. Judge whether there is data
3. Access to data
/ / loop to determine whether the cursor is the end of the last line.
while(rs.next()){
/ / get data
/ / 6.2 obtaining data
int id = rs.getInt(1);
String name = rs.getString("name");
double balance = rs.getDouble(3);
System.out.println(id + "---" + name + "---" + balance);
}
* exercise:
* define a method, query the data of emp table, encapsulate it as an object, load the collection, and return.
1. Define Emp class
2. Define the method public list < EMP > findall() {}
3. Implementation method select * from emp;
package cn.jdbc;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import cn.domain.Emp;
import cn.utils.JDBCUtils;
/*
* * Define a method to query the data of emp table and encapsulate it as an object, then load the collection and return.
*/
public class Jdbcdemo8 {
/**
* Query all emp objects
* @return
*/
public List<Emp> findAll(){
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
List<Emp> list = null;
try {
//1. Registration driver
Class.forName("com.mysql.jdbc.Driver");
//2. Get connection
conn = DriverManager.getConnection("jdbc:mysql:///db2", "root", "root");
//3. definition of sql
String sql = "select * from emp";
//4. Get the object to execute sql
stmt = conn.createStatement();
//5. implementation of sql
rs = stmt.executeQuery(sql);
//6. Traverse result set, encapsulate object, load set
Emp emp = null;
list = new ArrayList<Emp>();
while(rs.next()){
//get data
int id = rs.getInt("id");
String ename = rs.getString("ename");
int job_id = rs.getInt("job_id");
int mgr = rs.getInt("mgr");
Date joindate = rs.getDate("joindate");
double salary = rs.getDouble("salary");
double bonus = rs.getDouble("bonus");
int dept_id = rs.getInt("dept_id");
// Create emp object and assign
emp = new Emp();
emp.setId(id);
emp.setEname(ename);
emp.setJob_id(job_id);
emp.setMgr(mgr);
emp.setJoindate(joindate);
emp.setSalary(salary);
emp.setBonus(bonus);
emp.setDept_id(dept_id);
//Loading set
list.add(emp);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return list;
}
public static void main(String[] args) {
List<Emp> list = new Jdbcdemo8().findAll();
System.out.println(list);
}
}
5. PreparedStatement: the object to execute sql
1. SQL injection: when splicing SQL, some special keywords of SQL participate in string splicing. Can cause security problems
1. Input the user at will, and enter the password: a 'or' a '='a
2. sql: select * from user where username = 'fhdsjkf' and password = 'a' or 'a' = 'a'
2. Solve sql injection problem: use PreparedStatement object to solve
3. Precompiled SQL: use? As a placeholder for parameters
4. Steps:
1. Import the driver jar package mysql-connector-java-5.1.37-bin.jar
2. Registration driver
3. Get database Connection object
4. Define sql
* Note: how to use sql parameters? As a placeholder. For example: select * from user where username =? And password =?;
5. Get the PreparedStatement Connection.prepareStatement(String sql) that executes the sql statement
6. To? Assignment:
* method: setXXX (parameter 1, parameter 2)
* parameter 1:? The position number of starts from 1
* parameter 2:? Value
7. Execute sql, accept the returned results, and do not need to pass sql statements
8. Treatment results
9. Release resources
5. Note: PreparedStatement will be used later to complete all operations of adding, deleting, modifying and querying
1. Prevent SQL injection
2. Higher efficiency
package cn.jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
import cn.utils.JDBCUtils;
/**
* * Practice:
* Demand:
1. Enter user name and password through keyboard
2. Judge whether the user has successfully logged in
*
*/
public class Jdbcdemo9 {
/**
* The login method is implemented by PreparedStatement
* @return
*/
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;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
//1. Get connection
conn = JDBCUtils.getConnection();
//2. definition of sql
String sql = "select * from user where username = ? and password = ? ";
//3. Get the object to execute sql
pstmt = conn.prepareStatement(sql);
//4. assign value to
pstmt.setString(1, username);
pstmt.setString(2, password);
//5. Execute the query without passing parameters
rs = pstmt.executeQuery();
//6. judgement
return rs.next();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCUtils.close(rs, pstmt, conn);
}
return false;
}
public static void main(String[] args) {
//1. Enter by keyboard, 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 Jdbcdemo9().login2(username, password);
//3. Judge results and output different statements
if(flag) {
//Login successfully
System.out.println("Login successfully");
}else {
//Login failed
System.out.println("Wrong user name or password");
}
}
}
