JDBC基础语法、statement对象和PreparedStatement对象、SQL注入及解决、数据库连接池等详解
一、JDBC
1、数据库驱动
数据库驱动是应用程序与数据库之间沟通的桥梁，我们的程序通过数据库驱动与数据库打交道。
2、JDBC
JDBC(java database connectivity)驱动程序是对JDBC规范完整的实现，它的存在在JAVA程序与数据库系统之间建立了一条通信的渠道。由数据库厂家负责实现
需要导入数据库驱动包 mysql-connector-java-5.1.47.jar
3、第一个JDBC程序
创建测试数据库
CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;USE jdbcStudy;CREATE TABLE users( id INT PRIMARY KEY, NAME VARCHAR(40), PASSWORD VARCHAR(40), email VARCHAR(60), birthday DATE
);INSERT INTO users(id,NAME,PASSWORD,email,birthday)
VALUES(1,'zhansan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04');导入数据库驱动包
- 创建一个项目
- 导入数据库驱动包
- 添加依赖（add as library）
编写测试代码
import java.sql.*;// 我的第一个JDBC程序
public class JdbcFirstDemo { public static void main(String[] args) throws ClassNotFoundException, SQLException { //1. 加载驱动 Class.forName("com.mysql.jdbc.Driver"); // 固定写法，加载驱动//2. 用户信息和url // useUnicode=true&characterEncoding=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 connection = DriverManager.getConnection(url, username, password);//4. 执行SQL的对象 Statement 执行sql的对象 Statement statement = connection.createStatement();//5. 执行SQL的对象 去 执行SQL，可能存在结果，查看返回结果 String sql = "SELECT * FROM users";ResultSet resultSet = statement.executeQuery(sql); //返回的结果集,结果 集中封装了我们全部的查询出来的结果while (resultSet.next()){ System.out.println("id=" + resultSet.getObject("id")); System.out.println("name=" + resultSet.getObject("NAME")); System.out.println("pwd=" + resultSet.getObject("PASSWORD")); System.out.println("email=" + resultSet.getObject("email")); System.out.println("birth=" + resultSet.getObject("birthday")); }//6、释放连接 resultSet.close(); statement.close(); connection.close();}
}步骤
- 加载驱动
- 连接数据库 DriverManager
- 获得执行sql的对象 Statement
- 获得返回的结果集
- 释放连接
DriverManager
// DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver"); // 固定写法，加载驱动
Connection connection = DriverManager.getConnection(url, username, password);// connection 代表数据库
// 数据库设置自动提交
// 事务提交
// 事务滚回
connection.rollback();
connection.commit();
connection.setAutoCommit();URL
String url = "jdbc:mysql://localhost:3306/jdbcstudy? useUnicode=true&characterEncoding=utf8&useSSL=true";// mysql -- 3306
// 协议 ://主机地址:端口号/数据库名?参数1&参数2&参数3
// oralce -- 1521
//jdbc:oracle:thin:@localhost:1521:sidStatement 执行SQL 的对象 PrepareStatement 执行SQL 的对象
String sql = "SELECT * FROM users"; // 编写SQLstatement.executeQuery(); //查询操作返回 ResultSet
statement.execute(); // 执行任何SQL
statement.executeUpdate(); // 更新、插入、删除。都是用这个，返回一个受影响的行数ResultSet 查询的结果集：封装了所有的查询结果
获得指定的数据类型
resultSet.getObject(); // 在不知道列类型的情况下使用
// 如果知道列的类型就使用指定的类型
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
resultSet.getObject();
....遍历、指针
resultSet.beforeFirst(); // 移动到前面
resultSet.afterLast(); // 移动到后面
resultSet.next(); //移动到下一个数据
resultSet.previous(); //移动到前一行
resultSet.absolute(row); //移动到指定行释放资源
resultSet.close();
statement.close();
connection.close(); // 耗资源，用完关掉！4、statement对象
Jdbc中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象 向数据库发送增删改查语句即可。
Statement对象的executeUpdate方法，用于向数据库发送增、删、改的sql语句，executeUpdate执行 完后，将会返回一个整数（即增删改语句导致了数据库几行数据发生了变化）。
Statement对象的Statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的 ResultSet对象。
create操作
使用executeUpdate(String sql)方法完成数据添加操作
Statement st = conn.createStatement();
String sql = "insert into user(….) values(…..) ";
int num = st.executeUpdate(sql);
if(num>0){ System.out.println("插入成功！！！");
}delete操作
使用executeUpdate(String sql)方法完成数据删除操作
Statement st = conn.createStatement();
String sql = "delete from user where id=1";
int num = st.executeUpdate(sql);
if(num>0){ System.out.println(“删除成功！！！");
}update操作
使用executeUpdate(String sql)方法完成数据修改操作
Statement st = conn.createStatement();
String sql = "update user set name='' where name=''";
int num = st.executeUpdate(sql);
if(num>0){ System.out.println(“修改成功！！！");
}read操作
使用executeQuery(String sql)方法完成数据查询操作
Statement st = conn.createStatement();
String sql = "select * from user where id=1";
ResultSet rs = st.executeQuery(sql);
while(rs.next()){ //根据获取列的数据类型，分别调用rs的相应方法映射到java对象中
}代码实现
在 src 下建立资源文件 db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy? useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456
- 提取连接工具类
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;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 (Exception e) { e.printStackTrace(); }}//获取连接 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 e) { e.printStackTrace(); } } if (st!=null){ try { st.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn!=null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
}- 编写增删改的方法， executeUpdate
//导入上面的连接工具类
//插入类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(); //获得SQL的执行对象String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)" + "VALUES(4,'kuangshen','123456','24736743@qq.com','202001-01')";int i = st.executeUpdate(sql); if (i>0){ System.out.println("插入成功！"); }} catch (SQLException e) { e.printStackTrace(); } finally { JdbcUtils.release(conn,st,rs); }}
}//导入上面的连接工具类
//删除类import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;public class TestDelete { public static void main(String[] args) {Connection conn = null; Statement st = null; ResultSet rs = null;try { conn = JdbcUtils.getConnection(); //获取数据库连接 st = conn.createStatement(); //获得SQL的执行对象String sql = "DELETE FROM users WHERE id = 4";int i = st.executeUpdate(sql); if (i>0){ System.out.println("删除成功！"); }} catch (SQLException e) { e.printStackTrace(); } finally { JdbcUtils.release(conn,st,rs); }}
}//导入上面的连接工具类
//更新类import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;public class TestUpdate { public static void main(String[] args) { Connection conn = null; Statement st = null; ResultSet rs = null;try { conn = JdbcUtils.getConnection(); //获取数据库连接 st = conn.createStatement(); //获得SQL的执行对象String sql = "UPDATE users SET `NAME`='kuangshen',`email`='24736743@qq.com' WHERE id=1";int i = st.executeUpdate(sql); if (i>0){ System.out.println("更新成功！"); }} catch (SQLException e) { e.printStackTrace(); } finally { JdbcUtils.release(conn,st,rs); }}
}- 查询executeQuery
//导入上面的连接工具类
//查询类import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;public class TestSelect { public static void main(String[] args) { Connection conn =null; Statement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); st = conn.createStatement();//SQL String sql = "select * from users where id = 1";rs = st.executeQuery(sql); //查询完毕会返回一个结果集 while (rs.next()){ System.out.println(rs.getString("NAME")); } } catch (SQLException e) { e.printStackTrace(); } finally { JdbcUtils.release(conn,st,rs); }}
}SQL注入问题
sql 存在漏洞，会被攻击导致数据泄露，SQL会被拼接 or
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;public class SQLInjection { public static void main(String[] args) {// login("kuangshen","123456"); login(" 'or '1=1"," 'or'1=1"); // 技巧}// 登录业务 public static void login(String username,String password){Connection conn =null; Statement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); st = conn.createStatement();// SELECT * FROM users WHERE `Name` = 'kuangshen' AND `password` = '123456';// SELECT * FROM users WHERE `Name` = '' or '1=1' AND `password` = '' or '1=1'; String sql = "select * from users where `NAME`='"+username+"' AND `password` ='"+password+"'";rs = st.executeQuery(sql); //查询完毕会返回一个结果集 while (rs.next()){ System.out.println(rs.getString("NAME")); System.out.println(rs.getString("password")); }} catch (SQLException e) { e.printStackTrace(); } finally { JdbcUtils.release(conn,st,rs); }}
}5、PreparedStatement对象
PreparedStatement 可以防止SQL注入。效率更高
- 增
import java.sql.Connection;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;public class TestInsert { public static void main(String[] args) { Connection conn = null; PreparedStatement st = null;try { conn = JdbcUtils.getConnection(); // 区别 // 使用？ 占位符代替参数 String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";st = conn.prepareStatement(sql); //预编译SQL，先写sql，然后不执行// 手动给参数赋值 st.setInt(1,4); //idst.setString(2,"zhangsan"); st.setString(3,"1232112"); st.setString(4,"1234567890@qq.com"); // 注意点： sql.Date 数据库 java.sql.Date() // util.Date Java new Date().getTime() 获得时间戳 st.setDate(5,new java.sql.Date(new Date().getTime()));//执行 int i = st.executeUpdate(); if (i>0){ System.out.println("插入成功！"); }} catch (SQLException e) { e.printStackTrace(); } finally { JdbcUtils.release(conn,st,null); } }
}- 删
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;public class TestDelete { public static void main(String[] args) { Connection conn = null; PreparedStatement st = null;try { conn = JdbcUtils.getConnection(); // 区别 // 使用？ 占位符代替参数 String sql = "delete from users where id=?";st = conn.prepareStatement(sql); //预编译SQL，先写sql，然后不执行// 手动给参数赋值 st.setInt(1,4);//执行 int i = st.executeUpdate(); if (i>0){ System.out.println("删除成功！"); }} catch (SQLException e) {e.printStackTrace(); } finally { JdbcUtils.release(conn,st,null); } }
}- 改
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;public class TestUpdate { public static void main(String[] args) { Connection conn = null;PreparedStatement st = null;try { conn = JdbcUtils.getConnection(); // 区别 // 使用？ 占位符代替参数 String sql = "update users set `NAME`=? where id=?;";st = conn.prepareStatement(sql); //预编译SQL，先写sql，然后不执行// 手动给参数赋值 st.setString(1,"zhangsan"); st.setInt(2,1);//执行 int i = st.executeUpdate(); if (i>0){ System.out.println("更新成功！"); }} catch (SQLException e) { e.printStackTrace(); } finally { JdbcUtils.release(conn,st,null); }}
}- 查
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;public class TestSelect { public static void main(String[] args) {Connection conn = null; PreparedStatement st = null; ResultSet rs = null;try { conn = JdbcUtils.getConnection();String sql = "select * from users where id = ?"; // 编写SQLst = conn.prepareStatement(sql); // 预编译st.setInt(1,2); //传递参数rs = st.executeQuery(); //执行if (rs.next()){ System.out.println(rs.getString("NAME")); }} catch (SQLException e) { e.printStackTrace();} finally { JdbcUtils.release(conn,st,rs); } }
}- 防止SQL注入
import java.sql.*;public class SQLInjection { public static void main(String[] args) {// login("lisi","123456"); login("'' or 1=1","123456"); }// 登录业务public static void login(String username,String password){Connection conn =null; PreparedStatement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); // PreparedStatement 防止SQL注入的本质，把传递进来的参数当做字符 // 假设其中存在转义字符，比如说 ' 会被直接转义 String sql = "select * from users where `NAME`=? and `PASSWORD`=?"; // Mybatisst = conn.prepareStatement(sql); st.setString(1,username); st.setString(2,password);rs = st.executeQuery(); //查询完毕会返回一个结果集 while (rs.next()){ System.out.println(rs.getString("NAME")); System.out.println(rs.getString("password")); }} catch (SQLException e) { e.printStackTrace(); } finally { JdbcUtils.release(conn,st,rs); }}
}6、使用IDEA连接数据库
- 点击右侧边栏的database
- 点击MySQL
- 连接成功后可以选择数据库
- 可以印行可视化操作或使用SQL命令操作
7、事务
要么都成功、要么都失败
事务原则
-
原子性：要么全部完成，要么都不完成
-
一致性：总数不变
-
隔离性：多个进程互不干扰
-
持久性：一旦提交不可逆，持久化到数据库了
隔离性的问题
- 脏读：一个事务读取了另一个没有提交的事务
- 不可重复读：在同一个事务内，重复读取表中的数据，表数据发生了改变
- 虚读（幻读）：在一个事务内，读取到了别人插入的数据，导致前后读出来结果不一致
代码实现
- 开启事务 conn.setAutoCommit(false);
- 一组业务执行完毕，提交事务
- 可以在catch 语句中显示的定义 回滚语句，默认失败就会回滚
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;public class TestTransaction2 { public static void main(String[] args) { Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); // 关闭数据库的自动提交，自动会开启事务 conn.setAutoCommit(false); // 开启事务String sql1 = "update account set money = money-100 where name = 'A'"; st = conn.prepareStatement(sql1); st.executeUpdate();int x = 1/0; // 报错String sql2 = "update account set money = money+100 where name = 'B'"; st = conn.prepareStatement(sql2); st.executeUpdate();//业务完毕，提交事务 conn.commit(); System.out.println("成功！");} catch (SQLException e) { // 若果失败，则默认回滚 try { conn.rollback(); // 如果失败则回滚事务} catch (SQLException e1) {e1.printStackTrace(); } e.printStackTrace(); } finally { JdbcUtils.release(conn,st,rs); }}
}8、数据库连接池
数据库连接——>数据库连接——>执行完毕——>释放
连接——>释放 十分浪费系统资源
池化技术：准备一些预先的资源，过来就连接预先准备好的
最小连接数： 10
最大连接数： 15
等待超时：100ms
编写连接池：实现接口 DataSource
开源数据源实现
-
DBCP
-
C3P0
-
Druid：阿里巴巴
使用了这些数据库连接池后，在开发中就不需要编写连接数据库的代码了
DBCP
需要的Jar包
commons-dbcp-1.4 、 commons-pool-1.6
C3P0
需要的Jar 包
c3p0-0.9.5.5、mchange-commons-java-0.2.19
不管什么数据源，本质都是实现DataSource接口，方法一样
发布评论