JDBC笔记（1）

1. JDBC所有步骤：

2. 数据库的连接方式：

private static void test05() throws IOException, Exception {
        InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.propertise");
        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");
        Class.forName(driverClass);
//        Connection conn = DriverManager.getConnection(url, user, password);
        Connection conn = DriverManager.getConnection(url,user,password);
        System.out.println(conn);
    }
}

其中，配置文件声明在工程的src目录下：【jdbc.properties】

user=root
password=abc123
url=jdbc:mysql://localhost:3306/test
driverClass=com.mysql.jdbc.Driver

使用配置文件的好处：
①实现了代码和数据的分离，如果需要修改配置信息，直接在配置文件中修改，不需要深入代码
②如果修改了配置信息，省去重新编译的过程。

3.使用PreparedStatement实现对数据库的增删改查

1.概述：由于Statement有弊端所有直接使用他的子类PreparedStatement进行操作
2.Java与SQL对应数据类型转换表：

3.增加操作：

package demo02;

import demo01.ConnectionTest;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Properties;

public class PreparedStatement01 {
    public static void main(String[] args)throws Exception  {
        //1.加载配置文件
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.propertise");
            Properties pros = new Properties();
            pros.load(is);

            //2.读取配置信息
            String user = pros.getProperty("user");
            String password = pros.getProperty("password");
            String url = pros.getProperty("url");
            String driverClass = pros.getProperty("driverClass");

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

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

            String sql = "insert into customers(name,email,birth)values(?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1,"张三");
            ps.setString(2,"zs12@qq.com");
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            java.util.Date date = sdf.parse("1111-01-13");
            ps.setDate(3,new Date(date.getTime()));

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




    }
    /*private static void test05() throws IOException, Exception {
        InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.propertise");
        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");
        Class.forName(driverClass);
//        Connection conn = DriverManager.getConnection(url, user, password);
        Connection conn = DriverManager.getConnection(url,user,password);
        System.out.println(conn);*//*
    }*/
}

可以把获取连接和关闭资源封装成包方便使用：

package util;

import demo01.ConnectionTest;

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

public class JDBCutils {


    public static void closeziyuan(Connection conn,PreparedStatement ps) {
        try {
            if(conn != null)
            conn.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if(ps != null)
            ps.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public static Connection huoqulianjie() throws Exception {
        Connection conn = null;
        PreparedStatement ps = null;

        InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.propertise");
        Properties pros = new Properties();
        pros.load(is);

        //2.读取配置信息
        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");

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

        //4.获取连接
        conn = DriverManager.getConnection(url, user, password);
//        System.out.println(conn);
        return conn;
    }
    public static void closeziyuan(Connection conn, PreparedStatement ps, ResultSet rs) {
        try {
            if(conn != null)
                conn.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if(ps != null)
                ps.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if(rs != null)
                rs.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

通用增删改的代码：

package demo02;

import util.JDBCutils;

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

public class PreparedStatement03 {
    public static void main(String[] args) {
//            String sql = "delete from customers where id = ?";
//            update(sql,21);
        String sql = "update  examstudent set StudentName = ?where FlowID =  ?";
        update(sql,"黎明",1);
    }
    public static void update(String sql,Object ...args) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCutils.huoqulianjie();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i+1,args[i]);//注意两个参数的声明不一样

            }
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }
        JDBCutils.closeziyuan(conn,ps);