JDBC
理解图
需要mysql包
下载官网：https://downloads.mysql.com/archives/c-j/
第一个JDBC项目
-
创建一个java项目,一路next
-
导入jar包
-
编写测试代码
步骤：
- 加载驱动 ——打开SQLyog
- 用户信息和url——输入用户账号密码和url (localhost)
- 连接成功，数据库对象——点击连接
- 获得执行SQL的对象
- 获得返回结果集合——执行语句，返回结果
- 释放连接
import java.sql.*; public class jdbc_01 { public static void main(String[] args) throws ClassNotFoundException, SQLException { //1. 加载驱动 ——打开SQLyog Class.forName("com.mysql.jdbc.Driver");//固定写法 //2. 用户信息和url //useUnicode=true 支持中文编码 &characterEncoding=utf8 设置中文字符UTF8&useSSL=true 使用安全的连接 String url = "jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=true"; String username="root"; String password="123456"; //3. 连接成功，数据库对象 Connection connection = DriverManager.getConnection(url, username, password); //4. 获得执行SQL的对象 Statement statement = connection.createStatement(); //5. 获得返回结果集合 String sql = "SELECT * FROM users"; ResultSet resultSet = statement.executeQuery(sql); while(resultSet.next()){ System.out.println(resultSet.getObject("id")); System.out.println(resultSet.getObject("name")); } //6.释放连接 resultSet.close(); statement.close(); connection.close(); } }
DriverManager
//加载驱动，不返回对象的原因（即不new的原因）
Class.forName("com.mysql.jdbc.Driver");
//com.mysql.jdbc.Driver类中存在了DriverManager.registerDriver(new Driver());
URL
String url = "jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
//mysql 默认3306端口
//jdbc:mysql 理解成https 就是连接Mysql
//localhost 理解成www.baidu.com
//jdbcStudy 数据库名
Connection
Connection connection = DriverManager.getConnection(url, username, password);
//connection 代表数据库
//数据库设置自动提交connection.getAutoCommit();
//事务提交connection.commit();
//事务回滚connection.rollback();
statement 执行SQL 的对象 prepareStatement
statement用于向是数据库发送sql
statement.executeQuery();//查询 返回ResultSet
statement.executeUpdate();//更新 插入 删除 返回受影响的行数
statement.execute();//SQL语句
Resultset 查询的结果集
resultSet.getString();
resultSet.getFloat();
resultSet.getInt();
resultSet.getObject();//知道类的类型就使用指定类型
resultSet.next();
释放资源 不关耗资源
resultSet.close();
statement.close();
connection.close();
封装
-
编写配置
-
编写工具类
-
编写测试类
文件结构
db.properties(字符串不用加引号)
driver = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username = root
password = 123456
工具类
package cn.dj.www.lesson01;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtil {
private static String driver=null;
private static String url=null;
private static String username=null;
private static String password=null;
static{
try{
//./ 表示上一级目录
InputStream inputStream = JdbcUtil.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(inputStream);
driver=properties.getProperty("driver");
url=properties.getProperty("url");
username=properties.getProperty("username");
password=properties.getProperty("password");
//驱动只要加载一次
Class.forName(driver);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
//释放连接
public static void release(Connection connection, Statement statement, ResultSet resultSet) {
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();
}
}
}
}
测试类
package cn.dj.www.lesson01;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Test {
public static void main(String[] args) throws SQLException {
Connection connection = JdbcUtil.getConnection();
Statement statement = connection.createStatement();
String name = "li%";//注意有''
String sql = "SELECT * FROM users " +
"where name like '"+name+"'";
ResultSet rs = statement.executeQuery(sql);
while(rs.next()){
System.out.println(rs.getString("name"));
System.out.println(rs.getInt("id"));
}
JdbcUtil.release(connection,statement,rs);
}
}
statement对象导致SQL注入
典型例子
String id = "1 or 1=1";
String sql = "SELECT * FROM users " +"where id = "+id;
prepareStatement
更安全，效率更高
package cn.dj.www.lesson01;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
public class PTest {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pst =null;
try {
conn = JdbcUtil.getConnection();
String sql = "insert into users(id,`name`,`password`,`email`,`birthday`)"+
"values(?,?,?,?,?)";
pst = conn.prepareStatement(sql);//预编译
//传递参数
pst.setInt(1,4);
pst.setString(2,"DJ");
pst.setString(3,"123456");
pst.setString(4,"123@163.com");
// java Date().getTime() 获得时间戳
pst.setDate(5, new java.sql.Date(new Date().getTime()));
int i = pst.executeUpdate();
if(i>0){
System.out.println("插入成功！");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtil.release(conn,pst,null);
}
}
}
解决1=1注入的例子
//防止SQL注入的本质：把传递进来的参数当做字符就是外面再包了'',假设其中存在转移字符如（‘）就直接忽略了
String name =" '' or 1=1";
String sql = "SELECT * FROM users " +"where name = ?";
pst.setString(1,name);