4数据库之JDBC（orcle数据库）

最新推荐文章于 2023-11-17 13:29:40 发布

Blues_lin

最新推荐文章于 2023-11-17 13:29:40 发布

阅读量315

点赞数

分类专栏： Oracle

本文链接：https://blog.csdn.net/Blues_lin/article/details/81414885

版权

Oracle 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

需要执行的SQL语句：

创建表结构：

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步骤是什么？

1、加载JDBC驱动程序

a、Oracle：oracle.jdbc.driver.OracleDriver
b、MySQL：com.mysql.jdbc.Driver

try {
	Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
	e.printStackTrace();
}

获取数据库连接

a、第一种方式：

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);//指定输出数据类型

create procedure get_age(birthday char, age out number)
is
       birthday_year char(4);
       current_year char(4);
begin
       birthday_year:=substr(birthday,1,4);
       current_year:=to_char(sysdate,'yyyy');
       age :=to_number(current_year)-to_number(birthday_year);
end get_age;

注意：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类型对象
	}
} catch (SQLException e) {
	e.printStackTrace();
}
			
try {
	if(connection!=null) {
		connection.close();//释放Connection类型对象
	}
} catch (SQLException e) {
	e.printStackTrace();
}

别忘了：CallableStatement继承自PreparedStatement，而PreparedStatement继承自Statement，参见close工程。

三、完整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();
			}
		}
	}
}

为什么？

原因非常简单：用户将特殊“密码”输入后，使得原有SQL语句发生了质的变化：select * from user_info where user_name='王_五'and password='' or '1'='1'

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();
			}
		}
	}
}

五、事务

需要执行SQL语句

create table account(
  id char(36) primary key,
  card_id varchar2(20) unique,
  name varchar2(8) not null,
  money number(10,2) default 0
)
insert into account values('6ab71673-9502-44ba-8db0-7f625f17a67d','1234567890','张三',1000);
insert into account (id,card_id,name) values('9883a53d-9127-4a9a-bdcb-96cf87afe831','0987654321','张三');

代码：

import java.sql.*;

