jdbc学习

最新推荐文章于 2024-06-28 16:42:10 发布

wsfhdhjs

最新推荐文章于 2024-06-28 16:42:10 发布

阅读量1k

点赞数

分类专栏： java基础文章标签： java mysql 开发语言

本文链接：https://blog.csdn.net/wsfhdhjs/article/details/121656030

版权

java基础专栏收录该内容

18 篇文章 0 订阅

订阅专栏

1.连接数据库

package com.atguigu.java;

import org.junit.Test;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

/**
 * @author shkstart
 * @create 2021-12-{DAY} 14:48
 */
public class ConnectionTest {

    @Test
    public void testConnection1() throws SQLException {

        //获取Driver实现类对象
        Driver driver = new com.mysql.jdbc.Driver();

        //jdbc:mysql:协议
        //localhost:ip地址
        //3306:默认mysql的端口号
        //test:test数据库
        String url="jdbc:mysql://localhost:3306/test";

        //将用户名和密码封装在Properties中
        Properties info = new Properties();
        info.setProperty("user","root");
        info.setProperty("password","wsf123");

        Connection conn = driver.connect(url,info);

        System.out.println(conn);
    }

    //方式2：对方式1的迭代：在如下的程序中不出现第三方api，使得程序具有更好的可移植性
    @Test
    public void testConnection2() throws Exception {
        //1.获取Driver实现类对象，使用反射
        Class<?> clazz = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver) clazz.newInstance();

        //2.提供要连接的数据库
        String url = "jdbc:mysql://localhost:3306/test";

        //3.提供连接需要的用户名和密码
        Properties info = new Properties();
        info.setProperty("user","root");
        info.setProperty("password","wsf123");

        //4.获取连接
        Connection conn = driver.connect(url, info);
        System.out.println(conn);

    }

    //方式3：使用DriverManager替换Driver
    @Test
    public void testConnection3() throws Exception{

        //1.获取Driver实现类的对象
        Class<?> clazz = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver) clazz.newInstance();

        //2.提供另外三个连接的基本信息
        String url="jdbc:mysql://localhost:3306/test";
        String user="root";
        String password="wsf123";

        //注册驱动
        DriverManager.registerDriver(driver);

        //获取连接
        Connection conn = DriverManager.getConnection(url, user, password);

        System.out.println(conn);
    }

    //方式4：使用DriverManager替换Driver
    @Test
    public void testConnection4() throws Exception{

        //1.提供另外三个连接的基本信息
        String url="jdbc:mysql://localhost:3306/test";
        String user="root";
        String password="wsf123";

        //2.获取Driver实现类的对象
        Class.forName("com.mysql.jdbc.Driver");


        //获取连接
        Connection conn = DriverManager.getConnection(url, user, password);
        System.out.println(conn);
    }


}

例子2

package com.atguigu3.preparedstatement.crud;

import com.atguigu3.util.JDBCUtils;
import org.junit.Test;

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.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Properties;

/**
 * @author shkstart
 * @create 2021-12-{DAY} 15:54
 */
public class PreparedStatementUpdateTest {

    @Test
    public void testCommonUpdate(){
        //删除操作
//        String sql = "delete from customers where id = ?";
//        update(sql,3);

        //修改操作
        String sql = "update `order` set order_name = ? where order_id = ?";
        update(sql,"DD","2");

    }

