perparestatement在mysql中的普通使用

perparestatement在mysql中的普通使用：

package perparestatement;

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

public class JDBCPerpareTest {

    public static void main(String[] args) throws Exception {
        Connection conn = JDBCTools.getConnection();
        //select   executeQuery()
        String sql = "select * from dept";
        PreparedStatement pst = conn.prepareStatement(sql);
        ResultSet rs = pst.executeQuery();
        while (rs.next()){
            int no = rs.getInt("deptno");
            String dname = rs.getString("dname");
            String loc = rs.getString("loc");
            System.out.println(no+" "+dname+" "+loc);
        }   

        //insert to   executeUpdate()   ?的使用
        String sql = "insert into dept values (?,'OPERATIONS','BOSTON')";
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setInt(1, 40);
        int ret = pst.executeUpdate();
        System.out.println(ret);

        //update  executeUpdate()
        String sql = "update dept set deptno=60 where deptno=50";
        PreparedStatement pst = conn.prepareStatement(sql);
        int ret = pst.executeUpdate();
        System.out.println(ret);

        //delete  execute()
        String sql = "delete from dept where deptno=40";
        PreparedStatement pst = conn.prepareStatement(sql);
        boolean b = pst.execute();
        System.out.println(b);
    }

}

和字节流的使用：

package perparestatement;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class JDBCPSTBinaryStream {

    public static void main(String[] args) throws Exception {
        //从数据库读入二进制文件
        String sql = "insert into mybin values (1,?)";
        File file = new File("psu.jpg");
        FileInputStream fis = new FileInputStream(file);
        Connection conn = JDBCTools.getConnection();
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setBinaryStream(1, fis, (int)(file.length()));
        pst.executeUpdate();
        JDBCTools.closePreparedStatement(pst);
        JDBCTools.closeConnetion(conn);



        //从数据库读出二进制文件
        String sql = "select * from mybin where id = ?";
        Connection conn = JDBCTools.getConnection();
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setInt(1, 1);
        ResultSet rs = pst.executeQuery();
        while(rs.next()){
            InputStream fis = rs.getBinaryStream("b");
            FileOutputStream fos = new FileOutputStream("2.jpg");
            int len = 0 ;
            byte[] bytes = new byte[1024 * 100];
            while((len=fis.read(bytes))!=-1){
                fos.write(bytes, 0, len);
            }
            fos.flush();
            fos.close();
            fis.close();
        }

        JDBCTools.closeResultSet(rs);
        JDBCTools.closePreparedStatement(pst);
        JDBCTools.closeConnetion(conn);



    }

}

和字符流的使用：

package perparestatement;

import java.io.File;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class JDBCPSTCharacterStream {

    public static void main(String[] args) throws Exception {
        //往表里插入文件
        String sql = "insert into mycontent values (1,?)";
        File file = new File("2.txt");
        FileReader r = new FileReader(file); 
        Connection conn = JDBCTools.getConnection();
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setCharacterStream(1,r,(int)file.length());
        int up = pst.executeUpdate();
        JDBCTools.closePreparedStatement(pst);
        JDBCTools.closeConnetion(con);

        //从表里读出文件
        Connection con = JDBCTools.getConnection();
        String sql = "select * from mycontent where id = ?";
        PreparedStatement pst = con.prepareStatement(sql);
        pst.setInt(1, 1);
        ResultSet rs = pst.executeQuery();
        while(rs.next()){
            Reader r = rs.getCharacterStream("contnet");
            FileWriter fw = new FileWriter("a.txt");
            char[] bys = new char[1024 * 100];
            int len = -1;
            while((len = r.read(bys))!=-1){
                fw.write(bys, 0, len);
                fw.flush();
            }
            fw.close();
            r.close();
        }
        JDBCTools.closePreparedStatement(pst);
        JDBCTools.closeConnetion(con);

    }

}

批处理：

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

public class PSTBatchTest {

    public static void main(String[] args) throws Exception {
        // 向person表中插入1000条数据

        String sql = "insert into person values(?,?)";

        Connection con = JDBCTools.getConnection();

        PreparedStatement pst = con.prepareStatement(sql);

        // 批处理
        long l=System.currentTimeMillis();
        for (int i = 1; i <= 100000; i++) {
            pst.setInt(1, i);
            pst.setString(2, "name" + i);
            pst.addBatch();

            if(i%1000==0){
                pst.executeBatch();
                pst.clearBatch(); //清空缓存。
            }

        }

        //执行批处理
        pst.executeBatch();

        pst.close();
        con.close();

        System.out.println(System.currentTimeMillis()-l);
    }
}