最后有分页、批处理等代码文件
01 DB.properties 创建数据库配置文件
className=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@localhost:1521:orcl
username=scott
password=tiger
02 DB.java 创建数据库工具类
/**
* 2016年5月31日上午10:45:35
*/
package d530JDBC.util;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.Scanner;
public class DB {
// 1 加载驱动
static Properties properties = new Properties();
static {
try {
properties.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("DB.properties"));
Class.forName(properties.getProperty("className"));
=============
dbs.load( new FileInputStream("D:/WorkSpace55/JDBC2016527/src/DBConfig.properties") );
InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream( fileName );
is = DBUtils.class.getClassLoader().getResourceAsStream("DBConfig.properties");
=============
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
// 2 获取连接
public static Connection getCon() {
Connection con = null;
try {
con = DriverManager.getConnection(properties.getProperty("url"), properties.getProperty("username"),
properties.getProperty("password"));
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
// 3获取会话stmt
public static Statement getStmt(Connection con) {
Statement stmt = null;
try {
stmt = con.createStatement();
} catch (SQLException e) {
e.printStackTrace();
}
return stmt;
}
// 3获取会话pstmt
public static PreparedStatement getPstmt(Connection con, String sql) {
PreparedStatement pstmt = null;
try {
pstmt = con.prepareStatement(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return pstmt;
}
// 4 获取结果集stmt
public static ResultSet getResultSet(Statement stmt, String sql) {
ResultSet rs = null;
try {
rs = stmt.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
// 4获取结果集pstmt
public static ResultSet getResultSet(PreparedStatement pstmt) {
ResultSet rs = null;
try {
rs = pstmt.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
// 5关闭
public static void closeAll(ResultSet rs, Statement stmt, Connection con) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static String Input(String msg) {
System.out.println(msg);
return new Scanner(System.in).nextLine();
}
}
03 User.java 创建实体类
/**
* 2016年5月31日下午12:38:16
*/
package d530JDBC.po;
import java.util.Date;
public class User {
private int id;
private String name;
private String password;
private Date regdate;
public User() {
}
public User(int id, String name, String password, Date regdate) {
super();
this.id = id;
this.name = name;
this.password = password;
this.regdate = regdate;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Date getRegdate() {
return regdate;
}
public void setRegdate(Date regdate) {
this.regdate = regdate;
}
@Override
public String toString() {
return "用户编号" + id + " 名称：" + name + " 密码" + password;
}
}
04 UserDAO.java 创建数据访问对象
/**
* 2016年5月31日下午12:42:22
*/
package d530JDBC.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import d530JDBC.po.User;
import d530JDBC.util.DB;
public class UserDAO {
public void addUser(User user, Connection con) {
String sql = "insert into tb_users values(?,?,?,?)";
PreparedStatement pstmt = DB.getPstmt(con, sql);
try {
// 设置编号
while (true) {
user.setId((int) (Math.random() * 1000) + 1000);
if (check(1, user, con)) {
pstmt.setInt(1, user.getId());
break;
}
}
// 设置姓名
while (true) {
user.setName(DB.Input("请输入姓名:"));
if (check(2, user, con)) {
pstmt.setString(2, user.getName());
break;
}
System.out.print("用户名已存在，");
}
// 设置密码
user.setPassword(DB.Input("输入密码"));
pstmt.setString(3, user.getPassword());
// 设置注册时间
user.setRegdate(new Date());
pstmt.setDate(4, new java.sql.Date(user.getRegdate().getTime()));
if (pstmt.executeUpdate() != 0) {
System.out.println("注册成功，信息如下：
" + user);
} else {
System.out.println("注册失败");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DB.closeAll(null, pstmt, null);
}
}
private boolean check(int i, User user, Connection con) {
boolean flag = true;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
if (i == 1) {
String sql = "select * from tb_users where id=?";
pstmt = DB.getPstmt(con, sql);
pstmt.setInt(1, user.getId());
} else {
String sql = "select * from tb_users where name=?";
pstmt = DB.getPstmt(con, sql);
pstmt.setString(1, user.getName());
}
rs = pstmt.executeQuery();
if (rs.next()) {
flag = false;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DB.closeAll(rs, pstmt, null);
}
return flag;
}
}
05 UserTest.java 创建测试文件
/**
* 2016年5月31日下午1:53:55
*/
package d530JDBC.Test;
import java.sql.Connection;
import d530JDBC.dao.UserDAO;
import d530JDBC.po.User;
import d530JDBC.util.DB;
// 测试类
public class UserTest {
public static void main(String[] args) {
// 建立连接
Connection con = DB.getCon();
// 办理业务
new UserDAO().addUser(new User(), con);
// 关闭连接
DB.closeAll(null, null, con);
}
}
06 分页文件
/**
1、分页查询:
在Oracle中使用 rownum 伪列
select * from ( select e.* , rownum RNO from emp e )
where RNO between ? and ? ;
select * from emp;
*/
public class JDBCTest06分页 {
//1获取连接
static Connection con = null;
static {
con = DBUtils.getConntionOfDB();
}
public static void main(String[] args) throws SQLException {
StringBuilder vs = new StringBuilder();
vs.append("select * from ( select e.* , rownum RNO from emp e )");
vs.append("where RNO between ? and ?");
String sql = "select count(1) from emp";
int num = 5; //每页约定显示5条记录
int pages = getTotalPages( num, sql);
while( true ){
System.out.printf("每页以%2d个记录方式显示，请输入要显示的页码[ 总页数为: %3d页 ]" , num, pages);
String str = null;
int page = 0;
while( true ){
str = inputString("
请输入[exit]>>>");
if( "exit".equalsIgnoreCase(str) ){
System.out.println("
业务结束了！");
System.out.println("byebye!");
System.exit(0);
}
if( str.matches("\d+") ){
page = Integer.parseInt( str );
break;
}
}
sql = vs.toString();
//显示第几页
findPageOfAll(page, num, sql);
}
}
public static String inputString( String info ){
System.out.print( info );
Scanner sc = new Scanner(System.in);
return sc.nextLine();
}
//sql = select count(1) from emp;
//获取总页数
public static int getTotalPages(int num, String sql) throws SQLException{
int pages = 0;
Statement stmt = DBUtils.getStatementOfDB(con);
ResultSet rs = DBUtils.getResultSetOfDB(stmt, sql);
if( rs.next() ){
pages = rs.getInt( 1 );
pages = (int)Math.ceil( pages * 1.0 /num );
}
return pages ;
}
/**
*
* @param sql
* sql语句
* @param num
* 每页显示的记录数
* @param page
* 第几页
*/
public static void findPageOfAll( int page, int num, String sql ){
int start = num*(page-1) + 1;
int end = num * page ;
ResultSet rs = null;
//2建立会话
PreparedStatement pstmt = DBUtils.getPreparedStatementOfDB(con, sql);
//3为?赋值
try {
pstmt.setInt(1, start);
pstmt.setInt(2, end);
//4 执行
rs = DBUtils.getResultSetOfDB(pstmt);
System.out.println("第 " + page + " 页的信息如下：");
//5 遍历
while( rs.next() ){
System.out.printf("%6d" , rs.getInt("empno") );
System.out.printf("%15s" , rs.getString("ename") );
System.out.printf("%15s" , rs.getString("job") );
System.out.printf("%6d" , rs.getInt("mgr") );
System.out.printf("%15tF" , rs.getDate("hiredate") );
System.out.printf("%12.0f" , rs.getDouble("sal") );
System.out.printf("%12.0f" , rs.getDouble("comm") );
System.out.printf("%6d" , rs.getInt("deptno") );
System.out.printf(" 第%6d号
" , rs.getInt("rno") );
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtils.closeAll(rs, pstmt, null);
}
}
}
07 遍历文件
/**
ResultSet 结果集的接口
ResultSetMetaData getMetaData(); 方法，用来获取表中所有字段
Object getObject(fieldName) 方法，获取指定字段名的字段值
ResultSetMetaData 字段接口
int getColumnCount(); 方法, 获取字段的个数
String getColumnName( i ); 方法, 获取第i个字段名
*/
// 遍历任意表
public class JDBCTest12遍历 {
public static void main(String[] args) throws SQLException {
String tableName = DBUtils.inputString("请输入表名[ emp 或 dept ]>>> ");
String sql = "select * from " + tableName ;
Connection con = DBUtils.getConntionOfDB();
PreparedStatement pstmt = DBUtils.getPreparedStatementOfDB(con, sql);
ResultSet rs = DBUtils.getResultSetOfDB(pstmt);
ResultSetMetaData metaData = rs.getMetaData();
int fields = metaData.getColumnCount();
//打印表头
for (int i = 1; i <= fields; i++) {
System.out.print( metaData.getColumnName( i ) + " ");
}
System.out.println("
-------------------------------------------------------");
//遍历
while( rs.next() ){
//每个记录的所有字段循环
for (int i = 1; i <= fields; i++) {
String fieldName = metaData.getColumnName(i);
Object fieldValue = rs.getObject(fieldName);
System.out.printf( "%12s" , fieldValue );
}
System.out.println(); //当一条记录获取并显示完毕，则换行
}
DBUtils.closeAll(rs, pstmt, con);
}
}
08 建表文件
/**
* 1、自动创建一个表。
create table empNew (
empno number(4) primary key,
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate Date,
sal number(7,2),
comm number(7,2),
deptno number(2)
);
晚上的主要任务:
1) 分页、注册、上传案例的消化。
2) 消化DAO设计模式.
3) 针对一张表，实现增、删、改、查 (其业务操作均在 DAO类中)
*/
public class JDBCTest11创建 {
public static void main(String[] args) {
StringBuilder vs = new StringBuilder();
vs.append("create table empNew (");
vs.append("empno number(4) primary key,");
vs.append("ename varchar2(10),");
vs.append("job varchar2(9),");
vs.append("mgr number(4),");
vs.append("hiredate Date,");
vs.append("sal number(7,2),");
vs.append("comm number(7,2),");
vs.append("deptno number(2)");
vs.append(")");
String sql = vs.toString();
//创建表
Connection con = DBUtils.getConntionOfDB();
PreparedStatement pstmt = DBUtils.getPreparedStatementOfDB(con, sql);
try{
pstmt.execute();
System.out.println("表创建成功！");
}catch(SQLException e ){
System.out.println("创建表失败，原因是: " + e.getMessage() );
}
DBUtils.closeAll(null, pstmt, con);
}
}
09 批处理文件
/**
1、什么是批处理？
*.bat ====>>> 批处理文件，通过业务的需要，它将多个命令以有序方式存放到一个 后缀为 bat的文件中。
这种文件就是批处理文件。 目的: 提高处理业务的效率。
在Oracle中，使用 ed命令来生成 数据库的脚本文件
ed a1.sql
@
2、在JDBC编程，如何实现批处理？
SQL> desc myemp;
名称 是否为空? 类型
----------------- -------- ------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
insert into myemp values (1001,'AAA','程序员',2002, '15-06月-16',10000,1000,40),
(1001,'AAA','程序员',2002, '15-06月-16',10000,1000,40),
(1001,'AAA','程序员',2002, '15-06月-16',10000,1000,40),
(1001,'AAA','程序员',2002, '15-06月-16',10000,1000,40),
(1001,'AAA','程序员',2002, '15-06月-16',10000,1000,40),
(1001,'AAA','程序员',2002, '15-06月-16',10000,1000,40);
*/
public class JDBCTest13批处理 {
public static void main(String[] args) {
String sql = "insert into myemp values (?,?,?,?, ?,?,?,?)";
int[] deptnos = {10, 20, 30, 40};
Connection con = DBUtils.getConntionOfDB();
PreparedStatement pstmt = null;
pstmt = DBUtils.getPreparedStatementOfDB(con, sql); //预处理sql
try{
con.setAutoCommit( false ); //关闭自动提交。
long t1 = System.currentTimeMillis();
System.out.println("正在大量的添加记录，需要一点时间，请等待......
");
for(int i = 1; i <= 6000; i++ ){
pstmt.setInt(1, 1000+i);
pstmt.setString(2, "A_" + i);
pstmt.setString(3, "P_" + i);
pstmt.setInt(4, 1000+i-1);
pstmt.setDate(5, new java.sql.Date( new Date().getTime()) );
pstmt.setFloat(6, 8000+i);
pstmt.setFloat(7, 0);
int loc = (int)(deptnos.length*Math.random());
pstmt.setInt( 8, deptnos[loc]);
pstmt.addBatch(); //将当前的sql语句添加到批处理缓冲区中
}
//int n = pstmt.executeUpdate(); //每个sql语句的执行
//执行批处理语句
int[] rsn = pstmt.executeBatch();
con.commit(); //手动提交
long t2 = new Date().getTime();
System.out.println("向 myemp 表中添加 一千个员工耗时为: " + (t2-t1) + " 毫秒。");
con.setAutoCommit(true); //打开自动提交。
}catch( SQLException e ){
try {
con.rollback();//回退
} catch (SQLException e1) {
e1.printStackTrace();
}
System.out.println("添加大量记录失败！");
}
DBUtils.closeAll(null, pstmt, con);
}
}