10、JDBC（重点）
10.1、数据库驱动
驱动：声卡，显卡，数据库
我们的程序会通过 数据库 驱动，和数据库打交道！
10.2、JDBC
SUN公司为了简化 开发人员的（对数据库的统一）操作，提供了一个（Java操作数据库的）规范，俗称JDBC，这些规范的实现由具体的厂商去做。
对于开发人员来说，我们只需要掌握JDBC接口的操作即可
java.sql
javax.sql
还需要导入一个数据库驱动包 mysql-connection-java-5.1.47.jar
Maven Repository: mysql » mysql-connector-java (mvnrepository.com)
10.3、第一个JDBC程序
创建测试数据库
CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci
USE jdbcstudy
CREATE TABLE `users1` (
`id` INT(3) ,
`name` VARCHAR(30) NOT NULL,
`password` VARCHAR(40),
`email` VARCHAR(60),
birthday DATE,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO `users1`(id,`name`,`password`,email,birthday)VALUES
(1,'zhangsan','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')
-
创建一个普通项目
-
导入数据库驱动
-
编写测试代码
package com.kuang.lesson01;
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 users1";
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("password=" +resultSet.getObject("password"));
System.out.println("email=" +resultSet.getObject("email"));
System.out.println("birthday=" +resultSet.getObject("birthday"));
System.out.println("================================================");
}
//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
//oracle -- 1521
//jdbc:oracle:thin:@localhost:1521:sid
Statement 执行SQL的对象 PrepareStatement执行SQL的对象
String sql = "SELECT * FROM users1"; //编写SQL
statement.executeQuery(); //查询操作返回 ResultSet
statement.execute(); // 执行任何SQL
statement.executeUpdate(); //更新、插入、删除。都是用这个，返回一个受影响的行数
ResultSet 查询的结果集：封装了所有的查询结果
获得指定的数据类型
resultSet.getObject();//在不知道列类型的情况下使用
//如果知道列的类型就是用指定的
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
......
遍历,指针
resultSet.beforeFirst(); //移动到最前面
resultSet.afterLast(); //移动到最后面
resultSet.next(); //移动到下一个数据
resultSet.previous(); //移动到前一行
resultSet.absolute(row); //移动到指定行
释放资源
//6.释放连接
resultSet.close();
statement.close();
connection.close(); //耗资源，用完关掉！
10.4 statement对象
Statement st = conn.createStatement(); //获取SQL的执行对象
String sql = "DELETE FROM users1 WHERE id = 4";
int i = st.executeUpdate(sql);
if(i>0){
System.out.println("删除成功！");
}
CRUD操作-read
使用executeQuery(String sql)方法完成数据查询操作，示例操作：
Statement st = conn.createStatement(); //获取SQL的执行对象
String sql = "select * from users1 where id = 1";
ResultSet rs = st.executeQuery(sql);
while(rs.next()){
//根据获取列的数据类型，分别调用rs的相应方法映射到java对象中
}
代码实现
1、提取工具类
package com.kuang.lesson02.utils;
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("dp.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();
}
}
}
}
2、编写增删改的方法：executeUpdate
package com.kuang.lesson02;
import com.kuang.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(); //获取SQL的执行对象
String sql = "INSERT INTO users1(id,`name`,`password`,`email`,`birthday`)" +
"VALUES('4','kuangshen','123456','806451154@qq.com','2020-01-01')";
int i = st.executeUpdate(sql);
if(i>0){
System.out.println("插入成功！");
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
JdbcUtils.release(conn,st,rs);
}
}
}
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 users1 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);
}
}
}
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 users1 SET `name`='rongge' WHERE id = 3";
int i = st.executeUpdate(sql);
if(i>0){
System.out.println("更新成功！");
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
JdbcUtils.release(conn,st,rs);
}
}
}
3、查询：executeQuery
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 users1 where id = 1";
rs =st.executeQuery(sql); //查询完毕会返回一个结果集
while(rs.next()){
System.out.println(rs.getString("name"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
SQL注入的问题
sql存在漏洞，会被攻击导致数据泄露，SQL会被拼接
package com.kuang.lesson02;
import com.kuang.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) {
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 users1 WHERE `name`='rongge' AND `password`='123456'
String sql = "SELECT * FROM users1 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"));
System.out.println("====================");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
