jdbc的一些笔记

接受控制台的一个输入类

import java.io.BufferedReader; import java.io.IOException; import java.io.InputStreamReader; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.Date; public class InputData { private BufferedReader buf = null; public InputData() { this.buf = new BufferedReader(new InputStreamReader(System.in)); } public String getString(String info) { String str = null; System.out.print(info);// 打印提示信息 try { str = this.buf.readLine(); } catch (IOException e) { e.printStackTrace(); } return str; } public int getInt(String info, String err) { int temp = 0; boolean flag = true;// 定义一个标志位 while (flag) { String str = this.getString(info); if (str.matches("//d+")) { temp = Integer.parseInt(str); flag = false;// 退出循环 } else { System.out.print(err); } } return temp; } public float getFloat(String info, String err) { float temp = 0.0f; boolean flag = true;// 定义一个标志位 while (flag) { String str = this.getString(info); if (str.matches("//d+.?//d+")) { temp = Float.parseFloat(str); flag = false;// 退出循环 } else { System.out.print(err); } } return temp; } public char getChar(String info, String err) { char temp = ' '; boolean flag = true;// 定义一个标志位 while (flag) { String str = this.getString(info); if (str.matches("//w")) { temp = str.charAt(0); flag = false;// 退出循环 } else { System.out.print(err); } } return temp; } public Date getDate(String info, String err) { Date temp = null ; boolean flag = true;// 定义一个标志位 while (flag) { String str = this.getString(info); if (str.matches("//d{4}-//d{2}-//d{2}")) { try { temp = new SimpleDateFormat("yyyy-MM-dd").parse(str) ; } catch (ParseException e) { System.out.print(err) ; } flag = false;// 退出循环 } else { System.out.print(err); } } return temp; } }

 

JDBC的模糊匹配:

String sql = "SELECT pid,name,age,birthday,salary FROM person WHERE name LIKE ? OR birthday LIKE ?" ; pstmt = conn.prepareStatement(sql) ; pstmt.setString(1,"%"+keyWord+"%") ; pstmt.setString(2,"%"+keyWord+"%") ;

 

jdbc的批处理:

for(int i=0;i<10;i++){ pstmt.setString(1, "lxh-" + i);// 第一个？号的内容 pstmt.setInt(2, 20 + i); // 第二个？号的内容 pstmt.setDate(3, new java.sql.Date(new java.util.Date().getTime())); pstmt.setFloat(4, 900*i); pstmt.addBatch() ; // 增加批处理 } // 执行SQL语句，更新数据库 int i[] = pstmt.executeBatch() ; System.out.println(i.length);

jdbc的错误事物提交回滚

//这里注意需要设置事物提交和回滚,有错误会回滚 conn.setAutoCommit(false) ; // 取消自动提交 // 3、Statement接口需要通过Connection接口进行实例化操作 stmt = conn.createStatement() ; try{ stmt.addBatch("INSERT INTO person(pid,name,age,birthday,salary) VALUES (perseq.nextval,'张三',30,TO_DATE('1995-02-14','yyyy-mm-dd'),9000.0) ") ; stmt.addBatch("INSERT INTO person(pid,name,age,birthday,salary) VALUES (perseq.nextval,'李四',30,TO_DATE('1995-02-14','yyyy-mm-dd'),9000.0) ") ; stmt.addBatch("INSERT INTO person(pid,name,age,birthday,salary) VALUES (perseq.nextval,'王'五',30,TO_DATE('1995-02-14','yyyy-mm-dd'),9000.0) ") ; stmt.addBatch("INSERT INTO person(pid,name,age,birthday,salary) VALUES (perseq.nextval,'赵六',30,TO_DATE('1995-02-14','yyyy-mm-dd'),9000.0) ") ; stmt.addBatch("INSERT INTO person(pid,name,age,birthday,salary) VALUES (perseq.nextval,'孙七',30,TO_DATE('1995-02-14','yyyy-mm-dd'),9000.0) ") ; // 执行SQL语句，更新数据库 int i[] = stmt.executeBatch() ; System.out.println(i.length); conn.commit() ;// 提交 }catch(Exception e){ conn.rollback() ;// 回滚 } // 4、关闭数据库 stmt.close() ; conn.close();

在Oracle中插入图片和读取图片

package com.oracleQuery; import java.io.BufferedInputStream; import java.io.BufferedOutputStream; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.InputStream; import java.io.OutputStream; import java.sql.Blob; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; import oracle.sql.BLOB; import org.junit.Test; public class TestBlob { /*create table tb_file2(name varchar(20),detail BLOB);*/ @Test public void InsertPic() throws Exception{ Class.forName("oracle.jdbc.OracleDriver").newInstance(); String url = "jdbc:oracle:thin:@localhost:1521:orcl"; String user = "scott"; String password = "tiger"; String sql = "insert into tb_file2 values (?,empty_blob())"; Connection conn = DriverManager.getConnection(url, user, password); ResultSet rs ; conn.setAutoCommit(false); PreparedStatement ps = conn.prepareStatement(sql); File file = new File("D://heima_webspace//JavaTest//src//me.jpg"); ps.setString(1, file.getName()); ps.executeUpdate(); ps.close(); rs = conn.createStatement().executeQuery("select detail from tb_file2 for update"); while(rs.next()){ oracle.sql.BLOB blob = (BLOB) rs.getBlob("detail"); int fileLength =(int) file.length(); System.out.println(fileLength); BufferedInputStream bis = new BufferedInputStream(new FileInputStream(file)); BufferedOutputStream bos = new BufferedOutputStream(blob.getBinaryOutputStream()); int c ; while((c=bis.read())!=-1){ bos.write(c); } bis.close(); bos.close(); } conn.commit(); } @Test public void ReadPic() throws Exception{ Class.forName("oracle.jdbc.OracleDriver").newInstance(); String url = "jdbc:oracle:thin:@localhost:1521:orcl"; String user = "scott"; String password = "tiger"; String sql = "select detail from tb_file2"; Connection conn = DriverManager.getConnection(url, user, password); conn.setAutoCommit(false); ResultSet rs = conn.createStatement().executeQuery(sql); while(rs.next()){ Blob b = rs.getBlob("detail"); File f = new File("d://m.jpg"); InputStream is = b.getBinaryStream(); FileOutputStream fos = new FileOutputStream(f); byte[] data = new byte[1024]; while(is.read(data)!=-1){ fos.write(data); } fos.close(); is.close(); } conn.commit(); } }

在Oracle中调用存储过程和存储函数

/* 创建存储过程，查询指定员工的姓名和工资 select ename,sal from emp where empno=? */ create or replace PROCEDURE queryEmpInfo(peno in NUMBER,pename out VARCHAR2,psal out NUMBER) as begin select ename,sal into pename, psal from emp where empno= peno; end; / /* 创建存储函数：要求返回员工的年收入，姓名，月薪，奖金 */ create or replace function queryEmp2(peno in number,pename out VARCHAR2,psal out NUMBER,pcomm out NUMBER) --返回年收入 RETURN NUMBER as begin select ename,sal,nvl(comm,0) into pename, psal, pcomm from emp where empno=peno; return psal*12 + pcomm; end; /

package com.oracleQuery; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.ResourceBundle; import org.junit.Test; public class TestOraclep { @Test //调用存储函数 public void QueryEmp() throws Exception { ResourceBundle rb = ResourceBundle.getBundle("c"); System.out.println(rb.getString("url")); Class.forName("oracle.jdbc.OracleDriver").newInstance(); String url = "jdbc:oracle:thin:@localhost:1521:orcl"; String user = "scott"; String password = "tiger"; String sql = "{?=call queryEmp(?,?,?,?)}"; Connection conn = DriverManager.getConnection(url, user, password); //生成对应的对象 CallableStatement call = conn.prepareCall(sql); call.registerOutParameter(1, oracle.jdbc.OracleTypes.NUMBER); call.setInt(2, 7788); call.registerOutParameter(3, oracle.jdbc.OracleTypes.VARCHAR); call.registerOutParameter(4, oracle.jdbc.OracleTypes.NUMBER); call.registerOutParameter(5, oracle.jdbc.OracleTypes.NUMBER); call.execute(); double annlIncome = call.getDouble(1); String name = call.getString(3); double sal = call.getDouble(4); double comm = call.getDouble(5); System.out.println("年薪为" +": "+annlIncome); System.out.println("姓名" +": "+name); System.out.println("月收入"+": "+sal); System.out.println("奖金" +": "+comm); call.close(); conn.close(); } @Test //调用存储过程 public void QueryEmpInfo() { try { Class.forName("oracle.jdbc.OracleDriver").newInstance(); String url = "jdbc:oracle:thin:@localhost:1521:orcl"; String user = "scott"; String password = "tiger"; String sql = "{call QueryempInfo(?,?,?)}"; Connection conn = DriverManager.getConnection(url, user, password); //生成对应的对象 CallableStatement call = conn.prepareCall(sql); call.setInt(1, 7788); call.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR); call.registerOutParameter(3, oracle.jdbc.OracleTypes.NUMBER); call.execute(); String name = call.getString(2); double sal = call.getDouble(3); System.out.println(name +": "+sal); call.close(); conn.close(); } catch (InstantiationException e) { } catch (IllegalAccessException e) { } catch (ClassNotFoundException e) { } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }

大的文本的存取

import java.io.File; import java.io.FileNotFoundException; import java.io.FileReader; import java.io.FileWriter; import java.io.Reader; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import org.junit.Test; import cn.itcast.utils.JdbcUtils; public class Demo1 { /* 大文本的存取 create database day15; use day15; create table testclob ( id int primary key, resume text ); */ @Test public void write() throws SQLException, FileNotFoundException{ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils.getConnection(); String sql = "insert into testclob(id,resume) values(?,?)"; st = conn.prepareStatement(sql); st.setInt(1, 1); String path = Demo1.class.getClassLoader().getResource("resume.txt").getPath(); File file = new File(path); st.setCharacterStream(2, new FileReader(file), (int) file.length()); st.executeUpdate(); }finally{ JdbcUtils.release(conn, st, rs); } } @Test public void read() throws Exception{ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils.getConnection(); String sql = "select resume from testclob where id=1"; st = conn.prepareStatement(sql); rs = st.executeQuery(); if(rs.next()){ Reader reader = rs.getCharacterStream("resume"); FileWriter writer = new FileWriter("c://1.txt"); char buffer[] = new char[1024]; int len = 0; while((len=reader.read(buffer))>0){ writer.write(buffer, 0, len); } reader.close(); writer.close(); } }finally{ JdbcUtils.release(conn, st, rs); } } }

大的二进制数据的读取

import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.FileReader; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import org.junit.Test; import cn.itcast.utils.JdbcUtils; public class Demo2 { /* 大的二进制数据的存取 create table testblob ( id int primary key, image blob ); alter table testblob modify image longblob; */ @Test public void writer() throws SQLException, FileNotFoundException{ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils.getConnection(); /* String sql1 = "set max_allowed_packet=3000000"; conn.prepareStatement(sql1).execute();*/ String sql2 = "insert into testblob(id,image) values(?,?)"; st = conn.prepareStatement(sql2); st.setInt(1, 3); String path = Demo2.class.getClassLoader().getResource("2.bmp").getPath(); File file = new File(path); st.setBinaryStream(2, new FileInputStream(path), (int) file.length()); st.executeUpdate(); }finally{ JdbcUtils.release(conn, st, rs); } } @Test public void read() throws SQLException, IOException{ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils.getConnection(); String sql = "select image from testblob where id=3"; st = conn.prepareStatement(sql); rs = st.executeQuery(); if(rs.next()){ InputStream in = rs.getBinaryStream("image"); FileOutputStream out = new FileOutputStream("c://1.bmp"); byte buffer[] = new byte[1024]; int len = 0; while((len=in.read(buffer))>0){ out.write(buffer, 0, len); } in.close(); out.close(); } }finally{ JdbcUtils.release(conn, st, rs); } } }