public class Trasaction {

	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 {
			//2、获取数据库连接
			String url = "jdbc:oracle:thin:@192.168.30.212:1522:lanqiao";
			connection = DriverManager.getConnection(url, "scott", "root");
			connection.setAutoCommit(false);//关闭默认自动事务提交
			//3、创建Statement实例
			statement = connection.createStatement();
			//4、执行SQL语句
			statement.executeUpdate("update account set money=money-100 where card_id= '1234567890'");
			statement.executeUpdate("update account set money=money100 where card_id= '0987654321'");
			connection.commit();//提交事务
		} catch (SQLException e) {
			try {
				connection.rollback();//撤销DML操作
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}finally {
			//6、关闭JDBC对象，释放资源
			try {
				if(statement!=null) {
					statement.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if(connection!=null) {
					connection.close();//close方法会提交事务
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

四、properties类型文件

上面代码我们将连接数据库的信息写在了Java源文件中，该源文件被编译成class类型的文件，如果后期我们需要修改连接数据库信息，则需要再次编译Java源文件，这种方式比较复杂，为了方便后期修改数据库连接信息，我们需要在src目录新建properties文件，然后使用Properties类读取该类型文件，具体操作如下：

1、在src目录创建properties类型文件，如下图所示：

db.properties中的代码如下：

db.user_name=scott

db.password=root

db.url=jdbc:oracle:thin:@192.168.30.212:1522:lanqiao

2、创建Properties类对象；

3、调用Properties类对象load方法加载properties类型文件；

4、调用Properties类对象getProperty方法获取properties类型文件中key所对应的数据；

package com.lq.db.test;

import java.io.IOException;
import java.io.InputStream;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class Login {
	
	static Properties properties = new Properties();//创建Properties类对象
	static {Login login = new Login();
		ClassLoader classLoader = login.getClass().getClassLoader();
		//如果properties文件在src根目录，则直接写该文件名即可
		InputStream inputStream = classLoader.getResourceAsStream("com/lq/db/db.properties");
		try {
			properties.load(inputStream);//加载properties类型文件
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args) {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		//通过调用getProperty方法获取properties类型文件中key所对应的数据
		String url = properties.getProperty("db.url");
		String userName = properties.getProperty("db.user_name");
		String password = properties.getProperty("db.password");
		try {
			DriverManager.getConnection(url, userName, password);
			System.out.println("数据库连接成功");
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("数据库连接失败");
		}
	}
}

五、JDBC工具类

IRowMapper接口

package com.lq.util;

import java.sql.ResultSet;

/**
 * 数据库数据映射接口
 *
 * @author 高焕杰
 */
public interface IRowMapper {

	/**
	 * 映射数据库数据抽象方法
	 * 
	 * @author 高焕杰
	 */
	void rowMapper(ResultSet rs);
}

DBUtil工具类

package com.lq.util;
import java.io.*;
import java.sql.*;
import java.util.Properties;

/**
 * 数据库连接工具类
 * 
 * @author GaoHuanjie
 */
public class DBUtil {
	
	static Properties properties = new Properties();//创建Properties类对象
	
	/**
	 * 加载数据库驱动
	 * 
	 * @author GaoHuanjie
	 */
	static{
		DBUtil login = new DBUtil();
		ClassLoader classLoader = login.getClass().getClassLoader();
		//如果properties文件在src根目录，则直接写该文件名即可
		InputStream inputStream = classLoader.getResourceAsStream("config/db.properties");
		try {
			properties.load(inputStream);//加载properties类型文件
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 获取数据库连接对象
	 * 
	 * @author 高焕杰
	 */
	private Connection getConnection() throws SQLException{ * 
	 * @author 高焕杰
	 */
	private Connection getConnection() throws SQLException{
		//Oracle数据库URL语法：jdbc:oracle:thin:@db_ip:db_port:db_name
		String url = properties.getProperty("db.url");
		String userName=properties.getProperty("db.user_name");//用户名
		String password=properties.getProperty("db.password");//密码
		return DriverManager.getConnection(url, userName, password);//与数据库建立连接
	}
	
	/**
	 * 修改数据，包括添加数据、删除数据和修改数据
	 * 
	 * @author 高焕杰
	 */
	public boolean update(String sql, Object...params){
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = getConnection();//与数据库建立连接
			//1、获取PrepareStatement对象
			preparedStatement = connection.prepareStatement(sql); 
			for (int i = 0; i < params.length; i++) { 
				preparedStatement.setObject(i+1, params[i]); //2、为问号赋值
			}
			//3、执行SQL语句，返回受影响的行数，如果没有受影响则返回0
			int result = preparedStatement.executeUpdate();
			if(result>0){
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			close(preparedStatement,connection);
		}
		return false;
	}

	/**
	 * 修改数据，包括添加数据、删除数据和修改数据
	 * 
	 * @author 高焕杰
	 */
	public boolean update(String sql){
		Connection connection = null;
		Statement statement = null;
		try {
			connection = getConnection();//与数据库建立连接
			statement = connection.createStatement();
			int result = statement.executeUpdate(sql);//返回受影响的行数，如果没有受影响则返回0
			if(result>0){
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			close(statement,connection);
		}
		return false;
	}

	/**
	 * 查询数据
	 * 
	 * @author 高焕杰
	 */
	public void select(String sql, IRowMapper rowMapper){
		Connection connection = null;
		Statement statement =null;
		ResultSet resultSet = null;
		try {
			connection = getConnection();//与数据库建立连接
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			rowMapper.rowMapper(resultSet);//接口回调
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			close(resultSet,statement,connection);
		}
	}

	/**
	 * 查询数据
	 * 
	 * @author 高焕杰
	 */
	public void select(String sql, Object [] params, IRowMapper rowMapper){
		Connection connection= null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection= getCResultSet resultSet = null;
		try {
			connection= getConnection();//与数据库建立连接
			preparedStatement = connection.prepareStatement(sql);//1、获取PrepareStatement对象
			for (int i = 0; i < params.length; i++) {
				preparedStatement.setObject(i+1, params[i]);//2、为问号赋值
			}
			resultSet = preparedStatement.executeQuery();//3、执行SQL语句
			rowMapper.rowMapper(resultSet);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			close(resultSet,preparedStatement,connection);
		}
	}
	
	/**
	 * 根据事务修改数据
	 * 
	 * @author 高焕杰
	 */
	public boolean transaction(String [] sqlArray){
		Connection connection = null;
		Statement statement = null;
		try {
			connection = getConnection();//与数据库建立连接
			connection.setAutoCommit(false);
			statement = connection.createStatement();
			for (String sql : sqlArray) {
				statement.executeUpdate(sql);
			}
			connection.commit();
			return true;
		} catch (SQLException e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}finally{
			close(statement,connection);
		}
		
		return false;
	} 
	
	/**
	 * 释放数据库资源
	 * 
	 * @author 高焕杰
	 */
	private void close(Statement statement,Connection connection){
		try {
			if (statement!=null) {
				statement.close();//如果传入的是PreparedStatement接口实现类对象，则此处出现接口回调，即在程序执行的时候实际执行的是PreparedStatement接口实现类中的close方法
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (connection!=null) {
				connection.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 释放数据库资源
	 * 
	 * @author 高焕杰
	 */
	private void close(ResultSet resultSet,Statement statement,Connection connection){
		try {
			if (resultSet!=null) {
				resultSet.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		close(statement,connection);
	}
}

Blues_lin

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
4数据库之JDBC（orcle数据库）

需要执行的SQL语句：创建表结构：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 va...
复制链接

扫一扫

专栏目录