版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
一、什么是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
处理结果
释放资源
六、编码
测试用的数据库表
测试工程结构:
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
注入的问题
.
161
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
