JDBC连接和释放连接的工具类:
public class JDBCCoonection {
private final static String DRIVE = "oracle.jdbc.OracleDriver";
private final static String URL ="jdbc:oracle:thin:@10.211.55.3:1521:orcl";
private final static String USER = "lee";
private final static String PASSWORD="lee";
private static Connection connection = null;
private final static Statement statement = null;
private final static PreparedStatement pst = null;
private final static ResultSet rs = null;
//获取connection连接
public static Connection getConnection() {
try {
Class.forName(DRIVE);//加载驱动
try {
connection = DriverManager.getConnection(URL, USER, PASSWORD);//获取连接
} catch (SQLException e) {
e.printStackTrace();
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return connection;
}
//关闭连接connection
public static void disConnection() throws SQLException {
if (rs!=null) {
rs.close();
}
if (statement !=null) {
statement.close();
}
if (pst !=null) {
pst.close();
}
if (connection != null) {
connection.close();
}
}
}
3.1 JDBC简介
JDBC(Java DataBase Connective),就是用Java代码操作数据库。
1) JDBC API
DBC API主要可以完成三件事:
①与数据库建立连接
②发送SQL语句
③返回处理结果
常用接口、类的介绍如下:
接口/类 | 简介 |
---|---|
DriverManager类 | 根据不同的数据库,管理相应的JDBC驱动。可以通过DriverManager类的getConnection()方法获取数据库连接。 |
Connection接口 | 由DriverManager产生,用于连接数据库并传递数据。 |
Statement接口 | 由Connection产生,用于执行增删改查等SQL语句。 |
PreparedStatement接口 | Statement的子接口(public interface PreparedStatement extends Statement{…}),同样是由Connection产生,同样用于执行增删改查等SQL语句。与Statement接口相比,具有高安全性(可以防止SQL注入等安全隐患)、高性能、高可读性和高可维护性的优点 |
CallableStatement接口 | PreparedStatement的子接口(public interface CallableStatement extends PreparedStatement {…}),同样是由Connection产生,用于调用并执行SQL存储过程或存储函数。 |
ResultSet接口 | 接收Statement或PreparedStatement执行查询操作后的结果集。 |
2) JDBC访问数据库的步骤
①导入JDBC驱动包,并加载驱动类
使用JDBC访问数据库前,需要先导入相应的驱动包(如oracle数据库的驱动包是ojdbc版本号.jar),之后再使用Class.forName()方法将具体的JDBC驱动类加载到Java虚拟机中,代码如下,
Class.forName("JDBC驱动类名");
如果指定的驱动类名不存在,则会引发ClassNotFoundException异常。
常见的JDBC驱动包、驱动类及访问连接如下表:
数据库 | JDBC驱动包 | JDBC驱动类 | 连接字符串 |
---|---|---|---|
Oracle | ojdbc版本号.jar | oracle.jdbc.OracleDriver | jdbc:oracle:thin:@localhost:1521:数据库实例名 |
MySQL | mysql-connector-java-版本号-bin.jar | com.mysql.cj.jdbc.Driver | jdbc:mysql://localhost:3306/数据库实例名 |
SqlServer | sqljdbc版本号.jar | com.microsoft.sqlserver.jdbc.SQLServerDriver | jdbc:microsoft:sqlserver://localhost:1433;databasename=数据库实例名 |
其中“连接字符串”中的1521、3306和1433分别是各个数据库的默认端口号。
②与数据库建立连接
JDBC使用DriverManager类来管理驱动程序,并通过getConnection()方法在数据库和相应的驱动程序之间建立起连接,如下,
Connection connection = DriverManager.getConnection("连接字符串","数据库用户名","数据库密码");
Connection接口的常用方法如下:
方法 | 简介 |
---|---|
Statement createStatement() throws SQLException | 创建Statement对象。 |
PreparedStatement prepareStatement(String sql) | 创建PreparedStatement对象。 |
③发送SQL语句,并获取执行结果
获得了Connection连接后,就可以通过Connection对象
来获得Statement
或PreparedStatement
对象,并通过该对象向数据库发送SQL语句。
如果SQL语句是增、删、改操作,则返回一个int
型结果,表示多少行受到了影响,即增、删、改了几条数据;
如果SQL语句是查询操作,则返回一个ResultSet
结果集,该结果集包含了SQL查询的所有结果。如下,
(1) Statement对象:
Statement stmt = connection.createStatement();//创建statement对象
int count = stmt.executeUpdate("增、删、改的SQL语句");//增、删、改
ResultSet rs = stmt.executeQuery("查询的SQL语句");//查询
Statement的常用方法:
方法 | 简介 |
---|---|
executeUpdate() | 用于执行INSERT、UPDATE 或 DELETE 语句,以及DDL(数据定义语言)语句,例如 CREATE TABLE 和 DROP TABLE。返回值是一个整数,表示受影响的行数(即涉及增删改的行数)。对于CREATE TABLE 或 DROP TABLE 等不操作行的语句,executeUpdate 的返回值总为零。 |
executeQuery() | 用于执行SELECT查询语句,返回值是一个ResultSet类型的结果集。 |
close() | 关闭Statement对象。 |
(2) PreparedStatement对象:
PreparedStatement pstmt = connection.prepareStatement("增、删、改、查的SQL语句");
int count = pstmt.executeUpdate();//增删改操作
ResultSet rs = pstmt.executeQuery();//查询
PreparedStatement的常用方法:
方法 | 简介 |
---|---|
executeUpdate() | 用法上,类似与Statement接口中的executeUpdate() |
executeQuery() | 用法上,类似与Statement接口中的executeQuery () |
setXxx() | 有setInt()、setString()、setDouble()等多个方法,用于给SQL中的占位符“?”赋值。setXxx()方法有两个参数,第一个参数表示占位符的位置(从1开始),第二个参数表示占位符所代表的具体值。例如可以将SQL写成“select * from student where name=? and age = ? ”,其中两个问号代表两个占位符,可以使用setString(1,”张三’’)和setInt(2,23)来分别为两个占位符赋值。 |
close() | 关闭PreparedStatement对象。 |
④处理返回结果集
如果是查询操作,可以通过循环取出结果集中的所有数据:先通过rs.next()获取每一行的数据,再通过rs.getXxx()获取行内的每一列数据,如下:
while(rs.next){
int stuNo = rs.getInt("stuNo");
String stuName = rs.getString("stuName");
…
}
ResultSet的常用方法:
方法 | 简介 |
---|---|
boolean next() | 将光标从当前位置向下移动一行,指向结果集中的下一行的数据。通常用来判断查询到的结果集中是否还有数据。如果有,则返回true,否则返回false。 |
boolean previous() | 将光标从当前位置向上移动一行。 |
int getInt(int columnIndex) | 获取当前结果集中指定列号的字段值,该指定列必须是整数类型的字段。例如,学生表中number类型的学号stuNo字段如果在第一列,就可以使用getInt(1)来获取值。除此之外,还有getFloat()、getString()、getDate()、getBinaryStream()等多个类似方法,用于获取不同类型的字段。 |
int getInt(String columnLabel) | 获取当前结果集中指定列名的字段值,该指定列必须是整数类型的字段。例如,学生表中number类型的学号stuNo字段,就可以使用getInt(“stuNo”)来获取值。除此之外,还有getFloat()、getString()、getDate()等多个类似方法,用于获取不同类型的字段。 |
void close() | 关闭ResultSet对象。 |
3) JDBC访问数据库的示例
数据库中存在一张学生表student,各字段名称及类型如下:
字段名 | 类型 | 含义 |
---|---|---|
stuNo | number(3) | 学号 |
stuName | varchar2(20) | 学生姓名 |
stuAge | number(3) | 学生年龄 |
①使用Statement操作数据库
增加:
@Test
public void insert() throws SQLException {
Connection connection = JDBCCoonection.getConnection();//获取连接
Statement statement = connection.createStatement();//生成statement
String Sql = "insert into student values(1,'张三',22)";
int update = statement.executeUpdate(Sql);
System.out.println("增加了"+update+"条数据");
JDBCCoonection.disConnection();
}
删除:
@Test
public void delete() throws SQLException {
Connection connection = JDBCCoonection.getConnection();//获取连接
Statement statement = connection.createStatement();//生成statement
String Sql = "delete from student where stuNo = 1 ";
int update = statement.executeUpdate(Sql);
System.out.println("删除了"+update+"条数据");
JDBCCoonection.disConnection();
}
修改:
@Test
public void update() throws SQLException {
Connection connection = JDBCCoonection.getConnection();//获取连接
Statement statement = connection.createStatement();//生成statement
String Sql ="update student set stuName = '小A' where stuNo = 1";
int update = statement.executeUpdate(Sql);
System.out.println("更新了"+update +"条数据");
JDBCCoonection.disConnection();
}
查询:
@Test
/**
* 发现:如果用if做遍历,只会返回第一条数据;所以必须用while遍历
* @throws SQLException
*/
public void query() throws SQLException {
Connection connection = JDBCCoonection.getConnection();//获取连接
Statement statement = connection.createStatement();//生成statement
String Sql = "select * from student";
ResultSet rs = statement.executeQuery(Sql);
while (rs.next()) {
int stuNo = rs.getInt("stuNo");
String stuName = rs.getString("stuName");
int stuAge = rs.getInt("stuAge");
System.out.println(stuNo+stuName+stuAge);
}
}
执行Junit测试,如图:
②使用PreparedStatement访问数据库
增加:
@Test
public void insert() throws SQLException {
Connection connection = JDBCCoonection.getConnection();//获取连接
String sql = "insert into student values(?,?,?)";
PreparedStatement pst = connection.prepareStatement(sql);//创建PreparedStatement对象
pst.setInt(1, 1);
pst.setString(2, "张三");
pst.setInt(3, 23);
int update = pst.executeUpdate();
System.out.println("增加了"+update+"条数据");
JDBCCoonection.disConnection();
}
删除:
@Test
public void delete() throws SQLException {
Connection connection = JDBCCoonection.getConnection();//获取连接
String sql = "delete from student where stuNo = ? ";
PreparedStatement pst = connection.prepareStatement(sql);//创建PreparedStatement对象
pst.setInt(1, 1);
int update = pst.executeUpdate();
System.out.println("删除了"+update+"条数据");
JDBCCoonection.disConnection();
}
修改:
@Test
public void update() throws SQLException {
Connection connection = JDBCCoonection.getConnection();//获取连接
String sql = "update student set stuName = ? where stuNo = ?";
PreparedStatement pst = connection.prepareStatement(sql);//创建PreparedStatement对象
pst.setString(1, "测试1");
pst.setInt(2, 2);
int update = pst.executeUpdate();
System.out.println("更新了"+update +"条数据");
JDBCCoonection.disConnection();
}
查询:
@Test
/**
* 发现:如果用if做遍历,只会返回第一条数据;所以必须用while遍历
* @throws SQLException
*/
public void query() throws SQLException {
Connection connection = JDBCCoonection.getConnection();//获取连接
String sql = "select stuNo,stuName,stuAge from student";
PreparedStatement pst = connection.prepareStatement(sql);//创建PreparedStatement对象
ResultSet rs = pst.executeQuery();
while (rs.next()) {
int stuNo = rs.getInt("stuNo");
String stuName = rs.getString("stuName");
int stuAge = rs.getInt("stuAge");
System.out.println(stuNo+stuName+stuAge);
}
}
③注意:
使用PreparedStatement进行模糊查询时,可以在setXxx()方法中加入通配符。例如,以下是根据String类型的name变量的模糊查询:
String querySql = "select * from student where stuName like ?" ;
pstmt.setString(1, "%" +name +"%");
rs = pstmt.executeQuery();
如果使用的是Statement,当需要给SQL语句拼接String类型变量时,必须加上单引号,例如”select * from student where stuName like '%"+stuName + "%'";
④资源关闭
为了及时的释放使用完毕的资源,需要在数据库访问结束时,调用各个对象的close()方法。
方法 | 立即释放的资源 |
---|---|
ResultSet接口的close()方法 | 此 ResultSet对象的数据库 JDBC资源 |
Statement接口的close()方法 | 此 Statement 对象的数据库 JDBC资源(包含ResultSet对象) |
Connection接口的close()方法 | 此Connection对象的数据库 JDBC 资源(包含ResultSet、 Statement对象) |
可以发现,三个资源之间存在包含关系,所以在编码时,释放资源的顺序应该写为:ResultSet对象–>Statement–>Connection对象。(也就是要倒着关闭)
此外,如果不及时的通过close()方法释放资源,已创建的Connection对象、Statement对象、ResultSet对象也会在GC执行垃圾回收时自动释放。但自动释放的方式会造成资源的释放不及时(必须等待GC回收),故不推荐。
⑤使用PreparedStatement的优势
现在我们知道,Statement和PreparedStatement都可以实现数据库的增删改查等操作。二者相比,PreparedStatement有如下优势:
1.提高了代码的可读性和可维护性
PreparedStatement可以避免了繁琐的SQL语句拼接操作。
例如,SQL语句“insert into student(stuNo,stuName,stuAge) values(5,‘王五’,25)”,
用Statement方式执行时,需要写成:
stmt.executeUpdate("insert into student(stuNo,stuName,stuAge ) values("+stuNo+",'"+stuName+"',"+stuAge+")");
而如果用PreparedStatement方式执行时,就可以先用?充当参数值的占位符,然后再用setXxx()方法设置?的具体值,避免了SQL语句的拼接操作。
2.提高了SQL语句的性能
创建Statement对象时不使用SQL语句做参数(如Statement stmt = connection.createStatement();),不会解析和编译SQL语句,而是每次调用executeUpdate()或executeQuery()方法时才进行SQL语句的解析和编译操作(如stmt.executeUpdate(“delete from student where stuno =5”);)。
而创建PreparedStatement对象时,是使用带占位符?的SQL语句作为参数(如PreparedStatement pstmt = connection.prepareStatement(“delete from student where stuName = ? and stuAge = ?”);),会预先解析和编译该SQL语句,之后通过setXxx()方法给占位符赋值,最后执行SQL语句时,就无需再解析和编译SQL语句,直接执行即可(如pstmt.executeUpdate())。这就使得,如果多次操作相同,就可以大大提高性能。即PreparedStatement是预编译的SQL语句对象。
3.提高了安全性,能有效防止SQL注入
使用PreparedStatement,传入的任何数据都不会和已经编译的SQL语句进行拼接,从而避免了SQL注入攻击。
例如,在使用Statement时,可能会用以下代码来进行登陆验证:
stmt = connection.createStatement();
String querySql = "select count(*) from login where username = '"+uname+"' and password = '"+upwd+"'" ;
rs = stmt.executeQuery (querySql);
…
if(rs.next()){
int result = rs.getInt("count(*)")
if(result>0) { //登陆成功}
else{//登陆失败}
}
这样的代码看起来没有问题,但试想如果用户输入uname的值是“任意值’ or 1=1–-upwd的值是“任意值”,则SQL语句拼接后的结果如下:
select count(*) from login where username = '任意值' or 1=1-- and password = '任意值'
SQL语句中,用“or 1=1” 使where条件永远成立,并且用“–-”将后面的SQL语句注释掉,这样就造成了安全隐患(SQL注入),使得并不存在的用户名和密码也能登录成功。而PreparedStatement中使用了占位符?以及setXxx()方法有效避免了这种漏洞。
综上,我们在实际开发中推荐使用PreparedStatement来操作数据库。
⑥处理CLOB/BLOB数据
实际开发中,经常会处理一些大文本数据(CLOB)或二进制数据(BLOB)。要想在数据库中读写CLOB或BLOB类型的数据,就必须使用PreparedStatement和IO流。
(1).读写CLOB数据
CLOB用于存放大文本数据。以下是将一篇小说(E:\ 幻城.txt)存入CLOB类型字段的具体步骤:
<1>创建myNovel表,并设置CLOB类型的字段novel,如下:
create table myNovel(
id number primary key,
novel clob
)
<2>将小说写入 myNovel表的novel字段(CLOB类型)
先将小说转为字符输入流,然后通过PreparedStatement的setCharacterStream ()方法写入数据库,如下,
WriteNovel.java
public class WriteNovel {
//将小说写入数据库
public static void writeNovelToClob() throws SQLException, FileNotFoundException, UnsupportedEncodingException {
Connection connection = JDBCCoonection.getConnection();//获取连接
String sql = "insert into myNovel(id,novel) values(?,?) ";
PreparedStatement pstmt = connection.prepareStatement(sql );
pstmt.setInt(1, 1);//id=1
//将小说转化为字符流,并设置编码格式为GBK
File file = new File("E:\\幻城.txt");
Reader reader = new InputStreamReader(new FileInputStream(file),"GBK");
//将字符输入流写入myNovel表
pstmt.setCharacterStream(2, reader,(int)file.length());
int result = pstmt.executeUpdate();
if (result>0) {
System.out.println("小说写入成功!");
}else {
System.out.println("小说写入失败!");
}
}
public static void main(String[] args) throws FileNotFoundException, SQLException, UnsupportedEncodingException {
writeNovelToClob();
}
}
其中,PrepareStatementCoonection为②中的工具类。
<3>读取数据库中的小说
通过ResultSet的getCharacterStream ()方法读取小说,然后通过IO流写入硬盘(src根目录),如下,
WriteNovel.java
public class ReadNovel {
public static void writeNovelToBlob() throws SQLException, IOException {
Connection connection = JDBCCoonection.getConnection();//获取连接
String sql = "select * from myNovel where id = ?";
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, 1);
ResultSet rs = pstmt.executeQuery();
while(rs.next()) {
Reader reader = rs.getCharacterStream("novel");
//通过io流将小说写入硬盘
FileWriter writer = new FileWriter("src/幻城.txt");
char[] temp = new char[200];
int length = -1;
while( (length = reader.read(temp) )!=-1) {
writer.write(temp,0,length);
}
writer.close();
reader.close();
System.out.println("小说读取成功!");
}
}
public static void main(String[] args) throws SQLException, IOException {
writeNovelToBlob();
}
}
说明:
MySQL数据库中没有CLOB类型,在MySQL用TEXT类型存储大文本数据。
(2).读写BLOB数据
BLOB用于存放图片、电影等二进制数据。以下是将图片(E:\ java.png)存入BLOB类型字段的具体步骤:
<1>创建myPicture表,并设置BLOB类型的字段img,如下:
create table myPicture(
id number(4) primary key,
img blob
)
<2>将图片写入 myPicture表的img字段(BLOB类型)
先将图片转为输入流,然后通过PreparedStatement的setBinaryStream()方法写入数据库,如下,
WirteImg.java
public class WirteImg {
/**
* 将图片转化为二进制格式,然后写入数据库
* @throws IOException
* @throws SQLException
*/
public static void wirteImgByclob() throws IOException, SQLException{
File file = new File("E:\\java.png");
FileInputStream in = new FileInputStream(file);//将文件转化为流的形式
Connection connection = JDBCCoonection.getConnection();//获取连接
String sql = "insert into myImg(id ,img) values (?,?) ";
PreparedStatement pstmt = connection.prepareStatement(sql);//获取preparestatement对象,并且预编译sql语句
pstmt.setInt(1, 1);//设置id为1
pstmt.setBinaryStream(2, in,(int)file.length());//设置图片流为in
int result = pstmt.executeUpdate();
if(result >0){
System.out.println("图片写入成功!");
}else{
System.out.println("图片写入失败!");
}
}
public static void main(String[] args) throws IOException, SQLException
{
wirteImgByclob();
}
}
ReadImg.java
public class ReadImg {
/**
* 从数据库中把图片读到硬盘
* @throws SQLException
* @throws IOException
*/
public static void readImgByBlog() throws SQLException, IOException {
Connection connection = JDBCCoonection.getConnection();//获取连接
String sql = "select id,img from myImg where id = ?";
PreparedStatement pstmt = connection.prepareStatement(sql );
pstmt.setInt(1, 1);//查询id为1的图片
ResultSet rs = pstmt.executeQuery();
while(rs.next()) {
InputStream imgin = rs.getBinaryStream("img");
InputStream in = new BufferedInputStream(imgin);//创建一个缓冲输入流,将输入流放在缓冲输入流中
OutputStream imgout = new FileOutputStream("src//mypic.png");//创建一个图片的输出路径
BufferedOutputStream out = new BufferedOutputStream(imgout);//创建一个缓冲输出流,将输出流放在缓冲输出流中
int length = -1;
while((length =in.read())!= -1) {
out.write(length);
}
out.close();
imgout.close();
in.close();
imgin.close();
System.out.println("图片读取成功!");
}
}
}
3.2 JSP访问数据库
在JSP中实现一个“用户注册”的功能。该功能写在WebContent目录下。
register.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>注册</title>
</head>
<body>
<form action="check.jsp">
用户名:<input type="text" name="username"></br>
密码:<input type="password" name="userpwd"></br>
<input type="submit" value="提交">
</form>
</body>
</html>
check.jsp
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>检查</title>
</head>
<body>
<%
String username = request.getParameter("username");//获取表单用户名
String password = request.getParameter("userpwd");//获取表单密码
Class.forName("oracle.jdbc.OracleDriver");//加载驱动
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@10.211.55.3:1521:orcl", "lee"," lee");//获取连接
String sql = "insert into t_user(username , password) values(?,?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, username);
pstmt.setString(2, password);
int result = pstmt.executeUpdate();
if(result == 1){
out.print("注册成功!");
}else{
out.print("注册失败!");
}
%>
</body>
</html>
运行以上代码,浏览器输入:http://localhost:8080/Register/register.jsp 就能实现用户注册功能,并将注册信息写入数据库。
点击提交后:
查看数据库:
需要注意,必须在page指令里导入Connection、PreparedStatement等的包名,如%@page import="java.sql.Connection"%;
并将数据库的驱动包加入Web工程,导入方法如下:将数据库驱动包(ojdbc7.jar)直接复制在WEB-INF下的lib文件夹中即可。