JDBC
数据库驱动
jdbc提供java操作数据库规范,用与链接三者的工具。
statement对象,用来执行sql语句
resultSet对象,用来保存查找到数据
个人项目讲解
创建db.properties
driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
url=jdbc:sqlserver://10.127.79.56:1433;databaseName=ETL_E2E
userName=sa
password=password
创建JdbcUtils类
框架
表结构
private static String driver = null;
private static String url = null;
private static String userName = null;
private static String password = null;
这是与db文件常量相互映射
public static Connection getConnetion() throws SQLException, ClassNotFoundException, IOException {
InputStream in = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(in);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
userName = properties.getProperty("userName");
password = properties.getProperty("password");
//driver only load once
Class.forName(driver); Connection conAIU = DriverManager.getConnection(url, userName, password);
return conAIU;
}
上面是utils的链接数据库方法,返回链接到的数据库。
public static void release(Statement stmt, Connection conn) {
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;
}
}
上面是链接释放method,每次链接完必须释放掉资源。
public static void release(ResultSet rs, Statement stmt, Connection conn) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}
release(stmt, conn);
}
}
上面是release()的重载,适用于有用到resultset变量的方法上。
创建实体类————Student
public class Student {
public int id;
public String name;
public int age;
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 getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
}
此类作用就是于数据库的目标表相互映射,再给fields创建get()&set()方法,作用类似django的models。
创建dao层,用来编写管理数据的方法
package com.jdbc.dao;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import com.jdbc.domain.Student;
import com.jdbc.utils.JDBCUtils;
public class StudentDao {
public boolean insert(Student student) {
// insert user
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnetion();
stmt = conn.createStatement();
String sql = "INSERT INTO stu02(id,name,age)" + " VALUES(" + student.getId() + ",'" + student.getName()
+ "'," + student.getAge() + ")";
int num = stmt.executeUpdate(sql);
if (num > 0) {
System.out.println("成功增加数据"+student);
return true;
}
return false;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(rs, stmt, conn);
}
return false;
}
插入方法,返回布尔值,表示是否成功插入数据,将输入的实体类Student的各个filed映射成表结构,insert数据库中
public ArrayList<Student> findAll(){
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
ArrayList<Student> list =new ArrayList<Student>();
try {
conn = JDBCUtils.getConnetion();
stmt = conn.createStatement();
String sql = "SELECT * FROM stu02";
rs = stmt.executeQuery(sql);
while(rs.next()) {
if(rs.getString("name")=="lily") {
break;
}
Student student =new Student();
student.setId(rs.getInt("id"));
student.setName(rs.getString("name"));
student.setAge(rs.getInt("age"));
list.add(student);
}
return list;
}catch(Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(rs, stmt, conn);
}
return null;
}
findall作用:查询所有数据,将其每个rs保存成student,返回到ArrayList集合中
public Student find(int id) {
//select by id
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnetion();
stmt = conn.createStatement();
String sql = "SELECT * FROM stu02 WHERE id=" + id;
rs = stmt.executeQuery(sql);
while(rs.next()) {
Student student =new Student();
student.setId(rs.getInt("id"));
student.setName(rs.getString("name"));
student.setAge(rs.getInt("age"));
return student;
}
return null;
}catch(Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(rs, stmt, conn);
}
return null;
}
按id查找
public boolean delete(int id) {
//delete by id
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnetion();
stmt = conn.createStatement();
String sql = "DELETE FROM stu02 WHERE id=" + id;
int num = stmt.executeUpdate(sql);
if (num > 0) {
return true;
}
return false;
}catch(Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(rs, stmt, conn);
}
return false;
}
按id删除,也就跟插入差不多
public boolean update(Student student) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnetion();
stmt = conn.createStatement();
String sql = "UPDATE stu02 set name='" + student.getName()
+ "',age=" + student.getAge() + " WHERE id=" + student.getId();
int num = stmt.executeUpdate(sql);
if (num > 0) {
return true;
}
return false;
}catch(Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(rs, stmt, conn);
}
return false;
}
}
将输入的student类数据保存,id是主键。
创建Test类
deleteTest
public class deleteTest {
public static void main(String[] args) {
StudentDao sd = new StudentDao();
boolean b = sd.delete(3);
System.out.println(b);
}
}
结果:
true
findAll()
public static void main(String[] args) {
StudentDao sd = new StudentDao();
ArrayList<Student> list= sd.findAll();
for(int i =0; i<list.size();i++) {
System.out.println("第"+ (i+1) +"条的数据username值为:"+ list.get(i).getName());
}
}
}
findById
package com.jdbc.Test;
import com.jdbc.dao.StudentDao;
import com.jdbc.domain.Student;
public class findStudentById {
public static void main(String[] args) {
StudentDao sd = new StudentDao();
Student student = sd.find(6);
System.out.println("id为"+student.getId()+"的name:"+ student.getName());
}
}
insert
public class insertTest {
public static void main(String[] args) {
StudentDao sd = new StudentDao();
Student student = new Student();
student.setId(6);
student.setName("shiqian6");
student.setAge(49);
boolean b =sd.insert(student);
System.out.println(b);
}
}
update
public class insertTest {
public static void main(String[] args) {
StudentDao sd = new StudentDao();
Student student = new Student();
student.setId(6);
student.setName("shiqian6");
student.setAge(49);
boolean b =sd.insert(student);
System.out.println(b);
}
}
sql注入
sql安全是重中之重,我们现在dao层增加一个Query方法,用一个String变量来当查询条件
public void query(String name) {
//select by name
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnetion();
stmt = conn.createStatement();
String sql = "SELECT * FROM stu02 WHERE name='" + name+"'";
rs = stmt.executeQuery(sql);
while(rs.next()) {
System.out.println(rs.getInt("id"));
System.out.println(rs.getString("name"));
System.out.println(rs.getInt("age"));
}
}catch(Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(rs, stmt, conn);
}
}
编写一个正常test类
public class QueryTest {
public static void main(String[] args) {
StudentDao sd = new StudentDao();
//sd.query("shiqian'"+"or 1='1");
sd.query("shiqian3");
}
}
结果:
3
shiqian3
47
注入情况
public class QueryTest {
public static void main(String[] args) {
StudentDao sd = new StudentDao();
sd.query("shiqian3'"+"or 1='1");
System.out.println("============================");
sd.query("shiqian3");
}
}
result:
1
jack
18
2
lily
19
3
shiqian3
47
4
update01_qian
101
6
shiqian6
49
============================
3
shiqian3
47
parperdSTament
为了降低SQL注入的风险性
可以用ParperedStament解决这个问题
该项目的Insert修改成下列代码
public boolean insert(Student student) {
// insert user
Connection conn = null;
//Statement stmt = null;
PreparedStatement pst =null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnetion();
String sql = "INSERT INTO stu02(id,name,age)" + " VALUES(?,?,?)";
pst =conn.prepareStatement(sql);
pst.setInt(1, student.getId());
pst.setString(2, student.getName());
pst.setInt(3, student.getAge());
int num = pst.executeUpdate();
if (num > 0) {
System.out.println("成功增加数据"+student);
return true;
}
return false;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(rs, stmt, conn);
}
return false;
}
将本来sql语句的修改值用占位符?代替,然后再用set值,一一对应。
test效果
StudentDao sd = new StudentDao();
Student student = new Student();
student.setId(7);
student.setName("shiqian7");
student.setAge(98);
boolean b =sd.insert(student);
System.out.println(b);
System.out.println("========================");
if(b == true) {
String name =student.getName();
sd.query(name);
}
result:
成功增加数据com.jdbc.domain.Student@6c6cb480
true
========================
7
shiqian7
98
所以成功添加数据
具体用parperedstatment修改后的代码
package com.jdbc.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import com.jdbc.domain.Student;
import com.jdbc.utils.JDBCUtils;
public class StudentDao {
public boolean insert(Student student) {
// insert user
Connection conn = null;
Statement stmt = null;
PreparedStatement pst = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnetion();
String sql = "INSERT INTO stu02(id,name,age)" + " VALUES(?,?,?)";
pst = conn.prepareStatement(sql);
pst.setInt(1, student.getId());
pst.setString(2, student.getName());
pst.setInt(3, student.getAge());
int num = pst.executeUpdate();
if (num > 0) {
System.out.println("成功增加数据" + student);
return true;
}
return false;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(rs, pst, conn);
}
return false;
}
public ArrayList<Student> findAll() {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
ArrayList<Student> list = new ArrayList<Student>();
try {
conn = JDBCUtils.getConnetion();
stmt = conn.createStatement();
String sql = "SELECT * FROM stu02";
rs = stmt.executeQuery(sql);
while (rs.next()) {
if (rs.getString("name") == "lily") {
break;
}
Student student = new Student();
student.setId(rs.getInt("id"));
student.setName(rs.getString("name"));
student.setAge(rs.getInt("age"));
list.add(student);
}
return list;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(rs, stmt, conn);
}
return null;
}
public Student find(int id) {
// select by id
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnetion();
String sql = "SELECT * FROM stu02 WHERE id=" + "(?)";
pst = conn.prepareStatement(sql);
pst.setInt(1, id);
rs = pst.executeQuery();
while (rs.next()) {
Student student = new Student();
student.setId(rs.getInt("id"));
student.setName(rs.getString("name"));
student.setAge(rs.getInt("age"));
return student;
}
return null;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(rs, pst, conn);
}
return null;
}
public void query(String name) {
// select by name
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnetion();
String sql = "SELECT * FROM stu02 WHERE name=(?)";
pst = conn.prepareStatement(sql);
pst.setString(1, name);
rs = pst.executeQuery();
while (rs.next()) {
System.out.println(rs.getInt("id"));
System.out.println(rs.getString("name"));
System.out.println(rs.getInt("age"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(rs, pst, conn);
}
}
public boolean delete(int id) {
// delete by id
Connection conn = null;
// Statement stmt = null;
PreparedStatement pst = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnetion();
String sql = "DELETE FROM stu02 WHERE id=" + "(?)";
pst = conn.prepareStatement(sql);
pst.setInt(1, id);
int num = pst.executeUpdate();
if (num > 0) {
return true;
}
return false;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(rs, pst, conn);
}
return false;
}
public boolean update(Student student) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnetion();
stmt = conn.createStatement();
String sql = "UPDATE stu02 set name='" + student.getName() + "',age=" + student.getAge() + " WHERE id="
+ student.getId();
int num = stmt.executeUpdate(sql);
if (num > 0) {
return true;
}
return false;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(rs, stmt, conn);
}
return false;
}
}
public class QueryTest {
public static void main(String[] args) {
StudentDao sd = new StudentDao();
sd.query("shiqian3"+"or 1=1");
System.out.println("============================");
sd.query("lily");
}
}
结果
============================
2
lily
19
重点分析一下query,再上一章,很明显看出sql注入的危害,通过更新后的代码,避免了风险。
preparedStament避免sql注入的本质就是,将传递进来的参数都当作字符,转义字符不在影响
ACID
原子性:要么都完成,反之亦然
一致性:总数不变
持久性:一旦提交不可逆
隔离性:进程互不干扰
- 脏读:一个事务读取了别人的事务
- 不可重复读:
- 虚读: