JDBC

使用JDBC连接Oracle

1) 导包，导入JDBC驱动

com.oracle下的ojdbc14，可以从http://mvnrepository.com 下载jar包

导包：右击项目àBuild PathàConfigure Build PathàLibrariesàAdd External JARsà选择下载的jar包àApplyàOK

2) 注册JDBC驱动

-参数：“驱动程序类名”

-Class.forName(“驱动程序类名”)

3) 创建连接，获得Connection对象

-需要3个参数：url，username，password

-连接到数据库

4) 创建Statement(语句)对象

-conn.getStatement()方法创建对象，用于执行SQL语句

-execute(sql)：执行SQL，常用于执行DDL，DCL

-executeUpdate(sql)：执行DML语句，如insert、update、delete

-executeQuery(sql)：执行DQL语句，如select

5) 处理SQL结果集

-execute(ddl)：如果没有异常则成功

-executeUpdate(dml)：返回数字，表示更新”行”数量，抛出异常则失败

-executeQuery(dql)：返回ResultSet(结果集)对象，代表2维查询结果

6) 关闭数据库连接

-conn.close()

演示：JDBC连接数据库

//1.注册驱动，告诉DriverManager用这个类

Class.forName("oracle.jdbc.OracleDriver");

//2.创建连接

String url = "jdbc:oracle:thin:@10.1.1.100:1521:orcl";

String user = "scott";

String password = "tiger";

Connection conn = DriverManager.getConnection(url, user, password);

//输出conn引用对象的实际类型，证明：驱动程序提供了Connection接口的实现类

System.out.println(conn.getClass());

//3.创建Statement(语句)对象

Statement smt = conn.createStatement();

//4.执行sql，获取结果集

String sql = "SELECT * FROM emp WHERE ename='SMITH' ";

ResultSet rs = smt.executeQuery(sql);

//5.处理结果集

while(rs.next()){

System.out.println(rs.getInt("empno")+

rs.getString("ename"));

}

//6.关闭数据库

conn.close();

演示：使用工具类获取连接

创建一个工具类，获取访问数据库的操作。将数据库配置写在一个properties属性文件里，工具类读取属性文件，逐行获取数据库参数。

1.创建db.properties

注意：properties属性文件里不要使用中文

src/main/resourcesàdb.properties

2.创建工具类，src/main/java/util/DBTool.java

package util;

import java.io.IOException;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.util.Properties;

public class DBTool {

private static String url;

private static String user;

private static String pwd;

static{

try {

//1.创建Properties对象

Properties p = new Properties();

//2.加载配置文件

p.load(DBTool.class.getClassLoader().getResourceAsStream("db.properties"));

String driver = p.getProperty("jdbc.driver");

url = p.getProperty("url");

user = p.getProperty("user");

pwd = p.getProperty("password");

//3.注册驱动

Class.forName(driver);

} catch (IOException e) {

//4.异常处理

/*

* 异常处理原则：

* 1.记录日志

* 2.能处理则处理，具体参考开发规范

* 3.不能处理则抛出

*/

e.printStackTrace();

throw new RuntimeException("加载db.properties失败!,e");

} catch (ClassNotFoundException e) {

e.printStackTrace();

throw new RuntimeException("找不到驱动类",e);

}

}

//4.创建连接方法

public static Connection getConnection() throws SQLException{

return DriverManager.getConnection(url,user,pwd);

}

//5.创建关闭连接方法

public static void close(Connection conn){

try {

if(conn != null){

conn.close();

}

} catch (SQLException e) {

e.printStackTrace();

throw new RuntimeException("关闭连接失败!,e");

}

}

}

3.创建测试类，进行测试，src/test/java/test/TestCase.java

package test;

import java.io.IOException;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Properties;

import org.junit.Test;

import util.DBTool;

