大刀l server访问mysql_第三章 数据库访问

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驱动类连接字符串Oracleojdbc版本号.jaroracle.jdbc.OracleDriverjdbc:oracle:thin:@localhost:1521:数据库实例名

MySQLmysql-connector-java-版本号-bin.jarcom.mysql.cj.jdbc.Driverjdbc:mysql://localhost:3306/数据库实例名

SqlServersqljdbc版本号.jarcom.microsoft.sqlserver.jdbc.SQLServerDriverjdbc: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，各字段名称及类型如下：

字段名类型含义stuNonumber(3)学号

stuNamevarchar2(20)学生姓名

stuAgenumber(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

pageEncoding="UTF-8"%>

注册

用户名：

密码：

check.jsp

pageEncoding="UTF-8"%>

检查

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("注册失败！");

}

%>

运行以上代码，浏览器输入：http://localhost:8080/Register/register.jsp 就能实现用户注册功能，并将注册信息写入数据库。

点击提交后：

查看数据库：

需要注意，必须在page指令里导入Connection、PreparedStatement等的包名，如%@page import="java.sql.Connection"%；并将数据库的驱动包加入Web工程，导入方法如下：将数据库驱动包(ojdbc7.jar)直接复制在WEB-INF下的lib文件夹中即可。