首页 > 代码库 > java_jdbc_oracle简单总结(2016-11-23)
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
java_jdbc_oracle简单总结(2016-11-23)
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。