JDBC的增删改查练习

最新推荐文章于 2022-08-04 08:34:38 发布

CCQsuai戈尔

最新推荐文章于 2022-08-04 08:34:38 发布

阅读量126

点赞数

分类专栏： JDBC 文章标签： java

本文链接：https://blog.csdn.net/CCQDDQ/article/details/120598123

版权

JDBC 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

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;

/**
 * @className: demo1
 * @description: 从控制台向数据库插入一条数据
 * @author: CCQ
 * @date: 2021/9/22
 **/
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;

/**
 * @className: demo2
 * @description: 查询操作
 * @author: CCQ
 * @date: 2021/9/22
 **/
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<Selectdata> demo = demo(Selectdata.class, sql, 10);
        for (Selectdata a :
                demo) {
            System.out.println(a.toString());
        }
    }




    static <T> ArrayList<T> demo(Class<T> 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<T> 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;

/**
 * @className: demo3
 * @description: 插入blob类型数据
 * @author: CCQ
 * @date: 2021/9/23
 **/
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;

/**
 * @className: demo4
 * @description: 查询blob类型数据并打印到picture1.jpg文件中
 * @author: CCQ
 * @date: 2021/9/23
 **/
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;

/**
 * @className: demo5
 * @description: 批量插入
 * @author: CCQ
 * @date: 2021/9/24
 **/

/*
addBatch();此方法是攒sql语句，类似于缓冲区？
executeBatch();此方法是批量执行sql语句
clearBatch();此方法是清空Batch里面的sql语句

*/
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();
    }
}