本文介绍了JDBC的使用，包括数据库驱动的概念、JDBC的作用、第一个JDBC程序的步骤，详细讲解了Statement对象和PreparedStatement对象的用法，强调了PreparedStatement在防止SQL注入方面的优势。
JDBC
1、数据库驱动
驱动：声卡、显卡、数据库……
我们的程序会通过数据库驱动和数据库打交道
2、JDBC
sun公司为了简化开发人员的（对数据库的统一）操作，提供了一个（Java操作数据库的规范），称为JDBC，这些规范的实现由具体的厂商去做
对于开发人员来说，只需要掌握JDBC接口的操作即可！
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,'zhangsan','123456','zs@qq.com','1999-09-8'),
(2,'lisi','123456','ls@qq.com','1996-06-24'),
(3,'wangwu','123456','ww@qq.com','1993-04-23');
1、创建一个普通项目
2、导入数据库驱动
3、编写测试代码
package com.vince;
import java.sql.*;
/**
* 我的第一个JDBC程序
* 1、加载驱动
* 2、用户信息和url
* 3、连接成功，返回一个数据库对象
* 4、执行SQL的对象
* 5、执行SQL的对象去执行SQL，可能存在结果，查看返回结果
* 6、释放连接
*/
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：设置他的中文字符集为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("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();
}
}
步骤总结：
1、加载驱动
2、连接数据库 DriverManager
3、获得执行sql的对象 Statement
4、获得返回的结果集
5、释放连接
DriverManager
// 1、加载驱动
// 不建议使用这种方式，因为这个Driver类当中有一个静态代码快执行了registerDriver要是用这个方式来，那么就会执行两次registerDriver()方法DriverManager.registerDriver(new com.mysql.jdbc.Driver());
// 这个才是固定写法
Class.forName("com.mysql.jdbc.Driver");
// connection代表数据库
/*
1、数据库设置自动提交
2、事务提交
3、事务回滚
connection.setAutoCommit();
connection.commit();
connection.rollback();
*/
Connection connection = DriverManager.getConnection(url, username, password);
URL
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSl=true";
// mysql的端口号默认为3306
// 协议://主机地址:端口号/数据库名?参数1&参数2&参数3
Statement zhi行sql的对象 PrepareStatement执行sql的对象
String sql = "SELECT * FROM users"; //执行sql
statement.excuteQuery(); //查询操作返回ResultSet
statement.excute(); //执行任何sql
statement.excuteUpdate(); //更新、插入、删除都用这个
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对象的excuteUpdate()方法用于向数据库发送增删改的sql语句，excuteUpdate执行完之后，将会返回一个整数（即增删改语句导致数据库几行数据发生了变化）
statement对象的excuteQuery()用于向数据库发送查询语句，excuteQuery方法返回代表查询结果的ResultSet对象
CRUD操作-create
使用excuteUpdate(String sql)方法完成数据添加操作，示例：
Statement st = conn.createStatement();
String sql = "insert into user(...) value(...)";
int num = st.executeUpdate(sql);
if(num>0){
System.out.println("插入成功！");
}
CRUD操作-delete
使用excuteUpdate(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
使用excuteUpdate(String sql)方法完成数据修改操作，示例：
Statement st = conn.createStatement();
String sql = "update from user set name='' where name = ''";
int num = st.executeUpdate(sql);
if(num>0){
System.out.println("修改成功！");
}
CRUD操作-read
使用excuteUpdate(String sql)方法完成数据添加操作，示例：
Statement st = conn.createStatement();
String sql = "select * from user where id=1";
ResultSet rs = st.excuteQuery(sql);
while(rs.next()){
//根据获取列的数据类型，分别调用rs的相应方法映射到java对象中
}
代码实现
//封装的工具类
package lesson02;
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 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 lesson02;
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 connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
statement = connection.createStatement();
String sql = "INSERT INTO users(id,`name`,`password`,`email`,`birthday`)" + "VALUES(4,'LingFeng','123456','3245642@qq.com','1999-08-04')";
int i = statement.executeUpdate(sql);
if (i > 0) {
System.out.println("插入成功！");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection, statement, resultSet);
}
}
}
//删除
package lesson02;
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 connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
statement = connection.createStatement();
String sql = "DELETE FROM users WHERE ID=4";
int i = statement.executeUpdate(sql);
if (i > 0) {
System.out.println("插入成功！");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection, statement, resultSet);
}
}
}
//更新
package lesson02;
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 connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
statement = connection.createStatement();
String sql = "UPDATE users SET ``name`='LingFeng',`email`='234654321@qq.com' WHERE id=1";
int i = statement.executeUpdate(sql);
if (i > 0) {
System.out.println("插入成功！");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection, statement, resultSet);
}
}
}
//查询
package lesson02;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestFind {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
statement = connection.createStatement();
String sql = "SELECT * FROM users where id=1";
resultSet = statement.executeQuery(sql);
while (resultSet.next()){
System.out.println(resultSet.getString("name"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection, statement, resultSet);
}
}
}
SQL注入的问题
sql存在漏洞，会被攻击导致数据泄露
package com.vince.lesson02;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class sqlzhuru {
public static void main(String[] args) {
login("LingFeng","123456");
login("'' or 1=1","123456");
}
public static void login(String username,String password){
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
statement = connection.createStatement();
String sql = "select * from users where `name`='"+username+"' and `password`='"+password+"'";
resultSet = statement.executeQuery(sql);
while (resultSet.next()){
System.out.println(resultSet.getString("name"));
System.out.println(resultSet.getString("password"));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
5、PreparedStatement对象
PreparedStatement可以防止sql注入，效果更好
1、增加
package com.vince.lesson03;
import com.vince.lesson02.JdbcUtils;
import java.sql.*;
import java.util.Date;
public class TestInsert {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JdbcUtils.getConnection();
//与Statement对象的区别
String sql = "INSERT INTO users(id,`name`,`password`,`email`,`birthday`) values(?,?,?,?,?)";
//预编译sql，先写sql，不执行
connection.prepareStatement(sql);
//手动给参数赋值
preparedStatement.setInt(1,4);
preparedStatement.setString(2,"qingqiang");
preparedStatement.setString(3,"1234532");
preparedStatement.setString(4,"2342344@qq.com");
/**
* 注意：
* sql.Date 数据库中
* java.Date java中
* new Date().getTime() 获得时间戳
*/
preparedStatement.setString(5, String.valueOf(
new java.sql.Date(new Date().getTime())));
//执行
int i = preparedStatement.executeUpdate();
if (i>0){
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection, preparedStatement, null);
}
}
}
2、删除
package com.vince.lesson03;
import com.vince.lesson02.JdbcUtils;
import java.sql.*;
public class TestDelete {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JdbcUtils.getConnection();
//与Statement对象的区别
String sql = "delete from users where id=?";
//预编译sql，先写sql，不执行
connection.prepareStatement(sql);
//手动给参数赋值
preparedStatement.setInt(1,4);
//执行
int i = preparedStatement.executeUpdate();
if (i>0){
System.out.println("删除成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection, preparedStatement, null);
}
}
}
3、更新
package com.vince.lesson03;
import com.vince.lesson02.JdbcUtils;
import java.sql.*;
public class TestUpdate {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JdbcUtils.getConnection();
//与Statement对象的区别
String sql = "update users set `name`=? where id=?";
//预编译sql，先写sql，不执行
connection.prepareStatement(sql);
//手动给参数赋值
preparedStatement.setString(1, "凌峰");
preparedStatement.setInt(2, 1);
//执行
int i = preparedStatement.executeUpdate();
if (i > 0) {
System.out.println("更新成功");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection, preparedStatement, null);
}
}
}
4、查询
package com.vince.lesson03;
import com.vince.lesson02.JdbcUtils;
import java.sql.*;
public class TestFind {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
//与Statement对象的区别
String sql = "select * from users where id=?";
//预编译sql，先写sql，不执行
connection.prepareStatement(sql);
//手动给参数赋值
preparedStatement.setInt(1,1);
//执行
resultSet = preparedStatement.executeQuery();
if (resultSet.next()){
System.out.println(resultSet.getString("name"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection, preparedStatement, resultSet);
}
}
}
5、注入
package com.vince.lesson03;
import com.vince.lesson02.JdbcUtils;
import java.sql.*;
public class sqlzhuru {
public static void main(String[] args) {
login("lisi","123456");
login("'' or 1=1","123456");
}
public static void login(String username,String password){
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
String sql = "select * from users where `name`=? and `password`=?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,username);
preparedStatement.setString(2,password);
//执行
resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
System.out.println(resultSet.getString("name"));
System.out.println(resultSet.getString("password"));
System.out.println("----------------------");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection, preparedStatement, resultSet);
}
}
}
扫一扫
1293
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