2种批处理

import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import org.junit.Test; import cn.itcast.utils.JdbcUtils; public class Demo4 { /* 批处理 create table testbatch ( id int primary key, name varchar(40) ); */ @Test public void test1() throws SQLException{ Connection conn = null; Statement st = null; ResultSet rs = null; try{ conn = JdbcUtils.getConnection(); String sql1 = "insert into testbatch(id,name) values(1,'aa')"; String sql2 = "update testbatch set name='bb' where id=1"; st = conn.createStatement(); st.addBatch(sql1); st.addBatch(sql2); st.executeBatch(); //[1,1] st.clearBatch(); }finally{ JdbcUtils.release(conn, st, rs); } } @Test public void test2() throws SQLException{ long starttime = System.currentTimeMillis(); Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils.getConnection(); String sql = "insert into testbatch(id,name) values(?,?)"; st = conn.prepareStatement(sql); for(int i=1;i<=1000003;i++){ st.setInt(1, i); st.setString(2, "aa" + i); st.addBatch(); if(i%1000==0){ //1000 2000 st.executeBatch(); st.clearBatch(); } } st.executeBatch(); }finally{ JdbcUtils.release(conn, st, rs); } long endtime = System.currentTimeMillis(); System.out.println("共花了：" + (endtime-starttime)/1000 + "秒！！"); } }