    //通用的增删改操作
    public void update(String sql,Object ...args)   {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            //sql中占位符的个数与可变形参的长度相同
            //1.获取数据库的连接
            conn = JDBCUtils.getConnection();
            //2.预编译sql语句，返回
            ps = conn.prepareStatement(sql);
            //3.填充占位符
            for(int i=0; i<args.length; i++){
                ps.setObject(i+1,args[i]);
            }

            //4.执行
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //5.资源的关闭
            JDBCUtils.closeResource(conn,ps);
        }



    }


    //修改customers表的一条记录
    @Test
    public void testUpdate() {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            //1.获取数据库的连接
            conn = JDBCUtils.getConnection();

            //2.预编译sql语句，返回PrepareStatement的实例
            String sql = "update customers set name = ? where id = ?";
            ps = conn.prepareStatement(sql);

            //3.填充占位符
            ps.setObject(1,"莫扎特");
            ps.setObject(2,18);

            //4.执行操作
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }

        //5.资源关闭
        JDBCUtils.closeResource(conn,ps);


    }

    //向customers表中添加一条记录
    @Test
    public void testInsert() {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");

            Properties pros = new Properties();
            pros.load(is);

            String user = pros.getProperty("user");
            String password = pros.getProperty("password");
            String url = pros.getProperty("url");
            String driverClass = pros.getProperty("driverClass");

            //2.加载驱动
            Class.forName(driverClass);

            //3.获取连接
            conn = DriverManager.getConnection(url, user, password);


            //4.预编译sql语句，返回PreparedStatement的实例
            String sql = "insert into customers(name,email,birth)values(?,?,?) ";
             ps = conn.prepareStatement(sql);

            //5.充占位符
            ps.setString(1,"哪吒");
            ps.setString(2,"nezha@gmail.com");
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            java.util.Date date = sdf.parse("1000-01-01");
            ps.setDate(3,new java.sql.Date(date.getTime()));

            //6.执行操作
            ps.execute();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ParseException e) {
            e.printStackTrace();
        }

        //7.资源关闭
        try {
            if (ps != null) {
                ps.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(conn!=null){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

}

例子3

CustomerForQuery

package com.atguigu3.preparedstatement.crud;

import com.atguigu3.bean.Customer;
import com.atguigu3.util.JDBCUtils;
import org.junit.Test;

import java.lang.reflect.Field;
import java.sql.*;

/**
 * 针对于Customers表的查询操作
 * @author shkstart
 * @create 2021-12-{DAY} 17:43
 */
public class CustomerForQuery {

    @Test
    public void testQueryForCustomers(){
        String sql = "select id,name,birth,email from customers where id = ?";
        Customer customer = queryForCustomers(sql, 13);
        System.out.println(customer);

        sql="select name,email from customers where name = ?";
        Customer customer1 = queryForCustomers(sql, "周杰伦");
        System.out.println(customer1);
    }


    /*
     * 针对于customers表的通用的查询操作
     */
    public Customer queryForCustomers(String sql,Object...args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);

            for(int i=0; i<args.length; i++){
                ps.setObject(i+1,args[i]);
            }

            rs = ps.executeQuery();
            //获取结果集的原数据
            ResultSetMetaData rsmd = rs.getMetaData();
            //通过ResultSetMetaData获取结果集中的列数
            int columnCount = rsmd.getColumnCount();
            if(rs.next()){
                Customer cust = new Customer();
                //处理结果集一行数据中的每一个列
                for(int i=0; i<columnCount; i++){
                    //获取列值
                    Object columnValue = rs.getObject(i + 1);

                    //获取每个列的列名
                    String columnName = rsmd.getColumnName(i + 1);

                    //给cust对象指定的columnValue属性，赋值为value，通过反射
                    Field field = Customer.class.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(cust,columnValue);
                }

                return cust;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,ps,rs);
        }

        return null;

    }


    @Test
    public void testQuery1() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            conn = JDBCUtils.getConnection();

            //预编译sql语句
            String sql = "select id,name,email,birth from customers where id = ?";
            ps = conn.prepareStatement(sql);

            //填充占位符
            ps.setObject(1,1);

            //执行,并返回结果集
            resultSet = ps.executeQuery();

            //处理结果集
            if(resultSet.next()){  //判断结果集的下一条是否有数据，如果有数据返回true，并指针下移，如果为false，则没有

                int id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                String email = resultSet.getString(3);
                Date birth = resultSet.getDate(4);

                Customer customer = new Customer(id, name, email, birth);
                System.out.println(customer);


            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //关闭资源
            JDBCUtils.closeResource(conn,ps,resultSet);
        }




    }


}

Customer

package com.atguigu3.bean;

import java.util.Date;

/**
 * ORM编程思想(object relational mapping)
 * 一个数据表对应一个java类
 * 表中的一条记录对应java类的一个对象
 * 表中的一个字段对应java类的一个属性
 * @author shkstart
 * @create 2021-12-{DAY} 18:01
 */
public class Customer {
    private int id;
    private String name;
    private String email;
    private Date birth;

    public Customer() {
        super();
    }

