JDBC
1、连接Oracle数据库
如果要连接数据库的话,则在进行开发之前必须首先准备出如下几个信息:
● 数据库的驱动程序:就是驱动程序配置包“包.类”;
└Oracle数据库:oracle.jdbc.driver.OracleDriver;
● 数据库的连接地址:不同的数据库有不同的连接地址;
└Oracle的连接地址:jdbc:oracle:thin:@192.168.0.105:1521:april;
● 数据库的用户名:scott;
● 数据库的密码:tiger;
以上这些信息准备好之后,下面就可以按照如下的步骤进行数据库的连接操作:
(1)加载类的驱动程序;
(2)通过DriverManager类取得一个Connection接口的对象,表示取得连接;
(3)进行若干个数据表的操作;
(4)关闭数据库,数据库操作属于资源操作,操作之后必须关闭;
范例:建立连接
package jdbc.oracle;
import java.sql.Connection; import java.sql.DriverManager;
public class JdbcDemo { public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver"; public static final String DBURL = "jdbc:oracle:thin:@192.168.0.105:1521:april"; public static final String DBUSER = "scott"; public static final String DBPASSWD = "tiger";
public static void main(String[] args) throws Exception { Connection conn = null; // 连接数据库接口 Class.forName(DBDRIVER); // 加载驱动程序 conn = DriverManager.getConnection(DBURL, DBUSER, DBPASSWD);// 取得连接 System.out.println(conn); conn.close(); // 关闭数据库连接 } } |
oracle.jdbc.driver.T4CConnection@75bf48de |
有非空结果返回输出,表示连接已成功,但是如果要连接超级用户sys,则需要在用户名后面添加“as sysdba”。
…… public static final String DBUSER = "sys as sysdba"; public static final String DBPASSWD = "oracle"; …… |
oracle.jdbc.driver.T4CConnection@72402ecb |
2、数据库操作:Statement(重点)
当取得了一个数据库连接对象之后,下面最为重要的就是要进行数据库的操作,数据库的操作使用Statement接口完成,但是如果要想取得此接口的实例化对象,必须依靠Connection的一个方法:
● 取得Statement接口对象:Statement createStatement()throws SQLException
而取得了Statement对象之后,下面就可以利用Statement接口的方法进行数据表的操作:
(1)数据的更新操作:int executeUpdate(String sql) throwsSQLException
(2)数据的查询操作:ResultSet executeQuery(Stringsql) throws SQLException
下面建立一张member表,通过程序进行表的CRUD操作。
范例:member表建立脚本
DROP SEQUENCE member_seq ; DROP TABLE member PURGE ; CREATE SEQUENCE member_seq ; CREATE TABLE member ( mid NUMBER PRIMARY KEY , name VARCHAR2(30) NOT NULL , age NUMBER(3) , birthday DATE , note CLOB ) ; |
2.1、插入操作
范例:增加数据
package jdbc.oracle;
import java.sql.Statement; import java.sql.Connection; import java.sql.DriverManager;
public class JdbcDemo { public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver"; public static final String DBURL = "jdbc:oracle:thin:@192.168.0.105:1521:april"; public static final String DBUSER = "scott"; public static final String DBPASSWD = "tiger";
public static void main(String[] args) throws Exception { Connection conn = null; Statement stmt = null; Class.forName(DBDRIVER); conn = DriverManager.getConnection(DBURL, DBUSER, DBPASSWD); stmt = conn.createStatement(); String sql = "INSERT INTO member(mid,name,age,birthday,note)" + "VALUES(member_seq.nextval,'Sam',22,TO_DATE('1990-01-12','yyyy-MM-dd'),'salesperson')"; int len = stmt.executeUpdate(sql); System.out.println("更新的行数:" + len); stmt.close(); conn.close(); } } |
更新的行数:1 |
注意,此时name和note都不能输入中文,否则插入的数据会不正常。待解决??!!
2.2、更新操作
package jdbc.oracle;
import java.sql.Statement; import java.sql.Connection; import java.sql.DriverManager;
public class JdbcDemo { public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver"; public static final String DBURL = "jdbc:oracle:thin:@192.168.0.105:1521:april"; public static final String DBUSER = "scott"; public static final String DBPASSWD = "tiger";
public static void main(String[] args) throws Exception { Connection conn = null; Statement stmt = null; Class.forName(DBDRIVER); conn = DriverManager.getConnection(DBURL, DBUSER, DBPASSWD); stmt = conn.createStatement(); String sql = "UPDATE member SET " + "name='abcde'" + " where mid=1"; int len = stmt.executeUpdate(sql); System.out.println("更新的行数:" + len); stmt.close(); conn.close(); } } |
以上操作并不能成功,待决解??!!
2.3、删除数据
package jdbc.oracle;
import java.sql.Statement; import java.sql.Connection; import java.sql.DriverManager;
public class JdbcDemo { public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver"; public static final String DBURL = "jdbc:oracle:thin:@192.168.0.105:1521:april"; public static final String DBUSER = "scott"; public static final String DBPASSWD = "tiger";
public static void main(String[] args) throws Exception { Connection conn = null; Statement stmt = null; Class.forName(DBDRIVER); conn = DriverManager.getConnection(DBURL, DBUSER, DBPASSWD); stmt = conn.createStatement(); String sql = "DELETE member WHERE mid=3"; int len = stmt.executeUpdate(sql); System.out.println("更新的行数:" + len); stmt.close(); conn.close(); } } |
更新的行数:1 |
2.4、查询数据
查询数据使用的语法是SELECT语句,但是在这里必须注意一点,此时的查询是需要将数据报表中的数据保存在程序的内存之中。
所以现在的关键就在于查询结果的ResultSet上了,因为所有的数据都要保存在此接口之中。
范例:查询全部数据
package jdbc.oracle;
import java.sql.ResultSet; import java.sql.Statement; import java.sql.Connection; import java.sql.DriverManager; import java.util.Date;
public class JdbcDemo { public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver"; public static final String DBURL = "jdbc:oracle:thin:@192.168.0.105:1521:april"; public static final String DBUSER = "scott"; public static final String DBPASSWD = "tiger";
public static void main(String[] args) throws Exception { Connection conn = null; Statement stmt = null; Class.forName(DBDRIVER); conn = DriverManager.getConnection(DBURL, DBUSER, DBPASSWD); stmt = conn.createStatement(); String sql = "SELECT mid,name,age,birthday,note FROM member"; ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { int mid = rs.getInt("mid");// 可换成rs.getInt(1) String name = rs.getString("name");// 可换成rs.getString(2) int age = rs.getInt(3); Date birthday = rs.getDate(4); String note = rs.getString(5); System.out.println(mid + "," + name + "," + age + "," + birthday + "," + note); } stmt.close(); conn.close(); } } |
1,So_Yeon,25,1987-10-05,cute 2,Ji_Yeon,19,1993-06-07,clean and pure |
3、PreparedStatement接口(核心)
PreparedStatement接口是Statement的子接口,使用此接口主要可以进行数据的预处理操作,在讲解PreparedStatement接口之前,首先来看一下如下的一个要求:
package jdbc.oracle;
import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement;
public class JdbcDemo { public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver"; public static final String DBURL = "jdbc:oracle:thin:@192.168.0.105:1521:april"; public static final String DBUSER = "scott"; public static final String DBPASSWD = "tiger";
public static void main(String[] args) throws Exception { Connection conn = null; Class.forName(DBDRIVER); Statement stmt = null; conn = DriverManager.getConnection(DBURL, DBUSER, DBPASSWD); stmt = conn.createStatement(); String name = "Hyo'Min";//此处添加了“‘”,所以会导致SQL语句拼凑错误! int age = 23; String birthday = "1989-05-30"; String note = "sexy"; String sql = "INSERT INTO member(mid,name,age,birthday,note)" + " VALUES(member_seq.nextval,'" + name + "'," + age + ",TO_DATE('" + birthday + "','yyyy-MM-dd'),'" + note + "')"; int len = stmt.executeUpdate(sql); System.out.println("更新的行数:" + len); stmt.close(); conn.close(); } } |
本程序执行的时候将出现如下的信息:
Thread [main] (Suspended (exception SQLException)) T4CStatement(OracleStatement).executeUpdate(String) line: 1573 JdbcDemo.main(String[]) line: 26 |
这种操作在开发之中无法避免!此时只能依照PreparedStatement接口完成,但是如果想要取得本接口的实例化对象则应该使用Connection接口的另外一个方法:
● 取得PreparedStatement接口对象:PreparedStatementprepareStatement(String sql) throws SQLException
而这个时候的SQL语句,由于要采用预处理的方式完成,所以使用每一个?表示占位符。
package jdbc.oracle;
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.text.SimpleDateFormat; import java.util.Date;
public class JdbcDemo { public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver"; public static final String DBURL = "jdbc:oracle:thin:@192.168.0.105:1521:april"; public static final String DBUSER = "scott"; public static final String DBPASSWD = "tiger";
public static void main(String[] args) throws Exception { Connection conn = null; Class.forName(DBDRIVER); PreparedStatement pstmt = null; String name = "Hyo'Min"; int age = 23; Date birthday = new Date(); birthday = new SimpleDateFormat("yyyy-MM-dd").parse("1989-05-30"); String note = "sexy"; String sql = "INSERT INTO member(mid,name,age,birthday,note)" + " VALUES(member_seq.nextval,?,?,?,?)"; conn = DriverManager.getConnection(DBURL, DBUSER, DBPASSWD); pstmt = conn.prepareStatement(sql); pstmt.setString(1, name); pstmt.setInt(2, age); pstmt.setDate(3, new java.sql.Date(birthday.getTime())); pstmt.setString(4, note); int len = pstmt.executeUpdate();// 执行更新时括号内不需要写sql System.out.println("更新的行数:" + len); conn.close(); } } |
更新的行数:1 |
在日常的操作之中,日期都使用java.util.Date表示,但是在SQL操作上日期就要使用java.sql.Date,那么就必须将java.util.Date变为java.sql.Date,但是不能直接利用转型,因为向下转型前必须先向上转型。
● java.util.Date方法:public long getTime()
● java.sql.Date构造:public Date(longdate)
既然使用PreparedStatement可以进行增加的操作,那么也可以进行数据查询的操作。
范例:查询全部数据
package jdbc.oracle;
import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Connection; import java.sql.DriverManager; import java.util.Date;
public class JdbcDemo { public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver"; public static final String DBURL = "jdbc:oracle:thin:@192.168.0.105:1521:april"; public static final String DBUSER = "scott"; public static final String DBPASSWD = "tiger";
public static void main(String[] args) throws Exception { Connection conn = null; PreparedStatement pstmt = null; Class.forName(DBDRIVER); conn = DriverManager.getConnection(DBURL, DBUSER, DBPASSWD); String sql = "SELECT mid,name,age,birthday,note FROM member"; pstmt = conn.prepareStatement(sql); ResultSet rs = pstmt.executeQuery(); while (rs.next()) { int mid = rs.getInt(1); String name = rs.getString(2); int age = rs.getInt(3); Date birthday = rs.getDate(4); String note = rs.getString(5); System.out.println(mid + "," + name + "," + age + "," + birthday + "," + note); } pstmt.close(); conn.close(); } } |
本程序唯一需要注意的就是由于在程序之中没有设置任何的“?”,所以不用使用PreparedStatement进行内容的设置。
范例:模糊查询
package jdbc.oracle;
import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Connection; import java.sql.DriverManager; import java.util.Date;
public class JdbcDemo { public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver"; public static final String DBURL = "jdbc:oracle:thin:@192.168.0.105:1521:april"; public static final String DBUSER = "scott"; public static final String DBPASSWD = "tiger";
public static void main(String[] args) throws Exception { Connection conn = null; PreparedStatement pstmt = null; Class.forName(DBDRIVER); String keyword = "Hyo"; conn = DriverManager.getConnection(DBURL, DBUSER, DBPASSWD); String sql = "SELECT mid,name,age,birthday,note FROM member" + " WHERE name LIKE ?"; pstmt = conn.prepareStatement(sql); pstmt.setString(1, "%" + keyword + "%"); ResultSet rs = pstmt.executeQuery(); while (rs.next()) { int mid = rs.getInt(1); String name = rs.getString(2); int age = rs.getInt(3); Date birthday = rs.getDate(4); String note = rs.getString(5); System.out.println(mid + "," + name + "," + age + "," + birthday + "," + note); } pstmt.close(); conn.close(); } } |
如果此时查询的时候没有指定任何的查询关键字,则表示查询全部。
范例:查询表中的记录数,如果统计一张表中的全部记录数使用COUNT()函数
package jdbc.oracle;
import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Connection; import java.sql.DriverManager;
public class JdbcDemo { public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver"; public static final String DBURL = "jdbc:oracle:thin:@192.168.0.105:1521:april"; public static final String DBUSER = "scott"; public static final String DBPASSWD = "tiger";
public static void main(String[] args) throws Exception { Connection conn = null; PreparedStatement pstmt = null; Class.forName(DBDRIVER); String keyword = "Hyo"; conn = DriverManager.getConnection(DBURL, DBUSER, DBPASSWD); String sql = "SELECT count(mid) FROM member" + " WHERE name LIKE ?"; pstmt = conn.prepareStatement(sql); pstmt.setString(1, "%" + keyword + "%"); ResultSet rs = pstmt.executeQuery(); if (rs.next()) { long count = rs.getLong(1); // 使用long型表示数据量 System.out.println("数据量是:" + count); } pstmt.close(); conn.close(); } } |
数据量是:4 |
曾经强调过,在使用COUNT()函数操作的时候即使表中没有记录了,也会返回数据是0。
范例:分页显示——使用ROWNUM结合子查询完成
package jdbc.oracle;
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet;
public class JdbcDemo { public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver"; public static final String DBURL = "jdbc:oracle:thin:@192.168.0.105:1521:april"; public static final String DBUSER = "scott"; public static final String DBPASSWD = "tiger";
public static void main(String[] args) throws Exception { Connection conn = null; Class.forName(DBDRIVER); PreparedStatement pstmt = null; int currentPage = 2; int lineSize = 5; String sql = "SELECT * FROM " + "(SELECT rownum rn,mid,name FROM member WHERE rownum<=?) temp " + "WHERE temp.rn>?"; conn = DriverManager.getConnection(DBURL, DBUSER, DBPASSWD); pstmt = conn.prepareStatement(sql); pstmt.setInt(1, currentPage * lineSize); pstmt.setInt(2, (currentPage - 1) * lineSize); ResultSet rs = pstmt.executeQuery(); while (rs.next()) { int rn = rs.getInt(1); int mid = rs.getInt(2); String name = rs.getString(3); System.out.println(rn + "\t" + mid + "\t" + name); } conn.close(); } } |
6 25 Ji_Yeon 7 51 Eun_Jung 8 52 Q-Ri 9 53 Bo Ram 10 54 Hwa_Young |
4、批操作及事务处理(理解)
在之前所学习到的内容实际上都属于JDBC 1.0的技术(在开发之中也就用到这些东西),JDBC的最新版本是4.0,但是基本上已经是无人问津,因为都使用Hibernate了。
但是在JDBC 2.0之后增加了几个功能:可滚动的结果集、使用结果集增加、修改、删除数据,这些都没人用,这些功能使用SQL语句最简单,但是在JDBC2.0之后有一个批处理的功能还稍微用点用,所谓的批处理指的是一次性进行数据表之中提交多条数据,下面为了说明问题以Statement接口操作为例,在此接口中提供了以下两个方法:
● 增加批处理:void addBatch(String sql)throws SQLException
● 执行批处理:int[] executeBatch()throws SQLException
范例:执行批处理
package jdbc.oracle;
import java.sql.Statement; import java.sql.Connection; import java.sql.DriverManager;
public class JdbcDemo { public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver"; public static final String DBURL = "jdbc:oracle:thin:@192.168.0.105:1521:april"; public static final String DBUSER = "scott"; public static final String DBPASSWD = "tiger";
public static void main(String[] args) throws Exception { Connection conn = null; Class.forName(DBDRIVER); Statement stmt = null; conn = DriverManager.getConnection(DBURL, DBUSER, DBPASSWD); stmt = conn.createStatement(); stmt.addBatch("insert into member(mid,name) values(member_seq.nextval,'Charlene_Choi')"); stmt.addBatch("insert into member(mid,name) values(member_seq.nextval,'Cyndi')"); stmt.addBatch("insert into member(mid,name) values(member_seq.nextval,'Hebe')"); stmt.executeBatch(); conn.close(); } } |