public class TestCase {

/**

* 1.JUnit可以让类中每个方法单独执行

* 2.对方法的要求：

* -方法是共有的

* -没有返回值

* -没有参数

* -前面必须写@Test注解

* 3.JUnit用来测试，而测试代码在正式WEB项目里不需要，

* 会连同JUnit包一起丢弃，所以JUnit包不用maven导入也可。

*/

/**

* 使用Properties读取db.properties

* 1.此类本质上是Map

* 2.专门用来读取properties文件

*/

@Test

public void test1(){

Connection conn = null;

Properties p = new Properties();

//获取ClassLoader并通过它从classes下读取db.properties

try {

p.load(TestCase.class.getClassLoader().getResourceAsStream("db.properties"));

System.out.println(p.getProperty("jdbc.driver"));

System.out.println(p.getProperty("url"));

System.out.println(p.get("user"));

System.out.println(p.getProperty("password"));

} catch (IOException e) {

e.printStackTrace();

}

}

/**

* 测试DBTool工具类

*/

@Test

public void test2(){

Connection conn = null;

try {

conn = DBTool.getConnection();

System.out.println(conn.getClass());

} catch (SQLException e) {

e.printStackTrace();

}finally{

DBTool.close(conn);

}

}

/**

* 执行DDL语句

*/

@Test

public void test3(){

//创建连接

Connection conn = null;

try {

conn = DBTool.getConnection();

//创建Statement对象

Statement smt = conn.createStatement();

//执行sql

String sql = "CREATE TABLE testtab("

+ "id NUMBER(8),"

+ "name VARCHAR2(50),"

+ "salary NUMBER(8,2) )";

boolean b = smt.execute(sql);

/*

* 返回结果：

* false:表示没有结果集

* true:表示有结果集

* 创建失败则抛出异常

*/

System.out.println(b); //false

} catch (SQLException e) {

e.printStackTrace();

}finally{

DBTool.close(conn);

}

}

/**

* 执行DML语句

*/

@Test

public void test4(){

//创建连接

Connection conn = null;

try {

conn = DBTool.getConnection();

//创建Statement对象

Statement smt = conn.createStatement();

String sql = "INSERT INTO testtab VALUES("

+ "2,'Jerry',2000.5)";

//返回DML语句影响的行数

int rows = smt.executeUpdate(sql);

System.out.println(rows);

} catch (SQLException e) {

e.printStackTrace();

}finally{

DBTool.close(conn);

}

}

/**

* 执行DQL语句

*/

@Test

public void test5(){

//创建连接

Connection conn = null;

try {

conn = DBTool.getConnection();

//创建Statement对象

Statement smt = conn.createStatement();

//执行sql

String sql = "SELECT * FROM testtab";

/*

* 返回结果集ResultSet，里面封装了多行多列的数据

* 该对象采用了迭代器模式，通常用while进行遍历

*/

ResultSet rs = smt.executeQuery(sql);

while(rs.next()){

/*

* 游标默认处于第一行之前

* 每次遍历就可以从rs获取下一行数据

* rs.get类型(字段名)

* rs.get类型(字段序号)

*/

System.out.println(rs.getInt("id")+","+rs.getString("name")+","+rs.getDouble("salary"));

}

} catch (SQLException e) {

e.printStackTrace();

}finally{

DBTool.close(conn);

}

}

}

使用Apache DBCP连接池连接数据库

1） 导包：需要两个jar包

-commons-dbcp-1.4.jar：连接池的实现

-commons-pool-1.5.jar：连接池实现的依赖库

http://maven.aliyun.com搜索：dbcp

复制坐标代码到pom.xml后保存

maven自动添加了两个jar包

2） 配置db.properties属性文件，添加初始连接数和最大连接数(可选)

3） 通过Properties读取db.properties属性文件

Properties p = new Properties();

p.load(DBUtil.class.getClassLoader().getResourceAsStream("db.properties"));

String driver = p.getProperty("jdbc.driver");

String url = p.getProperty("url");

String user = p.getProperty("user");

String pwd = p.getProperty("password");

String initsize = p.getProperty("initsize");

String maxsize = p.getProperty("maxsize");

4） 创建连接池

BasicDataSource ds = new BasicDataSource();

5） 设置连接池参数

ds.setDriverClassName(driver);

ds.setUrl(url);

ds.setUsername(user);

ds.setPassword(pwd);

ds.setInitialSize(Integer.parseInt(initsize));

ds.setMaxActive(Integer.parseInt(maxsize));

6） 创建数据库连接

Connection conn = ds.getConnection();

演示：通过连接池连接数据库

1.创建工具类utilàDBUtil

package util;

import java.io.IOException;

import java.sql.Connection;

import java.sql.SQLException;

import java.util.Properties;

import org.apache.commons.dbcp.BasicDataSource;

public class DBUtil {

private static BasicDataSource ds;

static{

//加载参数

Properties p = new Properties();

try {

p.load(DBUtil.class.getClassLoader().getResourceAsStream("db.properties"));

String driver = p.getProperty("jdbc.driver");

String url = p.getProperty("url");

String user = p.getProperty("user");

String pwd = p.getProperty("password");

String initsize = p.getProperty("initsize");

String maxsize = p.getProperty("maxsize");

//创建连接池

ds = new BasicDataSource();

//设置参数

ds.setDriverClassName(driver);

ds.setUrl(url);

ds.setUsername(user);

ds.setPassword(pwd);

ds.setInitialSize(Integer.parseInt(initsize));

ds.setMaxActive(Integer.parseInt(maxsize));

} catch (IOException e) {

e.printStackTrace();

throw new RuntimeException("加载db.properties失败!",e);

}

}

//创建连接方法

public static Connection getConnection() throws SQLException{

return ds.getConnection();

}

/**

* 目前连接是由连接池创建的，连接的实现类是由连接池提供的，

* 连接池将连接对象的close方法改为归还连接的逻辑。

*/

public static void close(Connection conn){

try {

if(conn != null){

conn.close(); //归还连接到数据库连接池

}

} catch (SQLException e) {

e.printStackTrace();

throw new RuntimeException("关闭连接失败!",e);

}

}

}

