廖雪峰Java15JDBC编程-3JDBC接口-2JDBC查询
我们可以使用JDBC查询来执行select语句。
1. Statement
try(Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)){
try(Statement stmt = conn.getStatement()){
try(ResultSet rs = stmt.executeQuery("select * from students where gender=\'M\'")){
while(rs.next()){
long id = rs.getLong(1); //注意：索引从1开始
long classId = rs.getLong(2);
String name = rs.getString(3);
String gender = rs.getString(4);
}
}
}
}
2. SQL注入：
使用Statement拼字符串很容易引发SQL注入的问题。这是因为SQL的参数往往是方法参数传入的。例如一个Login方法需要传入用户名、密码来查询对应的一行user的记录。
User login(String name, String pass){
...
stmt.executeQuery("select * from user where login ='"+name+"' and pass='"+pass+"'"); //select * from user where login = ' ' and pass = '';
...
}
如果用户输入的是程序期待的值，就可以拼出一个正确的SQL。
-- name="Bob", pass="1234"
select * from user where login = 'Bob' and pass = '1234';
但是如果用户输入的是一个精心构造的字符串，就可能拼出意想不到的SQL。这个SQL也是正确的，但它查询的条件并不是设定的意图。
```#sql -- name = "Bob' or pass=" , pass=" or pass=' " select * from user where login = 'Bob' or pass=' and pass=' or pass=''; ``` ## 3 PrepareStatement 为了避免SQL注入，我们要使用PreparedStatement ```#java User login(String name, String pass){ ... PreparedStatement ps = conn.prepareStatement("select * from user where login = ? and pass=?"); ps.setObject(1, name);//注意，索引从1开始 ps.setObject(2, pass); ... } ``` 始终使用？作为占位符，并把数据连同SQL本身传给数据库，使用PreparedStatement可以保证每次传给数据库的SQL语句都是相同的，只是占位符的数据不同，这样它可以高效利用数据库本身对查询的缓存。所以PreparedStatement比Statement更快更安全。 ### 3.1 如何使用PreparedStatement ```#java try(Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)){ try(PreparedStatment ps = conn.prepareStatement("select * from student where gender = ?")){ ps.setObject(1, "M"); try(ResultSet rs = ps.executeQuery()){ while(rs.next()){ long id = rs.getLong("id"); long classId = rs.getLong("class_id"); String name = rs.getString("name"); String gender = rs.getString("gender"); } } } } ``` ## 4 代码示例 ### 4.1 SQL文件init.sql ```#sql SET NAMES utf8mb4;-- init tables:
DROP TABLE IF EXISTS students;
DROP TABLE IF EXISTS classes;
-- 指定存储引擎是innodb引擎，表编码是utf8mb4
CREATE TABLE classes (
id BIGINT NOT NULL AUTO_INCREMENT,
name VARCHAR(10) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE students (
id BIGINT NOT NULL AUTO_INCREMENT,
class_id BIGINT NOT NULL,
name VARCHAR(10) NOT NULL,
gender CHAR(1) NOT NULL,
CONSTRAINT fk_class_id FOREIGN KEY (class_id) REFERENCES classes (id),
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- init data:
INSERT INTO classes (name) VALUES ('一班');
INSERT INTO classes (name) VALUES ('二班');
INSERT INTO classes (name) VALUES ('三班');
INSERT INTO classes (name) VALUES ('四班');
INSERT INTO students (class_id, name, gender) VALUES (1, '小明', 'M');
INSERT INTO students (class_id, name, gender) VALUES (1, '小红', 'F');
INSERT INTO students (class_id, name, gender) VALUES (1, '小军', 'M');
INSERT INTO students (class_id, name, gender) VALUES (2, '小白', 'F');
INSERT INTO students (class_id, name, gender) VALUES (2, '小兵', 'M');
INSERT INTO students (class_id, name, gender) VALUES (3, '小王', 'M');
INSERT INTO students (class_id, name, gender) VALUES (3, '小丽', 'F');
### 4.2 执行sql脚本
方法一：打开sh，执行
```#shell
#将init.sql脚本复制到容器内
docker cp init.sql 2202eecd49c9:/home/
docker exec -it csjmysql0828 sh;
mysql -u root -p test0828 < init.sql
方法二：通过Navicat执行：
选择数据库模式，如test0828，选择运行SQL文件
Student.java
package com.feiyangedu.sample.pop3;
public class Student {
public long id;
public long classId;
public String name;
public String gender;
public Student(){}
public Student(long id, long classId,String name,String gender){
this.id = id;
this.classId = classId;
this.name = name;
this.gender = gender;
}
public Student(long classId,String name,String gender){
this.classId = classId;
this.name = name;
this.gender = gender;
}
@Override
public String toString(){ //用于打印Student对象，如<Student id=7,class_id=3,name=小丽,gender=F>
return "<Student id="+id+",class_id="+classId+",name="+name+",gender="+gender+">";
}
}
4.3 获取Student内容JdbcSelect.java
package com.feiyangedu.sample.pop3;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class JdbcSelect {
static final String JDBC_URL="jdbc:mysql://localhost:13306/test0828?useSSL=false&characterEncoding=utf8&serverTimeZone=UTC";
static final String JDBC_USER="root";
static final String JDBC_PASSWORD="123456";
public static void main(String[] args) throws Exception{
List<Student> students = getAllStudent();
for(Student student:students){
System.out.println(student);
}
System.out.println( );
for(long i=1;i<=4;i++){
List<Student> list = getStudentsOfClass(i);
System.out.println("Students of class "+i+":");
for(Student student:list){
System.out.println(student);
}
}
}
static List<Student> getAllStudent() throws SQLException{
try(Connection conn = getConnection()){
try(PreparedStatement ps = conn.prepareStatement("select * from students")){
try(ResultSet rs = ps.executeQuery()){
List<Student> list = new ArrayList<>();
while(rs.next()){
long id = rs.getLong("id");
long classId = rs.getLong("class_id");
String name = rs.getString("name");
String gender = rs.getString("gender");
Student std = new Student(id,classId,name,gender);
list.add(std);
}
return list;
}
}
}
}
static List<Student> getStudentsOfClass(long thrClassId) throws SQLException{
try(Connection conn = getConnection()){
try(PreparedStatement ps = conn.prepareStatement("select * from students where class_id = ?")){
ps.setObject(1,thrClassId);
try(ResultSet rs = ps.executeQuery()){
List<Student> list = new ArrayList<>();
while(rs.next()){
long id = rs.getLong("id");
long classId = rs.getLong("class_id");
String name = rs.getString("name");;
String gender = rs.getString("gender");
Student std = new Student(id,classId,name,gender);
list.add(std);
}
return list;
}
}
}
}
static Connection getConnection() throws SQLException{
return DriverManager.getConnection(JDBC_URL,JDBC_USER,JDBC_PASSWORD);
}
}
4.4 获取计数JdbcSelect2.java
package com.feiyangedu.sample.pop3;
import java.sql.*;
public class JdbcSelect2 {
static final String JDBC_URL = "jdbc:mysql://localhost:13306/test0828?useSSL=false&characterEncoding=utf-8&serverTimeZone=UTC";
static final String JDBC_USER = "root";
static final String JDBC_PASSWORD = "123456";
public static void main(String[] args) throws Exception{
for(int i=1;i<=4;i++){
System.out.println(getNumOfStudent(i));
}
}
static long getNumOfStudent(long classId) throws SQLException{
try(Connection conn = getConnection()){
try(PreparedStatement ps = conn.prepareStatement("select count(*) num from students where class_id = ?")){
ps.setObject(1,classId);
try(ResultSet rs = ps.executeQuery()){
while(rs.next()){
long num = rs.getLong("num");
return num;
}
throw new RuntimeException("Empty result set");
}
}
}
}
static Connection getConnection() throws SQLException{
return DriverManager.getConnection(JDBC_URL,JDBC_USER,JDBC_PASSWORD);
}
}
5 JDBC查询总结
使用Statement（不推荐）和PrepareStatement（推荐）进行查询
- 总是优先使用PrepareStatement
- 查询结果是ResultSet