    public Customer(int id, String name, String email, Date birth) {
        this.id = id;
        this.name = name;
        this.email = email;
        this.birth = birth;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Date getBirth() {
        return birth;
    }

    public void setBirth(Date birth) {
        this.birth = birth;
    }

    @Override
    public String toString() {
        return "Customer{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", email='" + email + '\'' +
                ", birth=" + birth +
                '}';
    }
}

例子4

package com.atguigu3.preparedstatement.crud;

import com.atguigu3.bean.Order;
import com.atguigu3.util.JDBCUtils;
import org.junit.Test;

import java.lang.reflect.Field;
import java.sql.*;

/**
 * 针对于表的字段名于类的属性名不相同的情况：
 * 1.必须声明sql时，使用类的属性名来命名字段的别名
 * 2.使用ResultSetMetaData时，需要使用getColumnLabel()来替换getColumnName()获取列的别名
 * 针对于order表的查询操作
 * @author shkstart
 * @create 2021-12-{DAY} 14:39
 */
public class OrderForQuery {

    @Test
    public void testOrderForQuery(){
        String sql = "select order_id orderId,order_name orderName,order_date orderDate from `order` where order_id=?";
        Order order = orderForQuery(sql, 1);
        System.out.println(order);
    }

    /**
     * 针对于order表的通用查询操作
     * @return
     */
    public Order orderForQuery(String sql,Object...args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);

            for(int i=0; i<args.length; i++){
                ps.setObject(i+1,args[i]);
            }

            //执行，获取结果集
            rs = ps.executeQuery();
            //获取结果集的元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            //获取列数
            int columnCount = rsmd.getColumnCount();
            if(rs.next()){
                Order order = new Order();
                for(int i=0; i<columnCount; i++){
                    //获取每个列的列值
                    Object columnValue = rs.getObject(i + 1);
                    //获取每个列的列名
                    //获取列的列名：getColumnName()
                    //获取列的别名：getColumnLabel()
                    String columnLabel = rsmd.getColumnLabel(i + 1);

                    //通过反射，将对象指定名columnName的属性赋值为指定的columnValue
                    Field field = Order.class.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(order,columnValue);
                }
                return  order;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,ps,rs);
        }



        return  null;

    }

    @Test
    public void testQuery(){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "select order_id,order_name,order_date from `order` where order_id=?";
            ps = conn.prepareStatement(sql);
            ps.setObject(1,1);

            rs = ps.executeQuery();
            if(rs.next()){
                int id = (int) rs.getObject(1);
                String name = (String) rs.getObject(2);
                Date date = (Date) rs.getObject(3);

                Order order = new Order(id, name, date);
                System.out.println(order);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,ps,rs);
        }


    }

}

例子5

Customer

package com.atguigu3.bean;

import java.util.Date;

/**
 * ORM编程思想(object relational mapping)
 * 一个数据表对应一个java类
 * 表中的一条记录对应java类的一个对象
 * 表中的一个字段对应java类的一个属性
 * @author shkstart
 * @create 2021-12-{DAY} 18:01
 */
public class Customer {
    private int id;
    private String name;
    private String email;
    private Date birth;

    public Customer() {
        super();
    }

    public Customer(int id, String name, String email, Date birth) {
        this.id = id;
        this.name = name;
        this.email = email;
        this.birth = birth;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Date getBirth() {
        return birth;
    }

    public void setBirth(Date birth) {
        this.birth = birth;
    }

    @Override
    public String toString() {
        return "Customer{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", email='" + email + '\'' +
                ", birth=" + birth +
                '}';
    }
}

Order

package com.atguigu3.bean;

import java.sql.Date;

/**
 * @author shkstart
 * @create 2021-12-{DAY} 14:40
 */
public class Order {
    private int orderId;
    private String orderName;
    private Date orderDate;

    public Order() {
    }

    public Order(int orderId, String orderName, Date orderDate) {
        this.orderId = orderId;
        this.orderName = orderName;
        this.orderDate = orderDate;
    }

    public int getOrderId() {
        return orderId;
    }

    public void setOrderId(int orderId) {
        this.orderId = orderId;
    }

    public String getOrderName() {
        return orderName;
    }

    public void setOrderName(String orderName) {
        this.orderName = orderName;
    }

    public Date getOrderDate() {
        return orderDate;
    }

    public void setOrderDate(Date orderDate) {
        this.orderDate = orderDate;
    }

    @Override
    public String toString() {
        return "Order{" +
                "orderId=" + orderId +
                ", orderName='" + orderName + '\'' +
                ", orderDate=" + orderDate +
                '}';
    }
}

PreparedStatementQueryTest

package com.atguigu3.preparedstatement.crud;

import com.atguigu3.bean.Customer;
import com.atguigu3.bean.Order;
import com.atguigu3.util.JDBCUtils;
import org.junit.Test;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

/**
 * @author shkstart
 * @create 2021-12-{DAY} 15:29
 */
public class PreparedStatementQueryTest {