2.添加测试类进行测试

package test;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.sql.Statement;

import org.junit.Test;

import util.DBUtil;

public class TestCase2 {

/**

* 测试DBUtil

*/

@Test

public void test1(){

Connection conn = null;

try {

conn = DBUtil.getConnection();

System.out.println(conn);

} catch (SQLException e) {

e.printStackTrace();

}finally{

DBUtil.close(conn);

}

}

/**

* 使用DBUtil创建连接，并执行一个update语句

*/

@Test

public void test2(){

int id = 2;

String name = "Andy";

Connection conn = null;

try {

conn = DBUtil.getConnection();

Statement smt = conn.createStatement();

String sql = "UPDATE testtab SET "

+ "name='"+name+"'WHERE id="+id;

int rows = smt.executeUpdate(sql);

System.out.println(rows);

} catch (SQLException e) {

e.printStackTrace();

}finally{

DBUtil.close(conn);

}

}

/**

* 使用PreparedStatement执行DML语句

*/

@Test

public void test3(){

//删除name=Andy的行

String name = "Andy";

Connection conn = null;

try {

conn = DBUtil.getConnection();

String sql = "DELETE FROM testtab "

+ "WHERE name=?";

PreparedStatement ps = conn.prepareStatement(sql);

/*

* 给？赋值，将值暂存到PS里，由PS一次性发送给数据库

* ps.set类型(?的序号，?的值)，序号从1开始

*/

ps.setString(1, name);

//发送?的值，让数据库执行计划

ps.executeUpdate();

} catch (SQLException e) {

e.printStackTrace();

}

}

/**

* 使用PS执行DQL语句

*/

@Test

public void test4(){

//查询name=TOM的数据

String name = "TOM";

Connection conn = null;

try {

conn = DBUtil.getConnection();

String sql = "SELECT * FROM testtab "

+ "WHERE name=?";

PreparedStatement ps = conn.prepareStatement(sql);

ps.setString(1, name);

ResultSet rs = ps.executeQuery();

while(rs.next()){

System.out.println(rs.getInt("id")+","+rs.getString("name")+","+rs.getDouble("salary"));

}

} catch (SQLException e) {

e.printStackTrace();

}finally{

DBUtil.close(conn);

}

}

/**

* 使用PS执行查询，模拟登陆，

* 测试传入不正经密码时，会不会登陆成功

* 以此证明PS能防止SQL注入攻击

* 数据库中插入如下表做验证：

* CREATE TABLE logincheck(

name VARCHAR2(50),

password VARCHAR2(50)

);

INSERT INTO logincheck VALUES('zhangsan','a');

COMMIT;

*/

@Test

public void test5(){

//假设传入账号密码如下:

String name = "zhangsan";

String password = "a' OR 'b'='b";

Connection conn = null;

try {

conn = DBUtil.getConnection();

String sql = "SELECT * FROM logincheck "

+ "WHERE name=?"

+ "AND password=?";

PreparedStatement ps = conn.prepareStatement(sql);

ps.setString(1, name);

ps.setString(2, password);

ResultSet rs = ps.executeQuery();

//因为只有一行数据，所以rs.next()一次即可，存在说明登陆成功

if(rs.next()){

System.out.println("登陆成功!");

}else{

System.out.println("登陆失败!");

}

//登陆失败!

} catch (SQLException e) {

e.printStackTrace();

}finally{

DBUtil.close(conn);

}

}

/**

* 同样的，测试使用Statement传入以上sql查看执行结果

* 以此证明Statement不能防止sql注入攻击

*/

@Test

public void test6(){

String name = "zhangsan";

String password = "a' OR 'b'='b";

Connection conn = null;

try {

conn = DBUtil.getConnection();

String sql = "SELECT * FROM logincheck "

+ "WHERE name='"+name+"'AND password ='"+password+"'";

Statement smt = conn.createStatement();

ResultSet rs = smt.executeQuery(sql);

if(rs.next()){

System.out.println("登陆成功!");

}else{

System.out.println("登陆失败!");

}

//登陆成功!

} catch (SQLException e) {

e.printStackTrace();

}

}

/**

* ResultSetMetaData

*/

@Test

public void test7(){

Connection conn = null;

try {

conn = DBUtil.getConnection();

String sql = "SELECT * FROM logincheck";

Statement smt = conn.createStatement();

ResultSet rs = smt.executeQuery(sql);

//获取结果集元数据

ResultSetMetaData rsm = rs.getMetaData();

System.out.println(rsm.getColumnCount()); //2

System.out.println(rsm.getColumnName(1)); //NAME

System.out.println(rsm.getColumnTypeName(1)); //VARCHAR2

} catch (SQLException e) {

e.printStackTrace();

}finally{

DBUtil.close(conn);

}

}

/**

* 模拟实现转账的业务：

* 假设：当前用户已经登陆了网银，并且输入了收款方的账号

* 以及转账的金额，点击开始转账。

*

* 转账的步骤：

* 1.查询付款方账号，看余额够不够

* 2.查询收款方账号，是否正确

* 3.修改付款方账号余额-N元

* 4.修改收款方账号余额+N元

*

* 注意：转账是一个完整的业务，要保证在一个事务之内

* 所以只创建一个连接

*

* 在数据库中添加表

*create table accounts (

id varchar2(20),

name varchar2(30),

money number(11,2)

);

insert into accounts values('00001','张三',9000.0);

insert into accounts values('00002','李四',4000.0);

commit;

*/

@Test

public void test8(){

//假设付款方为李四，收款方为张三，转账金额为1000

String payID = "00002";

String recID = "00001";

double mny = 1000;

//建立数据库连接

Connection conn = null;

try {

conn = DBUtil.getConnection();

//执行sql查询余额是否足够

String sql = "SELECT * FROM accounts WHERE id=?";

PreparedStatement ps = conn.prepareStatement(sql);

ps.setString(1, payID);

ResultSet rs = ps.executeQuery();

Double payMny = null;

while(rs.next()){

payMny = rs.getDouble("money");

//打桩

System.out.println("付款方支付前余额"+payMny);

}

if(payMny<mny){

System.out.println("余额不足!");

return;

}

//查询收款账号是否存在

sql = "SELECT * FROM accounts WHERE id=?";

ps.setString(1, recID);

rs = ps.executeQuery();

if(!rs.next()){

System.out.println("收款方账号不存在");

return;

}

double recMny = rs.getDouble("money");

System.out.println("收款方收款前余额"+recMny);

//修改付款方账户余额

sql = "UPDATE accounts SET money=? "

+ "WHERE id=?";

ps = conn.prepareStatement(sql);

ps.setDouble(1, payMny-mny);

ps.setString(2, payID);

ps.executeUpdate();

/*

int x = Integer.parseInt("df");

try {

if(x != 5){

throw new Exception("模拟错误");

}

} catch (Exception e) {

e.printStackTrace();

}

程序有bug，这样的情况，会出现支付方钱扣了，但是收款方钱没增加的情况。

*/

//修改收款方账户余额

sql = "UPDATE accounts SET money=? "

+ "WHERE id=?";

ps = conn.prepareStatement(sql);

ps.setDouble(1, recMny+mny);

ps.setString(2, recID);

ps.executeUpdate();

//查询最后表数据

sql = "SELECT * FROM accounts";

ps = conn.prepareStatement(sql);

rs = ps.executeQuery();

while(rs.next()){

System.out.println(rs.getString("id")+","+rs.getString("name")+","+rs.getDouble("money"));

}

} catch (SQLException e) {

e.printStackTrace();

}finally{

DBUtil.close(conn);

}

}

}

