mysql java中的调用

package com.inco.hive.lytest;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.*;

public class connet_mysql {
    public static void main(String[] args)  {
        connet_mysql connet=new connet_mysql();
        try {
            /*connet.createsql();*/
           /* connet.savepoint();*/
            /*connet.update();*/
          /*  connet.select();*/
         /*   connet.insert();*/
           /* connet.selectject();*/
           /* connet.insertpic();*/
           /* connet.readtpic();*/
           /* connet.pro();*/
            connet.functio2();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public Connection getConnection(){
         Connection connection=null;
        try {
            //加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //数据库连接url
            String url="jdbc:mysql://localhost:3306/bigdata";
            String user="root";
            String password="123456";
            connection= DriverManager.getConnection(url,user,password);
        }
        catch (Exception ee){
            ee.printStackTrace();
        }
        return  connection;
    }
    public void createsql() {
        Connection con=null;
        Statement st=null;
        try {
            con = getConnection();
            //不自动提交
            con.setAutoCommit(false);
            st=con.createStatement();
            /*st.execute("delete from mytableV where id=2");*/
            st.execute("insert  into mytableV (id,nane)values (1,'jerry')");
            st.execute("insert  into mytableV (id,nane)values (2,'jek')");
            //手动提交事务
            con.commit();
            st.close();
            con.close();
            System.out.println("insert over");
        }
       catch (Exception ee){
            ee.printStackTrace();
            //手动提交的话有异常就回滚
           try {
               con.rollback();
           } catch (SQLException e) {
               e.printStackTrace();
           }
       }
        finally {
            try {
                if(st!=null &&!st.isClosed()){
                    st.close();
                }
                if(con!=null &&!con.isClosed()){
                    con.close();
                }
            }
            catch (Exception ee){
                ee.printStackTrace();
            }
        }
    }
    //保存点
    public  void savepoint(){
        Connection con=null;
        Statement st=null;
        try {
            con = getConnection();
            //不自动提交
            con.setAutoCommit(false);
            st = con.createStatement();
            st.execute("insert  into mytableV (id,nane)values (4,'4')");
            Savepoint s1=con.setSavepoint("1");

            st.execute("insert  into mytableV (id,nane)values (5,'5')");
            Savepoint s2=con.setSavepoint("2");

            st.execute("insert  into mytableV (id,nane)values (6,'6')");
            Savepoint s3=con.setSavepoint("3");
            con.rollback(s2);
            con.commit();
            st.close();
            con.close();
            System.out.println("insert  123");
        }
        catch (Exception ee){
            ee.printStackTrace();
        }
    }
    public  void update() throws Exception{
        Connection con=null;
        Statement st=null;
        con = getConnection();
        //不自动提交
        con.setAutoCommit(false);
        st = con.createStatement();
        st.execute("update  mytableV  set  nane='test' where  id='1'");
        con.commit();
        st.close();
        con.close();
        System.out.println("update  123");

    }
    public  void select() throws Exception{
        Connection con=null;
        Statement st=null;
        con = getConnection();
        //不自动提交
        con.setAutoCommit(false);
        st = con.createStatement();
        ResultSet rs=st.executeQuery("select * from mytableV");
        while (rs.next()){
            String id=rs.getString("id");
            String name=rs.getString("nane");
            //如果有年龄0岁这样的，用Integer强转。即可得到
            /*Integer age =(Integer)rs.getObject("age");*/
            System.out.println("id:"+id+"   "+"name:"+name);
        }
        con.commit();
        st.close();
        con.close();
        System.out.println("select  123");

    }
    //提交一个事务一次性插入100万数据
    public  void insert() throws Exception{
        int max=9998;
        Connection con=null;
        PreparedStatement  pst=null;
        con = getConnection();
        con.setAutoCommit(false);
        long start=System.currentTimeMillis();
        //绑定参数
        pst = con.prepareStatement("insert into mytableC (id,nane) values (?,?)");
        int count=0;
        for(int i = 1;i<=max;i++){
            pst.setInt(1,i);
            pst.setString(2,"tom"+i);
            //每1999个攒一个批次
            pst.addBatch();
            count ++;
            //执行批次
            if(count==1999){
                pst.executeBatch();
                pst.clearBatch();
                count =0;
            }
            if(count!=999 && i==max){
                pst.executeBatch();
                pst.clearBatch();
                count =0;
            }
        }
        con.commit();
        System.out.println(System.currentTimeMillis()-start);
        pst.close();
        con.close();
    }
    public  void selectject() throws Exception{
        Connection con=null;
        PreparedStatement  pst=null;
        con = getConnection();
        con.setAutoCommit(false);
        int i=0;
        pst = con.prepareStatement("select * from mytableV  where id = ? and nane = ?");
        //绑定参数
        pst.setString(1,"1' or 1=1 --");
        pst.setString(2,"tom");
        ResultSet rs=pst.executeQuery();
        while (rs.next()){
            String id=rs.getString("id");
            String name=rs.getString("nane");
        }
        //执行更新
        con.commit();
        pst.close();
        con.close();
    }
    //插入图片
    public  void insertpic() throws Exception{
        Connection con=null;
        PreparedStatement  pst=null;
        con = getConnection();
        con.setAutoCommit(false);
        long start=System.currentTimeMillis();
        //绑定参数
        pst = con.prepareStatement("insert into mytablepic (id,pic,nane) values (?,?,?)");
        pst.setInt(1,1);
        File file=new File("D:/123.jpg");
        FileInputStream fis= new FileInputStream(file);
        pst.setBinaryStream(2,fis,file.length());
        pst.setString(3,"tom");
        pst.executeUpdate();
        con.commit();
        System.out.println(System.currentTimeMillis()-start);
        pst.close();
        con.close();
    }
    //读取pic
    public  void readtpic() throws Exception{
        Connection con=null;
        PreparedStatement  pst=null;
        con = getConnection();
        con.setAutoCommit(false);
        long start=System.currentTimeMillis();
        //绑定参数
        String sql="select pic from mytablepic where id=? ";
        pst = con.prepareStatement(sql);
        //(1,1) 后面的参数即是上面的sql里面的值
        pst.setInt(1,1);
       ResultSet st=pst.executeQuery();
       if(st.next()){
           byte[] bytes =st.getBytes(1);
           FileOutputStream fis= new FileOutputStream("D:/12333.jpg");
           fis.write(bytes);
           fis.close();
       }
        con.commit();
        pst.close();
        con.close();
    }
    //存储过程
    public  void pro()throws Exception{
        Connection con=null;
        con = getConnection();
        CallableStatement cst=con.prepareCall("{ call simple(?,?,?)}");
        cst.setInt(1,1);
        cst.setInt(2,3);
        cst.registerOutParameter(3,Types.INTEGER);
        cst.execute();
        int s=cst.getInt(3);
        System.out.println(s);
        cst.close();
        con.close();
    }

    //调用函数
    public  void functio2()throws Exception{
        Connection con=null;
        con = getConnection();
        CallableStatement cst=con.prepareCall("{ ? = call functio2(?,?)}");
        cst.setInt(2,3);
        cst.setInt(3,3);
        cst.registerOutParameter(1,Types.INTEGER);
        cst.execute();
        int s=cst.getInt(1);
        System.out.println(s);
        cst.close();
        con.close();
    }



}