java_jdbc_oracle简单总结（2016-11-23）
JDBC连接oracle的实例
好久没写过jdbc，基本忘干净了，随意插一个图，简单学习一下。然后干别的。。。。。
使用jdbc操作数据库步骤是固定的
1.将驱动包导入到数据库，每一个数据库的驱动包都不一样，下面我提供一个Oracle数据库的驱动包http://download.csdn.net/detail/hncsy403/4530830将它下载后放入web项目中的 web-inf中的lib中
2.选择项目右键，选择Build Bath，在libraries中add JARs，选择刚才的jar包
学生类bean
package jdbc.bean;
import java.util.Date;
public class Student {
private int id;
private String name;
private String password;
private String sex;
private int age;
private Date birthday;
private String memo;
private String photo;
private Date regTime;
public int getAge() {
return age;
}
public Date getBirthday() {
return birthday;
}
public int getId() {
return id;
}
public String getMemo() {
return memo;
}
public String getName() {
return name;
}
public String getPassword() {
return password;
}
public String getPhoto() {
return photo;
}
public Date getRegTime() {
return regTime;
}
public String getSex() {
return sex;
}
public void setAge(int age) {
this.age = age;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public void setId(int id) {
this.id = id;
}
public void setMemo(String memo) {
this.memo = memo;
}
public void setName(String name) {
this.name = name;
}
public void setPassword(String password) {
this.password = password;
}
public void setPhoto(String photo) {
this.photo = photo;
}
public void setRegTime(Date regTime) {
this.regTime = regTime;
}
public void setSex(String sex) {
this.sex = sex;
}
}
工具类：DBUtil
package jdbc.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBUtil {
private static final String driverClass = "oracle.jdbc.driver.OracleDriver";
private static final String jdbcUrl = "jdbc:oracle:thin:@liumo:1521:ORCL";
private static final String user = "test_lm";
private static final String password = "test_lm";
public static Connection getConn() {
// 1.注册驱动
try {
Class.forName(driverClass);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
// 2.创建Connection(数据库连接对象)
Connection conn = null;
try {
conn = DriverManager.getConnection(jdbcUrl, user, password);
conn.setAutoCommit(false);
return conn;
} catch (SQLException e) {
e.printStackTrace();
}
/*
* Connection是Statement的工厂，一个Connection可以生产多个Statement。
* Statement是ResultSet的工厂，一个Statement却只能对应一个ResultSet（它们是一一对应的关系）。
* 所以在一段程序里要用多个ResultSet的时候，必须再Connection中获得多个Statement，然后一个Statement对应一个ResultSet。
*/
return null;
}
/**
* 关闭连接(数据库连接对象)
* @param conn
*/
public static void close(Connection conn) {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 关闭编译的 SQL 语句的对象
* @param stmt
*/
public static void close(Statement stmt) {
try {
if (stmt != null) {
stmt.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 关闭结果集
* @param rs
*/
public static void close(ResultSet rs) {
try {
if (rs != null) {
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 提交事务
* @param conn
*/
public static void commit(Connection conn) {
try {
if (conn != null) {
conn.commit();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 回滚事务
* @param conn
*/
public static void rollback(Connection conn) {
try {
if (conn != null) {
conn.rollback();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
实际的dao：StudentDao
package jdbc.dao;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import jdbc.bean.Student;
import jdbc.util.DBUtil;
public class StudentDao {
/**
* 保存
* @param student
*/
public void save(Student student) {
Connection conn = DBUtil.getConn();
PreparedStatement pstmt = null;
String sql = " insert into t_student(name,password,sex,age,birthday,memo,photo,reg_time) ";
sql += " values(?,?,?,?,?,?,?,?) ";
try {
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, student.getName());
pstmt.setString(2, student.getPassword());
pstmt.setString(3, student.getSex());
pstmt.setInt(4, student.getAge());
pstmt.setDate(5, new java.sql.Date(student.getBirthday().getTime())); // 只存年月日这种形式
pstmt.setString(6, student.getMemo());
try {
// 构建一个输入流存blob
pstmt.setBlob(7, new FileInputStream(student.getPhoto()));
} catch (FileNotFoundException e) {
e.printStackTrace();
}
pstmt.setTimestamp(8, new java.sql.Timestamp(student.getRegTime().getTime())); // 完整的时间格式
pstmt.executeUpdate();
DBUtil.commit(conn);
} catch (SQLException e) {
DBUtil.rollback(conn);
e.printStackTrace();
} finally {
DBUtil.close(pstmt);
DBUtil.close(conn);
}
}
/**
* 删除
* @param id
*/
public void delete(int id) {
Connection conn = DBUtil.getConn();
PreparedStatement pstmt = null;
String sql = " delete from t_student where id=?";
try {
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);
pstmt.executeUpdate();
DBUtil.commit(conn);
} catch (SQLException e) {
DBUtil.rollback(conn);
e.printStackTrace();
} finally {
DBUtil.close(pstmt);
DBUtil.close(conn);
}
}
/**
* 批量删除
* @param ids
*/
public void deleteBatch(int[] ids) {
Connection conn = DBUtil.getConn();
PreparedStatement pstmt = null;
String sql = " delete from t_student where id=?";
try {
pstmt = conn.prepareStatement(sql);
for (int id : ids) {
pstmt.setInt(1, id);
pstmt.addBatch();
}
pstmt.executeBatch();
DBUtil.commit(conn);
} catch (SQLException e) {
DBUtil.rollback(conn);
e.printStackTrace();
} finally {
DBUtil.close(pstmt);
DBUtil.close(conn);
}
}
/**
* 修改
* @param student
*/
public void update(Student student) {
Connection conn = DBUtil.getConn();
PreparedStatement pstmt = null;
String sql = " update t_student set name=?,password=?,sex=?,age=?,birthday=?,memo=?,photo=?,reg_time=? where id=?";
try {
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, student.getName());
pstmt.setString(2, student.getPassword());
pstmt.setString(3, student.getSex());
pstmt.setInt(4, student.getAge());
pstmt.setDate(5, new java.sql.Date(student.getBirthday().getTime())); // 只存年月日这种形式
pstmt.setString(6, student.getMemo());
try {
// 构建一个输入流存blob
pstmt.setBlob(7, new FileInputStream(student.getPhoto()));
} catch (FileNotFoundException e) {
e.printStackTrace();
}
pstmt.setTimestamp(8, new java.sql.Timestamp(student.getRegTime().getTime())); // 完整的时间格式
pstmt.setInt(9, student.getId());
pstmt.executeUpdate();
DBUtil.commit(conn);
} catch (SQLException e) {
DBUtil.rollback(conn);
e.printStackTrace();
} finally {
DBUtil.close(pstmt);
DBUtil.close(conn);
}
}
/**
* 查找
* @param id
* @return
*/
public Student find(int id) {
Connection conn = DBUtil.getConn();
PreparedStatement pstmt = null;
ResultSet resultSet = null;
String sql = " select * from t_student where id=?";
Student student = null;
try {
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);
resultSet = pstmt.executeQuery();
if (resultSet.next()) {
student = new Student();
student.setId(resultSet.getInt("id"));
student.setName(resultSet.getString("name"));
student.setAge(resultSet.getInt("age"));
student.setBirthday(resultSet.getDate("birthday"));
student.setMemo(resultSet.getString("memo"));
student.setPassword(resultSet.getString("password"));
student.setRegTime(resultSet.getTimestamp("reg_time"));
student.setSex(resultSet.getString("sex"));
InputStream in = resultSet.getBlob("photo").getBinaryStream();
String path = "d:\\ltf.jpg";
try {
OutputStream out = new FileOutputStream(path);
copy(in, out);
out.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
student.setPhoto(path);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(resultSet);
DBUtil.close(pstmt);
DBUtil.close(conn);
}
return student;
}
private void copy(InputStream in, OutputStream out) {
int i = 0;
try {
while ((i = in.read()) != -1) {
out.write(i);
}
out.flush();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 查询多条记录
* @return
*/
public List<Student> query() {
Connection conn = DBUtil.getConn();
PreparedStatement pstmt = null;
ResultSet resultSet = null;
String sql = " select * from t_student ";
List<Student> studentList = new ArrayList<Student>();
try {
pstmt = conn.prepareStatement(sql);
resultSet = pstmt.executeQuery();
while (resultSet.next()) {
Student student = new Student();
student.setId(resultSet.getInt("id"));
student.setName(resultSet.getString("name"));
student.setAge(resultSet.getInt("age"));
student.setBirthday(resultSet.getDate("birthday"));
student.setMemo(resultSet.getString("memo"));
student.setPassword(resultSet.getString("password"));
student.setRegTime(resultSet.getTimestamp("reg_time"));
student.setSex(resultSet.getString("sex"));
InputStream in = resultSet.getBlob("photo").getBinaryStream();
String path = "d:\\ltf.jpg";
try {
//将数据库存的图片放到磁盘的某个位置
OutputStream out = new FileOutputStream(path);
copy(in, out);
out.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
student.setPhoto(path);
studentList.add(student);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(resultSet);
DBUtil.close(pstmt);
DBUtil.close(conn);
}
return studentList;
}
}
测试类：DaoTest
package jdbc.test;
import static org.junit.Assert.fail;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import jdbc.bean.Student;
import jdbc.dao.StudentDao;
import org.junit.Test;
public class DaoTest {
@Test
public void test() {
fail("Not yet implemented");
Student s=new Student();
//s.setId(3);
s.setName("zss");
s.setPassword("zss");
s.setSex("女");
s.setAge(21);
try {
s.setBirthday(new SimpleDateFormat("yyyy-MM-dd").parse("1980-01-01"));
} catch (ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
s.setMemo("我人还不错");
s.setPhoto("c:\\ltf.jpg");
s.setRegTime(new Date());
StudentDao sd=new StudentDao();
//sd.save(s);
//sd.update(s);
//sd.delete(1);
//sd.deleteBatch(new int[]{2,3});
System.out.println(sd.query().size());
}
}
每次SQL操作都需要建立和关闭连接，这势必会消耗大量的资源开销，如何避免
分析：可以采用连接池，对连接进行统一维护，不必每次都建立和关闭。事实上这是很多对JDBC进行封装的工具所采用的。（等看到hibernate，spring连接数据库和事务的时候在研究一下。）
参考：
通过JDBC进行简单的增删改查（以MySQL为例） http://www.cnblogs.com/wuyuegb2312/p/3872607.html
JDBC学习之-如何获取Connection http://blog.csdn.net/luohuacanyue/article/details/8770450
