JDBC的增删改查练习

package JDBCDemo;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;
import java.util.Scanner;


public class demo1 {
    public static void main(String[] args) throws IOException, SQLException {
        Scanner scanner = new Scanner(System.in);
        System.out.print("请输入id：");
        String  id = scanner.next();
        System.out.print("请输入名字：");
        String  name = scanner.next();
        String sql ="insert into l values (?,?)";
        int i = test01(sql, id, name);
        System.out.println(i>0?"插入成功":"插入失败");
    }

   static int test01(String sql, Object...args){
       try {
           InputStream resourceAsStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
           Properties properties = new Properties();
           properties.load(resourceAsStream);
           Connection connection = DriverManager.getConnection(properties.getProperty("url"),properties.getProperty("user"),properties.getProperty("password"));
           PreparedStatement preparedStatement = connection.prepareStatement(sql);
           for (int i = 0; i < args.length; i++) {
               preparedStatement.setObject(i+1,args[i]);
           }
           //返回的是受影响的行数
           int i = preparedStatement.executeUpdate();
           return i;
       } catch (IOException e) {
           e.printStackTrace();
       } catch (SQLException throwables) {
           throwables.printStackTrace();
       }
       return 0;
   }
}

package JDBCDemo;

import JDBCTest.Selectdata;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.Properties;


public class demo2 {
    public static void main(String[] args) throws SQLException, NoSuchFieldException, InstantiationException, IllegalAccessException, IOException {
        String sql = "select id ,name from l where id <> ?";
        ArrayList demo = demo(Selectdata.class, sql, 10);
        for (Selectdata a :
                demo) {
            System.out.println(a.toString());
        }
    }




    static  ArrayList demo(Class clazz,String sql,Object...args) throws IOException, SQLException, NoSuchFieldException, IllegalAccessException, InstantiationException {
        InputStream resourceAsStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        properties.load(resourceAsStream);
        Connection connection = DriverManager.getConnection(properties.getProperty("url"),properties.getProperty("user"),properties.getProperty("password"));
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i < args.length; i++) {
            preparedStatement.setObject(i+1,args[i]);
        }
        ResultSet resultSet = preparedStatement.executeQuery();
        ArrayList arrayList = new ArrayList<>();
        ResultSetmetaData metaData = resultSet.getmetaData();
        int columnCount = metaData.getColumnCount();
        while (resultSet.next()){
            T t = clazz.newInstance();
            for (int i = 0; i < columnCount; i++) {
                String columnLabel = metaData.getColumnLabel(i+1);
                String string = resultSet.getString(i + 1);
                Field declaredField = clazz.getDeclaredField(columnLabel);
                declaredField.setAccessible(true);
                declaredField.set(t,string);
            }
            arrayList.add(t);
        }
        return arrayList;
    }
}

package JDBCDemo;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Properties;


public class demo3 {
    public static void main(String[] args) throws Exception{
        String sql ="insert into blobt values(?)";
        InputStream resourceAsStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        properties.load(resourceAsStream);
        Connection connection = DriverManager.getConnection(properties.getProperty("url"), properties.getProperty("user"), properties.getProperty("password"));
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setBlob(1,new FileInputStream(new File("picture.jpg")));
        int i = preparedStatement.executeUpdate();
        System.out.println(i);

    }
}

package JDBCDemo;

import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;


public class demo4 {
    public static void main(String[] args) throws Exception{
        InputStream resourceAsStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        properties.load(resourceAsStream);
        String sql ="select * from blobt";
        Connection connection = DriverManager.getConnection(properties.getProperty("url"),properties.getProperty("user"),properties.getProperty("password"));
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()){
            Blob blob = resultSet.getBlob(1);
            InputStream binaryStream = blob.getBinaryStream();
            FileOutputStream fileOutputStream = new FileOutputStream(new File("picture1.jpg"));
            byte[] buffer =new byte[1024];
            int len;
            while ((len =binaryStream.read(buffer))!=-1){
                fileOutputStream.write(buffer,0,len);
            }
        }
    }
}

package JDBCDemo;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;




public class demo5 {
    public static void main(String[] args) throws IOException, SQLException {
        InputStream resourceAsStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        properties.load(resourceAsStream);
        Connection connection = DriverManager.getConnection(properties.getProperty("url"), properties.getProperty("user"), properties.getProperty("password"));
        String sql ="insert into bigdata(name) values (?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //关闭自动提交
        connection.setAutoCommit(false); 
        long start =System.currentTimeMillis();
        for (int i = 1 ; i <=20000 ; i++) {
            preparedStatement.setString(1,String.valueOf(i));
           //攒‘sql’
            preparedStatement.addBatch();
            if (i%500==0){
                //执行batch里面攒起来的sql语句
             preparedStatement.executeBatch();

             //清空batch
             preparedStatement.clearBatch();
            }
        }
        //提交sql
        connection.commit();
        long end =System.currentTimeMillis();
        System.out.println(end-start);
        preparedStatement.close();
        connection.close();
    }
}