05—JDBC(1)

                                                      JDBC

数据准备:

create table user_info(
  id char(36) primary key,
  user_name varchar2(8) unique,
  password varchar2(10) not null,
  mobile char(11)
)


insert into user_info values ('9b4435ec-372c-456a-b287-e3c5aa23dff4','张三','admin','12345678901');
insert into user_info values ('a273ea66-0a42-48d2-a17b-388a2feea244','李%四','19840110','98765432130');
insert into user_info values ('eb0a220a-60ae-47b6-9e6d-a901da9fe355','张李三','869330','18338945560');
insert into user_info values ('6ab71673-9502-44ba-8db0-7f625f17a67d','王_五','775901','98765432130');
insert into user_info values ('0055d61c-eb51-4696-b2da-506e81c3f566','王_五%%','w1209','13856901237');
commit;

一、什么是JDBC:

JDBC全称为Java Database Connectivity,是一种使用Java代码连接数据库的技术。(注意:JDBC是Java代码用于Java程序与数据库连接)

二、JDBC的操作步骤:

1、加载驱动程序

a、Oracle:oracle.jdbc.driver.OracleDriver

b、MySQL:com.mysql.jdbc.Driver

try {
	Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
	e.printStackTrace();
}//代码示例

2、连接数据库

方法一:

String userName = "scott";//数据库用户名
String password = "root";//数据库密码
//Oracle数据库URL语法:jdbc:oracle:thin:@db_ip:db_port:db_name
String url="jdbc:oracle:thin:@192.168.30.212:1521:lanqiao";
Connection connection = DriverManager.getConnection(url, userName, password);

方法二:

String userName = "scott";
String password = "root";
Properties properties = new Properties();
properties.put("user",userName);//只能是user
properties.put("password", password);
properties.put("internal_logon", "sysdba");//sysdba身份登录数据库
//Oracle数据库URL语法:jdbc:oracle:thin:@db_ip:db_port:db_name
String url="jdbc:oracle:thin:@192.168.30.212:1521:lanqiao";
Connection connection=DriverManager.getConnection(url, properties);

3、创建Statement实例

Statement实例分为以下3种类型:    

a、执行静态SQL语句。通常通过Statement实例实现。

Statement statement = connection.createStatement();

b、执行动态SQL语句。通常通过PreparedStatement实例实现。

String sql = "select * from user_info where user_name like ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1, "张%");

c、执行数据库存储过程。通常通过CallableStatement实例实现。

String sql = "{call get_age(?,?)}";
CallableStatement callableStatement = connection.prepareCall(sql);
callableStatement.setString(1, "1984-01-10");//为问号占位符赋值callableStatement.registerOutParameter(2, oracle.jdbc.OracleTypes.NUMBER);//指定输出数据类型

注意:CallableStatement继承自PreparedStatement,而PreparedStatement继承自Statement

4、执行SQL语句

a、Statement

//修改操作
int result = statement.executeUpdate("delete from user_info where name like '%三%'");
//查询操作
ResultSet resultSet = statement.executeQuery("select * from user_info");

b、PreparedStatement

//修改操作
int result = preparedStatement.executeUpdate();
//查询操作
ResultSet resultSet = preparedStatement.executeQuery();

c、CallableStatement

callableStatement.execute();

5、处理结果

a、Statement

// 修改操作
if (result>0) {
	System.out.println("删除成功");
} else {
	System.out.println("删除失败");
}

// 查询操作
while(resultSet.next()) {//next用于移动指针并判断当前指针所指位置是否有数据
	String id=resultSet.getString("id");
	String nameName=resultSet.getString("user_name");
	String mobile = resultSet.getString("mobile");
	System.out.println(id+","+nameName+","+mobile);
}

b、PreparedStatement

//修改操作:
if (result>0) {
	System.out.println("删除成功");
} else {
	System.out.println("删除失败");
}

//查询操作:
while(resultSet.next()) {//next用于移动指针并判断当前指针所指位置是否有数据
	String id=resultSet.getString("id");
	String nameName=resultSet.getString("user_name");
	String mobile = resultSet.getString("mobile");
	System.out.println(id+","+nameName+","+mobile);
}

c、CallableStatement

int age = callableStatement.getInt(2);
System.out.println("年龄:"+age);

6、关闭JDBC对象,释放资源