演示：连接池线程处理

package util;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.Statement;

/**

* 用于测试数据库连接池的测试

* 连接池初始创建1个连接，最大2个连接

* 创建3个线程调用数据库连接，观察连接池工作原理

*/

public class DBCPThread {

public static void main(String[] args){

Thread t1 = new DemoThread(5000);

Thread t2 = new DemoThread(6000);

Thread t3 = new DemoThread(1000);

t1.start();

t2.start();

t3.start();

}

}

class DemoThread extends Thread{

private int wait;

public DemoThread(int wait) {

this.wait = wait;

}

@Override

public void run() {

Connection conn = null;

try{

conn = DBUtil.getConnection();

System.out.println("获取了数据库连接"+wait);

String sql = "SELECT 'dbcp' AS a FROM dual";

Statement st = conn.createStatement();

ResultSet rs = st.executeQuery(sql);

while(rs.next()){

System.out.println(rs.getString("a")+wait);

}

Thread.sleep(wait);

System.out.println("连接结束了"+wait);

}catch(Exception e){

e.printStackTrace();;

}finally{

DBUtil.close(conn);

}

}

}

运行结果：

获取了数据库连接5000

获取了数据库连接6000

dbcp5000

dbcp6000

连接结束了5000

获取了数据库连接1000

dbcp1000

