一、什么是jdbc:
jdbc是oracle公司指定的一套规范(一套接口)
二、jdbc作用:
连接数据库发送sql语句
处理结果
三、jdbc的组成:
java内置的2个包java.sql和javax.sql;
驱动:jdbc的实现类.由数据库厂商提供.例如MySql的驱动jar包
四、数据库URL
URL用于标识数据库的位置,通过URL地址可以让JDBC程序连接哪个数据库,URL的写法为:
常用数据库URL地址的写法:
Oracle写法:jdbc:oracle:thin:@localhost:1521:sid
MySql写法: jdbc:mysql://localhost:3306/sid
Mysql的url地址的简写形式: jdbc:mysql:///sid
常用属性:useUnicode=true&characterEncoding=UTF-8
sid:为数据库的名称
五、jdbc操作步骤:
1.数据库和表2.创建一个项目
3.导入驱动jar包
4.编码:
注册驱动
获取连接
编写sql
创建预编译的语句执行者
设置参数
执行sql
处理结果
释放资源
六、编码
测试用的数据库表
测试工程结构:
![](https://i-blog.csdnimg.cn/blog_migrate/a84afff848d018b0b6e1644edab89dee.png)
demo1:
package demo1;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Jdbctest {
public static void main(String[] args) {
Connection conn = null;
Statement statement = null;
ResultSet resultSet = null;
try {
//1.通过字节码对象注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获得连接对象
conn = DriverManager.getConnection("jdbc:mysql:///mydb1","root","root");
//3.通过conn对象获得向数据库发送sql语句的statement对象
statement = conn.createStatement();
//4.通过stmt对象执行sql语句,拿到执行的结果,返回给rs对象
resultSet = statement.executeQuery("select * from user");
//5.解析rs结果集中的数据
while(resultSet.next()) {
int id = resultSet.getInt("id") ;
String name = resultSet.getString("username");
System.out.println("id:"+id+" name:"+name);
}
//6.关闭资源
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
if(null !=resultSet)
try {
resultSet.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(null !=statement)
try {
statement.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(null !=conn)
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
demo2 jdbc连接工具类封装和CRUD操作
package utils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;
/**
* jdbc操作的工具类
* @author mChenys
*
*/
public class JdbcUtils {
private static String driverClass = null;
private static String url = null;
private static String username;
private static String password;
static {
//读取配置文件
ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
driverClass = bundle.getString("driverClass");
url = bundle.getString("url");
username = bundle.getString("username");
password = bundle.getString("password");
try {
//加载驱动
Class.forName(driverClass);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 获取mysql连接
* @return
*/
public static Connection getConnection() {
try {
return DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
/**
* 释放资源
* @param rs
* @param stmt
* @param conn
*/
public static void release(ResultSet rs, Statement stmt, Connection conn) {
if (null != rs)
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
rs = null;
}
if (null != stmt)
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
stmt = null;
}
if (null != conn)
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
conn = null;
}
}
public static void release(ResultSet rs, PreparedStatement stmt, Connection conn) {
if (null != rs)
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
rs = null;
}
if (null != stmt)
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
stmt = null;
}
if (null != conn)
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
conn = null;
}
}
}
package demo2;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import utils.JdbcUtils;
/**
* 测试CRUD操作
* @author mChenys
*
*/
public class JdbcCrud {
public static void main(String[] args) {
// testInsert();
// testDelete();
//testUpdate();
testQuery();
}
private static void testInsert() {
Connection conn = JdbcUtils.getConnection();
Statement stmt = null;
if (null != conn) {
try {
stmt = conn.createStatement();
// stmt.execute("insert into user(username) values('测试插入')");
stmt.executeUpdate("insert into user(username) values('测试插入2')");
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(null, stmt, conn);
}
}
}
private static void testDelete() {
Connection conn = JdbcUtils.getConnection();
Statement stmt = null;
if (null != conn) {
try {
stmt = conn.createStatement();
stmt.executeUpdate("delete from user where username='测试插入2'");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JdbcUtils.release(null, stmt, conn);
}
}
}
private static void testUpdate() {
Connection conn = JdbcUtils.getConnection();
Statement stmt = null;
if (null != conn) {
try {
stmt = conn.createStatement();
stmt.executeUpdate("update user set username='哈哈' where username='测试插入'");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JdbcUtils.release(null, stmt, conn);
}
}
}
private static void testQuery() {
Connection conn = JdbcUtils.getConnection();
Statement stmt = null;
ResultSet rs = null;
if (null != conn) {
try {
stmt = conn.createStatement();
rs= stmt.executeQuery("select * from user");
if(null !=rs) {
while(rs.next()) {
System.out.println("id:"+rs.getInt("id")+" name:"+rs.getString("username"));
}
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JdbcUtils.release(rs, stmt, conn);
}
}
}
}
jdbc.properties文件,必须放在src目录下
driverClass = com.mysql.jdbc.Driver
url = jdbc:mysql:///mydb1
username = root
password = root
demo3 PreparedStatement的使用
package demo3;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import utils.JdbcUtils;
/**
* PreparedStatement 的使用
* @author mChenys
*
*/
public class PrepareStmtTest {
public static void main(String[] args) {
// testInsert();
// testDelete();
// testUpdate();
testQuery();
}
private static void testInsert() {
Connection conn = JdbcUtils.getConnection();
PreparedStatement stmt = null;
if(null !=conn) {
try {
stmt = conn.prepareStatement("insert into user values(null,?)");
stmt.setString(1, "zhangs"); //替换?,索引从1开始
stmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(null, stmt, conn);
}
}
}
private static void testDelete() {
Connection conn = JdbcUtils.getConnection();
PreparedStatement stmt = null;
if(null !=conn) {
try {
stmt = conn.prepareStatement("delete from user where username=?");
stmt.setString(1, "zhangs"); //替换?,索引从1开始
stmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(null, stmt, conn);
}
}
}
private static void testUpdate() {
Connection conn = JdbcUtils.getConnection();
PreparedStatement stmt = null;
if(null !=conn) {
try {
stmt = conn.prepareStatement("update user set username=? where username=?");
stmt.setString(1, "呵呵");
stmt.setString(2, "哈哈");
stmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(null, stmt, conn);
}
}
}
private static void testQuery() {
Connection conn = JdbcUtils.getConnection();
PreparedStatement stmt = null;
ResultSet rs = null;
if(null !=conn) {
try {
stmt = conn.prepareStatement("select * from user");
rs= stmt.executeQuery();
if(null !=rs) {
while(rs.next()) {
System.out.println("id:"+rs.getString("id")+" name:"+rs.getString("username"));
}
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(rs, stmt, conn);
}
}
}
}
PreparedStatement是用于解决sql语句注入的问题
SQL
注入是用户利用某些系统没有对输入数据进行充分的检查,从而进行恶意破坏的行为。
1
、
statement
存在
sql
注入攻击问题
例如:登陆用户名输入时,采用
xxx
’
or
‘
1
’
=
‘
1
在数据库中就相当于执行了如下语句,导致
where
后面的条件是永真的结果。
select * from users where name='aaa' or
'1'='1' and password=''
2、
对于防范
SQL
注入,可以采用
PreparedStatement
取代
Statement
。
因为:通过
PreaparedStatement
对象
,
这个对象也是可以向数据库发送
sql
语句的
,
但是不同之处在于这个类对
sql
可以进行预编译
,
预编译了之后
,
再传入的特殊字符就不会当作特殊字符去处理
,
从而可以解决
sql
注入的问题
.