    @Test
    public void testGetInstance(){
        String sql = "select id,name,email from customers where id = ?";
        Customer customer = getInstance(Customer.class, sql, 12);
        System.out.println(customer);

        String sql1 = "select order_id orderId,order_name orderName from  `order` where order_id=?";
        Order order = getInstance(Order.class, sql1, 1);
        System.out.println(order);
    }

    public <T> T getInstance(Class<T> clazz,String sql,Object...args){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);

            for(int i=0; i<args.length; i++){
                ps.setObject(i+1,args[i]);
            }

            rs = ps.executeQuery();
            //获取结果集的原数据
            ResultSetMetaData rsmd = rs.getMetaData();
            //通过ResultSetMetaData获取结果集中的列数
            int columnCount = rsmd.getColumnCount();
            if(rs.next()){
                T t = clazz.getDeclaredConstructor().newInstance();
                //处理结果集一行数据中的每一个列
                for(int i=0; i<columnCount; i++){
                    //获取列值
                    Object columnValue = rs.getObject(i + 1);

                    //获取每个列的列名
                    String columnLabel = rsmd.getColumnLabel(i + 1);

                    //给cust对象指定的columnValue属性，赋值为value，通过反射
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,columnValue);
                }

                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,ps,rs);
        }

        return null;
    }


}

例子6

package com.atguigu3.preparedstatement.crud;

import com.atguigu3.bean.Customer;
import com.atguigu3.bean.Order;
import com.atguigu3.util.JDBCUtils;
import org.junit.Test;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author shkstart
 * @create 2021-12-{DAY} 15:29
 */
public class PreparedStatementQueryTest {

    @Test
    public void testGetForList(){
        String sql = "select id,name,email from customers where id<?";
        List<Customer> list = getForList(Customer.class, sql, 12);
        list.forEach(System.out::println);

        String sql1 = "select order_id orderId,order_name orderName from  `order` where order_id<?";
        List<Order> orderList = getForList(Order.class, sql1, 5);
        orderList.forEach(System.out::println);
    }

    public <T> List<T> getForList(Class<T> clazz, String sql, Object...args){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);

            for(int i=0; i<args.length; i++){
                ps.setObject(i+1,args[i]);
            }

            rs = ps.executeQuery();
            //获取结果集的原数据
            ResultSetMetaData rsmd = rs.getMetaData();
            //通过ResultSetMetaData获取结果集中的列数
            int columnCount = rsmd.getColumnCount();
            //創建集合對象
            ArrayList<T> list = new ArrayList<>();

            while (rs.next()){
                T t = clazz.getDeclaredConstructor().newInstance();
                //处理结果集一行数据中的每一个列:給t對象指定的屬性賦值
                for(int i=0; i<columnCount; i++){
                    //获取列值
                    Object columnValue = rs.getObject(i + 1);

                    //获取每个列的列名
                    String columnLabel = rsmd.getColumnLabel(i + 1);

                    //给cust对象指定的columnValue属性，赋值为value，通过反射
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,columnValue);
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,ps,rs);
        }

        return  null;
    }


    @Test
    public void testGetInstance(){
        String sql = "select id,name,email from customers where id = ?";
        Customer customer = getInstance(Customer.class, sql, 12);
        System.out.println(customer);

        String sql1 = "select order_id orderId,order_name orderName from  `order` where order_id=?";
        Order order = getInstance(Order.class, sql1, 1);
        System.out.println(order);
    }

    public <T> T getInstance(Class<T> clazz,String sql,Object...args){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);

            for(int i=0; i<args.length; i++){
                ps.setObject(i+1,args[i]);
            }

            rs = ps.executeQuery();
            //获取结果集的原数据
            ResultSetMetaData rsmd = rs.getMetaData();
            //通过ResultSetMetaData获取结果集中的列数
            int columnCount = rsmd.getColumnCount();
            if(rs.next()){
                T t = clazz.getDeclaredConstructor().newInstance();
                //处理结果集一行数据中的每一个列
                for(int i=0; i<columnCount; i++){
                    //获取列值
                    Object columnValue = rs.getObject(i + 1);

                    //获取每个列的列名
                    String columnLabel = rsmd.getColumnLabel(i + 1);

                    //给cust对象指定的columnValue属性，赋值为value，通过反射
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,columnValue);
                }

                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,ps,rs);
        }

        return null;
    }


}