连接结束了6000

连接结束了1000

演示：重写转账程序

SQL：

create table accounts (

id varchar2(20),

name varchar2(30),

money number(11,2)

);

insert into accounts values('00001','张三',9000.0);

insert into accounts values('00002','李四',4000.0);

commit;

转账程序：

package util;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

public class PayDemo {

public static void main(String[] args) {

Pay("00001","00002",1000);

}

/*

* 转账方法

*/

public static void Pay(String payID,String recID,double mny){

//转账SQL

String sql1 = "UPDATE accounts SET money=money+? "

+ "WHERE id=?";

String sql2 = "SELECT money FROM accounts "

+ "WHERE id=?";

//创建连接

Connection conn = null;

try {

conn = DBUtil.getConnection();

//关闭自动事务提交

conn.setAutoCommit(false);

//创建PS对象

PreparedStatement ps = conn.prepareStatement(sql1);

//转账操作

//设置参数

ps.setDouble(1, -mny);

ps.setString(2, payID);

int n = ps.executeUpdate();

if(n!=1){

throw new Exception("转账失败!");

}

//收款操作

//设置参数

ps.setDouble(1,mny);

ps.setString(2, recID);

n = ps.executeUpdate();

if(n != 1){

throw new Exception("收款失败!");

}

//查询余额

ps = conn.prepareStatement(sql2);

ps.setString(1, payID);

ResultSet rs = ps.executeQuery();

while(rs.next()){

double balance = rs.getDouble("money");

if(balance < 0){

throw new Exception("余额不足!");

}

}

//完成操作提交事务

conn.commit();

} catch (Exception e) {

e.printStackTrace();

if(conn != null){

try {

conn.rollback();

} catch (SQLException e1) {

e1.printStackTrace();

}

}

}finally{

DBUtil.close(conn);

}

}

}

运行结果：

运行Pay("00003","00002",1000)结果：

java.lang.Exception: 转账失败!

at util.PayDemo.Pay(PayDemo.java:40)

at util.PayDemo.main(PayDemo.java:12)

运行Pay("00001","00003",1000)结果：

java.lang.Exception: 收款失败!

at util.PayDemo.Pay(PayDemo.java:48)

at util.PayDemo.main(PayDemo.java:12)

运行Pay("00001","00002",10000)结果：

java.lang.Exception: 余额不足!

at util.PayDemo.Pay(PayDemo.java:57)

at util.PayDemo.main(PayDemo.java:12)

优化：

