文章目录
1. 数据库驱动
我们会通过数据库驱动和数据库打交道
2.JDBC
SUN公司为了简化开发人员对数据库的统一操作,提供了一个JAVA
操作数据库的规范JDBC
我们开发人员只需要掌握JDBC
接口的操作即可
3. 第一个JDBC程序
3.1 新建项目
- 启动IDEA,点击File,点击priject…创建一个普通项目
- 输入项目的名字,点击Create创建项目
3.2 导入jar包
- 在根目录下新建lib目录,导入jar包
- 然后右键lib目录,点击Add as Library…然后点击ok成功添加到项目中
3.3 JdbcFirstDemo类
在src目录创建一个JdbcFirstDemo类测试连接
package com.bin.lesson01;
import java.sql.*;
//我的第一个JDBC程序
public class JdbcFirstDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver"); //固定写法
//2.用户信息和url
// serverTimezone=GMT%2B8 设置时区为东八区
//useUnicode=true 支持中文编码
//characterEncoding=utf8 设置中文字符集为utf8
// useSSL=false 使用安全的连接
String url = "jdbc:mysql://localhost:3306/你的数据库名?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&useSSL=false";
String username = "用户名";
String password = "用户密码";
//3.连接成功.获取到数据库对象
Connection connection = DriverManager.getConnection(url, username, password);
//4.执行SQL对象 statement:执行sql的对象
Statement statement = connection.createStatement();
//5. 执行SQL的对象去执行SQL,可能存在的结果,查看返回结果
String sql = "SELECT * FROM t_user";
ResultSet resultSet = statement.executeQuery(sql); //返回的结果集合
while (resultSet.next()) {
System.out.println("userId=" + resultSet.getObject("userId"));
System.out.println("userName=" + resultSet.getObject("userName"));
System.out.println("userNickName=" + resultSet.getObject("userNickName"));
System.out.println("userPassword" + resultSet.getObject("userPassword"));
}
//6.释放连接
resultSet.close();
statement.close();
connection.close();
}
}
3.4 对象分析
DriverManager
// DriverManager.registerDriver(new com.mysql.jdbc.Driver())
Class.forName("com.mysql.cj.jdbc.Driver"); //固定写法
URL
String url = "jdbc:mysql://localhost:3306/你的数据库名?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&useSSL=false";
// 格式: jdbc:mysql://主机地址:端口号/你的数据库名?参数1&参数2&参数3
//ysql默认端口号为3306,如果是oralce数据库,端口号默认为1521
//oralce格式:jdbc:oralce:thin:@localhost:1521:sid
Connection
//connection 代表数据库
Connection connection = DriverManager.getConnection(url, username, password);
// 数据库支持自动提交
// 事务提交
// 事务回滚
connection.rollback();
connection.commit();
connection.setAutoCommit(true);
Statement
//执行sql的对象
Statement statement = connection.createStatement();
String sql = "SELECT * FROM t_user"; //编写sql
statement.executeQuery(sql); //查询操作返回ResultSet
statement.execute(sql); //可以执行任何sql
statement.executeUpdate(sql); //更新,插入,删除都使用这个,返回一个受影响的函数
ResultSet
//结果集对象,封装了所有的查询结果
resultSet.getObject("字段名"); // 在不知道列类型情况下使用
//如果直到列的类型就是用指定的类型
resultSet.getString("字段名");
resultSet.getInt("字段名");
resultSet.getFloat("字段名");
resultSet.getDouble("字段名");
resultSet.getDate("字段名");
...
遍历,指针(了解)
resultSet.beforeFirst(); //移动到最前面
resultSet.afterLast(); //移动到最后面
resultSet.next(); //移动到下一个数据
resultSet.previous(); //移动到前一行
resultSet.absolute(2); //移动到指定行(第2行)
释放资源
resultSet.close();
statement.close();
connection.close(); //耗资源,用完关掉
4. Statement对象
jdbc中的Statement对象用于向数据库发送SQL语句,想完成对数据库的增删查改,只需要通过这个对象向数据库发送增删查改语句即可.
Statement 对象的executeUpdate方法,用于向数据库发送增、删、改的SQL语句,executeUpdate执行完毕后,将会返回一个整数(即增删改导致几行数据发生了变化).
Statement.executeQuery 方法用于向数据库发送查询语句,executeQuery方法返回代表查询结果的ResultSet对象.
4.1 CRED核心
CURD-create
使用executeUpdate(String sql方法完成数据添加操作),实例:
String sql = "insert into t_user(...) values (...)";
int num =statement.executeUpdate(sql);
if( num > 0) {
System.out.println("插入成功");
}
CRUD-delete
String sql = "delete from t_user where userId=1";
int num =statement.executeUpdate(sql);
if( num > 0) {
System.out.println("删除成功");
}
CRUD-update
String sql = "update t_user set name='' where name=''";
int num =statement.executeUpdate(sql);
if( num > 0) {
System.out.println("修改成功");
}
CRUD-read
String sql = "SELECT * FROM t_user where userId=1";
ResultSet resultSet = statement.executeQuery(sql); //返回的结果集
while (resultSet.next()) {
根据获取列的数据类型,分别调用resultSet对应的方法映射到Java对象中
}
4.2 代码实现
- 提取工具类
package com.bin.lesson02.utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Collections;
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 inputStream = JdbcUtils.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");
//1. 驱动只用加载一次
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
// 2.获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
// 3.释放连接
public static void release(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
if(resultSet != null ) {
resultSet.close();
}
if(statement != null) {
statement.close();
}
if(connection != null) {
connection.close();
}
}
}
- 增加
package com.bin.lesson02;
import com.bin.lesson02.utils.JdbcUtils;
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) throws SQLException {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection(); //获取连接数据
statement = connection.createStatement(); //获取SQL的执行对象
String sql = "INSERT INTO t_class(classId, `className`, `classContent`) VALUES(4, '网络', '网络技术')"; //删除
int i = statement.executeUpdate(sql);
if(i > 0) {
System.out.println("插入成功");
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JdbcUtils.release(connection, statement, resultSet);
}
}
}
- 删除
package com.bin.lesson02;
import com.bin.lesson02.utils.JdbcUtils;
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) throws SQLException {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection(); //获取连接数据
statement = connection.createStatement(); //获取SQL的执行对象
String sql = "DELETE FROM t_class WHERE classId = 4";
int i = statement.executeUpdate(sql);
if(i > 0) {
System.out.println("删除成功");
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JdbcUtils.release(connection, statement, resultSet);
}
}
}
- 修改
package com.bin.lesson02;
import com.bin.lesson02.utils.JdbcUtils;
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) throws SQLException {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection(); //获取连接数据
statement = connection.createStatement(); //获取SQL的执行对象
String sql = "UPDATE t_class SET `classContent`='后端技术' WHERE `classId`=2";
int i = statement.executeUpdate(sql);
if(i > 0) {
System.out.println("更新成功");
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JdbcUtils.release(connection, statement, resultSet);
}
}
}
- 查询
package com.bin.lesson02;
import com.bin.lesson02.utils.JdbcUtils;
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) throws SQLException {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection(); //获取连接数据
statement = connection.createStatement(); //获取SQL的执行对象
String sql = "SELECT * FROM `t_class` WHERE `classId`=1";
resultSet = statement.executeQuery(sql); //查询完毕返回结果集
while (resultSet.next()) {
System.out.println(resultSet.getString("className"));
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JdbcUtils.release(connection, statement, resultSet);
}
}
}
5. SQL注入问题
5.1 statement对象
sql存在漏洞,会被攻击导致数据泄漏,SQL会被拼接,从而可能造成数据库信息泄漏
package com.bin.lesson03;
import com.bin.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SQL {
public static void main(String[] args) throws SQLException {
// 传入SQL信息
login("'or ' 1=1", "'or ' 1=1");
}
public static void login(String username, String password) throws SQLException {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection(); //获取连接数据
statement = connection.createStatement(); //获取SQL的执行对象
String sql = "SELECT * FROM `t_user` WHERE `userName`='" + username + "'AND `userPassword`='" + password + "'";
resultSet = statement.executeQuery(sql); //查询完毕返回结果集
while (resultSet.next()) {
System.out.println(resultSet.getString("userName"));
System.out.println(resultSet.getString("userPassword"));
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JdbcUtils.release(connection, statement, resultSet);
}
}
}
5.2 PreparedStatement对象
PreparedStatement可以防止SQL注入,并且效率更高!
使用: 使用?占位符代替参数,每一个?对应一个index,通过set传入对应位置的值
PreparedStatement防止SQL注入的本质,把传递进来的参数当做字符,假设存在转义字符,就直接忽略
package com.bin.lesson03;
import com.bin.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestInsert {
public static void main(String[] args) throws SQLException {
Connection connection = null;
PreparedStatement st = null;
try {
connection = JdbcUtils.getConnection();
String sql = "INSERT INTO t_class(classId, `className`, `classContent`) VALUES(?, ?, ?)";
st = connection.prepareStatement(sql);
st.setInt(1, 4);
st.setString(2, "网络");
st.setString(3, "网络技术");
int i = st.executeUpdate();
if(i > 0) {
System.out.println("插入成功");
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JdbcUtils.release(connection,st,null);
}
}
}
6. JDBC操作事务
package com.bin.lesson04;
import com.bin.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTeansaction1 {
public static void main(String[] args) throws SQLException {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
// 1.关闭数据库自动提交功能,自动开启事务
conn.setAutoCommit(false);
// 2.执行业务
String sql1 = "update student.result set StudentResult = StudentResult + 10 where StudentId=1";
st = conn.prepareStatement(sql1);
st.executeUpdate();
String sql2 = "update student.result set StudentResult = StudentResult - 10 where StudentId=2";
st = conn.prepareStatement(sql2);
st.executeUpdate();
// 3.业务完毕,提交事务
conn.commit();
System.out.println("成功!");
} catch (SQLException e) {
// 4.如果失败回滚事务
conn.rollback();
throw new RuntimeException(e);
} finally {
JdbcUtils.release(conn, st, rs);
}
}
}