例子7

package com.atguigu2.statement.crud;

import com.atguigu.statement.crud.User;
import com.atguigu3.util.JDBCUtils;
import org.junit.Test;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Scanner;

/**
 * 使用PreparedStatement替換Statement，解決SQL注入問題
 * @author shkstart
 * @create 2021-12-{DAY} 16:05
 */
public class PreparedStatementTest {

    @Test
    public void testLogin() {
        Scanner scan = new Scanner(System.in);

        System.out.print("用户名：");
        String user = scan.nextLine();
        System.out.print("密   码：");
        String password = scan.nextLine();


        String sql = "SELECT user,password FROM user_table WHERE user = ? and password = ?";
        User returnUser = getInstance(User.class,sql,user,password);
        if (returnUser != null) {
            System.out.println("登陆成功!");
        } else {
            System.out.println("用户名或密码错误！");
        }


    }

    public <T> T getInstance(Class<T> clazz,String sql,Object...args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);

            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }

            rs = ps.executeQuery();
            //获取结果集的原数据
            ResultSetMetaData rsmd = rs.getMetaData();
            //通过ResultSetMetaData获取结果集中的列数
            int columnCount = rsmd.getColumnCount();
            if (rs.next()) {
                T t = clazz.getDeclaredConstructor().newInstance();
                //处理结果集一行数据中的每一个列
                for (int i = 0; i < columnCount; i++) {
                    //获取列值
                    Object columnValue = rs.getObject(i + 1);

                    //获取每个列的列名
                    String columnLabel = rsmd.getColumnLabel(i + 1);

                    //给cust对象指定的columnValue属性，赋值为value，通过反射
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columnValue);
                }

                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, ps, rs);
        }

        return null;
    }

}

例子8

package com.atguigu5.blob;

import com.atguigu3.util.JDBCUtils;
import org.junit.Test;

import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;

/**
 *测试PrepareStatement操作Blob类型的数据
 * @author shkstart
 * @create 2021-12-{DAY} 16:46
 */
public class BlobTest {

    //向数据表customers中插入Blob类型的字段
    @Test
    public void testInsert(){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "insert into customers(name,email,birth,photo)values(?,?,?,?)";
            ps = conn.prepareStatement(sql);

            ps.setObject(1,"张宇豪");
            ps.setObject(2,"zhang@qq.com");
            ps.setObject(3,"1992-09-08");
            FileInputStream is = new FileInputStream(new File("885.png"));
            ps.setBlob(4,is);

            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }

        JDBCUtils.closeResource(conn,ps);

    }
}

例子9

package com.atguigu5.blob;

import com.atguigu3.bean.Customer;
import com.atguigu3.util.JDBCUtils;
import org.junit.Test;

import java.io.*;
import java.sql.*;

/**
 *测试PrepareStatement操作Blob类型的数据
 * @author shkstart
 * @create 2021-12-{DAY} 16:46
 */
public class BlobTest {

    //向数据表customers中插入Blob类型的字段
    @Test
    public void testInsert(){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "insert into customers(name,email,birth,photo)values(?,?,?,?)";
            ps = conn.prepareStatement(sql);

            ps.setObject(1,"张宇豪");
            ps.setObject(2,"zhang@qq.com");
            ps.setObject(3,"1992-09-08");
            FileInputStream is = new FileInputStream(new File("885.png"));
            ps.setBlob(4,is);

            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }

