jdbc-PreparedStatement实现crud操作

package JDBCTest.PreparedStatement;

import JDBCTest.JDBCUtils.JDBCUtils;
import org.junit.jupiter.api.Test;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.Properties;

/***
 * 使用PreparedStatement来替代Statement，来实现对数据表的增删改查
 * 增删改：
 * */

public class PreparedStatementTest {
    @Test
    public void test3(){
        /**
         * @apiNote : sql查询语句方法
         */

    }

    @Test
    public void testUpadate(){
        String sql = "delete  from `order` where order_id = ?";
        try {
            PreparedStatementTest.update(sql,2);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static void update(String sql,Object ...args) throws  Exception{
        com.mysql.jdbc.Connection connection = JDBCUtils.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i < args.length; i++) {
            preparedStatement.setObject(i+1,args[i]);//小心参数
        }
        preparedStatement.execute();
        JDBCUtils.closeRuscre(connection, (com.mysql.jdbc.PreparedStatement) preparedStatement);
    }
    @Test
    public void test2() throws  Exception{

        com.mysql.jdbc.Connection connection = JDBCUtils.getConnection();
        String sql ="alter table customers  change phone phone varchar(25)" ;
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        JDBCUtils.closeRuscre(connection, (com.mysql.jdbc.PreparedStatement) preparedStatement);


    }

    /***
     * 增删改查
     * 增删改3   查 需要有返回信息
     *
     * */
    @Test
    public void test() throws  Exception{
        //获取配置文件
        InputStream resourceAsStream = PreparedStatementTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        properties.load(resourceAsStream);
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driverClass = properties.getProperty("driverClass");
        //加载mysql
        Class.forName(driverClass);
        //获取连接
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);
        //4
        String sql = "INSERT INTO customers(NAME,email,birth)\n" +
                "VALUES(?,?,?)";
        String sql1 = "ALTER TABLE customers\n" +
                "ADD COLUMN phone VARCHAR(20)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        PreparedStatement preparedStatement1 = connection.prepareStatement(sql1);
        preparedStatement.setString(1,"李明");
        preparedStatement.setString(2,"liming@163.com");
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        java.util.Date parse = simpleDateFormat.parse("1000-01-1-12");
        preparedStatement.setDate(3, new Date(parse.getDate()));
        //执行sql
        preparedStatement.execute();
        preparedStatement1.execute();
        //资源的关闭
        connection.close();
        preparedStatement.close();
        resourceAsStream.close();

    }

package JDBCTest.PreparedStatement;

import JDBCTest.JDBCUtils.JDBCUtils;
import com.mysql.jdbc.Connection;
import org.junit.jupiter.api.Test;

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

public class CustomerForQuery {

    @Test
    public void test2(){
        String sql = "SELECT id,NAME,email,birth\n" +
                "FROM customers\n" +
                "WHERE id = ?";
        Customer customer = querForCustmers(sql, 1);
        System.out.println(customer.toString());

    }
    public Customer querForCustmers(String sql,Object ...args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i+1,args[i]);
            }
            resultSet = preparedStatement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            if (resultSet.next()){
                Customer cust = new Customer();
                for (int i = 0; i <columnCount ; i++) {
                    Object obj = resultSet.getObject(i+1);
                    String catalogName = metaData.getCatalogName(i + 1);

                    Field declaredField = Customer.class.getDeclaredField(catalogName);
                    declaredField.setAccessible(true);
                    declaredField.set(cust,obj);

                }
                return cust;

            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeRuscre(connection, (com.mysql.jdbc.PreparedStatement) preparedStatement,resultSet);
        }

        return null;

    }
    @Test
    public void test()   {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtils.getConnection();
            String sql = "select id,name,email,birth from customers where id = 1";
            preparedStatement = connection.prepareStatement(sql);

            resultSet = preparedStatement.executeQuery();
            //处理结果集
            if (resultSet.next()) {
                //获取当前数据的字段
                int anInt = resultSet.getInt(1);
                String string = resultSet.getString(2);
                String string1 = resultSet.getString(3);
                Date date = resultSet.getDate(4);
    //            //方拾1：
    //            System.out.println("id = " + anInt  + "姓名"  + string + " email = "+string1 + "生日 =" +date);
                Customer customer = new Customer(anInt,string,string1,date);
                System.out.println(customer.toString());

            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            JDBCUtils.closeRuscre(connection, (com.mysql.jdbc.PreparedStatement) preparedStatement,resultSet);
        }




    }

package JDBCTest.PreparedStatement;

import java.sql.Date;

public class Customer {
    private int id;
    private String name;
    private String email;
    private Date date;
    public Customer(){

    }

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

    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 getDate() {
        return date;
    }

    public void setDate(Date date) {
        this.date = date;
    }

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

}

}

}

package JDBCTest.JDBCUtils;

import JDBCTest.PreparedStatement.PreparedStatementTest;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;

import java.io.InputStream;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

public class JDBCUtils {
   public static  Connection getConnection() throws Exception{
       InputStream resourceAsStream = PreparedStatementTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
       Properties properties = new Properties();
       properties.load(resourceAsStream);
       String user = properties.getProperty("user");
       String password = properties.getProperty("password");
       String url = properties.getProperty("url");
       String driverClass = properties.getProperty("driverClass");
       //加载mysql
       Class.forName(driverClass);
       //获取连接
       java.sql.Connection connection = DriverManager.getConnection(url, user, password);
       return (Connection) connection;
   }
   public  static  void closeRuscre(Connection connection, com.mysql.jdbc.PreparedStatement preparedStatement){
       try {
           connection.close();
       } catch (SQLException throwables) {
           throwables.printStackTrace();
       }
       try {
           preparedStatement.close();
       } catch (SQLException throwables) {
           throwables.printStackTrace();
       }

   }
   public static  void closeRuscre(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet){
       try {
           connection.close();
       } catch (SQLException throwables) {
           throwables.printStackTrace();
       }
       try {
           preparedStatement.close();
       } catch (SQLException throwables) {
           throwables.printStackTrace();
       }
       try {
           resultSet.close();
       } catch (SQLException throwables) {
           throwables.printStackTrace();
       }

   }

}