catch (Exception e) {

e.printStackTrace();

if(conn != null){

try {

conn.rollback();

} catch (SQLException e1) {

e1.printStackTrace();

}

}

这里conn.rollback()也需要做异常处理，可以和conn.close(conn)一样，在DBUtil里面写一个静态回滚方法。

DBUtil类下添加方法：

public static void rollback(Connection conn) {

if(conn != null){

try {

conn.rollback();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

修改PayDemo里面的catch:

catch (Exception e) {

e.printStackTrace();

DBUtil.rollback(conn);

}

演示：Statement批量更新

/*

* Statement批量更新

* 创建3张表

*/

@Test

public void test1(){

String sql1 = "CREATE TABLE batchtest1("

+ "id NUMBER(8)，"

+ "name VARCHAR2(50)"

+ ")";

String sql2 = "CREATE TABLE batchtest2("

+ "id NUMBER(8)，"

+ "name VARCHAR2(50)"

+ ")";

String sql3 = "CREATE TABLE batchtest3("

+ "id NUMBER(8)，"

+ "name VARCHAR2(50)"

+ ")";

Connection conn = null;

try {

conn = DBUtil.getConnection();

Statement smt = conn.createStatement();

smt.addBatch(sql1);

smt.addBatch(sql2);

smt.addBatch(sql3);

int[] arr = smt.executeBatch();

System.out.println(Arrays.toString(arr));

} catch (Exception e) {

e.printStackTrace();

}finally{

DBUtil.close(conn);

}

}

演示：PreparedStatement批量更新

/**

* batchtest1表里面批量增加108条数据

* 每50条批量增加一次

*/

@Test

public void test2(){

/*

* 类似于导入这样的功能，是一个完整的业务，所以只需要一个事务

*/

String sql = "INSERT INTO batchtest1 VALUES(?,?)";

Connection conn = null;

try {

conn = DBUtil.getConnection();

conn.setAutoCommit(false);

PreparedStatement ps = conn.prepareStatement(sql);

for(int i=1;i<=108;i++){

ps.setInt(1, i);

ps.setString(2, "好汉"+i+"号");

//每一条数据都暂存到ps上

ps.addBatch();

if(i%50==0){

//每存50条就提交一次

ps.executeBatch();

//清空数据以便下一次提交

ps.clearBatch();

}

}

//避免有剩余的不满50条的数据，再次提交

ps.executeBatch();

conn.commit();

} catch (Exception e) {

e.printStackTrace();

DBUtil.rollback(conn);

}finally{

DBUtil.close(conn);

}

}

演示：返回自动主键

/*

* JDBC自动返回主键

*/

@Test

public void test3(){

//生成一个自动序列

String sql = "CREATE SEQUENCE batchtest_seq";

String sql2 = "INSERT INTO batchtest1 VALUES(batchtest_seq.NEXTVAL,?)";

Connection conn = null;

try {

//创建数据库连接

conn = DBUtil.getConnection();

//关闭自动事务

conn.setAutoCommit(false);

//创建smt

Statement smt = conn.createStatement();

//提交sql

smt.execute(sql);

//创建ps

PreparedStatement ps = conn.prepareStatement(sql2,new String[]{"id"});

ps.setString(1, "SMITH");

ps.executeUpdate();

//获取主键

ResultSet rs = ps.getGeneratedKeys();

if(rs.next()){

int id = rs.getInt(1);

System.out.println("id="+id);

}

conn.commit();

} catch (Exception e) {

e.printStackTrace();

DBUtil.rollback(conn);

}finally{

DBUtil.close(conn);

}

}

运行结果：

1

演示：分页查询员工

ORACLE分页公式：

int begin = (page-1)*pageSize+1

int end = begin+pageSize-1

/**

* 分页查询

*/

@Test

public void test4(){

//假设用户点击了第2页

int page = 2;

//假设需求规定了每页显示5条数据

int size = 5;

Connection conn = null;

try {

conn = DBUtil.getConnection();

String sql = "SELECT * FROM ("

+ "SELECT e.*,ROWNUM rn FROM ("

+ " SELECT * FROM emp ORDER BY empno"

+ ") e"

+ ") WHERE rn BETWEEN ? AND ?";

System.out.println(sql);

//打桩

PreparedStatement ps = conn.prepareStatement(sql);

//起始行

ps.setInt(1, (page-1)*size+1);

//终止行

ps.setInt(2, page*size);

ResultSet rs = ps.executeQuery();

while(rs.next()){

System.out.println(rs.getInt("empno")+","+rs.getString("ename"));

}

} catch (Exception e) {

e.printStackTrace();

}finally{

DBUtil.close(conn);

}

}

演示：封装DAO

1.准备的SQL：

create table emps (

empno number(8) primary key,

ename varchar(20),

job varchar(20),

mgr number(8),

hiredate date,

sal number(11,2),

comm number(11,2),

deptno number(8)

);

create sequence emps_seq;

insert into emps values(emps_seq.nextval,'张三','领导',0,sysdate,18000.0,3000.0,1);

insert into emps values(emps_seq.nextval,'李四','销售',1,sysdate,7000.0,5000.0,1);

insert into emps values(emps_seq.nextval,'王五','销售',1,sysdate,8000.0,2000.0,1);

insert into emps values(emps_seq.nextval,'马六','市场',1,sysdate,6000.0,0,1);

insert into emps values(emps_seq.nextval,'周七','市场',1,sysdate,5000.0,0,1);

insert into emps values(emps_seq.nextval,'冯八','市场',1,sysdate,4000.0,0,1);

commit;

2.创建emps实现类

src/main/java/entity/Emp.java

package entity;

import java.io.Serializable;

import java.sql.Date;

/**

* 1.尽量使用封装类型

* 2.使用java.sql下的日期类型

* 3.属性和表里面的列名一致

* 4.java Bean满足如下规范

* -必须有包

* -必须有无参构造

* -必须实现序列化结构

* -通常有get和set方法

*/

public class Emp implements Serializable{

private Integer empno;

private String ename;

private String job;

private Integer mgr;

private Date hiredate;

private Double sal;

private Double comm;

private Integer deptno;

public Emp() {

}

public Emp(Integer empno, String ename, String job, Integer mgr, Date hiredate, Double sal, Double comm,

Integer deptno) {

super();

this.empno = empno;

this.ename = ename;

this.job = job;

this.mgr = mgr;

this.hiredate = hiredate;

this.sal = sal;

this.comm = comm;

this.deptno = deptno;

}

public Integer getEmpno() {

return empno;

}

public void setEmpno(Integer empno) {

this.empno = empno;

}

public String getEname() {

return ename;

}

public void setEname(String ename) {

this.ename = ename;

}

public String getJob() {

return job;

}

public void setJob(String job) {

this.job = job;

}

public Integer getMgr() {

return mgr;

}

public void setMgr(Integer mgr) {

this.mgr = mgr;

}

public Date getHiredate() {

return hiredate;

}

public void setHiredate(Date hiredate) {

this.hiredate = hiredate;

}

public Double getSal() {

return sal;

}

public void setSal(Double sal) {

this.sal = sal;

}

public Double getComm() {

return comm;

}

public void setComm(Double comm) {

this.comm = comm;

}

public Integer getDeptno() {

return deptno;

}

public void setDeptno(Integer deptno) {

this.deptno = deptno;

}

@Override

public String toString() {

return "Emp [empno=" + empno + ", ename=" + ename + ", job=" + job + ", mgr=" + mgr + ", hiredate=" + hiredate

+ ", sal=" + sal + ", comm=" + comm + ", deptno=" + deptno + "]";

}

}

3.封装DAO

src/main/java/dao/EmpDao.java

package dao;

import java.io.Serializable;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;

import entity.Emp;

import util.DBUtil;

public class EmpDAO implements Serializable{

/**

* 新增一个员工

*/

public void save(Emp e){

String sql = "INSERT INTO emps VALUES (emps_seq.NEXTVAL,?,?,?,?,?,?,?)";

Connection conn = null;

try {

conn = DBUtil.getConnection();

PreparedStatement ps = conn.prepareStatement(sql);

ps.setString(1, e.getEname());

ps.setString(2, e.getJob());

ps.setInt(3, e.getMgr());

ps.setDate(4, e.getHiredate());

ps.setDouble(5, e.getSal());

ps.setDouble(6, e.getComm());

ps.setInt(7, e.getDeptno());

ps.executeUpdate();

System.out.println("新增一名员工成功!");

} catch (Exception e1) {

e1.printStackTrace();

throw new RuntimeException("新增员工失败!",e1);

}finally{

DBUtil.close(conn);

}

}

/**

* 根据ID修改一个员工

*/

public void update(Emp e){

String sql = "UPDATE emps SET "

+ "ename=?,"

+ "job=?,"

+ "mgr=?,"

+ "hiredate=?,"

+ "sal=?,"

+ "comm=?,"

+ "deptno=?"

+ "WHERE empno=?";

Connection conn = null;

try {

conn = DBUtil.getConnection();

PreparedStatement ps = conn.prepareStatement(sql);

ps.setString(1, e.getEname());

ps.setString(2, e.getJob());

ps.setInt(3, e.getMgr());

ps.setDate(4, e.getHiredate());

ps.setDouble(5, e.getSal());

ps.setDouble(6, e.getComm());

ps.setInt(7, e.getDeptno());

ps.setInt(8, e.getEmpno());

ps.executeUpdate();

System.out.println("修改成功!");

} catch (Exception e2) {

e2.printStackTrace();

throw new RuntimeException("修改员工失败!",e2);

}finally{

DBUtil.close(conn);

}

}

/**

* 根据ID删除一个员工

*/

public void delete(int id){

String sql = "DELETE FROM emps WHERE empno=?";

Connection conn = null;

try {

conn = DBUtil.getConnection();

PreparedStatement ps = conn.prepareStatement(sql);

ps.setInt(1, id);

ps.executeUpdate();

System.out.println("删除成功!");

} catch (Exception e) {

e.printStackTrace();

throw new RuntimeException("删除失败!",e);

}finally{

DBUtil.close(conn);

}

}

/**

* 根据ID查询一个员工

*/

public Emp findById(int id){

String sql = "SELECT * FROM emps WHERE empno=?";

Connection conn = null;

try {

conn = DBUtil.getConnection();

PreparedStatement ps = conn.prepareStatement(sql);

ps.setInt(1, id);

ResultSet rs = ps.executeQuery();

if(rs.next()){

Emp emp = new Emp();

emp.setEmpno(rs.getInt("empno"));

emp.setEname(rs.getString("ename"));

emp.setJob(rs.getString("job"));

emp.setMgr(rs.getInt("mgr"));

emp.setHiredate(rs.getDate("hiredate"));

emp.setSal(rs.getDouble("sal"));

emp.setComm(rs.getDouble("comm"));

emp.setDeptno(rs.getInt("deptno"));

return emp;

}

} catch (Exception e) {

e.printStackTrace();

throw new RuntimeException("查询失败!",e);

}finally{

DBUtil.close(conn);

}

return null;

}

/**

* 查询所有员工

*/

public List<Emp> findAll(){

List<Emp> list = new ArrayList<Emp>();

String sql = "SELECT * FROM emps ORDER BY empno";

Connection conn = null;

try {

conn = DBUtil.getConnection();

Statement smt = conn.createStatement();

ResultSet rs = smt.executeQuery(sql);

while(rs.next()){

Emp emp = new Emp();

emp.setEmpno(rs.getInt("empno"));

emp.setEname(rs.getString("ename"));

emp.setJob(rs.getString("job"));

emp.setMgr(rs.getInt("mgr"));

emp.setHiredate(rs.getDate("hiredate"));

emp.setSal(rs.getDouble("sal"));

emp.setComm(rs.getDouble("comm"));

emp.setDeptno(rs.getInt("deptno"));

list.add(emp);

}

System.out.println("查询成功!");

return list;

} catch (Exception e) {

e.printStackTrace();

throw new RuntimeException("查询失败!",e);

}finally{

DBUtil.close(conn);

}

}

/**

* 查询某一页员工数据

*/

public List<Emp> findByPage(int page,int size){

String sql = "SELECT * FROM ("

+ " SELECT e.* ,ROWNUM r FROM("

+ " SELECT * FROM emps ORDER BY empno"

+ " ) e"

+ ") WHERE r BETWEEN ? AND ?";

Connection conn = null;

try {

conn = DBUtil.getConnection();

PreparedStatement ps = conn.prepareStatement(sql);

ps.setInt(1, (page-1)*size+1);

ps.setInt(2, page*size);

ResultSet rs = ps.executeQuery();

List<Emp> list = new ArrayList<Emp>();

while(rs.next()){

Emp emp = new Emp();

emp.setEmpno(rs.getInt("empno"));

emp.setEname(rs.getString("ename"));

emp.setJob(rs.getString("job"));

emp.setMgr(rs.getInt("mgr"));

emp.setHiredate(rs.getDate("hiredate"));

emp.setSal(rs.getDouble("sal"));

emp.setComm(rs.getDouble("comm"));

emp.setDeptno(rs.getInt("deptno"));

list.add(emp);

}

return list;

} catch (Exception e) {

e.printStackTrace();

throw new RuntimeException("查询失败!",e);

}finally{

DBUtil.close(conn);

}

}

/**

* 查询某部门内的员工

*/

public List<Emp> findByDept(int deptno){

String sql = "SELECT * FROM emps WHERE deptno=?";

Connection conn = null;

try {

conn = DBUtil.getConnection();

PreparedStatement ps = conn.prepareStatement(sql);

ps.setInt(1, deptno);

ResultSet rs = ps.executeQuery();

List<Emp> list = new ArrayList<Emp>();

while(rs.next()){

Emp emp = new Emp();

emp.setEmpno(rs.getInt("empno"));

emp.setEname(rs.getString("ename"));

emp.setJob(rs.getString("job"));

emp.setMgr(rs.getInt("mgr"));

emp.setHiredate(rs.getDate("hiredate"));

emp.setSal(rs.getDouble("sal"));

emp.setComm(rs.getDouble("comm"));

emp.setDeptno(rs.getInt("deptno"));

list.add(emp);

}

return list;

} catch (Exception e) {

e.printStackTrace();

throw new RuntimeException("查询失败!",e);

}finally{

DBUtil.close(conn);

}

}

}

4.测试类

package test;

import java.sql.Date;

import java.util.List;

import org.junit.Test;

import dao.EmpDAO;

import entity.Emp;

public class TestCase4 {

/**

* 测试新增一名员工

*/

@Test

public void test1(){

Date date = new Date(System.currentTimeMillis());

System.out.println(date);

EmpDAO dao = new EmpDAO();

Emp e = new Emp();

e.setEname("李白");

e.setJob("诗人");

e.setMgr(10);

e.setHiredate(date);

e.setSal(5000.0);

e.setComm(3000.0);

e.setDeptno(10);

dao.save(e);

}

/**

* 根据ID查询员工信息

*/

@Test

public void test2(){

EmpDAO dao = new EmpDAO();

Emp e = dao.findById(9);

System.out.println(e);

}

/**

* 根据id修改员工信息

*/

@Test

public void test3(){

EmpDAO dao = new EmpDAO();

//先查出旧的数据，然后修改

Emp e = dao.findById(8);

System.out.println(e);

e.setJob("诗仙");

dao.update(e);

System.out.println(dao.findById(8));

}

/**

* 根据ID删除一个员工

*/

@Test

public void test4(){

new EmpDAO().delete(8);

}

/**

* 查询所有员工

*/

@Test

public void test5(){

List<Emp> list = new EmpDAO().findAll();

for(Emp e:list){

System.out.println(e);

}

}

/**

* 查询某一页员工

*/

@Test

public void test6(){

List<Emp>list = new EmpDAO().findByPage(1, 3);

for(Emp e:list){

System.out.println(e);

}

}

/**

* 查询某部门员工

*/

@Test

public void test7(){

List<Emp>list = new EmpDAO().findByDept(1);

for(Emp e:list){

System.out.println(e);

}

}

}