        JDBCUtils.closeResource(conn,ps);
    }

    //查询数据表customers中Blob类型的字段
    @Test
    public void testQuery() throws Exception{
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        InputStream is=null;
        FileOutputStream fos=null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "select id,name,email,birth,photo from customers where id = ?";
            ps = conn.prepareStatement(sql);

            ps.setInt(1,30);

            rs = ps.executeQuery();

            if(rs.next()){

                int id = rs.getInt("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                Date birth = rs.getDate("birth");

                Customer cust = new Customer(id, name, email, birth);

                System.out.println(cust);

                //将Blob类型的字段下载下来，以文件的方式保存在本地
                Blob photo = rs.getBlob("photo");
                is = photo.getBinaryStream();
                fos = new FileOutputStream("zhangyuhao.jpg");
                byte[] buffer = new byte[1024];
                int len;
                while((len=is.read(buffer))!=-1){
                    fos.write(buffer,0,len);
                }


            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {

            try {
                if(is!=null)
                    is.close();
            } catch (IOException e) {
                e.printStackTrace();
            }

            try {
                if(fos!=null)
                   fos.close();
            } catch (IOException e) {
                e.printStackTrace();
            }

            JDBCUtils.closeResource(conn,ps,rs);
        }


    }


}

例子10

package com.atguigu5.blob;

import com.atguigu3.util.JDBCUtils;
import org.junit.Test;

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

/**
 * @author shkstart
 * @create 2021-12-{DAY} 18:44
 */
public class InsertTest {

    //批量插入的方式2:使用PreparedStatement
    @Test
    public void testInsert1(){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "insert into goods(name)values(?)";
            ps = conn.prepareStatement(sql);
            for (int i = 1; i <= 20000; i++) {
                ps.setObject(1,"name_"+i);
                ps.execute();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,ps);
        }


    }

    /*
    * 批量插入的方式三
    * 1.addBatch(),executeBatch(),clearBatch()
    * 2.mysql服务器默认是关闭批处理的，我们需要一个参数，让mysql开启批处理的支持  ?rewriteBatchedStatements=true写在配置文件url后面
    *
    * 3.使用更新的mysql 驱动：mysql-connector-java-5.1.37-bin.jar
    *
    *
    * */

    @Test
    public void testInsert2(){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.getConnection();

            //设置不允许自动提交数据
            conn.setAutoCommit(false);

            String sql = "insert into goods(name)values(?)";
            ps = conn.prepareStatement(sql);
            for (int i = 1; i <= 20000; i++) {
                ps.setObject(1,"name_"+i);

                //1.攒sql
                ps.addBatch();
                if(i%500==0){
                    //2.执行batch
                    ps.executeBatch();
                    //3.清空batch
                    ps.clearBatch();
                }
            }
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,ps);
        }


    }

}

例子11

package day09.src.com.atguigu.transaction;

import day09.src.com.atguigu3.util.JDBCUtils;
import org.junit.Test;

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

/**
 * @author shkstart
 * @create 2021-12-{DAY} 20:30
 */
public class TransactionTest {


    @Test
    public void testUpdate(){

        String sql1 = "update user_table set balance = balance - 100 where user=?";
        update(sql1,"AA");
        //模拟网络异常
        System.out.println(10/0);
        String sql2 = "update user_table set balance = balance + 100 where user=?";
        update(sql2,"BB");



        System.out.println("转账成功");

    }


    //通用的增删改操作------version 1.0
    public int update(String sql,Object ...args){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            //sql中占位符的个数与可变形参的长度相同
            //1.获取数据库的连接
            conn = JDBCUtils.getConnection();
            //2.预编译sql语句，返回
            ps = conn.prepareStatement(sql);
            //3.填充占位符
            for(int i=0; i<args.length; i++){
                ps.setObject(i+1,args[i]);
            }
            //4.执行
            return ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //5.资源的关闭
            JDBCUtils.closeResource(conn,ps);
        }
        return 0;
    }

    @Test
    public void testUpdateWithTx()throws Exception{
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();

            //1.取消数据库的自动连接
            conn.setAutoCommit(false);

            String sql1 = "update user_table set balance = balance - 100 where user=?";
            update(conn,sql1,"AA");
            //模拟网络异常
              System.out.println(10/0);
            String sql2 = "update user_table set balance = balance + 100 where user=?";
            update(conn,sql2,"BB");


            System.out.println("转账成功");

            //2.提交数据
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            //3.回滚数据
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }finally {
            JDBCUtils.closeResource(conn,null);
        }


    }

    //通用的增删改操作------version 2.0
    public int update(Connection conn,String sql,Object ...args){
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            //3.填充占位符
            for(int i=0; i<args.length; i++){
                ps.setObject(i+1,args[i]);
            }
            //4.执行
            return ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {

            //修改其为自动提交数据
            //主要针对于使用数据库连接池的使用
            try {
                conn.setAutoCommit(true);
            } catch (SQLException e) {
                e.printStackTrace();
            }

            //5.资源的关闭
            JDBCUtils.closeResource(null,ps);
        }
        return 0;
    }


}

wsfhdhjs

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
jdbc学习

1.连接数据库package com.atguigu.java;import org.junit.Test;import java.sql.Connection;import java.sql.Driver;import java.sql.DriverManager;import java.sql.SQLException;import java.util.Properties;/** * @author shkstart * @create 2021-12-{DAY} 14:48
复制链接

扫一扫