try {
	if(resultSet!=null) {
		resultSet.close();//释放ResultSet类型对象
	}
} catch (SQLException e) {
	e.printStackTrace();
}
		
try {
	if(statement!=null) {
		statement.close();//释放CallableStatement、PreparedStatement或Statement类型对象CallableStatement继承自PreparedStatement,而PreparedStatement继承自Statement,参见close工程。
	}
} catch (SQLException e) {
	e.printStackTrace();
}
			
try {
	if(connection!=null) {
		connection.close();//释放Connection类型对象
	}
} catch (SQLException e) {
	e.printStackTrace();
}

三、完整JDBC示例:

a、Statement

查询:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Select {

	public static void main(String[] args) {
		try {
			//1、加载JDBC驱动程序
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			String url = "jdbc:oracle:thin:@192.168.30.212:1522:lanqiao";
			//2、获取数据库连接
			connection = DriverManager.getConnection(url, "scott", "root");
			//3、创建Statement实例
			statement = connection.createStatement();
			//4、执行SQL语句
			resultSet = statement.executeQuery("select * from user_info");
			//5、处理结果
			//next用于移动指针并判断当前指针所指位置是否有数据
			while(resultSet.next()) { 
				String id=resultSet.getString("id");
				String nameName=resultSet.getString("user_name");
				String mobile = resultSet.getString("mobile");
				System.out.println(id+","+nameName+","+mobile);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			//6、关闭JDBC对象,释放资源
			try {
				if(resultSet!=null) {
					resultSet.close();//释放ResultSet类型对象
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if(statement!=null) {
					statement.close();//释放Statement类型对象
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if(connection!=null) {
					connection.close();//释放Connection类型对象
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

修改:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Update {

	public static void main(String[] args) {
		try {
			//1、加载JDBC驱动程序
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		Connection connection = null;
		Statement statement = null;
		try {
			String url = "jdbc:oracle:thin:@192.168.1.18:1522:lanqiao";
			//2、获取数据库连接
			connection = DriverManager.getConnection(url, "scott", "root");
			//3、创建Statement实例
			statement = connection.createStatement();
			//4、执行SQL语句
			String sql="delete from user_info where user_name like '张%'";
			int result = statement.executeUpdate(sql);
			//5、处理结果
			if (result>0) {
				System.out.println("删除成功");
			} else {
				System.out.println("删除失败");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			//6、关闭JDBC对象,释放资源
			try {
				if(statement!=null) {
					statement.close();//释放Statement类型对象
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if(connection!=null) {
					connection.close();//释放Connection类型对象
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

b、PreparedStatement

查询:

import java.sql.*;

public class Select {

	public static void main(String[] args) {
		try {
			//1、加载JDBC驱动程序
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		Connection connection = null;
		PreparedStatement preparedStatement=null;
		ResultSet resultSet = null;
		try {
			String url = "jdbc:oracle:thin:@192.168.1.18:1522:lanqiao";
			//2、获取数据库连接
			connection = DriverManager.getConnection(url, "scott", "root");
			//3、创建Statement实例
			String userName="王_五";
			String password="775901";
			String sql = "select * from user_info where user_name=? and password= ?";
			preparedStatement = connection.prepareStatement(sql);
			//为?赋值
			preparedStatement.setObject(1, userName);
			preparedStatement.setObject(2, password);	
			//4、执行SQL语句
			resultSet = preparedStatement.executeQuery();
			//5、处理结果
			if(resultSet.next()) {//为什么不使用while循环——查询结果只有一条或0条数据
				System.out.println("登录成功");
			}else {
				System.out.println("登录失败");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			//6、关闭JDBC对象,释放资源
			try {
				if(resultSet!=null) {
					resultSet.close();//释放Statement类型对象
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if(preparedStatement!=null) {
					preparedStatement.close();//释放PreparedStatement类型对象
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if(connection!=null) {
					connection.close();//释放Connection类型对象
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

修改:

import java.sql.*;

public class Update {

	public static void main(String[] args) {
		try {
			//1、加载JDBC驱动程序
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		Connection connection = null;
		PreparedStatement preparedStatement=null;
		try {
			String url = "jdbc:oracle:thin:@192.168.1.18:1522:lanqiao";
			//2、获取数据库连接
			connection = DriverManager.getConnection(url, "scott", "root");
			//3、创建Statement实例
			String sql="delete from user_info where user_name like ?";
			preparedStatement = connection.prepareStatement(sql);
			//为?赋值
			preparedStatement.setObject(1, "张%");			
			//4、执行SQL语句
			int result = preparedStatement.executeUpdate();
			//5、处理结果
			if (result>0) {
				System.out.println("删除成功");
			} else {
				System.out.println("删除失败");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			//6、关闭JDBC对象,释放资源
			try {
				if(preparedStatement!=null) {
					preparedStatement.close();//释放Statement类型对象
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if(connection!=null) {
					connection.close();//释放Connection类型对象
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

c、CallableStatement

import java.sql.*;

public class Procedure {

	public static void main(String[] args) {
		try {
			//1、加载JDBC驱动程序
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		Connection connection = null;
		CallableStatement callableStatement = null;
		try {
			//2、获取数据库连接
			String url = "jdbc:oracle:thin:@192.168.30.212:1522:lanqiao";
			connection = DriverManager.getConnection(url, "scott", "root");
			//3、创建Statement实例
			String sql = "{call get_age(?,?)}";
	        	callableStatement = connection.prepareCall(sql);
	        	//为?赋值
	       	callableStatement.setString(1, "1984-01-10");//为问号占位符赋值
	        	int sqlType = oracle.jdbc.OracleTypes.NUMBER;
	        	callableStatement.registerOutParameter(2, sqlType);//指定输出数据类型	
                 //4、执行SQL语句
 	        	callableStatement.execute();
			    //5、处理结果
	        	int age = callableStatement.getInt(2);
	        	System.out.println("年龄:"+age);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			//6、关闭JDBC对象,释放资源
			try {
				if(callableStatement!=null) {
					callableStatement.close();//释放CallableStatement类型对象
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if(connection!=null) {
					connection.close();//释放Connection类型对象
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

四、SQL注入

SQL注入指通过将恶意SQL语句插入到特定SQL语句内,使特定SQL语句发生变化,最终达到欺骗数据库服务器使之执行恶意的SQL命令的一种方法

import java.sql.*;

public class Login {

	public static void main(String[] args) {
		try {
			//1、加载JDBC驱动程序
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			//2、获取数据库连接
			String url = "jdbc:oracle:thin:@192.168.30.212:1522:lanqiao";
			connection = DriverManager.getConnection(url, "scott", "root");
			//3、创建Statement实例
			statement = connection.createStatement();
			String userName="王_五";
			String password="' or '1'='1";
			String sql = "select * from user_info where user_name='"
				+userName+"'and password='"+password+"'";
			//4、执行SQL语句
			resultSet = statement.executeQuery(sql);
			//5、处理结果
			if(resultSet.next()) {//尽管密码错误了,依然可以登录成功
				System.out.println("登录成功");
			}else {
				System.out.println("登录失败");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			//6、关闭JDBC对象,释放资源
			try {
				if(resultSet!=null) {
					resultSet.close();//释放ResultSet类型对象
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if(statement!=null) {
					statement.close();//释放Statement类型对象
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if(connection!=null) {
					connection.close();//释放Connection类型对象
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

解决方法

import java.sql.*;

public class Login {

	public static void main(String[] args) {
		try {
			//1、加载JDBC驱动程序
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			//2、获取数据库连接
			String url = "jdbc:oracle:thin:@192.168.30.212:1522:hongqiao";
			connection = DriverManager.getConnection(url, "scott", "root");
			//3、创建Statement实例
			String sql = "select * from user_info where user_name=? and password=?";
			preparedStatement = connection.prepareStatement(sql);
			String userName="王_五";
			String password="' or '1'='1";
			preparedStatement.setObject(1, userName);//为问号占位符赋值
			preparedStatement.setObject(2, password);//为问号占位符赋值
			//4、执行SQL语句
			resultSet = preparedStatement.executeQuery();
			//5、处理结果
			if(resultSet.next()) {//密码错误,但由于使用了PreparedStatement语句,所以成功规避了SQL注入
				System.out.println("登录成功");
			}else {
				System.out.println("登录失败");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {//6、关闭JDBC对象,释放资源
			try {
				if(resultSet!=null) {
					resultSet.close();//释放ResultSet类型对象
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if(preparedStatement!=null) {
					preparedStatement.close();//释放PreparedStatement类型对象
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if(connection!=null) {
					connection.close();//释放Connection类型对象
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值