11、JDBC(重点)
11.1、数据库驱动
驱动:声卡、显卡、数据库
11.2、JDBC
SUN、公司为了简化开发人员的(对数据库的统一)操作,提供了一个(Java操作数据库的)规范。俗称JDBC。
java.sql
javax.sql
还需要一个数据库驱动包 mysql-connector-java-5.1.47
https://repo1.maven.org/maven2/mysql/mysql-connector-java/5.1.47/
11.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
)ENGINE=INNODB DEFAULT CHARSET=utf8;
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');
- 创建一个数据库
- 导入数据库驱动(在project中,右键选中Directory,新建lib文件,将jar包直接拷进去)
- 编写测试代码
package com.sun.lesson1;
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=false
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
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("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
Class.forName("com.mysql.jdbc.Driver");//固定写法,加载驱动
Connection connection = DriverManager.getConnection(url, username, password);
// connection 代表数据库
// 数据库设置自动提交
// 事务提交
// 事务回滚
connection.setAutoCommit();
connection.commit();
connection.rollback();
URL
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
//mysql -- 3306
jdbc:mysql://主机地址:端口号/数据库名?参数1&参数2&参数3
//oracle -- 1521
//jdbc:oracle:thin:@localhost:1521:sid
Statement执行SQL的对象
Statement statement = connection.createStatement(); //创建Statement对象,connection为数据库连接对象
String sql="SELECT * FROM users WHERE id >=2";
statement.executeQuery();//查询操作返回 ResultSet
statement.execute();//执行指定的sql语句
statement.executeUpdate();//更新、插入、删除、都用这个,返回值是受影响的行数或者0
ResultSet 查询的结果是:封装了所有的查询结果
获得指定的数据类型 ResultSet接口提供的get×××()方法
resultSet.getObject(); //在不知道列类型的情况下使用
resultSet.getString(); //如果知道列的类型,就使用指定类型
resultSet.getInt();
resultSet.getFloat();
resultSet.getDouble();
resultSet.beforeFirst();//移动到最前面
resultSet.afterLast();//移动到最后面
resultSet.next();//移动到下一个数据
resultSet.previous();//移动到前一行
resultSet.absolute();//移动到指定行
释放资源
//6.释放连接
resultSet.close();
statement.close();
connection.close();//耗资源,用完关掉
11.4、statement
jdbc中的statement对象用于向数据库发送SQL语句,想完成对数据库的增删改查,只需要通过这个对象向数据库发送增删改查语句即可
Statement对象的executeUpdate方法,用于向数据库发送增、删、改的sql语句,executeUpdate执行完后,将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)。
Statement.executeQuery方法用于向数据库发送查询语句,executeQuery方法返回代表查询结果的ResultSet对象。
CRUD操作—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("插入成功!");
}
CRUD—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("删除成功!");
}
CRUD—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("修改成功!");
}
CRUD—retrieve(检索)
使用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对象中
}
代码实现
-
提取工具类
package com.sun.lesson2.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("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 | ClassNotFoundException 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
Insert
package com.sun.lesson2.utils;
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(6,'xiaohong','123456','15454651@qq.com','2021-01-01')";
int i=st.executeUpdate(sql);
if(i>0){
System.out.println("插入成功!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
Delete
package com.sun.lesson2.utils;
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);
}
}
}
Update
package com.sun.lesson2.utils;
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`='xiaomei' WHERE `name`='xiaohong';";
int i=st.executeUpdate(sql);
if(i>0){
System.out.println("修改成功!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
- 查询
package com.sun.lesson2.utils;
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();
String sql="SELECT `id`,`password` FROM users";
rs=st.executeQuery(sql);//查询完毕会返回一个结果集
while(rs.next()){
System.out.println("id="+rs.getObject("id"));
System.out.println("password="+rs.getObject("password"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
JdbcUtils.release(conn,st,rs);
}
}
}
通过Scanner交互,实现登录。
package com.sun.lesson2;
import com.sun.lesson2.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class SQL注入 {
public static void main(String[] args) {
Scanner scanner1 = new Scanner(System.in);
System.out.println("输入登录名");
String username=scanner1.nextLine();
Scanner scanner2 = new Scanner(System.in);
System.out.println("输入密码");
String password=scanner2.nextLine();
login(username,password);
scanner1.close();
scanner2.close();
}
//登录业务
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`='zhangsan' AND `password`='123456';
String sql="SELECT * FROM users WHERE `name`='"+username+"' AND `password`='"+password+"'";
rs=st.executeQuery(sql);//查询完毕会返回一个结果集
while(rs.next()){
System.out.println("id="+rs.getObject("id"));
System.out.println("name="+rs.getObject("name"));
System.out.println("password="+rs.getObject("password"));
System.out.println("email="+rs.getObject("email"));
System.out.println("birthday="+rs.getObject("birthday"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
JdbcUtils.release(conn,st,rs);
}
}
}
SQL注入的问题
sql存在漏洞,会被攻击,导致数据泄露。SQL会被拼接 or
package com.sun.lesson2;
import com.sun.lesson2.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SQL注入2 {
public static void main(String[] args) {
login("'or'1=1","'or'1=1");//sql会被拼接
}
public static void login(String username,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`='"+username+"' AND `password`='"+password+"'";
rs=st.executeQuery(sql);
while(rs.next()){
System.out.println("id="+rs.getObject("id"));
System.out.println("name="+rs.getObject("name"));
System.out.println("password="+rs.getObject("password"));
System.out.println("email="+rs.getObject("email"));
System.out.println("birthday="+rs.getObject("birthday"));
System.out.println("==========================================");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
11.5、PrepareStatement对象
PrepareStatement可以防止SQL注入。效率更好!
- 新增
- 删除
- 更新
- 查询
- 防止SQL注入(使用PrepareStatement)
1.新增
package com.sun.lesson3;
import com.sun.lesson2.utils.JdbcUtils;
import java.sql.*;
import java.util.Date;
public class TestInsert {
public static void main(String[] args) {
Connection conn=null;
PreparedStatement pst=null;
try {
conn= JdbcUtils.getConnection();
//区别
//使用?占位符代替参数
String sql="insert into users(`id`,`name`,`password`,`email`,`birthday`)values(?,?,?,?,?)";
pst=conn.prepareStatement(sql); //预编译SQL,然后不执行。
//手动给参数赋值
pst.setInt(1,7);
pst.setString(2,"muyou");
pst.setString(3,"123455");
pst.setString(4,"45646514@qq.com");
//注意点:sql.Date 数据库 java.sql.Date()
// util.Date Java new 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{
JdbcUtils.release(conn,pst,null);
}
}
}
- 删除
package com.sun.lesson3;
import com.sun.lesson2.utils.JdbcUtils;
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 pst=null;
try {
conn= JdbcUtils.getConnection();
//区别
//使用?占位符代替参数
String sql="delete from users where id=?";
pst=conn.prepareStatement(sql); //预编译SQL,然后不执行。
//手动给参数赋值
pst.setInt(1,5);
//执行
int i= pst.executeUpdate();
if(i>0){
System.out.println("删除成功!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
JdbcUtils.release(conn,pst,null);
}
}
}
- 更新
package com.sun.lesson3;
import com.sun.lesson2.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
public class TestUpdate {
public static void main(String[] args) {
Connection conn=null;
PreparedStatement pst=null;
try {
conn= JdbcUtils.getConnection();
//区别
//使用?占位符代替参数
String sql="update users set `name`=? where `name`=?";
pst=conn.prepareStatement(sql); //预编译SQL,然后不执行。
//手动给参数赋值
pst.setString(1,"shijie");
pst.setString(2,"xiaocong");
//执行
int i= pst.executeUpdate();
if(i>0){
System.out.println("修改成功!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
JdbcUtils.release(conn,pst,null);
}
}
}
- 查询
package com.sun.lesson3;
import com.sun.lesson2.utils.JdbcUtils;
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 pst=null;
ResultSet rs =null;
try {
conn = JdbcUtils.getConnection();
String sql="SELECT `id`,`name`,`password` FROM users where id=?";
pst=conn.prepareStatement(sql);
pst.setInt(1,1); //传递参数
//执行
rs=pst.executeQuery();
while(rs.next()){
System.out.println("id="+rs.getObject("id"));
System.out.println("name="+rs.getObject("name"));
System.out.println("password="+rs.getObject("password"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,pst,rs);
}
}
}
- 防止SQL注入(使用PrepareStatement)
package com.sun.lesson2;
import com.sun.lesson2.utils.JdbcUtils;
import java.sql.*;
public class SQL注入2 {
public static void main(String[] args) {
login("''or1=1","123456");
}
public static void login(String username,String password){
Connection conn=null;
PreparedStatement pst=null;
ResultSet rs=null;
try {
conn=JdbcUtils.getConnection();
String sql="SELECT * FROM users WHERE `name`=? AND `password`=?";
pst=conn.prepareStatement(sql);
pst.setString(1,username);
pst.setString(2,password);
rs=pst.executeQuery();
while(rs.next()){
System.out.println("id="+rs.getObject("id"));
System.out.println("name="+rs.getObject("name"));
System.out.println("password="+rs.getObject("password"));
System.out.println("email="+rs.getObject("email"));
System.out.println("birthday="+rs.getObject("birthday"));
System.out.println("==========================================");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,pst,rs);
}
}
}