今日学习
今天接着昨天的JDBC学习,通过SQL注入了解了Statement和PreparedStatement的不同之处,学会了提取工具类来封装JDBC,简化了连接数据库的流程。
还学习了如何用IDEA连接数据库,在IDEA中进行基本的事务操作,并学习到了连接池的内容。
今天把狂神MYSQL教程看到最后1P的一半就累了,明天将它看完,还是返回CSS的学习吧,再补一补前端的内容。
Statement对象
jdbc中的statement对象用于向数据库发送SQL语句,想完成对数据库的增删改查,只需要通过这个对象向数据库发送增删改查语句即可。
CRUD操作中
create
delete
update
都使用executeUpdate(String sql)方法完成数据库操作
而read:
使用executeQuery(String sql)方法完成数据查询操作
示例1:
Statement st = conn.createStatement();
String sql = "insert into user(字段...)values(数据...)";
//String sql = "delete from user where id=1";
//String sql = "update user set name='' where id=1";
int num = st.executeUpdate(sql);
if(num > 0){
System.out.println("操作成功");
}
示例2:
Statement st = conn.createStatement();
String sql = "select * from users";
ResultSet resultSet = st.executeQuery(sql);
while(resultSet.next()){
//根据获取列的数据类型,分别调用rs的相应方法映射到java对象中
}
提取工具类 封装JDBC
主要代码:
package com.sea.lesson02.utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
//封装JDBC
public class JdbcUtils {
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
static {
try {
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");
//1、驱动只要加载一次
Class.forName(driver);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//2、获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,username,password);
}
//释放资源
public static void release(Connection conn, Statement st, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (st != null) {
try {
st.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
增删查改例
增
package com.sea.lesson02;
import com.sea.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestInsert {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
st = conn .createStatement();
String sql = "INSERT INTO users VALUES(6, '白', '123456', 'lllll@qq.com', CURRENT_TIME)";
int i = st.executeUpdate(sql);
if(i > 0){
System.out.println("插入成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn, st, rs);
}
}
}
查
package com.sea.lesson02;
import com.sea.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestQuery {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();//获取数据库连接
st = conn.createStatement();
String sql = "SELECT * FROM users";
rs = st.executeQuery(sql);
while(rs.next()){
System.out.println("NAME=" + rs.getString("NAME"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
SQL注入问题
解决方法:使用preparedStatement对象代替Statement
SQL存在漏洞,会被攻击导致数据泄露:“SQL会被拼接 'or’1=1”
SQL注入问题例:
package com.sea.lesson02;
import com.sea.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
//SQL注入问题
public class SQLInject {
public static void main(String[] args) {
//常规查询
//Login("'赵六'", "123456");
//注入技术
Login(" 'or'1=1", "123456");
}
public static void Login(String name, String password){
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();//获取数据库连接
st = conn.createStatement();
String sql = "SELECT * FROM users where `NAME`= '" + name + "' AND `PASSWORD` = '" + password + "'";
rs = st.executeQuery(sql);
while(rs.next()){
System.out.println("NAME=" + rs.getString("NAME"));
System.out.println("PASSWORD=" + rs.getString("PASSWORD"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
preparedStatement对象
PreparedStatement对象可以防止SQL注入且效率更高
防止SQL注入例:使用PreparedStatement代替Statement
package com.sea.lesson03;
import com.sea.lesson02.utils.JdbcUtils;
import java.sql.*;
//preparedStatement防止sql注入
public class SQLInject {
public static void main(String[] args) {
//常规查询
//Login("赵六", "123456");
//注入技术
Login(" 'or'1=1", "123456");
}
public static void Login(String username, String password){
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();//获取数据库连接
//preparedStatement防止SQL注入的本质,把传进来的参数当做字符
//假设其中存在转义字符,直接忽略 比如说'被直接转义
String sql = "SELECT * FROM users where `NAME`=? and `PASSWORD`=?";
pstm = conn.prepareStatement(sql);
pstm.setString(1,username);
pstm.setString(2,password);
rs = pstm.executeQuery();
while(rs.next()){
System.out.println("NAME=" + rs.getString("NAME"));
System.out.println("PASSWORD=" + rs.getString("PASSWORD"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,pstm,rs);
}
}
}
使用IDEA连接数据库
IDEA中事务的操作
值得注意的是,捕获异常后如果执行失败会自动回滚。
//1、开始事务
conn.setAutoCommit(false);//关闭自动提交自动开启事务
//2、一组业务执行完毕,提交事务
conn.commit();
总结一哈
今天的学习还是挺有意思的,MYSQL部分的基础学习也进入了终章,之后的时间再接着啃前端内容。