jdbc 相关代码 存储过程

零、批处理

package cn.itcast.demo;

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 Demo3 {

	/*
	
	 create table testbatch
	 (
	 	id varchar(40) 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','aaa')";
			String sql2 = "update testbatch set name='bbb' where id='1'";
			
			st = conn.createStatement();  //list
			st.addBatch(sql1);
			st.addBatch(sql2);
			
			//[3,4] 
			st.executeBatch();
			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<=10000006;i++){
				st.setString(1, i+"");
				st.setString(2, "aa" + i);
				st.addBatch();
				if(i%1000==0){
					st.executeBatch();
					st.clearBatch();
				}
			}
			st.executeBatch();
		}finally{
			JdbcUtils.release(conn, st, rs);
		}
		
		long endtime = System.currentTimeMillis(); 
		System.out.println("总花了：" + (endtime-starttime)/1000 + "秒");
	}
	
}

一、 获取自动生成的主键                          该操作仅对于insert有效

package cn.itcast.demo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import cn.itcast.utils.JdbcUtils;

public class Demo4 {

	/**
	 获取自动生成的主键
	 create table test
	 (
	 	id int primary key auto_increment,
	 	name varchar(40)
	 );
	 
	 */
	public static void main(String[] args) throws SQLException {

		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		
		try{
			conn = JdbcUtils.getConnection();
			String sql = "insert into test(name) values('aaa')";
			st = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			st.executeUpdate();
			
			rs = st.getGeneratedKeys();
			if(rs.next()){
				System.out.println(rs.getInt(1));
			}
			
		}finally{
			JdbcUtils.release(conn, st, rs);
		}
		
	}

}

二、 存储过程   调用存储过程

package cn.itcast.demo;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

import cn.itcast.utils.JdbcUtils;

public class Demo5 {

	/**
	 调用存储过程
	 * @throws SQLException 
	 */
	public static void main(String[] args) throws SQLException {
		
		Connection conn = null;
		CallableStatement  st = null;
		ResultSet rs = null;
		
		try{
			conn = JdbcUtils.getConnection();
			st = conn.prepareCall("{call demoSp(?,?)}");
			st.setString(1, "aaaaa");
			st.registerOutParameter(2, Types.VARCHAR);            //这个地方是设置数据库的编码类型
			st.execute();
			
			System.out.println(st.getString(2));
			
		}finally{
			JdbcUtils.release(conn, st, rs);
		}
	}

}

三、读文件

package cn.itcast.demo;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
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 table testclob
	 (
	 	id varchar(40) primary key,
	 	resume text
	 );

	 */
	
	@Test
	public void insert() 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.setString(1, "1");
			
			File file = new File("src/1.txt");
			FileReader reader = new FileReader(file);
			st.setCharacterStream(2, reader, (int) file.length());
			int num = st.executeUpdate();
			if(num>0){
				System.out.println("插入成功！！");
			}
		}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 id,resume from testclob where id='1'";
			st = conn.prepareStatement(sql);
			rs = st.executeQuery();
			if(rs.next()){
				//String resume = rs.getString("resume");
				Reader reader = rs.getCharacterStream("resume");
				FileWriter writer = new FileWriter("c:\\1.txt");
				try {
					int len = 0;
					char buffer[] = new char[1024];
					while ((len = reader.read(buffer)) > 0) {
						writer.write(buffer, 0, len);
					}
				} finally {
					if (reader != null) {
						reader.close();
					}
					writer.close();
				}
			}
		}finally{
			JdbcUtils.release(conn, st, rs);
		}
	}
}

package cn.itcast.demo;

import java.io.File;
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 org.junit.Test;

import cn.itcast.utils.JdbcUtils;

public class Demo2 {

	/*
	 create table testblob
	 (
	 	id varchar(40) primary key,
	 	image blob
	 ); 
	 */
	
	@Test
	public void insert() throws SQLException, FileNotFoundException{
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try{
			conn = JdbcUtils.getConnection();
			String sql = "insert into testblob(id,image) values(?,?)";
			st = conn.prepareStatement(sql);
			st.setString(1, "1");
			File file = new File("src/1.jpg");
			FileInputStream in = new FileInputStream(file);
			st.setBinaryStream(2, in, (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 id,image from testblob where id='1'";
			rs = conn.prepareStatement(sql).executeQuery();
			if(rs.next()){
				InputStream in = rs.getBinaryStream("image");
				OutputStream out = new FileOutputStream("c:\\1.jpg");;
				try {
					int len = 0;
					byte buffer[] = new byte[1024];
					while ((len = in.read(buffer)) > 0) {
						out.write(buffer, 0, len);
					}
				} finally {
					if (in != null)
						in.close();
					if (out != null)
						out.close();
				}
			}
		}finally{
			JdbcUtils.release(conn, st, rs);
		}
	}
}